DOM方式实现Excel导入


DOM解析Excel

在我们的工作场景中经常会遇到数据录入的需求, 有些批量数据录入太麻烦, 就需要用到批量导入的方式来提高效率. 这就涉及到读取Excel数据的技术. Appache Poi提供了DOM解析和SAX解析两种方式, 本篇主要记录自己工作中用到的DOM解析方式. 同样的, 入门api使用我不会详细写, 只是记录自己工作中封装过的工具方法, 方便以后翻看复习.

说明: 以下代码基于poi-3.17版本实现, poi-3.17及以上版本相比3.17以下版本样式设置的api改动比较大, 可能存在数据类型获取api过时或报错等, 请参考poi版本升级问题优化

1. 读取Excel公共方法

com.poi.util.PoiUtil#readExcel

/**
 * 上传excel文件到服务器后,从服务器获取上传的文件并解析文件的数据
 *
 * @param parseType 解析类型 DOM SAX
 * @param fileName 要解析的excel文件路径
 * @param rowReader 扩展接口,方便对解析的每行数据做处理
 * @return 返回解析的数据
 * @throws Exception
 */
public static List<List<String>> readExcel(String parseType, String fileName,
                                 ExcelRowReader rowReader) throws Exception {

    PoiUtil.validatorExcel(fileName);
    List<List<String>> rowsDataList = new ArrayList<List<String>>();
    int totalRows = 0;// 总行数
    if (CSISCONSTANT.EXCEL_PARSE_DOM.equals(parseType)) { // DOM解析
        ExcelReaderOfDom excelDom = new ExcelReaderOfDom();
        rowsDataList = excelDom.readExcelByDom(fileName);
    }

    totalRows = rowsDataList.size();
    logger.info(">>>>总行数: {}", totalRows);
    return rowsDataList;
}

2. 校验Excel文件格式

com.poi.util.PoiUtil#validatorExcel

/**
 * 校验excel文件格式
 *
 * @param fileName
 * @return
 * @throws MyException
 */
public static boolean validatorExcel(String fileName) throws MyException {

    if (StringUtils.isEmpty(fileName)) {

        throw new MyException(CSISERRORCODE.FILE_PATH_IS_NULL_ERROR_CODE,
                            CSISERRORCODE.FILE_PATH_IS_NULL_ERROR_INFO);
    }

    if (!(isExcel2003(fileName) || isExcel2007(fileName))) {

        throw new MyException(CSISERRORCODE.UPLOAD_EXCEL_EXTENSION_ERROR_CODE,
                            CSISERRORCODE.UPLOAD_EXCEL_EXTENSION_ERROR_INFO);
    }

    File file = new File(fileName);

    if (file == null || !file.exists()) {
        throw new MyException(CSISERRORCODE.FILE_NOT_EXIST_ERROR_CODE,
                            CSISERRORCODE.FILE_NOT_EXIST_ERROR_INFO);
    }

    return true;
}

3. 创建输入流读取Excel

com.poi.service.ExcelReaderOfDom#readExcelByDom

/**
 * 读取excel文件,支持2003和2007版本的读取
 * @param filePath
 * @return
 * @throws MyException
 */
