1、增加导出Excel方法
/**
* Excel导出调用方法
*
* @param fileName 导出的文件名称
* @param columnNames 导出的列名称
* @param keys 导出的列名称对应的英文Key
* @param dataList 需要导出的数据 通常情况就是列表查询出来的结果
* @param response HttpServletResponse
* @param msg 异常信息
* @throws IOException
*/
public static void exportExcel(String fileName, String[] columnNames, String[] keys, List<Map<String, Object>> dataList, HttpServletResponse response, String msg) throws IOException {
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
logger.info("-------export start--------");
ExportExcelUtil.createWorkBook(dataList, keys, columnNames).write(os);
} catch (IOException e) {
logger.error(msg + e.getMessage());
e.printStackTrace();
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes("GBK"), "iso-8859-1"));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null) {
bis.close();
}
if (bos != null) {
bos.close();
}
}
logger.info("-------export end, export success--------");
}
2、设置Excel样式
/**
* 创建excel文档,
*
* @param list 数据
* @param keys list中map的key数组集合
* @param columnNames excel的列名
*/
public static SXSSFWorkbook createWorkBook(List<Map<String, Object>> list, String[] keys, String[] columnNames) {
// 创建excel工作簿
SXSSFWorkbook wb = new SXSSFWorkbook();
// 创建第一个sheet(页),并命名
Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
// 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。
for (int i = 0; i < keys.length; i++) {
sheet.setColumnWidth(i, (short) (35.7 * 150));
}
// 创建第一行
Row row = sheet.createRow((short) 0);
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
// 创建两种字体
Font f = wb.createFont();
Font f2 = wb.createFont();
// 创建第一种字体样式(用于列名)
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
// 创建第二种字体样式(用于值)
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// 设置第一种单元格的样式(用于列名)
cs.setFont(f);
// 设置第二种单元格的样式(用于值)
cs2.setFont(f2);
//设置列名
for (int i = 0; i < columnNames.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
//设置每行每列的值
for (int i = 1; i < list.size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行,在页sheet上
Row row1 = sheet.createRow(i);
// 在row行上创建一个方格
for (short j = 0; j < keys.length; j++) {
Cell cell = row1.createCell(j);
cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
cell.setCellStyle(cs2);
}
}
return wb;
}
3、Controller调用
public void exportDeviceSims(DeviceReq req, HttpServletResponse response,User user) {
List<DeviceSimRsp> deviceSims = getDeviceSims(req,user).getRecords();
String[] columnNames = new String[]{"需要导出的列表"};
String[] keys = new String[]{"实体中对应列属性字段"};
try {
ExportExcelUtil.exportExcel("文件名称", columnNames, keys, ExportExcelUtil.createExclData(deviceSims), response, "导出失败");
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public static List<Map<String, Object>> createExclData(List<?> list) {
List<Map<String, Object>> mapList = new ArrayList<>();
Map<String, Object> map1 = new HashMap<String, Object>();
map1.put("sheetName", "sheet1");
mapList.add(map1);
for (int i = 0, n = list.size(); i < n; i++) {
Object bean = list.get(i);
Map<String, Object> map = JSON.parseObject(JSON.toJSONString(bean), Map.class);
mapList.add(map);
}
return mapList;
}