使用POI创建Excel文件下载

转载

mb5fe3290a9b4fe 2015-08-17 20:52:00

文章标签 java apache 导出excel 浮点数下载文件 文章分类 Java 后端开发

POIExcelUtils.java:

package com.saicfc.pmpf.internal.manage.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.saicfc.pmpf.common.enums.ChannelCodeEnums;

/** 
 * @author lizhiyong
 * @version $Id: POIExcelUtils.java, v 0.1
        2014年9月18日 上午9:28:30 Exp $ 
 */
public class POIExcelUtils {

    /**
     * 定制日期格式 
     */
    private static String       DATE_FORMAT    = "yyyy-MM-dd HH:mm:ss";

    /**
     * 定制浮点数格式 
     */
    private static String       NUMBER_FORMAT  = "#,##0.00";

    /**
     * 定制百分比格式
     */
    private static String       PRECENT_FORMAT = "0.00%";

    private static HSSFWorkbook workbook       = new HSSFWorkbook();

    private static HSSFSheet    sheet          = workbook.createSheet();

    private static HSSFRow      row;

    /**
     * 导出Excel文件
     * @param filePath
     * @throws IOException
     */
    public static void exportXLS(String filePath) throws IOException {
        try {
            FileOutputStream fOut = new FileOutputStream(filePath);
            workbook.write(fOut);
            fOut.flush();
            fOut.close();
        } catch (IOException e) {
            e.getStackTrace();
        }
    }

    /**
     * 导出Excel文件
     * @param file 
     * @throws IOException
     */
    public static void exportXLS(File file) throws IOException {
        try {
            FileOutputStream fOut = new FileOutputStream(file);
            workbook.write(fOut);
            fOut.flush();
            fOut.close();
        } catch (IOException e) {
            e.getStackTrace();
        }
    }

    /** 
     * 添加一行
     *  @param index 行号
     */
    public static void createRow(int index) {
        row = sheet.createRow(index);
    }

    /**
     * 设置单元格的字符值格式
     * @param index  列号
     * @param value  单元格填充的值
     */
    public static void setStringCell(int index, String value) {
        HSSFCell cell = row.createCell(index);
        cell.setCellValue(value);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    }

    /** 
     * 设置单元格日期格式
     *  @param index 列号
     *  @param value 单元格填充值
     */
    public static void setDateCell(int index, Calendar value) {
        HSSFCell cell = row.createCell(index);
        cell.setCellValue(value.getTime());
        //建立新的cell样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        //设置cell样式为定制的日期格式
        cellStyle.setDataFormat(format.getFormat(DATE_FORMAT));
        //居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置该cell日期的显示格式
        cell.setCellStyle(cellStyle);
    }

    /** 
     * 设置单元格整数數值格式
     *  @param index 列号
     *  @param value 单元格填充值
     */
    public static void setIntCell(int index, int value) {
        HSSFCell cell = row.createCell(index);
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellValue(value);
    }

    /** 
     * 设置单元格浮点数值格式
     *  @param index 列号
     *  @param value 单元格填充值
     */
    public static void setNumberCell(int index, double value) {
        HSSFCell cell = row.createCell(index);
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellValue(value);
        //建立新的cell样式 
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        //设置cell样式为定制的浮点数格式
        cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
        //设置该cell浮点数的显示格式 
        cell.setCellStyle(cellStyle);
    }

    /**
     * 设置单元格百分比格式
     * @param index  列号
     * @param value  单元格填充值
     */
    public static void setPercentCell(int index, double value) {
        HSSFCell cell = row.createCell(index);
        cell.setCellValue(value);
        //建立新的cell样式 
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        cellStyle.setDataFormat(format.getFormat(PRECENT_FORMAT));
        cell.setCellStyle(cellStyle);
    }

    public static void main(String[] args) {
        System.out.println(" 開始导出Excel文件 ");
        createRow(0);
        setStringCell(0, " 编号 ");
        setStringCell(1, " 名称 ");
        setStringCell(2, " 日期 ");
        setStringCell(3, " 金额 ");
        createRow(1);
        setIntCell(0, 1);
        setStringCell(1, " 工商银行 ");
        setDateCell(2, Calendar.getInstance());
        setNumberCell(3, 111123.99);
        createRow(2);
        setIntCell(0, 2);
        setStringCell(1, " 招商银行 ");
        setDateCell(2, Calendar.getInstance());
        setNumberCell(3, 222456.88);
        try {
            String filePath = "C:/lizhiyong.xls";
            exportXLS(filePath);
            System.out.println(" 导出Excel文件[成功] ");
        } catch (IOException e1) {
            System.out.println(" 导出Excel文件[失败] ");
            e1.printStackTrace();
        }
    }

