【工具类】ExcelUtils

这是笔者自己发现的比较好的Excel操作类。分享给大家。

在使用这个类之前,你需要导入以下依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

导出

package tool.excel;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 导出Excel工具类
 *
 * @param <T>
 * @author xxx
 */
public class ExportExcelUtils<T> {

    /**
     * 导出excel表格
     * 导出list的所有内容,使用属性名称作为列名称
     *
     * @param outputStream 文件输出流
     * @param list         内容
     */
    public boolean exportExcel(OutputStream outputStream,
                               List<T> list) {

        if (list == null || list.size() == 0) {
            System.out.println("您所查找的数据不存在!");
            return false;
        }
        T obj = list.get(0);
        Field[] fields = obj.getClass().getDeclaredFields();
        List<String> propertyList = new ArrayList<>();
        for (Field field : fields) {
            if ("serialVersionUID".equals(field.getName())) {
                continue;
            }
            propertyList.add(field.getName());
        }
        String[] propertys = new String[propertyList.size()];
        for (int i = 0; i < propertyList.size(); i++) {
            propertys[i] = propertyList.get(i);
        }
        return exportExcel(outputStream, "", propertys, propertys, list, "");
    }

    /**
     * 导出excel表格
     * 默认描述说明行高为11*256,标题行高为500,带有序号
     *
     * @param outputStream 文件输出流
     * @param sheetname    表名
     * @param titles       列名称
     * @param propertys    属性名称
     * @param list         内容
     * @param description  描述说明
     */
    public boolean exportExcel(OutputStream outputStream,
                               String sheetname,
                               String[] titles,
                               String[] propertys,
                               List<T> list,
                               String description) {
        return exportExcel(outputStream, sheetname, titles, propertys, list, description, (short) (11 * 256), (short) 500, true);
    }

