C# 关于导出的Excel设置批注 设置格式为int类型 设置字体

将数据导出，导出Excel有时候需要一些条件，比如：1.需要给某一列的表头加批注；2：需要设置某一列为int格式

1.给某一列的表头加批注

1 　　　　　　　　　 List<string> texts = new List<string>();
2                 List<int> ids = new List<int>();
3                 texts.Add("这是一个批注");
4                 ids.Add(19);

上述为给第19列的表头设置批注：“这是一个批注”

2.给某列的格式设置为int类型

1                table.Columns[16].DataType = typeof(int);
2                table.Columns[18].DataType = typeof(double);             

3.导出上述指定格式的数据

 1 ExcelHelper.ExportToExcel(table, "会议调研统计导出-" + DateTime.Now.ToString("yyyyMMddHHmmss"), texts, ids); 

 1 public static void ExportToExcel(DataTable dt, string fileName, List<string> IComments, List<int> Cells)
 2         {
 3             try
 4             {
 5                 var ds = new DataSet();
 6                 ds.Tables.Add(dt);
 7                 ExportToExcel(ds, fileName, IComments,Cells);
 8             }
 9             catch (Exception ex)
10             {
11                 throw ex;
12             }
13         }

 1 public static void ExportToExcel(DataSet ds, string fileName,List<string> IComments,List<int> Cells)
 2         {
 3             IWorkbook wookBook = new XSSFWorkbook();
 4 
 5             ICellStyle headerStyle = wookBook.CreateCellStyle();
 6             var headerFont = wookBook.CreateFont();
 7             headerFont.FontHeightInPoints = 12;
 8             headerFont.FontName = "黑体";
 9             headerFont.Color = HSSFColor.BlueGrey.Index;
10             headerStyle.SetFont(headerFont);
11 
12             ICellStyle cellStyle = wookBook.CreateCellStyle();
13             //cellStyle.WrapText = true;//自动换行
14             cellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直对齐  
15 
16             foreach (DataTable dt in ds.Tables)
17             {
18                 ISheet sheet = wookBook.CreateSheet(dt.TableName);
19                 IRow sheetColumn = sheet.CreateRow(0);
20 
21                 //写入列头
22                 for (int j = 0; j < dt.Columns.Count; j++)
23                 {
24                     var cell = sheetColumn.CreateCell(j);
25                     cell.SetCellValue(dt.Columns[j].ColumnName);
26                     cell.CellStyle = headerStyle;
27 
28                     sheet.AutoSizeColumn(j);//表格列宽自动调整,只能支持英文和数字
29                 }
30                 //写入行
31                 for (int i = 0; i < dt.Rows.Count; i++)
32                 {
33                     IRow row = sheet.CreateRow(i + 1);
34                     for (int j = 0; j < dt.Columns.Count; j++)
35                     {
36                         string gbStr = Encoding.GetEncoding("UTF-8").GetString(Encoding.UTF8.GetBytes(dt.Rows[i][j].ToString()));
37                         var cell = row.CreateCell(j);
38                         cell.CellStyle = cellStyle;
39 
40                         try
41                         {
42                             var dataType = dt.Columns[j].DataType;
43                             var tc = Type.GetTypeCode(dataType);
44                             if (tc == TypeCode.Int16 || tc == TypeCode.UInt16 || tc == TypeCode.Int32 || tc == TypeCode.UInt32 ||
45                                 tc == TypeCode.Int64 || tc == TypeCode.UInt64 || tc == TypeCode.Single || tc == TypeCode.Double || tc == TypeCode.Decimal)
46                             {
47                                 cell.SetCellType(CellType.Numeric);
48                                 double d;
49                                 double.TryParse(gbStr, out d);
50                                 cell.SetCellValue(d);
51                             }
52                             else
53                             {
54                                 cell.SetCellValue(gbStr);
55                             }
56                         }
57                         catch (Exception)
58                         {
59                         }
60                     }
61                 }
62             }
63             NPOI.SS.UserModel.ISheet newsheet = wookBook.GetSheetAt(0);
64             NPOI.SS.UserModel.IDrawing drawing = newsheet.CreateDrawingPatriarch();
65             int a = 0;
66             foreach (var item in IComments)
67             {
68                 NPOI.SS.UserModel.IComment comment1 = drawing.CreateCellComment(new NPOI.XSSF.UserModel.XSSFClientAnchor(0,0,0,0,0,0,0,0));
69                 comment1.Author = "";
70                 comment1.String = new NPOI.XSSF.UserModel.XSSFRichTextString(item);
71                 NPOI.SS.UserModel.IRow row = newsheet.GetRow(0);
72                 row.GetCell(Cells[0]).CellComment = comment1;
73                 a++;
74             }
75             var ms = new MemoryStream();
76             wookBook.Write(ms);
77 
78             //这里判断使用的浏览器是否为Firefox，Firefox导出文件时不需要对文件名显示编码，编码后文件名会乱码
79             //但是IE和Google需要编码才能保持文件名正常
80             if (HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -1 ||
81                 HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Safari") != -1)
82             {
83                 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
84             }
85             else
86             {
87                 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xlsx");
88             }
89 
90             HttpContext.Current.Response.ContentType = "application/OCTET-STREAM;charset=UTF-8";
91             HttpContext.Current.Response.BinaryWrite(ms.ToArray());
92             HttpContext.Current.Response.End();
93             ms.Flush();
94             ms.Position = 0;
95             ms.Close();
96             ms = null;
97         }