1.pom.xml
org.apache.poi poi${org-apache-poi.version} org.apache.poi poi-excelant${org-apache-poi.version} org.apache.poi poi-ooxml3.15
2.读取工具类
package com.common.utils;
import lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ExcelReadUtils {
private XSSFWorkbook workbook;
private XSSFSheet sheet;
private int sheetIndex = 0;
public ExcelReadUtils(){}
public ExcelReadUtils(File file) throws IOException {
FileInputStream inputStream = new FileInputStream(file);
this.workbook = new XSSFWorkbook(inputStream);
inputStream.close();
this.changeSheet();
}
public ExcelReadUtils(File file, int sheetIndex) throws IOException {
FileInputStream inputStream = new FileInputStream(file);
this.workbook = new XSSFWorkbook(inputStream);
inputStream.close();
this.sheetIndex = sheetIndex;
this.changeSheet();
}
public List getRowStrArr(int rowIndex){
List res = new ArrayList<>();
XSSFRow row = this.sheet.getRow(rowIndex);
for (int j = 0; j < row.getLastCellNum(); j++) {
XSSFCell dataCell = row.getCell(j);
String cellValue = this.convertCellValueToString(dataCell);
res.add(cellValue);
}
return res;
}
public Map getRowStrMap(int rowIndex, List keyArr){
List list = this.getRowStrArr(rowIndex);
Map res = new HashMap<>();
if( CollectionUtils.isNotEmpty(list) ){
for(int i=0; i> getAllDataMap(List keyArr){
List> res = new ArrayList<>();
for (int i = 0; i <= this.sheet.getLastRowNum(); i++) {
Map item = this.getRowStrMap(i, keyArr);
res.add(item);
}
return res;
}
private void changeSheet(){
this.sheet = this.workbook.getSheetAt(this.sheetIndex);
}
private String convertCellValueToString(Cell cell) {
if (null == cell) {
return null;
}
String returnValue = null;
switch (cell.getCellTypeEnum()) {
//字符串
case STRING:
returnValue = cell.getStringCellValue();
break;
//数字
case NUMERIC:
double numericCellValue = cell.getNumericCellValue();
boolean isInteger = isIntegerForDouble(numericCellValue);
if (isInteger) {
DecimalFormat df = new DecimalFormat("0");
returnValue = df.format(numericCellValue);
} else {
returnValue = Double.toString(numericCellValue);
}
break;
//布尔
case BOOLEAN:
boolean booleanCellValue = cell.getBooleanCellValue();
returnValue = Boolean.toString(booleanCellValue);
break;
//空值
case BLANK:
break;
//公式
case FORMULA:
cell.getCellFormula();
break;
//故障
case ERROR:
break;
default:
break;
}
return returnValue;
}
public static boolean isIntegerForDouble(Double num) {
double eqs = 1e-10; //精度范围
return num - Math.floor(num) < eqs;
}
public void close() throws IOException {
this.workbook.close();
}
public int getSheetIndex() {
return sheetIndex;
}
public ExcelReadUtils setSheetIndex(int sheetIndex) {
this.sheetIndex = sheetIndex;
this.changeSheet();
return this;
}
}
3.写入工具类
package com.common.utils;
import com.common.exceptions.BaseException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;
public class ExcelWriteUtils {
private XSSFWorkbook workbook;
private List sheetArr;
private List sheetNameArr;
private int currSheetIndex = 0;
private int currRowIndex = 0;
public ExcelWriteUtils(){
this.sheetNameArr = Arrays.asList("sheet1");
this.initSheetArr();
}
public ExcelWriteUtils(String sheetName) {
this.sheetNameArr = Arrays.asList(sheetName);
this.initSheetArr();
}
public ExcelWriteUtils(List sheetNameArr) {
this.sheetNameArr = sheetNameArr;
this.initSheetArr();
}
private void initSheetArr() {
if( CollectionUtils.isEmpty(this.sheetNameArr) ){
throw BaseException.logicException("sheet没有设置名称");
}
//XSSFWorkbook:07版本的xlsx
this.workbook = new XSSFWorkbook();
//构造sheet
this.sheetArr = new ArrayList<>();
this.sheetNameArr.forEach(sheetName->{
XSSFSheet sheet = this.workbook.createSheet(sheetName);
this.sheetArr.add(sheet);
});
}
private XSSFSheet getCurrSheet(){
return this.sheetArr.get(this.currSheetIndex);
}
public void writeRow(int rowIndex, List valueArr){
XSSFRow row = this.getCurrSheet().getRow(rowIndex);
if( row==null ){
row = this.getCurrSheet().createRow(rowIndex);
}
if( CollectionUtils.isNotEmpty(valueArr) ){
for(int i=0; i valueArr){
this.writeRow(this.currRowIndex, valueArr);
}
public void writeBatchRow(List> valueArrArr){
if( CollectionUtils.isNotEmpty(valueArrArr) ){
valueArrArr.forEach(valueArr->{
if( CollectionUtils.isNotEmpty(valueArr) ){
this.writeCurrRow(valueArr);
}
this.currRowIndex++;
});
}
}
public int getCurrSheetIndex() {
return currSheetIndex;
}
public void setCurrSheetIndex(int currSheetIndex) {
this.currSheetIndex = currSheetIndex;
}
public int getCurrRowIndex() {
return currRowIndex;
}
public void setCurrRowIndex(int currRowIndex) {
this.currRowIndex = currRowIndex;
}
public XSSFWorkbook getWorkbook() {
return workbook;
}
}
4.测试
上传文件，读取excel内容，log打印，然后生成新的excel，最后下载这个excel文件
4.1 控制器
package com.demo.mydemo.controller;
import com.common.utils.AjaxResult;
import com.common.utils.StringUtils;
import com.demo.mydemo.entity.dto.ExcelParamDTO;
import com.demo.mydemo.service.IExcelService;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLEncoder;
@Controller
@RequestMapping("/excel")
public class ExcelController {
@Autowired
private IExcelService excelService;
@RequestMapping("/index")
public String index() {
return "excel_index";
}
//限制只能post访问
@RequestMapping(value="/upload", method= RequestMethod.POST)
@ResponseBody
public AjaxResult uploadData(
@ApiParam(value = "参数") ExcelParamDTO param) {
String filePath = excelService.upload(param);
return AjaxResult.success("成功", filePath);
}
@RequestMapping("/download")
@ApiOperation(value = "下载文件")
public void download(
HttpServletResponse response,
@ApiParam(value = "文件路径") @RequestParam(value="filePath", required = true) String filePath
) throws IOException {
File fileUpload = new File(filePath);
String fileName = fileUpload.getName();
//encode后替换 解决空格问题
fileName = URLEncoder.encode(fileName, "UTF-8");
fileName = fileName.replace("+", "%20");
// 读到流中
// URL url = new URL(fileUpload.getFileUrl());
// URLConnection conn = url.openConnection();
// InputStream inputStream = conn.getInputStream();
InputStream inputStream = new FileInputStream(fileUpload);
response.reset();
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition", "attachment; filename=" + fileName);
ServletOutputStream outputStream = response.getOutputStream();
byte[] b = new byte[1024];
int len;
//从输入流中读取一定数量的字节，并将其存储在缓冲区字节数组中，读到末尾返回-1
while ((len = inputStream.read(b)) > 0) {
outputStream.write(b, 0, len);
}
inputStream.close();
}
}
4.2 服务类/业务类
接口类
package com.demo.mydemo.service;
import com.demo.mydemo.entity.dto.ExcelParamDTO;
import com.demo.mydemo.entity.dto.SmbParamDTO;
import java.io.File;
public interface IExcelService {
String upload(ExcelParamDTO param);
}
实现类
package com.demo.mydemo.service.impl;
import com.common.exceptions.BaseException;
import com.common.utils.*;
import com.demo.mydemo.entity.dto.*;
import com.demo.mydemo.service.IExcelService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;
import java.io.*;
import java.util.*;
import static java.util.regex.Pattern.compile;
@Service
@Slf4j
public class ExcelServiceImpl implements IExcelService {
@Override
public String upload(ExcelParamDTO param){
File uploadFile = null;
try {
//上传文件
uploadFile = this._uploadFile(param.getFiles(), "");
//读取excel
ExcelReadUtils readUtils = new ExcelReadUtils(uploadFile);
List keyArr = Arrays.asList("10-19", "20-29", "30-39", "40-49", "50-59", "60-69");
List> data = readUtils.getAllDataMap(keyArr);
if( CollectionUtils.isNotEmpty(data) ){
final int[] i = {1};
data.forEach(item->{
log.info("第"+ i[0] +"行：{}", JSONUtils.toJsonString(item));
i[0]++;
});
}
readUtils.close();
//写入excel
ExcelWriteUtils writeUtils = new ExcelWriteUtils("测试1");
if( CollectionUtils.isNotEmpty(data) ){
final int[] rowIndex = {0};
data.forEach(item->{
List valueArr = new ArrayList<>();
keyArr.forEach(key->{
valueArr.add( item.get(key) );
});
writeUtils.writeRow(rowIndex[0], valueArr);
rowIndex[0]++;
});
}
File targetFile = this._buildExcelFile(writeUtils.getWorkbook(), "");
return targetFile.getAbsolutePath();
} catch (Exception e) {
e.printStackTrace();
throw BaseException.logicException(e.getMessage());
} finally {
//删除上传的文件
if( uploadFile!=null && uploadFile.exists() ){
// uploadFile.delete();
}
}
}
private File _uploadFile(MultipartFile srcFile, String dir) throws Exception{
// 根路径，在 resources/static/upload
String folderPath = ResourceUtils.getURL("classpath:").getPath() + "upload/" + (StringUtils.isNotBlank(dir) ? (dir + "/") : "");
File folder = new File(folderPath);
if( !folder.exists() ){
folder.mkdirs();
}
UUID uuid = UUID.randomUUID();
File targetFile = new File(folder.getAbsolutePath(), uuid+"_"+srcFile.getOriginalFilename());
srcFile.transferTo(targetFile);
return targetFile;
}
private File _buildExcelFile(XSSFWorkbook workbook, String dir) throws IOException {
// 根路径，在 resources/static/upload
String folderPath = ResourceUtils.getURL("classpath:").getPath() + "upload/" + (StringUtils.isNotBlank(dir) ? (dir + "/") : "");
File folder = new File(folderPath);
if( !folder.exists() ){
folder.mkdirs();
}
UUID uuid = UUID.randomUUID();
File targetFile = new File(folder.getAbsolutePath(), uuid+"_build_.xlsx");
OutputStream out = new FileOutputStream(targetFile);
workbook.write(out);
workbook.close();
out.close();
return targetFile;
}
}
4.3 页面
excel测试
5.补充所有pom.xml
4.0.0 org.springframework.boot spring-boot-starter-parent2.6.7 com.example demo0.0.1-SNAPSHOT szy-tools My tools project for Spring Boot jar 1.8 1.1.6 3.17 org.springframework.boot spring-boot-starter-weborg.springframework.boot spring-boot-devtoolstrue org.springframework.boot spring-boot-starter-testtest org.springframework.boot spring-boot-starter-thymeleaforg.codehaus.jackson jackson-mapper-asl1.9.13 com.fasterxml.jackson.datatype jackson-datatype-jsr310org.projectlombok lomboktrue org.apache.commons commons-lang33.8.1 com.github.xiaoymin knife4j-spring-boot-starter3.0.3 org.apache.httpcomponents httpclientorg.samba.jcifs jcifs1.3.3 com.hierynomus smbj0.10.0 eu.bitwalker UserAgentUtils1.21 org.apache.poi poi${org-apache-poi.version} org.apache.poi poi-excelant${org-apache-poi.version} org.apache.poi poi-ooxml3.15 szy-tools org.springframework.boot spring-boot-maven-plugin
