家庭装修报价明细预算表_网络系统运维_宝安网站建设_如何建立网站的步骤

转自：转载地址

.NetCore WebAPI 导入、导出Excel文件

导入

思路：上传Excel文件，使用MemoryStream 在内存中加载，使用NPOI读取内容到Model类中。

/// <summary>
/// 导入Excel文件
/// </summary>
/// <param name="excelFile"></param>
/// <returns></returns>
[HttpPost]
public IActionResult UploadUserInfo(IFormFile excelFile)
{try{var postFile = Request.Form.Files[0];string extName = Path.GetExtension(postFile.FileName);if (!new string[] { ".xls", ".xlsx" }.Contains(extName)){return Ok(new{error = 1,msg = "必须是Excel文件"});}
    MemoryStream ms = new MemoryStream();postFile.CopyTo(ms);ms.Position = <span class="hljs-number">0</span>;IWorkbook wb = null;<span class="hljs-keyword">if</span> (extName.ToLower().Equals(<span class="hljs-string">".xls"</span>)) <span class="hljs-comment">// 97-2003版本</span>{wb = new HSSFWorkbook(ms);}<span class="hljs-keyword">else</span>{wb = new XSSFWorkbook(ms); <span class="hljs-comment">// 2007以上版本</span>}ISheet sheet = wb.GetSheetAt(<span class="hljs-number">0</span>);<span class="hljs-comment">//总行数（0开始）</span><span class="hljs-type">int</span> totalRow = sheet.LastRowNum;<span class="hljs-comment">// 总列数（1开始）</span><span class="hljs-type">int</span> totalColumn = sheet.GetRow(<span class="hljs-number">0</span>).LastCellNum;List&lt;Stu&gt; stuList = new();<span class="hljs-keyword">for</span> (<span class="hljs-type">int</span> i = <span class="hljs-number">1</span>; i &lt;= totalRow; i++){IRow row = sheet.GetRow(i);<span class="hljs-comment">// 判定第5列的值是不是日期，日期的值类型可以按日期来读，也可以用数据的方式来读</span>var isDate = DateUtil.IsCellDateFormatted(row.GetCell(<span class="hljs-number">4</span>));                   <span class="hljs-built_in">string</span> StuName = row.GetCell(<span class="hljs-number">0</span>).StringCellValue;<span class="hljs-type">int</span> Sex = row.GetCell(<span class="hljs-number">1</span>).StringCellValue == <span class="hljs-string">"男"</span> ? <span class="hljs-number">0</span> : <span class="hljs-number">1</span>;<span class="hljs-built_in">string</span> Phone = ((<span class="hljs-type">long</span>)row.GetCell(<span class="hljs-number">2</span>).NumericCellValue).ToString();<span class="hljs-type">int</span> CId = (<span class="hljs-type">int</span>)row.GetCell(<span class="hljs-number">3</span>).NumericCellValue;DateTime InDate = row.GetCell(<span class="hljs-number">4</span>).DateCellValue;decimal JF = (decimal)row.GetCell(<span class="hljs-number">5</span>).NumericCellValue;<span class="hljs-comment">// 第6列有可能是空的</span><span class="hljs-built_in">string</span> Pic = <span class="hljs-string">""</span>;<span class="hljs-keyword">if</span>(row.GetCell(<span class="hljs-number">6</span>) != null){CellType type = row.GetCell(<span class="hljs-number">6</span>).CellType;<span class="hljs-keyword">if</span> (type != CellType.Blank){Pic = row.GetCell(<span class="hljs-number">6</span>).StringCellValue;}}<span class="hljs-type">int</span> State = (<span class="hljs-type">int</span>)row.GetCell(<span class="hljs-number">7</span>).NumericCellValue;var stu = new Stu{StuName = StuName,Sex = Sex,Phone = Phone,CId = CId,InDate = InDate,JF = JF,Pic =Pic,State = State,IsOk = <span class="hljs-literal">true</span>,};stuList.Add(stu);}db.Stu.AddRange(stuList);db.SaveChanges();wb.Close();<span class="hljs-keyword">return</span> Ok(new{error = <span class="hljs-number">0</span>,importCount = stuList.Count,msg = <span class="hljs-string">""</span>});
}
catch (Exception)
{throw;
}

}

