Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.coast.controler; import com.coast.model.Product; import com.coast.model.ResultMSG; import com.coast.util.POIUtil; import com.coast.util.ProductToSAPUtil; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.Iterator; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; /** * * @author Coast */ public class Controler { public static ResultMSG merge(String sapFile, String exportFile, String mergedFilePath) { ResultMSG resultMSG = new ResultMSG(); resultMSG.setErrorMessage(""); try { ArrayList<Product> products; //??SAP products = readProductsFromMyExcel(sapFile, resultMSG); //?? String inFile = exportFile; //?? int lastSlash = sapFile.lastIndexOf(File.separator); String outFileName = sapFile.substring(lastSlash + 1, sapFile.length() - 5) + "_merged.xls"; String outFile = mergedFilePath + File.separator + outFileName; // writeProductsToExcel(products, inFile, outFile, resultMSG); } catch (Exception e) { e.printStackTrace(); } finally { return resultMSG; } } public static void writeProductsToExcel(ArrayList<Product> products, String inFile, String outFile, ResultMSG resultMSG) throws Exception { int sum = 0; InputStream is = null; OutputStream os = null; try { File f = new File(outFile); f.delete(); is = new FileInputStream(new File(inFile)); Workbook wb = WorkbookFactory.create(is); Sheet sheet = wb.getSheetAt(0); String brand = null; Iterator<Product> iter = products.iterator(); while (iter.hasNext()) { Product product = iter.next(); ProductToSAPUtil sapUtil = new ProductToSAPUtil(product); // if (product.getSnCode().equals("K630207E00")) { System.out.println("error:" + product); } String fullSize = product.getSize() + "(" + sapUtil.getInternationalSize() + ")"; int thatRowNum = getRowNum(sheet, product.getSnCode(), product.getColorCode(), fullSize); if (thatRowNum == 0) { String notFoundMsg = "SAP?sn=" + product.getSnCode() + " color=" + product.getColorCode() + " size=" + fullSize + " amount=" + product.getAmount() + "\n"; resultMSG.setErrorMessage(resultMSG.getErrorMessage() + notFoundMsg); } else { sheet.getRow(thatRowNum).createCell(6).setCellValue((int) product.getAmount()); sum += product.getAmount(); } } // os = new FileOutputStream(new File(outFile)); wb.write(os); os.flush(); resultMSG.setWriteMessage("?,:" + sum + ""); } catch (Exception e) { e.printStackTrace(); resultMSG.setWriteMessage(",:" + sum + ",:" + e.toString()); } finally { is.close(); os.close(); } } public static ArrayList<Product> readProductsFromMyExcel(String file, ResultMSG resultMSG) throws Exception { ArrayList<Product> products = new ArrayList<Product>(); InputStream is = null; int sum = 0; int row = 1; try { File f = new File(file); is = new FileInputStream(f); Workbook wb = WorkbookFactory.create(is); Sheet sheet = wb.getSheetAt(0); POIUtil poiUtil = new POIUtil(); int lastRowNum = sheet.getLastRowNum(); while (row <= lastRowNum) { //?? Cell firstCell = sheet.getRow(row).getCell(0); if (firstCell == null) break; if (firstCell.getRichStringCellValue().toString().toUpperCase() == "") break; //fullsn Cell fullSnCell = sheet.getRow(row).getCell(0); String fullSn = poiUtil.getCellContentToString(fullSnCell); int len = fullSn.length(); String snCode = fullSn.substring(0, len - 3); String colorCode = fullSn.substring(len - 3, len - 1); String sizeCode = fullSn.substring(len - 1, len); // String sizeRegex = convertSizeToRegex(sizeCode); //type Cell typeCell = sheet.getRow(row).getCell(1); String type = poiUtil.getCellContentToString(typeCell); //color Cell colorCell = sheet.getRow(row).getCell(2); String color = poiUtil.getCellContentToString(colorCell); //size Cell sizeCell = sheet.getRow(row).getCell(3); String size = poiUtil.getCellContentToString(sizeCell); //price Cell priceCell = sheet.getRow(row).getCell(5); String orgPrice = poiUtil.getCellContentToString(priceCell); //amount Cell amountCell = sheet.getRow(row).getCell(10); int amount = Integer.parseInt(poiUtil.getCellContentToString(amountCell)); //Porduct Product product = new Product(fullSn, snCode, colorCode, sizeCode, type, color, size, orgPrice, amount); products.add(product); sum += product.getAmount(); row++; } resultMSG.setReadMessage("??,:" + sum + "!"); } catch (Exception e) { System.out.println( "readProductsFromMyExcel:=" + row + "=?" + e.toString()); products = null; e.printStackTrace(); resultMSG.setReadMessage("?,:" + sum + "!:" + e.toString()); } finally { is.close(); System.out.println("?:" + sum); return products; } } /** * ?excel * @param sheet * @param sn * @param color * @param size * @return * @throws Exception */ public static int getRowNum(Sheet sheet, String sn, String colorCode, String size) throws Exception { int lastRowNum = sheet.getLastRowNum();//excell?lastRowNum+1; int rowNum = lastRowNum; while (rowNum > 0) { Cell snCell = sheet.getRow(rowNum).getCell(3); Cell colorCell = sheet.getRow(rowNum).getCell(4); Cell sizeCell = sheet.getRow(rowNum).getCell(5); POIUtil poiUtil = new POIUtil(); String targetSn = poiUtil.getCellContentToString(snCell); String targetColor = poiUtil.getCellContentToString(colorCell); String targetSize = poiUtil.getCellContentToString(sizeCell); if (targetSn.equals(sn) && targetColor.equals(colorCode) && targetSize.equals(size)) { System.out.println("Find! snCell:" + snCell.toString()); return rowNum; } rowNum--; // } return 0; } // public static String trimSize(String size) { // String editedSize = ""; // String regex = "[cm]"; // editedSize = size.replaceAll(regex, ""); // return editedSize; // } private static String convertSizeToRegex(String size) { String result; switch (size) { case "1": result = "^155.*"; break; case "2": result = "^160.*"; break; case "3": result = "^165.*"; break; case "4": result = "^170.*"; break; case "5": result = "^175.*"; break; case "6": result = "^180.*"; break; default: result = ""; break; } return result; } }