Excel读写工具类

缘起

在J2SE和J2EE应用程序开发中，经常会遇到上传Excel，导出Excel的功能开发，对Excel的操作无非就是读取Excel文件内容转成javabean，或者是将javabean对象写入Excel文件中。为了方便对Excel进行读写操作，可以将这块代码进行封装，读取Excel的操作封装在ExcelReadKit，写入Excel的操作封装在ExcelWriteKit工具类中。

核心代码

下面会写出核心的java代码，其中会用到apache poi 这个开源组建对Excel进行读写操作。

ExcelConfig.java

import org.apache.poi.ss.usermodel.Cell;

public class ExcelConfig {
    // Excel文件输出格式
    public static final String FT_XLS = "xls";
    public static final String FT_XLSX = "xlsx";

    // Excel格数据类型
    public static final int CT_NUMERIC = Cell.CELL_TYPE_NUMERIC;
    public static final int CT_STRING = Cell.CELL_TYPE_STRING;
    public static final int CT_BOOLEAN = Cell.CELL_TYPE_BOOLEAN;
    public static final int CT_BLANK = Cell.CELL_TYPE_BLANK;
    public static final int CT_FORMULA = Cell.CELL_TYPE_FORMULA;
}

ExcelReadResultBean.java

import java.util.List;

public class ExcelReadResultBean {
    private boolean result = true;
    private String errMsg = "";
    private List<List<String>> contentList = null;

    public boolean getResult() {
        return result;
    }

    public void setResult(boolean result) {
        this.result = result;
    }

    public String getErrMsg() {
        return errMsg;
    }

    public void setErrMsg(String errMsg) {
        this.errMsg = errMsg;
    }

    public List<List<String>> getContentList() {
        return contentList;
    }

    public void setContentList(List<List<String>> contentList) {
        this.contentList = contentList;
    }
}

ExcelWriteBean.java

