POI基本操作

POI基本操作

导入依赖

<!--xls(03)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<!--xlsx(07)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

POI 写入操作

//03版本Excel  最多支持65535行
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("hah");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("今日新增");
Cell cell1 = row.createCell(1);
cell1.setCellValue(new Date());

Row row1 = sheet.createRow(1);
Cell cell_1 = row1.createCell(0);
cell_1.setCellValue("每日新闻");
Cell cell1_2 = row1.createCell(1);
cell1_2.setCellValue("666");
FileOutputStream out = new FileOutputStream(new File(PATH,"11.xls"));
workbook.write(out);
out.close();



  //07版本Excel
  Workbook workbook = new XSSFWorkbook();
  Sheet sheet = workbook.createSheet("hah");
  Row row = sheet.createRow(0);
  Cell cell = row.createCell(0);
  cell.setCellValue("今日新增");
  Cell cell1 = row.createCell(1);
  cell1.setCellValue(new Date());

  Row row1 = sheet.createRow(1);
  Cell cell_1 = row1.createCell(0);
  cell_1.setCellValue("每日新闻");
  Cell cell1_2 = row1.createCell(1);
  cell1_2.setCellValue("666");
  FileOutputStream out = new FileOutputStream(new File(PATH,"22.xlsx"));
  workbook.write(out);
  out.close();



Workbook workbook = new SXSSFWorkbook();
 Sheet sheet = workbook.createSheet("hah");
 Row row = sheet.createRow(0);
 Cell cell = row.createCell(0);
 cell.setCellValue("今日新增");
 Cell cell1 = row.createCell(1);
 cell1.setCellValue(new Date());

 Row row1 = sheet.createRow(1);
 Cell cell_1 = row1.createCell(0);
 cell_1.setCellValue("每日新闻");
 Cell cell1_2 = row1.createCell(1);
 cell1_2.setCellValue("666");
 FileOutputStream out = new FileOutputStream(new File(PATH,"33.xlsx"));
 workbook.write(out);
 //清理临时文件
 ((SXSSFWorkbook)workbook).dispose();
 out.close();

POI读取操作

//获取一个文件流
        InputStream inputStream = new FileInputStream(new File(PATH,"会员消费商品明细表.xls"));
        //创建一个工作表，读取文件
        Workbook workbook=new HSSFWorkbook(inputStream);
        //获取工作簿名称

        Sheet sheet = workbook.getSheetAt(0);
        //获取第一行标题
        Row row_title = sheet.getRow(0);
        if (row_title!=null){
            //获取一行有多少列
            int cell_counts = row_title.getPhysicalNumberOfCells();
            for (int cellNum=0;cellNum<cell_counts;cellNum++){
                //遍历获取每一个单元格
                Cell cell = row_title.getCell(cellNum);
                if (cell !=null){
                    //获取单元格内元素类型
                    int cellType = cell.getCellType();
                    //获取单元格内元素内容
                    String cell_value = cell.getStringCellValue();
                    System.out.print(cellType+cell_value+" | ");
                }

            }


            //获取除标题行外的所有行
            int rows = sheet.getPhysicalNumberOfRows();
            for(int row_num=1;row_num< rows;row_num++){
                //遍历获取所有行
                Row row_data = sheet.getRow(row_num);
                if(row_data!=null){
                    //获取行有多少列
                    int cell_count = row_data.getPhysicalNumberOfCells();

                    for(int cell_num=0;cell_num<cell_count;cell_num++){
                        //遍历得到每一个单元格
                        Cell cell = row_data.getCell(cell_num);
                        if(cell!=null){
                            int cellType = cell.getCellType();
                            String cellValue = " ";
                            switch (cellType){
                                case HSSFCell.CELL_TYPE_STRING:
                                    System.out.print("[String]");
                                    cellValue = cell.getStringCellValue();
                                    break;
                                case HSSFCell.CELL_TYPE_BOOLEAN:
                                    System.out.print("[Boolean]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                    break;

                                case HSSFCell.CELL_TYPE_BLANK:
                                    System.out.print("[Blank]");

                                    break;
                                case HSSFCell.CELL_TYPE_NUMERIC:
                                    System.out.print("[numergic]");
                                    if(HSSFDateUtil.isCellDateFormatted(cell)){
                                        Date date = cell.getDateCellValue();
                                  cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                    }else {
                                        //不是日期格式，防止数字过长
                                        System.out.print("[转换为字符串]");
                                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                        cellValue=cell.toString();
                                    }
                                    break;
                                case HSSFCell.CELL_TYPE_ERROR:
                                    System.out.println("[数据类型错误]");
                                    break;
                            }
                            System.out.println(cellValue);
                        }
                    }

                }

操作公式

FileInputStream inputStream = new FileInputStream(new File(PATH,"计算公式.xls"));
Workbook workbook = new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);

Row row = sheet.getRow(4);
Cell cell = row.getCell(0);
HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
int cellType = cell.getCellType();
switch (cellType){
    case Cell.CELL_TYPE_FORMULA:
        String formula = cell.getCellFormula();
        System.out.println(formula);

        //计算
        CellValue evaluate = formulaEvaluator.evaluate(cell);
        String cellValue = evaluate.formatAsString();
        System.out.println(cellValue);
        break;
}