java实现导出报表poi

java实现报表的导出

在此我使用的是java原声jar包到处报表，基本都适用，这里可能会有一些相对封装不好的地方大家借鉴就好

首先创建一个实体类型：

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.hxjx.water.ht.servlet;
import java.io.File;  
import java.io.FileNotFoundException;  
import java.io.FileOutputStream;  
import java.io.IOException;  
  
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;  
  
/**
 *
 * @author Administrator
 */
public class ExportExcel {
    private HSSFWorkbook wb = null;  
    private HSSFSheet sheet = null;  
  
    /** 
     * @param wb 
     * @param sheet  
     */  
    public ExportExcel(HSSFWorkbook wb, HSSFSheet sheet) {  
        // super();  
        this.wb = wb;  
        this.sheet = sheet;  
    }  
  
    /** 
     * 创建通用EXCEL头部 
     *  
     * @param headString 
     *            头部显示的字符 
     * @param colSum 
     *            该报表的列数 
     */  
    @SuppressWarnings({ "deprecation", "unused" })  
    public void createNormalHead(String headString, int colSum) {  
        HSSFRow row = sheet.createRow(0);  
        // 设置第一行  
        HSSFCell cell = row.createCell(0);  
        // row.setHeight((short) 1000);  
  
        // 定义单元格为字符串类型  
        cell.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理  
        cell.setCellValue(new HSSFRichTextString(headString));  
  
        // 指定合并区域  
        /** 
         * public Region(int rowFrom, short colFrom, int rowTo, short colTo) 
         */  
        sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) colSum));  
  
        // 定义单元格格式，添加单元格表样式，并添加到工作簿  
        HSSFCellStyle cellStyle = wb.createCellStyle();  
        // 设置单元格水平对齐类型  
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐  
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐  
        cellStyle.setWrapText(true);// 指定单元格自动换行  
  
        // 设置单元格字体  
        HSSFFont font = wb.createFont();  
        // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
        // font.setFontName("宋体");  
        // font.setFontHeight((short) 600);  
        // cellStyle.setFont(font);  
        cell.setCellStyle(cellStyle);  
    }  
  
    /** 
     * 创建通用报表第二行 
     *  
     * @param params 
     *            统计条件数组 
     * @param colSum 
     *            需要合并到的列索引 
     */  
    @SuppressWarnings("deprecation")  
    public void createNormalTwoRow(String[] params, int colSum) {  
        // 创建第二行  
        HSSFRow row1 = sheet.createRow(1);  
  
        row1.setHeight((short) 400);  
  
        HSSFCell cell2 = row1.createCell(0);  
  
        cell2.setCellType(HSSFCell.ENCODING_UTF_16);  
        cell2.setCellValue(new HSSFRichTextString("时间：" + params[0] + "至"  
                + params[1]));  
  
        // 指定合并区域  
        /** 
         * public Region(int rowFrom, short colFrom, int rowTo, short colTo) 
         */  
        sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) colSum));  
  
        HSSFCellStyle cellStyle = wb.createCellStyle();  
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐  
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐  
        cellStyle.setWrapText(true);// 指定单元格自动换行  
  
        // 设置单元格字体  
        HSSFFont font = wb.createFont();  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
        font.setFontName("宋体");  
        font.setFontHeight((short) 250);  
        cellStyle.setFont(font);  
  
        cell2.setCellStyle(cellStyle);  
    }  
  
    /** 
     * 设置报表标题 
     *  
     * @param columHeader 
     *            标题字符串数组 
     */  
    public void createColumHeader(String[] columHeader) {  
  
        // 设置列头 在第三行  
        HSSFRow row2 = sheet.createRow(2);  
  
        // 指定行高  
        row2.setHeight((short) 600);  
  
        HSSFCellStyle cellStyle = wb.createCellStyle();  
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐  
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐  
        cellStyle.setWrapText(true);// 指定单元格自动换行  
  
        // 单元格字体  
        HSSFFont font = wb.createFont();  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
        font.setFontName("宋体");  
        font.setFontHeight((short) 250);  
        cellStyle.setFont(font);  
  
        // 设置单元格背景色  
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);  
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
  
        HSSFCell cell3 = null;  
  
        for (int i = 0; i < columHeader.length; i++) {  
            cell3 = row2.createCell(i);  
            cell3.setCellType(HSSFCell.ENCODING_UTF_16);  
            cell3.setCellStyle(cellStyle);  
            cell3.setCellValue(new HSSFRichTextString(columHeader[i]));  
        }  
    }  
  
    /** 
     * 创建内容单元格 
     *  
     * @param wb 
     *            HSSFWorkbook 
     * @param row 
     *            HSSFRow 
     * @param col 
     *            short型的列索引 
     * @param align 
     *            对齐方式 
     * @param val 
     *            列值 
     */  
    public void cteateCell(HSSFWorkbook wb, HSSFRow row, int col, short align,  
            String val) {  
        HSSFCell cell = row.createCell(col);  
        cell.setCellType(HSSFCell.ENCODING_UTF_16);  
        cell.setCellValue(new HSSFRichTextString(val));  
        HSSFCellStyle cellstyle = wb.createCellStyle();  
        cellstyle.setAlignment(align);  
        cell.setCellStyle(cellstyle);  
    }  
  
    /** 
     * 创建合计行 
     *  
     * @param colSum 
     *            需要合并到的列索引 
     * @param cellValue 
     */  
    @SuppressWarnings("deprecation")  
    public void createLastSumRow(int colSum, String[] cellValue) {  
  
        HSSFCellStyle cellStyle = wb.createCellStyle();  
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐  
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐  
        cellStyle.setWrapText(true);// 指定单元格自动换行  
  
        // 单元格字体  
        HSSFFont font = wb.createFont();  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
        font.setFontName("宋体");  
        font.setFontHeight((short) 250);  
        cellStyle.setFont(font);  
        // 获取工作表最后一行  
        HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));  
        HSSFCell sumCell = lastRow.createCell(0);  
  
        sumCell.setCellValue(new HSSFRichTextString("合计"));  
        sumCell.setCellStyle(cellStyle);  
        // 合并 最后一行的第零列-最后一行的第一列  
        sheet.addMergedRegion(new Region(sheet.getLastRowNum(), (short) 0,  
                sheet.getLastRowNum(), (short) colSum));// 指定合并区域  
  
        for (int i = 2; i < (cellValue.length + 2); i++) {  
            // 定义最后一行的第三列  
            sumCell = lastRow.createCell(i);  
            sumCell.setCellStyle(cellStyle);  
            // 定义数组 从0开始。  
            sum加粗样式Cell.setCellValue(new HSSFRichTextString(cellValue[i - 2]));  
        }  
    }  
  
    /** 
     * 输入EXCEL文件 
     *  
     * @param fileName 
     *            文件名 
     */  
    public void outputExcel(String fileName) {  
        FileOutputStream fos = null;  
        try {  
            fos = new FileOutputStream(new File(fileName));  
            wb.write(fos);  
            fos.close();  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    }  
  
    /** 
     * @return the sheet 
     */  
    public HSSFSheet getSheet() {  
        return sheet;  
    }  
  
    /** 
     * @param sheet 
     *            the sheet to set 
     */  
    public void setSheet(HSSFSheet sheet) {  
        this.sheet = sheet;  
    }  
  
    /** 
     * @return the wb 
     */  
    public HSSFWorkbook getWb() {  
        return wb;  
    }  
  
    /** 
     * @param wb 
     *            the wb to set 
     */  
    public void setWb(HSSFWorkbook wb) {  
        this.wb = wb;  
    }  
}  

