Simple example of C# Npoi operating Excel

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

Tags: C#

Posted by TheCase on Wed, 25 May 2022 11:03:40 +0300