Apache POI(5):公式(formula)

Apache POI(5):公式(formula)

2016年08月01日 17:44:49

阅读数:1145
  1.  
    package com.hthk.iisz.util;
  2.  
     
  3.  
    import java.io.File;
  4.  
    import java.io.FileOutputStream;
  5.  
     
  6.  
    import org.apache.poi.xssf.usermodel.XSSFCell;
  7.  
    import org.apache.poi.xssf.usermodel.XSSFRow;
  8.  
    import org.apache.poi.xssf.usermodel.XSSFSheet;
  9.  
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  10.  
     
  11.  
    public class FormulaTest {
  12.  
     
  13.  
    public static void main(String[] args) throws Exception {
  14.  
    formula();
  15.  
    }
  16.  
     
  17.  
    public static void formula() throws Exception {
  18.  
    XSSFWorkbook workbook = new XSSFWorkbook();
  19.  
    XSSFSheet sheet = workbook.createSheet("formula");
  20.  
    XSSFRow row = sheet.createRow(1);
  21.  
    XSSFCell cell = row.createCell(1);
  22.  
    cell.setCellValue("A =");
  23.  
    cell = row.createCell(2);
  24.  
    cell.setCellValue(2);
  25.  
    row = sheet.createRow(2);
  26.  
    cell = row.createCell(1);
  27.  
    cell.setCellValue("B =");
  28.  
    cell = row.createCell(2);
  29.  
    cell.setCellValue(4);
  30.  
    row = sheet.createRow(3);
  31.  
    cell = row.createCell(1);
  32.  
    cell.setCellValue("Total =");
  33.  
    cell = row.createCell(2);
  34.  
    // create sum formula
  35.  
    cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
  36.  
    cell.setCellFormula("SUM(C2:C3)");
  37.  
     
  38.  
    cell = row.createCell(3);
  39.  
    cell.setCellValue("SUM(C2:C3)");
  40.  
    row = sheet.createRow(4);
  41.  
    cell = row.createCell(1);
  42.  
    cell.setCellValue("POWER =");
  43.  
    cell = row.createCell(2);
  44.  
    // create power formula
  45.  
    cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
  46.  
    cell.setCellFormula("POWER(C2,C3)");
  47.  
     
  48.  
    cell = row.createCell(3);
  49.  
    cell.setCellValue("POWER(C2,C3)");
  50.  
    row = sheet.createRow(5);
  51.  
    cell = row.createCell(1);
  52.  
    cell.setCellValue("MAX =");
  53.  
    cell = row.createCell(2);
  54.  
    // Create MAX formula
  55.  
    cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
  56.  
    cell.setCellFormula("MAX(C2,C3)");
  57.  
     
  58.  
    cell = row.createCell(3);
  59.  
    cell.setCellValue("MAX(C2,C3)");
  60.  
    row = sheet.createRow(6);
  61.  
    cell = row.createCell(1);
  62.  
    cell.setCellValue("FACT =");
  63.  
    cell = row.createCell(2);
  64.  
    // Create FACT formula
  65.  
    cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
  66.  
    cell.setCellFormula("FACT(C3)");
  67.  
     
  68.  
    cell = row.createCell(3);
  69.  
    cell.setCellValue("FACT(C3)");
  70.  
    row = sheet.createRow(7);
  71.  
    cell = row.createCell(1);
  72.  
    cell.setCellValue("SQRT =");
  73.  
    cell = row.createCell(2);
  74.  
    // Create SQRT formula
  75.  
    cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
  76.  
    cell.setCellFormula("SQRT(C5)");
  77.  
     
  78.  
    cell = row.createCell(3);
  79.  
    cell.setCellValue("SQRT(C5)");
  80.  
    workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
  81.  
    FileOutputStream out = new FileOutputStream(new File("formula.xlsx"));
  82.  
    workbook.write(out);
  83.  
    out.close();
  84.  
    System.out.println("fromula.xlsx written successfully");
  85.  
    }
  86.  
     
  87.  
    }

效果截图

工作总结 npoi 模板 导出公式 excel-LMLPHP

 
 
cell.SetCellType(CellType.FORMULA);
sheet.ForceFormulaRecalculation = true;

1.使用cell.setCellFormula方法重新在制定Cell里写入公式。

2.使用sheet.setForceFormulaRecalculation(true);方法强制让改Sheet执行公式。

EvaluateFormulaCell

#region 插入块
int rowsindex = 0;
for (int i = 0; i < lastarea.Count - 1; i++)
{
int tj = rowsindex + 70;
for (; rowsindex < tj; rowsindex++)
{
sheet.CreateRow(rowsindex + 70);   //有些行为null  统一创建一下
sheet.CopyRow(rowsindex, rowsindex + 70);
}
}
#endregion

//统计
if ((HSSFCell)sheet.GetRow(dataarea + 40).GetCell(3) == null) //一个道理 为空 创建
{
ICell cell = (HSSFCell)sheet.GetRow(dataarea + 40).CreateCell(3);
}
((HSSFCell)sheet.GetRow(dataarea + 40).GetCell(3)).SetCellType(CellType.FORMULA); //设置表达式格式

//((HSSFRow)sheet.GetRow(dataarea + 40)).GetCell(3).SetCellValue("=SUM(D" + (dataarea + 1) + ":D" + (dataarea + 40) + ")");
((HSSFRow)sheet.GetRow(dataarea + 40)).GetCell(3).SetCellFormula("SUM(D" + (dataarea + 1) + ":D" + (dataarea + 40) + ")"); //设置表达式的值 不用=

05-26 09:16