Excel生成操作类：
1 代码 2 using System; 3 using System.Collections.Generic; 4 using System.Text; 5 using System.IO; 6 using NPOI; 7 using NPOI.HPSF; 8 using NPOI.HSSF; 9 using NPOI.HSSF.UserModel; 10 using System.Data; 11 12 namespace StarTech.NPOI 13 { 14 /// <summary> 15 /// Excel生成操作类 16 /// </summary> 17 public class NPOIHelper 18 { 19 /// <summary> 20 /// 导出列名 21 /// </summary> 22 public static System.Collections.SortedList ListColumnsName; 23 /// <summary> 24 /// 导出Excel 25 /// </summary> 26 /// <param name="dgv"></param> 27 /// <param name="filePath"></param> 28 public static void ExportExcel(DataTable dtSource, string filePath) 29 { 30 if (ListColumnsName == null || ListColumnsName.Count == 0) 31 throw (new Exception("请对ListColumnsName设置要导出的列明！")); 32 33 HSSFWorkbook excelWorkbook = CreateExcelFile(); 34 InsertRow(dtSource, excelWorkbook); 35 SaveExcelFile(excelWorkbook, filePath); 36 } 37 /// <summary> 38 /// 导出Excel 39 /// </summary> 40 /// <param name="dgv"></param> 41 /// <param name="filePath"></param> 42 public static void ExportExcel(DataTable dtSource, Stream excelStream) 43 { 44 if (ListColumnsName == null || ListColumnsName.Count == 0) 45 throw (new Exception("请对ListColumnsName设置要导出的列明！")); 46 47 HSSFWorkbook excelWorkbook = CreateExcelFile(); 48 InsertRow(dtSource, excelWorkbook); 49 SaveExcelFile(excelWorkbook, excelStream); 50 } 51 /// <summary> 52 /// 保存Excel文件 53 /// </summary> 54 /// <param name="excelWorkBook"></param> 55 /// <param name="filePath"></param> 56 protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, string filePath) 57 { 58 FileStream file = null; 59 try 60 { 61 file = new FileStream(filePath, FileMode.Create); 62 excelWorkBook.Write(file); 63 } 64 finally 65 { 66 if (file != null) 67 { 68 file.Close(); 69 } 70 } 71 } 72 /// <summary> 73 /// 保存Excel文件 74 /// </summary> 75 /// <param name="excelWorkBook"></param> 76 /// <param name="filePath"></param> 77 protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, Stream excelStream) 78 { 79 try 80 { 81 excelWorkBook.Write(excelStream); 82 } 83 finally 84 { 85 86 } 87 } 88 /// <summary> 89 /// 创建Excel文件 90 /// </summary> 91 /// <param name="filePath"></param> 92 protected static HSSFWorkbook CreateExcelFile() 93 { 94 HSSFWorkbook hssfworkbook = new HSSFWorkbook(); 95 return hssfworkbook; 96 } 97 /// <summary> 98 /// 创建excel表头 99 /// </summary> 100 /// <param name="dgv"></param> 101 /// <param name="excelSheet"></param> 102 protected static void CreateHeader(HSSFSheet excelSheet) 103 { 104 int cellIndex = 0; 105 //循环导出列 106 foreach (System.Collections.DictionaryEntry de in ListColumnsName) 107 { 108 HSSFRow newRow = excelSheet.CreateRow(0); 109 HSSFCell newCell = newRow.CreateCell(cellIndex); 110 newCell.SetCellValue(de.Value.ToString()); 111 cellIndex++; 112 } 113 } 114 /// <summary> 115 /// 插入数据行 116 /// </summary> 117 protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook) 118 { 119 int rowCount = 0; 120 int sheetCount = 1; 121 HSSFSheet newsheet = null; 122 123 //循环数据源导出数据集 124 newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount); 125 CreateHeader(newsheet); 126 foreach (DataRow dr in dtSource.Rows) 127 { 128 rowCount++; 129 //超出10000条数据 创建新的工作簿 130 if (rowCount == 10000) 131 { 132 rowCount = 1; 133 sheetCount++; 134 newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount); 135 CreateHeader(newsheet); 136 } 137 138 HSSFRow newRow = newsheet.CreateRow(rowCount); 139 InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook); 140 } 141 } 142 /// <summary> 143 /// 导出数据行 144 /// </summary> 145 /// <param name="dtSource"></param> 146 /// <param name="drSource"></param> 147 /// <param name="currentExcelRow"></param> 148 /// <param name="excelSheet"></param> 149 /// <param name="excelWorkBook"></param> 150 protected static void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook) 151 { 152 for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++) 153 { 154 //列名称 155 string columnsName = ListColumnsName.GetKey(cellIndex).ToString(); 156 HSSFCell newCell = null; 157 System.Type rowType = drSource[columnsName].GetType(); 158 string drValue = drSource[columnsName].ToString().Trim(); 159 switch (rowType.ToString()) 160 { 161 case "System.String"://字符串类型 162 drValue = drValue.Replace("&", "&"); 163 drValue = drValue.Replace(">", ">"); 164 drValue = drValue.Replace("<", "<"); 165 newCell = currentExcelRow.CreateCell(cellIndex); 166 newCell.SetCellValue(drValue); 167 break; 168 case "System.DateTime"://日期类型 169 DateTime dateV; 170 DateTime.TryParse(drValue, out dateV); 171 newCell = currentExcelRow.CreateCell(cellIndex); 172 newCell.SetCellValue(dateV); 173 174 //格式化显示 175 HSSFCellStyle cellStyle = excelWorkBook.CreateCellStyle(); 176 HSSFDataFormat format = excelWorkBook.CreateDataFormat(); 177 cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss"); 178 newCell.CellStyle = cellStyle; 179 180 break; 181 case "System.Boolean"://布尔型 182 bool boolV = false; 183 bool.TryParse(drValue, out boolV); 184 newCell = currentExcelRow.CreateCell(cellIndex); 185 newCell.SetCellValue(boolV); 186 break; 187 case "System.Int16"://整型 188 case "System.Int32": 189 case "System.Int64": 190 case "System.Byte": 191 int intV = 0; 192 int.TryParse(drValue, out intV); 193 newCell = currentExcelRow.CreateCell(cellIndex); 194 newCell.SetCellValue(intV.ToString()); 195 break; 196 case "System.Decimal"://浮点型 197 case "System.Double": 198 double doubV = 0; 199 double.TryParse(drValue, out doubV); 200 newCell = currentExcelRow.CreateCell(cellIndex); 201 newCell.SetCellValue(doubV); 202 break; 203 case "System.DBNull"://空值处理 204 newCell = currentExcelRow.CreateCell(cellIndex); 205 newCell.SetCellValue(""); 206 break; 207 default: 208 throw (new Exception(rowType.ToString() + "：类型数据无法处理!")); 209 } 210 } 211 } 212 } 213 //排序实现接口 不进行排序 根据添加顺序导出 214 public class NoSort : System.Collections.IComparer 215 { 216 public int Compare(object x, object y) 217 { 218 return -1; 219 } 220 } 221 }
调用方法：
1 代码 2 //导出数据列 实现根据添加顺序导出列 3 StarTech.NPOI.NPOIHelper.ListColumnsName = new SortedList(new StarTech.NPOI.NoSort()); 4 StarTech.NPOI.NPOIHelper.ListColumnsName.Add("MemberName", "姓名"); 5 StarTech.NPOI.NPOIHelper.ListColumnsName.Add("username", "账号"); 6 StarTech.NPOI.NPOIHelper.ListColumnsName.Add("starttime", "登陆时间"); 7 StarTech.NPOI.NPOIHelper.ListColumnsName.Add("lasttime", "在线到期时间"); 8 StarTech.NPOI.NPOIHelper.ListColumnsName.Add("state", "状态"); 9 Response.Clear(); 10 Response.BufferOutput = false; 11 Response.ContentEncoding = System.Text.Encoding.UTF8; 12 string filename = HttpUtility.UrlEncode(DateTime.Now.ToString("在线用户yyyyMMdd")); 13 Response.AddHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); 14 Response.ContentType = "application/ms-excel"; 15 StarTech.NPOI.NPOIHelper.ExportExcel(dtSource, Response.OutputStream); 16 Response.Close();