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数据的效果
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, 进入测试页面
7.3 测试结果
通过控制台日志, 查看读取Excel文件效果
相关推荐
数据分流写入Excel 待补充
个人博客
欢迎各位访问我的个人博客: https://www.crystalblog.xyz/
备用地址: https://wang-qz.gitee.io/crystal-blog/