Excel批量导入数据

负熵流

已于 2024-03-19 17:17:16 修改

阅读量455

点赞数 4

分类专栏： Java 文章标签： excel

于 2024-03-19 17:14:40 首次发布

本文链接：https://blog.csdn.net/ClaireCheney/article/details/136848914

版权

Java 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

文章介绍了如何在Java中利用ApachePOI库（特别是poi-ooxml版本3.16）读取Excel文件，将数据解析为CreateGoodsRequest对象，用于商品信息管理。

摘要由CSDN通过智能技术生成

用到的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);

    }


}