Spring Boot项目中读取创建Excel文件

maven依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>


    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <!--解析excel-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>RELEASE</version>
    </dependency>
    <!--  file文件转化为mutifile      -->
    <dependency>
        <groupId>org.apache.httpcomponents</groupId>
        <artifactId>httpcore</artifactId>
        <version>4.4.9</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-test</artifactId>
        <version>5.1.6.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-web</artifactId>
        <version>5.2.9.RELEASE</version>
    </dependency>
</dependencies>

读取Excel文件

package com.example.test;

import org.apache.http.entity.ContentType;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bouncycastle.util.test.Test;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

public class ExcelReadTest {
    /**
     *将File文件转化为MultipartFile文件
     * @param url
     * @return
     * @throws IOException
     */
    public MultipartFile fileToMultipartFile(String url) throws IOException {
        File file = new File(url);
        FileInputStream fileInputStream = new FileInputStream(file);
        MultipartFile multipartFile = new MockMultipartFile(file.getName(), file.getName(),
                ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream);
        return multipartFile;
    }

    /**
     * 将文件转化为可操作的类型
     * @param multipartFile
     * @return
     * @throws IOException
     */
    public Workbook getWorkFile(MultipartFile multipartFile) throws IOException {
        Workbook workbook = null;
        //获取文件的类型
        String type = multipartFile.getName().substring(multipartFile.getName().lastIndexOf(".")+1);
        //获取文件字节输入流
        InputStream in = multipartFile.getInputStream();//获取文件输入流
        if ("xls".equals(type)) {
            workbook = new HSSFWorkbook(in);
        } else if ("xlsx".equals(type)) {
            workbook = new XSSFWorkbook(in);
        }
        return workbook;
    }

    /**
     * 遍历读取excel的每一个单元格
     * @param workbook
     */
    public void readFile(Workbook workbook) {
        //遍历sheet
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
            //得到单个sheet
            Sheet sheet = workbook.getSheetAt(numSheet);
            if (sheet == null) {
                continue;
            }
            //得到单个sheet的行数
            int rowCount = sheet.getLastRowNum();
            //从第二行开始，遍历Sheet的每一行(第一行一般是标题，所以不遍历)
            for (int rowNum = 0; rowNum < rowCount; rowNum++) {
                try {
                    //得到单行数据
                    Row row = sheet.getRow(rowNum);
                    if (row != null) {
                        int cellCount = row.getLastCellNum();
                        for (int cellNum = 0;cellNum<cellCount;cellNum++){
                            Cell cell = row.getCell(cellNum);
                            String cellValue = "";
                            if (cell!=null){
                                cell.setCellType(CellType.STRING);//提前设置String类型，防止数字后加.0
                                cellValue = cell.getStringCellValue();
                                //如果上面的setCellType(CellType.STRING)过期，可以先将Cell转化为CellBase，然后再定义类型
//                                CellBase cellBase = (CellBase) cell;
//                                cellBase.setCellType(CellType.STRING);
//                                cellValue = cellBase.getStringCellValue();
                            }
                            System.out.print(cellValue + "\t");
                        }
                        System.out.println("");
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public void excelUtil(String url) throws IOException {
        MultipartFile multipartFile = fileToMultipartFile(url);
        Workbook workbook = getWorkFile(multipartFile);
        readFile(workbook);
    }

    public static void main(String[] args) throws IOException {
//        File file = new File("G:\\IDEAWorkspeace\\test\\excle\\src\\main\\resources\\excel\\试点团队配置模板.xls");
        String file = "G:\\IDEAWorkspeace\\test\\excle\\src\\main\\resources\\excel\\试点团队配置模板.xls";
        ExcelReadTest test = new ExcelReadTest();
        test.excelUtil(file);
    }
}

创建Excel文件

package com.example.test;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

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

public class ExcelWriteTest {
    public void createExcel(){
        //1、创建workbook
        HSSFWorkbook wb = new HSSFWorkbook();

        //2、创建sheet
        HSSFSheet sheet = wb.createSheet("团队信息");

        ///3、创建第一行标题
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);  //居中

        List<String> title = new ArrayList<>();
        title.add("团队代码");
        title.add("团队名称");
        //4、插入标题的值
        for (int i =0; i < title.size(); i++){
            Cell  cell = row.createCell(i);
            cell.setCellValue(title.get(i));
            cell.setCellStyle(style);
        }
//        5、创建单元格的值
        List<Map> data = new ArrayList<>();
        Map map = new HashMap();
        map.put("code", 1);
        map.put("name", "一团队");
        data.add(map);
        //4、插入具体的单元格值
        for (int i = 0; i < data.size(); i++) {
            //从第二行开始插入数据
            row = sheet.createRow(i+1);
//            获取数据
            Map cellData = data.get(i);
            Set<String> set = cellData.keySet();
            int j = 0;
            for (String key : set){
                row.createCell(j).setCellValue(String.valueOf(cellData.get(key)));
                j++;
            }
        }
        //6、将生成excel文件保存到指定路径下
        try {
            File file = new File("G:\\IDEAWorkspeace\\test\\excle\\src\\main\\resources\\excel\\write.xls");
           if (file.exists()){
               file.delete();
           }
            FileOutputStream fout = new FileOutputStream(file);
            wb.write(fout);
            fout.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("文件已成功创建");
    }

    public static void main(String[] args) {
        ExcelWriteTest writeTest = new ExcelWriteTest();
        writeTest.createExcel();
    }
}