SpringBoot项目实战（六）

SpringBoot实战之集成POI

1.熟悉poi以及读写操作

1.新建一个模块导入依赖

注意：一般得excel都会有2003和2007两个版本，xls03和xlsx07

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.michael</groupId>
    <artifactId>poi-learn</artifactId>
    <version>1.0-SNAPSHOT</version>
    <!--导入依赖-->
    <dependencies>

        <!--xls03-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <!--xlsx07-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- 日期格式化工具 -->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.1</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

    </dependencies>

</project>

2.读文件练习

a.xlsx版本练习

package com.michael;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;

public class ExcelReadPractice {

    String targetFile = "进件平台一期配置结构(进件表配置).xlsx";
    String currentPath = System.getProperty("user.dir");
    String targetPath =  currentPath + File.separator + targetFile;


    @Test
    public void Test2007() throws Exception {

        //define the target file
        System.out.println("the current project path: "+ targetPath);
        FileInputStream fileInputStream = new FileInputStream(targetPath);

        //the the workbook of the target file
        XSSFWorkbook xssfSheets = new XSSFWorkbook(fileInputStream);
        //get the target sheet
        XSSFSheet sheetAt = xssfSheets.getSheetAt(0);

        //get the value of 1-column 1-row
        XSSFRow row = sheetAt.getRow(6);
        XSSFCell cell = row.getCell(1);

        System.out.println("get the value of 1-column 6-row:"+cell.getStringCellValue());

        fileInputStream.close();
    }
}

b.xls练习

    @Test
    public void Test2003() throws IOException {
        String targetPath =  currentPath + File.separator + targetFile03;
        System.out.println("target path is :"+targetPath);
        FileInputStream fileInputStream = new FileInputStream(targetPath);

        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);

        HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);

        HSSFRow row = sheetAt.getRow(1);
        HSSFCell cell = row.getCell(1);

        System.out.println("get the value of 1-column 1-row:"+cell.getStringCellValue());

        fileInputStream.close();
    }

c.整表数据读取练习

    @Test
    public void testCell() throws IOException {
        System.out.println("the current project path: "+ targetPath);
        FileInputStream fileInputStream = new FileInputStream(targetFile);

        XSSFWorkbook xssfSheets = new XSSFWorkbook(fileInputStream);
        XSSFSheet sheet = xssfSheets.getSheet("基础资料与当前产品字段映射关系");

        // get the row title of the target sheet
        XSSFRow rowTitle = sheet.getRow(0);
        if (rowTitle != null){
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                XSSFCell cell = rowTitle.getCell(cellNum);
                if(cell != null){
                    String stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue+"|");
                }
            }
            System.out.println();
        }
        
        // get all content of the target sheet
        int rowSum = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowSum; rowNum++) {
            //row
            XSSFRow row = sheet.getRow(rowNum);
            if (row != null){
                
                int cells = row.getPhysicalNumberOfCells();
                // get all columns of rowNum
                for (int cellNum = 0; cellNum < cells; cellNum++) {
                    System.out.print("["+(rowNum+1)+"-"+(cellNum+1)+"]");
                    XSSFCell cell = row.getCell(cellNum);

                    if(cell != null){
                        int cellType = cell.getCellType();
                        String cellValue = "";

                        switch  (cellType ) {
                            case XSSFCell.CELL_TYPE_STRING:
                                System.out.print("[Sring]");
                                cellValue = cell.getStringCellValue();
                                break;
                            case XSSFCell.CELL_TYPE_BOOLEAN:
                                System.out.print("[Boolean]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case XSSFCell.CELL_TYPE_BLANK:
                                System.out.print("[Blank]");
                                break;
                            case XSSFCell.CELL_TYPE_NUMERIC:
                                System.out.print("[Numeric]");
                                // judege if it is date

                                if(DateUtil.isCellDateFormatted(cell)){
                                    System.out.print("[date]");
                                    Date dateCellValue = cell.getDateCellValue();
                                    cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd");
                                }else {
                                    System.out.print("[change to string]");
                                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case XSSFCell.CELL_TYPE_ERROR:
                                System.out.print("data type error");
                                break;
                        }
                        System.out.print(cellValue+"|");
                    }
                }
                System.out.println();
            }
        }
        fileInputStream.close();
    }

d.读取公式并执行

    @Test
    public void testFormula() throws Exception {

        String targetPath =  currentPath + File.separator + targetFileFormula;
        System.out.println("the current project path: "+ targetPath);
        FileInputStream is = new FileInputStream(targetPath);
        // 读取到Workbook  03
        Workbook workbook = new HSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);  // 获取表

        // 读取 5,1   题库（全部都是Excel录入的）
        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);

        // 公式计算器 eval
        HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

        // formulaEvaluator 输出单元的内容
        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;
        }
    }