public List<List<String>> readExcelByDom(String filePath) throws MyException {

    List<List<String>> dataList = new ArrayList<List<String>>();
    InputStream is = null;
    PoiUtil.validatorExcel(filePath); // 校验excel格式
    boolean isExcel2003 = true;

    if (PoiUtil.isExcel2007(filePath)) {

        isExcel2003 = false;
    }

    File file = new File(filePath);

    try {
        is = new FileInputStream(file);
        dataList = read(is, isExcel2003);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } finally {
        if (is != null) {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    return dataList;
}

4. 判断Excel文件类型

com.poi.util.PoiUtil#isExcel2007

/**
 * 校验excel文件是否是2007版本
 *
 * @param filePath
 * @return
 */
public static boolean isExcel2007(String filePath) {

    return filePath.matches("^.+\\.(?)(xlsx)$");
}

5. 创建Workbook解析Excel

com.poi.service.ExcelReaderOfDom#read(java.io.InputStream, boolean)

/**
 * 使用poi解析excel文档
 * @param is
 * @param isExcel2003
 * @return
 */
public List<List<String>> read(InputStream is, boolean isExcel2003) {

    List<List<String>> dataList = null;
    Workbook wb = null;
    try {

        if (isExcel2003) {
         // POIFSFileSystem的解析方式占用内存更小
            wb = new HSSFWorkbook(new POIFSFileSystem(is)); 
         // HSSFWorkbook 用来解析Excel2003, 解析上限65535行记录
            // wb = new HSSFWorkbook(is); 

        } else {
         // XSSFWorkbook 用来解析Excel2007, 解析上限104万记录, 如果EXCEL高于2007版本,
         // 用SXSSFWorkbook解析
            wb = new XSSFWorkbook(is); 
        }

        dataList = read(wb);

    } catch (IOException e) {
        e.printStackTrace();
    }

    return dataList;
}

com.poi.service.ExcelReaderOfDom#read(org.apache.poi.ss.usermodel.Workbook)

/**
 * 读取excel工作簿
 * 如果单元格的值是公式, 可以通过
 * {@link org.apache.poi.ss.usermodel.FormulaEvaluator#evaluate(Cell)}计算公式
 * {@link CellValue#formatAsString()} 将单元格数据转换为字符串数据返回
 * @param wb
 * @return
 */
public List<List<String>> read(Workbook wb) {

    List<List<String>> dataList = new ArrayList<List<String>>();
    int sheetNum = wb.getNumberOfSheets();
    logger.info("sheetNum:" + sheetNum);

    for (int i = 0; i < sheetNum; i++) {

    // logger.info("current sheet index:"+i);
        // 获取每一个sheet表
        Sheet sheet = wb.getSheetAt(i); 
    //    totalRows = sheet.getLastRowNum();
        totalRows = sheet.getPhysicalNumberOfRows(); // 获取sheet表的总行数

        if (totalRows >= 1 && sheet.getRow(0) != null) { // sheet表有数据
    //        totalCells = sheet.getRow(i).getLastCellNum();
         // 获取sheet表数据的列数-单元格总数
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells(); 
        }

        int rowIndex = 0; //记录每个sheet表格的当前行数
        for (Iterator<Row> rows = sheet.rowIterator(); rows.hasNext(); ) {

            Row row = rows.next();
            if (row == null) {
                continue;
            }

            if (rowIndex == 0) {
                // 每个sheet表格的标题行都不添加进来
                rowIndex++;
                continue;
            }
            // 如果不是第一个sheet表格, 后面sheet表格的第一行是标题行, 数据不应该添加进来,
         // 因为第一个sheet表格的第一行已经添加进去了
            if (i != 0 && rowIndex == 0) { 
            // 因为筛选掉了第一行标题行, rowIndex要到下一行:数据行, 不然rowIndex永远为1,
            // 后面的数据都无法添加进去
                rowIndex++; 
                continue;
            }

            List<String> rowList = new ArrayList<String>(); // 每一行记录

            // 迭代器方式会将空单元格直接过滤掉,导致数据和标题栏的字段对应不上,
         // 该为普通for循环将空单元格用空字符串占位
        // for (Iterator<Cell> cells = row.cellIterator(); cells.hasNext(); ) {
            for (int j = 0; j < totalCells; j++) {
            // Cell cell = cells.next();
                Cell cell = row.getCell(j);
                String cellValue = ""; // 每个单元格的值
                if (cell != null) {
               // 数据类型判断, 获取不同类型的数据
               CellType cellTypeEnum = cell.getCellTypeEnum();
               switch (cellTypeEnum) {

                  case NUMERIC: // 数字类型
                     // 日期格式的数字
                     if (HSSFDateUtil.isCellDateFormatted(cell)) {

                        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                        // 转换为日期字符串
                        cellValue = sdf.format(cell.getDateCellValue());
                     } else {
                        // 不是日期格式的数字,防止数字过长, 转换为字符串类型
                        cell.setCellType(CellType.STRING);
                        cellValue = String.valueOf(cell.getRichStringCellValue());
                     }
                     break;
                  case STRING: // 字符串
                     cellValue = cell.getStringCellValue();
                     break;
                  case BOOLEAN: // 布尔
                     cellValue = String.valueOf(cell.getBooleanCellValue());
                     break;
                  case FORMULA: // 公式
                     cellValue = String.valueOf(cell.getCellFormula());
                     break;
                  case BLANK: // 空
                     cellValue = "";
                     break;
                  case ERROR: // 错误
                     cellValue = "非法字符";
                     break;
                  default:
                     cellValue = "未知类型";
                     break;
               }
            }
                rowList.add(cellValue); // 添加单元格数据到每行集合
            }

            dataList.add(rowList);
            rowIndex++; // 每添加完一行记录, 当前行数+1
        }
    }

    return dataList;
}

6. 测试Excel导入

6.1 测试代码

com.test.poi.PoiExcelTest#testReadExcel

/**
 * 测试上传excel
 * 一般场景是读取用户上传的excel文件,解析里面的数据,存入db
 *
 * @throws Exception
 */
@Test
public void testReadExcel() throws Exception {

    String filePath = "D:\\study\\excel/poi_my.xls"; // 2003版

//  String filePath = "D:\\study\\excel/poi_my.xlsx"; // 2007版

    String parseType = "DOM";

    long start = System.currentTimeMillis();

    List<List<String>> dataList = PoiUtil.readExcel(parseType, filePath, 
                                                   new ExcelReaderImpl());

    long end = System.currentTimeMillis();

    /**
     * 65536行记录耗时、cpu利用率比较
     * DOM 2003版 986ms  77%
     * DOM 2007版 4429ms 80%
     */
    logger.info("解析excel耗时:" + (end - start) + "ms!");
    logger.info("dataList size is:" + dataList.size());
    dataList.forEach(System.out::println);
}

6.1 测试结果

通过控制台日志, 查看读取Excel数据的效果

image-20210917220957743

7. Web端测试

7.1 编写controller

com.poi.controller.ExcelController

package com.poi.controller;

import com.constant.CSISCONSTANT;
import com.exception.MyException;
import com.poi.entity.Employee;
import com.poi.service.ExcelReaderImpl;
import com.poi.util.PoiTemplateUtil;
import com.poi.util.PoiUtil;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.Part;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;

/**
 * 类描述:文件上传下载
 * @Author wang_qz
 * @Date 2021/8/14 21:07
 * @Version 1.0
 */
@Controller
@RequestMapping("/excel")
public class ExcelController {

    @RequestMapping("/toExcelPage2")
    public String todownloadPage2() {
        return "excelPage2";
    }

    /**
     * 经过服务器临时上传目录中转的实现
     * @param file
     * @return
     * @throws Exception
     */
    @PostMapping("/uploadExcel2")
    @ResponseBody
    public String uploadExcel2(@RequestParam("file") MultipartFile file) 
       throws Exception {
        // 上传文件名称, 因为当前读取excel的工具类中是去服务器中去读取文件的, 
       // 所以需要先将导入的文件上传到服务器
        String filename = file.getOriginalFilename();
        // 解决文件名中文乱码问题
        filename = new String(filename.getBytes(), "utf-8");
        String uploadName = CSISCONSTANT.TEMP_UPLOAD_DIR + filename;
        FileOutputStream write = new FileOutputStream(uploadName);
        InputStream read = file.getInputStream();
        byte[] bys = new byte[1024];
        while (read.read(bys) != -1) {
            write.write(bys, 0, bys.length);
            write.flush();
        }
        write.close();

        // 读取上传的Excel并解析数据 DOM方法解析
        List<List<String>> dataList = PoiUtil.readExcel(
           CSISCONSTANT.EXCEL_PARSE_DOM, uploadName, new ExcelReaderImpl());
        dataList.forEach(System.out::println);
        return "upload successful!";
    }
}

7.2 编写jsp页面

webapp/WEB-INF/jsp/excelPage2.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>测试excel文件下载</title>
</head>
<body>
<h3>点击下面链接, 进行excel文件下载</h3>
<a href="<c:url value='/excel/downloadExcel2'/>">Excel文件下载</a>
<hr/>
<hr/>
<h3>点击下面按钮, 进行excel文件上传</h3>
<form action="<c:url value='/excel/uploadExcel2'/>" method="post" enctype="multipart/form-data">
    <input type="file" name="file"/><br/>
    <input type="submit" value="上传Excel"/>
</form>
</body>
</html>

启动tomcat, 访问http://localhost:8080/excel/toExcelPage2, 进入测试页面

image-20210917214557918

7.3 测试结果

通过控制台日志, 查看读取Excel文件效果

image-20210917220957743

相关推荐

数据分流写入Excel

Poi版本升级优化

StringTemplate实现Excel导出

Poi模板技术

SAX方式实现Excel导入

Poi实现Excel导出

EasyExcel学习笔记

EasyPoi学习笔记


文章作者: 王子
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 王子 !
评论
  目录