C# operate Excel based on npoi

Time:2021-12-7
catalogue
  • 1 cell drop-down box
  • 2 add comments
  • 3 read data
  • epilogue
  • Common problem solving
    • Npoi export add annotation function
    • When POI exports excel, the drop-down list value exceeds 255
    • Date format import confusion
    • be careful

1 cell drop-down box

In development, we will encounter setting drop-down boxes for cells. Generally, it can be written as follows:


var cellRanges = new CellRangeAddressList(firstRow, lastRow, firstCol, latsCol);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(stringArray);
HSSFDataValidation validate = new HSSFDataValidation(cellRanges, constraint);
validate.ShowProptBox = true;
sheet.AddValidationData(validate);

However, if the string array has length restrictions, such as the problem that the drop-down list value exceeds 255 when npoi exports excel (string literals in formulas can’t be bigger than 255 characters ASCII)
Solution
Save the drop-down content by creating an additional sheet page in Excel and convert it to drop-down box data.

ISheet hidden = workbook.CreateSheet(columnName);
IRow row = null;
ICell cell = null;
for (int i = 0; i < stringArray.Length; i++)
{
    row = hidden.CreateRow(i);
    cell = row.CreateCell(0);
    cell.SetCellValue(stringArray[i]);
}
IName namedCell = workbook.CreateName();
namedCell.NameName = column.ColumnName;
//Note that the following syntax is an excel formula. It is recommended not to leave a ` $'out. Many documents should be omitted.
namedCell.RefersToFormula = $"{columnName}!$A$1:$A${stringArray.Length}";
DVConstraint constraint =  DVConstraint.CreateFormulaListConstraint(columnName);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, latsCol);
HSSFDataValidation validate = new HSSFDataValidation(addressList, constraint);
sheet.AddValidationData(dataValidate);

2 add comments

The code is as follows:

HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
//This code parameter should not be fixed. It is used to locate the position and size of your annotation.
HSSFComment comment = 
    (HSSFComment)patriarch.CreateCellComment(new HSSFFClientAnchor(0, 0, 255,255, col1, 0, col1 + 2, 5));
comment.Author = "Dison";
Comment.string = new hssfrichtextstring ($"content");
cell.CellComment = comment;

3 read data

How to parse formula results
The code is as follows:


if (row.GetCell(i).CellType.Equals(CellType.Formula))
{
    var data = row.GetCell(i).RichStringCellValue;
}

If you want to read the formula, you can also read it as follows:


var data = row.GetCell(i).ToString();

However, it should be noted that there is no equal sign “=” in the result. Here I am demonstrating, so I wrote local variables.

Date format MM DD YY to yyyy MM DD
Since the numbers and dates in Excel are in numeric format,; The treatment is as follows:


if (row.GetCell(i).CellType.Equals(CellType.Numeric))
{
    ICell cell = row.GetCell(i);
    short format = cell.CellStyle.DataFormat;
    if (format != 0)
    {
        var data = cell.DateCellValue.ToString("yyyy-MM-dd");
    }
    else 
    {
        var data = cell.NumericCellValue;
    }
}

epilogue

Npoi is also a relatively mature excel operation library. The information on the Internet is really sketchy. But as a programmer, you must learn to be patient, especially debug.

Common problem solving

Npoi export add annotation function

//Add comments
HSSFPatriarch patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFComment comment12 = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 2, 3));// Annotation display positioning
Comment12. String = new hssfrichtextstring ("please fill in the full department name!");
HSSFCell cell12 = (HSSFCell)headerRow.CreateCell(12);// Give comments to cells
cell12.CellComment = comment12;

However, there is an important point to clarify, that is, the position and size of annotations are controlled by the eight parameters of hssfclientanchor. Never simply write hssfclientanchor (0, 0, 0, 1, 2, 2, 3),

Because the position of comments in each cell is different (the position when editing comments). What to do, of course, is to understand the meaning of parameters:

In short:

It is necessary to explain the parameters of hssfclientanchor (dx1, DY1, DX2, dy2, col1, row1, col2, row2):

  • Dx1: X offset of starting cell;
  • DY1: y offset of starting cell;
  • DX2: X offset of the terminating cell;
  • Dy2: y offset of the terminating cell;
  • Col1: starting cell column number;
  • Row1: start cell row sequence number;
  • Col2: termination unit column No;
  • Row2: end cell row sequence number;

In fact, the first four are offsets, and the last four are related to the position and size of annotations.

Take my own example:


HSSFComment comment1 = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(255, 125, 1023, 150, colindex + 1, rowIndex - 1,  colindex + 2, rowIndex + 4));
  • rowIndex   Is the row of the current cell, and COLINDEX is the column of the current cell. You can navigate to the current cell through rows and columns.
  • colindex  + 1 corresponds to the above parameter col1   Indicates that the starting position of the annotation is the next column of the number of columns in the current cell, that is, the original is column 5, so the annotation starts in column 6.
  • Rowindex – 1 corresponds to the above parameter row1   Indicates that the starting position of the cortex is the previous row of the current cell row number, that is, it was row 2, so the annotation starts at row 1.
  • colindex + 2,   rowIndex  + four   These two parameters are where the cell ends    + two   + four   It determines the size of the annotation. The reason is the same as COLINDEX  + 1,rowIndex – 1 。

