POI 操作excel

展示效果

maven 依赖

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

代码

package com.ruoyi.poitest;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

public class Test {
    public static void main(String[] args) throws IOException {
        //测试数据
        List<Map<String,Object>> dataList = new ArrayList<>();
        //使用 LinkedHashMap 为了数据能够有序对应表头
        Map<String,Object> m1 = new LinkedHashMap<>();
        m1.put("dept","开发");
        m1.put("name","张三");
        m1.put("money","5000");
        Map<String,Object> m2 = new LinkedHashMap<>();
        m2.put("dept","测试");
        m2.put("name","李四");
        m2.put("money","6000");
        Map<String,Object> m3 = new LinkedHashMap<>();
        m3.put("dept","运维");
        m3.put("name","王五");
        m3.put("money","7000");
        dataList.add(m1);
        dataList.add(m2);
        dataList.add(m3);

        //创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();


        //定义字体
        XSSFFont font = workbook.createFont();
        font.setColor(IndexedColors.YELLOW.getIndex());//字体颜色
        font.setFontHeight(20);//字体高度（大小）
        font.setBold(true);//是否加粗字体 true 加 false 不加



        //定义样式
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        //背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//设置前景颜色
        //cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());//设置背景颜色（无效）
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//这个要加上，不然设置背景颜色无效

        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框

        cellStyle.setFont(font);//设置字体



        //sheet 分页
        String[] sheetNames = {"page1","page2","page3","page4","page5"};
        for(int i = 0;i<sheetNames.length;i++){
            workbook.createSheet(sheetNames[i]);//创建多个分页
        }

        //向excel 内写入数据其实是需要向指定的分页内写入，所以当前选择 page2 分页进行操作。
        XSSFSheet page2 = workbook.getSheet("page2");

        //分页的第一行（用来设置表头）
        XSSFRow row = page2.createRow(0);
        String[] tableTitle = {"部门","姓名","薪资"};//表头内容
        for(int i = 0;i<tableTitle.length;i++){
//            row.createCell(i).setCellStyle(cellStyle);
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(tableTitle[i]);

            cell.setCellStyle(cellStyle);
        }

        //向表格内写入数据
        for(int j = 0;j<dataList.size();j++){
            Map<String, Object> dataMap = dataList.get(j);
            //从第二行开始写
            XSSFRow row1 = page2.createRow(j + 1);
            int lie = 0;
            for(Map.Entry entry : dataMap.entrySet()){
                XSSFCell cell = row1.createCell(lie);
                cell.setCellValue(String.valueOf(entry.getValue()));
                lie++;
            }

        }

        //输出流（指定生成的excel 存放位置）
        FileOutputStream outputStream = new FileOutputStream(new File("C:\\Users\\pc\\Desktop\\test1.xlsx"));
        //将创建的表格写入
        workbook.write(outputStream);

        outputStream.close();//关闭流

        System.out.println("文件创建成功！");
    }
}