求一份NPOI的ExcelHelper类
0
悬赏园豆：10
[已解决问题]
解决于 2014-12-24 08:41
最近要写C#导入导出的模块了，求一份NPOI的ExcelHelper类，谢谢
最佳答案
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 }
收获园豆：5
NpoiDataType没看懂，我先复制出来，建立个小程序测试下，谢谢了
其他回答(2)
0
你也太懒了！！！自己不会动手在博客园搜？？
http://www.cnblogs.com/yzb305070/archive/2012/08/30/2664159.html
收获园豆：5
一直没找到好用的，excel的单元格cell的内容是空的，不知道怎样读取。
@草帽船长: 你先看下别人写的，其实很简单，把细看下，一定要自己看懂了，再下手，这样以后出问题好改。至于你说的问题，估计默认从表头开始读的，不懂就调试看嘛，很简单的。
@不忘初心: 读到的单元格的内容是空的，就是有的单元格是有内容的，有的单元格是没内容的
@草帽船长: 可能是数据类型不一致啊 里面有多种类型读取方式，你再看看?
1
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 }
太懒了，提问不带这样的。
