使用java导出Excel.docx

package com.ireport;import java.io.IOException;import java.io.OutputStream;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.LinkedList;import java.util.List;import javax.faces.context.FacesContext;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import mons.lang.StringUtils;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.CellRangeAddress;import org.apache.poi.hssf.util.HSSFColor;import com.bean.ReportBean;/* * 创建报表和样式 * author Administrator * */public class ReportActionHandler private static ReportActionHandler instance = new ReportActionHandler();private HSSFWorkbook workbook;private HSSFCellStyle styleEven; /偶数行样式private HSSFCellStyle styleOdd; /奇数行样式private HSSFCellStyle styleTitle;/标题样式private HSSFCellStyle styleColTitle;/列标题样式private ReportActionHandler()public static ReportActionHandler getInstance()return instance;/* * 生成报表 * param list */public void buildExcelReport(List list) workbook = new HSSFWorkbook();/ 创建一个Excel文件 ReportActionHandler.getInstance().initCellStyle(); workbook.createSheet();/ 创建一个Excel的Sheet String reportTitlt = null;/Excel文档标题 List colTitle = null;/Excel第一列标题 buildExcelReportTemplate(reportTitlt,colTitle); List data = list;/Excel封装的数据 buildExcelReportData(data); String footRowMessage = null;/Excel最后一列的内容 buildExcelReportFootRow(footRowMessage); setSheetHeight(); String fileName = null;/网络文件名称 sendExcelReport(fileName);/* * 构建excel报表模板 * param workbook * param sheet * param reportTitle 报表标题 * param colTitle 列标题 */private void buildExcelReportTemplate(String reportTitle,List colTitle)try HSSFSheet sheet = workbook.getSheetAt(0);/ 创建第一行 HSSFRow row0 = sheet.createRow(0); / 设置行高 row0.setHeight(short) 900); / 创建第一列 (标题) HSSFCell cell0 = row0.createCell(0); if(StringUtils.isEmpty(reportTitle) reportTitle = 2013年古井软件工作人员情况调查表; cell0.setCellValue(new HSSFRichTextString(reportTitle+new Date().toLocaleString(); cell0.setCellStyle(styleTitle); /* * 合并单元格 * 第一个参数 x：第一个单元格的行数（从0开始） * 第二个参数 y：第二个单元格的行数（从0开始） * 第三个参数 m：第一个单元格的列数（从0开始） * 第四个参数 n：第二个单元格的列数（从0开始） * 就是 把x-y的行以及m-n的列和并成一个单元格 */ CellRangeAddress range = new CellRangeAddress(0, 0, 0, 8); sheet.addMergedRegion(range); /如下设计excel表的模板 第二行 列头 HSSFRow tempRow = sheet.createRow(1); tempRow.setHeight(short) 750); List cTitle = colTitle; if(null=colTitle|colTitle.isEmpty() List tempColTitle = new ArrayList(); tempColTitle.add(用户名); tempColTitle.add(发表内容); tempColTitle.add(开始时间); tempColTitle.add(结束时间); tempColTitle.add(进度); tempColTitle.add(创建人); tempColTitle.add(修改人); tempColTitle.add(创建时间); tempColTitle.add(修改时间); cTitle = tempColTitle; for(int index=0;indexcTitle.size();index+) HSSFCell tempCell = tempRow.createCell(index); tempCell.setCellValue(cTitle.get(index); tempCell.setCellStyle(styleColTitle); catch(Exception ex) ex.printStackTrace(); /* * 把数据封装到报表中 * param workbook * param sheet * param data 将被封装的数据 */private void buildExcelReportData(List data)HSSFSheet sheet = workbook.getSheetAt(0);int rowNumb = sheet.getLastRowNum()+1;/确定报表的行位置 DateFormat dateFormat = new SimpleDateFormat(yyyy-MM-dd);/处理时间格式List beanCell = new LinkedList();/封装单个reportBean的信息for(int index =0;indexdata.size();index+) beanCell.clear(); ReportBean reportBean = data.get(index); beanCell.add(reportBean.getUserName(); beanCell.add(reportBean.getContent(); String tempBeginTime = dateFormat.format(reportBean.getBeginTime(); beanCell.add(tempBeginTime); String tempEndTime = dateFormat.format(reportBean.getEndTime(); beanCell.add(tempEndTime); beanCell.add(reportBean.getProgress(); beanCell.add(reportBean.getCreateBy(); beanCell.add(reportBean.getUpdateBy(); String tempCreateDate = dateFormat.format(reportBean.getCreateDate(); beanCell.add(tempCreateDate); String tempUpdateDate = dateFormat.format(reportBean.getUpdateDate(); beanCell.add(tempUpdateDate); HSSFRow tempRow = sheet.createRow(rowNumb); tempRow.setHeight(short)500); if(rowNumb % 2 = 0) wrapValueAndStyle(beanCell, tempRow ,styleEven); else wrapValueAndStyle(beanCell, tempRow,styleOdd); rowNumb+; /* * 封装数据和样式 */private void wrapValueAndStyle(List beanCell, HSSFRow tempRow,HSSFCellStyle style) for(int i=0;i=8;i+) HSSFCell tempCell = tempRow.createCell(i); tempCell.setCellValue(beanCell.get(i); tempCell.setCellStyle(style); /* * 设置最后一列的数据 * param workbook * param footRowMessage 最后一列的内容 */private void buildExcelReportFootRow(String footRowMessage) HSSFSheet sheet = workbook.getSheetAt(0); HSSFRow footRow = sheet.createRow(sheet.getLastRowNum()+1); HSSFCell footRowcell = footRow.createCell(0); if(StringUtils.isEmpty(footRowMessage) footRowMessage = 审 定：XXX 审 核：XXX 汇 总：XX; footRowcell.setCellValue(new HSSFRichTextString(footRowMessage); /* * 发送报表到客户端 * param workbook */ private void sendExcelReport(String filename) HttpServletResponse response =(HttpServletResponse)FacesContext.getCurrentInstance().getExternalContext().getResponse(); HttpServletRequest request = (HttpServletRequest)FacesContext.getCurrentInstance().getExternalContext().getRequest(); if(StringUtils.isEmpty(filename) filename = 成员数据表+new Date().toLocaleString()+.xls;/设置下载时客户端Excel的名称 /这里使用到一个辅助类 ReportActionUtil filename = ReportActionUtil.encodeFilename(filename, request); response.setContentType(application/vnd.ms-excel); response.setHeader(Content-disposition, attachment;filename= + filename); try OutputStream ouputStream = response.getOutputStream(); workbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); catch (IOException ex) ex.printStackTrace(); /* * 报表样式设置 * param workbook */ private void initCellStyle() short fontSize = 10;HSSFFont font = workbook.createFont();font.setFontName(HSSFFont.FONT_ARIAL);/font.setFontHeightInPoints(fontSize);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);this.styleEven = workbook.createCellStyle();styleEven.setFont(font);/内容居中显示styleEven.setAlignment(HSSFCellStyle.ALIGN_CENTER);/末尾边款粗细设置styleEven.setBorderBottom(HSSFCellStyle.BORDER_THIN);/末尾边款颜色设置styleEven.setBottomBorderColor(HSSFColor.BLACK.index);/左边框粗细设置styleEven.setBorderLeft(HSSFCellStyle.BORDER_THIN);/左边框颜色设置styleEven.setLeftBorderColor(HSSFColor.BLACK.index);/右边框粗细设置styleEven.setBorderRight(HSSFCellStyle.BORDER_THIN);/右边框颜色设置styleEven.setRightBorderColor(HSSFColor.RED.index);/顶端边框粗细设置styleEven.setBorderTop(HSSFCellStyle.BORDER_THIN);/顶端边框颜色设置styleEven.setTopBorderColor(HSSFColor.BLACK.index);/是否封装整个表格,如果一行放不下自动换行使所有数据显示styleEven.setWrapText(true);this.styleOdd = workbook.createCellStyle();/克隆已经设置好的样式，不过保证出自同一个HSSFWorkbook对象创建styleOdd.cloneStyleFrom(styleEven);/设置固体前景色styleOdd.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);/设置固体前景色的比重styleOdd.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);this.styleTitle