3.写文件练习

a.xls版本练习

package com.michael;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWritePractice {

    String targetFile03 = "testWrite003.xls";
    String currentPath = System.getProperty("user.dir");

    @Test
    public void testWrite2003() throws IOException {
        // create a new workbook of an excel file
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        // create a new sheet
        HSSFSheet sheet = hssfWorkbook.createSheet("sheet-create");
        // create a new row_1
        HSSFRow row = sheet.createRow(0);

        // create the values for the new row_1
        HSSFCell cell01 = row.createCell(0);
        cell01.setCellValue("register today");
        HSSFCell cell02 = row.createCell(1);
        cell02.setCellValue("999");

        // create a new row_2
        HSSFRow row2 = sheet.createRow(1);

        // create the values for the new row_2
        HSSFCell cell201 = row2.createCell(0);
        cell201.setCellValue("summary time");

        HSSFCell cell202 = row2.createCell(1);
        cell202.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

        // create a file
        String targetPath =  currentPath + File.separator + targetFile03;
        FileOutputStream fileOutputStream = new FileOutputStream(targetPath);

        // write the new data into the created file
        hssfWorkbook.write(fileOutputStream);
        
        // close the file stream
        fileOutputStream.close();
    }
}

b.xlsx版本练习

    @Test
    public void testWrite2007() throws IOException {
        // create a new workbook of excel
        XSSFWorkbook xssfSheets = new XSSFWorkbook();

        // create a new sheet for the workbook
        XSSFSheet sheet = xssfSheets.createSheet("sheet-new-01");

        // create a  new row_1
        XSSFRow row1 = sheet.createRow(0);

        // write the values for the created row_1
        XSSFCell cell01 = row1.createCell(0);
        cell01.setCellValue("create time");
        XSSFCell cell02 = row1.createCell(1);
        cell02.setCellValue("summary count");

        // create a new row_2
        XSSFRow row2 = sheet.createRow(1);

        // write the values for the created row_2
        XSSFCell cell201 = row2.createCell(0);
        cell201.setCellValue(new DateTime().toString("yyyy-MM-dd"));
        XSSFCell cell202 = row2.createCell(1);
        cell202.setCellValue("888");

        // create a file
        String targetPath =  currentPath + File.separator + targetFile07;
        FileOutputStream fileOutputStream = new FileOutputStream(targetPath);

        // write the data into the target file
        xssfSheets.write(fileOutputStream);

        fileOutputStream.close();
    }

c.excel2003版大数据写入

    /*
        the xls only supports 65536 rows data,
        there will be an error if the count of rows beyounds 65536
    * */
    @Test
    public void testWriteBigData2003() throws IOException {
        long startTime = System.currentTimeMillis();
        // create a workbook of the excel 2003
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

        // create an sheet for the workbook
        HSSFSheet test_the_big_data = hssfWorkbook.createSheet("test the big data");

        // create 65536 rows
        System.out.println("start to write the data");
        for (int i = 0; i < 65536; i++) {
            HSSFRow row = test_the_big_data.createRow(i);

            // write data for each row
            for (int rowNum = 0; rowNum < 10; rowNum++) {
                HSSFCell cell = row.createCell(rowNum);
                cell.setCellValue(rowNum);
            }
        }

        // create a new file
        String targetPath =  currentPath + File.separator + targetFile03;
        FileOutputStream fileOutputStream = new FileOutputStream(targetPath);

        // write all data into the file
        hssfWorkbook.write(fileOutputStream);

        fileOutputStream.close();

        System.out.println("finish to write the data");
        long endTime = System.currentTimeMillis();
        System.out.println("writing data costs "+ (double)(endTime-startTime)/1000 + " seconds");
    }

超过数据容量后异常