package com.trendy.fw.common.excel;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class ExcelWriteBean {
    String fileName = "";// 文件名，不带后缀
    String fileType = "";// 文件类型，xsl或xslx
    String sheetName = "";// sheet名
    List<String> headerList = new ArrayList<String>();// 表头列表，可以为空
    List<List<Object>> contentList = new ArrayList<List<Object>>();// 内容列表
    HashMap<Integer, Integer> cellTypeMap = new HashMap<Integer, Integer>();// 表格类型，不填写默认为字符串
    HashMap<Integer, String> cellFormatMap = new HashMap<Integer, String>();// 表格格式，不填写默认为字符串格式

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public String getFileType() {
        return fileType;
    }

    public void setFileType(String fileType) {
        this.fileType = fileType;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public List<String> getHeaderList() {
        return headerList;
    }

    public void setHeaderList(List<String> headerList) {
        this.headerList = headerList;
    }

    public List<List<Object>> getContentList() {
        return contentList;
    }

    public void setContentList(List<List<Object>> contentList) {
        this.contentList = contentList;
    }

    public HashMap<Integer, Integer> getCellTypeMap() {
        return cellTypeMap;
    }

    public void setCellTypeMap(HashMap<Integer, Integer> cellTypeMap) {
        this.cellTypeMap = cellTypeMap;
    }

    public void setCellType(int cellIndex, int cellType) {
        cellTypeMap.put(cellIndex, cellType);
    }

    public HashMap<Integer, String> getCellFormatMap() {
        return cellFormatMap;
    }

    public void setCellFormatMap(HashMap<Integer, String> cellFormatMap) {
        this.cellFormatMap = cellFormatMap;
    }

    public void setCellFormat(int cellIndex, String cellFormat) {
        cellFormatMap.put(cellIndex, cellFormat);
    }
}

ExcelReadKit.java

package com.trendy.fw.common.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.trendy.fw.common.util.DateKit;

public class ExcelReadKit {

    protected static Logger log = LoggerFactory.getLogger(ExcelReadKit.class);

    /**
     * 
     * @param filePath
     *            文件路径
     * @param sheetIndex
     *            第x个sheet
     * @return
     */
    public ExcelReadResultBean readExcel(String filePath, int sheetIndex) {
        ExcelReadResultBean resultBean = new ExcelReadResultBean();
        try {
            Sheet sheet = null;
            if (filePath.endsWith(ExcelConfig.FT_XLS)) {
                FileInputStream fis = new FileInputStream(filePath); // 根据excel文件路径创建文件流
                POIFSFileSystem fs = new POIFSFileSystem(fis); // 利用poi读取excel文件流
                HSSFWorkbook wb = new HSSFWorkbook(fs); // 读取excel工作簿
                sheet = wb.getSheetAt(sheetIndex); // 读取excel的sheet，0表示读取第一个
            } else {
                OPCPackage pkg = OPCPackage.open(new File(filePath));
                XSSFWorkbook wb = new XSSFWorkbook(pkg);// 读取excel工作簿
                sheet = wb.getSheetAt(sheetIndex); // 读取excel的sheet，0表示读取第一个
                pkg.close();
            }

            resultBean = realSheetValue(sheet);
        } catch (Exception e) {
            log.error("[读取Excel<{}>出错]：", filePath, e);
            resultBean.setResult(false);
            resultBean.setErrMsg("读取Excel文件出错");
        }
        return resultBean;
    }

    /**
     * 
     * @param fis
     *            输入的文件流
     * @param sheetIndex
     *            第x个sheet
     * @return
     */
    public ExcelReadResultBean readExcel(InputStream fis, int sheetIndex) {
        ExcelReadResultBean resultBean = new ExcelReadResultBean();
        try {
            Sheet sheet = null;
            Workbook wb = null;
            try {
                POIFSFileSystem fs = new POIFSFileSystem(fis); // 利用poi读取excel文件流
                wb = new HSSFWorkbook(fs); // 读取excel工作簿
                sheet = wb.getSheetAt(sheetIndex); // 读取excel的sheet，0表示读取第一个
            } catch (Exception e) {
                wb = new XSSFWorkbook(fis); // 读取excel工作簿
                sheet = wb.getSheetAt(sheetIndex); // 读取excel的sheet，0表示读取第一个
            }

            resultBean = realSheetValue(sheet);

            wb.cloneSheet(sheetIndex);
            fis.close();
        } catch (Exception e) {
            log.error("读取Excel文件流时出错：", e);
            resultBean.setResult(false);
            resultBean.setErrMsg("读取Excel文件流出错");
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (Exception e) {
                }
            }
        }
        return resultBean;
    }

    private ExcelReadResultBean realSheetValue(Sheet sheet) {
        ExcelReadResultBean resultBean = new ExcelReadResultBean();
        boolean result = true;
        String errMsg = "";
        List<List<String>> list = new ArrayList<List<String>>();

        int i = 0, j = 0;
        for (i = 0; i <= sheet.getLastRowNum(); i++) {
            try {
                Row row = sheet.getRow(i); // 取出sheet中的某一行数据
                if (row != null) {
                    List<String> rowList = new ArrayList<String>(row.getPhysicalNumberOfCells());
                    // 获取该行中总共有多少列数据row.getLastCellNum()
                    for (j = 0; j < row.getLastCellNum(); j++) {
                        try {
                            Cell cell = row.getCell(j); // 获取该行中的一个单元格对象
                            /*
                             * 当取某一行中的数据的时候，需要判断数据类型，否则会报错
                             * java.lang.NumberFormatException: You cannot get a
                             * string value from a numeric cell等等错误
                             */
                            if (cell != null) {// 判断cell是否为空
                                if (cell.getCellType() == ExcelConfig.CT_NUMERIC) {
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否日期类型
                                        Date dateValue = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                                        rowList.add(DateKit.formatDate(dateValue, DateKit.DEFAULT_DATE_TIME_FORMAT));
                                    } else {
                                        rowList.add(String.valueOf(cell.getNumericCellValue()));
                                    }
                                } else if (cell.getCellType() == ExcelConfig.CT_FORMULA) {// 读取公式的值
                                    try {
                                        rowList.add(String.valueOf(cell.getNumericCellValue()));
                                    } catch (IllegalStateException e) {
                                        rowList.add(String.valueOf(cell.getRichStringCellValue()));
                                    }
                                } else {
                                    rowList.add(cell.getStringCellValue());
                                }
                            } else {// 如果cell为空，用空格字段代替
                                rowList.add("");
                            }
                        } catch (Exception e) {
                            log.error("读取{}行{}列时出错", i + 1, j + 1);
                            result = false;
                            errMsg = errMsg + "读取" + (i + 1) + "行" + (j + 1) + "列时出错;";
                            rowList.add("");
                        }
                    }
                    list.add(rowList);
                }
            } catch (Exception e) {
                log.error("读取{}行时出错", i + 1);
                result = false;
                errMsg = errMsg + "读取" + (i + 1) + "行时出错";
            }
        }

        resultBean.setResult(result);
        resultBean.setErrMsg(errMsg);
        resultBean.setContentList(list);
        return resultBean;
    }
}

