CIS/src/main/java/com/welampiot/utils/ExcelUtil.java

package com.welampiot.utils;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
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.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {
    /**
     * 文件导出到excel
     *
     * @param titleList   标题列表 一维列表
     * @param contentList 数据列表 二维列表
     * @return
     */
    public static String outExcel(List<String> titleList, List<List<String>> contentList){
        XSSFWorkbook sheets = new XSSFWorkbook();
        XSSFSheet sheet = sheets.createSheet();

        // 冻结第一行
        sheet.createFreezePane(0,1,0,1);

        // 创建居中显示的单元格样式
        CellStyle style = sheets.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 创建表头，也就是第一行
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < titleList.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(titleList.get(i));
            cell.setCellStyle(style); // 设置单元格为居中显示样式
        }

        //创建表内容，从第二行开始
        for (int i = 0; i < contentList.size(); i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < contentList.get(i).size(); j++) {
                XSSFCell cell = row.createCell(j);
                cell.setCellValue(contentList.get(i).get(j));
                cell.setCellStyle(style); // 设置单元格为居中显示样式
            }
        }
        long l = System.currentTimeMillis();
        String filePath = "/upload/temp/"+l+".xls";
        File file = new File(".."+filePath);
        //将文件保存到指定的位置
        try {
            File file2 = new File("../upload/temp/");
            if (!file2.exists()) file2.mkdirs();
            sheets.write(new FileOutputStream(file));
            System.out.println("写入成功");
            sheets.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return filePath;
    }

    public static List<Map> inExcel(MultipartFile file){
//        String contentType = file.getContentType();
//        String fileName = file.getOriginalFilename();
        List<Map> list = new ArrayList<>();
        try {
            //根据路径获取这个操作excel的实例
            HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream());
            //根据页面index 获取sheet页
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row = null;
            //循环sesheet页中数据从第二行开始，第一行是标题
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                //获取每一行数据
                row = sheet.getRow(i);
                Map<Integer, String> stringMap = new HashMap<>();
                System.out.println(row.getRowNum());
                for (int j = 0; j < row.getRowNum(); j++) {
                    if (row.getCell(j) == null) continue;
                    String string = row.getCell(j).toString();
                    stringMap.put(j,string);
                }
                list.add(stringMap);
            }
        } catch (Exception e) {
//            e.printStackTrace();
        }
        return list;
    }
}