书写一个工具类

 public void expont(HttpServletRequest request, HttpServletResponse response, List<Map<String, String>> lis, List columName, List list1, String name)
            throws UnsupportedEncodingException, IOException {
            //lis 为sql查出的数据串 list1为该表的列 name 为表名字,columName数据库        表的字段名称或者别名
  System.out.println("导出报表1");
        String fileName = name + ".xls";
        fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename="
                + fileName);// 指定下载的文件名  
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        OutputStream output = response.getOutputStream();
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
        // 定义单元格报头  
        String worksheetTitle = name;

        HSSFWorkbook wb = new HSSFWorkbook();

        // 创建单元格样式  
        HSSFCellStyle cellStyleTitle = wb.createCellStyle();
        // 指定单元格居中对齐  
        cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐  
        cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定当单元格内容显示不下时自动换行  
        cellStyleTitle.setWrapText(true);
        // ------------------------------------------------------------------  
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 指定单元格居中对齐  
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐  
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定当单元格内容显示不下时自动换行  
        cellStyle.setWrapText(true);
        // ------------------------------------------------------------------  
        // 设置单元格字体  
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyleTitle.setFont(font);
        //------
        HSSFSheet sheet = wb.createSheet();
        ExportExcel exportExcel = new ExportExcel(wb, sheet);
        // 创建报表头部  
        exportExcel.createNormalHead(worksheetTitle,list1.size() );
        // 定义第一行  
        //for循环定义第一行的每一列
        HSSFRow row1 = sheet.createRow(1);
        for (int i = 0; i < list1.size(); i++) {
            HSSFCell cell1 = row1.createCell(i);
            cell1.setCellStyle(cellStyleTitle);
            System.out.println("__=++"+list1.get(i));
            cell1.setCellValue(new HSSFRichTextString((String) list1.get(i)));
        }
        //定义第二行  
        HSSFRow row = sheet.createRow(2);
        HSSFCell cell = row.createCell(1);

        for (int i = 0; i < lis.size(); i++) {

            row = sheet.createRow(i + 2);

            for (int b = 0; b <columName.size(); b++) {
                cell = row.createCell(b);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(new HSSFRichTextString(lis.get(i).get(columName.get(b))));

            }
        }
        try {
            bufferedOutPut.flush();
            wb.write(bufferedOutPut);
            bufferedOutPut.close();
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("Output   is   closed ");
        } finally {
            lis.clear();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

}

查出数据调用工具类

    public void SiteList(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String sql = "select no,siteName,addr,name,sitephone,cardNum ,agent,developMan,waterNum from site where deleted=0 ";
        MysqlDB db = new MysqlDB();
        //为sql查出的数据串
        List<Map<String, String>> lis = db.getMultQuery(sql);
        System.out.println("导出报表");
        //,columName数据库表的字段名称或者别名;
        List columName = new ArrayList();
        columName.add("no");//字段名字
        columName.add("siteName");
        columName.add("addr");
        columName.add("name");
        columName.add("sitephone");
        columName.add("cardNum");
        columName.add("agent");
        columName.add("developMan");
        columName.add("waterNum");
        //list1为该表的列 name 为表名字
        List list1 = new ArrayList();
        list1.add("编号");//这里的名字就是报表导出后excle表头的名字
        list1.add("名称");
        list1.add("地址");
        list1.add("人");
        list1.add("电话");
        list1.add("量");
        list1.add("asd");
        list1.add("asf");
        list1.add("asf");
        //表名字
        String name="网点信息表";
        expont( request,  response, lis, columName,  list1,  name);

    }