导出

导出后端

思路：使用NPOI使用 IWorkBook ，一行一行写入要导出数据，最终返回 FileContentResult

默认（不使用模板）

/// <summary>
/// 导出所有的信息为Excel
/// </summary>
/// <returns></returns>
[HttpGet]
public IActionResult ExportExcel()
{try{var list = db.Stu.Where(s => s.IsOk).ToList();
    IWorkbook wb = new XSSFWorkbook();ISheet sheet = wb.CreateSheet(<span class="hljs-string">"Sheet1"</span>);<span class="hljs-comment">// 第一行 标题</span>IRow row = sheet.CreateRow(<span class="hljs-number">0</span>);row.CreateCell(<span class="hljs-number">0</span>).SetCellValue(<span class="hljs-string">"姓名"</span>);row.CreateCell(<span class="hljs-number">1</span>).SetCellValue(<span class="hljs-string">"性别"</span>);row.CreateCell(<span class="hljs-number">2</span>).SetCellValue(<span class="hljs-string">"手机号码"</span>);row.CreateCell(<span class="hljs-number">3</span>).SetCellValue(<span class="hljs-string">"学院"</span>);row.CreateCell(<span class="hljs-number">4</span>).SetCellValue(<span class="hljs-string">"入学日期"</span>);row.CreateCell(<span class="hljs-number">5</span>).SetCellValue(<span class="hljs-string">"综合积分"</span>);row.CreateCell(<span class="hljs-number">6</span>).SetCellValue(<span class="hljs-string">"照片"</span>);row.CreateCell(<span class="hljs-number">7</span>).SetCellValue(<span class="hljs-string">"状态"</span>);<span class="hljs-comment">// 第二行 写数据</span><span class="hljs-type">int</span> i = <span class="hljs-number">1</span>;foreach (var item in <span class="hljs-built_in">list</span>){row = sheet.CreateRow(i);row.CreateCell(<span class="hljs-number">0</span>).SetCellValue(item.StuName);row.CreateCell(<span class="hljs-number">1</span>).SetCellValue(item.Sex == <span class="hljs-number">0</span> ? <span class="hljs-string">"男"</span> : <span class="hljs-string">"女"</span>);row.CreateCell(<span class="hljs-number">2</span>).SetCellValue(<span class="hljs-type">double</span>.Parse(item.Phone));row.CreateCell(<span class="hljs-number">3</span>).SetCellValue(item.CId);<span class="hljs-comment">// 日期格式的导出</span>ICell cell = row.CreateCell(<span class="hljs-number">4</span>);ICellStyle style = wb.CreateCellStyle();IDataFormat format = wb.CreateDataFormat();style.DataFormat = format.GetFormat(<span class="hljs-string">"yyyy-MM-dd"</span>);cell.CellStyle = style;cell.SetCellValue(DateTime.Parse(item.InDate.ToString(<span class="hljs-string">"yyyy-MM-dd"</span>)));row.CreateCell(<span class="hljs-number">5</span>).SetCellValue((<span class="hljs-type">double</span>)item.JF);row.CreateCell(<span class="hljs-number">6</span>).SetCellValue(item.Pic);row.CreateCell(<span class="hljs-number">7</span>).SetCellValue(item.State);i++;}<span class="hljs-comment">// 写 WorkBook信息到 内存流中</span>byte[] buffer = null;using (MemoryStream ms = new MemoryStream()){wb.Write(ms);buffer = ms.ToArray();}<span class="hljs-comment">// .xlsx文件对应的Mime信息</span>var mime = new FileExtensionContentTypeProvider().Mappings[<span class="hljs-string">".xlsx"</span>];<span class="hljs-keyword">return</span> File(buffer, mime, <span class="hljs-string">"学生信息.xlsx"</span>);}
catch (Exception)
{throw;
}

}

使用模板

/// <summary>
/// 导出Excel（使用模板）
/// </summary>
/// <returns></returns>
[HttpGet]
public IActionResult ExportExcelByTemplate()
{try{IWorkbook wb = null;var template = Directory.GetCurrentDirectory() + @"\wwwroot\Template\template.xlsx";// 按模板内容创建 IWorkbookusing(FileStream fs = new FileStream(template, FileMode.OpenOrCreate)){wb = new XSSFWorkbook(fs);}
    var <span class="hljs-built_in">list</span> = db.Stu.Where(s =&gt; s.IsOk).ToList();ISheet sheet = wb.GetSheetAt(<span class="hljs-number">0</span>);<span class="hljs-type">int</span> i = <span class="hljs-number">1</span>;IRow row = null;foreach (var item in <span class="hljs-built_in">list</span>){row = sheet.CreateRow(i);row.CreateCell(<span class="hljs-number">0</span>).SetCellValue(item.StuName);row.CreateCell(<span class="hljs-number">1</span>).SetCellValue(item.Sex == <span class="hljs-number">0</span> ? <span class="hljs-string">"男"</span> : <span class="hljs-string">"女"</span>);row.CreateCell(<span class="hljs-number">2</span>).SetCellValue(<span class="hljs-type">double</span>.Parse(item.Phone));row.CreateCell(<span class="hljs-number">3</span>).SetCellValue(item.CId);<span class="hljs-comment">// 日期格式的导出</span>ICell cell = row.CreateCell(<span class="hljs-number">4</span>);ICellStyle style = wb.CreateCellStyle();IDataFormat format = wb.CreateDataFormat();style.DataFormat = format.GetFormat(<span class="hljs-string">"yyyy-MM-dd"</span>);cell.CellStyle = style;cell.SetCellValue(DateTime.Parse(item.InDate.ToString(<span class="hljs-string">"yyyy-MM-dd"</span>)));row.CreateCell(<span class="hljs-number">5</span>).SetCellValue((<span class="hljs-type">double</span>)item.JF);row.CreateCell(<span class="hljs-number">6</span>).SetCellValue(item.Pic);row.CreateCell(<span class="hljs-number">7</span>).SetCellValue(item.State);i++;}byte[] buffer = null;using (MemoryStream ms = new MemoryStream()){wb.Write(ms);buffer = ms.ToArray();}<span class="hljs-comment">// .xlsx文件对应的Mime信息</span>var mime = new FileExtensionContentTypeProvider().Mappings[<span class="hljs-string">".xlsx"</span>];<span class="hljs-keyword">return</span> File(buffer, mime, <span class="hljs-string">"学生信息.xlsx"</span>);}
catch (Exception)
{throw;
}

}

导出前端（调用）

使用 axios 调用

// 导出为Excel文件(.xlsx)
// 简单方法exportExce() {let url ="http://localhost:23474/api/Stu/ExportExcel?page=1&size=4&bId=0"; //可以在路径中传递参数window.location.href = url;},
// 标准方法
exportExcel() {this.axios.get("http://localhost:23474/api/Stu/ExportExcel", {responseType: "blob",}).then((res) => {var blob = new Blob([res.data], {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",});var a = document.createElement("a"); // js创建一个a标签var href = window.URL.createObjectURL(blob); // 文档流转化成Base64a.href = href;a.download = "学生数据.xlsx"; // 下载后文件名document.body.appendChild(a);a.click(); // 点击下载document.body.removeChild(a); // 下载完成移除元素window.URL.revokeObjectURL(href);});
},