|
1
4 package com.openedit.store.excelconvert;
5
6 import java.util.ArrayList  ;
7 import java.util.List ;
8
9 import org.apache.commons.logging.Log;
10 import org.apache.commons.logging.LogFactory;
11 import org.apache.poi.hssf.usermodel.HSSFCell;
12 import org.apache.poi.hssf.usermodel.HSSFRow;
13 import org.apache.poi.hssf.usermodel.HSSFSheet;
14 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
15 import org.openedit.money.Money;
16
17 import com.openedit.store.Category;
18 import com.openedit.store.InventoryItem;
19 import com.openedit.store.Price;
20 import com.openedit.store.PriceSupport;
21 import com.openedit.store.Product;
22 import com.openedit.store.Store;
23 import com.openedit.store.StoreException;
24
25 public class GenericExcelConvert extends ExcelConverter
26 {
27
28 private static final Log log = LogFactory.getLog(GenericExcelConvert.class);
29 protected static final short SKU_CELL = (short) 0;
30 protected static final short PRODUCT_ID_CELL = (short) 1;
31 protected static final short PRODUCT_NAME_CELL = (short) 2;
32 protected static final short PRODUCT_DESCRIPTION_CELL = (short) 3;
33 protected static final short KEYWORDS_CELL = (short) 4;
34 protected static final short CATALOG_ID1 = (short) 5;
35 protected static final short CATALOG_DESC = (short) 6;
36 protected static final short SIZE_CELL = (short) 7;
37 protected static final short COLOR_CELL = (short) 8;
38 protected static final short PRICE_CELL = (short) 9;
39 protected static final short WEIGHT_CELL = (short) 10;
40 protected static final short QUANTITY_CELL = (short) 11;
41
42 protected Category fieldLastCatalog;
43
44 public GenericExcelConvert()
45 {
46 super();
47 }
48
49 protected void processWorkbook(Store inStore,List inLog, HSSFWorkbook inWorkbook) throws Exception
50 {
51 HSSFSheet sheet = inWorkbook.getSheetAt(0);
52 int rowNum = 0;
53 boolean done = false;
54 List products = new ArrayList ();
55 Header header = new Header();
56 Product product = null;
57 while (!done)
58 {
59 HSSFRow row = sheet.getRow(rowNum);
60 rowNum++;
61 if (row == null)
62 {
63 break;
64 }
65 if ( rowNum == 1 )
66 {
67 short col = 0;
68 while( true )
69 {
70 HSSFCell cell = row.getCell(col);
71 if ( cell != null && cell.getCellType() != HSSFCell.CELL_TYPE_BLANK)
72 {
73 header.addCol(col,toString( cell ) );
74 }
75 else
76 {
77 break;
78 }
79 col++;
80 }
81 continue;
82 }
83 HSSFCell skuCell = row.getCell(SKU_CELL);
84 if (skuCell == null || skuCell.getCellType() == HSSFCell.CELL_TYPE_BLANK)
85 {
86 done = true;
87 }
88 else
89 {
90 InventoryItem inventoryItem = createInventoryItem(row, header);
91 HSSFCell idCell = row.getCell(PRODUCT_ID_CELL);
92
93 if (idCell != null && idCell.getCellType() != HSSFCell.CELL_TYPE_BLANK )
95 {
96 String id = toString( idCell );
97 if ( product == null || !product.getId().equals(id))
98 {
99 product = createProduct(inStore, row,id);
100 products.add(product);
101 }
102 }
103
104 if (product == null)
105 {
106 inLog.add("No product at or above row " + rowNum);
107 }
108 else
109 {
110 product.addInventoryItem(inventoryItem);
111 }
112 }
113 }
114 inLog.add("Processed: " + products.size() + " products");
115 inStore.getCatalogArchive().saveCatalogs();
116 saveOutput(inStore, products);
117
118 }
119
120
125 protected Product createProduct(Store inStore, HSSFRow inRow, String inId) throws Exception
126 {
127 Product product = inStore.getProduct(inId);
128 if ( product == null)
129 {
130 product = new Product();
131 product.setId(inId);
132 }
133 else
134 {
135 product.clearItems();
136 }
137 HSSFCell nameCell = inRow.getCell(PRODUCT_NAME_CELL);
138 product.setName( toString( nameCell ) );
139 HSSFCell descriptionCell = inRow.getCell(PRODUCT_DESCRIPTION_CELL);
140 if( descriptionCell != null)
141 {
142 String des = toString( descriptionCell);
143 if ( des != null)
144 {
145 des = des.replaceAll("\r\n|\r|\n|\n\r","<br>");
146 }
147 product.setDescription( des );
148 }
149 String keywords = toString( inRow.getCell(KEYWORDS_CELL) );
150 if ( keywords != null && keywords.length() > 0)
151 {
152 product.setKeywords(keywords);
153 }
154
155 addCatalog(inStore, inRow, product);
156 return product;
157 }
158
159 protected void addCatalog(Store inStore, HSSFRow inRow, Product product) throws StoreException
160 {
161 HSSFCell cell = inRow.getCell(CATALOG_ID1);
162 String id = toString( cell );
163 if( id != null && id.length() > 0)
164 {
165 Category cat1= inStore.getCatalog(id);
166 if ( cat1 == null)
167 {
168 String desc = toString(inRow.getCell(CATALOG_DESC));
169 cat1 = inStore.getCatalogArchive().addCatalog(id,desc);
170 }
171 product.addCatalog(cat1);
172 }
173 }
174
175
180 protected InventoryItem createInventoryItem(HSSFRow inRow, Header inHeader)
181 {
182 InventoryItem inventoryItem = new InventoryItem();
183 HSSFCell skuCell = inRow.getCell(SKU_CELL);
184 inventoryItem.setSku(toString( skuCell ) );
185 HSSFCell sizeCell = inRow.getCell(SIZE_CELL);
186 inventoryItem.setSize(toString( sizeCell ) );
187 HSSFCell colorCell = inRow.getCell(COLOR_CELL);
188 inventoryItem.setColor(toString(colorCell ));
189
190 String val = toString(inRow.getCell(PRICE_CELL));
191 if ( val != null)
192 {
193 PriceSupport price = new PriceSupport();
194 price.addTierPrice(1,new Price(new Money( val ) ) );
195 inventoryItem.setPriceSupport(price);
196 }
197
198 String weightCell = toString( inRow.getCell(WEIGHT_CELL) );
199 if ( weightCell != null && weightCell.length() > 0)
200 {
201 inventoryItem.setWeight(Double.parseDouble(weightCell));
202 }
203
204 String quantity = toString( inRow.getCell(QUANTITY_CELL) );
205 if ( quantity != null && quantity.length() > 0)
206 {
207 inventoryItem.setQuantityInStock(Integer.parseInt(quantity));
208 }
209 else
210 {
211 inventoryItem.setQuantityInStock(1000);
212 }
213
214 for (int i = QUANTITY_CELL + 1; i < inHeader.getSize(); i++)
216 {
217 String col = inHeader.getColumn(i);
218 HSSFCell extCell = inRow.getCell((short)i);
219 if ( extCell == null || extCell.getCellType() != HSSFCell.CELL_TYPE_BLANK)
220 {
221 inventoryItem.addProperty(col, toString(extCell));
222 }
223 }
224
225 return inventoryItem;
226 }
227
228 }
229 |
Popular Tags |
Java API By Example, From Geeks To Geeks.