Excel文件导出
jsp页面
?
//导Excel
?function makeExcel(){
???? var startDate=$('#startDate').val();
???? var endDate=$('#endDate').val();
????
???? if('' == startDate || null == startDate){
???????? alert('开始日期不能为空！');
???????? $('#startDate').focus();
???????? return false ;
???? }
????
???? if('' == endDate || null == endDate){
???????? alert('终止日期不能为空！');
???????? $('#endDate').focus();
???????? return false ;
???? }
??if(confirm("确认导出Excel吗?")){
???window.location.href="<%=basePath%>jsp/pmas/custReportAction!makeExcelYhsdgc.action?startDate="+startDate+"&endDate="+endDate;???
??}
?}
?
------------------------------------------------------
?
Java代码
public void makeExcelYhsdgc(){
??HttpServletResponse response = ServletActionContext.getResponse();
??Map<String, String> param = new HashMap<String, String>();
??param.put("startDate", startDate);
??param.put("endDate", endDate);
??
??List resList = new ArrayList();
??
??String filename = "10kV及以上用户受电工程信息明细表.xls";
??try {
???filename = URLEncoder.encode(filename, "UTF-8");
??} catch (UnsupportedEncodingException e1) {
???logger.error("不支持UTF-8编码", e1);
??}
??response.addHeader("Content-Disposition", "attachment;filename="+ filename);
??
??response.setContentType("application/msexcel;charset=UTF-8");// 定义输出类型
??resList = icustReportService.queryYhsdgc(param);
??ServletOutputStream sos = null;
??ByteArrayOutputStream out = null;
??try {
???sos = response.getOutputStream();
???Workbook wb = makeYhsdgcWorkbook(resList);
???out = new ByteArrayOutputStream();
???wb.write(out);
???byte[] buf = out.toByteArray();
???sos.write(buf);
???sos.flush();
??} catch (IOException e) {
???logger.error("Excel文件导出异常", e);
??} finally {
???try {
????if (sos != null) {
?????sos.close();
????}
????if (out != null) {
?????out.close();
????}
???} catch (IOException e) {
????logger.error("Excel文件导出异常", e);
???}
??}
?}
--------?? makeYhsdgcWorkbook(resList) 方法
private Workbook makeYhsdgcWorkbook(List<CustMonthInfo> list) throws IOException {
??Workbook wb = new HSSFWorkbook();
??
??Sheet sheet = wb.createSheet("10kV及以上用户受电工程信息明细表");
??CellStyle styleTitle = wb.createCellStyle();
??styleTitle.setBorderBottom(CellStyle.BORDER_THIN);
??styleTitle.setBorderTop(CellStyle.BORDER_THIN);
??styleTitle.setBorderLeft(CellStyle.BORDER_THIN);
??styleTitle.setBorderRight(CellStyle.BORDER_THIN);
??styleTitle.setAlignment(CellStyle.ALIGN_CENTER);
??styleTitle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
??Font fontTitle = wb.createFont();
??fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD);
??fontTitle.setFontHeightInPoints((short) 16);
??styleTitle.setFont(fontTitle);
??// Title
??Row row = sheet.createRow(0);
??Cell cell = row.createCell(0);
??cell.setCellValue("10kV及以上用户受电工程信息明细表");
??cell.setCellStyle(styleTitle);
??CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, 22);
??ExcelUtil.addMergedRegionStyle(wb, sheet, rangeAddress, styleTitle);
??// 表头
??CellStyle style = wb.createCellStyle();
??style.setBorderBottom(CellStyle.BORDER_THIN);
??style.setBorderTop(CellStyle.BORDER_THIN);
??style.setBorderLeft(CellStyle.BORDER_THIN);
??style.setBorderRight(CellStyle.BORDER_THIN);
??style.setAlignment(CellStyle.ALIGN_CENTER);
??style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
??Font font = wb.createFont();
??font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
??fontTitle.setFontHeightInPoints((short) 12);
??style.setFont(font);
??row = sheet.createRow(1);
??
??// 序号
??cell = row.createCell(0);
??cell.setCellValue("序号");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 0,0), style);
??sheet.setColumnWidth((short) 0, (short) 1800);
??// 申请编号
??cell = row.createCell(1);
??cell.setCellValue("申请编号");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 1,1), style);
??sheet.setColumnWidth((short) 1, (short) 4000);
??// 户名
??cell = row.createCell(2);
??cell.setCellValue("户名");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 2,2), style);
??sheet.setColumnWidth((short) 2, (short) 10000);
??// 地址
??cell = row.createCell(3);
??cell.setCellValue("地址");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 3,3), style);
??sheet.setColumnWidth((short) 3, (short) 10000);
??// 用电联系人
??cell = row.createCell(4);
??cell.setCellValue("用电联系人");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 4,4), style);
??sheet.setColumnWidth((short) 4, (short) 4000);
??// 联系电话
??cell = row.createCell(5);
??cell.setCellValue("联系电话");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 5,5), style);
??sheet.setColumnWidth((short) 5, (short) 7000);
??// 供电容量
??cell = row.createCell(6);
??cell.setCellValue("供电容量");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 6,6), style);
??sheet.setColumnWidth((short) 6, (short) 4000);
??// 供电电压
??cell = row.createCell(7);
??cell.setCellValue("供电电压");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 7,7), style);
??sheet.setColumnWidth((short) 7, (short) 4000);
??// 申请时间
??cell = row.createCell(8);
??cell.setCellValue("申请时间");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 1, 8,8), style);
??sheet.setColumnWidth((short) 7, (short) 5000);
??//接电时间
??cell = row.createCell(9);
??cell.setCellValue("接电时间");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 1,9, 9), style);
??sheet.setColumnWidth((short) 7, (short) 4000);
??// 设计单位
??cell = row.createCell(10);
??cell.setCellValue("设计单位");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 1,10, 10), style);
??sheet.setColumnWidth((short) 7, (short) 10000);
??// 施工单位
??cell = row.createCell(11);
??cell.setCellValue("施工单位");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 1,11, 11), style);
??sheet.setColumnWidth((short) 7, (short) 10000);
??// 高压设备生产厂家
??cell = row.createCell(12);
??cell.setCellValue("高压设备生产厂家");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 1,12, 12), style);
??sheet.setColumnWidth((short) 7, (short) 10000);
??// 有无供电方关联企业
??cell = row.createCell(13);
??cell.setCellValue("有无供电方关联企业");
??cell.setCellStyle(style);
??ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 1,13, 13), style);
??sheet.setColumnWidth((short) 7, (short) 3000);
??// 明细数据
??for (int i = 0; i < list.size(); i++) {
???CustMonthInfo custMonthInfo =(CustMonthInfo) list.get(i);
???row = sheet.createRow(i+3);
???// 序号
???cell = row.createCell(0);
???cell.setCellValue(""+(i+1));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 0, 0), style);
???
???// 申请编号
???cell = row.createCell(1);
???cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getAppid()));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 1, 1), style);
???// 户名
???cell = row.createCell(2);
???cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getCustname()));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 2, 2), style);
???// 地址
???cell = row.createCell(3);
???cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getAddress()));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 3, 3), style);
???// 用电联系人
???cell = row.createCell(4);
???cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getContactname()));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 4, 4), style);
???// 联系电话
???cell = row.createCell(5);
???cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getContactphone()));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 5, 5), style);
???// 供电容量
???cell = row.createCell(6);
???cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getCapa()));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 6, 6), style);
???// 供电电压
???cell = row.createCell(7);
???cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getVoltage()));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 7, 7), style);
???//申请时间
???cell = row.createCell(8);
???cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getApptime()));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 8, 8), style);
???//接电时间
???cell = row.createCell(9);
???cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getRealtime()));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 9, 9), style);
???//设计单位
???cell = row.createCell(10);
???cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getDesignname()));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 10, 10), style);
???//施工单位
???cell = row.createCell(11);
???cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getFidname()));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 11, 11), style);
???//高压设备生产厂家
???cell = row.createCell(12);
???cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getFactory()));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 12, 12), style);
???//有无供电方关联企业
???cell = row.createCell(13);
???cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getGlqy()));
???cell.setCellStyle(style);
???ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 13, 13), style);
??}
??return wb;
?}
