koulab

技術系のメモ

Amazon Seller Centralでアップロードによる一括商品登録のアップロードステータスの確認のxlsxファイルをJavaから読み出す

Apache POI

https://poi.apache.org/

maven

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.1</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.1</version>
</dependency>


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

public class XlsxTest {
    public static void main(String[] args) throws IOException {

        String xlsxFileAddress = "C:\\SE\\amzn1.result.xlsx";

        //code from https://itsakura.com/java-poi
        XSSFWorkbook workbook1;
        try {
            workbook1 = new XSSFWorkbook(new FileInputStream(xlsxFileAddress));
            XSSFSheet sheet1 =  workbook1.getSheet("テンプレート");
            XSSFRow row1 = sheet1.getRow(0);
            XSSFCell version = row1.getCell(1);

            System.out.println(version); //-> Version=2015.0127

            for(int i = sheet1.getFirstRowNum(); i <= sheet1.getLastRowNum(); i++){
                try {
                    XSSFRow row = sheet1.getRow(i);

                    //エラーのある項目数
                    int numberOfAttributesWithErrors =  (int)getCheckDataCellValue(row.getCell(0));
                    //修正案のある項目数
                    int numberOfAttributesWithOtherSuggestions =  (int)getCheckDataCellValue(row.getCell(1));

                    //商品管理番号(SKU)
                    String sku =  (String)getCheckDataCellValue(row.getCell(2));

                    System.out.println(sku + "\t" + numberOfAttributesWithErrors + "\t" + "\t" + numberOfAttributesWithOtherSuggestions);
                }catch (ClassCastException e){ }
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static Object getCheckDataCellValue(Cell cell) {
        CellType cellType = cell.getCellType();
        if (cellType == CellType.BLANK) {
            return "";
        }
        switch (cellType) {
            case STRING:
                return cell.getRichStringCellValue().getString();

            case NUMERIC:
                return ((int)(cell.getNumericCellValue()));
        }

        return "";
    }


}