首页 新闻 搜索 专区 学院

求一份NPOI的ExcelHelper类

0
悬赏园豆:10 [已解决问题] 解决于 2014-12-24 08:41

最近要写C#导入导出的模块了,求一份NPOI的ExcelHelper类,谢谢

秋刀鱼No1的主页 秋刀鱼No1 | 初学一级 | 园豆:31
提问于:2014-12-23 15:14
< >
分享
最佳答案
0
  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Web;
  5 using System;
  6 using System.Collections.Generic;
  7 using System.Text;
  8 using System.IO;
  9 using NPOI.HSSF.UserModel;
 10 using System.Data;
 11 using System.Collections;
 12 using System.Drawing;
 13 using NPOI.HSSF.Util;
 14 using NPOI.SS.UserModel;
 15 using NPOI.HPSF;
 16 using NPOI.XSSF.UserModel;
 17 namespace E3S_V.Common
 18 {
 19     /// <summary>
 20     /// Excel文件到DataSet的转换类
 21     /// </summary>
 22     public class NpoiHelper
 23     {
 24         #region 读取Excel文件内容转换为DataSet
 25         /// <summary>
 26         /// 读取Excel文件内容转换为DataSet,列名依次为 "c0"……c[columnlength-1]
 27         /// </summary>
 28         /// <param name="FileName">文件绝对路径</param>
 29         /// <param name="startRow">数据开始行数(1为第一行)</param>
 30         /// <param name="ColumnDataType">每列的数据类型</param>
 31         /// <returns></returns>
 32         public DataSet ReadExcel(string FileName, int startRow, params NpoiDataType[] ColumnDataType)
 33         {
 34             int ertime = 0;
 35             int intime = 0;
 36             DataSet ds = new DataSet("ds");
 37             DataTable dt = new DataTable("dt");
 38             DataRow dr;
 39             StringBuilder sb = new StringBuilder();
 40             using (FileStream stream = new FileStream(@FileName, FileMode.Open, FileAccess.Read))
 41             {
 42                 IWorkbook workbook = WorkbookFactory.Create(stream);//使用接口,自动识别excel2003/2007格式
 43                 ISheet sheet = workbook.GetSheetAt(0);//得到里面第一个sheet
 44                 int j;
 45                 IRow row;
 46                 #region ColumnDataType赋值
 47                 if (ColumnDataType.Length <= 0)
 48                 {
 49                     row = sheet.GetRow(startRow - 1);//得到第i行
 50                     ColumnDataType = new NpoiDataType[row.LastCellNum];
 51                     for (int i = 0; i < row.LastCellNum; i++)
 52                     {
 53                         ICell hs = row.GetCell(i);
 54                         ColumnDataType[i] = GetCellDataType(hs);
 55                     }
 56                 }
 57                 #endregion
 58                 for (j = 0; j < ColumnDataType.Length; j++)
 59                 {
 60                     Type tp = GetDataTableType(ColumnDataType[j]);
 61                     dt.Columns.Add("c" + j, tp);
 62                 }
 63                 for (int i = startRow - 1; i <= sheet.PhysicalNumberOfRows; i++)
 64                 {
 65                     row = sheet.GetRow(i);//得到第i行
 66                     if (row == null) continue;
 67                     try
 68                     {
 69                         dr = dt.NewRow();
 70 
 71                         for (j = 0; j < ColumnDataType.Length; j++)
 72                         {
 73                             dr["c" + j] = GetCellData(ColumnDataType[j], row, j);
 74                         }
 75                         dt.Rows.Add(dr);
 76                         intime++;
 77                     }
 78                     catch (Exception er)
 79                     {
 80                         ertime++;
 81                         sb.Append(string.Format("第{0}行出错:{1}\r\n", i + 1, er.Message));
 82                         continue;
 83                     }
 84                 }
 85                 ds.Tables.Add(dt);
 86             }
 87             if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
 88             return ds;
 89         }
 90         #endregion
 91         Color LevelOneColor = Color.Green;
 92         Color LevelTwoColor = Color.FromArgb(201, 217, 243);
 93         Color LevelThreeColor = Color.FromArgb(231, 238, 248);
 94         Color LevelFourColor = Color.FromArgb(232, 230, 231);
 95         Color LevelFiveColor = Color.FromArgb(250, 252, 213);
 96 
 97         #region 从DataSet导出到MemoryStream流2003
 98         /// <summary>
 99         /// 从DataSet导出到MemoryStream流2003
100         /// </summary>
101         /// <param name="SaveFileName">文件保存路径</param>
102         /// <param name="SheetName">Excel文件中的Sheet名称</param>
103         /// <param name="ds">存储数据的DataSet</param>
104         /// <param name="startRow">从哪一行开始写入,从0开始</param>
105         /// <param name="datatypes">DataSet中的各列对应的数据类型</param>
106         public bool CreateExcel2003(string SaveFileName, string SheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes)
107         {
108             try
109             {
110                 if (startRow < 0) startRow = 0;
111                 HSSFWorkbook wb = new HSSFWorkbook();
112                 wb = new HSSFWorkbook();
113                 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
114                 dsi.Company = "pkm";
115                 SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
116                 si.Title =
117                 si.Subject = "automatic genereted document";
118                 si.Author = "pkm";
119                 wb.DocumentSummaryInformation = dsi;
120                 wb.SummaryInformation = si;
121                 ISheet sheet = wb.CreateSheet(SheetName);
122                 //sheet.SetColumnWidth(0, 50 * 256);
123                 //sheet.SetColumnWidth(1, 100 * 256);
124                 IRow row;
125                 ICell cell;
126                 DataRow dr;
127                 int j;
128                 int maxLength = 0;
129                 int curLength = 0;
130                 object columnValue;
131                 DataTable dt = ds.Tables[0];
132                 if (datatypes.Length < dt.Columns.Count)
133                 {
134                     datatypes = new NpoiDataType[dt.Columns.Count];
135                     for (int i = 0; i < dt.Columns.Count; i++)
136                     {
137                         string dtcolumntype = dt.Columns[i].DataType.Name.ToLower();
138                         switch (dtcolumntype)
139                         {
140                             case "string": datatypes[i] = NpoiDataType.String;
141                                 break;
142                             case "datetime": datatypes[i] = NpoiDataType.Datetime;
143                                 break;
144                             case "boolean": datatypes[i] = NpoiDataType.Bool;
145                                 break;
146                             case "double": datatypes[i] = NpoiDataType.Numeric;
147                                 break;
148                             default: datatypes[i] = NpoiDataType.String;
149                                 break;
150                         }
151                     }
152                 }
153 
154                 #region 创建表头
155                 row = sheet.CreateRow(0);//创建第i行
156                 ICellStyle style1 = wb.CreateCellStyle();//样式
157                 IFont font1 = wb.CreateFont();//字体
158 
159                 font1.Color = HSSFColor.White.Index;//字体颜色
160                 font1.Boldweight = (short)FontBoldWeight.Bold;//字体加粗样式
161                 //style1.FillBackgroundColor = HSSFColor.WHITE.index;//GetXLColour(wb, LevelOneColor);// 设置图案色
162                 style1.FillForegroundColor = HSSFColor.Green.Index;//GetXLColour(wb, LevelOneColor);// 设置背景色
163                 style1.FillPattern = FillPattern.SolidForeground;
164                 style1.SetFont(font1);//样式里的字体设置具体的字体样式
165                 style1.Alignment = HorizontalAlignment.Center;//文字水平对齐方式
166                 style1.VerticalAlignment = VerticalAlignment.Center;//文字垂直对齐方式
167                 row.HeightInPoints = 25;
168                 for (j = 0; j < dt.Columns.Count; j++)
169                 {
170                     columnValue = dt.Columns[j].ColumnName;
171                     curLength = Encoding.Default.GetByteCount(columnValue.ToString());
172                     maxLength = (maxLength < curLength ? curLength : maxLength);
173                     int colounwidth = 256 * maxLength;
174                     sheet.SetColumnWidth(j, colounwidth);
175                     try
176                     {
177                         cell = row.CreateCell(j);//创建第0行的第j列
178                         cell.CellStyle = style1;//单元格式设置样式
179 
180                         try
181                         {
182                             cell.SetCellType(CellType.String);
183                             cell.SetCellValue(columnValue.ToString());
184                         }
185                         catch { }
186 
187                     }
188                     catch
189                     {
190                         continue;
191                     }
192                 }
193                 #endregion
194 
195                 #region 创建每一行
196                 for (int i = startRow; i < ds.Tables[0].Rows.Count; i++)
197                 {
198                     dr = ds.Tables[0].Rows[i];
199                     row = sheet.CreateRow(i + 1);//创建第i行
200                     for (j = 0; j < dt.Columns.Count; j++)
201                     {
202                         columnValue = dr[j];
203                         curLength = Encoding.Default.GetByteCount(columnValue.ToString());
204                         maxLength = (maxLength < curLength ? curLength : maxLength);
205                         int colounwidth = 256 * maxLength;
206                         sheet.SetColumnWidth(j, colounwidth);
207                         try
208                         {
209                             cell = row.CreateCell(j);//创建第i行的第j列
210                             #region 插入第j列的数据
211                             try
212                             {
213                                 NpoiDataType dtype = datatypes[j];
214                                 switch (dtype)
215                                 {
216                                     case NpoiDataType.String:
217                                         {
218                                             cell.SetCellType(CellType.String);
219                                             cell.SetCellValue(columnValue.ToString());
220                                         } break;
221                                     case NpoiDataType.Datetime:
222                                         {
223                                             cell.SetCellType(CellType.String);
224                                             cell.SetCellValue(columnValue.ToString());
225                                         } break;
226                                     case NpoiDataType.Numeric:
227                                         {
228                                             cell.SetCellType(CellType.Numeric);
229                                             cell.SetCellValue(Convert.ToDouble(columnValue));
230                                         } break;
231                                     case NpoiDataType.Bool:
232                                         {
233                                             cell.SetCellType(CellType.Boolean);
234                                             cell.SetCellValue(Convert.ToBoolean(columnValue));
235                                         } break;
236                                     case NpoiDataType.Richtext:
237                                         {
238                                             cell.SetCellType(CellType.Formula);
239                                             cell.SetCellValue(columnValue.ToString());
240                                         } break;
241                                 }
242                             }
243                             catch
244                             {
245                                 cell.SetCellType(CellType.String);
246                                 cell.SetCellValue(columnValue.ToString());
247                             }
248                             #endregion
249 
250                         }
251                         catch
252                         {
253                             continue;
254                         }
255                     }
256                 }
257                 #endregion
258 
259                 //using (FileStream fs = new FileStream(@SaveFileName, FileMode.OpenOrCreate))//生成文件在服务器上
260                 //{
261                 //    wb.Write(fs);
262                 //}
263                 //string SaveFileName = "output.xls";
264                 using (FileStream fs = new FileStream(@SaveFileName, FileMode.OpenOrCreate, FileAccess.Write))//生成文件在服务器上
265                 {
266                     wb.Write(fs);
267                     Console.WriteLine("文件保存成功!" + SaveFileName);
268                 }
269 
270                 return true;
271             }
272             catch (Exception er)
273             {
274                 Console.WriteLine("文件保存成功!" + SaveFileName);
275                 return false;
276             }
277 
278         }
279         #endregion
280 
281         #region 从DataSet导出到MemoryStream流2007
282         /// <summary>
283         /// 从DataSet导出到MemoryStream流2007
284         /// </summary>
285         /// <param name="SaveFileName">文件保存路径</param>
286         /// <param name="SheetName">Excel文件中的Sheet名称</param>
287         /// <param name="ds">存储数据的DataSet</param>
288         /// <param name="startRow">从哪一行开始写入,从0开始</param>
289         /// <param name="datatypes">DataSet中的各列对应的数据类型</param>
290         public bool CreateExcel2007(string SaveFileName, string SheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes)
291         {
292             try
293             {
294                 if (startRow < 0) startRow = 0;
295                 XSSFWorkbook wb = new XSSFWorkbook();
296                 ISheet sheet = wb.CreateSheet(SheetName);
297                 IRow row;
298                 ICell cell;
299                 DataRow dr;
300                 int j;
301                 int maxLength = 0;
302                 int curLength = 0;
303                 object columnValue;
304                 DataTable dt = ds.Tables[0];
305                 if (datatypes.Length < dt.Columns.Count)
306                 {
307                     datatypes = new NpoiDataType[dt.Columns.Count];
308                     for (int i = 0; i < dt.Columns.Count; i++)
309                     {
310                         string dtcolumntype = dt.Columns[i].DataType.Name.ToLower();
311                         switch (dtcolumntype)
312                         {
313                             case "string": datatypes[i] = NpoiDataType.String;
314                                 break;
315                             case "datetime": datatypes[i] = NpoiDataType.Datetime;
316                                 break;
317                             case "boolean": datatypes[i] = NpoiDataType.Bool;
318                                 break;
319                             case "double": datatypes[i] = NpoiDataType.Numeric;
320                                 break;
321                             default: datatypes[i] = NpoiDataType.String;
322                                 break;
323                         }
324                     }
325                 }
326 
327                 #region 创建表头
328                 row = sheet.CreateRow(0);//创建第i行
329                 ICellStyle style1 = wb.CreateCellStyle();//样式
330                 IFont font1 = wb.CreateFont();//字体
331 
332                 font1.Color = HSSFColor.White.Index;//字体颜色
333                 font1.Boldweight = (short)FontBoldWeight.Bold;//字体加粗样式
334                 //style1.FillBackgroundColor = HSSFColor.WHITE.index;//GetXLColour(wb, LevelOneColor);// 设置图案色
335                 style1.FillForegroundColor = HSSFColor.Green.Index;//GetXLColour(wb, LevelOneColor);// 设置背景色
336                 style1.FillPattern = FillPattern.SolidForeground;
337                 style1.SetFont(font1);//样式里的字体设置具体的字体样式
338                 style1.Alignment = HorizontalAlignment.Center;//文字水平对齐方式
339                 style1.VerticalAlignment = VerticalAlignment.Center;//文字垂直对齐方式
340                 row.HeightInPoints = 25;
341                 for (j = 0; j < dt.Columns.Count; j++)
342                 {
343                     columnValue = dt.Columns[j].ColumnName;
344                     curLength = Encoding.Default.GetByteCount(columnValue.ToString());
345                     maxLength = (maxLength < curLength ? curLength : maxLength);
346                     int colounwidth = 256 * maxLength;
347                     sheet.SetColumnWidth(j, colounwidth);
348                     try
349                     {
350                         cell = row.CreateCell(j);//创建第0行的第j列
351                         cell.CellStyle = style1;//单元格式设置样式
352 
353                         try
354                         {
355                             //cell.SetCellType(CellType.STRING);
356                             cell.SetCellValue(columnValue.ToString());
357                         }
358                         catch { }
359 
360                     }
361                     catch
362                     {
363                         continue;
364                     }
365                 }
366                 #endregion
367 
368                 #region 创建每一行
369                 for (int i = startRow; i < ds.Tables[0].Rows.Count; i++)
370                 {
371                     dr = ds.Tables[0].Rows[i];
372                     row = sheet.CreateRow(i + 1);//创建第i行
373                     for (j = 0; j < dt.Columns.Count; j++)
374                     {
375                         columnValue = dr[j];
376                         curLength = Encoding.Default.GetByteCount(columnValue.ToString());
377                         maxLength = (maxLength < curLength ? curLength : maxLength);
378                         int colounwidth = 256 * maxLength;
379                         sheet.SetColumnWidth(j, colounwidth);
380                         try
381                         {
382                             cell = row.CreateCell(j);//创建第i行的第j列
383                             #region 插入第j列的数据
384                             try
385                             {
386                                 NpoiDataType dtype = datatypes[j];
387                                 switch (dtype)
388                                 {
389                                     case NpoiDataType.String:
390                                         {
391                                             //cell.SetCellType(CellType.STRING);
392                                             cell.SetCellValue(columnValue.ToString());
393                                         } break;
394                                     case NpoiDataType.Datetime:
395                                         {
396                                             // cell.SetCellType(CellType.STRING);
397                                             cell.SetCellValue(columnValue.ToString());
398                                         } break;
399                                     case NpoiDataType.Numeric:
400                                         {
401                                             //cell.SetCellType(CellType.NUMERIC);
402                                             cell.SetCellValue(Convert.ToDouble(columnValue));
403                                         } break;
404                                     case NpoiDataType.Bool:
405                                         {
406                                             //cell.SetCellType(CellType.BOOLEAN);
407                                             cell.SetCellValue(Convert.ToBoolean(columnValue));
408                                         } break;
409                                     case NpoiDataType.Richtext:
410                                         {
411                                             // cell.SetCellType(CellType.FORMULA);
412                                             cell.SetCellValue(columnValue.ToString());
413                                         } break;
414                                 }
415                             }
416                             catch
417                             {
418                                 //cell.SetCellType(HSSFCell.CELL_TYPE_STRING);
419                                 cell.SetCellValue(columnValue.ToString());
420                             }
421                             #endregion
422 
423                         }
424                         catch
425                         {
426                             continue;
427                         }
428                     }
429                 }
430                 #endregion
431 
432                 //using (FileStream fs = new FileStream(@SaveFileName, FileMode.OpenOrCreate))//生成文件在服务器上
433                 //{
434                 //    wb.Write(fs);
435                 //}
436                 //string SaveFileName = "output.xlsx";
437                 using (FileStream fs = new FileStream(SaveFileName, FileMode.OpenOrCreate, FileAccess.Write))//生成文件在服务器上
438                 {
439                     wb.Write(fs);
440                     Console.WriteLine("文件保存成功!" + SaveFileName);
441                 }
442                 return true;
443             }
444             catch (Exception er)
445             {
446                 Console.WriteLine("文件保存失败!" + SaveFileName);
447                 return false;
448             }
449 
450         }
451         #endregion
452 
453         private short GetXLColour(HSSFWorkbook workbook, System.Drawing.Color SystemColour)
454         {
455             short s = 0;
456             HSSFPalette XlPalette = workbook.GetCustomPalette();
457             HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);
458             if (XlColour == null)
459             {
460                 XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B);
461                 s = XlColour.Indexed;
462             }
463             else
464                 s = XlColour.Indexed;
465             return s;
466         }
467 
468         #region 读Excel-根据NpoiDataType创建的DataTable列的数据类型
469         /// <summary>
470         /// 读Excel-根据NpoiDataType创建的DataTable列的数据类型
471         /// </summary>
472         /// <param name="datatype"></param>
473         /// <returns></returns>
474         private Type GetDataTableType(NpoiDataType datatype)
475         {
476             Type tp = typeof(string);//Type.GetType("System.String")
477             switch (datatype)
478             {
479                 case NpoiDataType.Bool:
480                     tp = typeof(bool);
481                     break;
482                 case NpoiDataType.Datetime:
483                     tp = typeof(DateTime);
484                     break;
485                 case NpoiDataType.Numeric:
486                     tp = typeof(double);
487                     break;
488                 case NpoiDataType.Error:
489                     tp = typeof(string);
490                     break;
491                 case NpoiDataType.Blank:
492                     tp = typeof(string);
493                     break;
494             }
495             return tp;
496         }
497         #endregion
498 
499         #region 读Excel-得到不同数据类型单元格的数据
500         /// <summary>
501         /// 读Excel-得到不同数据类型单元格的数据
502         /// </summary>
503         /// <param name="datatype">数据类型</param>
504         /// <param name="row">数据中的一行</param>
505         /// <param name="column">哪列</param>
506         /// <returns></returns>
507         private object GetCellData(NpoiDataType datatype, IRow row, int column)
508         {
509 
510             switch (datatype)
511             {
512                 case NpoiDataType.String:
513                     try
514                     {
515                         return row.GetCell(column).DateCellValue;
516                     }
517                     catch
518                     {
519                         try
520                         {
521                             return row.GetCell(column).StringCellValue;
522                         }
523                         catch
524                         {
525                             return row.GetCell(column).NumericCellValue;
526                         }
527                     }
528                 case NpoiDataType.Bool:
529                     try { return row.GetCell(column).BooleanCellValue; }
530                     catch { return row.GetCell(column).StringCellValue; }
531                 case NpoiDataType.Datetime:
532                     try { return row.GetCell(column).DateCellValue; }
533                     catch { return row.GetCell(column).StringCellValue; }
534                 case NpoiDataType.Numeric:
535                     try { return row.GetCell(column).NumericCellValue; }
536                     catch { return row.GetCell(column).StringCellValue; }
537                 case NpoiDataType.Richtext:
538                     try { return row.GetCell(column).RichStringCellValue; }
539                     catch { return row.GetCell(column).StringCellValue; }
540                 case NpoiDataType.Error:
541                     try { return row.GetCell(column).ErrorCellValue; }
542                     catch { return row.GetCell(column).StringCellValue; }
543                 case NpoiDataType.Blank:
544                     try { return row.GetCell(column).StringCellValue; }
545                     catch { return ""; }
546                 default: return "";
547             }
548         }
549         #endregion
550 
551         #region 获取单元格数据类型
552         /// <summary>
553         /// 获取单元格数据类型
554         /// </summary>
555         /// <param name="hs"></param>
556         /// <returns></returns>
557         private NpoiDataType GetCellDataType(ICell hs)
558         {
559             NpoiDataType dtype;
560             DateTime t1;
561             string cellvalue = "";
562 
563             switch (hs.CellType)
564             {
565                 case CellType.Blank:
566                     dtype = NpoiDataType.String;
567                     cellvalue = hs.StringCellValue;
568                     break;
569                 case CellType.Boolean:
570                     dtype = NpoiDataType.Bool;
571                     break;
572                 case CellType.Numeric:
573                     dtype = NpoiDataType.Numeric;
574                     cellvalue = hs.NumericCellValue.ToString();
575                     break;
576                 case CellType.String:
577                     dtype = NpoiDataType.String;
578                     cellvalue = hs.StringCellValue;
579                     break;
580                 case CellType.Error:
581                     dtype = NpoiDataType.Error;
582                     break;
583                 case CellType.Formula:
584                 default:
585                     dtype = NpoiDataType.Datetime;
586                     break;
587             }
588             if (cellvalue != "" && DateTime.TryParse(cellvalue, out t1)) dtype = NpoiDataType.Datetime;
589             return dtype;
590         }
591         #endregion
592     }
593 
594     #region 枚举(Excel单元格数据类型)
595     /// <summary>
596     /// 枚举(Excel单元格数据类型)
597     /// </summary>
598     public enum NpoiDataType
599     {
600         /// <summary>
601         /// 字符串类型-值为1
602         /// </summary>
603         String,
604         /// <summary>
605         /// 布尔类型-值为2
606         /// </summary>
607         Bool,
608         /// <summary>
609         /// 时间类型-值为3
610         /// </summary>
611         Datetime,
612         /// <summary>
613         /// 数字类型-值为4
614         /// </summary>
615         Numeric,
616         /// <summary>
617         /// 复杂文本类型-值为5
618         /// </summary>
619         Richtext,
620         /// <summary>
621         /// 空白
622         /// </summary>
623         Blank,
624         /// <summary>
625         /// 错误
626         /// </summary>
627         Error
628     }
629     #endregion
630 
631 }
View Code
收获园豆:5
luk々man | 菜鸟二级 |园豆:207 | 2014-12-23 15:35

