POI

POI

1.基本功能

  1. HSSF 操作Excel 03,最大支持65536行,xls。
  2. XSSF 操作Excel OOXML 07版,支持无线行数,xlsx。
  3. HWPF 操作Word。
  4. HSLF 操作PowerPoint。
  5. HDGF 操作Visio。

2.POI和EasyExcel

  1. POI会存在内查溢出的问题,而easyExcel可以有效的避免这个问题。
  2. POI操作Excel是将初级加载到内存,所以会有内存溢出的问题,而easyExcel是一行一行的操作数据。
  3. EasyExcel Github源码地址,https://github.com/alibaba/easyexcel
  4. EasyExcel文档地址,https://www.yuque.com/easyexcel/doc/easyexcel

3.HSSFWorkbook,03,Write

  1. HSSFWorkbook只能下65536行数据,但是操作时间比XSSFWorkbook更快。
/**
 * 创建一个 03版本的 excel,并写数据。
 * 后缀 xls。
 * 03使用 HSSFWorkbook
 * 07使用 XSSFWorkbook
 * 07可以有无线行,SXSSFWorkbook是XSSFWorkbook的升级版。
 
 * 当写入的行数超过65536时,出现异常
 * Exception in thread "main" java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
 * @param path
 */
public static void writeExcel03(String path) throws Exception {
    // 1 创建一个工作簿
    Workbook workbook = new HSSFWorkbook();

    // 2 创建一个工作表
    Sheet sheet = workbook.createSheet();

    // 3 创建一行
    Row row1 = sheet.createRow(0);
    // 4 创建一列,并写入值
    Cell cell1 = row1.createCell(0);
    cell1.setCellValue("用户名");

    Cell cell2 = row1.createCell(1);
    cell2.setCellValue("密码");

    Row row2 = sheet.createRow(1);
    Cell c1 = row2.createCell(0);
    c1.setCellValue("tom");

    Cell c2 = row2.createCell(1);
    c2.setCellValue("123");

    FileOutputStream fileOutputStream = new FileOutputStream(new File(path + "03.xls"));
    // Excel输出
    workbook.write(fileOutputStream);
    fileOutputStream.close();
}

4.XSSFWorkbook,07,Write

  1. XSSFWorkbook可以写入无限行的数据,但是操作数据比HSSFWorkbook需要的时间更长。
/**
 * 创建一个 07版本的 excel,并写数据。
 * 后缀 xlsx。
 * 03使用 HSSFWorkbook
 * 07使用 XSSFWorkbook
 * 07可以有无限行,SXSSFWorkbook是XSSFWorkbook的升级版。
 * @param path
 */
public static void writeExcel07(String path) throws Exception {
    // 1 创建一个工作簿
    Workbook workbook = new XSSFWorkbook();

    // 2 创建一个工作表
    Sheet sheet = workbook.createSheet();

    // 3 创建一行
    Row row1 = sheet.createRow(0);
    // 4 创建一列,并写入值
    Cell cell1 = row1.createCell(0);
    cell1.setCellValue("用户名");

    Cell cell2 = row1.createCell(1);
    cell2.setCellValue("密码");

    Row row2 = sheet.createRow(1);
    Cell c1 = row2.createCell(0);
    c1.setCellValue("tom");

    Cell c2 = row2.createCell(1);
    c2.setCellValue("123");

    FileOutputStream fileOutputStream = new FileOutputStream(new File(path + "07.xlsx"));
    // Excel输出
    workbook.write(fileOutputStream);
    fileOutputStream.close();
}

5.SXSSFWorkbook,XSSFWorkbook的升级

/**
 * 使用XSSFWorkbook的升级 SXSSFWorkbook来更快的写入数据
 * 需要3秒
 * @param path
 * @throws Exception
 */
public static void writeExcel07Test02(String path) throws Exception {
    long start = System.currentTimeMillis();
    // 1 创建一个工作簿
    SXSSFWorkbook workbook = new SXSSFWorkbook();

    // 2 创建一个工作表
    Sheet sheet = workbook.createSheet();

    for (int i = 0;i < 65536;i++) {
        Row row = sheet.createRow(i);
        for (int j = 0;j < 10;j++) {
            Cell cell = row.createCell(j);
            cell.setCellValue(j);
        }
    }

    FileOutputStream fileOutputStream = new FileOutputStream(new File(path + "07.xlsx"));
    // Excel输出
    workbook.write(fileOutputStream);
    fileOutputStream.close();

    long end = System.currentTimeMillis();
    System.out.println((end - start) / 1000);
}

6.从Excel读取数据

/**
 * 从Excel中读取数据
 */
