使用Java导出Excel案例详解

package com.wy.common.util;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

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.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;

//这是一个导出Excel的工具类
public class ExportExcelUtils{
/**
* 这是一个通用的方法,将一个map集合作为表格内容输入到excel中
*
* @param title
* 表格标题名
* @param headers
* 表格属性列名数组
* @param rows
* 需要显示的数据集合,row为List

@SuppressWarnings("deprecation")public void exportExcel(String title, String[] headers,List<Map> rows, String[] rowkeys, OutputStream out) {    // 声明一个工作薄    HSSFWorkbook workbook = new HSSFWorkbook();    // 生成一个表格    HSSFSheet sheet = workbook.createSheet(title);    // 设置表格默认列宽度为15个字节    sheet.setDefaultColumnWidth((short) 25);    if(rows!=null && rows.size()>0 ){        //判断传入集合数据，如为空即不填充数据导出空Excel        /** ----------- 生成标题     ------------*/        //第一行写入标题        HSSFFont fontTitle = workbook.createFont();        HSSFCellStyle titleStyle = workbook.createCellStyle();        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        fontTitle.setFontHeightInPoints((short) 16);        titleStyle.setFont(fontTitle);        if(headers!=null){            //这里作非空验证            sheet.addMergedRegion(new Region(0,(short)0,0,(short)(headers.length-1)));        }        HSSFRow rowTitle = sheet.createRow(0);        rowTitle.setHeight((short)500);        HSSFCell cellTitle = rowTitle.createCell(0);        cellTitle.setCellStyle(titleStyle);        cellTitle.setCellValue(title);        /** ----------- 生成表头     ------------*/        HSSFFont fontHeader = workbook.createFont();        fontHeader.setBoldweight(Font.BOLDWEIGHT_BOLD);        HSSFCellStyle cstyleHeader =workbook.createCellStyle();        cstyleHeader.setFont(fontHeader);        cstyleHeader.setAlignment(CellStyle.ALIGN_CENTER);        cstyleHeader.setBorderBottom(CellStyle.BORDER_THIN);        cstyleHeader.setBorderLeft(CellStyle.BORDER_THIN);        cstyleHeader.setBorderRight(CellStyle.BORDER_THIN);        cstyleHeader.setBorderTop(CellStyle.BORDER_THIN);        HSSFRow row = sheet.createRow(1);        for (short i = 0; i < headers.length; i++) {            HSSFCell cell = row.createCell(i);            //cell.setCellStyle(style);            HSSFRichTextString text = new HSSFRichTextString(headers[i]);            cell.setCellStyle(cstyleHeader);            cell.setCellValue(text);        }        /** ----------- 生成数据行     ------------*/        int index = 2;        HSSFFont fontData = workbook.createFont();        fontData.setBoldweight(Font.BOLDWEIGHT_NORMAL);        HSSFCellStyle cstyleData =workbook.createCellStyle();        cstyleData.setBorderBottom(CellStyle.BORDER_THIN);        cstyleData.setBorderLeft(CellStyle.BORDER_THIN);        cstyleData.setBorderRight(CellStyle.BORDER_THIN);        cstyleData.setBorderTop(CellStyle.BORDER_THIN);        cstyleData.setFont(fontData);        cstyleData.setAlignment(CellStyle.ALIGN_CENTER);        /*      HSSFCellStyle cstyleData1 =workbook.createCellStyle();        cstyleData1.setBorderBottom(HSSFCellStyle.BORDER_THIN);        cstyleData1.setBorderLeft(HSSFCellStyle.BORDER_THIN);        cstyleData1.setBorderRight(HSSFCellStyle.BORDER_THIN);        cstyleData1.setBorderTop(HSSFCellStyle.BORDER_THIN);        cstyleData1.setFont(fontData);        cstyleData1.setAlignment(HSSFCellStyle.ALIGN_LEFT);*/        for(Map row1 : rows){            row = sheet.createRow(index);            //获取map的所有需要导入到excel中数据key值            for(int i = 0 ; i < rowkeys.length ; i++){                HSSFCell cell = row.createCell(i);                cell.setCellStyle(cstyleData);                String key = rowkeys[i];                String cellValue="";                if(row1.get(key)!=null){                    cellValue =row1.get(key).toString();                }                else{                    cellValue="";                }                cell.setCellValue(cellValue);            }            index++;        }        /** ----------- 生成说明行     ------------*/        HSSFFont fontDesc = workbook.createFont();        HSSFCellStyle descStyle =workbook.createCellStyle();        descStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);        descStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);        descStyle.setAlignment(CellStyle.ALIGN_LEFT);        descStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);        fontDesc.setFontHeightInPoints((short) 9);        descStyle.setFont(fontDesc);        //最后行写入描述        /*        sheet.addMergedRegion(new Region(index,(short)0,index,(short)(headers.length-1)));        HSSFRow rowDesc = sheet.createRow(index);        rowDesc.setHeight((short)1500);        HSSFCell cellDesc = rowDesc.createCell(0);        cellDesc.setCellStyle(descStyle);        descStyle.setWrapText(true);        cellDesc.setCellValue(new HSSFRichTextString(conditionsDescrip));         */    }    try {        workbook.write(out);    } catch (IOException e) {        e.printStackTrace();    }}

}

