效果圖
image.png
引入依賴
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
實(shí)現(xiàn)類
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;
/**
* 抽象接口，為擴(kuò)展poi和jxl等其他導(dǎo)出技術(shù)
* 設(shè)計(jì)模式：模板模式
*/
public interface ExcelTemplate<T> {
/**
* 序號(hào)計(jì)數(shù)器
*/
AtomicInteger COUNTER = new AtomicInteger(0);
/**
* 抽象模板方法
* @param fileName 導(dǎo)出的文件名
* @param exportList 導(dǎo)出的數(shù)據(jù)集合
*/
void execute(String fileName, List<T> exportList);
/**
* 接口默認(rèn)實(shí)現(xiàn)方法缔御，獲取反射類字段
* @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;
}
/**
* 接口默認(rèn)實(shí)現(xiàn)方法，獲取反射類字段值
* @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導(dǎo)出excel文件
* @Author: lilin
* @Date: 2022/1/24
*/
public class JxlExcelTemplate<T> implements ExcelTemplate<T>{
/** 多行標(biāo)題最長的一列拟逮，用來計(jì)算其他標(biāo)題行合并 */
private Integer titleMaxLen;
/** 標(biāo)題 */
private String[][] title;
/** 數(shù)據(jù)遍歷起始行，首行一般為標(biāo)題适滓，例如：標(biāo)題行占了3敦迄，就要從4開始創(chuàng)建行數(shù)據(jù) */
private Integer startIndex;
private WritableSheet sheet;
private HttpServletResponse response;
private WritableWorkbook workbook;
/** 單元格默認(rèn)寬度 */
private static Integer DEFAULT_WIDTH = 12;
/**
* 構(gòu)造器
* @param title 標(biāo)題行
* @param sheetName sheet名稱
* @param response 響應(yīng)
*/
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();
}
}
/**
* 標(biāo)題行初始化 + 合并
* @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;
}
/**
* 可重寫的導(dǎo)出數(shù)據(jù)表格
*/
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");// 設(shè)定輸出文件頭
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();
}
}
}
定義注解，方便控制各項(xiàng)屬性
package 你自己的包路徑;
import java.lang.annotation.*;
import java.util.Map;
/**
* 導(dǎo)出信息配置注解
* @author 李林
* @date 2022-01-22
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExportField {
/**
* 是否為序號(hào)字段
* 為了應(yīng)對導(dǎo)出需要從1開始計(jì)數(shù)凭迹，默認(rèn)不需要
*/
boolean isNo() default false;
/**
* 暫時(shí)沒用罚屋，作為標(biāo)記
* 單元格頂部標(biāo)題，當(dāng)只需要一層標(biāo)題的時(shí)候嗅绸，僅加入此項(xiàng)配置即可脾猛，就不需要傳二維數(shù)組的標(biāo)題了
*/
String fieldName() default "";
/**
* 默認(rèn)單元格寬度,256為一個(gè)字節(jié)寬度
*/
int width() default 18;
/**
* 當(dāng)字段為日期類型時(shí)，希望的轉(zhuǎn)換格式
*/
String dateFormat() default "yyyy-MM-dd HH:mm:ss";
}
隨便定義個(gè)類鱼鸠，作為數(shù)據(jù)庫查詢返回實(shí)體類
package 你自己的包路徑;
import com.erhya.admin.service.poi.ExportField;
import lombok.Data;
import java.math.BigDecimal;
@Data
public class H33ExportDTO {
/**
* 訂單編號(hào)
*/
private String orderNo;
/**
* 買家會(huì)員名
*/
private String vipName;
/**
* 買家支付寶賬號(hào)
*/
@ExportField(width = 30)
private String zhiPayAccount;
/**
* 總金額
*/
private BigDecimal amount;
/**
* 訂單狀態(tài)
*/
private String orderStatus;
/**
* 收貨人姓名
*/
private String userName;
/**
* 收貨地址
*/
@ExportField(width = 30)
private String userAddr;
/**
* 聯(lián)系手機(jī)
*/
private String mobile;
/**
* 訂單時(shí)間
*/
@ExportField(width = 22)
private String orderTime;
/**
* 寶貝標(biāo)題
*/
@ExportField(width = 100)
private String title;
}
調(diào)用測試類猛拴，controller：http://localhost:port/項(xiàng)目名/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("飛利浦白色大平板開關(guān)插座家用墻壁86型暗裝五孔帶USB面板燈開關(guān)愉昆，家裝三年質(zhì)保");
data.setAmount(new BigDecimal(RandomUtils.nextDouble(50, 10000d)));
h33ExportDTOS.add(data);
}
String[][] title = {{"比奇堡物流信息"},{"訂單編號(hào)", "買家會(huì)員名", "買家支付寶賬號(hào)", "總金額", "訂單狀態(tài)", "收貨人姓名", "收貨地址", "聯(lián)系手機(jī)", "訂單付款時(shí)間", "寶貝標(biāo)題"}};
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");
}
