Briefly record NPOI's operation on Excel table
1, Read Excel data
1-1 reading Excel documents
① Check whether the Excel extension is ". xls" or ". xlsx";
② Open the file stream and read the Excel file;
③ Instantiate an IWorkbook through the Excel extension and pass in the obtained file stream: ". xls" to create "HSSFWorkbook"; ". xlsx" creates "XSSFWorkbook";
④ Close the file stream (very important).
/// <summary> ///Read IWorkbook /// </summary> public IWorkbook ReadWorkbook = null; /// <summary> ///Get read WorkBook /// </summary> public void GetReadWorkbook(string FilePath) { // Get extension string ExtensionName = System.IO.Path.GetExtension(FilePath); // File stream FileStream FileStream = new FileStream(FilePath, FileMode.Open, FileAccess.ReadWrite); // Write xls into workbook version 2003 if (ExtensionName.Equals(".xls")) { ReadWorkbook = new HSSFWorkbook(FileStream); } // Write xlsx into workbook version 2007 else if (ExtensionName.Equals(".xlsx")) { ReadWorkbook = new XSSFWorkbook(FileStream); } else { ReadWorkbook = null; } FileStream.Close(); }
1-2 get Sheet
1. Get all sheets
Get all sheets by looping through the Workbook.
/// <summary> ///Gets the Sheet name in the table /// </summary> public List<ISheet> Sheets = null; /// <summary> ///Get all sheets /// </summary> public void GetSheets() { // Get table Sheets = new List<ISheet>(); var SheetCount = ReadWorkbook.NumberOfSheets; for (int i = 0; i < SheetCount; i++) { Sheets.Add(ReadWorkbook.GetSheetAt(i)); } }
2. Get Sheet by Sheet name
① Get Sheet by Sheet name.
② Obtain the subscript corresponding to the Sheet array through the Sheet name;
get the Sheet through the Sheet subscript.
// 1. Get Sheet by Sheet name int sheetIndex =ReadWorkbook.GetSheet("SheetName") // 2.1 obtain the subscript corresponding to the Sheet array through the Sheet name; int sheetIndex =ReadWorkbook.GetSheetIndex("SheetName") // 2.2 obtain the corresponding Sheet data through the Sheet subscript Isheet sheet =ReadWorkbook.GetSheetAt(sheetIndex )
1-3 read Sheet data and output
① Get the number of rows in the Sheet table: rowCount;
② Get the number of columns corresponding to the Sheet row: column;
③ Get the cell data corresponding to a row and a column: cellvalue = row GetCell(j). ToString();
④ Output the obtained cell: console Wlite(cellValue ).
/// <summary> ///Get Sheet data /// </summary> /// <param name="Sheet"></param> private void GetSheetData(ISheet Sheet) { if (Sheet == default) { return null; } IRow row; // 1. Get the number of rows var rowCount = Sheet.LastRowNum; // Get data from the fourth row (subscript 3), and the first three rows are headers // If you start from the first line, i=0 is OK for (int i = 3; i <= rowCount; i++) { var dataTable = new DataTable_Model(); // Get specific lines row = Sheet.GetRow(i); if (row != null) { // 2. Get the number of columns corresponding to the row var column = row.LastCellNum; for (int j = 0; j < column; j++) { // 3. Obtain the cell data corresponding to a row and a column var cellValue = row.GetCell(j).ToString(); // 4. Output cell data Console.Wlite(cellValue+" "); } // Line feed Console.WliteLine(); } } }
1-4 modify cell data
① Obtain the corresponding ISheet through the Sheet name;
② Get the number of rows of the modified Sheet;
③ Get the number of columns of the corresponding row of the modified Sheet;
④ Obtain the cell data corresponding to a row and a column;
⑤ Pass a value to the cell to overwrite the corresponding cell data;
⑥ Write the modification of the Workbook into the file stream and operate the file accordingly;
⑦ Note: when the file is opened, the file stream will report an error. In order not to affect the operation again, be sure to close the file stream.
Similarly: if you want to modify the data in a row of the Sheet / modify the data in a specified cell of the Sheet, you can assign it to a row / cell and modify the data in the corresponding cell.
/// <summary> ///Modify Field Sheet /// </summary> /// <param name="SheetName"></param> /// <returns></returns> private void UpdateSheet(string SheetName, string FilePath) { // Create file stream FileStream fsWrite = new FileStream(FilePath, FileMode.Open, FileAccess.Write); try { // 1. Obtain the corresponding ISeet through the Sheet name -- where ReadWorkbook is obtained when reading Excel documents var sheet = ReadWorkbook.GetSheet(SheetName); // 2. Get the number of rows int rowCount = sheet.LastRowNum; for (int i = 0; i < rowCount; i++) { // 3. Get the number of columns corresponding to the row int columnount = sheet.GetRow(i).LastCellNum; for (int j = 0; j < columnount; j++) { // 4. Get the cell data corresponding to a row and a column // The first three lines are designed as headers, so i+3 can be defined here var sheetCellValue = sheet.GetRow(i + 3).GetCell(j); // 5. Transfer values to cells to overwrite the corresponding cell data sheetCellValue.SetCellValue(sheetCellValue + "Update"); } } // 6. Write the modification of the Workbook to the file stream and operate the file accordingly ReadWorkbook.Write(fsWrite); } catch (Exception ex) { throw ex; } finally { // 7. Close the file stream: close the file stream with or without an error fsWrite.Close(); } }
1-5 delete Sheet
① Obtain the corresponding ISheet subscript through the Sheet name;
② Remove Sheet by Sheet subscript;
③ Write the modification of the Workbook into the file stream and operate the file accordingly;
note: when the file is opened, the file stream will report an error. In order not to affect the operation again, be sure to close the file stream.
/// <summary> ///Delete one of the sheets ///BUG: after deletion, there is no BUG in the Sheet table /// </summary> /// <param name="SheetName"></param> /// <param name="FilePath"></param> /// <returns></returns> public bool RemoveOneSheet(string SheetName, string FilePath) { var Result = false; // Create file stream FileStream fsWrite = new FileStream(FilePath, FileMode.Open, FileAccess.Write); try { // Find Sheet by subscript 1 var sheetIndex = ReadWorkbook.GetSheetIndex(SheetName); if (sheetIndex >= 0) { // 2. Remove Sheet by Sheet subscript ReadWorkbook.RemoveSheetAt(sheetIndex); // 3. Write the modification of the Workbook to the file stream and operate the file accordingly ReadWorkbook.Write(fsWrite); Result = true; } return Result; } catch (Exception ex) { throw ex; } finally { // 4. Close the file stream: close the file stream with or without an error fsWrite.Close(); } }
1-6 clear a row of data in the Sheet table
Only empty the data, but this row exists in Excel:
① Obtain the corresponding ISheet through the Sheet name;
② Navigate to the specified row to delete;
③ Clear row data;
④ Write the modification of the Workbook into the file stream and operate the file accordingly;
note: when the file is opened, the file stream will report an error. In order not to affect the operation again, be sure to close the file stream.
/// <summary> ///Clear the specified row data of Sheet /// </summary> /// <param name="Row"></param> /// <param name="SheetName"></param> /// <param name="FilePath"></param> /// <returns></returns> public bool EmptySheetRow(int RowNum, string SheetName, string FilePath) { var Result = false; FileStream fsWrite = new FileStream(FilePath, FileMode.Open, FileAccess.Write); try { 1. adopt Sheet Get the corresponding name ISheet ISheet sheet_Table = ReadWorkbook.GetSheet(SheetName); if (sheet_Table != null) { // 2. Navigate to the specified row to delete IRow row = sheet_Table.GetRow(RowNum - 1); if (row != null) { // 3. Clear row data sheet_Table.RemoveRow(row); // 4. Write the modification of the Workbook into the file stream and operate the file accordingly; ReadWorkbook.Write(fsWrite); Result = true; } } else { new Exception("This the Sheet does not exist"); } return Result; } catch (Exception ex) { throw ex; } finally { fsWrite.Close(); } }
1-7 delete a row in the Sheet table
Sheet does not provide direct deletion of row data. Here, move up and down to delete the specified row:
① Obtain the corresponding ISheet through the Sheet name;
② Determine the number of rows to move: moveNum = EndNum - StartNum;
③ Move the moveNum row up. among
④ Write the modification of the Workbook into the file stream and operate the file accordingly;
note: when the file is opened, the file stream will report an error. In order not to affect the operation again, be sure to close the file stream.
/// <summary> ///Delete the specified row data of Sheet /// </summary> ///< param name = "startNum" > start line < / param > ///< param name = "endnum" > terminate line < / param > /// <param name="SheetName"></param> /// <param name="FilePath"></param> /// <returns></returns> public bool RemoveSheetRow(int StartNum, int EndNum, string SheetName, string FilePath) { var Result = false; FileStream fsWrite = new FileStream(FilePath, FileMode.Open, FileAccess.Write); try { // 1. Obtain the corresponding ISheet through the Sheet name ISheet sheetTable = ReadWorkbook.GetSheet(SheetName); if (sheetTable == null) { new Exception("This the Sheet does not exist"); } // 2. Determine the number of rows to move: ensure endnum > = startNum int moveNum = EndNum - StartNum; // 3. Move moveNum row up for (int i = 0; i <= moveNum; i++) { // Move up: EndNum+1 to sheettable Lastrownum + 1 line moves up (- n) n lines, - N can only be - 1 sheetTable.ShiftRows(EndNum, sheetTable.LastRowNum, -1); // If you cycle once, move up once. If the number of lines to start moving after moving is - 1, you need EndNum -1. If you don't understand, refer to the picture above EndNum -= 1; } //Write data to the open xls file ReadWorkbook.Write(fsWrite); Result = true; return Result; } catch (Exception ex) { throw ex; } finally { fsWrite.Close(); } }
2, Import Excel data
2-1 create WorkBook
① Check whether the Excel extension is ". xls" or ". xlsx";
② Open the file stream and read the Excel file;
③ Instantiate an empty IWorkbook table through Excel extension: ". xls" create "HSSFWorkbook"; ". xlsx" creates "XSSFWorkbook";
/// <summary> ///Write IWorkbook /// </summary> public IWorkbook WriteWorkbook = null; /// <summary> ///Get write WorkBook /// </summary> public void GetWriteWorkbook(string FilePath) { // Get extension string ExtensionName = System.IO.Path.GetExtension(FilePath); // Write xls into workbook version 2003 if (ExtensionName.Equals(".xls")) { WriteWorkbook = new HSSFWorkbook(); } // Write xlsx into workbook version 2007 else if (ExtensionName.Equals(".xlsx")) { WriteWorkbook = new XSSFWorkbook(); } else { WriteWorkbook = null; } }
2-2 write Excel document
① Add a data table named SheetTest on WriteWorkbook;
② Define the number of rows rowCount=6;
③ Define the number of columns. Columnamount = 8;
④ Circular construction bank;
⑤ Create a cell corresponding to a row or column;
⑥ Add a value to the cell.
note: when the file is opened, the file stream will report an error. In order not to affect the operation again, be sure to close the file stream.
/// <summary> ///Table entity class data to table data /// </summary> /// <param name="FilePath"></param> /// <returns></returns> private void TableDataToCell(string FilePath) { FileStream fsWrite = new FileStream(FilePath, FileMode.Create, FileAccess.Write); try { // 1. Add a data table named SheetTest on WriteWorkbook ISheet sheet=WriteWorkbook.CreateSheet("SheetTest "); // 2. Define the number of lines var rowCount = 6; // 3. Define the number of columns int columnount = 8; for (int i = 0; i < rowCount; i++) { // 4. Create row IRow row = sheet.CreateRow(i); for (int j = 0; j < columnount; j++) { // 5. Create a cell corresponding to a row or column ICell cell = row.CreateCell(j); // 6. Add values to cells cell.SetCellValue($"The first{i}Line number{j}column"); // Add table style cell.CellStyle = ExpandFliePath.OtherRowStyle(); } } //7. Write the form to the file stream WriteWorkbook.Write(fsWrite); } catch (Exception ex) { throw ex; } finally { // Close file stream fsWrite.Close(); } }
3, Cell style
Define the style. The style defined here is relatively simple.
Cell style when creating or obtaining a cell, cell CellStyle =OtherRowStyle(); You can get the style
/// <summary> ///Style /// </summary> /// <returns></returns> public static ICellStyle OtherRowStyle() { var workbook = WriteWorkbook; ICellStyle cellStyle = workbook.CreateCellStyle(); IFont font = workbook.CreateFont(); font.FontName = "Microsoft YaHei "; //font.FontHeightInPoints = 15; ////Set font bold style //font.IsBold = true; // Use the SetFont method to add a font style to a cell style cellStyle.SetFont(font); // Cell styles: align horizontally and center cellStyle.Alignment = HorizontalAlignment.Center; //frame cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; // Auto wrap cellStyle.WrapText = true; ////Background color //cellStyle.FillForegroundColor = BlueGrey.Index; return cellStyle; }