    /** 
     * 生成一个Excel文件POI
     * @param inputFile 输入模板文件路径 
     * @param outputFile 输入文件存放于server路径 
     * @param dataList 待导出数据 
     * @throws Exception 
     */
    @SuppressWarnings("rawtypes")
    public static File exportExcelFile(String channelCode, String filePath, List titleList,
                                       List dataList, String fileName) throws Exception {
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdir();
            System.out.println("目录已创建");
        }
        if (ChannelCodeEnums.PINGAN.getChannelCode().equals(channelCode)) {
            //设置列宽
            sheet.setColumnWidth(0, 5000);
            sheet.setColumnWidth(1, 4000);
            sheet.setColumnWidth(2, 8000);
        }
        //定义文件名称格式并创建
        File excelFile = File.createTempFile(fileName, ".xls", new File(filePath));
        //加入头信息
        int row = 0;
        for (Iterator iterator = titleList.iterator(); iterator.hasNext();) {
            LinkedHashMap titleMap = (LinkedHashMap) iterator.next();
            //新增一行
            createRow(row);
            int cell = 0;
            for (Iterator titleIterator = titleMap.entrySet().iterator(); titleIterator.hasNext();) {
                java.util.Map.Entry titleEntry = (java.util.Map.Entry) titleIterator.next();
                //向列中加入值
                setStringCell(cell, (String) titleEntry.getValue());
                cell++;
            }
            row++;
        }

        //以下開始加入单元格信息 
        int rows = titleList.size();
        for (Iterator iterator = dataList.iterator(); iterator.hasNext();) {
            LinkedHashMap dataMap = (LinkedHashMap) iterator.next();
            //新增一行
            createRow(rows);
            int cells = 0;
            for (Iterator dataIterator = dataMap.entrySet().iterator(); dataIterator.hasNext();) {
                java.util.Map.Entry dataEntry = (java.util.Map.Entry) dataIterator.next();
                if (ChannelCodeEnums.PINGAN.getChannelCode().equals(channelCode)) {
                    if ("refChannelOrderNo".equals(dataEntry.getKey())) {
                        //向列中加入值
                        setStringCell(cells, (String) dataEntry.getValue());
                    } else if ("amount".equals(dataEntry.getKey())) {
                        //向列中加入浮点型数值
                        setNumberCell(cells, Double.parseDouble((String) dataEntry.getValue()));
                    } else {
                        //向列中加入值
                        setStringCell(cells, (String) dataEntry.getValue());
                    }
                } else {
                    //向列中加入值
                    setStringCell(cells, (String) dataEntry.getValue());
                }
                cells++;
            }
            rows++;
        }

        exportXLS(excelFile);
        return excelFile;
    }
}

以下是调用：

String fileName = "平安银行(PINGAN)退款数据";
 List titleList = new ArrayList();
 LinkedHashMap titleMap = new LinkedHashMap();
 titleMap.put("title1", "订单号");
 titleMap.put("title2", "退款金额");
 titleMap.put("title3", "退款原因");
 titleList.add(0, titleMap);
 File file;
 try {
    file = POIExcelUtils.exportExcelFile(channelCode, filePath, titleList, exportData,
                    fileName);
    //下载文件
    downLoadFile(response, filePath, file);
  } catch (Exception e) {
    log.error("下载失败", e);
}

/**
     * 下载文件
     * @param response
     * @param filePath  文件路径
     * @param file   文件
     * @throws IOException
     */
    public void downLoadFile(HttpServletResponse response, String filePath, File file)
                                                                                      throws IOException {
        String fileName = file.getName();
        //下载文件
        FileManageUtils.exportFile(response, filePath + fileName, fileName);
        //删除单个文件
        FileManageUtils.deleteFile(filePath, fileName);
    }

package com.saicfc.pmpf.internal.manage.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import javax.servlet.http.HttpServletResponse;

/**
 * 文件管理
 * @author lizhiyong
 * @version $Id: FileManageUtils.java, v 0.1
        2014年9月11日 上午9:37:47 Exp $
 */
public class FileManageUtils {

    /**
     * 下载文件
     * @param response
     * @param csvFilePath
     *              文件路径
     * @param fileName
     *              文件名
     * @throws IOException
     */
    public static void exportFile(HttpServletResponse response, String csvFilePath, String fileName)
                                                                                                    throws IOException {
        response.setContentType("application/csv;charset=GBK");
        response.setHeader("Content-Disposition",
            "attachment;  filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));
        //URLEncoder.encode(fileName, "GBK")

        InputStream in = null;
        try {
            in = new FileInputStream(csvFilePath);
            int len = 0;
            byte[] buffer = new byte[1024];
            response.setCharacterEncoding("GBK");
            OutputStream out = response.getOutputStream();
            while ((len = in.read(buffer)) > 0) {
                //out.write(new byte[] { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF });
                out.write(buffer, 0, len);
            }
        } catch (FileNotFoundException e) {
            System.out.println(e);
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    /**
     * 删除该文件夹filePath下的全部文件
     * @param filePath
     *            文件文件夹路径
     */
    public static void deleteFiles(String filePath) {
        File file = new File(filePath);
        if (file.exists()) {
            File[] files = file.listFiles();
            for (int i = 0; i < files.length; i++) {
                if (files[i].isFile()) {
                    files[i].delete();
                }
            }
        }
    }

    /**
     * 删除单个文件
     * @param filePath
     *         文件文件夹路径
     * @param fileName
     *         文件名
     */
    public static void deleteFile(String filePath, String fileName) {
        File file = new File(filePath);
        if (file.exists()) {
            File[] files = file.listFiles();
            for (int i = 0; i < files.length; i++) {
                if (files[i].isFile()) {
                    if (files[i].getName().equals(fileName)) {
                        files[i].delete();
                        return;
                    }
                }
            }
        }
    }
}