最近项目中需要将table 中数据导出到excel ,当时我想的两种方案,一种是通过前端插件TableExport.js。发现简单使用的话,只是可以导出table 中原生的数据。一旦table 有jstl 标签判断的话,它读不到。最后采取通过服务端到来实现导出Excel 格式是.xlsx。这个方案可以导出大量的数据。分页显示。核心代码如下;
1、这个公用的方法不带表头标题的。
/**
* 不带表头的Excel
* @param title
* @param headMap
* @param jsonArray
* @param datePattern
* @param colWidth
* @param out
*/
public static void exportToExcel(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);
// 生成一个表格
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 headerRow = sheet.createRow(0); //列头 rowIndex =1
for(int i=0;i<headers.length;i++)
{
headerRow.createCell(i).setCellValue(headers[i]);
}
rowIndex = 1;//数据内容从 rowIndex=1开始
}
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();
newCell.setCellValue(cellValue);
}
rowIndex++;
}
// 自动调整宽度
sheet.trackAllColumnsForAutoSizing();
for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}
try {
workbook.write(out);
workbook.close();
boolean flag = workbook.dispose();//释放磁盘空间。处理在磁盘上支持这个工作簿的临时文件。调用该方法将使工作簿不可用。
System.out.println(flag);//如果所有临时文件都被成功删除,则为真。
} catch (IOException e) {
e.printStackTrace();
}
}
2、如何调用。
/**
* 整合数据导出到Excle
*
* @param response
* @param userApplyMap
*/
public void exportExcle(HttpServletResponse response, List<Map<String, Object>> userApplyMap) {
ServletOutputStream outputStream = null;
FileInputStream fileInputStream = null;
String toDir = null;
try {
JSONArray userApplyInfos = new JSONArray();
if (!userApplyMap.isEmpty()) {// 数据集
for (Map<String, Object> map : userApplyMap) {
com.alibaba.fastjson.JSONObject userApply = new com.alibaba.fastjson.JSONObject();
userApply.put("userName", map.get("userName"));
userApply.put("bankOpeningName", map.get("bankOpeningName"));
userApply.put("expenditure", map.get("expenditure"));
userApply.put("amount", map.get("amount"));
userApply.put("serviceFee", map.get("serviceFee"));
userApply.put("bankNo", map.get("bankNo"));
userApply.put("withdraw", getWithdrawOrStatus(map, 1));
userApply.put("bankOpening", new StringBuilder(
map.get("bankOpening").toString() + "\n" + map.get("bankName").toString()));
userApply.put("status", getWithdrawOrStatus(map, 2));
userApply.put("addTime",
DateFormat.getFormatNowTime(Long.parseLong(map.get("addTime").toString()), null));
userApplyInfos.add(userApply);
}
Map<String, String> headMap = new LinkedHashMap<String, String>();// 存放表头部信息
headMap.put("userName", "账号");
headMap.put("bankOpeningName", "收款人");
headMap.put("expenditure", "金额");
headMap.put("amount", "金额");
headMap.put("serviceFee", "手续费");
headMap.put("bankNo", "账户");
headMap.put("withdraw", "方式");
headMap.put("bankOpening", "信息");
headMap.put("status", "状态");
headMap.put("addTime", "申请时间");
Random random = new Random();
String title = "提现信息";
// 生成文件临时存放目录
toDir = QuzuUtils.getInstance().getDir(random);
String path = new StringBuffer().append(toDir).append(title).append(QuzuConst.TYPE_ARRAY[0]).toString();
OutputStream outXlsx = new FileOutputStream(path);
ExcelUtil.exportToExcel(headMap, userApplyInfos, null, 0, outXlsx);
outXlsx.close();
response.setHeader("Content-Disposition", "attachment;filename="
+ new String((title + QuzuConst.TYPE_ARRAY[0]).getBytes(), "iso-8859-1"));
outputStream = response.getOutputStream();
fileInputStream = new FileInputStream(path);
byte[] bytes = new byte[1024];
int size;
while (-1 != (size = fileInputStream.read(bytes))) {
outputStream.write(bytes, 0, size);
}
outputStream.flush();
} else {
ResponseUtil.write(response, "暂无数据可导出");
}
} catch (Exception e) {
logger.error("导出提现信息异常", e);
} finally {
if (fileInputStream != null)
try {
fileInputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
if (outputStream != null)
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
// 删除临时文件
Utils.getInstance().deleteTempFiles(toDir);
}
/**
* 导出excle
*
* @param userApplySearchVo
* @param request
* @param response
*/
@RequestMapping("/exportInfo")
public void exportInfo(UserApplySearchVo userApplySearchVo, HttpServletRequest request,
HttpServletResponse response) {
try {
Map<String, Object> params = new HashMap<>();
creatParams(userApplySearchVo, params);
List<Map<String, Object>> userAccountApplies = userAccountApplyManageService.selectListWithUserName(params);//查出需要导出的数据
exportExcle(response, userAccountApplies);//这里调用
} catch (Exception e) {
logger.error("查询列表异常", e);
}
}
3、效果图,号码随便抓的就打码。