Excel批量导入数据

用到的jar包

implementation group: 'org.apache.poi',name: 'poi-ooxml',version: '3.16'

import com.edu.shop.api.reqs.CreateGoodsRequest;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.WorkbookFactory;
import org.springframework.stereotype.Service;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;


@Service
public class ExcelUtils<T>  {
    public static List<CreateGoodsRequest> importExcelForGoods(String excelFilePath,Long channel) {
        List<CreateGoodsRequest> goodsList = new ArrayList<>();
        /*
        使用try-with-resources语法打开一个文件输入流（FileInputStream）和创建一个Workbook对象，并在try块结束时自动关闭这些资源
         */
        try (FileInputStream fileInputStream = new FileInputStream(excelFilePath);
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {

            Sheet sheet = workbook.getSheetAt(0);

            for (Row row : sheet) {
                if (row.getRowNum() == 0) {
                    continue; // Skip header row
                }

                CreateGoodsRequest goods = new CreateGoodsRequest();
                goods.setBusinessType("BASE");
                goods.setGoodsType(1);
                goods.setSpecsType(1);
                goods.setChannelId(channel);

                goods.setName(row.getCell(0).getStringCellValue());
                goods.setDescription(row.getCell(1).getStringCellValue());
                goods.setBarcode(row.getCell(2).getStringCellValue());
                goods.setTotalStock(Integer.valueOf(row.getCell(3).getStringCellValue()));
                //价格单位分
                goods.setOriginalPrice(new BigDecimal(row.getCell(4).getStringCellValue()).multiply(BigDecimal.valueOf(100)));
                goods.setPrice(new BigDecimal(row.getCell(5).getStringCellValue()).multiply(BigDecimal.valueOf(100)));
                goods.setStatus(row.getCell(6).getStringCellValue().equals("启用")?1:2);
                goodsList.add(goods);
            }
        } catch (IOException | InvalidFormatException e) {
            throw new RuntimeException(e);
        }
        return goodsList;
    }

    public static void main(String[] args) {
        String excelFilePath = "F:/FeiShuDownloads/新版商品资料.xlsx";

        ExcelUtils.importExcelForGoods(excelFilePath,1160L);

    }


}