本文介绍如何使用easypoi将数据当如到excel中;
官方文档:http://easypoi.mydoc.io/
里面有介绍easypoi的注解使用等。
poi的依赖
<!--easypoi的依赖-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
本文是将用户名和密码导入到excel中,所以封装一个用户对象
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.*;
import lombok.experimental.Accessors;
import java.io.Serializable;
/**
* @program: bootshirojwt
* @description:
* @author: liu.chuanjiang
* @create: 2022-05-11 20:55
*/
@Builder
@Data
@EqualsAndHashCode(callSuper = false)
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
public class UserInfo implements Serializable {
private static final long serialVersionUID = 1L;
@Excel(name = "用户名", width = 20, orderNum = "1")
private String userName;
@Excel(name = "密码", width = 20, orderNum = "2")
private String password;
}
如果需要对excel的样式进行设置,那么就封装一个样式类
import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;
/**
* @ClassName: ExcelExportMyStylerImpl
* @Description: 自定义报表导出样式,可以修改表头颜色,高度等
* @Author: sunt
* @Date: 2019/8/29 21:39
* @Version 1.0
**/
public class ExcelExportMyStyler extends AbstractExcelExportStyler implements IExcelExportStyler {
public ExcelExportMyStyler(Workbook workbook) {
super.createStyles(workbook);
}
@Override
public CellStyle getTitleStyle(short color) {
CellStyle titleStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);// 加粗
titleStyle.setFont(font);
titleStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
titleStyle.setFillForegroundColor(IndexedColors.AQUA.index);// 设置颜色
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setBorderRight(BorderStyle.THIN);
titleStyle.setWrapText(true);
return titleStyle;
}
@SuppressWarnings("deprecation")
@Override
public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
CellStyle style = workbook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setDataFormat(STRING_FORMAT);
if (isWarp) {
style.setWrapText(true);
}
return style;
}
@Override
public CellStyle getHeaderStyle(short color) {
CellStyle titleStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);// 加粗
font.setColor(IndexedColors.RED.index);
font.setFontHeightInPoints((short) 11);
titleStyle.setFont(font);
titleStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
titleStyle.setFillForegroundColor(IndexedColors.WHITE.index);// 设置颜色
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setBorderRight(BorderStyle.THIN);
titleStyle.setWrapText(true);
return titleStyle;
}
@SuppressWarnings("deprecation")
@Override
public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
CellStyle style = workbook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setDataFormat(STRING_FORMAT);
if (isWarp) {
style.setWrapText(true);
}
return style;
}
}
接下就是excel导出的核心之处了,excel工具类
package com.lcj.easypoi;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
/**
* @ClassName: ExcelExportUtil
* @Description: Exceld导出工具类
* @Author: sunt
* @Date: 2019/8/30 14:49
* @Version 1.0
**/
@Slf4j
public class MyExcelExportUtil {
/**
* Excel文件导出,导出的文件名默认为:headTitle+当前系统时间
*
* @param listData 要导出的list数据
* @param pojoClass 定义excel属性信息
* @param headTitle Excel文件头信息
* @param sheetName Excel文件sheet名称
* @param response
*/
public static void exportExcel(Collection<?> listData, Class<?> pojoClass, String headTitle, String sheetName, HttpServletResponse response) {
log.info("进入到exportExcel");
ExportParams params = new ExportParams(headTitle, sheetName);
params.setHeight((short) 8);
params.setStyle(ExcelExportMyStyler.class);
try {
Workbook workbook = ExcelExportUtil.exportExcel(params, pojoClass, listData);
String fileName = headTitle + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
fileName = URLEncoder.encode(fileName, "UTF8");
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel;chartset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void exportExcel1(Collection<?> listData, Class<?> pojoClass, String headTitle, String sheetName) {
log.info("进入到exportExcel");
FileOutputStream outStream=null;
try {
String fileName = "C:\\Users\\Administrator.DESKTOP-JMAFGHR\\Desktop\\工作\\test.xlsx";
outStream = new FileOutputStream(fileName);
ExportParams params = new ExportParams(headTitle, sheetName);
params.setHeight((short) 8);
params.setStyle(ExcelExportMyStyler.class);
Workbook workbook = ExcelExportUtil.exportExcel(params, pojoClass, listData);
workbook.write(outStream);
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (outStream != null) {
outStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
测试类
/**
* @program: test_pay
* @description:
* @author: liu.chuanjiang
* @create: 2022-05-11 22:19
*/
public class test {
public static void main(String[] args) {
List<UserInfo> list=new ArrayList<>();
UserInfo userInfo1 = new UserInfo().setUserName("张三").setPassword("123");
list.add(userInfo1);
UserInfo userInfo2 = new UserInfo().setUserName("李四").setPassword("456");
list.add(userInfo2);
MyExcelExportUtil.exportExcel1(list,UserInfo.class,"用户信息","用户号信息");
}
}