SpringBoot写excel文件

摘要：读取txt文本文件数据并写如Excel中
1、添加依赖

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

引入上方依赖包，如果依然报错，加入下方依赖

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

2、功能代码

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author liting
 * @date 2020/03/01  21:36
 */
public class FileController {

    private static final Logger LOGGER = LoggerFactory.getLogger(FileController.class);

    private static String sourPath = "/Users/liting/Desktop/虚拟机/20200302/UNICOM_TELECOM_INFO.txt";
    private static String destPath = "/Users/liting/Desktop/虚拟机/20200304";

    public static void main(String[] args) {
        try {
            List<Map<String, String>> mapList = getFile();
            String excelFile = createExcelFile(mapList);
            LOGGER.info("文件路径:{}", excelFile);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //写excel文件
    public static String createExcelFile(List<?> list) {
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        //创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet();
        xssfWorkbook.setSheetName(0, "局数据表");
        //创建表头
        XSSFRow head = sheet.createRow(0);
        String[] heads = {"操作类型", "归属地址", "号段", "局向", "生效时间", "失效时间", "操作时间"};
        for (int i = 0; i < 7; i++) {
            XSSFCell cell = head.createCell(i);
            cell.setCellValue(heads[i]);
        }
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-mm-dd HH:mm:ss");
        if (!list.isEmpty()) {
            for (int i = 1; i <= list.size(); i++) {
                Map<Object, String> map = (Map) list.get(i - 1);
                //创建行,从第二行开始，所以for循环的i从1开始取
                XSSFRow row = sheet.createRow(i);
                //创建单元格，并填充数据
                XSSFCell cell = row.createCell(0);
                cell.setCellValue(map.get("操作类型"));
                cell = row.createCell(1);
                cell.setCellValue(map.get("归属地址"));
                cell = row.createCell(2);
                cell.setCellValue(map.get("号段"));
                cell = row.createCell(3);
                cell.setCellValue(map.get("局向"));
                cell = row.createCell(4);
                cell.setCellValue(map.get("生效时间"));
                cell = row.createCell(5);
                cell.setCellValue(map.get("失效时间"));
                cell = row.createCell(6);
                cell.setCellValue(map.get("操作时间"));
            }
        }
        //创建临时文件的目录
        File file = new File(destPath);
        if (!file.exists()) {
            file.mkdirs();
        }

        //临时文件路径/文件名
        destPath = file + "/" + System.currentTimeMillis() +
                String.valueOf(UUID.randomUUID()).replace("-", "") + ".xlsx";
        OutputStream outputStream = null;
        //使用FileOutputStream将内存中的数据写到本地，生成临时文件
        try {
            outputStream = new FileOutputStream(destPath);
            xssfWorkbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.flush();
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return destPath;
    }

    //获取文件信息
    public static List<Map<String, String>> getFile() throws Exception {

        FileInputStream fin = new FileInputStream(sourPath);
        InputStreamReader reader = new InputStreamReader(fin);
        BufferedReader buffReader = new BufferedReader(reader);
        String str = "";
        List list = new ArrayList();
        while ((str = buffReader.readLine()) != null) {
            Map<Object, String> map = new HashMap<Object, String>();
            String[] split = str.split(";");
            if (split.length >= 7) {
                map.put("操作类型", split[1]);
                map.put("归属地址", split[2]);
                map.put("号段", split[3]);
                map.put("局向", split[4]);
                map.put("生效时间", split[5]);
                map.put("失效时间", split[6]);
                map.put("操作时间", split[7]);
                list.add(map);
            }
        }
        return list;
    }

}