spring boot整合poi实现excel文件导入导出实战
今天科比离去，今天肺炎病毒持续肆虐。。。
意识到生命的脆弱，今天我继续前行，比以往更加坚定和紧迫，这辈子不活好自己就算白来一趟。
1.项目介绍
最近帮朋友做了一个小工具，就是实现：上传一个excel文件，将数据导入mysql表中，再调用存储过程进行转换计算，最后再将处理结果导出成excel。中间用到的一些知识点记录下来和朋友分享。
2.效果图
3.pom整合
<!--解析excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<!--处理2007 excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>4.工具类
package com.laoxu.test.helloweb.util;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
/**
* @Description:
* @Author laoxu
* @Date 2020/1/24 16:00
**/
public class ExcelUtil {
private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
/**
* 获取并解析excel文件，返回一个二维集合
* @param file 上传的文件
* @return 二维集合（第一重集合为行，第二重集合为列，每一行包含该行的列集合，列集合包含该行的全部单元格的值）
*/
public static ArrayList<ArrayList<String>> analysis(MultipartFile file) {
ArrayList<ArrayList<String>> result = new ArrayList<>();
//获取文件名称
String fileName = file.getOriginalFilename();
System.out.println(fileName);
try {
//获取输入流
InputStream in = file.getInputStream();
//判断excel版本
Workbook workbook = null;
if (judegExcelEdition(fileName)) {
workbook = new XSSFWorkbook(in);
} else {
workbook = new HSSFWorkbook(in);
}
//获取第一张工作表
Sheet sheet = workbook.getSheetAt(0);
//从第二行开始获取
Row row=null;
for (int i=1; i<sheet.getPhysicalNumberOfRows();i++) {
row = sheet.getRow(i);
//循环获取每一列
ArrayList<String> list = new ArrayList<>();
Cell cell = null;
for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
// logger.info(i+":"+j);
cell = row.getCell(j);
if(cell==null){
cell = row.createCell(j);
}
cell.setCellType(CellType.STRING);
list.add(cell.getStringCellValue());
}
//将装有每一列的集合装入大集合
result.add(list);
//关闭资源
workbook.close();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
System.out.println("===================未找到文件======================");
} catch (IOException e) {
e.printStackTrace();
System.out.println("===================上传失败======================");
}
return result;
}
/**
* 判断上传的excel文件版本（xls为2003，xlsx为2017）
* @param fileName 文件路径
* @return excel2007及以上版本返回true，excel2007以下版本返回false
*/
private static boolean judegExcelEdition(String fileName){
if (fileName.matches("^.+\\.(?i)(xls)$")){
return false;
}else {
return true;
}
}
}
5.controller
package com.laoxu.test.helloweb.controller;
import com.laoxu.test.helloweb.entity.WorkDt;
import com.laoxu.test.helloweb.service.WorkDtService;
import com.laoxu.test.helloweb.util.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.util.*;
/**
* @Description: excel上传
* @Author laoxu
* @Date 2020/1/24 16:25
**/
@Controller
@RequestMapping("/api/excel/")
public class ExcelUploadController {
@Autowired
WorkDtService workDtService;
//接受文件上传
@RequestMapping("/upload")
@ResponseBody
public Map<String,Object> uploadFile(MultipartFile file, HttpServletResponse response) {
Map<String,Object> map = new HashMap<>(16);
//解析excel文件
List<ArrayList<String>> row = ExcelUtil.analysis(file);
if(row.size()>0){
//1.清空表
workDtService.removeAll();
//2.插入数据
WorkDt entity = null;
int size=0;
for (int i = 0;i<row.size();i++){
entity = new WorkDt();
List<String> cell = row.get(i);
size = cell.size();
// 不足32个的 补全
for (int j = size; j < 32; j++) {
cell.add("");
}
entity.setSeqno(Integer.valueOf(cell.get(0)));
entity.setD1(cell.get(1));
entity.setD2(cell.get(2));
entity.setD3(cell.get(3));
entity.setD4(cell.get(4));
entity.setD5(cell.get(5));
entity.setD6(cell.get(6));
entity.setD7(cell.get(7));
entity.setD8(cell.get(8));
...
workDtService.add(entity);
/* for (int j = 0;j<cell.size();j++){
System.out.print(cell.get(j)+" ");
}
System.out.println();*/
}
}
//3.调用存储过程
Map<String,String> param = new HashMap<>();
param.put("year","2019");
param.put("month","12");
workDtService.updateWorkDt(param);
Map<String,Object> result = new HashMap<>();
result.put("code",0);
result.put("msg","");
return result;
}
/**
* 导出
* @param response
* @throws Exception
*/
@GetMapping("/export")
public void export(HttpServletResponse response) throws Exception{
// 创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建sheet
HSSFSheet sheet = workbook.createSheet("sheet1");
// String id = UUID.randomUUID().toString();
String fileName = "计算结果.xls";// 设置要导出的文件的名字
// 获取数据集合
List<WorkDt> list = workDtService.getAll();
// 在表中存放查询到的数据放入对应的列
HSSFRow row = null;
WorkDt entity=null;
for (int i = 0; i < list.size(); i++) {
entity = list.get(i);
row = sheet.createRow(i);
row.createCell(0).setCellValue(entity.getSeqno());
row.createCell(1,CellType.STRING).setCellValue(entity.getD1());
row.createCell(2,CellType.STRING).setCellValue(entity.getH1());
row.createCell(3).setCellValue(entity.getD2());
row.createCell(4).setCellValue(entity.getH2());
row.createCell(5).setCellValue(entity.getD3());
row.createCell(6).setCellValue(entity.getH3());
row.createCell(7,CellType.STRING).setCellValue(entity.getD4());
row.createCell(8,CellType.STRING).setCellValue(entity.getH4());
row.createCell(9).setCellValue(entity.getD5());
row.createCell(10).setCellValue(entity.getH5());
row.createCell(11,CellType.STRING).setCellValue(entity.getD6());
row.createCell(12,CellType.STRING).setCellValue(entity.getH6());
...
}
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition",
"attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
response.flushBuffer();
workbook.write(response.getOutputStream());
}
}
