1. Excel2Json

2. Json2Excel

3. 엑셀파일 업로드

1. Excel2Json

1. 엑셀 파일 읽기 (Read Excel File into Java List Objects)

/**
 * Step 1:
 * Read Excel File into Java List Objects
 *
 *@paramfilePath
*@return
*/
private static List readExcelFile(String filePath){
    try {
        FileInputStream excelFile = new FileInputStream(new File(filePath));
        Workbook workbook = new XSSFWorkbook(excelFile);

        Sheet sheet = workbook.getSheet("Customers");
        Iterator rows = sheet.iterator();

        List lstCustomers = new ArrayList();

        int rowNumber = 0;
        while (rows.hasNext()) {
            Row currentRow = (Row) rows.next();

            // skip header
            if(rowNumber == 0) {
                rowNumber++;
                continue;
            }

            Iterator cellsInRow = currentRow.iterator();

            Customer cust = new Customer();

            int cellIndex = 0;
            while (cellsInRow.hasNext()) {
                Cell currentCell = (Cell) cellsInRow.next();

                if(cellIndex==0) { // ID
                    cust.setId(String.valueOf(currentCell
										.getNumericCellValue()));
                } else if(cellIndex==1) { // Name
                    cust.setName(currentCell.getStringCellValue());
                } else if(cellIndex==2) { // Address
                    cust.setAddress(currentCell.getStringCellValue());
                } else if(cellIndex==3) { // Age
                    cust.setAge((int) currentCell.getNumericCellValue());
                }

                cellIndex++;
            }

            lstCustomers.add(cust);
        }

        // Close WorkBook
        workbook.close();
        System.out.println();
        System.out.println("readExcelFile");
        System.out.println("lstCustomers" + lstCustomers);
        return lstCustomers;

    } catch (IOException e) {
        throw new RuntimeException("FAIL! -> message = " + e.getMessage());
    }
}

2. Json String (Convert Java Objects to JSON String)

/**
 * Step 2:
 * Convert Java Objects to JSON String
 *
 *@paramcustomers
*@paramfileName
*/
private static String convertObjects2JsonString(List customers) {
    ObjectMapper mapper = new ObjectMapper();
    String jsonString = "";

    try {
        jsonString = mapper.writeValueAsString(customers);
    } catch (JsonProcessingException e) {
        e.printStackTrace();
    }
    System.out.println();
    System.out.println("Json String");
    System.out.println("jsonStrng" + jsonString);
    return jsonString;
}

3. Json File (Write Java List Objects to JSON File)

/**
 * Step 3:
 * Convert Java Objects to JSON File
 *
 *@paramcustomers
*@parampathFile
*/
private static void writeObjects2JsonFile(List customers, String pathFile) {
    ObjectMapper mapper = new ObjectMapper();

    File file = new File(pathFile);
    try {
        // Serialize Java object info JSON file.
        mapper.writeValue(file, customers);
    } catch (IOException e) {
        e.printStackTrace();
    }
}

2. Json2Excel

1)  Convert JSON String to Java List Objects\

/**
 * Step 1:
 * Convert JSON String to Java List Objects
 *
 *@parampathFile
*@return
*/
private static List convertJsonString2Objects(String jsonString){
    List customers = null;

    try {
        customers = new ObjectMapper().readValue(jsonString, 
										new TypeReference<>(){});
    } catch (JsonParseException e) {
        e.printStackTrace();
    } catch (JsonMappingException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    System.out.println(customers);
    return customers;
}

2. Convert JSON String to Java List Objects

/**
 * Step 2:
 * Convert JSON String to Java List Objects
 *
 *@parampathFile
*@return
*/
private static List readJsonFile2Objects(String pathFile){
    InputStream inJson = Customer.class.getResourceAsStream(pathFile);
    List customers = null;

    try {
        customers = new ObjectMapper().readValue(inJson, 
										new TypeReference<>(){});
    } catch (JsonParseException e) {
        e.printStackTrace();
    } catch (JsonMappingException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return customers;
}

3. Write Java Object Lists to Excel File

/**
 * Step 3:
 * Write Java Object Lists to Excel File
 *
 *@paramcustomers
*@paramfilePath
*@throwsIOException
 */
private static void writeObjects2ExcelFile(List <Customer> customers, String filePath) throws IOException {
    String[] COLUMNs = {"Id", "Name", "Address", "Age"};

    Workbook workbook = new XSSFWorkbook();

    CreationHelper createHelper = workbook.getCreationHelper();

    Sheet sheet = workbook.createSheet("Customers");

    Font headerFont = workbook.createFont();
    headerFont.setBold(true);
    headerFont.setColor(IndexedColors.BLUE.getIndex());

    CellStyle headerCellStyle = workbook.createCellStyle();
    headerCellStyle.setFont(headerFont);

    // Row for Header
    Row headerRow = sheet.createRow(0);

    // Header
    for (int col = 0; col < COLUMNs.length; col++) {
        Cell cell = headerRow.createCell(col);
        cell.setCellValue(COLUMNs[col]);
        cell.setCellStyle(headerCellStyle);
    }

    // CellStyle for Age
    CellStyle ageCellStyle = workbook.createCellStyle();
    ageCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));

    int rowIdx = 1;
    for (Customer customer : customers) {
        Row row = sheet.createRow(rowIdx++);

        row.createCell(0).setCellValue(customer.getId());
        row.createCell(1).setCellValue(customer.getName());
        row.createCell(2).setCellValue(customer.getAddress());

        Cell ageCell = row.createCell(3);
        ageCell.setCellValue(customer.getAge());
        ageCell.setCellStyle(ageCellStyle);
    }

    FileOutputStream fileOut = new FileOutputStream(filePath);
    workbook.write(fileOut);
    fileOut.close();
    workbook.close();
}

3. 엑셀파일 업로드

클라이언트(js가 되겠지)에서 파일객체로 엑셀파일 받아서 특정 경로.

-다운로드라면 특정 경로에 있는 파일 열기

-클라이언트한테 전달

-스트림으로 파일 열기 및 전달 고민