NPOIHelper
1 /* 2 * Made by Anby 3 * 2013-7-10 4 * 功能：通过NPoI组件操作Excel格式及设置 5 */ 6 7 using System; 8 using System.Collections.Generic; 9 using System.Data; 10 using System.IO; 11 using System.Linq; 12 using System.Text; 13 using NPOI.HPSF; 14 using NPOI.HSSF.UserModel; 15 using NPOI.HSSF.Util; 16 using NPOI.SS.UserModel; 17 using NPOI.SS.Util; 18 19 namespace TestApp 20 { 21 public class NPOIHelper 22 { 23 24 private HSSFWorkbook hssfworkbook = new HSSFWorkbook(); 25 26 private string xlsPath=""; 27 28 public NPOIHelper(string path) 29 { 30 xlsPath = path; 31 using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read)) 32 { 33 hssfworkbook = new HSSFWorkbook(file); 34 } 35 } 36 37 #region 属性 38 39 public string XlsPath 40 { 41 get { return xlsPath; } 42 set { xlsPath = value; } 43 } 44 45 #endregion 46 47 #region 方法 48 /// <summary> 49 /// 创建一个空的Excel文档，指定sheet名 50 /// </summary> 51 /// <param name="xlspath">excel保存路径,默认为xls后缀名</param> 52 /// <param name="sheets">sheet名称</param> 53 public void CreateEmptyExcelFile(string xlspath,params string[] sheets) 54 { 55 InitializeWorkbook(); 56 57 if (sheets.Count()>0) 58 { 59 for (int i = 0; i < sheets.Count(); i++) 60 { 61 hssfworkbook.CreateSheet(sheets[i]); 62 } 63 } 64 else 65 { 66 hssfworkbook.CreateSheet("sheet1"); 67 } 68 69 ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false; 70 ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false; 71 72 FileStream file = new FileStream(xlspath+".xls", FileMode.Create); 73 hssfworkbook.Write(file); 74 file.Close(); 75 } 76 #endregion 77 78 #region 私有方法 79 80 /// <summary> 81 /// 设置xls的信息 82 /// </summary> 83 private void InitializeWorkbook() 84 { 85 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); 86 dsi.Company = "NPOI Team"; 87 hssfworkbook.DocumentSummaryInformation = dsi; 88 89 SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); 90 si.Subject = "NPOI Example"; 91 hssfworkbook.SummaryInformation = si; 92 } 93 94 private void WriteToFile() 95 { 96 //Write the stream data of workbook to the root directory 97 FileStream file = new FileStream(xlsPath, FileMode.Create); 98 hssfworkbook.Write(file); 99 file.Close(); 100 } 101 102 #endregion 103 104 /// <summary> 105 /// 写入数据，无格式 106 /// </summary> 107 /// <param name="Sheetindex">sheet索引</param> 108 /// <param name="value"></param> 109 public void SetCellValuesInXls(int Sheetindex, int RowIndex, int CellIndex, string value) 110 { 111 InitializeWorkbook(); 112 113 ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex); 114 115 sheet1.CreateRow(RowIndex).CreateCell(CellIndex).SetCellValue(value); 116 117 WriteToFile(); 118 } 119 /// <summary> 120 /// 写入日期格式 121 /// </summary> 122 /// <param name="Sheetindex"></param> 123 /// <param name="RowIndex"></param> 124 /// <param name="CellIndex"></param> 125 /// <param name="value"></param> 126 public void SetDateCellInXls(int Sheetindex, int RowIndex, int CellIndex, string date) 127 { 128 InitializeWorkbook(); 129 130 ISheet sheet = hssfworkbook.GetSheetAt(Sheetindex); 131 // Create a row and put some cells in it. Rows are 0 based. 132 IRow row = sheet.CreateRow(RowIndex); 133 134 // Create a cell and put a date value in it. The first cell is not styled as a date. 135 ICell cell = row.CreateCell(CellIndex); 136 cell.SetCellValue(date); 137 138 ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); 139 140 cellStyle.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("yyyy年m月d日"); 141 cell.CellStyle = cellStyle; 142 143 //ICell cell2 = row.CreateCell(1); 144 //cell2.SetCellValue(new DateTime(2008, 5, 5)); 145 //ICellStyle cellStyle2 = hssfworkbook.CreateCellStyle(); 146 //IDataFormat format = hssfworkbook.CreateDataFormat(); 147 //cellStyle2.DataFormat = format.GetFormat("yyyy年m月d日"); 148 //cell2.CellStyle = cellStyle2; 149 150 //ICell cell3 = row.CreateCell(2); 151 //cell3.CellFormula = "DateValue(\"2005-11-11 11:11:11\")"; 152 //ICellStyle cellStyle3 = hssfworkbook.CreateCellStyle(); 153 //cellStyle3.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"); 154 //cell3.CellStyle = cellStyle3; 155 156 WriteToFile(); 157 } 158 /// <summary> 159 /// 增加备注 160 /// </summary> 161 /// <param name="Sheetindex"></param> 162 /// <param name="RowIndex"></param> 163 /// <param name="CellIndex"></param> 164 /// <param name="value"></param> 165 /// <param name="commentStr">备注信息</param> 166 public void SetCellCommentInXls(int Sheetindex, int RowIndex, int CellIndex, string value, string commentStr) 167 { 168 InitializeWorkbook(); 169 170 ISheet sheet = hssfworkbook.GetSheetAt(Sheetindex); 171 172 IDrawing patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); 173 174 ICell cell1 = sheet.CreateRow(RowIndex).CreateCell(CellIndex); 175 cell1.SetCellValue(new HSSFRichTextString(value)); 176 177 //anchor defines size and position of the comment in worksheet 178 IComment comment1 = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 4, 2, 6, 5)); 179 180 // set text in the comment 181 comment1.String = (new HSSFRichTextString(commentStr)); 182 183 // The first way to assign comment to a cell is via HSSFCell.SetCellComment method 184 cell1.CellComment = (comment1); 185 #region old 186 ////Create another cell in row 6 187 //ICell cell2 = sheet.CreateRow(6).CreateCell(1); 188 //cell2.SetCellValue(value); 189 //HSSFComment comment2 = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 4, 8, 6, 11)); 190 ////modify background color of the comment 191 //comment2.SetFillColor(204, 236, 255); 192 //HSSFRichTextString str = new HSSFRichTextString("Normal body temperature"); 193 ////apply custom font to the text in the comment 194 //IFont font = hssfworkbook.CreateFont(); 195 //font.FontName = ("Arial"); 196 //font.FontHeightInPoints = 10; 197 //font.Boldweight = (short)FontBoldWeight.BOLD; 198 //font.Color = HSSFColor.RED.index; 199 //str.ApplyFont(font); 200 //comment2.String = str; 201 //comment2.Visible = true; //by default comments are hidden. This one is always visible. 202 //comment2.Author = "Bill Gates"; 203 ///** 204 // * The second way to assign comment to a cell is to implicitly specify its row and column. 205 // * Note, it is possible to set row and column of a non-existing cell. 206 // * It works, the commnet is visible. 207 // */ 208 //comment2.Row = 6; 209 //comment2.Column = 1; 210 #endregion 211 WriteToFile(); 212 } 213 /// <summary> 214 /// 给表格画边框 215 /// </summary> 216 /// <param name="Sheetindex"></param> 217 /// <param name="RowIndex"></param> 218 /// <param name="CellIndex"></param> 219 public void SetBorderStyleInXls(int Sheetindex, int RowIndex, int CellIndex) 220 { 221 InitializeWorkbook(); 222 223 ISheet sheet = hssfworkbook.GetSheetAt(Sheetindex); 224 225 // Create a row and put some cells in it. Rows are 0 based. 226 IRow row = sheet.GetRow(RowIndex); 227 228 // Create a cell and put a value in it. 229 ICell cell = row.GetCell(CellIndex); 230 231 // Style the cell with borders all around. 232 ICellStyle style = hssfworkbook.CreateCellStyle(); 233 style.BorderBottom = BorderStyle.THIN; 234 style.BottomBorderColor = HSSFColor.BLACK.index; 235 style.BorderLeft = BorderStyle.DASH_DOT_DOT; 236 style.LeftBorderColor = HSSFColor.GREEN.index; 237 style.BorderRight = BorderStyle.HAIR; 238 style.RightBorderColor = HSSFColor.BLUE.index; 239 style.BorderTop = BorderStyle.MEDIUM_DASHED; 240 style.TopBorderColor = HSSFColor.ORANGE.index; 241 cell.CellStyle = style; 242 243 WriteToFile(); 244 } 245 /// <summary> 246 /// 给单元格加公式 247 /// </summary> 248 /// <param name="Sheetindex"></param> 249 /// <param name="RowIdex"></param> 250 /// <param name="CellIndex"></param> 251 /// <param name="formula">公式</param> 252 public void SetFormulaOfCellInXls(int Sheetindex, int RowIdex, int CellIndex, string formula) 253 { 254 InitializeWorkbook(); 255 256 ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex); 257 258 ICellStyle blackBorder = hssfworkbook.CreateCellStyle(); 259 blackBorder.BorderBottom = BorderStyle.THIN; 260 blackBorder.BorderLeft = BorderStyle.THIN; 261 blackBorder.BorderRight = BorderStyle.THIN; 262 blackBorder.BorderTop = BorderStyle.THIN; 263 blackBorder.BottomBorderColor = HSSFColor.BLACK.index; 264 blackBorder.LeftBorderColor = HSSFColor.BLACK.index; 265 blackBorder.RightBorderColor = HSSFColor.BLACK.index; 266 blackBorder.TopBorderColor = HSSFColor.BLACK.index; 267 268 IRow row = sheet1.GetRow(RowIdex); 269 ICell cell = row.CreateCell(CellIndex); 270 cell.CellFormula = formula; 271 272 WriteToFile(); 273 } 274 /// <summary> 275 /// 设置打印区域 276 /// </summary> 277 /// <param name="Sheetindex"></param> 278 /// <param name="Area">"A5:G20"</param> 279 public void SetPrintAreaInXls(int Sheetindex, string Area) 280 { 281 InitializeWorkbook(); 282 283 hssfworkbook.SetPrintArea(Sheetindex,Area ); 284 285 WriteToFile(); 286 } 287 /// <summary> 288 /// 设置打印格式，默认为A4纸 289 /// </summary> 290 /// <param name="Sheetindex"></param> 291 /// <param name="Area"></param> 292 public void SetPrintFormatInXls(int Sheetindex) 293 { 294 InitializeWorkbook(); 295 296 ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1"); 297 sheet1.SetMargin(MarginType.RightMargin, (double)0.5); 298 sheet1.SetMargin(MarginType.TopMargin, (double)0.6); 299 sheet1.SetMargin(MarginType.LeftMargin, (double)0.4); 300 sheet1.SetMargin(MarginType.BottomMargin, (double)0.3); 301 302 sheet1.PrintSetup.Copies = 3; 303 sheet1.PrintSetup.NoColor = true; 304 sheet1.PrintSetup.Landscape = true; 305 sheet1.PrintSetup.PaperSize = (short)PaperSize.A4; 306 307 sheet1.FitToPage = true; 308 sheet1.PrintSetup.FitHeight = 2; 309 sheet1.PrintSetup.FitWidth = 3; 310 sheet1.IsPrintGridlines = true; 311 312 WriteToFile(); 313 } 314 /// <summary> 315 /// 设置表格的宽和高 316 /// </summary> 317 /// <param name="Sheetindex"></param> 318 /// <param name="RowIdex"></param> 319 /// <param name="width"></param> 320 /// <param name="height"></param> 321 public void SetWidthAndHeightInXls(int Sheetindex, int RowIdex,int CellIndex,int width,short height) 322 { 323 InitializeWorkbook(); 324 325 ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex); 326 //set the width of columns 327 sheet1.SetColumnWidth(CellIndex, width); 328 329 //set the width of height 330 sheet1.GetRow(RowIdex).Height = height; 331 332 sheet1.DefaultRowHeightInPoints = 50; 333 334 WriteToFile(); 335 } 336 /// <summary> 337 /// 设置单元格对齐方式 338 /// </summary> 339 /// <param name="Sheetindex"></param> 340 /// <param name="RowIdex"></param> 341 /// <param name="CellIndex"></param> 342 /// <param name="Horizont">水平对齐方式：left,center,right,justify</param> 343 /// <param name="Vertical">垂直对齐方式：top,center,buttom,justify</param> 344 public void SetAlignmentInXls(int Sheetindex, int RowIdex, int CellIndex, string Horizont, string Vertical) 345 { 346 InitializeWorkbook(); 347 348 ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex); 349 350 IRow row = sheet1.GetRow(RowIdex); 351 ICellStyle style = hssfworkbook.CreateCellStyle(); 352 if (Horizont == "left") 353 { 354 style.Alignment = HorizontalAlignment.LEFT; 355 } 356 else if (Horizont == "center") 357 { 358 style.Alignment = HorizontalAlignment.CENTER; 359 } 360 else if (Horizont == "right") 361 { 362 style.Alignment = HorizontalAlignment.RIGHT; 363 } 364 else if (Horizont == "justify") 365 { 366 style.Alignment = HorizontalAlignment.JUSTIFY; 367 } 368 369 if (Vertical == "top") 370 { 371 style.VerticalAlignment = VerticalAlignment.TOP; 372 } 373 else if (Vertical == "center") 374 { 375 style.VerticalAlignment = VerticalAlignment.CENTER; 376 } 377 else if (Vertical == "buttom") 378 { 379 style.VerticalAlignment = VerticalAlignment.BOTTOM; 380 } 381 else if (Vertical=="justify") 382 { 383 style.VerticalAlignment = VerticalAlignment.JUSTIFY; 384 } 385 style.Indention = 3; 386 387 row.GetCell(CellIndex).CellStyle = style; 388 389 WriteToFile(); 390 } 391 /// <summary> 392 /// 放大缩小工作簿 根据 sub/den 进行缩放 393 /// </summary> 394 /// <param name="Sheetindex">要放大的sheet</param> 395 /// <param name="sub">比列的分子</param> 396 /// <param name="den">比列的分母</param> 397 public void ZoomSheet(int Sheetindex,int sub,int den) 398 { 399 InitializeWorkbook(); 400 401 ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex); 402 sheet1.SetZoom(sub, den); // 75 percent magnification 403 404 WriteToFile(); 405 } 406 /// <summary> 407 /// 在单元格内使用多行存储数据 408 /// </summary> 409 /// <param name="Sheetindex"></param> 410 /// <param name="RowIdex"></param> 411 /// <param name="CellIndex"></param> 412 /// <param name="rows">使用的行数</param> 413 /// <param name="value">在换行的后面加上 \n</param> 414 public void UseNewlinesInCellsInXls(int Sheetindex, int RowIdex, int CellIndex,int rows,string value) 415 { 416 InitializeWorkbook(); 417 418 ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex); 419 420 //use newlines in cell 421 IRow row1 = sheet1.GetRow(RowIdex); 422 ICell cell1 = row1.GetCell(CellIndex); 423 424 //to enable newlines you need set a cell styles with wrap=true 425 ICellStyle cs = hssfworkbook.CreateCellStyle(); 426 cs.WrapText = true; 427 cell1.CellStyle = cs; 428 429 row1.HeightInPoints = rows * sheet1.DefaultRowHeightInPoints; 430 cell1.SetCellValue(value); 431 WriteToFile(); 432 } 433 /// <summary> 434 /// 单元格使用基础公式 435 /// </summary> 436 /// <param name="Sheetindex"></param> 437 /// <param name="RowIdex"></param> 438 /// <param name="CellIndex"></param> 439 /// <param name="Formula"></param> 440 public void UseBasicFormulaInXls(int Sheetindex, int RowIdex, int CellIndex, string Formula) 441 { 442 InitializeWorkbook(); 443 ISheet s1 = hssfworkbook.GetSheetAt(Sheetindex); 444 //set A4=A2+A3,set D2=SUM(A2:C2);A5=cos(5)+sin(10) 445 s1.GetRow(RowIdex).GetCell(CellIndex).CellFormula = Formula; 446 WriteToFile(); 447 } 448 449 450 /// <summary> 451 /// 冻结行，FreezeRow为要冻结的行 452 /// </summary> 453 /// <param name="Sheetindex"></param> 454 public void SplitAndFreezePanes(int Sheetindex, int FreezeRow) 455 { 456 InitializeWorkbook(); 457 458 ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex); 459 460 // Freeze just one row 461 sheet1.CreateFreezePane(0, FreezeRow); 462 463 WriteToFile(); 464 } 465 466 467 /// <summary> 468 /// 缩放指定单元格字体 469 /// </summary> 470 /// <param name="Sheetindex"></param> 471 /// <param name="RowIdex"></param> 472 /// <param name="CellIndex"></param> 473 public void ShrinkToFitColumnInXls(int Sheetindex, int RowIdex, int CellIndex) 474 { 475 InitializeWorkbook(); 476 477 ISheet sheet = hssfworkbook.GetSheetAt(Sheetindex); 478 IRow row = sheet.GetRow(RowIdex); 479 //create cell value 480 ICell cell1 = row.GetCell(CellIndex); 481 482 ICellStyle cellstyle1 = hssfworkbook.CreateCellStyle(); 483 cellstyle1.ShrinkToFit = true; 484 WriteToFile(); 485 } 486 487 488 /// <summary> 489 /// 将字体旋转指定角度 490 /// </summary> 491 /// <param name="Sheetindex"></param> 492 /// <param name="RowIdex"></param> 493 /// <param name="CellIndex"></param> 494 /// <param name="Angle"></param> 495 public void RotateTextInXls(int Sheetindex, int RowIdex, int CellIndex,short Angle) 496 { 497 InitializeWorkbook(); 498 499 ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex); 500 501 IRow row = sheet1.GetRow(RowIdex); 502 //set the style 503 ICellStyle style = hssfworkbook.CreateCellStyle(); 504 style.Rotation = Angle; 505 row.GetCell(CellIndex).CellStyle = style; 506 507 WriteToFile(); 508 } 509 510 public void RepeatingRowsAndColumns(int Sheetindex, int RowIdex, int CellIndex) 511 { 512 InitializeWorkbook(); 513 514 ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex); 515 516 IFont boldFont = hssfworkbook.CreateFont(); 517 boldFont.FontHeightInPoints = 22; 518 boldFont.Boldweight = (short)FontBoldWeight.BOLD; 519 520 ICellStyle boldStyle = hssfworkbook.CreateCellStyle(); 521 boldStyle.SetFont(boldFont); 522 523 IRow row = sheet1.GetRow(RowIdex); 524 ICell cell = row.GetCell(CellIndex); 525 cell.CellStyle = (boldStyle); 526 527 // Set the columns to repeat from column 0 to 2 on the first sheet 528 hssfworkbook.SetRepeatingRowsAndColumns(Sheetindex, 0, 2, -1, -1); 529 530 WriteToFile(); 531 } 532 533 534 535 /// <summary> 536 /// 向单元格中写入数字格式 537 /// </summary> 538 /// <param name="Sheetindex"></param> 539 /// <param name="RowIdex"></param> 540 /// <param name="CellIndex"></param> 541 /// <param name="type">double，RMB，scentific，percent，phone，ChineseCapital，ChineseDate</param> 542 public void NumberFormatInXls(int Sheetindex, int RowIdex, int CellIndex,string type) 543 { 544 InitializeWorkbook(); 545 546 ISheet sheet = hssfworkbook.GetSheetAt(Sheetindex); 547 //increase the width of Column A 548 sheet.SetColumnWidth(0, 5000); 549 //create the format instance 550 IDataFormat format = hssfworkbook.CreateDataFormat(); 551 552 // Create a row and put some cells in it. Rows are 0 based. 553 ICell cell = sheet.GetRow(RowIdex).GetCell(CellIndex); 554 ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); 555 556 if (type=="double") 557 { 558 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); 559 } 560 else if (type == "RMB") 561 { 562 cellStyle.DataFormat = format.GetFormat("¥#,##0"); 563 } 564 else if (type == "scentific") 565 { 566 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00"); 567 } 568 else if (type == "percent") 569 { 570 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%"); 571 } 572 else if (type == "phone")//phone number format - "021-65881234" 573 { 574 cellStyle.DataFormat = format.GetFormat("000-00000000"); 575 } 576 else if (type == "ChineseCapital") //Chinese capitalized character number - 壹贰叁 元 577 { 578 cellStyle.DataFormat = format.GetFormat("[DbNum2][$-804]0 元"); 579 } 580 else if (type == "ChineseDate") 581 { 582 cellStyle.DataFormat = format.GetFormat("yyyy年m月d日"); 583 } 584 cell.CellStyle = cellStyle; 585 586 WriteToFile(); 587 } 588 589 590 /// <summary> 591 /// 将一个单元格赋予两个表格的乘积 592 /// </summary> 593 /// <param name="Sheetindex"></param> 594 /// <param name="RowIdex">要赋值的单元格行索引</param> 595 /// <param name="CellIndex">要赋值的单元格列索引</param> 596 /// <param name="targateRow1">第一个单元格的行</param> 597 /// <param name="targateCell1">第一个单元格的列</param> 598 /// <param name="targateRow2">第二个单元格的行</param> 599 /// <param name="targateCell2">第二个单元格的列</param> 600 public void MultplicationTableInXls(int Sheetindex, int RowIdex, int CellIndex,int targateRow1,int targateCell1, 601 int targateRow2,int targateCell2) 602 { 603 InitializeWorkbook(); 604 605 ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex); 606 607 IRow row = sheet1.GetRow(RowIdex); 608 609 string formula = GetCellPosition(targateRow1, targateCell1) + "*" + GetCellPosition(targateRow2, targateCell2); 610 611 row.CreateCell(CellIndex).CellFormula = formula; 612 613 WriteToFile(); 614 } 615 public string GetCellPosition(int row, int col) 616 { 617 col = Convert.ToInt32('A') + col; 618 row = row + 1; 619 return ((char)col) + row.ToString(); 620 } 621 /// <summary> 622 /// 合并单元格 623 /// </summary> 624 /// <param name="Sheetindex"></param> 625 /// <param name="firstRowIdex">起始单元格</param> 626 /// <param name="firstCellIndex"></param> 627 /// <param name="lastRowIdex">结束单元格</param> 628 /// <param name="lastCellIndex"></param> 629 public void MergeCellsInXls(int Sheetindex, int firstRowIdex, int firstCellIndex, int lastRowIdex, int lastCellIndex) 630 { 631 InitializeWorkbook(); 632 633 ISheet sheet = hssfworkbook.GetSheetAt(Sheetindex); 634 635 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5)); 636 637 CellRangeAddress region = new CellRangeAddress(firstRowIdex, lastRowIdex, firstCellIndex, lastCellIndex); 638 639 sheet.AddMergedRegion(region); 640 641 WriteToFile(); 642 } 643 644 #region 未处理 645 public void LoanCalculator(int Sheetindex, int RowIdex, int CellIndex) 646 { 647 InitializeWorkbook(); 648 Dictionary<String, ICellStyle> styles = CreateStyles(hssfworkbook); 649 ISheet sheet = hssfworkbook.GetSheetAt(Sheetindex); 650 sheet.IsPrintGridlines = (false);//取消打印格的显示 651 sheet.DisplayGridlines = (false); 652 653 IPrintSetup printSetup = sheet.PrintSetup; 654 printSetup.Landscape = (true); 655 sheet.FitToPage = (true); 656 sheet.HorizontallyCenter = (true); 657 658 sheet.SetColumnWidth(0, 3 * 256); 659 sheet.SetColumnWidth(1, 3 * 256); 660 sheet.SetColumnWidth(2, 11 * 256); 661 sheet.SetColumnWidth(3, 14 * 256); 662 sheet.SetColumnWidth(4, 14 * 256); 663 sheet.SetColumnWidth(5, 14 * 256); 664 sheet.SetColumnWidth(6, 14 * 256); 665 666 CreateNames(hssfworkbook); 667 668 IRow titleRow = sheet.CreateRow(0); 669 titleRow.HeightInPoints = (35); 670 for (int i = 1; i <= 7; i++) 671 { 672 titleRow.CreateCell(i).CellStyle = styles["title"]; 673 } 674 ICell titleCell = titleRow.GetCell(2); 675 titleCell.SetCellValue("Simple Loan Calculator"); 676 sheet.AddMergedRegion(CellRangeAddress.ValueOf("$C$1:$H$1")); 677 678 IRow row = sheet.CreateRow(2); 679 ICell cell = row.CreateCell(4); 680 cell.SetCellValue("Enter values"); 681 cell.CellStyle = styles["item_right"]; 682 683 row = sheet.CreateRow(3); 684 cell = row.CreateCell(2); 685 cell.SetCellValue("Loan amount"); 686 cell.CellStyle = styles["item_left"]; 687 cell = row.CreateCell(4); 688 cell.CellStyle = styles["input_$"]; 689 cell.SetAsActiveCell(); 690 691 row = sheet.CreateRow(4); 692 cell = row.CreateCell(2); 693 cell.SetCellValue("Annual interest rate"); 694 cell.CellStyle = styles["item_left"]; 695 cell = row.CreateCell(4); 696 cell.CellStyle = styles["input_%"]; 697 698 row = sheet.CreateRow(5); 699 cell = row.CreateCell(2); 700 cell.SetCellValue("Loan period in years"); 701 cell.CellStyle = styles["item_left"]; 702 cell = row.CreateCell(4); 703 cell.CellStyle = styles["input_i"]; 704 705 row = sheet.CreateRow(6); 706 cell = row.CreateCell(2); 707 cell.SetCellValue("Start date of loan"); 708 cell.CellStyle = styles["item_left"]; 709 cell = row.CreateCell(4); 710 cell.CellStyle = styles["input_d"]; 711 712 row = sheet.CreateRow(8); 713 cell = row.CreateCell(2); 714 cell.SetCellValue("Monthly payment"); 715 cell.CellStyle = styles["item_left"]; 716 cell = row.CreateCell(4); 717 cell.CellFormula = ("IF(Values_Entered,Monthly_Payment,\"\")"); 718 cell.CellStyle = styles["formula_$"]; 719 720 row = sheet.CreateRow(9); 721 cell = row.CreateCell(2); 722 cell.SetCellValue("Number of payments"); 723 cell.CellStyle = styles["item_left"]; 724 cell = row.CreateCell(4); 725 cell.CellFormula = ("IF(Values_Entered,Loan_Years*12,\"\")"); 726 cell.CellStyle = styles["formula_i"]; 727 728 row = sheet.CreateRow(10); 729 cell = row.CreateCell(2); 730 cell.SetCellValue("Total interest"); 731 cell.CellStyle = styles["item_left"]; 732 cell = row.CreateCell(4); 733 cell.CellFormula = ("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")"); 734 cell.CellStyle = styles["formula_$"]; 735 736 row = sheet.CreateRow(11); 737 cell = row.CreateCell(2); 738 cell.SetCellValue("Total cost of loan"); 739 cell.CellStyle = styles["item_left"]; 740 cell = row.CreateCell(4); 741 cell.CellFormula = ("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")"); 742 cell.CellStyle = styles["formula_$"]; 743 744 745 WriteToFile(); 746 } 747 748 /** 749 * cell styles used for formatting calendar sheets 750 */ 751 private static Dictionary<String, ICellStyle> CreateStyles(IWorkbook wb) 752 { 753 Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>(); 754 755 ICellStyle style = null; 756 IFont titleFont = wb.CreateFont(); 757 titleFont.FontHeightInPoints = (short)14; 758 titleFont.FontName = "Trebuchet MS"; 759 style = wb.CreateCellStyle(); 760 style.SetFont(titleFont); 761 style.BorderBottom = BorderStyle.DOTTED; 762 style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index; 763 styles.Add("title", style); 764 765 IFont itemFont = wb.CreateFont(); 766 itemFont.FontHeightInPoints = (short)9; 767 itemFont.FontName = "Trebuchet MS"; 768 style = wb.CreateCellStyle(); 769 style.Alignment = (HorizontalAlignment.LEFT); 770 style.SetFont(itemFont); 771 styles.Add("item_left", style); 772 773 style = wb.CreateCellStyle(); 774 style.Alignment = HorizontalAlignment.RIGHT; 775 style.SetFont(itemFont); 776 styles.Add("item_right", style); 777 778 style = wb.CreateCellStyle(); 779 style.Alignment = HorizontalAlignment.RIGHT; 780 style.SetFont(itemFont); 781 style.BorderRight = BorderStyle.DOTTED; 782 style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index; 783 style.BorderBottom = BorderStyle.DOTTED; 784 style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index; 785 style.BorderLeft = BorderStyle.DOTTED; 786 style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index; 787 style.BorderTop = BorderStyle.DOTTED; 788 style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index; 789 style.DataFormat = (wb.CreateDataFormat().GetFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); 790 styles.Add("input_$", style); 791 792 style = wb.CreateCellStyle(); 793 style.Alignment = HorizontalAlignment.RIGHT; 794 style.SetFont(itemFont); 795 style.BorderRight = BorderStyle.DOTTED; 796 style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index; 797 style.BorderBottom = BorderStyle.DOTTED; 798 style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index; 799 style.BorderLeft = BorderStyle.DOTTED; 800 style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index; 801 style.BorderTop = BorderStyle.DOTTED; 802 style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index; 803 style.DataFormat = (wb.CreateDataFormat().GetFormat("0.000%")); 804 styles.Add("input_%", style); 805 806 style = wb.CreateCellStyle(); 807 style.Alignment = HorizontalAlignment.RIGHT; 808 style.SetFont(itemFont); 809 style.BorderRight = BorderStyle.DOTTED; 810 style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index; 811 style.BorderBottom = BorderStyle.DOTTED; 812 style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index; 813 style.BorderLeft = BorderStyle.DOTTED; 814 style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index; 815 style.BorderTop = BorderStyle.DOTTED; 816 style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index; 817 style.DataFormat = wb.CreateDataFormat().GetFormat("0"); 818 styles.Add("input_i", style); 819 820 style = wb.CreateCellStyle(); 821 style.Alignment = (HorizontalAlignment.CENTER); 822 style.SetFont(itemFont); 823 style.DataFormat = wb.CreateDataFormat().GetFormat("m/d/yy"); 824 styles.Add("input_d", style); 825 826 style = wb.CreateCellStyle(); 827 style.Alignment = HorizontalAlignment.RIGHT; 828 style.SetFont(itemFont); 829 style.BorderRight = BorderStyle.DOTTED; 830 style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index; 831 style.BorderBottom = BorderStyle.DOTTED; 832 style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index; 833 style.BorderLeft = BorderStyle.DOTTED; 834 style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index; 835 style.BorderTop = BorderStyle.DOTTED; 836 style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index; 837 style.DataFormat = wb.CreateDataFormat().GetFormat("$##,##0.00"); 838 style.BorderBottom = BorderStyle.DOTTED; 839 style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index; 840 style.FillForegroundColor = IndexedColors.GREY_25_PERCENT.Index; 841 style.FillPattern = FillPatternType.SOLID_FOREGROUND; 842 styles.Add("formula_$", style); 843 844 style = wb.CreateCellStyle(); 845 style.Alignment = HorizontalAlignment.RIGHT; 846 style.SetFont(itemFont); 847 style.BorderRight = BorderStyle.DOTTED; 848 style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index; 849 style.BorderBottom = BorderStyle.DOTTED; 850 style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index; 851 style.BorderLeft = BorderStyle.DOTTED; 852 style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index; 853 style.BorderTop = BorderStyle.DOTTED; 854 style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index; 855 style.DataFormat = wb.CreateDataFormat().GetFormat("0"); 856 style.BorderBottom = BorderStyle.DOTTED; 857 style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index; 858 style.FillForegroundColor = IndexedColors.GREY_25_PERCENT.Index; 859 style.FillPattern = (FillPatternType.SOLID_FOREGROUND); 860 styles.Add("formula_i", style); 861 862 return styles; 863 } 864 865 866 //define named ranges for the inputs and formulas 867 public static void CreateNames(IWorkbook wb) 868 { 869 IName name; 870 871 name = wb.CreateName(); 872 name.NameName = ("Interest_Rate"); 873 name.RefersToFormula = ("'Loan Calculator'!$E$5"); 874 875 name = wb.CreateName(); 876 name.NameName = ("Loan_Amount"); 877 name.RefersToFormula = ("'Loan Calculator'!$E$4"); 878 879 name = wb.CreateName(); 880 name.NameName = ("Loan_Start"); 881 name.RefersToFormula = ("'Loan Calculator'!$E$7"); 882 883 name = wb.CreateName(); 884 name.NameName = ("Loan_Years"); 885 name.RefersToFormula = ("'Loan Calculator'!$E$6"); 886 887 name = wb.CreateName(); 888 name.NameName = ("Number_of_Payments"); 889 name.RefersToFormula = ("'Loan Calculator'!$E$10"); 890 891 name = wb.CreateName(); 892 name.NameName = ("Monthly_Payment"); 893 name.RefersToFormula = ("-PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)"); 894 895 name = wb.CreateName(); 896 name.NameName = ("Total_Cost"); 897 name.RefersToFormula = ("'Loan Calculator'!$E$12"); 898 899 name = wb.CreateName(); 900 name.NameName = ("Total_Interest"); 901 name.RefersToFormula = ("'Loan Calculator'!$E$11"); 902 903 name = wb.CreateName(); 904 name.NameName = ("Values_Entered"); 905 name.RefersToFormula = ("IF(ISBLANK(Loan_Start),0,IF(Loan_Amount*Interest_Rate*Loan_Years>0,1,0))"); 906 } 907 908 #endregion 909 910 911 /// <summary> 912 /// excel中插入图片,支持jpeg 913 /// </summary> 914 /// <param name="Sheetindex"></param> 915 /// <param name="RowIdex"></param> 916 /// <param name="CellIndex"></param> 917 /// <param name="picpath">图片地址</param> 918 /// <param name="dx1">图片坐标</param> 919 /// <param name="dy1"></param> 920 /// <param name="dx2"></param> 921 /// <param name="dy2"></param> 922 /// <param name="col1">表格</param> 923 /// <param name="row1"></param> 924 /// <param name="col2"></param> 925 /// <param name="row2"></param> 926 public void InsertPicturesInXls(int Sheetindex, int RowIdex, int CellIndex, string picpath 927 ,int dx1,int dy1,int dx2,int dy2,int col1,int row1,int col2,int row2) 928 { 929 InitializeWorkbook(); 930 931 ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex); 932 933 HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch(); 934 //create the anchor 935 HSSFClientAnchor anchor; 936 anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2); 937 anchor.AnchorType = 2; 938 //load the picture and get the picture index in the workbook 939 HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(picpath, hssfworkbook)); 940 //Reset the image to the original size. 941 //picture.Resize(); //Note: Resize will reset client anchor you set. 942 picture.LineStyle = LineStyle.DashDotGel; 943 944 WriteToFile(); 945 } 946 947 private int LoadImage(string path, HSSFWorkbook wb) 948 { 949 FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read); 950 byte[] buffer = new byte[file.Length]; 951 file.Read(buffer, 0, (int)file.Length); 952 return wb.AddPicture(buffer, PictureType.JPEG); 953 954 } 955 /// <summary> 956 /// 隐藏Excel行和列 957 /// </summary> 958 /// <param name="Sheetindex"></param> 959 /// <param name="RowIdex"></param> 960 /// <param name="CellIndex"></param> 961 /// <param name="isHiddenCol"></param> 962 public void HideColumnAndRowInXls (int Sheetindex, int RowIdex, int CellIndex,bool isHiddenCol) 963 { 964 InitializeWorkbook(); 965 966 ISheet s = hssfworkbook.GetSheetAt(Sheetindex); 967 IRow r1 = s.GetRow(RowIdex); 968 969 970 //hide IRow 2 971 r1.ZeroHeight = true; 972 973 //hide column C 974 s.SetColumnHidden(CellIndex, isHiddenCol); 975 976 WriteToFile(); 977 } 978 /// <summary> 979 /// 填充背景颜色 980 /// </summary> 981 /// <param name="Sheetindex"></param> 982 /// <param name="RowIdex"></param> 983 /// <param name="CellIndex"></param> 984 /// <param name="fpt">填充类型</param> 985 /// <param name="Forecolor">前景色：NPOI.HSSF.Util.HSSFColor.BLUE.index</param> 986 /// <param name="backcolor">背景颜色：NPOI.HSSF.Util.HSSFColor.BLUE.index</param> 987 public void FillBackgroundInXls(int Sheetindex, int RowIdex, int CellIndex,FillPatternType fpt,short Forecolor,short backcolor) 988 { 989 InitializeWorkbook(); 990 991 ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex); 992 //fill background 993 ICellStyle style1 = hssfworkbook.CreateCellStyle(); 994 style1.FillForegroundColor = Forecolor; 995 style1.FillPattern = fpt; 996 style1.FillBackgroundColor = backcolor; 997 sheet1.GetRow(RowIdex).GetCell(CellIndex).CellStyle= style1; 998 WriteToFile(); 999 } 1000 1001 1002 /// <summary> 1003 /// 1004 /// </summary> 1005 /// <param name="Sheetindex"></param> 1006 /// <param name="RowIdex"></param> 1007 /// <param name="CellIndex"></param> 1008 /// <param name="fontName">字体名</param> 1009 /// <param name="color">颜色</param> 1010 /// <param name="istalic">斜体</param> 1011 /// <param name="IsStrikeout">删除线</param> 1012 /// <param name="size">字体大小</param> 1013 public void ApplyFontInXls(int Sheetindex,int RowIdex, int CellIndex, string fontName, short color, bool istalic, bool IsStrikeout,short size) 1014 { 1015 InitializeWorkbook(); 1016 1017 ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex); 1018 1019 //font style1: underlined, italic, red color, fontsize=20 1020 IFont font1 = hssfworkbook.CreateFont(); 1021 font1.FontName = "宋体"; 1022 font1.Color = HSSFColor.RED.index; 1023 font1.IsItalic = istalic; 1024 font1.IsStrikeout = IsStrikeout; 1025 //font1.Underline = (byte)FontUnderlineType.DOUBLE; 1026 font1.FontHeightInPoints = size; 1027 //bind font with style 1 1028 1029 ICell cell1 = sheet1.GetRow(RowIdex).GetCell(CellIndex); 1030 ICellStyle style1 = hssfworkbook.CreateCellStyle(); 1031 style1.SetFont(font1); 1032 cell1.CellStyle = style1; 1033 WriteToFile(); 1034 } 1035 1036 /// <summary> 1037 /// 设置sheet的颜色 1038 /// </summary> 1039 /// <param name="Sheetindex"></param> 1040 /// <param name="RowIdex"></param> 1041 /// <param name="CellIndex"></param> 1042 public void ChangeSheetTabColorInXls(int Sheetindex) 1043 { 1044 InitializeWorkbook(); 1045 1046 ISheet sheet = hssfworkbook.GetSheetAt(Sheetindex); 1047 sheet.TabColorIndex = HSSFColor.AQUA.index; 1048 1049 WriteToFile(); 1050 } 1051 1052 1053 public string GetCellValue(int sheetIndex,int rowIndex,int cellIndex) 1054 { 1055 InitializeWorkbook(); 1056 1057 ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex); 1058 1059 ICell cell= sheet1.GetRow(rowIndex).GetCell(cellIndex); 1060 1061 return cell.StringCellValue; 1062 1063 } 1064 1065 1066 1067 #region 导入导出 1068 /* 1069 * DataTable table = new DataTable(); 1070 * MemoryStream ms = DataTableRenderToExcel.RenderDataTableToExcel(table) as MemoryStream; 1071 * Response.AddHeader("Content-Disposition", string.Format("attachment; filename=Download.xls")); 1072 * Response.BinaryWrite(ms.ToArray()); 1073 * ms.Close(); 1074 * ms.Dispose(); 1075 1076 * if (this.fuUpload.HasFile) 1077 * { 1078 * DataTable table = DataTableRenderToExcel.RenderDataTableFromExcel(this.fuUpload.FileContent, 1, 0); 1079 * this.gvExcel.DataSource = table; 1080 * this.gvExcel.DataBind(); 1081 * } 1082 */ 1083 /// <summary> 1084 /// Datatable导出Excel 1085 /// </summary> 1086 /// <param name="SourceTable"></param> 1087 /// <returns></returns> 1088 private static Stream RenderDataTableToExcel(DataTable SourceTable) 1089 { 1090 HSSFWorkbook workbook = new HSSFWorkbook(); 1091 MemoryStream ms = new MemoryStream(); 1092 ISheet sheet = workbook.CreateSheet(); 1093 IRow headerRow = sheet.CreateRow(0); 1094 1095 // handling header. 1096 foreach (DataColumn column in SourceTable.Columns) 1097 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); 1098 1099 // handling value. 1100 int rowIndex = 1; 1101 1102 foreach (DataRow row in SourceTable.Rows) 1103 { 1104 IRow dataRow = sheet.CreateRow(rowIndex); 1105 foreach (DataColumn column in SourceTable.Columns) 1106 { 1107 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); 1108 } 1109 1110 rowIndex++; 1111 } 1112 1113 workbook.Write(ms); 1114 ms.Flush(); 1115 ms.Position = 0; 1116 1117 sheet = null; 1118 headerRow = null; 1119 workbook = null; 1120 1121 return ms; 1122 } 1123 /// <summary> 1124 /// Datatable导出Excel 1125 /// </summary> 1126 /// <param name="SourceTable"></param> 1127 /// <param name="FileName"></param> 1128 public static void RenderDataTableToExcel(DataTable SourceTable, string FileName) 1129 { 1130 MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream; 1131 FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write); 1132 byte[] data = ms.ToArray(); 1133 1134 fs.Write(data, 0, data.Length); 1135 fs.Flush(); 1136 fs.Close(); 1137 1138 data = null; 1139 ms = null; 1140 fs = null; 1141 } 1142 /// <summary> 1143 /// 导出excel为Datatable 1144 /// </summary> 1145 /// <param name="ExcelFileStream"></param> 1146 /// <param name="SheetName"></param> 1147 /// <param name="HeaderRowIndex"></param> 1148 /// <returns></returns> 1149 public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex) 1150 { 1151 HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); 1152 ISheet sheet = workbook.GetSheet(SheetName); 1153 1154 DataTable table = new DataTable(); 1155 1156 IRow headerRow = sheet.GetRow(HeaderRowIndex); 1157 int cellCount = headerRow.LastCellNum; 1158 1159 for (int i = headerRow.FirstCellNum; i < cellCount; i++) 1160 { 1161 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); 1162 table.Columns.Add(column); 1163 } 1164 1165 int rowCount = sheet.LastRowNum; 1166 1167 for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++) 1168 { 1169 IRow row = sheet.GetRow(i); 1170 DataRow dataRow = table.NewRow(); 1171 1172 for (int j = row.FirstCellNum; j < cellCount; j++) 1173 dataRow[j] = row.GetCell(j).ToString(); 1174 } 1175 1176 ExcelFileStream.Close(); 1177 workbook = null; 1178 sheet = null; 1179 return table; 1180 } 1181 /// <summary> 1182 /// 将Excel转换为Datatable 1183 /// </summary> 1184 /// <param name="ExcelFileStream"></param> 1185 /// <param name="SheetIndex"></param> 1186 /// <param name="HeaderRowIndex"></param> 1187 /// <returns></returns> 1188 public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex) 1189 { 1190 HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); 1191 ISheet sheet = workbook.GetSheetAt(SheetIndex); 1192 1193 DataTable table = new DataTable(); 1194 1195 IRow headerRow = sheet.GetRow(HeaderRowIndex); 1196 int cellCount = headerRow.LastCellNum; 1197 1198 for (int i = headerRow.FirstCellNum; i < cellCount; i++) 1199 { 1200 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); 1201 table.Columns.Add(column); 1202 } 1203 1204 int rowCount = sheet.LastRowNum; 1205 1206 for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++) 1207 { 1208 IRow row = sheet.GetRow(i); 1209 DataRow dataRow = table.NewRow(); 1210 1211 for (int j = row.FirstCellNum; j < cellCount; j++) 1212 { 1213 if (row.GetCell(j) != null) 1214 dataRow[j] = row.GetCell(j).ToString(); 1215 } 1216 1217 table.Rows.Add(dataRow); 1218 } 1219 1220 ExcelFileStream.Close(); 1221 workbook = null; 1222 sheet = null; 1223 return table; 1224 } 1225 1226 #endregion 1227 1228 1229 } 1230 } 1231 1232 NPOIHelper
sheet.SetColumnWidth(8, 20 * 500); //设置列宽
来源网络：http://www.cnblogs.com/anbylau2130/p/3182495.html#undefined
