Java使用Apache POI导出excel

系列文章目录

一、Java使用Apache POI导出excel
二、Apache POI 操作Excel常用方法
三、Apache poi 拆分单元格并赋值
四、使用easypoi模板方法导出excel
五、Apache poi给excel单元格添加下拉框或数据验证

---

---

一、效果展示

二、引入依赖

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

三、Java代码

    public static void main(String[] args) {
        List<LinkedHashMap<String, Object>> mapArrayList = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            LinkedHashMap<String, Object> map = new LinkedHashMap<>();
            map.put("name", i);
            map.put("age", i);
            map.put("class", i + "aaaaaaaaaaaaaaaaaaa");
            mapArrayList.add(map);
        }

        List<String> head = new ArrayList<>();
        head.add("姓名");
        head.add("年龄");
        head.add("班级");

        try {
            String path = ExcelUtil.getInstance().exportExcel(mapArrayList, head, "D:\\桌面\\", "excel导出");
            String path2 = ExcelUtil.getInstance().exportExcel2(mapArrayList, head, "D:\\桌面\\", "excel导出");
            System.out.println(path);
            System.out.println(path2);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

---

注：
HSSF：是操作Excel97-2003版本，扩展名为.xls。

XSSF：是操作Excel2007版本开始，扩展名为.xlsx。

public class ExcelUtil {

    private static ExcelUtil instance = new ExcelUtil();

    private ExcelUtil(){}

    public static ExcelUtil getInstance(){
        return instance;
    }

    public String exportExcel (List<LinkedHashMap<String, Object>> list, List<String> head,
                               String excelPath, String filename) throws Exception {

        Map<String, Object> map = list.get(0);
        Set<String> stringSet = map.keySet();
        ArrayList<String> field = new ArrayList<>(stringSet);

        HSSFWorkbook wb;
        // 定义一个新的工作簿
        wb = new HSSFWorkbook();

        // 创建一个Sheet页
        HSSFSheet sheet = wb.createSheet();
        sheet.createFreezePane( 0, 1, 0, 1 );
        // 设置行高
        sheet.setDefaultRowHeight((short) (2 * 256));
        // 设置列宽
        for (int i = 0; i < head.size(); i++) {
            sheet.setColumnWidth(i, 4000);
        }
        // 设置单元格字体样式
        HSSFFont font = wb.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) 16);
        // 创建单元格文字居中样式并设置标题单元格居中
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle cellStyle2 = wb.createCellStyle();
        cellStyle2.setAlignment(HorizontalAlignment.CENTER);
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 表头设置冻结与北京颜色
        cellStyle2.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 表头
        HSSFRow row1 = sheet.createRow(0);
        for (int i = 0; i < head.size(); i++) {
            HSSFCell cell = row1.createCell(i);
            cell.setCellValue(head.get(i));
            cell.setCellStyle(cellStyle2);
        }
        // 写入数据
        for (int j = 0; j < list.size(); j++) {
            HSSFRow row = sheet.createRow(j + 1);
            for (int i = 0; i < field.size(); i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(list.get(j).get(field.get(i)).toString());
                cell.setCellStyle(cellStyle);
            }
        }


        String path = excelPath + filename + ".xls";
        File savefile = new File(excelPath);
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        File file = new File(path);
        FileOutputStream fileOutputStream = new FileOutputStream(file);
        wb.write(fileOutputStream);
        wb.close();
        fileOutputStream.close();
        return path;

    }

	public String exportExcel2 (List<LinkedHashMap<String, Object>> list, List<String> head,
                               String excelPath, String filename) throws Exception {

        Map<String, Object> map = list.get(0);
        Set<String> stringSet = map.keySet();
        ArrayList<String> field = new ArrayList<>(stringSet);

        // 定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();

        // 创建一个Sheet页
        XSSFSheet sheet = wb.createSheet();
        sheet.createFreezePane( 0, 1, 0, 1 );
        // 设置行高
        sheet.setDefaultRowHeight((short) (2 * 256));
        // 设置列宽
        for (int i = 0; i < head.size(); i++) {
            sheet.setColumnWidth(i, 4000);
        }
        // 设置单元格字体样式
        XSSFFont font = wb.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) 16);
        // 创建单元格文字居中样式并设置标题单元格居中
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        XSSFCellStyle cellStyle2 = wb.createCellStyle();
        cellStyle2.setAlignment(HorizontalAlignment.CENTER);
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 表头设置冻结与北京颜色
        cellStyle2.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 表头
        XSSFRow row1 = sheet.createRow(0);
        for (int i = 0; i < head.size(); i++) {
            XSSFCell cell = row1.createCell(i);
            cell.setCellValue(head.get(i));
            cell.setCellStyle(cellStyle2);
        }
        // 写入数据
        for (int j = 0; j < list.size(); j++) {
            XSSFRow row = sheet.createRow(j + 1);
            for (int i = 0; i < field.size(); i++) {
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(list.get(j).get(field.get(i)).toString());
                cell.setCellStyle(cellStyle);
            }
        }


        String path = excelPath + filename + ".xlsx";
        File savefile = new File(excelPath);
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        File file = new File(path);
        FileOutputStream fileOutputStream = new FileOutputStream(file);
        wb.write(fileOutputStream);
        wb.close();
        fileOutputStream.close();
        return path;

    }

}

---