从3月5号开始,和几个同学开发一个项目,近期刚刚上线,博客又荒了好多。项目里涉及到spring框架下导出excel文档,在网上查了一下,相关资料还是挺多的,不过觉得很多资料比较陈旧,查了好几篇文章才搞定,所以还是决定将涉及到的相关技术整理出来。我讲的会努力做到清晰,不过并不是特别完整,因为目前用的笔记本内存小,速度有点慢,打开ide内存就占了80%,再开个浏览器,内存基本用到97%,也就不想额外花时间写一个完整的示例demo了。另外,下面只是导出excel的一种方法,实际还有其他方法,需要按照自己使用场景选择。
1. 首先maven包含apache poi库。如果使用IntelliJ IDEA,在工程pom文件包含下面代码后,IntelliJ IDEA就会自动下载相应poi库,非常省事。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
2. 浏览器发起导出excel表单操作。如果直接点击按钮提交,导出excel会简单一些,对于服务端返回的数据流,浏览器解析后会自动弹出保存对话框,此时只需选择保存路径及文件名即可完成下载。如果采用ajax方式,由于传输的是字符串,此时浏览器解析后不会自动弹出保存对话框,也就无法完成保存,不过稍微麻烦一些,需要通过类似下面的js代码,间接构造表单并提交请求就可以了。比如:
function doExportExcel(ids){
var param = '';
for (var i = 0; i < ids.length; i++) {
param += ids[i] + ',';
}
var url = '${path}/exportExcel.do';
jQuery('<form action="'+ url +'" method="post"><input type="text" name="ids" value="' + param +'"/></form>').appendTo('body').submit().remove();
}
ids是一系列待导出的数据id数组,比如浏览器勾选一组要导出的数据,然后提交到服务端导出相应的数据。
3. 服务端接受请求。我目前的使用场景是,浏览器端提交一组待导出的数据id,如上面js代码所示,服务端接收相应参数后,解析id,并依次查询数据。详细就不讲了,假设当前要导出的是User数据,已经查询完毕并整理好,保存在List<User>数组里。User就是一个普通java bean,定义如下:
public class User {
private Integer index;// 序号
private String cardNumber;// 收款账号
private String realname;// 收款户名
private String bankName;// 收款银行
private Float money;// 转账金额
public User() {
}
public User(Integer index, String cardNumber, String realname, String bankName, Float money) {
this.index = index;
this.cardNumber = cardNumber;
this.realname = realname;
this.bankName = bankName;
this.money = money;
}
public Integer getIndex() {
return index;
}
public void setIndex(Integer index) {
this.index = index;
}
public String getCardNumber() {
return cardNumber;
}
public void setCardNumber(String cardNumber) {
this.cardNumber = cardNumber;
}
public String getRealname() {
return realname;
}
public void setRealname(String realname) {
this.realname = realname;
}
public String getBankName() {
return bankName;
}
public void setBankName(String bankName) {
this.bankName = bankName;
}
public Float getMoney() {
return money;
}
public void setMoney(Float money) {
this.money = money;
}
}
导出excel示例代码如下:
@ResponseBody
@RequestMapping(value = "/exportExcel", method = RequestMethod.POST)
public ModelAndView exportExcel(HttpServletRequest request, HttpServletResponse response, String ids) {
List<User> users = new ArrayList<>();
/*...省略数据查询与整理过程...*/
// 导出excel
String[] headers = {"序号", "收款账号", "收款户名", "收款银行", "转账金额"};
String[] fieldNames = {"index", "cardNumber", "realname", "bankName", "money"};
try {
ExportDataToExcel exportExcel = new ExportDataToExcel();
XSSFWorkbook workbook = exportExcel.exportExcel("人员名单", headers, fieldNames, loanUsers);
ViewExcel viewExcel = new ViewExcel();
viewExcel.buildExcelDocument(null, workbook, request, response);
return new ModelAndView(viewExcel);
} catch (Exception e) {
LOGGER.error("导出Excel表格数据输出异常..." + e.getMessage());
}
return null;
}
注意上面headers及fieldNames数组元素与User类成员的对应关系。
public class ViewExcel extends AbstractXlsxView {
@Override
public void buildExcelDocument(Map<String, Object> map, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
String fileName = "人员名单_" + DateUtil.getDateFormat(new Date()) + ".xlsx";
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
}
public class ExportDataToExcel {
private static Logger LOGGER = LoggerFactory.getLogger(ExportDataToExcel.class);
// 创建excel文档并填充数据
public XSSFWorkbook exportExcel(String title, String[] headers, String[] fieldNames, List<User> data) {
// 工作薄xlsx文件
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet(title);
sheet.setColumnWidth(0, 2000);
sheet.setColumnWidth(1, 6500);
sheet.setColumnWidth(2, 3000);
sheet.setColumnWidth(3, 3000);
sheet.setColumnWidth(4, 3000);
// 字体
XSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
// 样式
XSSFCellStyle textStyle = createCellStyle(workbook, font);
// 标题行
XSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(textStyle);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
// 数据行
Iterator<User> it = data.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
User t = (User) it.next();
// 根据javabean属性的先后顺序, 动态调用getXxx()方法获取属性值
for (int i = 0; i < fieldNames.length; i++) {
XSSFCell cell = row.createCell(i);
String fieldName = fieldNames[i];
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
Class<? extends Object> tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
Object value = getMethod.invoke(t, new Object[]{});
// 判断值的类型后进行强制类型转换
String textValue;
if (value instanceof Boolean) {
boolean bValue = (Boolean) value;
textValue = bValue ? "是" : "否";
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
textValue = sdf.format(date);
} else {
textValue = (value != null) ? value.toString() : null;
}
if (textValue != null) {
if (textValue.length() < 8) {
// 注意规避手机号和银行卡号(长度小于8位的当作数字类型处理)
try {
if (textValue.matches("[0-9]+")) {
XSSFCellStyle intStyle = createCellStyle(workbook, font);
XSSFDataFormat dataFormat = workbook.createDataFormat();
intStyle.setDataFormat(dataFormat.getFormat("0"));
cell.setCellStyle(intStyle);
cell.setCellValue(Integer.parseInt(textValue));
} else {
XSSFCellStyle floatStyle = createCellStyle(workbook, font);
XSSFDataFormat dataFormat = workbook.createDataFormat();
floatStyle.setDataFormat(dataFormat.getFormat("0.00"));
cell.setCellStyle(floatStyle);
cell.setCellValue(Double.parseDouble(textValue));
}
} catch (Exception e) {
cell.setCellStyle(textStyle);
cell.setCellValue(textValue);
}
} else {
cell.setCellStyle(textStyle);
cell.setCellValue(textValue);
}
}
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
}
}
}
return workbook;
}
// 创建样式
private XSSFCellStyle createCellStyle(XSSFWorkbook workbook, XSSFFont font) {
XSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.WHITE.index);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
style.setFont(font);
return style;
}
}