NpoiDataType没看懂,我先复制出来,建立个小程序测试下,谢谢了

秋刀鱼No1 | 园豆:31 (初学一级) | 2014-12-23 15:40
其他回答(2)
0

你也太懒了!!!自己不会动手在博客园搜??

http://www.cnblogs.com/yzb305070/archive/2012/08/30/2664159.html

收获园豆:5
卷王归来 | 园豆:99 (初学一级) | 2014-12-23 15:37

一直没找到好用的,excel的单元格cell的内容是空的,不知道怎样读取。

支持(1) 反对(0) 秋刀鱼No1 | 园豆:31 (初学一级) | 2014-12-23 15:41

@草帽船长: 你先看下别人写的,其实很简单,把细看下,一定要自己看懂了,再下手,这样以后出问题好改。至于你说的问题,估计默认从表头开始读的,不懂就调试看嘛,很简单的。

支持(0) 反对(0) 卷王归来 | 园豆:99 (初学一级) | 2014-12-23 15:45

@不忘初心: 读到的单元格的内容是空的,就是有的单元格是有内容的,有的单元格是没内容的

支持(0) 反对(0) 秋刀鱼No1 | 园豆:31 (初学一级) | 2014-12-23 15:46

@草帽船长: 可能是数据类型不一致啊 里面有多种类型读取方式,你再看看?

