excel 导出，关于POI的使用经验

最近项目中用到导出的工作，也写了好多的excel工具，为了提高工作效率，思索着如何处理这方面的事情，为自己的今后更能很好的处理这方面的问题。

工具：poi和hutool工具。

poi的API官网：http://poi.apache.org/spreadsheet/quick-guide.html#Borders

hutool的首页：http://www.hutool.cn/

hutool工具做的很好，但是对应自定义的导出还得借助原生的poi力量来完成导出excel的工作。

maven依赖：

   <dependency>
            <groupId>com.xiaoleilu</groupId>
            <artifactId>hutool-all</artifactId>
            <version>3.2.3</version>
        </dependency>

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

借助hutool导出的第一个例子：

public static void main(String[] args) throws IOException {
        List<String> row1 = CollUtil.newArrayList("aa", "bb", "cc", "dd");
        List<String> row2 = CollUtil.newArrayList("aa1", "bb1", "cc1", "dd1");
        List<String> row3 = CollUtil.newArrayList("aa2", "bb2", "cc2", "dd2");
        List<String> row4 = CollUtil.newArrayList("aa3", "bb3", "cc3", "dd3");
        List<String> row5 = CollUtil.newArrayList("aa4", "bb4", "cc4", "dd4");
        List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);

        //通过工具类创建writer
        String path = "d:/writeTest.xlsx";
        ExcelWriter writer = ExcelUtil.getWriter(path);
        File file = new File(path);
        if(file.exists()) {
            file.delete();
        }
        //通过构造方法创建writer
        //ExcelWriter writer = new ExcelWriter("d:/writeTest.xls");
        //跳过当前行，既第一行，非必须，在此演示用
        writer.passCurrentRow();

        writer.merge(3,"测试标题");

        //一次性写出内容
        writer.write(rows);
        //关闭writer，释放内存
        writer.close();

        System.out.println("over");
    }

导出的样式如下：

但是有的时候，不是我们想要的样子，例如我们想导出如下的样子：

只合并后面2个单元格，第三个要有边框。这个就得结合POI了。

public static void main(String[] args) {
        List<String> row1 = CollUtil.newArrayList("aa", "bb", "cc", "dd");
        List<String> row2 = CollUtil.newArrayList("aa1", "bb1", "cc1", "dd1");
        List<String> row3 = CollUtil.newArrayList("aa2", "bb2", "cc2", "dd2");
        List<String> row4 = CollUtil.newArrayList("aa3", "bb3", "cc3", "dd3");
        List<String> row5 = CollUtil.newArrayList("aa4", "bb4", "cc4", "dd4");
        System.out.println(row1.size());
        List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);

        //通过工具类创建writer
        String path = "d:/writeTest.xlsx";
        ExcelWriter writer = ExcelUtil.getWriter(path);
        File file = new File(path);
        if(file.exists()) {
            file.delete();
        }
        //通过构造方法创建writer
        //ExcelWriter writer = new ExcelWriter("d:/writeTest.xls");

        //跳过当前行，既第一行，非必须，在此演示用
        writer.passCurrentRow();

        //合并单元格后的标题行，使用默认标题样式
        Workbook workbook = writer.getWorkbook();
        writer.merge(row1.size()-2, "测试标题");
        workbook.setSheetName(0, "这个是test");
        Sheet sheet = workbook.getSheetAt(0);
        //给某个单元格设置边框
        Row row = sheet.getRow(1);
        Cell cell = row.createCell(3);
        //cell.setCellValue("");

        // Style the cell with borders all around.
        CellStyle style = workbook.createCellStyle();

        //style.setBorderBottom(BorderStyle.HAIR);
        //style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        //style.setBorderLeft(BorderStyle.HAIR);
        //style.setLeftBorderColor(IndexedColors.GREEN.getIndex());

        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLUE.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cell.setCellStyle(style);

        //一次性写出内容
        writer.write(rows);
        //关闭writer，释放内存
        writer.close();

        System.out.println("over");
    }

关于导出excel的概念我们要清楚：

依次分别是：excel文件，Sheet，行，单元格。

单元格下面就是存放内容了！

demo：结合POI的合并单元格的例子：

public static void main(String[] args) {
        List<String> row1 = CollUtil.newArrayList("aa", "bb", "cc", "dd");
        List<String> row2 = CollUtil.newArrayList("aa1", "bb1", "cc1", "dd1");
        List<String> row3 = CollUtil.newArrayList("aa2", "bb2", "cc2", "dd2");
        List<String> row4 = CollUtil.newArrayList("aa3", "bb3", "cc3", "dd3");
        List<String> row5 = CollUtil.newArrayList("aa4", "bb4", "cc4", "dd4");
        System.out.println(row1.size());
        List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);

        //通过工具类创建writer
        String path = "d:/writeTest.xlsx";
        ExcelWriter writer = ExcelUtil.getWriter(path);
        File file = new File(path);
        if(file.exists()) {
            file.delete();
        }
        //通过构造方法创建writer
        //ExcelWriter writer = new ExcelWriter("d:/writeTest.xls");

        //跳过当前行，既第一行，非必须，在此演示用
        writer.passCurrentRow();
        writer.passCurrentRow();

        //合并单元格后的标题行，使用默认标题样式
        Workbook workbook = writer.getWorkbook();
        //writer.merge(row1.size()-2, "测试标题");

        workbook.setSheetName(0, "这个是test");
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.createRow(1);
        Cell cell = row.createCell(1);
        cell.setCellValue("women");
        sheet.addMergedRegionUnsafe(new CellRangeAddress(
                1, //first row (0-based)
                1, //last row  (0-based)
                1, //first column (0-based)
                2  //last column  (0-based)
        ));
       //给某个单元格设置边框
       /* Row row = sheet.getRow(1);
        Cell cell = row.createCell(1);
        cell.setCellValue("333");
*/
        //Style the cell with borders all around.
        CellStyle style = workbook.createCellStyle();

        //style.setBorderBottom(BorderStyle.HAIR);
        //style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREEN.getIndex());

        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLUE.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cell.setCellStyle(style);
        style.setAlignment(HorizontalAlignment.CENTER); // 居中
        Cell cell2 = row.createCell(2);
        cell2.setCellStyle(style);

        //一次性写出内容
        writer.write(rows);
        //关闭writer，释放内存
        writer.close();

        System.out.println("over");
    }