（1）JavaWeb学习总结之列表Excel下载
来源：互联网 发布：我做淘宝创业5年失败了 编辑：程序博客网 时间：2024/05/16 04:24
在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);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 + "]"; }} 3.3 将模板与数据结合并且导出excle：@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;"); }
阅读全文
1 0
- （1）JavaWeb学习总结之列表Excel下载
- javaweb学习总结（一）文件上传和下载(转载)
- JavaWeb学习总结（1）------JavaWeb开发入门
- JavaWeb学习总结（一）——servlet 之入门
- JavaWeb学习总结（一）
- Javaweb学习总结（一）
- Javaweb学习总结（二）
- Javaweb学习总结（三）
- Javaweb学习总结（四）
- Javaweb学习总结（五）
- Javaweb学习总结（六）
- Javaweb学习总结-（六）
- 学习总结之JavaWeb实现文件上传
- 学习总结之JavaWeb实现分页
- javaweb学习之xml（1）
- javaweb文件上传,下载,下载列表展示
- JavaWeb学习总结(五十)——文件上传和下载（转）
- JavaWeb学习总结(五十)——文件上传和下载
- SGISTL源码探究-STL中的算法(前言)
- bzoj1010 [HNOI2008]玩具装箱toy（斜率优化DP）
- 【运维分享】全方位管理你的小机
- Mybatis 异常： java.util.ArrayList and java.lang.String
- 配置yum源aliyun
- （1）JavaWeb学习总结之列表Excel下载
- jxl解析问题总结
- Quartz任务调度基本使用
- OpenAI Gym学习
- requests 库使用
- el部分
- leetcode 547. Friend Circles
- 我的Android进阶之旅------>android中一些特殊字符（如：←↑→↓等箭头符号）的Unicode码值
- 移动游戏的动态趋势