支持(0) 反对(0) 卷王归来 | 园豆:99 (初学一级) | 2014-12-23 15:51
0
  1 protected void Page_Load(object sender, EventArgs e)
  2         {
  3             CreateSheet("Excel文件", CreatTable());
  4         }
  5 
  6         /// <summary>
  7         /// 创建工作簿
  8         /// </summary>
  9         /// <param name="fileName">下载文件名</param>
 10         /// <param name="dt">数据源</param>
 11         public static void CreateSheet(string fileName, DataTable dt)
 12         {
 13             HSSFWorkbook workbook = new HSSFWorkbook();
 14             MemoryStream ms = new MemoryStream();
 15 
 16             //创建一个名称为Payment的工作表
 17             ISheet paymentSheet = workbook.CreateSheet("Payment");
 18 
 19             //数据源
 20             DataTable tbPayment = dt;
 21 
 22             //头部标题
 23             IRow paymentHeaderRow = paymentSheet.CreateRow(0);
 24 
 25             //循环添加标题
 26 
 27             foreach (DataColumn column in tbPayment.Columns)
 28             {
 29                 paymentHeaderRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
 30                 //paymentHeaderRow.Height = (short)3000;
 31             }
 32 
 33             ICellStyle style = workbook.CreateCellStyle();//样式
 34             style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREEN.index;
 35 
 36             // 内容
 37             int paymentRowIndex = 1;
 38 
 39             foreach (DataRow row in tbPayment.Rows)
 40             {
 41                 IRow newRow = paymentSheet.CreateRow(paymentRowIndex);
 42 
 43                 //循环添加列的对应内容
 44                 foreach (DataColumn column in tbPayment.Columns)
 45                 {
 46                     newRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
 47                 }
 48                 newRow.RowStyle = style;
 49                 paymentRowIndex++;
 50             }
 51 
 52             //列宽自适应,只对英文和数字有效
 53             for (int i = 0; i <= dt.Rows.Count; i++)
 54             {
 55                 paymentSheet.AutoSizeColumn(i);
 56             }
 57             //获取当前列的宽度,然后对比本列的长度,取最大值
 58             for (int columnNum = 0; columnNum <= dt.Columns.Count; columnNum++)
 59             {
 60                 int columnWidth = paymentSheet.GetColumnWidth(columnNum) / 256;
 61                 for (int rowNum = 1; rowNum <= paymentSheet.LastRowNum; rowNum++)
 62                 {
 63                     IRow currentRow;
 64                     //当前行未被使用过
 65                     if (paymentSheet.GetRow(rowNum) == null)
 66                     {
 67                         currentRow = paymentSheet.CreateRow(rowNum);
 68                     }
 69                     else
 70                     {
 71                         currentRow = paymentSheet.GetRow(rowNum);
 72                     }
 73 
 74                     if (currentRow.GetCell(columnNum) != null)
 75                     {
 76                         ICell currentCell = currentRow.GetCell(columnNum);
 77                         int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
 78                         if (columnWidth < length)
 79                         {
 80                             columnWidth = length;
 81                         }
 82                     }
 83                 }
 84                 paymentSheet.SetColumnWidth(columnNum, columnWidth * 256);
 85             }
 86 
 87             //将表内容写入流 通知浏览器下载
 88             workbook.Write(ms);
 89             System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", fileName));
 90             System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray()); //进行二进制流下在
 91 
 92             workbook = null;
 93             ms.Close();
 94             ms.Dispose();
 95         }
 96 
 97         /// <summary>
 98         /// 虚拟 DataTable内容
 99         /// </summary>
100         /// <returns></returns>
101         public static DataTable CreatTable()
102         {
103             //创建DataTable 将数据库中没有的数据放到这个DT中
104             DataTable datatable = new DataTable();
105             datatable.Columns.Add("列1", typeof(string));
106             datatable.Columns.Add("列2", typeof(string));
107             datatable.Columns.Add("列3", typeof(string));
108             //创建DatatTable 结束---------------------------
109 
110             //开始给临时datatable赋值
111             for (int i = 0; i < 10; i++)
112             {
113                 DataRow row = datatable.NewRow();
114                 row["列1"] = "列列列列列列列列列列列列列列列列列列列列列列列列列列列列";
115                 row["列2"] = "列222222222222222222222222222222222222222";
116                 row["列3"] = "列3333333322222222222211111111111111111111111113";
117                 datatable.Rows.Add(row);
118             }
119             return datatable;
120         }

太懒了,提问不带这样的。

LiGoper | 园豆:32 (初学一级) | 2014-12-23 17:05
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册