public static void readExcel03(String p) throws Exception {
    FileInputStream inputStream = new FileInputStream(p + "03.xls");

    HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
    HSSFSheet sheet = workbook.getSheetAt(0);

    String value;
    int rowCount = sheet.getPhysicalNumberOfRows();
    for (int i = 0;i < rowCount;i++) {
        HSSFRow row = sheet.getRow(i);
        int cellCount = row.getPhysicalNumberOfCells();

        for (int j = 0;j < cellCount;j++) {
            HSSFCell cell = row.getCell(j);

            CellType cellType = cell.getCellType();
            switch (cellType) {
                case STRING:
                    value = cell.getStringCellValue();
                    System.out.println("字符串 - " + value);
                break;
                case NUMERIC:
                    // 日期类型
                    if (DateUtil.isCellDateFormatted(cell)) {
                        value = new DateTime(cell.getDateCellValue()).toString("yyyy-MM-dd");
                        System.out.println("日期 - " + value);
                    }else {
                        double d = cell.getNumericCellValue();
                        System.out.println("数字类型 - " + d);
                    }
                    break;
                case BLANK:
                    System.out.println("空 - " + cell.getStringCellValue());
                    break;
                case BOOLEAN:
                    System.out.println("布尔 - " + cell.getBooleanCellValue());
                    break;
                case ERROR:
                    System.out.println("错误 - " + cell.getErrorCellValue());
                    break;
                case _NONE:
                    System.out.println("none - " + cell.getStringCellValue());
                case FORMULA:
                    System.out.println("formula");
                default:
                    System.out.println("不知道的类型");
            }
        }
    }
    inputStream.close();
}

7.当Excel单元格式公式的时候,进行相关计算

/**
 * 计算Excel中的公式
 * @param p
 * @throws Exception
 */
public static void readExcel07(String p) throws Exception {
    FileInputStream inputStream = new FileInputStream(p + "07.xlsx");

    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);

    Row row = sheet.getRow(0);
    Cell cell = row.getCell(3);

    XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);

    switch (cell.getCellType()) {
        case FORMULA:
            String value = cell.getCellFormula();

            System.out.println(value);

            CellValue cellValue = evaluator.evaluate(cell);
            System.out.println("公式计算后的值 = " + cellValue.formatAsString());
            break;
    }
}

8.EasyExcel简单使用

  1. EasyExcel读写本地数据。
@Slf4j
public class EasyExcelUtils {

    /**
     * 读取数据
     */
    public static <T> List<T> readData(InputStream is, Class<T> type) {

        return EasyExcelFactory.read(is).head(type).sheet().doReadSync();
    }

    /**
     * 读取数据,并且将每行的数据输出。
     */
    public static <T> List<T> readDataAndOut(InputStream is, Class<T> type) {

        ReadListener<User> readListener = new ReadListener<>() {

            @Override
            public void invoke(User data, AnalysisContext context) {
                log.info("data is {}", data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("read end");
            }
        };

        return EasyExcelFactory.read(is, readListener).head(type).sheet().doReadSync();
    }


    public static <T> void writeData(OutputStream os, List<T> data, Class<T> type) {

        EasyExcelFactory.write(os).head(type).sheet().doWrite(data);
    }
}

/**
 * EasyExcel本地读写数据
 */
public class EasyExcelTest {

    public static void main(String[] args) throws FileNotFoundException {
        test03();
    }

    public static void test03() throws FileNotFoundException {
        FileOutputStream os = new FileOutputStream("F:\\数据\\2.xls");

        List<User> users = new ArrayList<>();
        users.add(new User("tom", "1234", 1, "01", new Date()));
        users.add(new User("alice", "1234", 2, "01", new Date()));
        users.add(new User("bob", "1234", 3, "01", new Date()));

        EasyExcelUtils.writeData(os, users, User.class);
    }

    public static void test02() throws FileNotFoundException {
        FileInputStream inputStream = new FileInputStream("F:\\数据\\2.xls");

        List<User> users = EasyExcelUtils.readDataAndOut(inputStream, User.class);
        System.out.println(users);
    }

    public static void test01() throws FileNotFoundException {
        FileInputStream inputStream = new FileInputStream("F:\\数据\\1.xls");

        List<User> users = EasyExcelUtils.readData(inputStream, User.class);
        System.out.println(users);
    }
}
  1. Web中读写数据。
@Slf4j
@RestController
public class UserController {

    @PostMapping("/read/data")
    public List<User> readData(@RequestPart("file") MultipartFile file) throws IOException {
        List<User> users = EasyExcelUtils.readData(file.getInputStream(), User.class);
        log.info("data is {}", users);
        return users;
    }

    @GetMapping("/download/data")
    public void downloadData(HttpServletResponse response) throws IOException {

        List<User> users = new ArrayList<>();
        users.add(new User("tom", "1234", 1, "01", new Date()));
        users.add(new User("alice", "1234", 2, "01", new Date()));
        users.add(new User("bob", "1234", 3, "01", new Date()));

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");

        String fileName = UUID.fastUUID().toString() + ExcelTypeEnum.XLS.getValue();
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);

        EasyExcelUtils.writeData(response.getOutputStream(), users, User.class);
    }
}
posted @ 2021-09-21 21:11  行稳致远方  阅读(20)  评论(0)    收藏  举报