ExcelWriteKit.java

   package com.trendy.fw.common.excel;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.trendy.fw.common.config.Constants;
import com.trendy.fw.common.util.StringKit;

public class ExcelWriteKit {
    protected static Logger log = LoggerFactory.getLogger(ExcelWriteKit.class);

    private short fontSize = 11;

    private CellStyle cellStyleCommon = null;
    private CellStyle cellStyleHeader = null;
    private CellStyle cellStyleNumeric = null;
    private CellStyle cellStyleDate = null;

    private Font fontStyleCommon = null;
    private Font fontStyleBolder = null;

    private boolean isNeedStyle = true;

    public ExcelWriteKit() {

    }

    public Workbook createWorkbook(String fileType) {
        Workbook wb = null;
        if (fileType.equals(ExcelConfig.FT_XLS)) {
            wb = new HSSFWorkbook();
        } else {
            wb = new SXSSFWorkbook(-1);
        }
        return wb;
    }

    /**
     * 创建一个内容格，字符串格式
     * 
     * @param wb
     *            工作表
     * @param row
     *            行
     * @param cellIndex
     *            列
     * @param cellValue
     *            内容值
     * @return
     */
    public Cell createCell(Workbook wb, Row row, int cellIndex, String cellValue) {
        Cell cell = row.createCell(cellIndex);
        cell.setCellType(ExcelConfig.CT_STRING);
        cell.setCellValue(cellValue);
        cell.setCellStyle(getCommonCellStyle(wb));
        return cell;
    }

    private CellStyle getCommonCellStyle(Workbook wb) {
        if (isNeedStyle) {
            if (cellStyleCommon == null) {
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle = addCellBorder(cellStyle);
                cellStyle.setFont(getCellFont(wb));
                cellStyleCommon = cellStyle;
            }
        }
        return cellStyleCommon;
    }

    /**
     * 创建一个表头内容格
     * 
     * @param wb
     *            工作表
     * @param row
     *            列
     * @param cellIndex
     *            行
     * @param cellValue
     *            内容值
     * @return
     */
    public Cell createHeaderCell(Workbook wb, Row row, int cellIndex, String cellValue) {
        Cell cell = row.createCell(cellIndex);
        cell.setCellValue(cellValue);
        cell.setCellStyle(getHeaderCellStyle(wb));
        return cell;
    }

    private CellStyle getHeaderCellStyle(Workbook wb) {
        if (isNeedStyle) {
            if (cellStyleHeader == null) {
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle = addCellBorder(cellStyle);
                cellStyle.setFont(getCellBoldFont(wb));
                cellStyleHeader = cellStyle;
            }
        }
        return cellStyleHeader;
    }

    /**
     * 创建一个数字内容格
     * 
     * @param wb
     *            内容表
     * @param row
     *            列
     * @param cellIndex
     *            行
     * @param cellValue
     *            内容值
     * @param formatStr
     *            格式
     * @return
     */
    public Cell createNumericCell(Workbook wb, Row row, int cellIndex, double cellValue, String formatStr) {
        Cell cell = row.createCell(cellIndex, ExcelConfig.CT_NUMERIC);
        cell.setCellValue(cellValue);
        cell.setCellStyle(getNumericCellStyle(wb, formatStr));
        return cell;
    }

