将数据导出，导出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 }