EasyExcel
1.导入(数据为json)
1.导入头
public void saveHeadData(PageData pd, MultipartFile file) throws Exception{
EasyExcelHeadListener easyExcelListener = new EasyExcelHeadListener(dynamicExcelMapper, pd);
EasyExcel.read(file.getInputStream(), easyExcelListener).sheet(0).doRead();
}
package com.ruoyi.project.common.easyexcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.ruoyi.common.utils.PageData;
import com.ruoyi.project.business.mapper.DynamicExcelMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class EasyExcelHeadListener extends AnalysisEventListener {
private static final Logger logger = LoggerFactory.getLogger(EasyExcelHeadListener.class);
private List<Map<Integer, Map<Integer, String>>> list;
private PageData pd;
private DynamicExcelMapper dynamicExcelMapper;
public EasyExcelHeadListener(DynamicExcelMapper dynamicExcelMapper, PageData pd) {
this.dynamicExcelMapper = dynamicExcelMapper;
this.pd = pd;
this.list = new ArrayList<>();
}
@Override
public void invokeHeadMap(Map headMap, AnalysisContext context) {
logger.info("解析到一条头数据:{}, ", JSON.toJSONString(headMap));
try{
pd.put("head", JSON.toJSONString(headMap, SerializerFeature.QuoteFieldNames));
saveHeadData(pd);
}catch(Exception e){
logger.info(e.getMessage());
e.printStackTrace();
}
}
@Override
public void invoke(Object data, AnalysisContext context) {
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
private void saveHeadData(PageData pd) throws Exception {
Integer num = dynamicExcelMapper.getDynamicExcelId(pd);
if (num != null) {
dynamicExcelMapper.updateHeadData(pd);
}else {
dynamicExcelMapper.saveHeadData(pd);
}
}
}
<insert id="saveHeadData" parameterType="pd" useGeneratedKeys="true" keyProperty="bid">
insert into dynamic_excel(
<if test="head != null">head,</if>
<if test="proj_id != null">bus_id,</if>
creater,createtime
)values
(
<if test="head != null and head != ''">#{head},</if>
<if test="proj_id != null and head != ''">#{dynamic_excelid},</if>
#{creater},now()
)
</insert>
<update id="updateHeadData" parameterType="pd">
update dynamic_excel
<trim prefix="SET" suffixOverrides=",">
<if test="head != null">head = #{head},</if>
</trim>
where bus_id = #{dynamic_excelid}
</update>
2.导入内容
public void saveContentData(PageData pd, MultipartFile file) throws Exception{
EasyExcelBodyListener easyExcelListener = new EasyExcelBodyListener(dynamicExcelContentMapper, pd);
EasyExcel.read(file.getInputStream(), easyExcelListener).sheet(0).doRead();
}
package com.ruoyi.project.common.easyexcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.ruoyi.common.utils.PageData;
import com.ruoyi.project.business.mapper.DynamicExcelContentMapper;
import lombok.SneakyThrows;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class EasyExcelBodyListener extends AnalysisEventListener {
private static final Logger logger = LoggerFactory.getLogger(EasyExcelBodyListener.class);
private List<Map<String, String>> contentList;
private static final int BATCH_COUNT = 50;
private PageData pd;
private DynamicExcelContentMapper dynamicExcelContentMapper;
public EasyExcelBodyListener(DynamicExcelContentMapper dynamicExcelContentMapper, PageData pd) {
this.dynamicExcelContentMapper = dynamicExcelContentMapper;
this.pd = pd;
this.contentList = new ArrayList<>();
}
@Override
@SneakyThrows
public void invoke(Object data, AnalysisContext context) {
Map<String, String> content = new HashMap<>();
content.put("bid", pd.get("bid") + "");
content.put("content", JSON.toJSONString(data, SerializerFeature.QuoteFieldNames));
contentList.add(content);
if (contentList.size() > BATCH_COUNT) {
saveContentData();
contentList.clear();
}
}
@SneakyThrows
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveContentData();
logger.info("所有数据解析完成!");
}
private void saveContentData() throws Exception{
dynamicExcelContentMapper.saveContentData(contentList);
}
}
<insert id="saveContentData" parameterType="List">
INSERT INTO `dynamic_excel_content`(bid, content) VALUES
<foreach collection="list" index="index" item="item" separator=",">
<foreach collection="item" index="key" item="value" open="(" close=")" separator=",">
#{value}
</foreach>
</foreach>
</insert>
3.添加头({“0”:“年龄”,“1”:“姓名”})
JSONObject head = (JSONObject) pd.get("head");
pd.put("head", head.toString());
dynamicExcelService.saveHeadData(pd);
4.添加内容({“0”:“18”,“1”:“大概”})
@Transactional(rollbackFor = Exception.class)
public void saveContentData(PageData pd) throws Exception{
List<Map<String, String>> contentList = new ArrayList<>();
Map<String, String> content = null;
JSONArray contentArray = (JSONArray) pd.get("content");
for (Object data : contentArray) {
content = new HashMap<>();
content.put("bid", pd.get("bid") + "");
content.put("content", JSON.toJSONString(data, SerializerFeature.QuoteFieldNames));
contentList.add(content);
}
int num = dynamicExcelContentMapper.getDynamicExcelContentIdCount(pd);
if (num > 0) {
dynamicExcelContentMapper.deleteDynamicExcelContent(pd);
dynamicExcelContentMapper.saveContentData(contentList);
}else {
dynamicExcelContentMapper.saveContentData(contentList);
}
}
2.导出(和web导出)
1.导出excel
public String DownloadServer(String path, String fileName, HorizontalCellStyleStrategy customStyle, List<List<String>> head, List<List<String>> data) {
File newFile = new File(path + File.separator + fileName);
if (!newFile.getParentFile().exists())
newFile.getParentFile().mkdirs();
EasyExcel.write(path + File.separator + fileName)
.registerWriteHandler(new CustomCellWriteHandler())
.registerWriteHandler(customStyle)
.head(head)
.sheet()
.doWrite(data);
}
2.web导出excel
public void ExcelDownload(String wordFilePath, String name, HorizontalCellStyleStrategy customStyle, List<List<String>> head, List<List<String>> data, HttpServletResponse response) {
try{
File newFilePath = new File(wordFilePath);
newFilePath.mkdirs();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(name, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream())
.head(head)
.registerWriteHandler(new CustomCellWriteHandler())
.registerWriteHandler(customStyle)
.sheet()
.doWrite(data);
}catch(Exception e){
e.printStackTrace();
}
}
head/data(head和data是一对多的关系)
private List<List<String>> head(List<PageData> list) {
List<List<String>> returnList = new ArrayList<>();
JSONObject head = JSON.parseObject(list.get(0).getString("head"));
List<String> headData;
for (int i = 0, len = head.size(); i < len; i++) {
headData = new ArrayList<>();
headData.add(head.getString(String.valueOf(i)));
returnList.add(headData);
}
return returnList;
}
private List<List<String>> data(List<PageData> list) {
List<List<String>> returnList = new ArrayList<>();
List<PageData> contentList = (List)list.get(0).get("contentList");
for (int i = 0, len = contentList.size(); i < len; i++) {
JSONObject content = JSON.parseObject(contentList.get(i).getString("content"));
List<String> dataList = new ArrayList<>();
for (int j = 0, leng = content.size(); j < leng; j++) {
dataList.add(content.getString(String.valueOf(j)));
}
returnList.add(dataList);
}
return returnList;
}
customStyle(表头和内容风格)
private HorizontalCellStyleStrategy getCustomStyle() {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)14);
headWriteFont.setFontName("宋体");
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setWrapped(false);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontName("宋体");
contentWriteFont.setFontHeightInPoints((short)13);
contentWriteCellStyle.setWriteFont(contentWriteFont);
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
CustomCellWriteHandler(自动设置列宽)
package com.ruoyi.project.common.easyexcel;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
private static final int COLUMN_WIDTH = 20;
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);
public CustomCellWriteHandler() {
}
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap(16);
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}else {
if(columnWidth<COLUMN_WIDTH){
columnWidth =columnWidth*2;
}
}
Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth* 256);
}
}
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = (CellData)cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch(type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
1.//创建一个流,等待写入excel文件内容
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
ExcelWriter excelWriter = EasyExcel.write(byteArrayOutputStream).build();
WriteSheet writeFail = EasyExcel.writerSheet(0, "Sheet1").head(CustomerReturnsVO.class).build();
excelWriter.write(list, writeFail);
excelWriter.finish();
InputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
try {
remoteFileService.save(inputStream, CommonUtil.getFileNodeUrl(filenodePath));
} catch (IOException e) {
log.error(message + "上传文件失败,原因: " + e.getMessage() , e);
}