    private CellStyle getNumericCellStyle(Workbook wb, String formatStr) {
        if (isNeedStyle) {
            if (cellStyleNumeric == null) {
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle = addCellBorder(cellStyle);
                cellStyle.setFont(getCellFont(wb));

                DataFormat format = wb.createDataFormat();
                cellStyle.setDataFormat(format.getFormat(formatStr));
                cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);

                cellStyleNumeric = cellStyle;
            }
        }
        return cellStyleNumeric;
    }

    /**
     * 创建一个日期内容格
     * 
     * @param wb
     *            内容表
     * @param row
     *            列
     * @param cellIndex
     *            行
     * @param cellValue
     *            内容值
     * @param formatStr
     *            格式
     * @return
     */
    public Cell createDateCell(Workbook wb, Row row, int cellIndex, Date cellValue, String formatStr) {
        Cell cell = row.createCell(cellIndex);
        cell.setCellValue(cellValue);
        cell.setCellStyle(getDateCellStyle(wb, formatStr));
        return cell;
    }

    private CellStyle getDateCellStyle(Workbook wb, String formatStr) {
        if (isNeedStyle) {
            if (cellStyleDate == null) {
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle = addCellBorder(cellStyle);
                cellStyle.setFont(getCellFont(wb));
                cellStyleDate = cellStyle;
            }
        }
        return cellStyleDate;
    }

    /**
     * 增加内容格边线
     * 
     * @param cellStyle
     * @return
     */
    public CellStyle addCellBorder(CellStyle cellStyle) {
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        return cellStyle;
    }

    /**
     * 获取普通字体
     * 
     * @param wb
     *            工作表
     * @return
     */
    public Font getCellFont(Workbook wb) {
        if (fontStyleCommon == null) {
            Font font = wb.createFont();
            font.setFontHeightInPoints(getFontSize());
            fontStyleCommon = font;
        }
        return fontStyleCommon;
    }

    /**
     * 获取加粗字体
     * 
     * @param wb
     *            工作表
     * @return
     */
    public Font getCellBoldFont(Workbook wb) {
        if (fontStyleBolder == null) {
            Font font = wb.createFont();
            font.setFontHeightInPoints(getFontSize());
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            fontStyleBolder = font;
        }
        return fontStyleBolder;
    }

    /**
     * 写Excel工作簿
     * 
     * @param bean
     *            WriteExcelBean
     * @return Workbook工作簿对象
     */
    public Workbook writeExcel(ExcelWriteBean bean) {
        Workbook wb = createWorkbook(bean.getFileType());
        String sheetName = bean.getSheetName();
        if (!StringKit.isValid(sheetName)) {
            sheetName = "sheet1";
        }
        Sheet sheet = wb.createSheet(sheetName);

        // 处理表头内容
        if (bean.getHeaderList().size() > 0) {
            Row row = sheet.createRow(0);
            for (int i = 0; i < bean.getHeaderList().size(); i++) {
                String headerValue = bean.getHeaderList().get(i);
                createHeaderCell(wb, row, i, headerValue);
            }
        }

        // 处理表中内容
        if (bean.getContentList().size() > 0) {
            int rowCount = 1;// 行计数器
            // 没有表头的情况
            if (bean.getHeaderList().size() == 0) {
                rowCount = 0;
            }

            for (List<Object> contentList : bean.getContentList()) {
                Row row = sheet.createRow(rowCount);
                for (int i = 0; i < contentList.size(); i++) {
                    Object cellValue = contentList.get(i);
                    if (getCellType(i, bean.getCellTypeMap()) == ExcelConfig.CT_NUMERIC) {
                        if (cellValue == null) {// 如果值为空，默认填0
                            cellValue = new Integer(0);
                        }
                        createNumericCell(wb, row, i, Double.valueOf(cellValue.toString()),
                                getCellFormat(i, bean.getCellFormatMap()));
                    } else {
                        if (cellValue == null) {// 如果值为空，默认空字符串
                            cellValue = new String("");
                        }
                        createCell(wb, row, i, cellValue.toString());
                    }
                }
                rowCount++;

                if (rowCount % 100 == 0) {
                    try {
                        ((SXSSFSheet) sheet).flushRows(100);
                    } catch (Exception e) {
                        log.error("", e);
                    }
                }
            }

        }
        return wb;
    }

    /**
     * 写Excel工作簿多个sheet
     * 
     * @param bean
     *            WriteExcelBean
     * @return Workbook工作簿对象
     */
    public Workbook writeExcel(List<ExcelWriteBean> excelWriteList) {
        if(excelWriteList == null || excelWriteList.size()==0){
            return null;
        }
        Workbook wb = createWorkbook(excelWriteList.get(0).getFileType());

        int sheetNumber = 0;
        for(ExcelWriteBean bean : excelWriteList){
            sheetNumber++;
            String sheetName = bean.getSheetName();
            if (!StringKit.isValid(sheetName)) {
                sheetName = "sheet"+sheetNumber;
            }
            Sheet sheet = wb.createSheet(sheetName);

            // 处理表头内容
            if (bean.getHeaderList().size() > 0) {
                Row row = sheet.createRow(0);
                for (int i = 0; i < bean.getHeaderList().size(); i++) {
                    String headerValue = bean.getHeaderList().get(i);
                    createHeaderCell(wb, row, i, headerValue);
                }
            }

            // 处理表中内容
            if (bean.getContentList().size() > 0) {
                int rowCount = 1;// 行计数器
                // 没有表头的情况
                if (bean.getHeaderList().size() == 0) {
                    rowCount = 0;
                }

                for (List<Object> contentList : bean.getContentList()) {
                    Row row = sheet.createRow(rowCount);
                    for (int i = 0; i < contentList.size(); i++) {
                        Object cellValue = contentList.get(i);
                        if (getCellType(i, bean.getCellTypeMap()) == ExcelConfig.CT_NUMERIC) {
                            if (cellValue == null) {// 如果值为空，默认填0
                                cellValue = new Integer(0);
                            }
                            createNumericCell(wb, row, i, Double.valueOf(cellValue.toString()),
                                    getCellFormat(i, bean.getCellFormatMap()));
                        } else {
                            if (cellValue == null) {// 如果值为空，默认空字符串
                                cellValue = new String("");
                            }
                            createCell(wb, row, i, cellValue.toString());
                        }
                    }
                    rowCount++;

                    if (rowCount % 100 == 0) {
                        try {
                            ((SXSSFSheet) sheet).flushRows(100);
                        } catch (Exception e) {
                            log.error("", e);
                        }
                    }
                }

            }
        }
        return wb;
    }

    /**
     * 输出Excel文件
     * 
     * @param bean
     *            WriteExcelBean
     * @param filePath
     *            文件全路径
     */
    public void outputExcel(ExcelWriteBean bean, String filePath) {
        FileOutputStream fos = null;
        try {
            Workbook wb = writeExcel(bean);
            String fileName = bean.getFileName() + "." + bean.getFileType();
            fos = new FileOutputStream(filePath + Constants.FILE_SEPARATOR + fileName);
            wb.write(fos);
            fos.close();
        } catch (IOException e) {
            log.error("输出文件[{}]出错：", filePath, e);
        } catch (Exception e) {
            log.error("输出文件[{}]出错：", filePath, e);
        } finally {
            try {
                if (fos != null)
                    fos.close();
            } catch (Exception e) {
                log.error("输出文件[{}]出错：", filePath, e);
            }
        }
    }

    /**
     * 获取cell的类型
     * 
     * @param cellIndex
     * @param cellTypeMap
     * @return
     */
    private int getCellType(int cellIndex, HashMap<Integer, Integer> cellTypeMap) {
        int cellType = ExcelConfig.CT_STRING;
        try {
            if (!cellTypeMap.isEmpty()) {
                cellType = cellTypeMap.get(cellIndex);
            }
        } catch (Exception e) {
            cellType = ExcelConfig.CT_STRING;
        }
        return cellType;
    }

    /**
     * 获取cell的格式
     * 
     * @param cellIndex
     * @param cellFormatMap
     * @return
     */
    private String getCellFormat(int cellIndex, HashMap<Integer, String> cellFormatMap) {
        String cellFormat = "";
        try {
            if (!cellFormatMap.isEmpty()) {
                cellFormat = cellFormatMap.get(cellIndex);
            }
        } catch (Exception e) {
            cellFormat = "";
        }
        return cellFormat;
    }

    public short getFontSize() {
        return fontSize;
    }

    public void setFontSize(short fontSize) {
        this.fontSize = fontSize;
    }

    public CellStyle getCellStyleCommon() {
        return cellStyleCommon;
    }

    public void setCellStyleCommon(CellStyle cellStyleCommon) {
        this.cellStyleCommon = cellStyleCommon;
    }

    public CellStyle getCellStyleHeader() {
        return cellStyleHeader;
    }

    public void setCellStyleHeader(CellStyle cellStyleHeader) {
        this.cellStyleHeader = cellStyleHeader;
    }

    public CellStyle getCellStyleNumeric() {
        return cellStyleNumeric;
    }

    public void setCellStyleNumeric(CellStyle cellStyleNumeric) {
        this.cellStyleNumeric = cellStyleNumeric;
    }

    public CellStyle getCellStyleDate() {
        return cellStyleDate;
    }

    public void setCellStyleDate(CellStyle cellStyleDate) {
        this.cellStyleDate = cellStyleDate;
    }

    public Font getFontStyleCommon() {
        return fontStyleCommon;
    }

    public void setFontStyleCommon(Font fontStyleCommon) {
        this.fontStyleCommon = fontStyleCommon;
    }

    public Font getFontStyleBolder() {
        return fontStyleBolder;
    }

    public void setFontStyleBolder(Font fontStyleBolder) {
        this.fontStyleBolder = fontStyleBolder;
    }

    public boolean getIsNeedStyle() {
        return isNeedStyle;
    }

    public void setIsNeedStyle(boolean isNeedStyle) {
        this.isNeedStyle = isNeedStyle;
    }
}

