JAVA实现EXCEL文件导出（项目实战）

html/jsp页面EXCEL导出按钮

<input type="submit" class="btn btn-query" id="expEventFile" value="导出EXCEL列表" onClick="expExcel();" style="float:none;margin-left:160px;"/>

js方法：

// 导出exl, 修改于2018 06 29	lenian
function expExcel(){
	var startTime=$("#startTime").val();
	var endTime=$("#endTime").val();
	var officeid = getUrlParam("officeid");
	if(officeid == null) {
		officeid = $("#deptid").val();
	} else {
		officeid = getUrlParam("officeid").replace("#", "")
	}
	
	// 重新选择处室后，导出的列表也应该随之改变
	var selDeptid = $("#selDeptid").val();
	// 为什么是""而不是null，原因：来自于input表单
	if(selDeptid != ""){
		officeid = selDeptid;	// 赋值,表示导出某处室的列表
	}
	
	document.location.href = "${ctx}/security/base/peoplesafeimg!expPortFolioExcel.action?startTime="+startTime+"&endTime="+endTime+"&deptid="+officeid;
}

PeoplesafeimgAction.java

/**
	 * @author lenian
	 * @date 2018 06 13
	 * （机务、运输、货运）系统工作量数据列表导出EXCEL
	 * @return
	 */
	public String expPortFolioExcel(){
		WritableWorkbook workbook = null;
		try {
			HttpServletResponse response = ServletActionContext.getResponse();
			response.reset();
			OutputStream os = response.getOutputStream();
			String fileName = "工作量.xls";
			ExcelUtils.setResponseHeader(response, fileName);
			String header = null;
			if (deptid == 102) {	// 运输
				header = "序号, 姓名, 身份证号, 时间, 日均工作分钟数, 日均工作量（列数）, 日均工作量（钩数）";
			} else {	// 机务、货运
				header = "序号, 姓名, 身份证号, 时间, 工作量"; // score分数
			}
			//String header = "序号, 姓名, 身份证号, 时间, 点内, 点外, 分数, 分钟数, 列数, 沟数";
			workbook = peopleSafeImgManager.expPortFolioExcel(os,startTime, endTime, header, deptid);
			if(UtilFuns.isNotEmpty(workbook)){
				workbook.write();
			}
			workbook.close();
			os.flush();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(workbook != null){
				try {
					workbook.close();
				} catch (Exception e2) {
					e2.printStackTrace();
				}
			}
		}
		return null;
	}

PeopleSafeImgManager.java

/**
 * @author lenian
 * @date 2018 06 13
 * （机务、运输、货运）系统工作量数据列表导出EXCEL
 * @return
 */
public WritableWorkbook expPortFolioExcel(OutputStream os,String startTime,String endTime,String header, Long deptid) {
	WritableWorkbook book = null;
	List<Object> list = expPortFolioByTime(startTime, endTime, deptid);
	try {
		WritableCellFormat wcf_title = new WritableCellFormat( new WritableFont(WritableFont.ARIAL, 16, WritableFont.NO_BOLD, false,
				UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK)); // 单元格定义
		book = jxl.Workbook.createWorkbook(os);
		//创建一个sheet
		WritableSheet sheet = book.createSheet("工作量", 0);
//			sheet.setColumnView(7, 2000);
		String[] headers = header.split(",");
		for (short i = 0; i < headers.length; i++) {
			sheet.addCell(new Label(i, 0, headers[i], wcf_title));
		}
		if (UtilFuns.isNotEmpty(list)) {
			for (int i = 0; i < list.size(); i++) {
				Object[] object = (Object[]) list.get(i);
				try {
					if (deptid == 102) {	// 运输
						sheet.addCell(new Label(0, i+1, String.valueOf(i+1), wcf_title));
						sheet.addCell(new Label(1, i+1, null==object[0]?"":object[0].toString(), wcf_title));
						sheet.addCell(new Label(2, i+1, null==object[1]?"":object[1].toString(), wcf_title));
						sheet.addCell(new Label(3, i+1, null==object[2]?"":object[2].toString(), wcf_title));
						sheet.addCell(new Label(4, i+1, null==object[3]?"":object[3].toString(), wcf_title));
						sheet.addCell(new Label(5, i+1, null==object[4]?"":object[4].toString(), wcf_title));
						sheet.addCell(new Label(6, i+1, null==object[5]?"":object[5].toString(), wcf_title));
//							sheet.addCell(new Label(7, i+1, null==object[6]?"":object[6].toString(), wcf_title));
//							sheet.addCell(new Label(8, i+1, null==object[7]?"":object[7].toString(), wcf_title));
//							sheet.addCell(new Label(9, i+1, null==object[8]?"":object[8].toString(), wcf_title));
					} else {	// 机务、货运
						sheet.addCell(new Label(0, i+1, String.valueOf(i+1), wcf_title));
						sheet.addCell(new Label(1, i+1, null==object[0]?"":object[0].toString(), wcf_title));
						sheet.addCell(new Label(2, i+1, null==object[1]?"":object[1].toString(), wcf_title));
						sheet.addCell(new Label(3, i+1, null==object[2]?"":object[2].toString(), wcf_title));
						sheet.addCell(new Label(4, i+1, null==object[3]?"":object[3].toString(), wcf_title));
					}
				} catch (Exception e) {
					System.out.println(i);
				}
			}
		}
	}catch (IOException e) {
		System.out.println("EXCEL创建失败！");
		e.printStackTrace();
		
	}catch (RowsExceededException e) {
		System.out.println("EXCEL单元设置创建失败！");
		e.printStackTrace();
	} catch (WriteException e) {
		System.out.println("EXCEL写入失败！");
		e.printStackTrace();
	} 
	return book;
}