用POI创建Excel文件及下载

来源：互联网发布：js函数返回值编辑：程序博客网时间：2024/05/14 20:55

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.12014年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 输入文件存放于服务器路径      * @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.12014年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;                    }                }            }        }    }}

0 0