/*Action层中的方法*/public String exportQuesByTypeAndId(){        try {            ExportExcelUtils<T> ex = new ExportExcelUtils<T>();            ByteArrayOutputStream bos = new ByteArrayOutputStream();            // 获取查询数据            List<Map> rows = null;            String questionnaireId = ServletActionContext.getRequest().getParameter("questionnaireId");            TbInteractionQuestionnaire tbQuestionnaire=(TbInteractionQuestionnaire) questionnaireService.getDataBaseDao().load(TbInteractionQuestionnaire.class, questionnaireId);            String type=ServletActionContext.getRequest().getParameter("type");            rows = questionnaireService.exportQuesByTypeAndId(questionnaireId,type);            int i=0;            String name=tbQuestionnaire.getQuestionnaireTitle()+"_参与人员_"+DataConvertUtil.getCurrentDate();            if(rows!=null && rows.size()>0 && rows.get(0)!=null){            //headers 表示excel首行标题                String[] headers =new String[rows.get(0).size()];            //rowkeys 表示excel填充数据行对应的key                String[] rowkeys =new String[rows.get(0).size()];                System.out.println(rows.size());                Iterator ita = rows.get(0).entrySet().iterator();                 while(ita.hasNext()){                    /*这里面我将数据库查询出的数据字段名称                    替换成对应中文作为Excel每列标题，将字段                    名称直接作为数据行的key，分别将标题和                    key存放在数组中，这里大家可以自定义                    Excel的每列标题，key可以直接取库中                    字段名称。                    */                    Entry entry = (Entry)ita.next();                    headers[i]=entry.getKey().toString().replaceAll("userAccount","工号/学号").replaceAll("orgName","部门").replaceAll("persontype", "类别").replaceAll("voteFlag","参与状态");                    rowkeys[i]=entry.getKey().toString();                    i++;                }                ex.exportExcel(name, headers, rows,                 rowkeys,bos);            }            else{                /*                这里当数据集为空则传入null,                输出流对象必输传                */                ex.exportExcelNoAnswerForQues(name                                 ,null, null,null,bos);            }            byte[] fileBytes = bos.toByteArray();            ByteArrayInputStream bis = new ByteArrayInputStream(fileBytes);            bos.close();            inputStream = bis;            //这里fileName指定为文件的名称（全局变量需要给出相应的getter,setter方法）            fileName = new String((name + ".xls").getBytes(),"ISO-8859-1");        } catch (FileNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } catch (IOException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } catch (Exception e) {            rspCode = RtnCodeConstant.RTN_CODE_UNKNOW_ERROR;            e.printStackTrace();        }        return SUCCESS;    }

//这里是struts的配置

<action name="exportQuesByTypeAndId"   class="queryQuertionnaireAction"         method="exportQuesByTypeAndId">    <result type="stream">        <param name="noCache">true</param>        <param name="contentType">application/octet-stream</param>        <param name="inputName">inputStream</param>        <param name="contentDisposition">attachment;filename="${fileName}"</param>        <param name="bufferSize">false</param>    </result>    <result      name="noSession">/jsps/login/no_session.jsp    </result></action>

//下图为数据库 中查询的数据

//注：导出excel需通过location.href 的方式直接请求后台方法，或者使用表单提交方式