POI Excel导出，我的又一个模板方法

下面有源码，下载就可以用，如果因为包的问题，就拷贝我的包就行了

首先是模板工具类的写法：

package com.util;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.MessageFormat;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
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.HSSFPrintSetup;
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;

/**
 * 导出Excel的工具类，只需给相应参数即可
 *
 * @author Administrator
 *
 */
public class ExcelUtil {
    /**
     * 导出Excel的方法
     *
     * @param request
     *            Web应用中的request对象
     * @param excelContent
     *            数据体集合，集合内放置String数组
     * @param columnNames
     *            数据列的头，使用数组
     * @param titleName
     *            要导出的文件名称
     * @param fOut
     *            导出流，Web中使用response得到
     * @throws IOException
     *             可能会爆出的异常
     */
    @SuppressWarnings("deprecation")
    public static void create(HttpServletRequest request, List<?> excelContent,
            String[] columnNames, String titleName, OutputStream fOut)
            throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();// 建立新HSSFWorkbook对象
        HSSFSheet sheet = wb.createSheet("sheet1");// 建立新的sheet对象
        // ================合并标题列=========================
        Region region = new Region();
        region.setColumnFrom((short) 0);
        region.setColumnTo((short) (columnNames.length - 1));
        region.setRowFrom((short) 0);
        region.setRowTo((short) 0);
        sheet.addMergedRegion(region);
        // ===============================================
        // 标题列列的字体样式
        HSSFFont titleFont = wb.createFont();
        titleFont.setColor(HSSFFont.COLOR_RED);
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        titleFont.setFontHeight((short) 300);

        // 选择列的字体样式
        HSSFFont headerFont = wb.createFont();
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerFont.setColor(HSSFFont.BOLDWEIGHT_NORMAL);
        HSSFPrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        sheet.createFreezePane(0, 1);
        sheet.setAutobreaks(true);
        sheet.setDefaultColumnWidth((short) 13.5);
        printSetup.setFitHeight((short) 100);
        printSetup.setFitWidth((short) 180);
        // 标题列样式
        HSSFCellStyle titlestyle = wb.createCellStyle();
        titlestyle.setFont(titleFont);
        titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titlestyle.setFillForegroundColor(HSSFColor.WHITE.index);
        titlestyle.setFillPattern(HSSFCellStyle.SQUARES);
        titlestyle.setLeftBorderColor(HSSFColor.BLACK.index);
        titlestyle.setRightBorderColor(HSSFColor.BLACK.index);
        titlestyle.setTopBorderColor(HSSFColor.BLACK.index);
        titlestyle.setBottomBorderColor(HSSFColor.BLACK.index);
        // titlestyle.setFillPattern((short)300);

        titlestyle.setWrapText(true);
        // 选择列样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(headerFont);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setHidden(true);
        // 内容列的样式
        HSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setDataFormat(wb.createDataFormat().getFormat("0.00"));
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 写入标题
        HSSFRow titleRow = null;

        if (!titleName.equals("")) {
            titleRow = sheet.createRow((short) 0);
            titleRow.setHeightInPoints(30.120f);
            HSSFCell titlecell = titleRow.createCell((short) 0);// 标题
            titlecell.setEncoding(HSSFCell.ENCODING_UTF_16);//
            titlecell.setCellStyle(titlestyle);
            titlecell.setCellValue(titleName);
        }

        // 创建列名称
        HSSFRow headRow = sheet.createRow((short) 1);//
        headRow.setHeightInPoints(20.120f);

        for (int i = 0; i < columnNames.length; i++) {
            HSSFCell cell = headRow.createCell((short) i);
            cell.setCellStyle(style);
            cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell编码解决中文高位字节截断
            cell.setCellValue(columnNames[i]);
        }
        // 创建内容
        for (int i = 0; i < excelContent.size(); i++) {
            // 建立新行
            HSSFRow row = sheet.createRow((short) i + 2);
            for (int j = 0; j < ((Object[]) excelContent.get(i)).length; j++) {
                // 新建一列
                HSSFCell cell = row.createCell((short) j);
                cell.setCellStyle(style2);
                cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell编码解决中文高位字节截断
                if (j == 0) {
                    cell.setCellValue((i + 1) + "");
                    continue;
                }
                Object t = ((Object[]) excelContent.get(i))[j];
                if (t instanceof BigDecimal) {
                    cell.setCellValue(((BigDecimal) ((Object[]) excelContent
                            .get(i))[j]).toString());
                    continue;
                }
                if (t instanceof java.sql.Date) {
                    cell.setCellValue(((java.sql.Date) ((Object[]) excelContent
                            .get(i))[j]).toString());
                    continue;
                }
                if (t instanceof java.util.Date) {
                    cell
                            .setCellValue(((java.util.Date) ((Object[]) excelContent
                                    .get(i))[j]).toString());
                    continue;
                }
                cell.setCellValue((String) ((Object[]) excelContent.get(i))[j]);
            }
        }

        request.setCharacterEncoding("gbk");
        MessageFormat.format("{0}/{1}.xls", request.getRealPath("expExcel"),
                Long.toString(System.currentTimeMillis())); // filename是工作薄的存放位置
        wb.write(fOut);
        fOut.flush();
        fOut.close();
    }

}

 

再者是调用的Servlet，只是为了演示，具体使用看自己的了：

package com.servlet;

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

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.util.ExcelUtil;

public class ExcelServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;

    @SuppressWarnings("unchecked")
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        List<?> excelContent = getPersonList();
        String[] columnNames = new String[] { "编号", "名称", "住址", "介绍", "出生日期",
                "月薪" };
        String titleName = "测试Excel工具类导出.xls";

        OutputStream fOut = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment; filename="
                + new String(titleName.getBytes("GB2312"), "ISO8859-1"));

        ExcelUtil.create(request, excelContent, columnNames, titleName, fOut);
    }

    @Override
    protected void doPost(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        this.doGet(request, response);
    }

    /**
     * 组装数据实体，实际上是内涵数组的集合，具体组装根据业务自行编写代码实现 数据体中的数字，小数点，日期等不用考虑直接使用
     *
     * @return
     */
    @SuppressWarnings("unchecked")
    public static List getPersonList() {
        List list = new ArrayList();
        String[] p1 = new String[] { "00001", "科研室主任", "海淀区", "北京大学研究生毕业",
                "1988-9-6", "4500.99" };
        String[] p2 = new String[] { "00002", "营销部经理", "朝阳区", "北京国际商务学院本科生",
                "1995-6-8", "6000.25" };
        String[] p3 = new String[] { "00003", "策划部专员", "石景山区", "清华大学应届毕业生",
                "1889-9-5", "10000" };
        list.add(p1);
        list.add(p2);
        list.add(p3);
        return list;
    }

}

 

如果有问题，欢迎大家提出意见和建议，我们一同进步

评论

1 楼 suan2046 2012-05-02  

仅仅只是针对小数据量的 导出，大数据量就有OOM问题