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;
}