com.gitee.fufu669.utils.CacheExcelUtil Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of cache-fetch-later Show documentation
cache-fetch-later
There is a newer version: 6.666.66021
package com.gitee.fufu669.utils;

import com.gitee.fufu669.aspect.Logging;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.Cell;
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 org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class CacheExcelUtil implements Logging {

    public static final Logger logger = LoggerFactory.getLogger(CacheExcelUtil.class);

    public static List> readXlsxOrXls(MultipartFile file) {
        //判断文件是否为空
        if (file.isEmpty()) {
            return new ArrayList<>();
        }
        String name = file.getOriginalFilename();
        long size = file.getSize();
        if (StringUtils.isEmpty(name) || size == 0) {
            return new ArrayList<>();
        }
        String postfix = getPostfix(name);
        //读取文件内容
        if ("xlsx".equals(postfix)) {
            return readXlsx(file);
        } else if ("xls".equals(postfix)) {
            return readXls(file);
        } else {
            return new ArrayList<>();
        }
    }


    public static List> readXlsx(MultipartFile file) {
        List> list = new ArrayList>();
        InputStream input = null;
        XSSFWorkbook wb = null;
        try {
            input = file.getInputStream();
            //创建文档
            wb = new XSSFWorkbook(input);
            ArrayList rowList = null;
            int totoalRows = 0;//总行数
            int totalCells = 0;//总列数
            //读取sheet(页)
            for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
                XSSFSheet xssfSheet = wb.getSheetAt(sheetIndex);
                if (xssfSheet == null) {
                    continue;
                }
                totoalRows = xssfSheet.getLastRowNum();
                //读取row
                for (int rowIndex = 0; rowIndex <= totoalRows; rowIndex++) {
                    XSSFRow xssfRow = xssfSheet.getRow(rowIndex);
                    if (xssfRow == null) {
                        continue;
                    }
                    rowList = new ArrayList();
                    totalCells = xssfRow.getLastCellNum();
                    //读取列
                    for (int cellIndex = 0; cellIndex < totalCells; cellIndex++) {
                        XSSFCell xssfCell = xssfRow.getCell(cellIndex);
                        if (xssfCell == null) {
                            rowList.add("");
                        } else {
                            xssfCell.setCellType(Cell.CELL_TYPE_STRING);
                            rowList.add(String.valueOf(xssfCell.getStringCellValue()));
                        }
                    }
                    list.add(rowList);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (wb != null) {
                    wb.close();
                }
                if (input != null) {
                    input.close();
                }
            } catch (Exception e) {
            }
        }
        return list;
    }

    public static List> readXls(MultipartFile file) {
        List> list = new ArrayList>();
        //创建输入流
        InputStream input = null;
        //创建文档
        HSSFWorkbook wb = null;
        try {
            input = file.getInputStream();
            //创建文档
            wb = new HSSFWorkbook(input);
            ArrayList rowList = null;
            int totoalRows = 0;//总行数
            int totalCells = 0;//总列数
            //读取sheet(页)
            for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
                HSSFSheet hssfSheet = wb.getSheetAt(sheetIndex);
                if (hssfSheet == null) {
                    continue;
                }
                totoalRows = hssfSheet.getLastRowNum();
                //读取row
                for (int rowIndex = 0; rowIndex <= totoalRows; rowIndex++) {
                    HSSFRow hssfRow = hssfSheet.getRow(rowIndex);
                    if (hssfRow == null) {
                        continue;
                    }
                    rowList = new ArrayList();
                    totalCells = hssfRow.getLastCellNum();
                    //读取列
                    for (int cellIndex = 0; cellIndex < totalCells; cellIndex++) {
                        HSSFCell hssfCell = hssfRow.getCell(cellIndex);
                        if (hssfCell == null) {
                            rowList.add("");
                        } else {
                            hssfCell.setCellType(Cell.CELL_TYPE_STRING);
                            rowList.add(String.valueOf(hssfCell.getStringCellValue()));
                        }
                    }
                    list.add(rowList);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (wb != null) {
                    wb.close();
                }
                if (input != null) {
                    input.close();
                }
            } catch (Exception e) {
            }
        }
        return list;
    }

    public static String getPostfix(String path) {
        if (StringUtils.isEmpty(path) || !path.contains(".")) {
            return null;
        }
        return path.substring(path.lastIndexOf(".") + 1, path.length()).trim();
    }

}