在Web应用系统开发中,列表Excel下载功能是非常常用的功能,今天来总结一下JavaWeb中的列表Excel下载功能的实现:
1.实现excel下载的方式:
现在主流的操作Excel文件的开源工具有很多,用得比较多的就是Apache的POI及JExcelAPI。这里我们用Apache POI!需要的资源Apache的大本营下载POI的jar包:http://poi.apache.org/
2.使用poi需要使用的包:
poi-3.15.jar,poi-excelant-3.14.jar,poi-ooxml-3.14.jar,poi-ooxml-schemas-3.14.jar,poi-scratchpad-3.14.jar 。 版本大家可以根据自己的情况决定。
3.下载excel的步骤:
首先,配置excel信息模板;接着,读取模板信息;紧后,查询需要的数据;其次,将模板与数据结合;最后,导出excle。
3.1 配置excel信息模板:
<?xml version="1.0" encoding="UTF-8"?>
<excel>
<excelFileName>LicenseContractList</excelFileName>
<url>excelTest</url>
<excelDownload>
<sheetName>LicenseContractList</sheetName>
<title>License Contract List</title>
<queryId>Employee.getLists</queryId>
<parameters><![CDATA[lstPjt[],lstPjtCnt,employeeCode,name,email,department,registrant,maleChecked,femaleChecked,readcount]]></parameters>
<useI18nMessage>false</useI18nMessage>
<columnList>
<column>
<colId>EMPLOYEECODE</colId>
<colName>employeeCode</colName>
<width>120</width>
<colAlign>C</colAlign>
</column>
<column>
<colId>NAME</colId>
<colName>name</colName>
<width>300</width>
<colAlign>L</colAlign>
</column>
</columnList>
</excelDownload>
</excel>
3.2 读取模板信息和查询需要的数据:
final SAXParserFactory factory = SAXParserFactory.newInstance(); final SAXParser saxParser = factory.newSAXParser(); String urlFile = null; urlFile = request.getSession().getServletContext().getContextPath(); String excelConfigurationFile = (String) paramMap.get("template"); final File file = new File("C:/upgradeopen/anyframe-ui-spring-sample/src/main/resources/excel/"+excelConfigurationFile+".xml"); final ExcelInfoHandler handler = new ExcelInfoHandler(); System.out.println("cqtest"); if (file.canRead()) { saxParser.parse(file, handler); } final ExcelSheetVO excelSheetVo = handler.getSheetInfo(); for(final ExcelDownloadVO excelMeta : excelSheetVo.getSheetList() ) { if(excelMeta == null){ logger.error("Excel mapping not found."); result.put("success", "success"); } Object object = new Object(); Map map = new HashMap(); map.put("employeeCode", ""); resultList.add( (ArrayList<Map>) dao.selectListSqlById(excelMeta.getQueryId(), map) ); } OutputStream fileOut = null; final XSSFWorkbook workbook = getWorkBook(excelSheetVo.getSheetList(), resultList); fileOut = response.getOutputStream(); setHttpResponse(excelSheetVo , request, response); workbook.write(fileOut);
3.3 将模板与数据结合并且导出excle:import java.util.ArrayList; import java.util.List; import org.xml.sax.Attributes; import org.xml.sax.SAXException; import org.xml.sax.helpers.DefaultHandler; import com.anyframe.ui.sample.excel.vo.ExcelDownloadColumnVO; import com.anyframe.ui.sample.excel.vo.ExcelDownloadVO; import com.anyframe.ui.sample.excel.vo.ExcelSheetVO; public class ExcelInfoHandler extends DefaultHandler { private boolean url; private boolean queryId; private boolean sheetName; private boolean title; private boolean downloadFileName; private boolean parameters; private boolean excelFileName; private boolean useI18nMessage; private boolean colId; private boolean colName; private boolean width; private boolean colAlign; private ExcelDownloadVO excel; private ExcelDownloadColumnVO col; private List<ExcelDownloadVO> sheetList; private ExcelSheetVO sheetInfo; @Override public void startElement(final String uri, final String localName, final String qName, final Attributes attributes) throws SAXException { if ("excel".equalsIgnoreCase(qName)) { this.sheetList = new ArrayList<ExcelDownloadVO>(); this.sheetInfo = new ExcelSheetVO(); } else if ("excelDownload".equalsIgnoreCase(qName)) { this.excel = new ExcelDownloadVO(); sheetList.add(excel); sheetInfo.setSheetList(sheetList); } else if ("columnList".equalsIgnoreCase(qName)) { excel.setColumns(new ArrayList<ExcelDownloadColumnVO>()); } else if ("column".equalsIgnoreCase(qName)) { col = new ExcelDownloadColumnVO(); } else if ("url".equalsIgnoreCase(qName)) { url = true; } else if ("queryId".equalsIgnoreCase(qName)) { queryId = true; } else if ("downloadFileName".equalsIgnoreCase(qName)) { downloadFileName = true; } else if ("sheetName".equalsIgnoreCase(qName)) { sheetName = true; } else if ("title".equalsIgnoreCase(qName)) { title = true; } else if ("parameters".equalsIgnoreCase(qName)) { parameters = true; } else if ("useI18nMessage".equalsIgnoreCase(qName)) { useI18nMessage = true; } else if ("colId".equalsIgnoreCase(qName)) { colId = true; } else if ("colName".equalsIgnoreCase(qName)) { colName = true; } else if ("width".equalsIgnoreCase(qName)) { width = true; } else if ("excelFileName".equalsIgnoreCase(qName)) { excelFileName = true; } else if ("colAlign".equalsIgnoreCase(qName)) { colAlign = true; } } @Override public void characters(final char ch[], final int start, final int length) throws SAXException { if (url) { //excel.setUrl(new String(ch, start, length)); sheetInfo.setUrl(new String(ch, start, length)); url = false; } else if (queryId) { excel.setQueryId(new String(ch, start, length)); queryId = false; } else if (downloadFileName) { excel.setDownloadFileName(new String(ch, start, length)); downloadFileName = false; } else if (sheetName) { excel.setSheetName(new String(ch, start, length)); sheetName = false; } else if (title) { excel.setTitle(new String(ch, start, length)); title = false; } else if (parameters) { excel.setParameters(new String(ch, start, length)); parameters = false; } else if (useI18nMessage) { excel.setUseI18nMessage(isTrue(new String(ch, start, length))); useI18nMessage = false; } else if (excelFileName) { sheetInfo.setExcelFileName(new String(ch, start, length)); excelFileName = false; } else if (colId) { col.setColId(new String(ch, start, length)); colId = false; } else if (colName) { col.setColName(new String(ch, start, length)); colName = false; } else if (width) { try { col.setWidth(Integer.parseInt(new String(ch, start, length))); } catch (final Exception e) { col.setWidth(200); } width = false; } else if (colAlign) { try { col.setColAlign(new String(ch, start, length)); } catch (final Exception e) { col.setColAlign("L"); } colAlign = false; } } private boolean isTrue(final String str) { return "true".equalsIgnoreCase(str); } @Override public void endElement(final String uri, final String localName, final String qName) throws SAXException { if ("column".equalsIgnoreCase(qName)) { if(col == null){ return; }else{ this.excel.getColumns().add(col); } } } public boolean isColAlign() { return colAlign; } public void setColAlign(final boolean colAlign) { this.colAlign = colAlign; } /* (non-Javadoc) * @see java.lang.Object#toString() */ @Override public String toString() { return "ExcelInfoHandler [url=" + url + ", queryId=" + queryId + ", sheetName=" + sheetName + ", title=" + title + ", downloadFileName=" + downloadFileName + ", parameters=" + parameters + ", excelFileName=" + excelFileName + ", useI18nMessage=" + useI18nMessage + ", colId=" + colId + ", colName=" + colName + ", width=" + width + ", colAlign=" + colAlign + ", excel=" + excel + ", col=" + col + ", sheetList=" + sheetList + ", sheetInfo=" + sheetInfo + "]"; } }
@SuppressWarnings({"rawtypes", "unchecked"}) private XSSFWorkbook getWorkBook(final List<ExcelDownloadVO> excelList, final List<ArrayList> dataList){ final XSSFWorkbook workbook = new XSSFWorkbook(); int s = 0 ; int start_cell; int row_index; start_cell = 0; row_index = 1; // 시트별로 작성 for(final ExcelDownloadVO excelMeta : excelList) { final XSSFSheet sheet = workbook.createSheet(excelMeta.getSheetName()); XSSFRow row; XSSFCell cell; row = sheet.createRow(0); final List<ExcelDownloadColumnVO> columnInfoList = excelMeta.getColumns(); final int columnCount = columnInfoList.size(); final String[] header = new String[columnCount]; final String[] fieldName = new String[columnCount]; final String[] fieldAlign = new String[columnCount]; /*************************************************/ // title 출력 /*************************************************/ sheet.addMergedRegion(new CellRangeAddress(0, 0, start_cell, (start_cell + columnCount-1)) ); for (int i = start_cell; i < columnCount; i++) { cell = row.createCell(start_cell + i); cell.setCellStyle(getStyleTitle(workbook)); } cell = row.createCell(start_cell); cell.setCellValue(excelMeta.getTitle()); cell.setCellStyle(getStyleTitle(workbook)); /**************************************************/ /*************************************************/ // 엑셀생성 날짜 출력 /*************************************************/ /*row = sheet.createRow(row_index-1); cell = row.createCell(start_cell ); cell.setCellValue(new HSSFRichTextString("생성일시 : " + getCreateExcelDate()));*/ //cell.setCellStyle(style_data); /**************************************************/ ExcelDownloadColumnVO columnInfo = null; row = sheet.createRow(row_index); //header setting for (int i = 0; i < columnCount; i++) { columnInfo = columnInfoList.get(i); header[i] = columnInfo.getColName(); fieldName[i] = columnInfo.getColId(); fieldAlign[i] = columnInfo.getColAlign(); cell = row.createCell(start_cell + i); cell.setCellValue(new XSSFRichTextString(header[i])); cell.setCellStyle(getStyleHeader(workbook)); sheet.setColumnWidth(start_cell + i, columnInfo.getWidth() * 40); } final List<Map> resultList = dataList.get(s); // final XSSFCellStyle style_data = getStyleData(workbook); for (int i = 0; i < resultList.size(); i++) { row = sheet.createRow(row_index + i + 1); final Map resultMap = resultList.get(i); for (int j = 0; j < columnCount; j++) { XSSFCellStyle style_data = getStyleData(workbook); cell = row.createCell(start_cell + j); cell.setCellValue(resultMap.get(fieldName[j]) == null ? "" : String.valueOf( resultMap.get(fieldName[j]) )); //style_data.setAlignment(CellStyle.ALIGN_LEFT); if("L".equals(fieldAlign[j])) { style_data.setAlignment(XSSFCellStyle.ALIGN_LEFT); } else if("C".equals(fieldAlign[j])) { style_data.setAlignment(XSSFCellStyle.ALIGN_CENTER); } else if("R".equals(fieldAlign[j])) { style_data.setAlignment(XSSFCellStyle.ALIGN_RIGHT); } else { style_data.setAlignment(XSSFCellStyle.ALIGN_LEFT); } cell.setCellStyle(style_data); } } s++ ; } return workbook; } private XSSFCellStyle getStyleTitle(final XSSFWorkbook workbook){ final XSSFCellStyle style_title = (XSSFCellStyle) workbook.createCellStyle(); // create fonts objects final XSSFFont font = (XSSFFont) workbook.createFont(); font.setFontHeightInPoints((short) 15); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style_title.setAlignment(XSSFCellStyle.ALIGN_CENTER); style_title.setBorderTop(XSSFCellStyle.BORDER_THIN); style_title.setBorderLeft(XSSFCellStyle.BORDER_THIN); style_title.setBorderRight(XSSFCellStyle.BORDER_THIN); style_title.setBorderBottom(XSSFCellStyle.BORDER_THIN); style_title.setFillForegroundColor(HSSFColor.WHITE.index); style_title.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style_title.setAlignment(XSSFCellStyle.ALIGN_CENTER); style_title.setFont(font); return style_title; } private XSSFCellStyle getStyleHeader(final XSSFWorkbook workbook){ final XSSFCellStyle style_header = (XSSFCellStyle) workbook.createCellStyle(); // create fonts objects final XSSFFont font = (XSSFFont) workbook.createFont(); // Set font 1 to 12 point type, blue and bold font.setFontHeightInPoints((short) 11); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style_header.setAlignment(XSSFCellStyle.ALIGN_CENTER); style_header.setBorderTop(XSSFCellStyle.BORDER_THIN); style_header.setBorderLeft(XSSFCellStyle.BORDER_THIN); style_header.setBorderRight(XSSFCellStyle.BORDER_THIN); style_header.setBorderBottom(XSSFCellStyle.BORDER_THIN); style_header.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); //style_header.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); style_header.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style_header.setFont(font); return style_header; } private XSSFCellStyle getStyleData(final XSSFWorkbook workbook){ //data style final XSSFCellStyle style_data = (XSSFCellStyle) workbook.createCellStyle(); style_data.setAlignment(XSSFCellStyle.ALIGN_CENTER); style_data.setBorderLeft(XSSFCellStyle.BORDER_THIN); style_data.setBorderRight(XSSFCellStyle.BORDER_THIN); style_data.setBorderBottom(XSSFCellStyle.BORDER_THIN); return style_data; } private void setHttpResponse(final ExcelSheetVO excelSheetVo , final HttpServletRequest request, final HttpServletResponse response) throws IOException{ String fileName = excelSheetVo.getExcelFileName(); final Calendar cal = Calendar.getInstance(); final SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss",Locale.getDefault()); if (StringUtil.isEmptyTrimmed(fileName)) { fileName = formatter.format(cal.getTime()) +".xlsx"; } else { fileName = fileName + "_" + formatter.format(cal.getTime()) + ".xlsx"; } // determine MIME type String mimeType = URLConnection.guessContentTypeFromName(fileName); if (mimeType == null) { mimeType = "application/octet-stream"; } if (logger.isDebugEnabled()) { logger.debug("mimetype: " + mimeType); } // set content type (MIME) response.setContentType(mimeType); // set file info to response. final String userAgent = request.getHeader("User-Agent"); final boolean isIe = userAgent.indexOf("Trident") > 0 || userAgent.indexOf("MSIE") > 0; if (isIe) { fileName = URLEncoder.encode(fileName,"UTF-8").replaceAll("\\+", "%20"); } else { fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1"); } //attachment or inline response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\";"); response.setHeader("Content-Transfer-Encoding", "binary"); response.setHeader("Content-Description", "JSP Generated Data"); response.setHeader("Pragma", "no-cache;"); response.setHeader("Expires", "-1;"); }