java封装实现Excel建表读写操作

2018-09-01 05:39:33来源：博客园阅读 ()

对 Excel 进行读写操作是生产环境下常见的业务，网上搜索的实现方式都是基于POI和JXL第三方框架，但都不是很全面。小编由于这两天刚好需要用到，于是就参考手写了一个封装操作工具，基本涵盖了Excel表（分有表头和无表头）的创建，并对它们进行读写操作。为方便大家，有需要者可以点击文后点解下载直接使用哦，当然也可以根据自己需求举一反三自己定制，相信对于聪明的你也不是什么难事。话不多说，直接贴源码

pom.xml 文件：

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.0</version>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>1.8.0-beta2</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.8.0-beta2</version>
    </dependency>
  </dependencies>

建表工具类：ExcelBuider.java

 /**
         * 建表工具类
         * @author Sherman
         * email:1253950375@qq.com
         * created in 2018/8/24
         */
        @Slf4j
        public class ExcelBuilder {

            private static HSSFSheet sheet;
            private static HSSFWorkbook wb;
            private static boolean hasHeader;

            /**
             * 初始化
             * @param excellName 表名
             */
            public ExcelBuilder(String excellName) {
                wb = new HSSFWorkbook();
                sheet = wb.createSheet(excellName);
            }

            /**
             *  设置表头，装配表头数据
             * @param value 字符串数组，用来作为表头的值
             *
             */
            public ExcelBuilder header(String... value) {
                if (value != null && value.length != 0) {
                    //设置表头样式
                    HSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFont(font("黑体", true, 12));
                    HSSFRow row = sheet.createRow(0);
                    for (int i = 0; i < value.length; i++) {
                        HSSFCell cell = row.createCell(i);
                        cell.setCellValue(value[i]);
                        cell.setCellStyle(cellStyle);
                    }
                    hasHeader = true;
                }
                return this;
    }

    /**
     * excel 表内容装配
     * @param content 待装配表格内容的二维数组
     * @return
     */
    public ExcelBuilder content(List<List<Object>> content) {
        if (content != null && !content.isEmpty()) {
            int index;
            for (int i = 0; i < content.size(); i++) {
                index = hasHeader == false ? i : i + 1;
                HSSFRow row = sheet.createRow(index);
                for (int j = 0; j < content.get(i).size(); j++) {
                    String r = "";
                    Object value = content.get(i).get(j);
                    //根据数据类型装配
                    if (value instanceof String) {
                        r = (String) value;
                    } else if (value instanceof Number) {
                        r = String.valueOf(value);
                    } else if (value instanceof BigDecimal) {
                        r = String.valueOf(value);
                    } else {
                        if (!(value instanceof Date) && !(value instanceof Timestamp)) {
                            if (!(value instanceof ZonedDateTime) && !(value instanceof LocalDateTime)) {
                                if (value instanceof Enum) {
                                    r = ((Enum) value).name();
                                } else if (value != null) {

                                   log.info("Error of create row, Unknow field type: " + value.getClass().getName());
                                }
                            } else {
                                DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                                r = formatter.format((TemporalAccessor) value);
                            }
                        } else {
                            DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                            r = sdf.format(value);
                        }
                    }

                    row.createCell(j).setCellValue(r);
                }
            }
        }
        return this;
    }

    /**
     * 自动调整列宽大小
     */
    public ExcelBuilder autoColumnWidth() {
        for (int j = 0; j < sheet.getRow(0).getLastCellNum(); j++) {
            int maxLength = 0;
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                String value = sheet.getRow(i).getCell(j).getStringCellValue();
                int length = 0;
                if (value != null) {
                    length = value.getBytes().length;
                }
                if (length > maxLength) {
                    maxLength = length;
                }
            }
            sheet.setColumnWidth(j, maxLength > 30 ? (30 * 256 + 186) : (maxLength * 256 + 186));
        }
        return this;
    }

    /**
     * 实例化
     * @param hasHeader 是否有表头
     * @return Excel表格
     */
    public AbstractExcel build(Boolean hasHeader) {
        return hasHeader ? new HeaderExcel(sheet) : new NoHeaderExcel(sheet);
    }

    /**
     *
     * @param fontName 字体名字
     * @param isBold  是否粗体
     * @param fontSize 字体大小
     * @return 字体
     */
    private HSSFFont font(String fontName, boolean isBold, int fontSize) {
        HSSFFont font = wb.createFont();
        if (fontName != null) font.setFontName(fontName);
        else font.setFontName("黑体");
        font.setBold(isBold);
        font.setFontHeightInPoints((short) fontSize);
        return font;
    }

}

excel的抽象父类：

/**
 * @author Sherman
 * created in 2018/8/24
 */

public abstract class AbstractExcel {
    private final HSSFSheet sheet;

    public AbstractExcel() {
        HSSFWorkbook wb = new HSSFWorkbook();
        sheet = wb.createSheet();
    }

