<!--excel导入-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
@Data
@ColumnWidth(value = 20)
public class SampleExcel extends BaseExcel {
@ExcelProperty(value = {"序号"})
@ColumnWidth(20)
private Integer xuNum;
@ExcelProperty(value = {"样品编号"})
@ColumnWidth(30)
private String sampleNum;
@ExcelProperty(value = {"调查船"})
@ColumnWidth(20)
private String surveyShip;
@ExcelProperty(value = {"航次"})
@ColumnWidth(30)
private String voyage;
@ExcelProperty(value = {"海域"})
@ColumnWidth(20)
private String area;
@ExcelProperty(value = {"站位"})
@ColumnWidth(20)
private String station;
@ExcelProperty(value = {"x坐标"})
@ColumnWidth(20)
private String stationX;
@ExcelProperty(value = {"y坐标"})
@ColumnWidth(20)
private String stationY;
@ExcelProperty(value = {"详细地址"})
@ColumnWidth(20)
private String detailedAddress;
@ExcelProperty(value = {"结束深度(m)"})
@ColumnWidth(20)
private String endDepth;
@ExcelProperty(value = {"心长(m)"})
@ColumnWidth(20)
private String heartLength;
@ExcelProperty(value = {"存放位置"})
@ColumnWidth(20)
private String position;
@ExcelProperty(value = {"保存状况"})
@ColumnWidth(20)
private String saveStatus;
@ExcelProperty(value = {"备注"})
@ColumnWidth(20)
private String marks;
}
package org.jeecg.modules.sample.excel;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.google.common.collect.Maps;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
@Slf4j
public class EasyExcelUtils {
private static ExcelReaderBuilder readExcel(InputStream fileStream, Class<? extends Object> clazz, AnalysisEventListener listener) {
if(listener == null) {
listener = new ExcelListener();
}
return EasyExcel.read(fileStream, clazz, listener);
}
/**
* 单sheet页读取excel返回数据集合
* @param fileStream 文件流
* @param clazz 监听器
* @param sheetNum 读取第sheetNum+1个sheet页
* @param rowNumber rowNumber+1行开始读取
* @return list
*/
public static List<Object> readExcel(InputStream fileStream, Class<? extends Object> clazz, Integer sheetNum, Integer rowNumber) {
ExcelListener listener = new ExcelListener();
readExcel(fileStream, clazz, listener, sheetNum, rowNumber);
return listener.getDatas();
}
/**
* 单sheet页读取excel
* @param fileStream 文件流
* @param listener 监听器
* @param sheetNum 读取第sheetNum+1个sheet页
* @param rowNumber rowNumber+1行开始读取
*/
public static void readExcel(InputStream fileStream, Class<? extends Object> clazz, AnalysisEventListener listener, Integer sheetNum, Integer rowNumber) {
if(sheetNum == null) {
sheetNum = 0;
}
if(rowNumber == null) {
rowNumber = 1;
}
ExcelReaderBuilder builder = readExcel(fileStream, clazz, listener);
builder.sheet(sheetNum).headRowNumber(rowNumber).doRead();
}
/**
* 多sheet页读取excel并返回结果集
* @param fileStream 文件流
* @param clazz 实体类
* @return map
*/
public static Map<String, List<Object>> readExcelManySheets(InputStream fileStream, Class<? extends Object> clazz) {
ExcelListener listener = new ExcelListener();
ExcelReaderBuilder builder = readExcel(fileStream, clazz, listener);
ExcelReader reader = builder.build();
List<ReadSheet> sheetList = reader.excelExecutor().sheetList();
Map<String, List<Object>> returnMap = Maps.newHashMap();
for(ReadSheet readSheet : sheetList) {
listener.getDatas().clear();
//读取每一个sheet的内容
reader.read(readSheet);
List<Object> objects = listener.getDatas();
returnMap.put(readSheet.getSheetName(), objects);
}
reader.finish();
return returnMap;
}
/**
* 多sheet页读取excel
* @param fileStream 文件流
* @param listener 监听器
* @return sheetList
*/
public static List<ReadSheet> readExcelManySheets(InputStream fileStream, Class<? extends Object> clazz, ExcelListener listener) {
ExcelReaderBuilder builder = readExcel(fileStream, clazz, listener);
ExcelReader reader = builder.build();
return reader.excelExecutor().sheetList();
}
/**
* 导出excel，一个sheet，有模板
* @param response
* @param list 数据集合
* @param fileName 文件名
* @param sheetName sheet名
* @param clazz 模板实体类
*/
public static void writeExcel(HttpServletResponse response, List<?> list,
String fileName, String sheetName, Class<? extends Object> clazz) {
writeExcel(response, list, fileName, sheetName, clazz, new CustomSheetWriteHandler());
// try {
// response.setContentType("application/vnd.ms-excel");
// response.setCharacterEncoding("utf-8");
// response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(list);
// } catch (IOException e) {
// e.printStackTrace();
// }
}
public static void writeExcel(
HttpServletResponse response, List<?> list,
String fileName, String sheetName, Class<? extends Object> clazz,
CustomSheetWriteHandler customSheetWriteHandler) {
// writeExcel(response, list, fileName, sheetName, clazz, new CustomSheetWriteHandler(mapDropDown, firstRow, lastRow));
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), clazz)
.registerWriteHandler(customSheetWriteHandler)
.sheet(sheetName).doWrite(list);
} catch (IOException e) {
e.printStackTrace();
}
}
public static void writeExcel(
HttpServletResponse response, List<?> list,
String fileName, String sheetName, Class<? extends Object> clazz,
CustomSheetWriteNewHandler customSheetWriteHandler) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), clazz)
.registerWriteHandler(customSheetWriteHandler)
/*.registerWriteHandler(new CustomCellWriteHandler())*/
.sheet(sheetName).doWrite(list);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 导出excel，多个sheet，有模板
* @param response
* @param map 数据map,key为sheetName, value为数据集合
* @param fileName 文件名
* @param clazz 模板实体类
*/
public static void writeExcelWithSheets(HttpServletResponse response, Map<String, List<?>> map,
String fileName, Class<? extends Object> clazz) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx");
ExcelWriterBuilder builder = EasyExcel.write(response.getOutputStream(), clazz);
writeExcelWithSheets(builder, map, clazz);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 导出excel到本地，多个sheet，有模板
* @param filePath
* @param map
* @param fileName
* @param clazz
*/
public static void writeExcelWithSheets(String filePath, Map<String, List<?>> map,
String fileName, Class<? extends Object> clazz) {
ExcelWriterBuilder builder = EasyExcel.write(filePath + fileName + ".xlsx", clazz);
writeExcelWithSheets(builder, map, clazz);
}
private static void writeExcelWithSheets(ExcelWriterBuilder builder, Map<String, List<?>> map, Class<? extends Object> clazz) {
//注册单元格拦截器
builder.registerWriteHandler(new CustomCellWriteHandler());
//注册sheet拦截器
builder.registerWriteHandler(new CustomSheetWriteHandler());
ExcelWriter excelWriter = builder.build();
WriteSheet writeSheet =null;
int num = 0;
for(Map.Entry<String, List<?>> entry : map.entrySet()){
writeSheet = EasyExcel.writerSheet(num, entry.getKey()).build();
excelWriter.write(entry.getValue(), writeSheet);
num++;
}
excelWriter.finish();
}
}
package org.jeecg.modules.sample.excel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
/**
* 解析监听器，
* 每解析一行会回调invoke()方法。
* 整个excel解析结束会执行doAfterAllAnalysed()方法
*/
@Data
@Slf4j
public class ExcelListener extends AnalysisEventListener {
private List<Object> datas = new ArrayList<>();
/**
* 逐行解析
* object : 当前行的数据
*/
@Override
public void invoke(Object object, AnalysisContext context) {
//当前行
// context.getCurrentRowNum()
if (object != null) {
datas.add(object);
}
}
/**
* 解析完所有数据后会调用该方法
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("【{}】共{}条数据，所有数据解析完成！", context.readSheetHolder().getSheetName(), datas.size());
}
}
package org.jeecg.modules.sample.excel;
public class BaseUtils {
/**
* 判断字符串是否为数值
* @param s 传入字符串
* @return boolean
*/
public static boolean isNumeric(String s) {
if (s != null && !"".equals(s.trim()))
return s.matches("-?[0-9]+.?[0-9]*");
else
return false;
}
/**
* 将数字转换成英文字母
* @param num
* @return
*/
public static String numberToLetter(int num) {
if (num < 0) {
return null;
}
String letter = "";
do {
if (letter.length() > 0) {
num--;
}
letter = ((char) (num % 26 + (int) 'A')) + letter;
num = (int) ((num - num % 26) / 26);
} while (num > 0);
return letter;
}
/**
* 分钟数转天、时、分
* @param min
* @return
*/
public static String minConvertDayHourMin(Double min){
String html="0分";
if(min!=null){
Double m=(Double) min;
String format;
Object[] array;
Integer days =(int) (m/(60*24));
Integer hours = (int) (m/(60)-days*24);
Integer minutes = (int) (m-hours*60-days*24*60);
if(days>0){
format="%1$,d天%2$,d小时%3$,d分";
array=new Object[]{days,hours,minutes};
}else if(hours>0){
format="%1$,d小时%2$,d分";
array=new Object[]{hours,minutes};
}else{
format="%1$,d分";
array=new Object[]{minutes};
}
html= String.format(format, array);
}
return html;
}
/**
*
* @param day
* @param hour
* @param min
* @return min
*/
public static int dayHourMinConvertMin(int day,int hour,int min){
int days=day*24*60;
int hours=hour*60;
return days+hours+min;
}
}
package org.jeecg.modules.sample.excel;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import java.util.HashMap;
import java.util.Map;
/**
* 自定义sheet拦截器
*/
@Slf4j
@Data
public class CustomSheetWriteNewHandler implements SheetWriteHandler {
private Map<Integer,String []> mapDropDown = new HashMap<>();
private Integer firstRow = 1;
private Integer lastRow = 1000;
public CustomSheetWriteNewHandler() {
}
public CustomSheetWriteNewHandler(Map<Integer,String []> mapDropDown, Integer firstRow, Integer lastRow) {
this.mapDropDown = mapDropDown;
this.firstRow = firstRow;
this.lastRow = lastRow;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
if (mapDropDown.isEmpty()) {
return;
}
Sheet sheet = writeSheetHolder.getSheet();
// 自定义Cell格式为动态下拉列表
// 注：此方法适用下拉数据量较多的情况，会生成另一个sheet页，并隐藏该页数据
// 若下拉数据少，调用另一个拦截器CustomSheetWriteHandler直接生成下拉列表即可
setDropDownCell(writeWorkbookHolder, sheet);
log.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());
}
private void setDropDownCell(WriteWorkbookHolder writeWorkbookHolder, Sheet sheet) {
//获取一个workbook
Workbook workbook = writeWorkbookHolder.getWorkbook();
//定义sheet的名称
String hiddenName = "请勿删除";
//1.创建一个隐藏的sheet
Sheet hidden = workbook.createSheet(hiddenName);
final int[] cellNum = {0};
mapDropDown.forEach((k, v) -> {
DataValidationHelper helper = sheet.getDataValidationHelper();
// 设置下拉单元格的首行 末行 首列 末列
CellRangeAddressList rangeList = new CellRangeAddressList(firstRow, lastRow, k, k);
DataValidationConstraint constraint;
// 若下拉列表值太多，则使用另一种方式
if (v.length > 10) {
//2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
for (int i = 0, length = v.length; i < length; i++) {
// 1:表示你开始的行数 3表示 你开始的列数
Row row = hidden.getRow(i);
if (row == null) {
row = hidden.createRow(i);
}
Cell cell = row.getCell(cellNum[0]);
if (cell == null) {
cell = row.createCell(cellNum[0]);
}
cell.setCellValue(v[i]);
}
hidden.setColumnHidden(cellNum[0], true);
//4 A1:A代表隐藏域创建第N列createCell(N)时。以A1列开始A行数据获取下拉数组
String cellName = BaseUtils.numberToLetter(cellNum[0]);
Name category1Name = workbook.createName();
category1Name.setNameName(hiddenName+cellNum[0]);
category1Name.setRefersToFormula(hiddenName + "!$"+cellName+"$1:$"+cellName+"$"+v.length);
//5 将刚才设置的sheet引用到你的下拉列表中
constraint = helper.createFormulaListConstraint(hiddenName+cellNum[0]);
cellNum[0]++;
} else {
constraint = helper.createExplicitListConstraint(v);
}
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.createErrorBox("提示","此值与单元格定义格式不一致");
// 处理Excel兼容性问题
if (validation instanceof XSSFDataValidation) {
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
} else {
validation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(validation);
});
}
}
package org.jeecg.modules.sample.excel;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import java.util.HashMap;
import java.util.Map;
/**
* 自定义sheet拦截器
*/
@Slf4j
@Data
public class CustomSheetWriteHandler implements SheetWriteHandler {
private Map<Integer,String []> mapDropDown = new HashMap<>();
private Integer firstRow = 1;
private Integer lastRow = 1000;
public CustomSheetWriteHandler() {
}
public CustomSheetWriteHandler(Map<Integer,String []> mapDropDown, Integer firstRow, Integer lastRow) {
this.mapDropDown = mapDropDown;
this.firstRow = firstRow;
this.lastRow = lastRow;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
if (mapDropDown.isEmpty()) {
return;
}
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
mapDropDown.forEach((k, v) -> {
// 下拉列表约束数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
// 设置下拉单元格的首行 末行 首列 末列
CellRangeAddressList rangeList = new CellRangeAddressList(firstRow, lastRow, k, k);
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.createErrorBox("提示","此值与单元格定义格式不一致");
// 处理Excel兼容性问题
if (validation instanceof XSSFDataValidation) {
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
} else {
validation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(validation);
});
log.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());
}
}
package org.jeecg.modules.sample.excel;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import java.util.List;
/**
* 自定义单元格拦截器
*/
@Slf4j
public class CustomCellWriteHandler implements CellWriteHandler {
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
// 这里可以对cell进行任何操作
log.info("第{}行，第{}列写入完成。{}", cell.getRowIndex(), cell.getColumnIndex(),cell.getStringCellValue());
/* Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置前景填充样式
cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//前景填充色
cell.setCellStyle(cellStyle);*/
/* Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
Font cellFont = workbook.createFont();
cellFont.setColor((short)12);
cellStyle.setFont(cellFont);
cell.setCellStyle(cellStyle);*/
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
}
package org.jeecg.modules.sample.excel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.List;
@ApiModel(value = "excel导入结果数据模型")
@Data
public class ExcelImportResult {
@ApiModelProperty(value = "异常提示信息")
private List<ExcelAbnormalPrompt> excelAbnormalPrompts;
@ApiModelProperty(value = "成功条数")
private Long successNumber;
@ApiModelProperty(value = "失败条数")
private Long failNumber;
}
/**
* 导入模版
* @param file
* @return
*/
@PostMapping("/importSampleExcel")
public ResultVO importSampleExcel(MultipartFile file) {
if(file == null) {
return new ResultVO<>(StatusCode.BUSINESS_ERROR, StatusCodeDesc.ERROR_PARAMS_DESC);
}
return receiptService.importSampleExcel(file);
}
/**
* 导出模板
* @param response
* @throws UnsupportedEncodingException
*/
@PostMapping("/exportSpTemplate")
public void exportSpTemplate(HttpServletResponse response) throws UnsupportedEncodingException {
String fileName = URLEncoder.encode("入库单", "UTF-8");
String sheetName = "入库单";
Map<Integer,String[]> mapDropDown=new HashMap<>();
EasyExcelUtils.writeExcel(response, null, fileName, sheetName, SampleExcel.class, new CustomSheetWriteNewHandler(mapDropDown, SampleExcel.firstRow,100 ));
}
816
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
