easyPoi本来是挺好用的,和一个java实体类关联,操控实体数组就能写出来一个Excel文件,但在实际用途中我们需要为一些特殊的单元格做一些调整,让人看的舒服一些,也更加突出。
要达到这种效果。先加上maven
<!--poi-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>
<!--poi end-->
首先是easyPoi的工具类,复制粘贴就好。
package com.demo.utils;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
/**
* 导入导出公用方法
*/
public class PoiUtils {
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
defaultExport(list, fileName, response);
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
if (workbook != null) ;
downLoadExcel(fileName, response, workbook);
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
throw new RuntimeException(e.getMessage());
}
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null) ;
downLoadExcel(fileName, response, workbook);
}
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
} catch (NoSuchElementException e) {
throw new RuntimeException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}
return list;
}
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
} catch (NoSuchElementException e) {
throw new RuntimeException("excel文件不能为空");
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
return list;
}
}
接下来就是我们的实体类,加上注解就能和Excel相互关联了
@Data
public class ApiDto implements Comparable<ApiDto>{
@Excel(name="ID",orderNum = "0")
private String id;
@Excel(name="是否成功出发策略",orderNum = "1")
private String successs;
@Excel(name = "请求地址", orderNum = "2",width = 30)
private String url;
@Excel(name = "参数", orderNum = "3",width = 30)
private String param;
@Excel(name = "名称", orderNum = "4",width = 20)
private String name;
@Excel(name = "状态", orderNum = "5")
private String code;
@Excel(name = "时间", orderNum = "6")
private String time;
@Excel(name = "数据", orderNum = "7",width = 100)
private String data;
@Excel(name="策略表数量变化",orderNum = "8",width = 10)
private String columnChange;
}
测试代码
其实就是获取到Cell,Cell就映射这一个单元格,目前也是初探poi,所以就暴力的使用枚举了,以后再看看能不能优化一下。
package com.test;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.demo.vo.ApiDto;
import com.jfinal.log.Log;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
/**
* 通用json获取工具
*/
public class Test {
private final static Log LOG = Log.getLog(Test.class);
@org.junit.Test
public void test(){
List<ApiDto> list = new ArrayList<>();
ApiDto apiDto1 = new ApiDto("1","2","3","4","5","6","186ms","8","9");
ApiDto apiDto2 = new ApiDto("1","2","3","4","5","6","822ms","8","9");
ApiDto apiDto3 = new ApiDto("1","2","asd","4","5","6","456ms","8","9");
list.add(apiDto1);
list.add(apiDto2);
list.add(apiDto3);
//Workbook就是在内存中的Excel对应的对象
Workbook wb = ExcelExportUtil.exportExcel(new ExportParams("dnc_test","test"),
ApiDto.class, list);
//获取到你这个Excel的长和宽
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(0);
int rowNum = sheet.getLastRowNum();
int colNum = row.getPhysicalNumberOfCells();
//创建字体对象,注意这不是awt包下的,是poi给我们封装了一个
Font font = wb.createFont();
font.setBold(true);
short index = HSSFColor.HSSFColorPredefined.RED.getIndex();
font.setColor(index);
font.setFontHeightInPoints((short) 12);
font.setFontName("宋体");
//效率非常低的二次循环遍历
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
while (j < colNum) {
//这里我们就获得了Cell对象,对他进行操作就可以了
Cell cell = row.getCell((short) j);
String value = row.getCell((short) j).toString();
value = value.trim();
if(value.endsWith("ms")){
Integer time = Integer.parseInt(value.substring(0,value.length()-2));
HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setFont(font);
cell.setCellStyle(style);
}
j++;
}
}
//把内存中的Excel写到磁盘中去
File file = new File("E:/test.xls");
if(file.exists()){
file.delete();
}
try {
file.createNewFile();
FileOutputStream fileOutputStream = new FileOutputStream(file);
wb.write(fileOutputStream);
LOG.info("xls文件写入成功,地址:"+file.getAbsolutePath());
wb.close();
} catch (IOException e) {
LOG.info("xls文件写入失败,地址:"+file.getAbsolutePath());
e.printStackTrace();
}
}
}
然后就可以得到一开始的效果了。当然如果你有优化的办法还希望不吝指教,让我们一同进步。