在Java-web项目开发过程中经常会遇到导入/导出Excel表格的业务场景。SXSSFWorkbook是apache基金会提供的Excel导出工具类，它允许导出大批量数量而不会导致内存溢出，因为在导出时，只有可配置的一部分行保存在内存中。
1、jar依赖：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
2、公共类
2.1、订单类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Order {
/**
* 订单编号
*/
private String orderId;
/**
* 订单名称
*/
private String orderName;
/**
* 订单类型，如 1：违章缴罚；2：六年免检；3：驾驶证补换；
* 4：行驶证补换；5：线上年审；6：警医邮预约；
*/
private Integer orderType;
/**
* 渠道标识，如 wxgzh：微信公众号；jfxcx：缴罚小程序；
* nsxcx：年审小程序；dhyx：电话营销；
*/
private String channelTag;
/**
* 实收金额
*/
private BigDecimal realAmount;
/**
* 其他字段……
*/
//……
}
2.2、工具类
public class ExcelUtil {
/**
* 导出Excel
* @param result
* @param headArray 表头数组（有序）
* @param dataArray 字段数组（有序）
* @return
*/
public static Workbook exportExcel(List<Map<String,Object>> result, String[] headArray, String[] dataArray){
//表示SXSSFWorkbook只会保留100条数据在内存中，避免内存溢出
Workbook wb = new SXSSFWorkbook(1000);
//1页面
Sheet sheet = wb.createSheet("Sheet1");
//2行标题
Row row = sheet.createRow(0);
Cell cell = null;
for (int i = 0; i < headArray.length; i++) {
cell = row.createCell(i);
cell.setCellValue(headArray[i]);
}
Map<String,Object> map = null;
for (int j = 0; j < result.size(); j++) {
map = result.get(j);
row = sheet.createRow(j + 1);
for(int k = 0; k < headArray.length; k++ ){
cell = row.createCell(k);
cell.setCellValue(map.get(dataArray[k]) + "");
}
}
return wb;
}
/**
* 读取Excel文件（List）
* @param is 输入流
* @param flag 表头字段（中文）
* @return
* @throws Exception
*/
public static List<List<String>> readExcelAsList(InputStream is, String[] flag) throws Exception{
Workbook wb = WorkbookFactory.create(is);
List<List<String>> result = new ArrayList<List<String>>();
//对excel表的各个sheet进行遍历
for(int numSheet = 0;numSheet<wb.getNumberOfSheets();numSheet++){
Sheet Sheet = wb.getSheetAt(numSheet);
if(Sheet==null){
continue;
}
Row row = Sheet.getRow(0);
if(null == row){
continue;
}
int min = row.getFirstCellNum();
int max = row.getLastCellNum();
int[] no = new int [flag.length];
for(int n = 0; n < flag.length; n++){
no[n] = -1;
}
for(int i = min; i < max; i++){
Cell cell = row.getCell(i);
for(int a = 0; a < flag.length; a++){
if(cell.getStringCellValue().equals(flag[a]))
no[a]=i;
}
}
for(int rowNum = 1; rowNum <= Sheet.getLastRowNum(); rowNum++){
//判断一行数据是否为空
int isBlank = 0;
row = Sheet.getRow(rowNum);
if (row != null){
List<String> rowList = new ArrayList<String>();
for(int a = 0; a < flag.length; a++){
if(no[a] != -1){
Cell cell = row.getCell(no[a]);
String cellValue = "";
if (null != cell) {
// 以下是判断数据的类型
switch (cell.getCellTypeEnum()) {
case NUMERIC: // 数字
if (HSSFDateUtil.isCellDateFormatted(cell)){
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
cellValue = formater.format(d);
}else {
DecimalFormat df = new DecimalFormat("0");
cellValue = df.format(cell.getNumericCellValue());
}
break;
case STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case FORMULA: // 公式
cellValue = cell.getCellFormula() + "";
break;
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
if (StringUtils.isBlank(cellValue)){
isBlank++;
}
rowList.add(cellValue);
}
}
if (isBlank != flag.length){
result.add(rowList);
}
}
}
}
return result;
}
/**
* 读取Excel文件（Map）
* @param is 输入流
* @param flag 表头字段（中文）
* @return
* @throws Exception
*/
public static List<Map<String,Object>> readExcelAsMap(InputStream is, String[] flag) throws Exception{
Workbook XssfWorkbook = WorkbookFactory.create(is);
List<Map<String,Object>> result = new ArrayList<>();
//对excel表的各个sheet进行遍历
for(int numSheet = 0;numSheet<XssfWorkbook.getNumberOfSheets();numSheet++){
Sheet XssfSheet = XssfWorkbook.getSheetAt(numSheet);
if(XssfSheet==null){
continue;
}
Row row = XssfSheet.getRow(0);
if(row == null){
continue;
}
int min = row.getFirstCellNum();
int max = row.getLastCellNum();
int[] no = new int [flag.length];
for(int n = 0; n < flag.length; n++){
no[n]=-1;
}
for(int i = min; i < max; i++){
Cell cell = row.getCell(i);
if(cell.getStringCellValue().equals("") || cell.getStringCellValue() == null){
max--;
}
for(int a = 0; a < flag.length; a++){
if(cell.getStringCellValue().equals(flag[a]))
no[a]=i;
}
}
for(int rowNum = 1; rowNum <= XssfSheet.getLastRowNum(); rowNum++){
row = XssfSheet.getRow(rowNum);
Map<String,Object> rowMap = new HashMap<>();
for(int a = 0; a < flag.length; a++){
if(no[a] != -1){
Cell cell = row.getCell(no[a]);
if(cell == null){
rowMap.put(flag[a],null);
continue;
}
String cellValue = "";
if (null != cell) {
// 以下是判断数据的类型
switch (cell.getCellTypeEnum()) {
case NUMERIC: // 数字
if (HSSFDateUtil.isCellDateFormatted(cell)){
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
cellValue = formater.format(d);
}else {
DecimalFormat df = new DecimalFormat("0");
cellValue = df.format(cell.getNumericCellValue());
}
break;
case STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case FORMULA: // 公式
cellValue = cell.getCellFormula() + "";
break;
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
rowMap.put(flag[a], cellValue);
}
}
result.add(rowMap);
}
}
return result;
}
}
3、Excel操作
3.1、导出
3.1.1、导出接口
@RestController
@RequestMapping("/apache/poi")
public class TestExportExcelController {
/**
* 导出Excel表格
* @param response
*/
@RequestMapping("/exportExcel")
public void exportExcel(HttpServletResponse response)throws Exception{
//获取导出数据
List<Order> list = testGetExportData();
// //创建excel数据对象
// //表示SXSSFWorkbook只会保留100条数据在内存中，避免内存溢出
// SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1000);
//
// //创建工作表
// SXSSFSheet sxssfSheet = sxssfWorkbook.createSheet("订单信息");
//
// //创建标题行
// SXSSFRow titleRow = sxssfSheet.createRow(0);
// titleRow.createCell(0).setCellValue("订单编号");
// titleRow.createCell(1).setCellValue("订单名称");
// titleRow.createCell(2).setCellValue("订单类型");
// titleRow.createCell(3).setCellValue("下单渠道");
// titleRow.createCell(4).setCellValue("订单金额");
//
// //创建数据行
// for(int i = 0; i < list.size(); i++){
// Order order = list.get(i);
// SXSSFRow simpleRow = sxssfSheet.createRow(i + 1);
// simpleRow.createCell(0).setCellValue(order.getOrderId() == null ? "" : order.getOrderId());
// simpleRow.createCell(1).setCellValue(order.getOrderName() == null ? "" : order.getOrderName());
// simpleRow.createCell(2).setCellValue(order.getOrderType() == null ? "" : String.valueOf(order.getOrderType()));
// simpleRow.createCell(3).setCellValue(order.getChannelTag() == null ? "" : order.getChannelTag());
// simpleRow.createCell(4).setCellValue(order.getRealAmount() == null? "" : String.valueOf(order.getRealAmount()));
// }
//使用工具类
List beanMapList = list.stream().map(order -> BeanMap.create(order)).collect(Collectors.toList());
String[] headArray = {"订单编号","订单名称","订单类型","下单渠道","订单金额"};
String[] dataArray = {"orderId","orderName","orderType","channelTag","realAmount"};
Workbook sxssfWorkbook = ExcelUtil.exportExcel(beanMapList, headArray, dataArray);
//设置响应头为下载文件
String fileName = "test.xlsx";
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="+ new String(fileName.getBytes(),"UTF-8"));
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
//表格对象注入stream流中
ServletOutputStream os = response.getOutputStream();
sxssfWorkbook.write(os);
os.flush();
os.close();
}
/**
* 获取导出数据（真正业务是从数据库中查询）
* @return
*/
private List<Order> testGetExportData() {
List<Order> list = new ArrayList<Order>(){
{
add(new Order("202101100001","粤XXXXXX违章缴罚订单",1,"wxgzh",new BigDecimal("200")));
add(new Order("202101100002","粤XXXXXX六年免检订单",2,"nsxcx",new BigDecimal("30")));
add(new Order("202101100003","粤XXXXXX驾驶证补换订单",3,"wxgzh",new BigDecimal("25")));
}
};
return list;
}
}
3.1.1、测试
启动项目，在浏览器请求：http://localhost:8080/apache/poi/exportExcel，下载文件如下：
3.2、导入
3.2.1、导出接口
@RestController
@RequestMapping("/apache/poi")
public class TestImportExcelController {
@RequestMapping("/testImportExport_1")
public void testImportExport1(@RequestParam(value = "file") MultipartFile file)throws Exception{
InputStream is = file.getInputStream();
String[] flag = {"订单编号","订单名称","订单类型","下单渠道","订单金额"};
List<List<String>> resultList = ExcelUtil.readExcelAsList(is, flag);
System.out.println("resultList: " + resultList);
}
@RequestMapping("/testImportExport_2")
public void testImportExport2(@RequestParam(value = "file") MultipartFile file)throws Exception{
InputStream is = file.getInputStream();
String[] flag = {"订单编号","订单名称","订单类型","下单渠道","订单金额"};
List<Map<String, Object>> resultMap = ExcelUtil.readExcelAsMap(is, flag);
System.out.println("resultMap: " + resultMap);
}
}
3.2.2、测试
启动项目，随便写个HTML，运行……
<!doctype html>
<html>
<head>
<title>first page</title>
<meta charset="utf-8"/>
</head>
<body>
<form action="http://localhost:8080/apache/poi/testImportExport_1" method="POST" enctype="multipart/form-data">
Excel_1: <input type="file" name="file" value=""/>
<input type="submit" value="提交">
</form></br>
<form action="http://localhost:8080/apache/poi/testImportExport_2" method="POST" enctype="multipart/form-data">
Excel_2: <input type="file" name="file" value=""/>
<input type="submit" value="提交">
</form></br>
</body>
</html>
resultList: [[202101100001, 粤XXXXXX违章缴罚订单, 1, wxgzh, 200], [202101100002, 粤XXXXXX六年免检订单, 2, nsxcx, 30], [202101100003, 粤XXXXXX驾驶证补换订单, 3, wxgzh, 25]]
resultMap: [{订单类型=1, 订单编号=202101100001, 订单名称=粤XXXXXX违章缴罚订单, 订单金额=200, 下单渠道=wxgzh}, {订单类型=2, 订单编号=202101100002, 订单名称=粤XXXXXX六年免检订单, 订单金额=30, 下单渠道=nsxcx}, {订单类型=3, 订单编号=202101100003, 订单名称=粤XXXXXX驾驶证补换订单, 订单金额=25, 下单渠道=wxgzh}]
1778
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