WebExcelWriteKit.java

package com.trendy.fw.common.excel;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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

import org.apache.poi.ss.usermodel.Workbook;

import com.trendy.fw.common.web.HttpResponseKit;

public class WebExcelWriteKit extends ExcelWriteKit {

    /**
     * 输出成文档
     * 
     * @param wb
     *            工作表
     * @param fileName
     *            文件名
     * @param response
     */
    public void output(Workbook wb, String fileName, HttpServletRequest request, HttpServletResponse response) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            wb.write(os);

            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);

            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            HttpResponseKit.setAttachmentFile(request, response, fileName);

            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(response.getOutputStream());

            byte[] buff = new byte[2048];
            int bytesRead;

            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (IOException e) {
            log.error("输出Excel出错:", e);
        } catch (Exception e) {
            log.error("输出Excel出错:", e);
        } finally {
            try {
                if (bis != null)
                    bis.close();
                if (bos != null)
                    bos.close();
                if (os != null)
                    os.close();
            } catch (Exception e) {
                log.error("输出Excel出错:{}", e);
            }
        }
    }

    /**
     * 从页面上输出Excel
     * 
     * @param bean
     *            WriteExcelBean
     * @param response
     */
    public void outputExcel(ExcelWriteBean bean, HttpServletRequest request, HttpServletResponse response) {
        Workbook wb = writeExcel(bean);
        String fileName = bean.getFileName() + "." + bean.getFileType();
        output(wb, fileName, request, response);
    }

    /**
     * 从页面上输出Excel多个sheet
     * 
     * @param bean
     *            WriteExcelBean
     * @param response
     */
    public void outputExcel(List<ExcelWriteBean> excelWriteList, HttpServletRequest request, HttpServletResponse response) {
        Workbook wb = writeExcel(excelWriteList);
        String fileName = excelWriteList.get(0).getFileName() + "." + excelWriteList.get(0).getFileType();
        output(wb, fileName, request, response);
    }
}

使用说明

将以上代码放到同一个excel包中，在对Excel进行读写的操作的时候，分别调用ExcelReadKit和ExcelWriteKit两个类中的方法，可以很方便地将Excel中的内容转换成ExcelReadResultBean对象，封装ExcelWriteBean对象将数据写入Excel文件中。

备注：以上代码使用需要依赖 apache poi jar包，记得先引入到项目当中

源码共享在https://github.com/xiongyouqiang/trendy_framework.git 大家有需要的话可以下载看看。