js代码 使用input隐藏域提交json数据
ids = ids.substr(0,ids.length-1);
keys = keys.substr(0,keys.length-1);
columnNames = columnNames.substr(0,columnNames.length-1);
var sParms = {
ids: ids,
keys: keys,
columnNames: columnNames
};
var parametersStr = JSON.stringify(sParms).toString();
console.log(parametersStr);
var url = "/exch_platform/cashInfo/export";
//创建隐藏表单
exportForm = document.createElement("form");
exportForm.setAttribute('id',"_exportForm");
exportForm.setAttribute("action", url);
exportForm.setAttribute("method", "post");
//对应查询条件的开始时间
var input1 = document.createElement("input");
input1.type="text";
input1.name = "idsP";
input1.value = parametersStr;
exportForm.append(input1);
document.body.appendChild(exportForm);
exportForm.submit();
Java后端接收并处理参数
@RequestMapping(value = "/export",method = RequestMethod.POST)
public void export(HttpServletRequest request, HttpServletResponse response) {
String idsP = request.getParameter("idsP");
JSONObject jsonObject = JSONObject.parseObject(idsP);
String idStr = jsonObject.getString("ids");
String[] ids = new String[0];
if(StringUtils.isNoneEmpty(idStr)){
ids = idStr.split(",");
}
String keys = jsonObject.getString("keys");
String columnNames = jsonObject.getString("columnNames");
cashInfoService.selectByConditionToExport(request, response, ids);
}
service层处理数据
excel导出工具类
public static void downloadExcel(HttpServletResponse response, String fileName,
List<Map<String,Object>> list, String[] keys, String[] columnNames) throws UnsupportedEncodingException, IOException {
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
ExcelUtil.createWorkBook(list,keys,columnNames).write(os);
} catch (IOException e) {
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(), "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;
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();
}
}
将数据写入excel
/**
* 创建excel文档,
* @param list 数据
* @param keys list中map的key数组集合
* @param columnNames excel的列名
* */
public static Workbook createWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) {
// 创建excel工作簿
Workbook wb = new HSSFWorkbook();
// 创建第一个sheet(页),并命名
//Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
Sheet sheet = wb.createSheet("sheet1");
// 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。
for(int i=0;i<keys.length;i++){
sheet.setColumnWidth((short) 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());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 创建第二种字体样式(用于值)
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// Font f3=wb.createFont();
// f3.setFontHeightInPoints((short) 10);
// f3.setColor(IndexedColors.RED.getIndex());
// 设置第一种单元格的样式(用于列名)
cs.setFont(f);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);
// 设置第二种单元格的样式(用于值)
cs2.setFont(f2);
cs2.setBorderLeft(CellStyle.BORDER_THIN);
cs2.setBorderRight(CellStyle.BORDER_THIN);
cs2.setBorderTop(CellStyle.BORDER_THIN);
cs2.setBorderBottom(CellStyle.BORDER_THIN);
cs2.setAlignment(CellStyle.ALIGN_CENTER);
//设置列名
for(int i=0;i<columnNames.length;i++){
Cell cell = row.createCell(i);
/**
* 对括号进行转义 add by 白毅
*/
if(columnNames[i].indexOf("& #41;") != -1){
columnNames[i] = columnNames[i].replaceAll("& #40;","(");
columnNames[i] = columnNames[i].replaceAll("& #41;",")");
}
cell.setCellValue(StringEscapeUtils.unescapeJava(columnNames[i]));
cell.setCellStyle(cs);
}
//设置每行每列的值
for (int i = 1; i <= list.size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行,在页sheet上
Row row1 = sheet.createRow((int) i);
// 在row行上创建一个方格
for(short j=0;j<keys.length;j++){
Cell cell = row1.createCell(j);
if(list.get(i-1).get(keys[j]) != null){
if(StringUtils.isNoneEmpty(list.get(i-1).get(keys[j]).toString()) && BusinessUtils.isNumeric(list.get(i-1).get(keys[j]).toString())){
cell.setCellValue(BusinessUtils.rvZeroAndDot(list.get(i-1).get(keys[j]).toString()));
}else{
cell.setCellValue(list.get(i-1).get(keys[j]) == null ? " " : list.get(i-1).get(keys[j]).toString());
}
}else{
cell.setCellValue(list.get(i-1).get(keys[j]) == null ? " " : list.get(i-1).get(keys[j]).toString());
}
cell.setCellStyle(cs2);
}
}
return wb;
}