<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* @author cc
* 添加动态标题
*/
public class MonthSheetWriteHandler implements SheetWriteHandler {
//第几个sheet
private int sheetNo;
//标题最后一列
private int lastCol;
//标题
private String title;
public MonthSheetWriteHandler(int sheetNo,int lastCol,String title){
this.sheetNo = sheetNo;
this.lastCol = lastCol;
this.title = title;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(sheetNo);
//设置标题
Row row2 = sheet.createRow(0);
row2.setHeight((short) (1.4 * 320));
Cell cell1 = row2.createCell(0);
cell1.setCellValue(title);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
Font font = workbook.createFont();
font.setBold(true);
cellStyle.setFont(font);
cell1.setCellStyle(cellStyle);
Cell cell2 = row2.createCell(1);
cell2.setCellStyle(cellStyle);
Cell cell3 = row2.createCell(2);
cell3.setCellStyle(cellStyle);
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, lastCol));
//设置填表日期,填报人,联系方式
Row row3 = sheet.createRow(1);
row3.setHeight((short) (1.4 * 320));
Cell row3Cell1 = row3.createCell(0);
row3Cell1.setCellValue("填表日期");
row3Cell1.setCellStyle(cellStyle);
Cell row3Cell = row3.createCell(1);
row3Cell.setCellValue("填表人");
row3Cell.setCellStyle(cellStyle);
Cell row3Cell2 = row3.createCell(2);
row3Cell2 .setCellValue("联系方式");
row3Cell2.setCellStyle(cellStyle);
}
}
public static HorizontalCellStyleStrategy style2() {
// 头的样式
WriteCellStyle headStyle = new WriteCellStyle();
headStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//设置颜色不填充
headStyle.setFillPatternType(FillPatternType.NO_FILL);
WriteFont headFont = new WriteFont();
//字体加粗
headFont.setBold(true);
headFont.setFontHeightInPoints((short) 11);
headStyle.setWriteFont(headFont);
headStyle.setWrapped(true);
//headStyle.setBorderLeft(BorderStyle.NONE);
//headStyle.setBorderRight(BorderStyle.NONE);
// 内容的样式
WriteCellStyle contentStyle = new WriteCellStyle();
//填充颜色
contentStyle.setFillPatternType(FillPatternType.NO_FILL);
contentStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontName("宋体");
contentWriteFont.setFontHeightInPoints((short) 11);
contentStyle.setWriteFont(contentWriteFont);
contentStyle.setBorderTop(BorderStyle.THIN);
contentStyle.setBorderBottom(BorderStyle.THIN);
contentStyle.setBorderLeft(BorderStyle.THIN);
contentStyle.setBorderRight(BorderStyle.THIN);
//水平居中
contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//垂直居中
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//自动换行
contentStyle.setWrapped(true);
return new HorizontalCellStyleStrategy(headStyle, contentStyle);
}
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.ss.usermodel.*;
import java.util.HashMap;
import java.util.List;
/**
* @Author cc
* @Date 2020/8/25 15:44
* @Version 1.0
* 拦截处理单元格创建
*/
public class CellColorSheetWriteHandler implements CellWriteHandler {
/**
* map
* key：第i行
* value：第i行中单元格索引集合
*/
private HashMap<Integer, List<Integer>> map;
/**
* 颜色
*/
private Short colorIndex;
/**
* 有参构造
*/
public CellColorSheetWriteHandler(HashMap<Integer, List<Integer>> map, Short colorIndex) {
this.map = map;
this.colorIndex = colorIndex;
}
/**
* 无参构造
*/
public CellColorSheetWriteHandler() {
}
/**
* 在创建单元格之前调用
*/
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
/**
* 在单元格创建后调用
*/
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
/**
* 在单元上的所有操作完成后调用
*/
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
/**
* 考虑到导出数据量过大的情况，不对每一行的每一个单元格进行样式设置，只设置必要行中的某个单元格的样式
*/
//当前行的第i列
int i = cell.getColumnIndex();
//不处理第一行
if (0 != cell.getRowIndex()) {
List<Integer> integers = map.get(cell.getRowIndex());
if (integers != null && integers.size() > 0) {
if (integers.contains(i)) {
// 根据单元格获取workbook
Workbook workbook = cell.getSheet().getWorkbook();
//设置行高
writeSheetHolder.getSheet().getRow(cell.getRowIndex()).setHeight((short) (1.4 * 256));
// 单元格策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置背景颜色不填充
contentWriteCellStyle.setFillPatternType(FillPatternType.NO_FILL);
// 设置背景颜色白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// 设置垂直居中为居中对齐
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置左右对齐为靠左对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 设置单元格上下左右边框为细边框
contentWriteCellStyle.setBorderBottom(BorderStyle.NONE);
contentWriteCellStyle.setBorderLeft(BorderStyle.NONE);
contentWriteCellStyle.setBorderRight(BorderStyle.NONE);
contentWriteCellStyle.setBorderTop(BorderStyle.NONE);
// 创建字体实例
WriteFont cellWriteFont = new WriteFont();
// 设置字体大小
cellWriteFont.setFontName("宋体");
cellWriteFont.setFontHeightInPoints((short) 11);
//设置字体颜色
cellWriteFont.setColor(colorIndex);
//单元格颜色
//contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
contentWriteCellStyle.setWriteFont(cellWriteFont);
CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, contentWriteCellStyle);
//设置当前行第i列的样式
cell.getRow().getCell(i).setCellStyle(cellStyle);
}
}
}
}
}
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
/**
* @author cc
*/
@HeadRowHeight(20)
@ContentRowHeight(15)
@ColumnWidth(20)
@Data
public class ExportInfo {
@ExcelProperty(value = {"一级标题","姓名","姓名"} ,index = 0)
private String name;
@ExcelProperty(value = {"一级标题","2级标题1","年龄"},index = 1)
private String age;
@ExcelProperty(value = {"一级标题","2级标题1","邮箱"},index = 2)
private String email;
@ExcelProperty(value = {"一级标题","2级标题1","地址"},index = 3)
@ExcelIgnore
private String address;
}
@RequestMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) throws Exception {
String fileName = "报表";
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
List<ExportInfo> list = getList();
ExportInfo e = new ExportInfo();
e.setName("盖章");
e.setEmail("签字");
list.add(e);
//用来记录需要为第`key`行中的第`value.get(i)`列设置样式
HashMap<Integer, List<Integer>> map = new HashMap<>();
map.put(7, Arrays.asList(0, 1, 2));
//这里 需要指定写用哪个class去写
WriteSheet writeSheet = EasyExcel.writerSheet(0, "学生信息1")
.head(ExportInfo.class)
.registerWriteHandler(new MonthSheetWriteHandler(0,2,"动态标题"))
.registerWriteHandler(style2())
//设置默认样式及写入头信息开始的行数
/*.useDefaultStyle(true)*/
//指定单元格样式
.registerWriteHandler(new CellColorSheetWriteHandler(map, IndexedColors.RED.getIndex()))
.relativeHeadRowIndex(2)
.build();
excelWriter.write(list, writeSheet);
writeSheet = EasyExcel.writerSheet(1, "学生信息2").head(ExportInfo.class).build();
excelWriter.write(list, writeSheet);
//千万别忘记finish 会帮忙关闭流
excelWriter.finish();
}
@RestController
public class TeachingProcessController {
@Autowired
private TeachingProcessService teachingProcessService;
@PostMapping("/teachingProcess/uploadExcel")
@ApiOperation(value = "导入教学进度excel数据", notes = "导入教学进度excel数据")
public Object uploadExcel(MultipartFile file) {
return teachingProcessService.uploadExcel(file,teachingProcessService);
}
}
/**service方法**/
public Object uploadExcel(MultipartFile file,TeachingProcessService teachingProcessService) {
try {
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(file.getInputStream(), TeachingProcess.class, new TeachingProcessListener(teachingProcessService)).sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
return BaseResult.error("教学进度excel导入失败");
}
return BaseResult.ok();
}
/** 实体类 **/
@TableName("t_teaching_process")
@Data
public class TeachingProcess extends BaseEntity {
@ExcelProperty(value = "instructional_class_id")
private String instructionalClassId;
@ExcelProperty(value = "classroom")
private String classroomName;
@ExcelProperty(value = "coursre_week")
private Float courseWeek;
@ExcelProperty(value = "course_weekday")
private Float courseWeekday;
@ExcelProperty(value = "course_section")
private Integer courseSection;
@ExcelProperty(value = "CONTENT")
private String content;
@ExcelProperty(value = "content_length")
private Integer contentLength;
@ExcelProperty(value = "course_name")
private String courseName;
@ExcelProperty(value = "date")
private Date date;
}
/**
* excel数据保存
* @author cc
* @since 2021年05月13日
*/
public class TeachingProcessListener extends AnalysisEventListener<TeachingProcess> {
private static final Logger LOGGER = LoggerFactory.getLogger(TeachingProcessListener.class);
/**
* 每隔3000条存储数据库，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 3000;
List<TeachingProcess> list = new ArrayList<>();
public TeachingProcessService teachingProcessService;
public TeachingProcessListener(){ }
public TeachingProcessListener(TeachingProcessService teachingProcessService){
this.teachingProcessService = teachingProcessService;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(TeachingProcess data, AnalysisContext context) {
list.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据，开始存储数据库！", list.size());
teachingProcessService.saveBatch(list);
LOGGER.info("存储数据库成功！");
}
}
链接: https://alibaba-easyexcel.github.io/quickstart/write.html
因篇幅问题不能全部显示，请点此查看更多更全内容
Copyright © 2019- igat.cn 版权所有 赣ICP备2024042791号-1
违法及侵权请联系：TEL:199 1889 7713 E-MAIL:2724546146@qq.com
本站由北京市万商天勤律师事务所王兴未律师提供法律服务