写在前面
此为总结篇，很多都是参考网友总结的方法，然后在自己的项目中实践，感觉很实用，又不想存粹的拿来主义，现将原文链接附上以作记录和参考：
1、http://blog.csdn.net/houxuehan/article/details/50960259
2、http://www.cnblogs.com/wqsbk/p/5417180.html
3、http://www.anyrt.com/blog/list/poiexcel.html
4、http://poi.apache.org/spreadsheet/quick-guide.html 「官方」
本文中我们将看到什么
- Maven配置
- 导出数据列表到Excel的公共方法
- 工具类调用接口
- Controller书写样例
- 生成带有文档内部链接的Excel文档
- 多任务通用模式下载任务解决方案，采用阻塞队列和线程池
Maven
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
导出数据列表到Excel的公共方法
/**
* 导出excel公共方法，适合列表类文件导出。
* 导出文件中的数值列支持选择计算统计。
* 此方法为内存型导出，请注意数据量过大的问题
* @param title
* @param headMap
* @param jsonArray
* @param datePattern
* @param colWidth
* @param out
*/
public static void exportExcelX(String title, Map<String, String> headMap, JSONArray jsonArray,
String datePattern, int colWidth, OutputStream out) {
if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
// 声明一个工作薄
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
workbook.setCompressTempFiles(true);
//表头样式
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
Font titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 20);
titleFont.setBoldweight((short) 700);
titleStyle.setFont(titleFont);
// 列头样式
CellStyle headerStyle = workbook.createCellStyle();
//headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setAlignment(HorizontalAlignment.CENTER);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
//cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
// 生成一个(带标题)表格
SXSSFSheet sheet = workbook.createSheet();
//设置列宽
int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
String fieldName = iter.next();
properties[ii] = fieldName;
headers[ii] = headMap.get(fieldName);
int bytes = fieldName.getBytes().length;
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
ii++;
}
// 遍历集合数据，产生数据行
int rowIndex = 0;
for (Object obj : jsonArray) {
if (rowIndex == 65535 || rowIndex == 0) {
if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
SXSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
titleRow.createCell(0).setCellValue(title);
titleRow.getCell(0).setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
SXSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
for (int i = 0; i < headers.length; i++) {
headerRow.createCell(i).setCellValue(headers[i]);
headerRow.getCell(i).setCellStyle(headerStyle);
}
rowIndex = 2;//数据内容从 rowIndex=2开始
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
SXSSFRow dataRow = sheet.createRow(rowIndex);
for (int i = 0; i < properties.length; i++) {
SXSSFCell newCell = dataRow.createCell(i);
Object o = jo.get(properties[i]);
String cellValue = "";
if (o == null) cellValue = "";
else if (o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
else if (o instanceof Float || o instanceof Double) {
cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
}
else cellValue = o.toString();
if (o instanceof Float || o instanceof Double || o instanceof BigDecimal) {
newCell.setCellValue(Double.parseDouble(cellValue));
}else if(o instanceof Integer) {
newCell.setCellValue(Integer.parseInt(cellValue));
}else{
newCell.setCellValue(cellValue);
}
newCell.setCellStyle(cellStyle);
}
rowIndex++;
}
try {
workbook.write(out);
} catch (IOException e) {
log.error("导出Excel操作时组装Excel文件出现异常", e);
} finally {
if(workbook != null) {
try {
workbook.close();
workbook.dispose();
} catch (IOException e) {
log.error("关闭流时出现异常", e);
}
}
}
}
以上方法适用于导出数据列表，提供文件大标题、列标题等，而且对数值列进行了专门的格式化处理，以方便需要后期选中统计处理。这种通用方法是内存型处理方式，需要注意查询的列表内容不能过大，可能上十万还能hold住，主要还是看各位的机器性能和内存容量，否则可能出现OOM，通常对于大文件的处理，建议分批查询再写入到excel中。excel文档可能存在sheet页行数不能超过65535的问题，对于这样的解决方案以上方法也已给出，就是扩展到另外一张sheet页。
针对以上方法，我们给出一个使用样例，供大家参考使用。
工具类调用接口
/**
* 导出文件到本地指定目录
* @param title
* @param fileName
* @param headMap
* @param ja
*/
public static void downloadExcelFile(String title, String fileName, Map<String, String> headMap, JSONArray ja) {
try {
FileOutputStream fileOut = new FileOutputStream(fileName);
ExcelUtil.exportExcelX(title, headMap, ja, null, 0, fileOut);
} catch (Exception e) {
log.error("处理导出文件时遇到异常", e);
}
}
/**
* 通过web调用方式直接导出Excel文件
* @param title
* @param headMap
* @param ja
* @param response
*/
public static void downloadExcelFile(String title, Map<String, String> headMap, JSONArray ja, HttpServletResponse response) {
try {
ByteArrayOutputStream os = new ByteArrayOutputStream();
ExcelUtil.exportExcelX(title, headMap, ja, null, 0, os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面
response.reset();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
response.setContentLength(content.length);
ServletOutputStream outputStream = response.getOutputStream();
BufferedInputStream bis = new BufferedInputStream(is);
BufferedOutputStream bos = new BufferedOutputStream(outputStream);
byte[] buff = new byte[8192];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
// 自行在finally中处理流的关闭
bis.close();
bos.close();
outputStream.flush();
outputStream.close();
} catch (Exception e) {
log.error("导出Excel操作时操作下载请求出现异常", e);
}
}
基于SpringBoot的Controller
@RequestMapping("export")
@ResponseBody
public void exportOrderListData(HttpServletRequest request, HttpServletResponse response) {
// 查询条件 【略】
PageQuery pageQuery = new PageQuery();
// 查询数据
pageQuery.setPageSize(Integer.MAX_VALUE);
orderInfoService.queryOrderLogInfoList(pageQuery);
// 数据集合，将其转换成json串
String jsonString = JSON.toJSONString(pageQuery.getList());
JSONArray jsonArray = JSON.parseArray(jsonString);
// 表头，key是获取数据时将要调用的get方法，value是显示的列标题
Map<String, String> headMap = new LinkedHashMap<>();
headMap.put("operationTime", "日期");
headMap.put("buyerPhoneNum", "手机号");
headMap.put("tradingNum", "交易ID");
headMap.put("areaInfo", "区域信息");
headMap.put("deviceNo", "设备编号");
headMap.put("deviceAddr", "设备地址");
headMap.put("cardSlotName", "货道名称");
headMap.put("commId", "商品编号");
headMap.put("commodityName", "商品名称");
headMap.put("tradingPrice", "成本价");
headMap.put("tradingPrice", "零售价");
headMap.put("buyerPayPrice", "实际售价");
headMap.put("empty", "代金券");
headMap.put("empty", "大转盘奖励商品");
headMap.put("empty", "随机立减金额");
// 导出表格名称
String title = "订单列表";
// 执行导出
ExcelUtil.downloadExcelFile(title, headMap, jsonArray, response);
}
在controller中直接调用导出文件到浏览器的接口，用户的浏览器将自动阻塞并等待文件下载，如果调用生成文件到本地的方法，则没有返回，直接在本地磁盘生成文件。需要注意的是，如果待导出文件很大，将会花费很长的计算和生成excel文档的时间，这可能会导致用户浏览器请求出现超时，影响用户体验，到时具体问题具体处理，你可以自己想办法规避这种情况，比如采用异步执行任务的方式，用户点击导出之后开启异步线程进行处理，然后直接返回提示信息，稍后将下载文件的链接给到用户即可。（下面有涉及到）
Tips：如果想通过web调用直接导出Excel文件，那么在前端页面需要使用提交表单跳转的方式来请求下载，不能使用Ajax调用的方式，这点相信大家应该明了。
比如，你可以写出这种通用的调用方式：
function export(action){
var form = $('#form');
form.attr('action', action);
form.attr('method', 'post');
form.attr('target', '_self');
form.submit();
}
到这里，相信你已经具备初步的导出Excel功能了，但是很多地方还需要根据自身情况进行优化，再有，以上编码难免有错误，请自行更正，相信都比较简单，而且估计导出Excel方法中很多方法都已过时，可以通过查看源码的方式，找到更新更优的方法。
下面，我们给出一个针对复杂一点儿的业务（或者说场景）给出的一种文档方式。
带有内部链接的Excel
估计你也有遇到这种情况，就是导出的列表数据中，可能需要点击某个值，再链接到它关联的详情信息页面，这在excel中也是可以实现的，具体就是，通过点击某个单元格，然后链接到某个sheet页以显示更加详细的内容，同时，在详情页面上，额外增加一个「回到主页」的超链，这样整个文档就和在页面中点击差不多效果了。
比如下面这种效果：
列表页面
详情页面
基于SpringBoot的Controller中的实现方法
@RequestMapping("exportFile")
@ResponseBody
public void exportExcelWithDetail(HttpServletRequest request, HttpServletResponse response) {
PageQuery pageQuery = new PageQuery<>();
// 查询条件 【略】
log.info("开始执行导出商品销售数据信息Excel文件【START】");
FileOutputStream fileOut = null;
ByteArrayOutputStream os = new ByteArrayOutputStream();
// 流式下载
ServletOutputStream outputStream = null;
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try{
XSSFWorkbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
// 创建主页
XSSFSheet sheet = wb.createSheet("Home");
// 创建页头
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell((short) 0);
cell.setCellValue("商品销售数据信息");
sheet.addMergedRegion(new CellRangeAddress(
0, //first row (0-based)
0, //last row (0-based)
0, //first column (0-based)
4 //last column (0-based)
));
// 创建页头样式
// -- 字体
XSSFFont font = wb.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("Microsoft YaHei UI");
font.setBold(true);
// -- 页头样式
XSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
style.setAlignment(HorizontalAlignment.CENTER);
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// -- 赋予页头样式
cell.setCellStyle(style);
// 创建标题栏
XSSFRow titleRow = sheet.createRow(1);
Cell title_cell_0 = titleRow.createCell((short) 0);
title_cell_0.setCellValue("日期");
Cell title_cell_1 = titleRow.createCell((short) 1);
title_cell_1.setCellValue("区域信息");
Cell title_cell_2 = titleRow.createCell((short) 2);
title_cell_2.setCellValue("设备编号");
Cell title_cell_3 = titleRow.createCell((short) 3);
title_cell_3.setCellValue("商品销售总量");
Cell title_cell_4 = titleRow.createCell((short) 4);
title_cell_4.setCellValue("商品销售详情");
// 设置单元格宽度
int width = "商品销售详情".getBytes().length * 256;
sheet.setColumnWidth(0, width);
sheet.setColumnWidth(1, width);
sheet.setColumnWidth(2, width);
sheet.setColumnWidth(3, width);
sheet.setColumnWidth(4, width);
// -- 字体
XSSFFont titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short)12);
titleFont.setFontName("Microsoft YaHei UI");
titleFont.setColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
// -- 标题样式
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HorizontalAlignment.LEFT);
titleStyle.setFillBackgroundColor(new XSSFColor(new java.awt.Color(75, 102, 128)));
titleStyle.setFillPattern(FillPatternType.FINE_DOTS);
titleStyle.setBorderTop(BorderStyle.THIN);
titleStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
titleStyle.setBorderRight(BorderStyle.THIN);
titleStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
titleStyle.setBorderBottom(BorderStyle.THIN);
titleStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
titleStyle.setBorderLeft(BorderStyle.THIN);
titleStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 赋予标题栏样式
title_cell_0.setCellStyle(titleStyle);
title_cell_1.setCellStyle(titleStyle);
title_cell_2.setCellStyle(titleStyle);
title_cell_3.setCellStyle(titleStyle);
title_cell_4.setCellStyle(titleStyle);
// 超链接样式
XSSFCellStyle hlink_style = wb.createCellStyle();
Font hlink_font = wb.createFont();
hlink_font.setUnderline(Font.U_SINGLE);
hlink_font.setColor(IndexedColors.BLUE.getIndex());
hlink_style.setFont(hlink_font);
hlink_style.setAlignment(HorizontalAlignment.CENTER);
hlink_style.setBorderTop(BorderStyle.THIN);
hlink_style.setTopBorderColor(IndexedColors.BLACK.getIndex());
hlink_style.setBorderRight(BorderStyle.THIN);
hlink_style.setRightBorderColor(IndexedColors.BLACK.getIndex());
hlink_style.setBorderBottom(BorderStyle.THIN);
hlink_style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
hlink_style.setBorderLeft(BorderStyle.THIN);
hlink_style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 内容样式-居左
XSSFCellStyle contentStyle_left = wb.createCellStyle();
contentStyle_left.setBorderTop(BorderStyle.THIN);
contentStyle_left.setTopBorderColor(IndexedColors.BLACK.getIndex());
contentStyle_left.setBorderRight(BorderStyle.THIN);
contentStyle_left.setRightBorderColor(IndexedColors.BLACK.getIndex());
contentStyle_left.setBorderBottom(BorderStyle.THIN);
contentStyle_left.setBottomBorderColor(IndexedColors.BLACK.getIndex());
contentStyle_left.setBorderLeft(BorderStyle.THIN);
contentStyle_left.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 内容样式-居中
XSSFCellStyle contentStyle_center = wb.createCellStyle();
contentStyle_center.setAlignment(HorizontalAlignment.CENTER);
contentStyle_center.setBorderTop(BorderStyle.THIN);
contentStyle_center.setTopBorderColor(IndexedColors.BLACK.getIndex());
contentStyle_center.setBorderRight(BorderStyle.THIN);
contentStyle_center.setRightBorderColor(IndexedColors.BLACK.getIndex());
contentStyle_center.setBorderBottom(BorderStyle.THIN);
contentStyle_center.setBottomBorderColor(IndexedColors.BLACK.getIndex());
contentStyle_center.setBorderLeft(BorderStyle.THIN);
contentStyle_center.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
// 计数器
long totalPage = 0l;
long pageNum = 1;
long count = Integer.MAX_VALUE;
// 处理循环
log.info("开始循环插入列表基本数据，并且开始查询销售数据详情信息。");
do{
// 分页查询数据
PageQuery<TbDeviceInfo> pageQuery = new PageQuery();
pageQuery.setPageNumber(pageNum++);
pageQuery.setPageSize(10);
exportQuery(request, pageQuery);
totalPage = pageQuery.getTotalPage();
// 保证某些功能只执行一次
if(count > totalPage) {
count = totalPage;
}
// 搭建数据
List<TbDeviceInfo> tbDeviceInfoList = pageQuery.getList();
if(pageQuery.getTotalRow() > 0 && null != tbDeviceInfoList && !tbDeviceInfoList.isEmpty()) {
log.info("本次需要导出的记录数[同时需要执行该次数DB查询]：{}", pageQuery.getTotalRow());
for(int i=0, len=tbDeviceInfoList.size(); i < len; i++) {
TbDeviceInfo tbDeviceInfo = tbDeviceInfoList.get(i);
// 创建详情sheet页
String prefix = String.valueOf((totalPage - count) * pageQuery.getPageSize() + (i + 1)).concat("_");
XSSFSheet sheetDetail = wb.createSheet(prefix + tbDeviceInfo.getDeviceId()); // sheet页名称为设备ID
XSSFRow detailHeadRow = sheetDetail.createRow(0);
XSSFCell detailHeadCell = detailHeadRow.createCell((short)0);
detailHeadCell.setCellValue("商品销售数据详情");
detailHeadCell.setCellStyle(style);
sheetDetail.addMergedRegion(new CellRangeAddress(
0, //first row (0-based)
0, //last row (0-based)
0, //first column (0-based)
4 //last column (0-based)
));
sheetDetail.setColumnWidth(0, width);
sheetDetail.setColumnWidth(1, width);
sheetDetail.setColumnWidth(2, width);
sheetDetail.setColumnWidth(3, width);
sheetDetail.setColumnWidth(4, width);
sheetDetail.setColumnWidth(5, width);
// 创建回到首页链接
XSSFCell toHomeCell = detailHeadRow.createCell((short)5);
toHomeCell.setCellValue("点击回到首页");
Hyperlink link2 = createHelper.createHyperlink(HyperlinkType.DOCUMENT);
link2.setAddress("'Home'!A1");
toHomeCell.setHyperlink(link2);
toHomeCell.setCellStyle(hlink_style);
// 创建详情公共信息
// 日期
XSSFRow dateRow = sheetDetail.createRow(1);
dateRow.createCell((short)0).setCellValue("日期");
dateRow.createCell((short)1).setCellValue(tbDeviceInfo.getOrderDate());
dateRow.getCell(0).setCellStyle(contentStyle_left);
dateRow.getCell(1).setCellStyle(contentStyle_left);
dateRow.createCell(2).setCellStyle(contentStyle_left);
dateRow.createCell(3).setCellStyle(contentStyle_left);
dateRow.createCell(4).setCellStyle(contentStyle_left);
sheetDetail.addMergedRegion(new CellRangeAddress(
1, //first row (0-based)
1, //last row (0-based)
1, //first column (0-based)
4 //last column (0-based)
));
// 区域信息
XSSFRow areaRow = sheetDetail.createRow(2);
areaRow.createCell((short)0).setCellValue("区域信息");
areaRow.createCell((short)1).setCellValue(tbDeviceInfo.getAreaInfo());
areaRow.getCell(0).setCellStyle(contentStyle_left);
areaRow.getCell(1).setCellStyle(contentStyle_left);
areaRow.createCell(2).setCellStyle(contentStyle_left);
areaRow.createCell(3).setCellStyle(contentStyle_left);
areaRow.createCell(4).setCellStyle(contentStyle_left);
sheetDetail.addMergedRegion(new CellRangeAddress(
2, //first row (0-based)
2, //last row (0-based)
1, //first column (0-based)
4 //last column (0-based)
));
// 设备编号
XSSFRow deviceNoRow = sheetDetail.createRow(3);
deviceNoRow.createCell((short)0).setCellValue("设备编号");
deviceNoRow.createCell((short)1).setCellValue(tbDeviceInfo.getDeviceId());
deviceNoRow.getCell(0).setCellStyle(contentStyle_left);
deviceNoRow.getCell(1).setCellStyle(contentStyle_left);
deviceNoRow.createCell(2).setCellStyle(contentStyle_left);
deviceNoRow.createCell(3).setCellStyle(contentStyle_left);
deviceNoRow.createCell(4).setCellStyle(contentStyle_left);
sheetDetail.addMergedRegion(new CellRangeAddress(
3, //first row (0-based)
3, //last row (0-based)
1, //first column (0-based)
4 //last column (0-based)
));
// 设备地址
XSSFRow deviceAddrRow = sheetDetail.createRow(4);
deviceAddrRow.createCell((short)0).setCellValue("设备地址");
deviceAddrRow.createCell((short)1).setCellValue(tbDeviceInfo.getDeviceAddr());
deviceAddrRow.getCell(0).setCellStyle(contentStyle_left);
deviceAddrRow.getCell(1).setCellStyle(contentStyle_left);
deviceAddrRow.createCell(2).setCellStyle(contentStyle_left);
deviceAddrRow.createCell(3).setCellStyle(contentStyle_left);
deviceAddrRow.createCell(4).setCellStyle(contentStyle_left);
sheetDetail.addMergedRegion(new CellRangeAddress(
4, //first row (0-based)
4, //last row (0-based)
1, //first column (0-based)
4 //last column (0-based)
));
// 销售详情列表标题
XSSFRow detailTitleRow = sheetDetail.createRow(5);
XSSFCell detailTitle_cell_0 = detailTitleRow.createCell((short)0);
detailTitle_cell_0.setCellValue("序号");
XSSFCell detailTitle_cell_1 = detailTitleRow.createCell((short)1);
detailTitle_cell_1.setCellValue("商品名称");
XSSFCell detailTitle_cell_2 = detailTitleRow.createCell((short)2);
detailTitle_cell_2.setCellValue("货道总容量");
XSSFCell detailTitle_cell_3 = detailTitleRow.createCell((short)3);
detailTitle_cell_3.setCellValue("单价");
XSSFCell detailTitle_cell_4 = detailTitleRow.createCell((short)4);
detailTitle_cell_4.setCellValue("销售数量");
// 设备标题样式
detailTitle_cell_0.setCellStyle(titleStyle);
detailTitle_cell_1.setCellStyle(titleStyle);
detailTitle_cell_2.setCellStyle(titleStyle);
detailTitle_cell_3.setCellStyle(titleStyle);
detailTitle_cell_4.setCellStyle(titleStyle);
// 查询数据库，补充详情数据
Integer deviceId = tbDeviceInfo.getId();
String orderDate = tbDeviceInfo.getOrderDate();
List<GoodsSaleDetailInfo> goodsSaleDetailInfoList = goodsSaleService.queryGoodsSaleDetails(deviceId, orderDate);
if(null != goodsSaleDetailInfoList && !goodsSaleDetailInfoList.isEmpty()) {
for(int j=0, len2=goodsSaleDetailInfoList.size(); j < len2; j++) {
GoodsSaleDetailInfo goodsSaleDetailInfo = goodsSaleDetailInfoList.get(j);
XSSFRow dataRow = sheetDetail.createRow(6 + j);
XSSFCell data_cell_0 = dataRow.createCell((short)0);
data_cell_0.setCellValue(j + 1);
XSSFCell data_cell_1 = dataRow.createCell((short)1);
data_cell_1.setCellValue(goodsSaleDetailInfo.getCommodityName());
XSSFCell data_cell_2 = dataRow.createCell((short)2);
data_cell_2.setCellValue(goodsSaleDetailInfo.getSlotCount());
XSSFCell data_cell_3 = dataRow.createCell((short)3);
data_cell_3.setCellValue(goodsSaleDetailInfo.getCommodityPrice() + "");
XSSFCell data_cell_4 = dataRow.createCell((short)4);
data_cell_4.setCellValue(goodsSaleDetailInfo.getSalesCount());
// 设置内容单元格样式
data_cell_0.setCellStyle(contentStyle_center);
data_cell_1.setCellStyle(contentStyle_left);
data_cell_2.setCellStyle(contentStyle_center);
data_cell_3.setCellStyle(contentStyle_center);
data_cell_4.setCellStyle(contentStyle_center);
}
}
// -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
// 创建主页内容栏
String index = String.valueOf(2 + (totalPage - count) * pageQuery.getPageSize() + i);
XSSFRow contentRow = sheet.createRow(Integer.valueOf(index));
XSSFCell content_cell_0 = contentRow.createCell((short) 0);
content_cell_0.setCellValue(tbDeviceInfo.getOrderDate()); // 日期
XSSFCell content_cell_1 = contentRow.createCell((short) 1);
content_cell_1.setCellValue(tbDeviceInfo.getAreaInfo()); // 区域信息
XSSFCell content_cell_2 = contentRow.createCell((short) 2);
content_cell_2.setCellValue(tbDeviceInfo.getDeviceId()); // 设备编号
XSSFCell content_cell_3 = contentRow.createCell((short) 3);
content_cell_3.setCellValue(tbDeviceInfo.getSalesCount()); // 销售数量
// 文件内链接内容
XSSFCell content_cell_4 = contentRow.createCell((short) 4);
content_cell_4.setCellValue("点击查看");
Hyperlink link2Detail = createHelper.createHyperlink(HyperlinkType.DOCUMENT);
link2Detail.setAddress("'" + prefix + tbDeviceInfo.getDeviceId() + "'!A1");
content_cell_4.setHyperlink(link2Detail);
content_cell_4.setCellStyle(hlink_style);
// 赋予内容单元格样式
content_cell_0.setCellStyle(contentStyle_center);
content_cell_1.setCellStyle(contentStyle_left);
content_cell_2.setCellStyle(contentStyle_left);
content_cell_3.setCellStyle(contentStyle_center);
}
}else {
log.info("本次查询没有匹配到符合条件的记录，无记录导出。");
// 创建无数据内容的提示语
XSSFRow emptyRow = sheet.createRow(2);
XSSFCell emptyCell = emptyRow.createCell((short) 0);
emptyCell.setCellValue("没有查询到符合条件的记录");
sheet.addMergedRegion(new CellRangeAddress(
2, //first row (0-based)
2, //last row (0-based)
0, //first column (0-based)
4 //last column (0-based)
));
XSSFCellStyle emptyStyle = wb.createCellStyle();
emptyStyle.setAlignment(HorizontalAlignment.CENTER);
emptyCell.setCellStyle(emptyStyle);
}
}while(--count > 0);
// -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
// 输出
*//*String filename = DateUtil.getFormatDate(new Date(), "yyyyMMddHHmmssSSS") + "-workbook.xlsx";
fileOut = new FileOutputStream(filename);
wb.write(fileOut);*//*
wb.write(os);
wb.close();
// 执行流下载
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面
response.reset();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+ new String(("商品销售数据信息列表.xlsx").getBytes(), "iso-8859-1"));
response.setContentLength(content.length);
outputStream = response.getOutputStream();
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(outputStream);
byte[] buff = new byte[8192];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
outputStream.flush();
}catch (Exception e) {
log.error("export excel error", e);
}finally {
// 流的关闭 【略】
}
}
上面的代码篇幅有点儿大，方法没有经过抽取和优化，后续可以自行处理，详细逻辑还是很清晰的，加之以注释说明，很容易看懂。
接下来，我们将针对项目中存在多个相同方式的下载任务，提供一种解决方案。
将导出任务添加到队列中，并定时用线程清理队列中的任务，达到对服务器端减压的效果；同时，针对导出后的任务，以通知的形式告知用户导出任务已结束，可以到下载界面进行下载。
这种解决方案的代码实现有很多种，下面将使用阻塞队列以及线程池的方式来处理，仅作参考。
以下是基于SpringBoot的实现方式，代码中可能涉及到其他的技术标签，可忽略
目前只能给出核心代码，实现思路已经很明确，很多实现细节可自行处理，也还有很多可优化之处
Bean配置
@Slf4j
@Configuration
public class ExportMethodConfiguration {
@Bean(initMethod = "execute", destroyMethod = "shutdown")
public ExportBlockingQueueService ExportBlockingQueueService(){
// 创建保存文件的路径
String filePath = "." + File.separator + "excel";
File dir = new File(filePath);
if(!dir.exists()){
dir.mkdir();
log.info("保存Excel文件的目录暂不存在，现已创建成功。{}", filePath);
}
return new ExportBlockingQueueServiceImpl();
}
}
阻塞队列接口和实现
/** 接口 */
public interface ExportBlockingQueueService {
void addTask(ExportExcelWrapper exportExcelService);
void execute();
void shutdown();
}
/** 实现 */
@Slf4j
public class ExportBlockingQueueServiceImpl implements ExportBlockingQueueService {
ScheduledExecutorService scheduledExecutorService = Executors.newScheduledThreadPool(1);
Queue<ExportExcelWrapper> linkedBlockingQueue = new ConcurrentLinkedQueue<>();
@Override
public void addTask(ExportExcelWrapper exportExcelService) {
try {
log.info("导出Excel任务已加入到执行队列中");
linkedBlockingQueue.add(exportExcelService);
} catch (Exception e) {
log.error("将导出excel文件任务加入到队列时出现异常。", e);
}
}
@Override
public void shutdown() {
scheduledExecutorService.shutdown();
}
public void execute() {
scheduledExecutorService.scheduleAtFixedRate(new Runnable() {
@Override
public void run() {
try {
log.info("-------- loop");
ExportExcelWrapper exportExcelWrapper = linkedBlockingQueue.poll();
if (null != exportExcelWrapper) {
log.info("开始执行下载任务");
exportExcelWrapper.getExportExcelService().executeQueryAndExport(exportExcelWrapper.getPageQuery(),
exportExcelWrapper.getOperateUser(), exportExcelWrapper.getDownloadFilePath());
}
} catch (Exception e) {
e.printStackTrace();
}
}
}, 3, 30, TimeUnit.SECONDS);
}
}
导出任务包装类
@Data
public class ExportExcelWrapper {
// 导出文件存放的相对路径
private String downloadFilePath = "." + File.separator + "excel";
// 登录用户
private LoginUser operateUser;
// 查询条件集合
private PageQuery pageQuery;
// 执行导出任务的实现方法
private ExportExcelService exportExcelService;
}
导出Excel任务的公共接口与实现举例
/** 接口声明 */
public interface ExportExcelService {
void executeQueryAndExport(PageQuery pageQuery, LoginUser operateUser, String downloadFilePath);
}
/** 实现举例 */
@Service
@Slf4j
public class UserInfoExportExcelServiceImpl extends ExportExcelHelper implements ExportExcelService {
@Autowired
private OPUserService opUserService;
@Override
public void executeQueryAndExport(PageQuery pageQuery, LoginUser operateUser, String downloadFilePath) {
// 查询数据
pageQuery.setPageSize(Integer.MAX_VALUE);
opUserService.queryUserPage(pageQuery);
// 数据集合
String jsonString = JSON.toJSONString(pageQuery.getList());
JSONArray jsonArray = JSON.parseArray(jsonString);
// 表头
Map<String, String> headMap = new LinkedHashMap<>();
headMap.put("loginName", "登录名称");
headMap.put("userName", "用户名称");
headMap.put("roleName", "用户角色");
headMap.put("email", "Email");
headMap.put("mobile", "联系电话");
headMap.put("accessAuth", "授权准入");
headMap.put("giveTime", "加入时间");
// 导出表格名称
String title = "用户信息列表";
String fileName = DateUtil.getFormatDate(new Date(), "yyyyMMddHHmmssSSS") + title + ".xlsx";
String filePath = downloadFilePath + File.separator + fileName;
// 执行导出
ExcelUtil.downloadExcelFile(title, filePath, headMap, jsonArray);
log.info("导出Excel文件【{}】成功", fileName);
// 保存下载记录
createExportFileRecod(fileName, filePath, operateUser.getUserId());
log.info("保存导出记录成功。");
// 执行邮件发送通知
sendEmail(operateUser, fileName, filePath);
}
}
在Controller中调用
@RequestMapping("exportFile")
@ResponseBody
public void queryUserExportDataList(HttpServletRequest request, HttpServletResponse response) {
// 查询条件 【略】
PageQuery pageQuery = new PageQuery<>();
LoginUser loginUser = (LoginUser) redisExService.getModel(CacheKey.USER_LOGIN.getKey().concat(request.getSession().getId()));
ExportExcelWrapper exportExcelWrapper =new ExportExcelWrapper();
exportExcelWrapper.setExportExcelService(exportExcelService);
exportExcelWrapper.setPageQuery(pageQuery);
exportExcelWrapper.setOperateUser(loginUser);
exportBlockingQueueService.addTask(exportExcelWrapper);
// 可以在这里给用户回复提示语
responseMsg(response);
}
好了，到目前为止，你基本上已经掌握了如何使用Java导出Excel文件了，当然，这里只是导出文件的基本方式，可能实际当中有各种不同各种奇怪的需求，这就需要自己根据实际场景去处理了。
希望上面的知识对你的项目有所帮助。
网友评论