Excel操作基本方法 - 服务器端不用安装Office工具
ExcelData
View Code
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using MyXls.SL2;
6 using org.in2bits.MyXls;
7 using System.Data;
8 using System.IO;
9 using System.Web;
10 using System.Collections;
11
12
13
14 public class ExcelData
15 {
16 public NLog.Logger log = NLog.LogManager.GetCurrentClassLogger();
17
18 public ExcelData()
19 { }
20
21 #region 下载导出文件--数据源DataTable
22 /// <summary>
23 /// 下载导出文件--数据源DataTable
24 /// </summary>
25 /// <param name="sheetName">sheet名称</param>
26 /// <param name="xlsname">文件名称</param>
27 /// <param name="table">数据集DataTable</param>
28 public void ExportExcelForWeb(string sheetName, string xlsname, DataTable table)
29 {
30
31 XlsDocument xls = new XlsDocument();
32 Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
33 try
34 {
35
36 if (table == null || table.Rows.Count == 0) { return; }
37 //XlsDocument xls = new XlsDocument();
38 //Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
39
40 //填充表头
41 foreach (DataColumn col in table.Columns)
42 {
43 sheet.Cells.Add(1, col.Ordinal + 1, col.ColumnName);
44 }
45
46 //填充内容
47 for (int i = 0; i < table.Rows.Count; i++)
48 {
49 for (int j = 0; j < table.Columns.Count; j++)
50 {
51 sheet.Cells.Add(i + 2, j + 1, table.Rows[i][j].ToString());
52 }
53 }
54
55 //保存
56 //xls.FileName = xlsname;
57 //xls.Save();
58
59 #region 客户端保存
60 MemoryStreamSave(xls, sheet, xlsname);
61
62 #endregion
63 //xls = null;
64 }
65 catch (Exception ex)
66 {
67 log.Debug(ex.Message);
68 }
69 finally
70 {
71 sheet = null;
72 xls = null;
73 }
74
75 }
76 #endregion
77
78 #region 导出Excel,指定标题,按标题导出指定字段内容
79 /// <summary>
80 /// 导出Excel,指定标题,按标题导出指定字段内容
81 /// </summary>
82 /// <param name="table">导出数据源DataTable</param>
83 /// <param name="titleList">数据标题行</param>
84 public void ExportExcelForWeb(DataTable table, Dictionary<string,string> titleList)
85 {
86
87 XlsDocument xls = new XlsDocument();
88 Worksheet sheet = xls.Workbook.Worksheets.Add("sheet1");
89 try
90 {
91
92 if (table == null || table.Rows.Count == 0) { return; }
93
94 //填充表头
95 int n =1;
96 IDictionaryEnumerator Enum = titleList.GetEnumerator();
97 while (Enum.MoveNext())
98 {
99 sheet.Cells.Add(1, n, Enum.Value);
100 log.Debug(Enum.Key + " " + Enum.Value);
101 n++;
102 }
103
104
105 //填充内容
106 for (int i = 0; i < table.Rows.Count; i++)
107 {
108 int j = 1;
109 IDictionaryEnumerator col = titleList.GetEnumerator();
110 while (col.MoveNext())
111 {
112 sheet.Cells.Add(i + 2, j, table.Rows[i][col.Key.ToString()].ToString());
113 j++;
114 }
115 //for (int j = 0; j < titleList.Count; j++)
116 //{
117 // sheet.Cells.Add(i + 2, j + 1, table.Rows[i][titleList.GetKey(j).ToString()].ToString());
118 //}
119
120
121 }
122
123 //保存
124 //xls.FileName = xlsname;
125 //xls.Save();
126
127 #region 客户端保存
128 MemoryStreamSave(xls, sheet);
129 #endregion
130 //xls = null;
131 }
132 catch (Exception ex)
133 {
134 log.Debug(ex.Message);
135 }
136 finally
137 {
138 sheet = null;
139 xls = null;
140 }
141
142 }
143 #endregion
144
145
146
147 #region 导出Excel,指定标题
148 /// <summary>
149 /// 导出Excel,指定标题
150 /// </summary>
151 /// <param name="sheetName">sheet名称</param>
152 /// <param name="xlsname">excel文件名称</param>
153 /// <param name="table">导出数据源DataTable</param>
154 /// <param name="titleList">数据标题行</param>
155 public void ExportExcelForWeb(string sheetName, string xlsname, DataTable table, Dictionary<string, string> titleList)
156 {
157
158 XlsDocument xls = new XlsDocument();
159 Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
160 try
161 {
162
163 if (table == null || table.Rows.Count == 0) { return; }
164 //XlsDocument xls = new XlsDocument();
165 //Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
166
167 //填充表头
168 foreach (DataColumn col in table.Columns)
169 {
170 string name = col.ColumnName.Trim();
171 object namestr = (object)name;
172 IDictionaryEnumerator Enum = titleList.GetEnumerator();
173 while (Enum.MoveNext())
174 {
175 if (Enum.Key.ToString().Trim() == name)
176 {
177 namestr = Enum.Value;
178 }
179 }
180 sheet.Cells.Add(1, col.Ordinal + 1, namestr);
181 }
182
183 //填充内容
184 for (int i = 0; i < table.Rows.Count; i++)
185 {
186 for (int j = 0; j < table.Columns.Count; j++)
187 {
188 sheet.Cells.Add(i + 2, j + 1, table.Rows[i][j].ToString());
189 }
190 }
191
192 //保存
193 //xls.FileName = xlsname;
194 //xls.Save();
195
196 #region 客户端保存
197 using (MemoryStream ms = new MemoryStream())
198 {
199 xls.Save(ms);
200 ms.Flush();
201 ms.Position = 0;
202 sheet = null;
203 xls = null;
204 HttpResponse response = System.Web.HttpContext.Current.Response;
205 response.Clear();
206
207 response.Charset = "UTF-8";
208 response.ContentType = "application/vnd-excel";
209 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + xlsname));
210 //System.Web.HttpContext.Current.Response.WriteFile(fi.FullName);
211 byte[] data = ms.ToArray();
212 System.Web.HttpContext.Current.Response.BinaryWrite(data);
213
214 }
215
216 #endregion
217 //xls = null;
218 }
219 catch (Exception ex)
220 {
221 log.Debug(ex.Message);
222 }
223 finally
224 {
225 sheet = null;
226 xls = null;
227 }
228
229 }
230 #endregion
231
232
233 #region DataSet生成XLS报表
234 /// <summary>
235 /// DataSet生成XLS报表
236 /// </summary>
237 /// <param name="ds">获取DataSet数据集</param>
238 /// <param name="xlsName">报表表名</param>
239 private void xlsGridview(DataSet ds, string xlsName)
240 {
241 XlsDocument xls = new XlsDocument();
242 Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称
243 try
244 {
245 int rowIndex = 1;
246 int colIndex = 0;
247
248 System.Data.DataTable table = ds.Tables[0];
249
250 Cells cells = sheet.Cells;
251 foreach (DataColumn col in table.Columns)
252 {
253 colIndex++;
254 //sheet.Cells.AddValueCell(1,colIndex,col.ColumnName);//添加XLS标题行
255 cells.Add(1, colIndex, col.ColumnName);
256 }
257
258 foreach (DataRow row in table.Rows)
259 {
260 rowIndex++;
261 colIndex = 0;
262 foreach (DataColumn col in table.Columns)
263 {
264 colIndex++;
265 //sheet.Cells.AddValueCell(rowIndex, colIndex, row[col.ColumnName].ToString());//将数据添加到xls表格里
266 Cell cell = cells.Add(rowIndex, colIndex, Convert.ToDouble(row[col.ColumnName].ToString()));//转换为数字型
267 //如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
268 cell.Font.FontFamily = FontFamilies.Roman; //字体
269 cell.Font.Bold = true; //字体为粗体
270 }
271 }
272 }
273 catch (Exception ex)
274 {
275 log.Debug(ex.Message);
276 }
277 finally
278 {
279 xls = null;
280 sheet = null;
281 }
282 }
283 #endregion
284
285 #region 生成导出数据,客户端保存
286 /// <summary>
287 /// 生成导出数据,客户端保存,默认文件名称
288 /// </summary>
289 /// <param name="xls">XlsDocument数据,</param>
290 /// <param name="sheet">Worksheet</param>
291 public void MemoryStreamSave(XlsDocument xls, Worksheet sheet)
292 {
293 using (MemoryStream ms = new MemoryStream())
294 {
295 xls.Save(ms);
296 ms.Flush();
297 ms.Position = 0;
298 sheet = null;
299 xls = null;
300 HttpResponse response = System.Web.HttpContext.Current.Response;
301 response.Clear();
302
303 response.Charset = "UTF-8";
304 response.ContentType = "application/vnd-excel";
305 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond + ".xls"));
306 //System.Web.HttpContext.Current.Response.WriteFile(fi.FullName);
307 byte[] data = ms.ToArray();
308 System.Web.HttpContext.Current.Response.BinaryWrite(data);
309
310 }
311 }
312 /// <summary>
313 /// 生成导出数据,客户端保存
314 /// </summary>
315 /// <param name="xls"></param>
316 /// <param name="sheet"></param>
317 /// <param name="fileName"></param>
318 public void MemoryStreamSave(XlsDocument xls, Worksheet sheet, string fileName)
319 {
320 using (MemoryStream ms = new MemoryStream())
321 {
322 xls.Save(ms);
323 ms.Flush();
324 ms.Position = 0;
325 sheet = null;
326 xls = null;
327 HttpResponse response = System.Web.HttpContext.Current.Response;
328 response.Clear();
329
330 response.Charset = "UTF-8";
331 response.ContentType = "application/vnd-excel";
332 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + fileName + ".xls"));
333 //System.Web.HttpContext.Current.Response.WriteFile(fi.FullName);
334 byte[] data = ms.ToArray();
335 System.Web.HttpContext.Current.Response.BinaryWrite(data);
336
337 }
338 }
339 #endregion
340
341 }
ExcelNPOI
读取导入Excel第三方NPOI，脱离Office环境，服务器端不用安装Office工具
View Code
1 using System;
2 using System.Collections.Generic;
3 using System.Data;
4 using System.IO;
5 using System.Text;
6 using System.Web;
7 using NPOI;
8 using NPOI.HPSF;
9 using NPOI.HSSF;
10 using NPOI.HSSF.UserModel;
11 using NPOI.HSSF.Util;
12 using NPOI.POIFS;
13 using NPOI.Util;
14 using NPOI.SS.UserModel;
15
16
17 public class ExcelNPOI
18 {
19 public NLog.Logger log = NLog.LogManager.GetCurrentClassLogger();
20
21 #region 导出Excel
22
23 /// <summary>
24 /// 导出Excel
25 /// </summary>
26 /// <param name="dtSource">DataTable数据源</param>
27 /// <param name="strHeaderText">标题</param>
28 /// <param name="strFileName">文件路径</param>
29 public static void Export(DataTable dtSource, string strHeaderText, string strFileName)
30 {
31 using (MemoryStream ms = Export(dtSource, strHeaderText))
32 {
33 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
34 {
35 byte[] data = ms.ToArray();
36 fs.Write(data, 0, data.Length);
37 fs.Flush();
38 }
39 }
40 }
41
42 /// <summary>
43 /// DataTable导出到Excel的MemoryStream
44 /// </summary>
45 /// <param name="dtSource">源DataTable</param>
46 /// <param name="strHeaderText">表头文本</param>
47 public static MemoryStream Export(DataTable dtSource, string strHeaderText)
48 {
49 HSSFWorkbook workbook = new HSSFWorkbook();
50 ISheet sheet = workbook.CreateSheet();
51
52 #region 右击文件 属性信息
53 {
54 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
55 dsi.Company = "";
56 workbook.DocumentSummaryInformation = dsi;
57
58 SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
59 si.Author = ""; //填加xls文件作者信息
60 si.ApplicationName = ""; //填加xls文件创建程序信息
61 si.LastAuthor = ""; //填加xls文件最后保存者信息
62 si.Comments = ""; //填加xls文件作者信息
63 si.Title = ""; //填加xls文件标题信息
64 si.Subject = "";//填加文件主题信息
65 si.CreateDateTime = DateTime.Now;
66 workbook.SummaryInformation = si;
67 }
68 #endregion
69
70 ICellStyle dateStyle = workbook.CreateCellStyle();
71 IDataFormat format = workbook.CreateDataFormat();
72 dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
73
74 //取得列宽
75 int[] arrColWidth = new int[dtSource.Columns.Count];
76 foreach (DataColumn item in dtSource.Columns)
77 {
78 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
79 }
80 for (int i = 0; i < dtSource.Rows.Count; i++)
81 {
82 for (int j = 0; j < dtSource.Columns.Count; j++)
83 {
84 int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
85 if (intTemp > arrColWidth[j])
86 {
87 arrColWidth[j] = intTemp;
88 }
89 }
90 }
91
92
93
94 int rowIndex = 0;
95
96 foreach (DataRow row in dtSource.Rows)
97 {
98 #region 新建表，填充表头，填充列头，样式
99 if (rowIndex == 65535 || rowIndex == 0)
100 {
101 if (rowIndex != 0)
102 {
103 sheet = workbook.CreateSheet();
104 }
105
106 #region 表头及样式
107 {
108 IRow headerRow = sheet.CreateRow(0);
109 headerRow.HeightInPoints = 25;
110 headerRow.CreateCell(0).SetCellValue(strHeaderText);
111
112 ICellStyle headStyle = workbook.CreateCellStyle();
113 headStyle.Alignment = HorizontalAlignment.CENTER;
114 IFont font = workbook.CreateFont();
115 font.FontHeightInPoints = 20;
116 font.Boldweight = 700;
117 headStyle.SetFont(font);
118
119 headerRow.GetCell(0).CellStyle = headStyle;
120
121 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
122
123 }
124 #endregion
125
126
127 #region 列头及样式
128 {
129 IRow headerRow = sheet.CreateRow(1);
130
131
132 ICellStyle headStyle = workbook.CreateCellStyle();
133 headStyle.Alignment = HorizontalAlignment.CENTER;
134 IFont font = workbook.CreateFont();
135 font.FontHeightInPoints = 10;
136 font.Boldweight = 700;
137 headStyle.SetFont(font);
138
139
140 foreach (DataColumn column in dtSource.Columns)
141 {
142 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
143 headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
144
145 //设置列宽
146 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
147
148 }
149 }
150 #endregion
151
152 rowIndex = 2;
153 }
154 #endregion
155
156
157 #region 填充内容
158 IRow dataRow = sheet.CreateRow(rowIndex);
159 foreach (DataColumn column in dtSource.Columns)
160 {
161 ICell newCell = dataRow.CreateCell(column.Ordinal);
162
163 string drValue = row[column].ToString();
164
165 switch (column.DataType.ToString())
166 {
167 case "System.String"://字符串类型
168 newCell.SetCellValue(drValue);
169 break;
170 case "System.DateTime"://日期类型
171 DateTime dateV;
172 DateTime.TryParse(drValue, out dateV);
173 newCell.SetCellValue(dateV);
174
175 newCell.CellStyle = dateStyle;//格式化显示
176 break;
177 case "System.Boolean"://布尔型
178 bool boolV = false;
179 bool.TryParse(drValue, out boolV);
180 newCell.SetCellValue(boolV);
181 break;
182 case "System.Int16"://整型
183 case "System.Int32":
184 case "System.Int64":
185 case "System.Byte":
186 int intV = 0;
187 int.TryParse(drValue, out intV);
188 newCell.SetCellValue(intV);
189 break;
190 case "System.Decimal"://浮点型
191 case "System.Double":
192 double doubV = 0;
193 double.TryParse(drValue, out doubV);
194 newCell.SetCellValue(doubV);
195 break;
196 case "System.DBNull"://空值处理
197 newCell.SetCellValue("");
198 break;
199 default:
200 newCell.SetCellValue("");
201 break;
202 }
203
204 }
205 #endregion
206
207 rowIndex++;
208 }
209
210
211 using (MemoryStream ms = new MemoryStream())
212 {
213 workbook.Write(ms);
214 ms.Flush();
215 ms.Position = 0;
216
217 sheet.Dispose();
218 //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
219 return ms;
220 }
221
222 }
223
224
225 /// <summary>
226 /// 用于Web导出
227 /// </summary>
228 /// <param name="dtSource">源DataTable</param>
229 /// <param name="strHeaderText">表头文本</param>
230 /// <param name="strFileName">文件名</param>
231 public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
232 {
233
234 HttpContext curContext = HttpContext.Current;
235
236 // 设置编码和附件格式
237 curContext.Response.ContentType = "application/vnd.ms-excel";
238 curContext.Response.ContentEncoding = Encoding.UTF8;
239 curContext.Response.Charset = "";
240 curContext.Response.AppendHeader("Content-Disposition",
241 "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
242
243 curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
244 curContext.Response.End();
245
246 }
247
248 #endregion
249
250 #region 导入Excel
251
252 /// <summary>读取excel
253 /// 默认第一行为标头
254 /// </summary>
255 /// <param name="strFileName">excel文档路径</param>
256 /// <returns></returns>
257 public static DataTable Import(string strFileName)
258 {
259 DataTable dt = new DataTable();
260
261 HSSFWorkbook hssfworkbook;
262 using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
263 {
264 hssfworkbook = new HSSFWorkbook(file);
265 }
266 ISheet sheet = hssfworkbook.GetSheetAt(0);
267 System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
268
269 #region 文档中使用方法
270
271
272 //for (int j = 0; j < 5; j++)
273 //{
274 // dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
275 //}
276
277 //while (rows.MoveNext())
278 //{
279 // IRow row = (HSSFRow)rows.Current;
280 // DataRow dr = dt.NewRow();
281
282 // for (int i = 0; i < row.LastCellNum; i++)
283 // {
284 // ICell cell = row.GetCell(i);
285
286
287 // if (cell == null)
288 // {
289 // dr[i] = null;
290 // }
291 // else
292 // {
293 // dr[i] = cell.ToString();
294 // }
295 // }
296 // dt.Rows.Add(dr);
297 //}
298 #endregion
299
300 IRow headerRow = sheet.GetRow(0);
301 int cellCount = headerRow.LastCellNum;
302
303 for (int j = 0; j < cellCount; j++)
304 {
305 ICell cell = headerRow.GetCell(j);
306 dt.Columns.Add(cell.ToString());
307 }
308
309 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
310 {
311 IRow row = sheet.GetRow(i);
312 DataRow dataRow = dt.NewRow();
313
314 for (int j = row.FirstCellNum; j < cellCount; j++)
315 {
316 if (row.GetCell(j) != null)
317 dataRow[j] = row.GetCell(j).ToString();
318 }
319
320 dt.Rows.Add(dataRow);
321 }
322 return dt;
323 }
324 #endregion
325 }
----------调用
Dictionary<string, string> ht = new Dictionary<string, string>();
//SortedList ht = new SortedList();
ht.Add("数据库字段", "显示字段");
Common.ExcelData exceldt = new Common.ExcelData();
exceldt.ExportExcelForWeb(dt, ht);//dt为table的数据 ！