    /**
     * 填写内容
     *
     * @param outputStream         文件输出流
     * @param sheetname            表名
     * @param titles               列名称
     * @param propertys            属性名称
     * @param list                 内容
     * @param description          描述说明
     * @param descriptionRowHeight 描述说明行高
     * @param titleRowHeight       标题行高
     * @param serialFlag           是否添加序号
     */
    public boolean exportExcel(OutputStream outputStream,
                               String sheetname,
                               String[] titles,
                               String[] propertys,
                               List<T> list,
                               String description,
                               short descriptionRowHeight,
                               short titleRowHeight,
                               boolean serialFlag) {
        try {
            if (StringUtils.isBlank(sheetname)) {
                sheetname = "sheet1";
            }
            if (titles == null || titles.length == 0) {
                System.out.println("请设置列名");
                return false;
            }
            if (propertys == null || propertys.length == 0) {
                System.out.println("请设置属性名");
                return false;
            }
            if (titles.length != propertys.length) {
                System.out.println("列名数量与属性名数量不一致");
                return false;
            }
            if (list == null || list.size() == 0) {
                System.out.println("您所查找的数据不存在!");
                return false;
            }
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet(sheetname);
            //设置默认行宽
            sheet.setDefaultColumnWidth(20);
            //列数
            int columnLength = titles.length;
            //行数
            int rowNum = 0;

            // 设置表描述
            if (StringUtils.isNotBlank(description)) {
                fillDescript(workbook, sheet, columnLength, rowNum++, description, descriptionRowHeight);
            }
            // 设置表头
            fillTitle(workbook, sheet, columnLength, rowNum++, titles, titleRowHeight, serialFlag);
            // 填充数据
            fillBody(workbook, sheet, columnLength, rowNum, propertys, list, serialFlag);

            // 将文件输出到客户端浏览器
            try {
                workbook.write(outputStream);
                outputStream.flush();
                outputStream.close();
                return true;
            } catch (Exception e) {
                System.out.println(sheetname + ":导出表格异常2");
                e.printStackTrace();
                return false;
            }
        } catch (Exception e) {
            System.out.println(sheetname + ":导出表格异常2");
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 填写描述说明
     *
     * @param workbook     文件
     * @param sheet        表
     * @param columnLength 总列数
     * @param rownum       起始行
     * @param description  描述说明
     * @param rowHeight    行高
     */
    private void fillDescript(XSSFWorkbook workbook, XSSFSheet sheet, int columnLength, int rownum, String description, short rowHeight) {
        XSSFCellStyle style = getDescriptStyle(workbook);
        //新增一行
        XSSFRow row = sheet.createRow(rownum);
        //行高,默认11*256
        row.setHeight(rowHeight);
        for (int i = 0; i < columnLength; i++) {
            //列索引从0开始
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString richTextString = new XSSFRichTextString("");
            if (i == 0) {
                //首行说明文字
                richTextString = new XSSFRichTextString(description);
            }
            cell.setCellValue(richTextString);
        }
        // 合并单元格
        // 起始行, 终止行, 起始列, 终止列
        CellRangeAddress cellRangeAddress = new CellRangeAddress(rownum, rownum, 0, columnLength);
        sheet.addMergedRegion(cellRangeAddress);
    }

    /**
     * 填写标题
     *
     * @param workbook     文件
     * @param sheet        表
     * @param columnLength 总列数
     * @param rownum       起始行
     * @param titles       列名称
     * @param rowHeight    行高
     * @param serialFlag   是否添加序号
     */
    private void fillTitle(XSSFWorkbook workbook, XSSFSheet sheet, int columnLength, int rownum, String[] titles, short rowHeight, boolean serialFlag) {
        XSSFCellStyle style = getTitleStyle(workbook);
        //新增一行
        XSSFRow row = sheet.createRow(rownum);
        //行高,默认500
        row.setHeight(rowHeight);
        XSSFCell cell;
        if (serialFlag) {
            //列索引从0开始
            cell = row.createCell(0);
            cell.setCellStyle(style);
            cell.setCellValue(new XSSFRichTextString("序号"));
        }
        for (int i = 0; i < columnLength; i++) {
            if (serialFlag) {
                //列索引从1开始
                cell = row.createCell(i + 1);
            } else {
                //列索引从1开始
                cell = row.createCell(i);
            }
            cell.setCellStyle(style);
            String textValue = "";
            if (StringUtils.isNotBlank(titles[i])) {
                textValue = titles[i];
            }
            cell.setCellValue(new XSSFRichTextString(textValue));
        }
        //冻结窗口
        sheet.createFreezePane(0, rownum + 1);
    }

    /**
     * 填写内容
     *
     * @param workbook     文件
     * @param sheet        表
     * @param columnLength 总列数
     * @param rownum       起始行
     * @param propertys    属性名称
     * @param list         内容
     * @param serialFlag   是否添加序号
     */
    private void fillBody(XSSFWorkbook workbook, XSSFSheet sheet, int columnLength, int rownum, String[] propertys, List<T> list, boolean serialFlag) {
        try {
            XSSFCellStyle style = getBodyStyle(workbook);
            for (int i = 0; i < list.size(); i++) {
                //新增一行
                XSSFRow row = sheet.createRow(rownum + i);
                XSSFCell cell;
                if (serialFlag) {
                    cell = row.createCell(0);
                    cell.setCellStyle(style);
                    cell.setCellValue(i + 1);
                }
                T obj = list.get(i);
                for (int j = 0; j < propertys.length; j++) {
                    if (serialFlag) {
                        //列索引从1开始
                        cell = row.createCell(j + 1);
                    } else {
                        //列索引从1开始
                        cell = row.createCell(j);
                    }
                    cell.setAsActiveCell();
                    cell.setCellStyle(style);
                    //属性
                    String propertyName = propertys[j];
                    if (StringUtils.isNotBlank(propertyName)) {
                        // 拼接get方法获取值
                        Object value = getFieldValueByName(propertyName, obj);
                        if (null != value) {
                            // 属性类型
                            String typeName = value.getClass().getTypeName();
                            switch (typeName) {
                                case "java.lang.String":
                                    cell.setCellValue(new XSSFRichTextString(value.toString()));
                                    break;
                                case "java.util.Date":
                                    cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) value));
                                    break;
                                case "java.lang.Integer":
                                    cell.setCellValue((Integer) value);
                                    break;
                                case "java.lang.Float":
                                    cell.setCellValue((Float) value);
                                    break;
                                case "java.lang.Double":
                                    cell.setCellValue((Double) value);
                                    break;
                                case "java.util.List":
                                    cell.setCellValue("");
                                    break;
                                default:
                                    cell.setCellValue(value.toString());
                                    break;
                            }
                        } else {
                            cell.setCellValue("");
                        }
                    } else {
                        cell.setCellValue("");
                    }
                }
            }
        } catch (Exception ex) {
            System.out.println("设置表内容异常");
            ex.printStackTrace();
        }
    }

    /**
     * 根据属性名获取属性值
     */
    public Object getFieldValueByName(String fieldName, Object o) throws Exception {
        String firstLetter = fieldName.substring(0, 1).toUpperCase();
        String getter = "get" + firstLetter + fieldName.substring(1);
        Method method = o.getClass().getMethod(getter, new Class[]{});
        return method.invoke(o, new Object[]{});
    }

    /**
     * 设置描述说明样式
     *
     * @param workbook workbook
     * @return XSSFCellStyle
     */
    private XSSFCellStyle getDescriptStyle(XSSFWorkbook workbook) {
        //标题样式
        XSSFCellStyle style = workbook.createCellStyle();
        //边框
        setBorderStyle(style);
        //style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        //style.setVerticalAlignment(VerticalAlignment.BOTTOM);//垂直居中
        //自动换行
        style.setWrapText(true);
        //字体
        Font font = workbook.createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 9);
        style.setFont(font);
        return style;
    }

    /**
     * 设置标题样式
     *
     * @param workbook workbook
     * @return XSSFCellStyle
     */
    private XSSFCellStyle getTitleStyle(XSSFWorkbook workbook) {
        //标题样式
        XSSFCellStyle style = workbook.createCellStyle();
        //居中
        setCenterStyle(style);
        //边框
        setBorderStyle(style);
        //背景颜色为灰色
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(new XSSFColor(new java.awt.Color(150, 150, 150)));
        //字体
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 9);
        style.setFont(font);
        return style;
    }

    /**
     * 设置内容样式
     *
     * @param workbook workbook
     * @return XSSFCellStyle
     */
    private XSSFCellStyle getBodyStyle(XSSFWorkbook workbook) {
        //标题样式
        XSSFCellStyle style = workbook.createCellStyle();
        //居中
        setCenterStyle(style);
        //边框
        setBorderStyle(style);
        //字体
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 9);
        style.setFont(font);
        return style;
    }

    /**
     * 设置边框样式
     *
     * @param style 风格
     */
    private void setBorderStyle(XSSFCellStyle style) {
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //上边框
        style.setBorderTop(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
    }

    /**
     * 设置居中样式
     *
     * @param style 风格
     */
    private void setCenterStyle(XSSFCellStyle style) {
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
    }
}

