com.feingto.cloud.kit.ExcelKit Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of feingto-kit Show documentation
There is a newer version: 2.5.2.RELEASE
package com.feingto.cloud.kit;

import lombok.Setter;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.Assert;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel工具类
 *
 * @author longfei
 */
@SuppressWarnings("WeakerAccess")
public class ExcelKit {
    public static String EXCEL03 = "xls";
    public static String EXCEL07 = "xlsx";
    private Workbook wb;// 工作表对象
    private Sheet sheet;// 工作表sheet
    private Row row;// 行对象
    private Object[] objs;// 对象数组
    @Setter
    private int sheetIndex = 0;// sheet下标值

    public static ExcelKit NEW(MultipartFile file) {
        ExcelKit excel = new ExcelKit();
        Assert.notNull(file.getOriginalFilename(), "文件名不能为空");
        InputStream in;
        try {
            in = file.getInputStream();
        } catch (IOException e) {
            throw new RuntimeException("文件读取失败", e);
        }

        String ext = FileKit.getFileExt(file.getOriginalFilename());
        if (EXCEL03.equals(ext)) {
            excel.builHSSF(in);
        } else if (EXCEL07.equals(ext)) {
            excel.builXSSF(in);
        }

        return excel;
    }

    private void builXSSF(InputStream inputStream) {
        try {
            wb = new XSSFWorkbook(inputStream);
        } catch (Exception e) {
            throw new RuntimeException("文件解析失败", e);
        }
    }

    private void builHSSF(InputStream inputStream) {
        try {
            wb = new HSSFWorkbook(inputStream);
        } catch (Exception e) {
            throw new RuntimeException("文件解析失败", e);
        }
    }

    /**
     * 获取单元格值
     *
     * @param row   excel行对象
     * @param index excel单元格的位置
     * @return 返回获取单元格值
     */
    private static String getCellValue(Row row, int index) {
        Cell cell = row.getCell((short) index);
        String value = "";
        if (cell != null) {
            CellType cellType = cell.getCellTypeEnum();
            if (CellType.NUMERIC.equals(cellType)) {
                value = DateUtil.isCellDateFormatted(cell)
                        ? DateKit.format(cell.getDateCellValue())
                        : new DecimalFormat("#.##").format(cell.getNumericCellValue());
            } else if (CellType.BOOLEAN.equals(cellType)) {
                value = String.valueOf(cell.getBooleanCellValue());
            } else {
                value = cell.getStringCellValue();
            }
        }
        return value.trim();
    }

    /**
     * 获取单个工作薄的标题栏
     *
     * @param startRow 起始行（从0开始）
     * @return Object[]
     */
    public Object[] getSheetCellTitle(int startRow) {
        sheet = wb.getSheetAt(sheetIndex);
        // 获取第startRow行的总列数
        int cellIndex = sheet.getRow(startRow).getPhysicalNumberOfCells();
        // 获取单元格
        row = sheet.getRow(startRow);
        if (null != row) {
            objs = new Object[cellIndex];
            for (int i = 0; i < cellIndex; i++) {
                objs[i] = getCellValue(row, i);
            }
        }
        return objs;
    }

    /**
     * 获取单个工作薄
     *
     * @return List