NPOI中关于公式的使用Formula

NPOI中关于公式的使用Formula

🍎前言:

​ 鄙人在做Excel数据导出时,一般不使用Excel的公式Formula,因为数据都在程序里获取到了,可以直接用(统计加总/平均…等也只需要做一个LINQ计算)setCellValue来设置.

​ 但一些需求要求固定使用Excel公式计算(可能是为了’美观’,亦或是’不信任程序的计算结果’),则需要在NPOI中设置Excel的公式.谨以此文介绍其用法以及一些细节.

1.数字计算公式的使用

像是SUM(),AVERAGE(),ABS()…

需要参与计算的Cell数据为数字类型[可以是空格,不能有字符串]

class Program
{
    static void Main(string[] args)
    {
        XSSFWorkbook workbook = new XSSFWorkbook();

        ISheet sheet = workbook.CreateSheet("TestSheet");

        IRow rowOne = sheet.CreateRow(0);
		
        //计算单元格需要为数字类型
        rowOne.CreateCell(0).SetCellValue(150.6);
        rowOne.CreateCell(1).SetCellValue(65.9);
        rowOne.CreateCell(2).SetCellValue(77);
        rowOne.CreateCell(3).SetCellValue(130);


        IRow rowTwo = sheet.CreateRow(1);

        rowTwo.CreateCell(0).SetCellValue(99.3);
        rowTwo.CreateCell(1).SetCellValue(81.3);
        rowTwo.CreateCell(2).SetCellValue(126.5);
        rowTwo.CreateCell(3).SetCellValue(67.1);

		//SetCellFormula方法用于设置公式
        rowOne.CreateCell(4).SetCellFormula("SUM(A1:D1)");
        rowTwo.CreateCell(4).SetCellFormula("SUM(A2:D2)");


        IRow rowthree = sheet.CreateRow(2);
        rowthree.CreateCell(0).SetCellFormula("AVERAGE(A1:A2)");
        rowthree.CreateCell(1).SetCellFormula("AVERAGE(B1:B2)");
        rowthree.CreateCell(2).SetCellFormula("AVERAGE(C1:C2)");
        rowthree.CreateCell(3).SetCellFormula("AVERAGE(D1:D2)");

        rowthree.CreateCell(4).SetCellFormula("AVERAGE(E1:E2)");

        FileStream fs = new FileStream("mytest.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);

        workbook.Write(fs);


        fs.Close();
    }
}

2.日期计算公式的使用

像是DATE(),EDATE()…

如果要输出日期格式的单元格,需额外指定DataFormat

(如果不指定日期格式,将以通用格式数字表达日期)

static void Main(string[] args)
{
    XSSFWorkbook workbook = new XSSFWorkbook();

    ISheet sheet = workbook.CreateSheet("TestSheet");

    IRow rowOne = sheet.CreateRow(0);

    //如果想要在Excel导出显示日期格式,你需要额外指定DataFormat
    XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
    IDataFormat dataFormat = workbook.CreateDataFormat();
    short format = dataFormat.GetFormat("yy/MM/dd");

    ICell date = rowOne.CreateCell(0);  date.SetCellValue(new DateTime(2024, 2, 28)); date.CellStyle = style;

    ICell edate = rowOne.CreateCell(1); edate.SetCellFormula("edate(A1,3)");


    style.SetDataFormat(format);

    edate.CellStyle = style;

    FileStream fs = new FileStream("mytest.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);

    workbook.Write(fs);

    fs.Close();

}

3.🌮🌏 动态设置公式内容中列号的确定和使用

前面的用例都是动态状态下的使用,如果在动态打印Excel的情况下使用公式,

则必须准确的获取到列号(like A,B,C,D…AA,BB,CC)

可以用如下代码获取列号:

public static string GetExcelColumnName(int columnNumber)
{
    StringBuilder columnName = new StringBuilder();

    while (columnNumber > 0)
    {
        int remainder = (columnNumber - 1) % 26;
        columnName.Insert(0, (char)('A' + remainder));
        columnNumber = (columnNumber - 1) / 26;
    }

    return columnName.ToString();
}

测试用例如下:

static void Main(string[] args)
{
    XSSFWorkbook workbook = new XSSFWorkbook();

    ISheet sheet = workbook.CreateSheet("TestSheet");

    IRow rowOne = sheet.CreateRow(0); 
    IRow rowTwo = sheet.CreateRow(1);
    IRow rowThree = sheet.CreateRow(2);
    IRow rowFour = sheet.CreateRow(3);

    List<double[]> sampledata = new List<double[]>() {

        new double[]{130.7,187.5,98.7,36.1},
        new double[]{56.9,88.3,91.5,120.2},
        new double[]{178.2,94.3,77.6,66.1},
        new double[]{98.23,203.2,182.1,142.2},

    };

    int start_col = 0;
    sampledata.ForEach(item => {

        rowOne.CreateCell(start_col).SetCellValue(item[0]);
        rowTwo.CreateCell(start_col).SetCellValue(item[1]);
        rowThree.CreateCell(start_col).SetCellValue(item[2]);
        rowFour.CreateCell(start_col).SetCellValue(item[3]);
        start_col++;
    });
	//动态使用公式
    rowOne.CreateCell(start_col).SetCellFormula($@"SUM(A1:{GetExcelColumnName(start_col)}1)");
    rowTwo.CreateCell(start_col).SetCellFormula($@"SUM(A2:{GetExcelColumnName(start_col)}2)");
    rowThree.CreateCell(start_col).SetCellFormula($@"SUM(A3:{GetExcelColumnName(start_col)}3)");
    rowFour.CreateCell(start_col).SetCellFormula($@"SUM(A4:{GetExcelColumnName(start_col)}4)");


    FileStream fs = new FileStream("mytest.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);

    workbook.Write(fs);

    fs.Close();
}