由于公司有很多需要excel导出的操作,类似下面,有很多重复代码
于是我用建造者 的模式进行简单的优化一下,使用简单的单元格设置,比如单元格的居中方式,我是统一设置,可以改造代码实现建造的效果,如果涉及复杂的单元格样式,这个不适用
下面为代码
import jxl.CellView;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;
/**
* @Author: renbaojia
* @CreateDate: 2019-05-14 10:01:04
* @Description: Excel工具类
*/
public class ExcelBuilder {
private HttpServletResponse response;
private WritableSheet sheet;
private WritableCellFormat headerCellFormat;
private WritableCellFormat bodyCellFormat;
private WritableWorkbook book;
private OutputStream outputStream;
private final int DEFAULT_FONT_SIZE = 10;
private final String DEFAULT_FONT_NAME = "宋体";
private final int DEFAULT_CELL_WIDTH = 5000;
/**
* 头部字体
*/
private WritableFont headerFont = new
WritableFont(WritableFont.createFont(DEFAULT_FONT_NAME), DEFAULT_FONT_SIZE,
WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.WHITE);
/**
* 内容字体
*/
private WritableFont bodyFont = new
WritableFont(WritableFont.createFont(DEFAULT_FONT_NAME), DEFAULT_FONT_SIZE,
WritableFont.NO_BOLD);
/**
* 创建对象
*
* @return 实例化对象
*/
public static ExcelBuilder create() {
return new ExcelBuilder();
}
/**
* @param response 请求
* @return 返回this
*/
public ExcelBuilder setResponse(HttpServletResponse response) {
this.response = response;
return this;
}
/**
* @param fontName 字体名称
* @param ps 大小
* @param colour 颜色
* @param underlineStyle 下划线
* @param italic 是否加粗
* @return this
*/
public ExcelBuilder setBoldBodyFont(String fontName, int ps, Colour colour,
UnderlineStyle underlineStyle, boolean italic) {
bodyFont = new
WritableFont(WritableFont.createFont(fontName), ps,
WritableFont.BOLD, italic, underlineStyle, colour);
return this;
}
/**
* @param fontName 字体名称
* @param ps 大小
* @param colour 颜色
* @param underlineStyle 下划线
* @param italic 是否加粗
* @return this
*/
public ExcelBuilder setNoBoldBodyFont(String fontName, int ps, Colour colour,
UnderlineStyle underlineStyle, boolean italic) {
bodyFont = new
WritableFont(WritableFont.createFont(fontName), ps,
WritableFont.NO_BOLD, italic, underlineStyle, colour);
return this;
}
/**
* @param fontName 字体名称
* @param ps 大小
* @param colour 颜色
* @param underlineStyle 下划线
* @param italic 是否加粗
* @return this
*/
public ExcelBuilder setBoldHeaderFont(String fontName, int ps, Colour colour,
UnderlineStyle underlineStyle, boolean italic) {
bodyFont = new
WritableFont(WritableFont.createFont(fontName), ps,
WritableFont.BOLD, italic, underlineStyle, colour);
return this;
}
/**
* @param fontName 字体名称
* @param ps 大小
* @param colour 颜色
* @param underlineStyle 下划线
* @param italic 是否加粗
* @return this
*/
public ExcelBuilder setNoBoldHeaderFont(String fontName, int ps, Colour colour,
UnderlineStyle underlineStyle, boolean italic) {
bodyFont = new
WritableFont(WritableFont.createFont(fontName), ps,
WritableFont.NO_BOLD, italic, underlineStyle, colour);
return this;
}
/**
* @param title 设置文件名和标题
* @return this
* @throws Exception 异常
*/
public ExcelBuilder setTitle(String title) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + new String(title.getBytes("gbk"),
"iso8859-1") + ".xls");
// 获取输出流
outputStream = response.getOutputStream();
book = Workbook.createWorkbook(outputStream);
sheet = book.createSheet(title, 0);
//头部单元格样式
headerCellFormat = new WritableCellFormat(headerFont);
//内容单元格样式
bodyCellFormat = new WritableCellFormat(bodyFont);
// 背景色
headerCellFormat.setBackground(Colour.PALE_BLUE);
//水平居中对齐
headerCellFormat.setAlignment(Alignment.CENTRE);
//竖直方向居中对齐
headerCellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
bodyCellFormat.setAlignment(Alignment.CENTRE);
bodyCellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
return this;
}
/**
* 设置列
*
* @param header 第一列的字符串数组
* @return this
*/
public ExcelBuilder setHeader(String... header) {
//单元格样式
CellView cellView = new CellView();
// 设置宽度
cellView.setSize(DEFAULT_CELL_WIDTH);
for (int i = 0; i < header.length; i++) {
sheet.setColumnView(i, cellView);
try {
sheet.addCell(new Label(i, 0, header[i], headerCellFormat));
} catch (WriteException e) {
e.printStackTrace();
}
}
return this;
}
/**
* 对传入的list进行反射获取属性存入sheet
*
* @param list 数据内容
* @return this
*/
public ExcelBuilder setList(Object list) {
List a = (List) list;
for (int i = 0; i < a.size(); i++) {
Object obj = a.get(i);
Class<?> clazz = obj.getClass();
Field[] declaredFields = clazz.getDeclaredFields();
for (int j = 0; j < declaredFields.length; j++) {
declaredFields[j].setAccessible(true);
try {
String value = (String) declaredFields[j].get(obj);
if (value != null) {
sheet.addCell(new Label(j, i + 1, value, bodyCellFormat));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
return this;
}
/**
* 输出流
*/
public void build() {
try {
book.write();
book.close();
outputStream.flush();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
使用方法
//JdbcTemplate查询返回的list
List<TouristExportDto> list = this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(TouristExportDto.class));
ExcelBuilder.create().setResponse(response)
.setTitle("标题")
.setBoldHeaderFont("宋体", 20, Colour.WHITE, UnderlineStyle.NO_UNDERLINE, true)
.setBoldBodyFont("宋体", 20, Colour.BLACK, UnderlineStyle.NO_UNDERLINE, true)
.setHeader("英文名称", "中文名称", "性别", "国籍", "职业", "出生日期", "证件号码")
.setList(Collections.singletonList(list).get(0)).build();