Excel读写工具类

比眉伴天荒 2022-06-08 13:52 224阅读 0赞

## 缘起 ##

在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][https_github.com_xiongyouqiang_trendy_framework.git] 大家有需要的话可以下载看看。

![这里写图片描述][SouthEast]

[https_github.com_xiongyouqiang_trendy_framework.git]: https://github.com/xiongyouqiang/trendy_framework.git
[SouthEast]: /images/20220608/98aa415e2bef468681f79f555bb94c2e.png