通用的jxl导出Excel文件
2022-02-16 本文已影响0人
木木呦
效果图
image.png
引入依赖
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
实现类
package 你自己的包路径;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Date;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
/**
* 抽象接口，为扩展poi和jxl等其他导出技术
* 设计模式：模板模式
*/
public interface ExcelTemplate<T> {
/**
* 序号计数器
*/
AtomicInteger COUNTER = new AtomicInteger(0);
/**
* 抽象模板方法
* @param fileName 导出的文件名
* @param exportList 导出的数据集合
*/
void execute(String fileName, List<T> exportList);
/**
* 接口默认实现方法，获取反射类字段
* @param fieldName 字段名
* @param clazz 反射类
* @return 字段信息
*/
default Field getField(String fieldName, Class<?> clazz){
Field field = null;
try {
field = clazz.getDeclaredField(fieldName);
field.setAccessible(true);
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
return field;
}
/**
* 接口默认实现方法，获取反射类字段值
* @param fieldName 字段名
* @param clazz 反射类
* @return 字段值
*/
default String getFieldVal(Field field, Class<?> clazz, String fieldName, Object obj) {
String getField = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
String fieldType = field.getGenericType().getTypeName();
ExportField fieldAnnotation = field.getAnnotation(ExportField.class);
String defaultDateFormat = "yyyy-MM-dd HH:mm:ss";
if (fieldAnnotation != null){
if (fieldAnnotation.isNo()){
return String.valueOf(COUNTER.addAndGet(1));
}
defaultDateFormat = fieldAnnotation.dateFormat();
}
Method method = null;
try {
method = clazz.getMethod("get" + getField);
} catch (NoSuchMethodException e) {
//基本类型，boolean等，生成的是isXXX，也可以再捕获一下获取
/*try {
method = clazz.getMethod("is" + getField);
} catch (NoSuchMethodException e1) {
e1.printStackTrace();
}*/
throw new NullPointerException("请将类中获取属性的方法使用get开始");
}
if (method == null){
return null;
}
Object invoke = null;
try {
method.setAccessible(true);
invoke = method.invoke(obj);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
if (invoke == null){
return null;
}
if (StringUtils.isNotEmpty(fieldType)){
switch (fieldType){
case "java.util.Date":
return DateFormatUtils.format((Date) invoke, defaultDateFormat);
case "java.sql.Clob":
return "自定义处理即可";
}
}
return invoke.toString();
}
}
package 你自己的包路径;
import jxl.Workbook;
import jxl.format.*;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.*;
/**
* jxl导出excel文件
* @Author: lilin
* @Date: 2022/1/24
*/
public class JxlExcelTemplate<T> implements ExcelTemplate<T>{
/** 多行标题最长的一列，用来计算其他标题行合并 */
private Integer titleMaxLen;
/** 标题 */
private String[][] title;
/** 数据遍历起始行，首行一般为标题，例如：标题行占了3，就要从4开始创建行数据 */
private Integer startIndex;
private WritableSheet sheet;
private HttpServletResponse response;
private WritableWorkbook workbook;
/** 单元格默认宽度 */
private static Integer DEFAULT_WIDTH = 12;
/**
* 构造器
* @param title 标题行
* @param sheetName sheet名称
* @param response 响应
*/
public JxlExcelTemplate(String[][] title, String sheetName, HttpServletResponse response) {
try {
this.title = title;
this.startIndex = this.title == null ? null : this.title.length;
ServletOutputStream ops = response.getOutputStream();
workbook = Workbook.createWorkbook(ops);
sheet = workbook.createSheet(sheetName, 0);
this.title = title;
this.response = response;
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 标题行初始化 + 合并
* @throws Exception
*/
private void titleInit() throws Exception {
if (this.title == null || this.title.length == 0){
throw new NullPointerException("title can not null");
}
if (titleMaxLen == null){
titleMaxLen = 0;
}
for (String[] t1 : title) {
int length = t1.length;
titleMaxLen = Math.max(length, titleMaxLen);
}
for (int i = 0; i < title.length; i++) {
String[] t2 = title[i];
for (int j = 0; j < t2.length; j++) {
if (t2.length == 1){
sheet.addCell(new Label(j, i, t2[j], buildTitleStyle()));
sheet.mergeCells(i, j, titleMaxLen - 1, j);
}else {
sheet.addCell(new Label(j, i, t2[j], buildTitleStyle()));
}
}
}
}
protected CellFormat buildTitleStyle(){
WritableCellFormat wcfFC = new WritableCellFormat();
try {
wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);
wcfFC.setAlignment(Alignment.CENTRE);
wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN);
wcfFC.setBackground(Colour.ORANGE);
} catch (WriteException e) {
e.printStackTrace();
}
return wcfFC;
}
protected WritableCellFormat buildColumStyle() {
WritableCellFormat wcfFC = new WritableCellFormat();
try {
wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);
wcfFC.setAlignment(Alignment.LEFT);
wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN);
} catch (WriteException e) {
e.printStackTrace();
}
return wcfFC;
}
/**
* 可重写的导出数据表格
*/
protected void buildList(List<T> list, WritableSheet sheet, Integer startIndex, String... showFiles){
try {
int i = startIndex;
for (T t : list) {
Class<?> clazz = t.getClass();
int j = 0;
for (String showFile : showFiles) {
int width = DEFAULT_WIDTH;
Field field = getField(showFile, clazz);
String fieldVal = getFieldVal(field, clazz, showFile, t);
ExportField fieldAnnotation = field.getAnnotation(ExportField.class);
if (fieldAnnotation != null){
width = fieldAnnotation.width();
}
sheet.addCell(new Label(j, i, fieldVal, buildColumStyle()));
sheet.setColumnView(j, width);
j++;
}
i++;
}
} catch (WriteException e) {
e.printStackTrace();
}
}
private void exportList(String fileName) throws Exception{
response.reset();
response.setContentType("application/json;charset=utf-8");
response.setHeader("Content-disposition", "attachment; filename="+ new String((fileName).getBytes("gbk"),"iso8859-1") +".xls");// 设定输出文件头
BufferedOutputStream bufferedOutPut = null;
ServletOutputStream output = null;
try {
output = response.getOutputStream();
bufferedOutPut = new BufferedOutputStream(output);
bufferedOutPut.flush();
workbook.write();
workbook.close();
} catch (IOException e){
e.printStackTrace();
} finally{
if (bufferedOutPut != null){
try {
bufferedOutPut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (output != null){
try {
output.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
@Override
public void execute(String fileName, List<T> exportList){
try {
titleInit();
buildList(exportList, sheet, startIndex);
exportList(fileName);
} catch (Exception e) {
e.printStackTrace();
}
}
public void execute(String fileName, List<T> exportList, String... showFiles){
try {
titleInit();
buildList(exportList, sheet, startIndex, showFiles);
exportList(fileName);
} catch (Exception e) {
e.printStackTrace();
}
}
}
定义注解，方便控制各项属性
package 你自己的包路径;
import java.lang.annotation.*;
import java.util.Map;
/**
* 导出信息配置注解
* @author 李林
* @date 2022-01-22
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExportField {
/**
* 是否为序号字段
* 为了应对导出需要从1开始计数，默认不需要
*/
boolean isNo() default false;
/**
* 暂时没用，作为标记
* 单元格顶部标题，当只需要一层标题的时候，仅加入此项配置即可，就不需要传二维数组的标题了
*/
String fieldName() default "";
/**
* 默认单元格宽度,256为一个字节宽度
*/
int width() default 18;
/**
* 当字段为日期类型时，希望的转换格式
*/
String dateFormat() default "yyyy-MM-dd HH:mm:ss";
}
随便定义个类，作为数据库查询返回实体类
package 你自己的包路径;
import com.erhya.admin.service.poi.ExportField;
import lombok.Data;
import java.math.BigDecimal;
@Data
public class H33ExportDTO {
/**
* 订单编号
*/
private String orderNo;
/**
* 买家会员名
*/
private String vipName;
/**
* 买家支付宝账号
*/
@ExportField(width = 30)
private String zhiPayAccount;
/**
* 总金额
*/
private BigDecimal amount;
/**
* 订单状态
*/
private String orderStatus;
/**
* 收货人姓名
*/
private String userName;
/**
* 收货地址
*/
@ExportField(width = 30)
private String userAddr;
/**
* 联系手机
*/
private String mobile;
/**
* 订单时间
*/
@ExportField(width = 22)
private String orderTime;
/**
* 宝贝标题
*/
@ExportField(width = 100)
private String title;
}
调用测试类，controller：http://localhost:port/项目名/buildTest/100
@GetMapping("buildTest/{dataCount}")
public void buildTest(@PathVariable Integer dataCount, HttpServletResponse response){
List<H33ExportDTO> h33ExportDTOS = new ArrayList<>();
H33ExportDTO data = null;
for (int i = 0; i < dataCount; i++) {
data = new H33ExportDTO();
data.setOrderNo(DateFormatUtils.format(new Date(), "yyyyMMddHHmmssS"));
data.setVipName("小明");
data.setZhiPayAccount("zhifu"+RandomUtils.nextInt(1000, 100000));
data.setMobile("15168455555");
data.setOrderStatus("正常");
data.setUserName("派大星");
data.setUserAddr("比奇堡分堡，XX座，X幢X单元1205");
data.setOrderTime(DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss"));
data.setTitle("飞利浦白色大平板开关插座家用墙壁86型暗装五孔带USB面板灯开关，家装三年质保");
data.setAmount(new BigDecimal(RandomUtils.nextDouble(50, 10000d)));
h33ExportDTOS.add(data);
}
String[][] title = {{"比奇堡物流信息"},{"订单编号", "买家会员名", "买家支付宝账号", "总金额", "订单状态", "收货人姓名", "收货地址", "联系手机", "订单付款时间", "宝贝标题"}};
PoiExcelTemplate<H33ExportDTO> commonTemplateAbstract = new PoiExcelTemplate<>(title, null, response);
commonTemplateAbstract.execute( "自定义文件名_" + DateFormatUtils.format(new Date(), "yyyyMMddHHmmss"),
h33ExportDTOS, "orderNo","vipName","zhiPayAccount","amount","orderStatus","userName","userAddr","mobile","orderTime","title");
}
另一篇：通用poi导出excel文件
