常见的数据处理除了增删改查,还有导入导出。
今天记录一次完整的excel导出流程……
前端jsp
<c:container columnWidth="0.06" style="margin-left:1px">
<c:Container labelWidth="120" width="80" >
<c:Button value="导出数据" style="margin: 3 15px" onClick="exportData"/>
</c:Container>
</c:container>
前端js
function exportData(){
//方法1:携带较少参数,可以直接这么使用
//window.location.href = "../resultHandle.ctrl?method=exportExcel&exportDataType=result";
//方法2:携带较多参数,需要进一步的封装处理
var url='../resultHandle.ctrl?method=exportExcel&exportDataType=result';
downloadByPost({
url : url,
data : {
paramSearch : paramSearch.getValue(),
//此处省略其他参数
}
});
}
function downloadByPost(options) {
var config = $.extend(true, { method: 'post' }, options);
var $iframe = $('<iframe id="down-file-iframe" />');
var $form = $('<form method="' + config.method + '" />');
$form.attr('action', config.url);
for (var key in config.data) {
$form.append('<input type="hidden" name="' + key + '" value="' + config.data[key] + '" />');
}
$iframe.append($form);
$(document.body).append($iframe);
$form[0].submit();
$iframe.remove();
}
后端controller
@RequestMapping(params = "method=exportExcel")
public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
String exportDataType = request.getParameter("exportDataType");
String start = request.getParameter("_startRow");
String pageSize = request.getParameter("_rowCount");
String excelName="导出数据.xlsx";
DataResultVo vo=dataResultBo.buildDataResultVo(request);
List<DataResultVo> list=null;
try {
list = dataResultBo.queryDataResult(vo,start,pageSize);
} catch (SQLException e) {
logger.error("异常提示->"+e.getMessage());
}
Workbook workbook = dataResultBo.createResultExcel(list);
try {
response.setContentType("text/html;charset=utf-8");
response.addHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(excelName,"UTF-8"));
//通过write方法,可以直接在浏览器上导出
workbook.write(response.getOutputStream());
} catch (UnsupportedEncodingException e) {
AJAXUtil.failure(response, "导出失败!");
e.printStackTrace();
} catch (IOException e) {
AJAXUtil.failure(response, "导出失败!");
e.printStackTrace();
}
}
后端Workbook创建
public Workbook createResultExcel(List<DataResultVo> list) {
List<String[]> valueList = new ArrayList<String[]>();
valueList.add(new String[] {"批次号", "操作时间", "操作人" });
for (DataResultVo dataResultVo : list) {
valueList.add(new String[] {
dataResultVo.getCheckNo(),
dataResultVo.getCreateTime(),
dataResultVo.getCreateBy()
});
}
return writeXSSFExcel(valueList);
}
public static XSSFWorkbook writeXSSFExcel(List<String[]> values) {
//创建excel工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//创建工作表sheet
XSSFSheet sheet = workbook.createSheet();
//写入数据
for (int rowIndex = 0; rowIndex < values.size(); rowIndex++){
addRow(sheet, rowIndex, values.get(rowIndex));
}
return workbook;
}
public static void addRow(Sheet sheet, int rowIndex, Object[] rowValues) {
Row row = sheet.createRow(rowIndex);
for (int columnNum = 0; columnNum < rowValues.length; columnNum++) {
Cell cell = row.createCell(columnNum);
Object valueObject = rowValues[columnNum];
if (valueObject instanceof Integer) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(((Integer) valueObject).doubleValue());
} else if (valueObject instanceof Double) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue((Double) valueObject);
} else if (valueObject instanceof String) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue((String) valueObject);
} else if (valueObject instanceof Boolean) {
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
cell.setCellValue((Boolean) valueObject);
} else if (valueObject instanceof Date) {
cell.setCellType(Cell.CELL_TYPE_STRING);
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
cell.setCellValue(fmt.format((Date) valueObject));
}
}
}