public void saveOrUpdate(List<JewelryEntity> list) {
if (list == null || list.isEmpty()) {
return;
}
Date now = new Date();
for (JewelryEntity e : list) {
e.setUpdateDate(now);
this.amazonJewelryDao.saveOrUpdate(e);
}
}
public void write2Excel(List<JewelryEntity> list, String excelPath) throws Exception {
if (list == null && list.isEmpty()) {
this.log.warn("Product list is empty !");
return;
}
// 1. copy excel head from template
File templateFile = new ClassPathResource("template/JewelryTemplate.xls").getFile();
String templateFilePath = templateFile.getAbsolutePath();
Workbook sourceBook = Workbook.getWorkbook(new File(templateFilePath));
WritableWorkbook targetBoook = Workbook.createWorkbook(new File(excelPath), sourceBook);
WritableSheet sheet1 = targetBoook.getSheet(0);
sheet1.getSettings().setHorizontalFreeze(1); // 设置列冻结
sheet1.getSettings().setVerticalFreeze(3); // 设置行冻结前2行
// 2. get columns name
Sheet st = sourceBook.getSheet(0);
int totalColumns = st.getColumns(); // for version it's 220
String[] columnNames = new String[totalColumns];
for (int i = 0; i < totalColumns; i++) {
columnNames[i] = st.getCell(i, 2).getContents();
}
// 3. write data to excel
for (int row = 0; row < list.size(); row++) {
JewelryEntity ent = list.get(row);
this.log.info(row + "------------------------");
for (int col = 0; col < totalColumns; col++) {
Object columnValue = this.getValueByJaveReflect(ent, columnNames[col]);
if (columnValue == null) {
if ("generic_keywords".equals(columnNames[col])) {
String keywords =
ent.getGenericKeywords1()
+ " "
+ ent.getGenericKeywords2()
+ " "
+ ent.getGenericKeywords3()
+ " "
+ ent.getGenericKeywords4()
+ " "
+ ent.getGenericKeywords5()
+ " "
+ ent.getNewKeywords();
int lenght = keywords.length() > 1000 ? 999 : keywords.length() - 1;
sheet1.addCell(new Label(col, row + 3, keywords.substring(0, lenght)));
} else {
sheet1.addCell(new Label(col, row + 3, null));
}
} else {
if (ParentChild.parent.toString().equals(ent.getParentChild())) {
// some column for parent is no needed
if (this.omitColumnNames4Parent.contains(columnNames[col])) {
sheet1.addCell(new Label(col, row + 3, null));
} else {
sheet1.addCell(new Label(col, row + 3, columnValue.toString()));
}
} else {
sheet1.addCell(new Label(col, row + 3, columnValue.toString()));
}
}
}
}
targetBoook.write();
targetBoook.close();
sourceBook.close();
}