ExcelReader.java

package com.kwan.springbootkwan.utils;

import com.kwan.springbootkwan.entity.ExcelData;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Objects;


/**
 * 读取excel工具类
 *
 * @author : qinyingjie
 * @version : 2.2.0
 * @date : 2023/8/14 15:28
 */
public class ExcelReader {
    public static List<ExcelData> readExcel(String filePath) {
        List<ExcelData> dataList = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            // Skip the header row
            if (rowIterator.hasNext()) {
                rowIterator.next();
            }
            //忽略第二行
            if (rowIterator.hasNext()) {
                rowIterator.next();
            }
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                final ExcelData excelDataFromRow = createExcelDataFromRow(row);
                if (Objects.nonNull(excelDataFromRow)) {
                    dataList.add(excelDataFromRow);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataList;
    }

    private static ExcelData createExcelDataFromRow(Row row) {
        ExcelData data = new ExcelData();
        final Cell cell = row.getCell(0);
        if (Objects.isNull(cell)) {
            return null;
        }
        DecimalFormat decimalFormat = new DecimalFormat("0");
        data.setProduct_id(decimalFormat.format(cell.getNumericCellValue()));
        data.setProductName(row.getCell(1).getStringCellValue());
        data.setCategoryName2(row.getCell(2).getStringCellValue());
        data.setGenderName(row.getCell(3).getStringCellValue());
        data.setProductYearName(row.getCell(4).getNumericCellValue());
        data.setSeasonName(row.getCell(5).getStringCellValue());
        data.setColorName(row.getCell(6).getStringCellValue());
        data.setStyleName(row.getCell(7).getStringCellValue());
        data.setAvg_sal_price(row.getCell(8).getNumericCellValue());
        data.setGross_margin(row.getCell(9).getNumericCellValue());
        data.setTotal7_size_store_day(row.getCell(10).getNumericCellValue());
        data.setDiscount(row.getCell(11).getNumericCellValue());
        data.setSal_store_count(row.getCell(12).getNumericCellValue());
        data.setDay_7_sal_qty_store_rate_rank(row.getCell(13).getNumericCellValue());
        data.setInv_store_qty(row.getCell(14).getNumericCellValue());
        data.setOndesk_date(row.getCell(15).getStringCellValue());
        data.setInv_store_ratio(row.getCell(16).getNumericCellValue());
        data.setAvg_now_price(row.getCell(17).getNumericCellValue());
        data.setAll_store_num(row.getCell(18).getNumericCellValue());
        data.setDay_7_size_store_day_rank(row.getCell(19).getNumericCellValue());
        data.setTotal_ondesk_day(row.getCell(20).getNumericCellValue());
        data.setTotal_replenish_qty(row.getCell(21).getNumericCellValue());
        data.setInitial_order_qty(row.getCell(22).getNumericCellValue());
        data.setArrive_qty(row.getCell(23).getNumericCellValue());
        data.setSal_out_rate(row.getCell(24).getNumericCellValue());
        data.setTotal_arrive_qty(row.getCell(25).getNumericCellValue());
        data.setInv_out_qty(row.getCell(26).getNumericCellValue());
        data.setTotal_sal_rank(row.getCell(27).getNumericCellValue());
        data.setTotal_sal_qty(row.getCell(28).getNumericCellValue());
        data.setInv_qty(row.getCell(29).getNumericCellValue());
        data.setReplenish_not_arrive_qty(row.getCell(30).getNumericCellValue());
        data.setCounter_date(row.getCell(31).getStringCellValue());
        data.setTotal_sal_amt(row.getCell(32).getNumericCellValue());
        data.setTotal_discount(row.getCell(33).getNumericCellValue());
        data.setSal_qty(row.getCell(34).getNumericCellValue());
        data.setInv_store_count(row.getCell(35).getNumericCellValue());
        data.setTotal_purchase_qty(row.getCell(36).getNumericCellValue());
        data.setDay_30_size_store_day_rank(row.getCell(37).getNumericCellValue());
        data.setInv_sal_ratio(row.getCell(38).getNumericCellValue());
        return data;
    }

    public static void main(String[] args) {
        String filePath = "/Users/qinyingjie/Downloads/商品运营助手_滴小R_样例数据_edited.xlsx";
        List<ExcelData> dataList = readExcel(filePath);
        for (ExcelData data : dataList) {
            System.out.println(data);
        }
    }
}