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 }
Excel生成类调用方法：
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();
调用Helper