But npoi export has a pit     That is, the annotation size will change with the size of the cell where it is located   This has little effect   If you want to solve this problem     You can only change the export method…

Never follow what others write on the Internet (0, 0, 0, 0, 1, 2, 2, 3). It will kill you. The annotation position will remain unchanged   The comments of any cell are in the same position.

When POI exports excel, the drop-down list value exceeds 255

//Create excel workbook object

Workbook workbook = new HSSFWorkbook();

//Generate a table setting: tab

Sheet sheet = workbook.createSheet("sheet1");

 

//Go to the database to query the data we want

List<Product> productList = Ebean.getServer(GlobalDBControl.getDB()) .createQuery(Product.class, "find product where 1 = 1 and status = 0 and producttype is not null ") .findList();

//Create an array to store the data we extracted

String[] productNameArray = new String[productList.size()];

//Traverse each peoduct object to get the product name attribute and add it to the array

for (int i = 0; i < productList.size(); i++)

{ Product product = productList.get(i);

productNameArray[i] = product.getTitle(); }

//Put the drop-down box data into a new sheet, and then excle loads the drop-down box data through the new sheet data

Sheet hidden = workbook.createSheet("hidden");

//Create cell object cell = null;

//Traverse the array above and put the data into the cells of the new sheet

for (int i = 0, length = productNameArray.length; i < length; i++)

{// fetch each element in the array

String name = productNameArray[i];

//Create the corresponding row object according to I (note that we will put each element on a separate row)

Row row = hidden.createRow(i);

//Create the first cell in each row

cell = row.createCell(0);

//Then assign the elements in the array to this cell

cell.setCellValue(name); }

 

//Create a name that can be referenced by other cells

Name namedCell = workbook.createName(); namedCell.setNameName("hidden");

//Sets the formula referenced by the name

namedCell.setRefersToFormula("hidden!$A$1:$A$" + productNameArray.length);

//Load the data and convert the data in the sheet named hidden into list form

DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");

//Set the 3-65534 behavior drop-down list in the first column

//(3, 65534, 0, 0) = = = = > (start row, end row, start column, end column)

CellRangeAddressList regions = new CellRangeAddressList(3, 65534, 0, 0);

//Bind the location selected from the settings drop-down list with the corresponding relationship of the data

DataValidation dataValidation = new HSSFDataValidation(regions, constraint);

//Set the second sheet to hide workbook.setsheetshidden (1, true);

//Assign data to the drop-down list sheet. Addvalidationdata (datavalidation);

//Finally, you can export the file. The following code is not written. I only write some code related to this problem

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

   In case of multiple columns, the following methods can be reused

private void ExcelTo255(Workbook workbook,String sheetName,int sheetNameIndex,String[] sheetData,int firstRow,int lastRow,int firstCol,int lastCol){
    //Put the drop-down box data into a new sheet, and then excle loads the drop-down box data through the new sheet data
    Sheet hidden = workbook.createSheet(sheetName);

    //Create cell object
    Cell cell =null;
    //Traverse the array above and put the data into the cells of the new sheet
    for (int i = 0, length = sheetData.length; i < length; i++){
        //Take out each element in the array
        String name = sheetData[i];
        //Create the corresponding row object according to I (note that we will put each element on a separate row)
        Row row = hidden.createRow(i);
        //Create the first cell in each row
        cell = row.createCell(0);
        //Then assign the elements in the array to this cell
        cell.setCellValue(name);
    }
    //Create a name that can be referenced by other cells
    Name namedCell = workbook.createName();
    namedCell.setNameName(sheetName);
    //Sets the formula referenced by the name
    namedCell.setRefersToFormula(sheetName+"!$A$1:$A$" + sheetData.length);
    //Load the data and convert the data in the sheet named hidden into list form
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(sheetName);

    //Set the 3-65534 behavior drop-down list in the first column
    //(3, 65534, 2, 2) = = = = > (start row, end row, start column, end column)
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    //Bind the location selected from the settings drop-down list with the corresponding relationship of the data
    DataValidation dataValidation = new HSSFDataValidation(regions, constraint);

    //Set the second sheet to hidden
    workbook.setSheetHidden(sheetNameIndex, true);
    //Assign data to drop-down list
    workbook.getSheetAt(0).addValidationData(dataValidation);
}

Date format import confusion

reason

When npoi is imported, it will roughly judge the format of the cells in the excel document,

There are blank, Boolean, numeric, string, error, formula, etc,

However, if there is no date, the date cell will be judged as numeric,

Therefore, the date format cell takes the value according to the number type,

Therefore, after the cell is judged as a number, you have to judge whether it is in date format.

/// <summary>
        ///Get cell type
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    short format = cell.CellStyle.DataFormat;
                    if (format != 0) { return cell.DateCellValue; } else { return cell.NumericCellValue; }
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                default:
                    return "=" + cell.CellFormula;
            }
        }

be careful

Use the long number type in Excel, otherwise such data may be misjudged as date type

For example: 00001232017001, you need to deal with the cell format – > set it to “general” type

The above is the details of operating Excel based on c# npoi. For more information about operating Excel based on c# npoi, please pay attention to other relevant articles of developeppaer!