0.NPOI brief introduction

Baidu Encyclopedia said that NPOI can read and write word or EXCEL documents without installing Office. NPOI is an open-source project for C# reading and writing Microsoft OLE2 component documents such as Excel and word.

Advantages: you can operate Word and Excel on a computer without Office installed, which is open source and free of charge.

1. Installation and call of npoi in C#

Create a new project, right-click in solution manager and click Manage NuGet package

  Enter NPOI in the pop-up form browsing interface

  Select the first NPOI and click Install (because I have installed it, it displays uninstall)

When the installation is complete, add the reference

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

Then you can use NPOI.

2.NPOI finds the number of rows and columns
  int lienum = sheet.GetRow(0).LastCellNum; / / number of columns in a row
  int rownum = sheet.LastRowNum; / / number of rows in the selected table

string filepath_save = filepath_sa + "\\" + filepath.Substring(filepath.LastIndexOf("\\") + 1);
IWorkbook workbook = null;
FileStream fs = new FileStream(filepath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (filepath_save.IndexOf(".xlsx") > 0) // Version 2007
    workbook = new XSSFWorkbook(fs);
else if (filepath_save.IndexOf(".xls") > 0) // 2003 version
    workbook = new HSSFWorkbook(fs);
ISheet sheet = workbook.GetSheetAt(workbook.ActiveSheetIndex);
int lienum = sheet.GetRow(0).LastCellNum;//Number of columns in a row
int rownum = sheet.LastRowNum;//Number of rows in the selected table

3.NPOI create cells

sheet.CreateRow(i) / / create line i

sheet.GetRow(i).CreateCell(j) / / create the j-th cell in row i

sheet.ShiftRows(0, sheet.LastRowNum, 2, true, false);
sheet.CreateRow(0);
sheet.CreateRow(1);
IRow row1 = sheet.GetRow(0);
IRow row2 = sheet.GetRow(1);
for (int i = 0; i < lienum; i++) { row1.CreateCell(i); row2.CreateCell(i); }

4.NPOI merge cells

First determine the region to be merged, and then execute the AddMergedRegion function

Note the setting of region, CellRangeAddress (to merge the first row of cells, the last row of cells, the first column of cells, and the last column of cells)

The number of rows and columns of cells in NPOI starts from 0

CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, row1.LastCellNum - 1);
CellRangeAddress region2 = new CellRangeAddress(1, 1, 0, row2.LastCellNum - 1);
//Merge first row
sheet.AddMergedRegion(region1);
//Merge the second comment line
sheet.AddMergedRegion(region2);

5.NPOI execution formula

sheet.GetRow(si).GetCell(j).SetCellFormula(formula_sum);

Set the formula to be executed in the SetCellFormula of the cell. Note that the column is written in ABCD.

Sheet.forceformulacalculation = true; / / ensure that the formula is executed instead of opening the Excel table. Double click the formula to execute it

string formula_sum = "SUM(" + sum_lie + 3.ToString() + ":" + sum_lie + sheet.LastRowNum.ToString() + ")";
sheet.GetRow(sheet.LastRowNum).GetCell(0).SetCellValue("total");
sheet.GetRow(sheet.LastRowNum).GetCell(8).SetCellFormula(formula_sum);

6.NPOI setting cell format and column width

ICellStyle cellstyle_title = workbook.CreateCellStyle();
// cellstyle_title.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellstyle_title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellstyle_title.VerticalAlignment = VerticalAlignment.Center;
// cellStyle.BorderBottom =;
IFont cellfont_title = workbook.CreateFont();
cellfont_title.FontName = "Isoline";
cellfont_title.FontHeightInPoints = 20;//Set font size
cellfont_title.IsBold = true;
cellstyle_title.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//upper
cellstyle_title.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//lower
cellstyle_title.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//Left
cellstyle_title.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//right
cellstyle_title.SetFont(cellfont_title);
sheet.GetRow(1).Height = 55 * 20; //The unit for setting the Height of the row is 1 / 20 points. For example, set the Height to 50 points

7. Save the modification result

fs.Close();
FileStream file = new FileStream(filepath_save, FileMode.Create);
workbook.Write(file);
file.Close();

 

8. Some problems and solutions encountered in the use of npoi

(1) After the cell content is wrapped, even if the row height becomes larger, it cannot be fully displayed, which is not convenient for the printing of results

ICellStyle notesStyle = workbook.CreateCellStyle();
notesStyle.WrapText = true;//Set the line feed first
StringBuilder noteString = new StringBuilder("Filling instructions:\n");
noteString.Append("1,The content of the first line;\n");
noteString.Append("2,Content of the second line\n");
noteString.Append("3,Content of the third line\n");
noteString.Append("4,Content of the fourth line");
notesStyle.WrapText = true;

(2) Setting of decimal places

sheet.GetRow(sheet.LastRowNum).GetCell(8).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");

(3) Auto adjust column width

for (int i=0;i<lienum;i++) { sheet.AutoSizeColumn(i); }