SpringBoot-导出excel

评论

步骤

  1. 引入依赖

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    <!--excel-->
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>4.1.2</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.2</version>
    </dependency>

  2. 编写工具类

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    package com.famesmart.facearcface.uitls;

    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    import java.io.File;
    import java.io.FileOutputStream;
    import java.util.List;
    import java.util.Map;
    import java.util.Set;

    /**
     * @Author Yiqing Zhang
     * @Date 2021-01-08 5:37 p.m.
     * @Version 1.0
     */
    public class ExcelUtil {
        public static void writeExcel(List<Map<String,Object>> dataList, String filePath) throws Exception {
            //创建文件
            File path = new File(filePath);
            if (!path.exists()) {
                path.mkdirs();
            }
            String fileName = System.currentTimeMillis() + ".xlsx";
            File file = new File(path, fileName);

            //创建一个excel对象
            Workbook workbook = new XSSFWorkbook();

            //创建第一个工作簿
            Sheet sheet = workbook.createSheet("dbName");

            //将数据库字段（map的key）写入第一行
            Map<String, Object> firstRowMap = dataList.get(0);
            Set<String> keySet = firstRowMap.keySet();
            Object[] keyArr = keySet.toArray();
            Row row0 = sheet.createRow(0);
            for (int i = 0; i < keyArr.length; i++) {
                //创建单元格并写入数据
                Cell cell = row0.createCell(i);
                cell.setCellValue(keyArr[i].toString());
            }

            //将数据写入其他行
            for (int i = 0; i < dataList.size(); i++) {
                //控制有多少行
                Map<String, Object> rowMap = dataList.get(i);
                Row row = sheet.createRow(i + 1);
                for (int j = 0; j < rowMap.size(); j++) {
                    Cell cell = row.createCell(j);
                    Object o = rowMap.get((String) keyArr[j]);
                    //除掉空值,也可以写入对应类型,此处直接写入string
                    cell.setCellValue(null==o?"":o.toString());
                }
            }

            //输出文件
            FileOutputStream fos = new FileOutputStream(file);
            workbook.write(fos);
        }
    }

YiqingWeb Developer

一位打工人