【JAVA】Excel数据导出的完整处理

常见的数据处理除了增删改查，还有导入导出。
今天记录一次完整的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));
    	}
    }
}