导入

package tool.excel;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

/**
 * 导入Excel工具类
 *
 * @param
 */
@Slf4j
public class ImportExcelUtils<T> {

    /**
     * HSSFWorkbook:是操作Excel2003以前(包括2003)的版本,扩展名是.xls;
     * XSSFWorkbook:是操作Excel2007的版本,扩展名是.xlsx;
     * 当数据量超出65536条后,在使用HSSFWorkbook或XSSFWorkbook,程序会报OutOfMemoryError:Javaheap space;内存溢出错误。这时应该用SXSSFworkbook
     *
     * @param filename        文件名
     * @param inputStream     输入流
     * @param titles          列名称
     * @param descriptionFlag 首行是否为描述
     * @return boolean
     */
    public String[][] ImportExcel(String filename, InputStream inputStream,
                               String[] titles,
                               boolean descriptionFlag) {
        try {
            // 验证文件名是否合格
            if (!validateExcel(filename)) {
                System.out.println("文件名不是excel格式");
                return null;
            }
            Workbook workbook = null;
            // 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(filename)) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                workbook = new HSSFWorkbook(inputStream);
            }
            if (workbook.getNumberOfSheets() == 0) {
                System.out.println("这是一个空文件,请编辑后重新上传");
                return null;
            }
            Sheet sheet = workbook.getSheetAt(0);
            //总行数
            int totalRows = sheet.getLastRowNum();
            if (totalRows == 0) {
                System.out.println("第一个sheet为空,请编辑后重新上传");
                return null;
            }
            //总列数
            int totalColumn = sheet.getRow(0).getPhysicalNumberOfCells();
            if (totalColumn == 0) {
                System.out.println("第一个sheet为空,请编辑后重新上传");
                return null;
            }
            if (totalColumn != titles.length) {
                System.out.println("第一个sheet列名数量与要求的不一致");
                return null;
            }
            return ImportExcel(sheet, totalRows, totalColumn, titles, descriptionFlag);
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("导入Excel发送异常");
            return null;
        }
    }

    /**
     * @param sheet           第一个表
     * @param totalRows       总行数
     * @param totalColumn     总列数
     * @param titles          列名称
     * @param descriptionFlag 首行是否为描述
     * @return
     */
    public String[][] ImportExcel(Sheet sheet, int totalRows, int totalColumn,
                               String[] titles, boolean descriptionFlag) {
        //起始行
        int initeRow = descriptionFlag ? 1 : 0;
        String[][] tableData = new String[totalRows - 1][totalColumn];
        for (int r = initeRow; r <= totalRows; r++) {
            Row row = sheet.getRow(r);
            String[] rowData = new String[totalColumn];
            for (int c = 0; c < totalColumn; c++) {
                Cell cell = row.getCell(c);
                CellType type = cell.getCellTypeEnum();
                String cellVal;
                switch (type) {
                    case STRING:
                        cellVal = cell.getStringCellValue().trim();
                        break;
                    case NUMERIC:
                        double val = cell.getNumericCellValue();
                        cellVal = String.valueOf(val);
                        double valDiv = val % 1.0;
                        if (valDiv == 0) {
                            cellVal = String.valueOf((long) val);
                        }
                        break;
                    case BOOLEAN:
                        cellVal = String.valueOf(cell.getBooleanCellValue());
                        break;
                    default:
                        cellVal = "";
                        break;
                }
                if (r == initeRow) {
                    String titleName = titles[c].trim();
                    if (!cellVal.equals(titleName)) {
                        System.out.printf("第%s列不是%s,请下载最新的用户模板!%n", c, titleName);
                        return null;

                    }
                } else {
                    rowData[c] = cellVal;
                }
            }
            if (r != initeRow) {
                tableData[r - initeRow - 1] = rowData;
            }
        }
        return tableData;
    }

    /**
     *
     *
     * @param filePath 文件路径
     * @return 验证EXCEL文件结果
     */
    private boolean validateExcel(String filePath) {
        return isExcel2003(filePath) || isExcel2007(filePath);
    }

    /**
     * @param filePath 文件路径
     * @return 是否是2003的excel,返回true是2003
     */
    private static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * @param filePath 文件路径
     * @return 是否是2007的excel,返回true是2007
     */
    private static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    private Map<String, Object> getExcelInfo(MultipartFile mFile) {
        Map<String, Object> map = new HashMap<String, Object>();
        // 获取文件名
        String fileName = mFile.getOriginalFilename();
        try {
            // 验证文件名是否合格
            if (!validateExcel(fileName)) {
                return null;
            }
            // 根据文件名判断文件是2003版本还是2007版本
            boolean isExcel2003 = true;
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            map = createExcel(mFile.getInputStream(), isExcel2003);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

    private Map<String, Object> createExcel(InputStream is, boolean isExcel2003) {
        Map<String, Object> map = new HashMap<String, Object>();
        try {
            Workbook wb = null;

            if (isExcel2003) {
                // 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {
                // 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            map.put("wb", wb);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return map;
    }
}
posted @ 2020-11-19 14:13  朱李洛克  阅读(1418)  评论(0编辑  收藏  举报
// 侧边栏目录 // https://blog-static.cnblogs.com/files/douzujun/marvin.nav.my1502.css