d.excel2007版大数据写入

    /*
        there is no limit in xlsx
        But be careful because it is easy to create the stack over flow
    * */
    @Test
    public void testWriteBigdata2007() throws IOException {
        long startTime = System.currentTimeMillis();
        System.out.println("start to write the data");
        // create a new workbook of an excel
        XSSFWorkbook xssfSheets = new XSSFWorkbook();

        // create a new sheet for the created workbook
        XSSFSheet test_the_big_data = xssfSheets.createSheet("test the big data");

        // create 1 million rows data for the created sheet
        for (int i = 0; i < 100_000; i++) {
            XSSFRow row = test_the_big_data.createRow(i);

            // write the data into each row
            for (int rowNum = 0; rowNum < 10; rowNum++) {
                XSSFCell cell = row.createCell(rowNum);
                cell.setCellValue(rowNum);
            }
        }

        // create a new file
        String targetPath =  currentPath + File.separator + targetFile07;
        FileOutputStream fileOutputStream = new FileOutputStream(targetPath);

        // write all data into the target file
        xssfSheets.write(fileOutputStream);

        fileOutputStream.close();

        System.out.println("finish to write all the data");

        long endTime = System.currentTimeMillis();
        System.out.println("Writing all data costs " + (double)(endTime-startTime)/1000 + " seconds");

    }

e.使用SXSSF来处理大数据得写入（速度较快）

    /*
        There is SXSSF which is faster when writing data
    * */
    @Test
    public void testWriteBigdataSXSSF() throws IOException{
        long startTime = System.currentTimeMillis();
        System.out.println("start to write the data....");
        // create a new workbook of the excel
        // the system will create a temporary area when created a new workbook
        // the system would write every 100 rows as a batch into the file
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();

        // create a new sheet for the created workbook
        Sheet test_the_faster_way = sxssfWorkbook.createSheet("test the faster way");

        // create 1 million rows for the created sheet
        for (int i = 0; i < 100_000; i++) {
            Row row = test_the_faster_way.createRow(i);
            // write data for each row
            for (int rowNum = 0; rowNum < 10; rowNum++) {
                Cell cell = row.createCell(rowNum);
                cell.setCellValue(rowNum);
            }

        }

        // create the file
        String targetPath =  currentPath + File.separator + targetFileFast;
        FileOutputStream fileOutputStream = new FileOutputStream(targetPath);

        // write all data into the file
        sxssfWorkbook.write(fileOutputStream);

        fileOutputStream.close();

        // suggest to clear the temporary data
        sxssfWorkbook.dispose();

        System.out.println("finish to write all data");
        long endTime = System.currentTimeMillis();
        System.out.println("writing all data costs "+(double)(endTime-startTime)/1000+" seconds");
    }

4.封装读文件的工具类

package com.michael;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

public class ExcelImportUtil {

    private HSSFFormulaEvaluator formulaEvaluator;
    private HSSFSheet sheet;
    private String pattern;// 日期格式

    public ExcelImportUtil() {
        super();
    }

    public ExcelImportUtil(InputStream is) throws IOException {
        this(is, 0, true);
    }

    public ExcelImportUtil(InputStream is, int sheetIndex) throws IOException {
        this(is, sheetIndex, true);
    }

    public ExcelImportUtil(InputStream is, int sheetIndex, boolean evaluateFormular) throws IOException {
        super();
        HSSFWorkbook workbook = new HSSFWorkbook(is);
        this.sheet = workbook.getSheetAt(sheetIndex);
        if (evaluateFormular) {
            this.formulaEvaluator = new HSSFFormulaEvaluator(workbook);
        }
    }

    public String getCellValue(Cell cell) throws Exception {

        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC://0

                if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
                    Date date = cell.getDateCellValue();
                    if (pattern != null) {
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        return sdf.format(date);
                    } else {
                        return date.toString();
                    }
                } else {
                    // 不是日期格式，则防止当数字过长时以科学计数法显示
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    return cell.toString();
                }

            case Cell.CELL_TYPE_STRING://1
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_FORMULA://2

                if (this.formulaEvaluator == null) {//得到公式
                    return cell.getCellFormula();
                } else {//计算公式
                    CellValue evaluate = this.formulaEvaluator.evaluate(cell);
                    return evaluate.formatAsString();
                }
            case Cell.CELL_TYPE_BLANK://3
                //注意空和没有值不一样，从来没有录入过内容的单元格不属于任何数据类型，不会走这个case
                return "";
            case Cell.CELL_TYPE_BOOLEAN://4
                return String.valueOf(cell.getBooleanCellValue());
            case Cell.CELL_TYPE_ERROR:
            default:
                throw new Exception("Excel数据类型错误");
        }
    }
}