C#实现excel 文件导出

实现方式：可以采用模板文件导出也可以采用代码生成文件， 将文件转化为字节流，然后用File（）方法输出即可。 

Controller

1.  
   public ActionResult DownloadRiskTipExcel(string nodeId, int level, string riskSourceName, string riskStatusValue)
2.  
   {
3.  
    
4.  
   var data = riskTipsService.DownloadRiskTipExcel(nodeId, level, riskSourceName, riskStatusValue);
5.  
   var name = $"风险提示-导出-" DateTime.Now.ToString("yyyyMMddhhmm") ".xlsx";
6.  
   return File(data, "application/octet-stream", name);
7.  
   }

IService

	byte[] DownloadRiskTipExcel(string nodeId, int level, string riskSourceName, string riskStatusValue);

Service

1.  
   public byte[] DownloadRiskTipExcel(string nodeId, int level, string riskSourceName, string riskStatusValue)
2.  
   {
3.  
   var dataList = GetRiskPointPageList(nodeId, level, riskSourceName, riskStatusValue);
4.  
   var host = GetService<IHostingEnvironment>();
5.  
    
6.  
   XSSFWorkbook workbook;
7.  
   string templateFilePath = templateFilePath = Path.GetFullPath(host.WebRootPath "/Template/RiskTipTemplate.xlsx");
8.  
    
9.  
   byte[] datas;
10.  
     
11.  
    using (var fs = System.IO.File.OpenRead(templateFilePath))
12.  
    {
13.  
     
14.  
    workbook = new XSSFWorkbook(fs);
15.  
    int sheetIndex = 0;
16.  
     
17.  
    ICellStyle cellStyle = workbook.CreateCellStyle();
18.  
    cellStyle.WrapText = true;
19.  
    cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
20.  
    cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
21.  
    cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
22.  
    cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
23.  
    cellStyle.Alignment = HorizontalAlignment.Center;
24.  
    cellStyle.VerticalAlignment = VerticalAlignment.Center;
25.  
     
26.  
    ICellStyle borderCellStyle = workbook.CreateCellStyle();
27.  
    borderCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
28.  
    borderCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
29.  
    borderCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
30.  
    borderCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
31.  
     
32.  
    IFont font = workbook.CreateFont();
33.  
    font.Boldweight = (short)FontBoldWeight.Bold;
34.  
    borderCellStyle.SetFont(font);
35.  
    borderCellStyle.Alignment = HorizontalAlignment.Center;
36.  
    borderCellStyle.VerticalAlignment = VerticalAlignment.Center;
37.  
     
38.  
    XSSFSheet sheet = workbook.GetSheetAt(0) as XSSFSheet;
39.  
    int rowIndex =1;
40.  
    foreach (var item in dataList)
41.  
    {
42.  
    IRow dataRow = sheet.GetRow(rowIndex);
43.  
    if (dataRow == null)
44.  
    {
45.  
    dataRow = sheet.CreateRow(rowIndex);
46.  
    }
47.  
    cellStyle.WrapText = false;
48.  
    SetCellValue(workbook, dataRow, cellStyle, 0, item.SegName);
49.  
    SetCellValue(workbook, dataRow, cellStyle, 1, item.SiteName);
50.  
    SetCellValue(workbook, dataRow, cellStyle, 2, item.RiskPointName);
51.  
    SetCellValue(workbook, dataRow, cellStyle, 3, item.RiskDesignLevelDesc);
52.  
    SetCellValue(workbook, dataRow, cellStyle, 4, item.ConstructionLevelDesc);
53.  
    SetCellValue(workbook, dataRow, cellStyle, 5, item.RiskTypeName);
54.  
    SetCellValue(workbook, dataRow, cellStyle, 6, item.RiskName);
55.  
    SetCellValue(workbook, dataRow, cellStyle, 7, item.RiskLevelDesc);
56.  
    SetCellValue(workbook, dataRow, cellStyle, 8, item.ProcessingStatusDesc);
57.  
    rowIndex ;
58.  
    }
59.  
     
60.  
     
61.  
    workbook.SetActiveSheet(0);
62.  
    }
63.  
    using (var ms = new MemoryStream())
64.  
    {
65.  
    workbook.Write(ms);
66.  
    datas = ms.ToArray();
67.  
    }
68.  
    return datas;
69.  
    }
70.  
     
71.  
    /// <summary>
72.  
    /// 单元格赋值
73.  
    /// </summary>
74.  
    private void SetCellValue(XSSFWorkbook workbook, IRow row, ICellStyle cellStyle, int columIndex, string value)
75.  
    {
76.  
    var cell = row.CreateCell(columIndex);
77.  
     
78.  
    cell.CellStyle = cellStyle;
79.  
     
80.  
    cell.SetCellValue(value);
81.  
    }

这篇好文章是转载于：学新通技术网