1. 引入pom
<!-- easypoi相关jar包 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
2. EasypoiUtil.java
package com.diit.dwyxbd.utils;
import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import com.alibaba.druid.util.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Base64;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;
@Component
public class EasypoiUtil {
//模板生成路径
@Value("${at.file-upload}")
private String paths;
/**
* 构建向word中插入的图片信息
*
* @param width
* @param height
* @param imagePath
* @return
*/
public ImageEntity structureImageDate(Integer width, Integer height, String imagePath) {
ImageEntity image = new ImageEntity();
image.setWidth(width);
image.setHeight(height);
image.setUrl(imagePath);
image.setType(ImageEntity.URL);
return image;
}
/**
* 根据base64生成图片
*
* @param base64Info
* @param filePath
* @return
*/
public String decodeBase64(String base64Info, File filePath) {
File dir = new File(filePath + File.separator + "homeImage");
if (!dir.exists()) {
dir.mkdirs();
}
if (StringUtils.isEmpty(base64Info)) {
return null;
}
String[] arr = base64Info.split("base64,");
//要保存的路径
String picPath = "/homeImage/" + UUID.randomUUID().toString() + ".png";
String save = filePath + picPath;
try {
byte[] buffer = Base64.getDecoder().decode(arr[0]);
OutputStream os = new FileOutputStream(save);
os.write(buffer);
os.close();
} catch (IOException e) {
e.printStackTrace();
}
return picPath;
}
/**
* 根据base64生成专题图片
*
* @param base64Info
* @param
* @return 路径
*/
public String specialDecodeBase64(String base64Info, String imagename, String tid) {
File dir = new File(paths + File.separator + "AssessmentImage" + File.separator + tid);
if (!dir.exists()) {
dir.mkdirs();
}
if (StringUtils.isEmpty(base64Info)) {
return null;
}
String[] arr = base64Info.split("base64,");
String save = dir.getPath() + File.separator + imagename;
try {
byte[] buffer = Base64.getDecoder().decode(arr[0]);
OutputStream os = new FileOutputStream(save);
os.write(buffer);
os.close();
} catch (IOException e) {
e.printStackTrace();
}
return save;
}
/**
* 导出多个sheet
* @param excelName
* @param head
* @param title
* @param data
* @param exportFolder
* @return
*/
public Map<String, Object> exportExcel(String excelName, String head, List<Map<String, Object>> title, List<Map<String, Object>> data, File exportFolder, String key) {
//构建标题
List<ExcelExportEntity> titleList = new ArrayList<>();//存放标题
for (Map<String, Object> map : title) {
if (map.get("alias") != null) {
ExcelExportEntity colEntity = new ExcelExportEntity(map.get("alias").toString(), map.get("field").toString());
colEntity.setNeedMerge(true);
titleList.add(colEntity);
}
}
Map<String, List<Map<String, Object>>> province = data.stream().collect(Collectors.groupingBy(e -> (String) e.get("province")));
Set<String> province_key = province.keySet();
List<Map<String, Object>> sheetList = new ArrayList<>();
for (String pro : province_key) {
List<Map<String, Object>> maps1 = province.get(pro); // 每个省份数据
// 创建sheet放入sheet集合
Map<String, Object> sheet = new HashMap<>();
sheetList.add(sheet);
ExportParams ep1 = new ExportParams(head, pro);
ep1.setStyle(ExcelStyleUtil.class);//设置样式
sheet.put("title", ep1);
sheet.put("entity", titleList);
sheet.put("data", maps1);
}
Workbook workbook = new HSSFWorkbook();// excelExportUtils.getWorkbook(ExcelType.HSSF, 0);
for (int i = 0; i < sheetList.size(); i++) {
Map<String, Object> map = sheetList.get(i);
ExportParams exportParams = (ExportParams) map.get("title");
List<ExcelExportEntity> entity = (List<ExcelExportEntity>) map.get("entity");
List<Map<String, Object>> listDataMap = (List<Map<String, Object>>) map.get("data");
ExcelExportService service = new ExcelExportService();
service.createSheetForMap(workbook, exportParams, entity, listDataMap);
//获取tab页的表格设置单元格自适应宽度
Sheet sheet1 = workbook.getSheetAt((short) i);
for (int j = 0; j < titleList.size(); j++) {
sheet1.autoSizeColumn((short) j);
}
}
//下载生成的excel
if (!exportFolder.exists()) {
exportFolder.mkdirs();
}
Map<String, Object> result = new HashMap<>();
/* 这中方式是直接生成excel保存到服务器某个地址*/
try {
FileOutputStream fos = new FileOutputStream(exportFolder.getPath() + "/" + excelName);
workbook.write(fos);
result.put("path", exportFolder.getPath());
result.put("fileName", excelName);
System.out.println(result);
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* 单sheet
* @param excelName
* @param head
* @param title
* @param data
* @param exportFolder
* @param key
* @return
*/
public Map<String, Object> exportExcel2(String excelName, String head, List<Map<String, Object>> title, List<Map<String, Object>> data, File exportFolder, String key) {
//构建标题
List<ExcelExportEntity> titleList = new ArrayList<>();//存放标题
for (Map<String, Object> map : title) {
if (map.get("alias") != null) {
ExcelExportEntity colEntity = new ExcelExportEntity(map.get("alias").toString(), map.get("field").toString());
colEntity.setNeedMerge(true);
titleList.add(colEntity);
}
}
List<Map<String, Object>> sheetList = new ArrayList<>();
Map<String, Object> sheet = new HashMap<>();
sheetList.add(sheet);
ExportParams ep1 = new ExportParams(head, key);
ep1.setStyle(ExcelStyleUtil.class);//设置样式
sheet.put("title", ep1);
sheet.put("entity", titleList);
sheet.put("data", data);
Workbook workbook = new HSSFWorkbook();// excelExportUtils.getWorkbook(ExcelType.HSSF, 0);
for (int i = 0; i < sheetList.size(); i++) {
Map<String, Object> map = sheetList.get(i);
ExportParams exportParams = (ExportParams) map.get("title");
List<ExcelExportEntity> entity = (List<ExcelExportEntity>) map.get("entity");
List<Map<String, Object>> listDataMap = (List<Map<String, Object>>) map.get("data");
ExcelExportService service = new ExcelExportService();
service.createSheetForMap(workbook, exportParams, entity, listDataMap);
//获取tab页的表格设置单元格自适应宽度
Sheet sheet1 = workbook.getSheetAt((short) i);
for (int j = 0; j < titleList.size(); j++) {
sheet1.autoSizeColumn((short) j);
}
}
//下载生成的excel
if (!exportFolder.exists()) {
exportFolder.mkdirs();
}
Map<String, Object> result = new HashMap<>();
/* 这中方式是直接生成excel保存到服务器某个地址*/
try {
FileOutputStream fos = new FileOutputStream(exportFolder.getPath() + "/" + excelName);
workbook.write(fos);
result.put("path", exportFolder.getPath());
result.put("fileName", excelName);
System.out.println(result);
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
}
3. ExcelStyleUtil.java
package com.diit.dwyxbd.utils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
public class ExcelStyleUtil implements IExcelExportStyler{
private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
private static final short FONT_SIZE_TEN = 10;
private static final short FONT_SIZE_ELEVEN = 11;
private static final short FONT_SIZE_TWELVE = 12;
/**
* 大标题样式
*/
private CellStyle headerStyle;
/**
* 每列标题样式
*/
private CellStyle titleStyle;
/**
* 数据行样式
*/
private CellStyle styles;
public ExcelStyleUtil(Workbook workbook) {
this.init(workbook);
}
/**
* 初始化样式
*
* @param workbook
*/
private void init(Workbook workbook) {
this.headerStyle = initHeaderStyle(workbook);
this.titleStyle = initTitleStyle(workbook);
this.styles = initStyles(workbook);
}
/**
* 大标题样式
*
* @param color
* @return
*/
@Override
public CellStyle getHeaderStyle(short color) {
return headerStyle;
}
/**
* 每列标题样式
*
* @param color
* @return
*/
@Override
public CellStyle getTitleStyle(short color) {
return titleStyle;
}
/**
* 数据行样式
*
* @param parity 可以用来表示奇偶行
* @param entity 数据内容
* @return 样式
*/
@Override
public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
return styles;
}
/**
* 获取样式方法
*
* @param dataRow 数据行
* @param obj 对象
* @param data 数据
*/
@Override
public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
return getStyles(true, entity);
}
/**
* 模板使用的样式设置
*/
@Override
public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
return null;
}
/**
* 初始化--大标题样式
*
* @param workbook
* @return
*/
private CellStyle initHeaderStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
return style;
}
/**
* 初始化--每列标题样式
*
* @param workbook
* @return
*/
private CellStyle initTitleStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
//背景色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
/**
* 初始化--数据行样式
*
* @param workbook
* @return
*/
private CellStyle initStyles(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
style.setDataFormat(STRING_FORMAT);
return style;
}
/**
* 基础样式
*
* @return
*/
private CellStyle getBaseCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
style.setWrapText(true);
return style;
}
/**
* 字体样式
*
* @param size 字体大小
* @param isBold 是否加粗
* @return
*/
private Font getFont(Workbook workbook, short size, boolean isBold) {
Font font = workbook.createFont();
//字体样式
font.setFontName("宋体");
//是否加粗
font.setBold(isBold);
//字体大小
font.setFontHeightInPoints(size);
return font;
}
}
4. 使用
easypoiUtil.exportExcel2("test.xls", null, fieldd, data, filePath, sheetName);
浙公网安备 33010602011771号