opencart商品批量上传

上一篇我们讲了如何将商品采集到excel，现在我们通过脚本程序一键将excel商品导入到我们自己的网站。

一 商品信息准备

读取excel里的商品信息

ReadExcelUtils excelReader = new ReadExcelUtils(EXCEL_PATH);
Map<Integer, Map<Integer, Object>> map = excelReader.readExcelContent();
System.out.println("获得Excel表格的内容:"+map.size());
for (int i = fromIndex; i <= map.size(); i++) {
    initDB(conn, lastId, Objects.requireNonNull(map.get(i)));
}

其中ReadExcelUtils 是我封装的一个读取excel的工具类
这样我们就把excel的数据，读到map里了。看下效果

这个是excel的数据

这个是读取结果。可以进行下一步，将数据导入我们的数据库。

二 将读取的数据插入到数据库

这里使用的JDBC连接远程服务器的数据库，然后就是开始操作我们的数据库里的各种表了

1 插入商品基本信息-表名oc_product

SQL 、插入代码 如下

public class Product {

    String sql ="INSERT INTO oc_product (product_id, model, sku, upc, ean, jan, isbn, mpn, location, quantity, stock_status_id, image, manufacturer_id, shipping, price, points, tax_class_id, date_available, weight, weight_class_id, length, width, height, length_class_id, subtract, minimum, sort_order, status, viewed, date_added, date_modified)" +
            " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
    public static String TABLE_NAME = "oc_product";
    String folder;

    public Product(int product_id, String model, String sku, float price,String folder) {
        this.product_id = product_id;
        this.model = model;
        this.sku = sku;
        this.price = price;
        this.folder = folder;
        this.image = FileUtils.getMainImageFromFolder(folder);
    }

    public int product_id;//
    public String model;//
    public String sku;
    public String upc="";
    public String ean="";
    public String jan="";
    public String isbn="";
    public String mpn="";
    public String location="";
    public int quantity = 10000;
    public int stock_status_id = 6;
    public String image="";
    public int manufacturer_id;
    public int shipping = 1;
    public float price;//
    public int points;
    public int tax_class_id;
    public Date date_available =new Date(System.currentTimeMillis());
    public float weight;
    public int weight_class_id = 1;
    public float length;
    public float width;
    public float height;
    public int length_class_id = 1;
    public int subtract = 1;
    public int minimum =1;
    public int sort_order =1 ;
    public int status =1;
    public int viewed ;
    public Timestamp date_added = new Timestamp(System.currentTimeMillis());
    public Timestamp date_modified = new Timestamp(System.currentTimeMillis());


    public void insert(Connection conn){

        try {
            PreparedStatement pstm =conn.prepareStatement(sql);
            pstm.setInt(1,product_id);
            pstm.setString(2,model);
            pstm.setString(3,sku);
            pstm.setString(4,upc);
            pstm.setString(5,ean);
            pstm.setString(6,jan);
            pstm.setString(7,isbn);
            pstm.setString(8,mpn);
            pstm.setString(9,location);
            pstm.setInt(10,quantity);
            pstm.setInt(11,stock_status_id);
            pstm.setString(12,image);
            pstm.setInt(13,manufacturer_id);
            pstm.setInt(14,shipping);
            pstm.setFloat(15,price);
            pstm.setInt(16,points);
            pstm.setInt(17,tax_class_id);
            pstm.setDate(18,date_available);
            pstm.setFloat(19,weight);
            pstm.setInt(20,weight_class_id);
            pstm.setFloat(21,length);
            pstm.setFloat(22,width);
            pstm.setFloat(23,height);
            pstm.setInt(24,length_class_id);
            pstm.setInt(25,subtract);
            pstm.setInt(26,minimum);
            pstm.setInt(27,sort_order);
            pstm.setInt(28,status);
            pstm.setInt(29,viewed);
            pstm.setTimestamp(30,date_added);
            pstm.setTimestamp(31,date_modified);
            pstm.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

这里一些字段是固定的，变化的字段从excel读出来放到具体的位置。

2 商品主图、附图-表oc_product_image

public class ProductImage {
    private static final String SQL = "INSERT INTO oc_product_image (product_image_id, product_id, image, sort_order) VALUES (?,?,?,?)";
    public int product_id;
    public String folder;

    public ProductImage(int product_id, String folder) {
        this.product_id = product_id;
        this.folder = folder;

    }

    public void insert(Connection conn){
        List<String> images = FileUtils.getSubMainImagesFromFolder(folder);
        if (images == null || images.size() == 0){
            return;
        }

        for (int i = 0; i < images.size(); i++) {
            String image = images.get(i);
            try {
                PreparedStatement pstm = conn.prepareStatement(SQL);
                pstm.setInt(1, DBUtils.getLastProjectImageId(conn) + 1);
                pstm.setInt(2, product_id);
                pstm.setString(3, image);
                pstm.setInt(4, i);
                pstm.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

3 产品描述-表oc_product_description

public class ProductDesc {
    private static final String SQL = "INSERT INTO oc_product_description (product_id, language_id, name, description, tag, meta_title, meta_description, meta_keyword) VALUES (?,?,?,?,?,?,?,?);";
    public int product_id;
    public int language_id;// 1 eng 2 cn
    public String name;
    public String description;
    public String tag;
    public String meta_title;
    public String meta_description;
    public String meta_keyword;


    public ProductDesc(String folder,int product_id, int language_id, String name, String description, String tag, String meta_description) {
        this.product_id = product_id;
        this.language_id = language_id;
        this.name = name;
        this.description = description;
        this.tag = tag;
        this.meta_title = name;
        this.meta_description = meta_description;
        this.meta_keyword = name;
        this.description = FileUtils.generateDesc(folder);
    }

    public void insert(Connection conn){
        try {
            PreparedStatement pstm =conn.prepareStatement(SQL);
            pstm.setInt(1,product_id);
            pstm.setInt(2,language_id);
            pstm.setString(3,name);
            pstm.setString(4,description);
            pstm.setString(5,tag);
            pstm.setString(6,meta_title);
            pstm.setString(7,meta_description);
            pstm.setString(8,meta_keyword);
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4 产品分类-表oc_product_to_category

public class Category {
    public static String TABLE_NAME = "oc_product_to_category";
    public static final String SQL = "INSERT INTO oc_product_to_category (product_id, category_id) VALUES (?,?);";
    public int product_id;
    public int category_id = 59;

    public Category(int product_id, int category_id) {
        this.product_id = product_id;
        this.category_id = category_id;
    }

    public void insert(Connection conn){
        try {
            PreparedStatement pstm =conn.prepareStatement(SQL);
            pstm.setInt(1,product_id);
            pstm.setInt(2,category_id);
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5 选项(选项这个操作稍微复杂些，涉及4张表，我们下节分享)

感兴趣的同学可以去我的自建站看下网站效果