<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
引入依赖之后,页面点击导出按钮,触发js请求后台导出的逻辑,js代码如下:
let paramData = 'flowType=' + this.searchData.flowType;
window.open('/api/chargeFlow/exportSupplierData?' + paramData);
js请求到的后台controller代码如下:
@RestController
@RequestMapping(value = "/api/chargeFlow")
public class SupplierController {
@Resource
private ExportSupplierDataService exportSupplierDataService;
@RequestMapping(value = "exportSupplierDailyGroupBill")
public String exportSupplierDailyGroupBill(HttpServletRequest request) {
exportSupplierDataService.execute(request);
return null;
}
}
execute方法如下:
protected void execute(HttpServletRequest request) {
String flowType = request.getParameter("flowType");
HttpServletResponse response = getCrrentResponse();
if (response == null) {
bizContextVo.setErrorMsgVo(ErrorUtils.buildExceptionVo("", "获取response失败!"));
return;
}
Workbook workbook = null;
//根据flowType查询数据库获取数据List<SupplierVo> supplierVos
workbook=processData2WorkBook(supplierVos);
if (workbook == null) {
//Excel文件构建失败!
return;
}
OutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
response.reset();
// 文件名称
String fileName = '供货商数据导出' + DateUtils.formatDate(new Date(), DateUtils.DATE_FORMAT_YYYYMMDDHHMMSS) + ".xls";
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
// 设定输出文件头
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
// 定义输出类型
response.setContentType("application/msexcel");
workbook.write(outputStream);
} catch (Exception e) {
//Excel文件导出失败!
} finally {
try {
if (outputStream != null) {
outputStream.flush();
outputStream.close();
}
} catch (IOException e) {
//Excel文件流关闭失败!
}
}
}
private Workbook processData2WorkBook(List<SupplierVo> data) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
if (CollectionUtils.isEmpty(data)) {
return workbook;
}
sheet.setDefaultRowHeightInPoints(18);
// 表头单元格样式
HSSFCellStyle titleStyle = workbook.createCellStyle();
// 自动换行
titleStyle.setWrapText(true);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleStyle.setFont(font);
// 文本单元格样式
HSSFCellStyle style = workbook.createCellStyle();
// 自动换行
// style.setWrapText(true);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 创建表头
HSSFRow firstRow = sheet.createRow(0);
for (int i = 0; i < 3; i++) {
HSSFCell cell = firstRow.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellValue("列名称"+i);
sheet.setColumnWidth(i, 3000);
}
int dataRowNum = 1
for (SupplierVo supplierVo : data) {
if (supplierVo == null) {
continue;
}
HSSFRow dataRow = sheet.createRow(dataRowNum++);
createCell(sheet, dataRow, style, 0, supplierVo.getName());
createCell(sheet, dataRow, style, 1, supplierVo.getPrice());
createCell(sheet, dataRow, style, 2, supplierVo.getNum());
} } }
return workbook; }
/**
* 获取当前response
* * @return */
private HttpServletResponse getCrrentResponse() {
ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
if (servletRequestAttributes == null) {
return null;
}
HttpServletResponse response = servletRequestAttributes.getResponse();
return response;
}
/**
* 创建列
* * @param row
* @param cellIndex
* @param value
*/
public void createCell(HSSFSheet sheet, HSSFRow row, HSSFCellStyle style, int cellIndex, Object value) {
HSSFCell cell = null;
if (null == value) {
row.createCell(cellIndex, HSSFCell.CELL_TYPE_BLANK);
} else {
String tempValue = value.toString();
cell = row.createCell(cellIndex);
cell.setCellValue(tempValue); cell.setCellStyle(style);
// 设置列宽
if (StringUtils.isNotBlank(tempValue)) {
int valueWidth = tempValue.getBytes().length * 256;
int columnWidth = sheet.getColumnWidth(cellIndex);
if (valueWidth > columnWidth) {
sheet.setColumnWidth(cellIndex, valueWidth);
} } } }