    public AbstractExcel(String sheetName){
        HSSFWorkbook wb = new HSSFWorkbook();
        sheet = wb.createSheet(sheetName);
    }

    public AbstractExcel(HSSFSheet sheet) {
        this.sheet = sheet;
    }



    public abstract List<Map<String, String>> getPayload();


    public void write(OutputStream op) throws IOException {
        sheet.getWorkbook().write(op);
        sheet.getWorkbook().close();
    }

    public String getStringFormatCellValue(HSSFCell cell) {
        String cellVal = "";
        DecimalFormat df = new DecimalFormat("#");
        switch (cell.getCellTypeEnum()) {
            case STRING:
                cellVal = cell.getStringCellValue();
                break;
            case NUMERIC:
                String dataFormat = cell.getCellStyle().getDataFormatString();
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellVal = df.format(cell.getDateCellValue());
                } else if ("@".equals(dataFormat)) {
                    cellVal = df.format(cell.getNumericCellValue());
                } else {
                    cellVal = String.valueOf(cell.getNumericCellValue());
                    df = new DecimalFormat("#.#########");
                    cellVal = df.format(Double.valueOf(cellVal));
                }
                break;
            case BOOLEAN:
                cellVal = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                cellVal = String.valueOf(cell.getCellFormula());
                break;
            default:
                cellVal = "";
        }
        return cellVal;
    }


}

有表头实现类

/**
 * @author Sherman
 * created in 2018/8/24
 */

public class HeaderExcel extends AbstractExcel {
    private final static boolean hasHeader = true;
    private final HSSFSheet sheet;

    public HeaderExcel(HSSFSheet sheet) {
        super(sheet);
        this.sheet = sheet;
    }

    public HeaderExcel(String sheetName, String excelPath) {
        HSSFWorkbook wb = null;
        try {
            wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(excelPath)));
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = sheetName == null || sheetName.isEmpty() ? wb.getSheetAt(0) : wb.getSheet(sheetName);
    }

    @Override
    public List<Map<String, String>> getPayload() {
        List<Map<String, String>> payLoad = new ArrayList<>();
        HSSFRow headRow = sheet.getRow(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            HSSFRow currentRow = sheet.getRow(i);
            Map<String, String> map = new HashMap<>();
            for (int j = 0; j < sheet.getRow(i).getLastCellNum(); j++) {
                map.put(getStringFormatCellValue(headRow.getCell(j)), getStringFormatCellValue(currentRow.getCell(j)));
            }
            payLoad.add(map);
        }
        return payLoad;
    }


}

无表头实现类

/**
 * @author Sherman
 * created in 2018/8/24
 */

public class NoHeaderExcel extends AbstractExcel {
    private final static boolean hasHeader = false;
    private HSSFSheet sheet;

    public NoHeaderExcel(HSSFSheet sheet) {
        super(sheet);
        this.sheet = sheet;
    }

    public NoHeaderExcel(String sheetName, String excelPath) {
        HSSFWorkbook wb = null;
        try {
            wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(excelPath)));
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = sheetName == null || sheetName.isEmpty() ? wb.getSheetAt(0) : wb.getSheet(sheetName);
    }


    @Override
    public List<Map<String, String>> getPayload() {
        List<Map<String, String>> payLoad = new ArrayList<>();
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            HSSFRow currentRow = sheet.getRow(i);
            Map<String, String> map = new HashMap<>();
            for (int j = 0; j <= sheet.getRow(i).getLastCellNum(); j++) {
                map.put(String.valueOf(j), getStringFormatCellValue(currentRow.getCell(j)));
            }
            payLoad.add(map);
        }
        return payLoad;
    }


}

测试工具类：

/**
 * Unit test for simple App.
 */
public class AppTest 
{
    /**
     * 测试建表，写表操作
     */
    @Test
    public void testExportExcel()
    {
        //测试数据
     String[] headers = new String[]{"A","B","C","D","E"};
         List<List<Object>> valueList = new LinkedList<>();
        for (char i = 'A'; i <= 'E' ; i++) {
            List<Object> rowList = new LinkedList<>();
            for (int j = 0; j <= 4; j++) {
                rowList.add(i+String.valueOf(j));
            }
            valueList.add(rowList);
        }

    AbstractExcel excel = new ExcelBuilder("报名表")
            .header(headers)
            .content(valueList)
            .autoColumnWidth()
            .build(true);

        try {
            File file = new File("E:\\excel\\test.xls");
            FileOutputStream op = new FileOutputStream(file);
            excel.write(op);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 测试读取表数据操作
     */
    @Test
    public void testImportExcel(){
        AbstractExcel excel = new HeaderExcel(null,"E:/excel/test.xls");
       List<Map<String,String>> values = excel.getPayload();
       values.forEach(stringStringMap -> {
           stringStringMap.entrySet().forEach(stringStringEntry -> {
               System.out.println(stringStringEntry.getKey()+"---->"+stringStringEntry.getValue());
           });

       });
    }

}