=========================1 files========================
package com.teckwah.logistics.dms.dao;
import java.util.Date;
import jxl.Cell;
import jxl.Sheet;
import com.teckwah.logistics.util.ActionMessageException;
import com.teckwah.logistics.util.Converter;
public abstract class PriceBookInfo {
private String projectCode;
/**
* @param projectCode
*/
public PriceBookInfo(String projectCode) {
this.projectCode = projectCode;
}
protected abstract Boolean isEOF(Sheet sheet,int row);
protected String getCellStringValue(Sheet sheet,int col,int row){
Cell cell = null;
try{
cell=sheet.getCell(col,row);
}
catch (Exception e){
}
String result = null;
if(cell!=null){
result = cell.getContents();
if (result!=null&&!"".equals(result.trim())){
result = result.trim();
}
}
return result;
}
protected Double getCellDoubleValue(Sheet sheet,int col,int row){
Double result=0.0;
String value = getCellStringValue(sheet,col,row);
if (value!=null && !value.equals("")){
try{
result=Double.valueOf(value);
}
catch(Exception e){
e.printStackTrace();
}
}else{
throw new ActionMessageException("TheValueNotAllowEmpty:"+(row+1)+":line,"+(col+1)+":column in file");
}
return result;
}
protected Long getCellLongValue(Sheet sheet,int col,int row){
Long result=0L;
String value = getCellStringValue(sheet,col,row);
if (value!=null && !value.equals("")){
try{
result=Long.valueOf(value);
}
catch(Exception e){
e.printStackTrace();
}
}else{
throw new ActionMessageException("TheValueNotAllowEmpty:"+(row+1)+":line,"+(col+1)+":column in file");
}
return result;
}
protected Date getCellDateValue(Sheet sheet,int col,int row){
Date result=null;
String value = getCellStringValue(sheet,col,row);
if (value!=null && !value.equals("")){
try{
result=Converter.convertStringToDateTimeByUS(value);
}
catch(Exception e){
e.printStackTrace();
}
}else{
throw new ActionMessageException("TheValueNotAllowEmpty:"+(row+1)+":line,"+(col+1)+":column in file");
}
return result;
}
protected Boolean getCellBooleanValue(Sheet sheet,int col,int row){
Boolean result=false;
String value = getCellStringValue(sheet,col,row);
if (value!=null && !value.trim().equals("") &&
(value.trim().equalsIgnoreCase("yes") ||
value.trim().equalsIgnoreCase("true") ||
value.trim().equalsIgnoreCase("1"))){
result =true;
}
return result;
}
/**
* @return projectCode
*/
public String getProjectCode() {
return projectCode;
}
/**
* @param projectCode 要设置的 projectCode
*/
public void setProjectCode(String projectCode) {
this.projectCode = projectCode;
}
}
=========================2 files========================
/**
* @author Linian
*/
package com.teckwah.logistics.dms.dao;
import java.io.File;
import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import com.teckwah.logistics.dms.dao.impl.PriceBookCodeDAO;
import com.teckwah.logistics.dms.dao.impl.PriceBookDAO;
import com.teckwah.logistics.domain.ItemMaster;
import com.teckwah.logistics.domain.ItemMasterId;
import com.teckwah.logistics.domain.PriceBook;
import com.teckwah.logistics.domain.PriceBookCode;
import com.teckwah.logistics.domain.PriceBookCodeId;
import com.teckwah.logistics.mim.dao.impl.ItemMasterDAO;
import com.teckwah.logistics.util.ActionMessageException;
import com.teckwah.logistics.util.Converter;
import com.teckwah.logistics.util.GlobUtil;
import com.teckwah.logistics.util.PropertiesUtil;
/**
* @author Linian
*
*/
public class UploadPriceBookInfo extends PriceBookInfo {
private final int startColumn = 0;
private final int firstRow = 0;
private final int startRow = 1;
private final static String SHEETNAME = "PriceBook Details Info";
private String projectCode;
private String timeZone;
public static String datePatternByUS="yyyy-MM-dd HH:mm:ss";
private ItemMasterDAO itemMasterDao;
private PriceBookDAO priceBookDao;
private PriceBookCodeDAO priceBookCodeDao;
private HashSet<String> set;
private jxl.write.DateFormat dateFormat;
private jxl.write.WritableCellFormat cellFormat;
private Map<String, PriceBookCode> priceBookCodeMap;
public UploadPriceBookInfo(String projectCode, String timeZone, ItemMasterDAO itemMasterDao, PriceBookDAO priceBookDao, PriceBookCodeDAO priceBookCodeDao) {
super(projectCode);
this.projectCode = projectCode;
this.timeZone = timeZone;
this.itemMasterDao = itemMasterDao;
this.priceBookDao = priceBookDao;
this.priceBookCodeDao = priceBookCodeDao;
this.set = new HashSet<String>();
this.dateFormat = new jxl.write.DateFormat(datePatternByUS);
this.cellFormat = new jxl.write.WritableCellFormat(dateFormat);
this.priceBookCodeMap = new HashMap<String, PriceBookCode>();
}
/* （非 Javadoc）
* @see com.teckwah.logistics.dms.dao.PriceBookInfo#isEOF(jxl.Sheet, int)
*/
@Override
protected Boolean isEOF(Sheet sheet, int row) {
Boolean result=true;
if(sheet!=null){
String value=getCellStringValue(sheet,startColumn,row+startRow);
result = value ==null || value.trim().equals("");
}
return result;
}
private String isValid(String itemNo){
if(itemNo==null||"".equals(itemNo.trim()))
throw new ActionMessageException("err.ItemNoIsNull");
if(!set.contains(itemNo)){
ItemMasterId id = new ItemMasterId(projectCode, itemNo);
ItemMaster itemMaster = itemMasterDao.findById(id);
if(itemMaster==null||itemMaster.getId().getItemNo()==null||"".equals(itemMaster.getId().getItemNo().trim())){
set.clear();
throw new ActionMessageException("err.ItemNoNotExist");
}else
set.add(itemNo);
}
return itemNo;
}
private PriceBookCode initPriceBookCode(PriceBookCode code, PriceBookCodeId id){
String key = id.getProjectCode()+id.getPriceBook();
if(!priceBookCodeMap.containsKey(key)){
code = priceBookCodeDao.getPriceBookCodeBy(projectCode, id.getPriceBook());
if(code!=null){
code.getId().setOrigin(id.getOrigin());
priceBookCodeMap.put(key, code);
}else{
throw new ActionMessageException("err.PriceBookNotExist");
}
}else{
code = priceBookCodeMap.get(key);
}
return code;
}
private Long isValid(Long origin){
boolean result = true;
if(origin!=null){
for(PropertiesUtil.Origin e : PropertiesUtil.Origin.values()){
if(e.ordinal()==origin.intValue()){
result = false;
break;
}
}
}else{
throw new ActionMessageException("err.OriginIsNull");
}
if(result){
throw new ActionMessageException("err.OriginIsValid");
}
return origin;
}
private Date isValid(Date date){
if(date==null)
throw new ActionMessageException("err.DateNotAllowEmpty");
return date;
}
private String isEmpty(String str){
if(str!=null && !"".equals(str.trim()))
return str.trim();
else
throw new ActionMessageException("err.TheValueNotAllowEmpty");
}
private Boolean getBooleanValue(Boolean data){
if(data==null)
data = false;
return data;
}
private Double isValid(Double data){
if(data==null||data<=0D)
throw new ActionMessageException("qtyOrPriceLessThanZero");
return data;
}
private void initPriceBook(PriceBook obj, Sheet st, int row){
PriceBookCodeId codeId = new PriceBookCodeId();
codeId.setProjectCode(projectCode);
codeId.setOrigin(isValid(getCellLongValue(st, startColumn, row+startRow)));
codeId.setPriceBook(isEmpty(getCellStringValue(st, startColumn+1, row+startRow)));
PriceBookCode code = new PriceBookCode();
obj.setPriceBookCode(initPriceBookCode(code, codeId));
obj.setItem(isValid(getCellStringValue(st, startColumn+2, row+startRow)));
obj.setCurrency(isEmpty(getCellStringValue(st, startColumn+3, row+startRow)));
obj.setMinQty(isValid(getCellDoubleValue(st, startColumn+4, row+startRow)));
obj.setQtyUnit(isEmpty(getCellStringValue(st, startColumn+5, row+startRow)));
obj.setEffectiveDate(Converter.convertToGmtDate(timeZone, isValid(getCellDateValue(st, startColumn+6, row+startRow))));
obj.setExpiredDate(Converter.convertToGmtDate(timeZone, isValid(getCellDateValue(st, startColumn+7, row+startRow))));
obj.setPrice(isValid(getCellDoubleValue(st, startColumn+8, row+startRow)));
obj.setForQuotation(getBooleanValue(getCellBooleanValue(st, startColumn+9, row+startRow)));
obj.setIsDefaultBook(getBooleanValue(getCellBooleanValue(st, startColumn+10, row+startRow)));
obj.setEntryDate(Converter.convertToGmtDate(timeZone, isValid(getCellDateValue(st, startColumn+11, row+startRow))));
obj.setRemark(isEmpty(getCellStringValue(st, startColumn+12, row+startRow)));
}
private void doPriceBook(Workbook workbook){
if(workbook!=null){
PriceBook obj;
Sheet st = workbook.getSheet(0); /// the first sheet...
if(st!=null){
for(int i=0; i<st.getRows(); i++){
if(isEOF(st, i)) break;
obj = new PriceBook();
initPriceBook(obj, st, i);
if(obj!=null)
priceBookDao.attachDirty(obj);
}
}
}
}
public void doGenPriceBookInfo(Workbook workbook){
doPriceBook(workbook);
}
private void writeToSheet(WritableSheet sheet, List<PriceBook> list){
if(list!=null&&list.size()>0){
PriceBook obj;
for(int i=0; i<list.size(); i++){
obj = list.get(i);
try {
sheet.addCell(new Label(startColumn, i+startRow, obj.getPriceBookCode().getId().getOrigin().toString()));
sheet.addCell(new Label(startColumn+1, i+startRow, obj.getPriceBookCode().getId().getPriceBook()));
sheet.addCell(new Label(startColumn+2, i+startRow, obj.getItem()));
sheet.addCell(new Label(startColumn+3, i+startRow, obj.getCurrency()));
sheet.addCell(new jxl.write.Number(startColumn+4, i+startRow, obj.getMinQty()));
sheet.addCell(new Label(startColumn+5, i+startRow, obj.getQtyUnit()));
sheet.addCell(new jxl.write.DateTime(startColumn+6, i+startRow, Converter.convertToLocalDateEx(timeZone, obj.getEffectiveDate()), cellFormat));
sheet.addCell(new jxl.write.DateTime(startColumn+7, i+startRow, Converter.convertToLocalDateEx(timeZone, obj.getExpiredDate()), cellFormat));
sheet.addCell(new jxl.write.Number(startColumn+8, i+startRow, obj.getPrice()));
sheet.addCell(new jxl.write.Boolean(startColumn+9, i+startRow, obj.getForQuotation()));
sheet.addCell(new jxl.write.Boolean(startColumn+10, i+startRow, obj.getIsDefaultBook()));
sheet.addCell(new jxl.write.DateTime(startColumn+11, i+startRow, Converter.convertToLocalDateEx(timeZone, obj.getEntryDate()), cellFormat));
sheet.addCell(new Label(startColumn+12, i+startRow, obj.getRemark()));
sheet.addCell(new jxl.write.Number (startColumn+13, i+startRow, obj.getPriceBookId()));
sheet.addCell(new Label(startColumn+14, i+startRow, obj.getPriceBookCode().getId().getProjectCode()));
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
}
}
private void genDownLoadPriceBookInfo(String realPath, List<PriceBook> list){
try {
WritableWorkbook book = Workbook.createWorkbook(new File(realPath));
WritableSheet sheet = book.createSheet(SHEETNAME, 0);
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD,
false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
jxl.write.WritableCellFormat format = new jxl.write.WritableCellFormat(wfc);
format.setAlignment(jxl.format.Alignment.JUSTIFY);
format.setBackground(jxl.format.Colour.GRAY_25);
for(int i=0; i<=14; i++)sheet.setColumnView(i, 18);
sheet.addCell(new Label(startColumn, firstRow, "ORIGIN", format));
sheet.addCell(new Label(startColumn+1, firstRow, "PRICEBOOK", format));
sheet.addCell(new Label(startColumn+2, firstRow, "ITEMNO", format));
sheet.addCell(new Label(startColumn+3, firstRow, "CURRENCY", format));
sheet.addCell(new Label(startColumn+4, firstRow, "MINQTY", format));
sheet.addCell(new Label(startColumn+5, firstRow, "QTYUNIT", format));
sheet.addCell(new Label(startColumn+6, firstRow, "EFFECTIVEDATE", format));
sheet.addCell(new Label(startColumn+7, firstRow, "EXPIREDDATE", format));
sheet.addCell(new Label(startColumn+8, firstRow, "PRICE", format));
sheet.addCell(new Label(startColumn+9, firstRow, "FORQUOTATION", format));
sheet.addCell(new Label(startColumn+10, firstRow, "ISDEFAULTBOOK", format));
sheet.addCell(new Label(startColumn+11, firstRow, "ENTRYDATE", format));
sheet.addCell(new Label(startColumn+12, firstRow, "REMARK", format));
sheet.addCell(new Label(startColumn+13, firstRow, "PRICEBOOKID", format));
sheet.addCell(new Label(startColumn+14, firstRow, "PROJECTCODE", format));
writeToSheet(sheet, list);
book.write();
book.close();
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException ee) {
ee.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
public String downloadPriceBookInfo(List<PriceBook> list){
String path = "/secure/printReport/";
String fileName = projectCode + "_" + (new Date()).getTime()+ ".xls";
String slash = GlobUtil.getSlash();
String realPath = GlobUtil.getRealPath("secure"+slash+"printReport" + slash +fileName);
genDownLoadPriceBookInfo(realPath, list);
return path+fileName;
}
/**
* @param itemMasterDao 要设置的 itemMasterDao
*/
public void setItemMasterDao(ItemMasterDAO itemMasterDao) {
this.itemMasterDao = itemMasterDao;
}
/**
* @return priceBookDao
*/
public PriceBookDAO getPriceBookDao() {
return priceBookDao;
}
/**
* @return set
*/
public HashSet<String> getSet() {
return set;
}
/**
* @return projectCode
*/
public String getProjectCode() {
return projectCode;
}
/**
* @return dateFormat
*/
public jxl.write.DateFormat getDateFormat() {
return dateFormat;
}
/**
* @return cellFormat
*/
public jxl.write.WritableCellFormat getCellFormat() {
return cellFormat;
}
/**
* @return priceBookCodeMap
*/
public Map<String, PriceBookCode> getPriceBookCodeMap() {
return priceBookCodeMap;
}
/**
* @return priceBookCodeDao
*/
public PriceBookCodeDAO getPriceBookCodeDao() {
return priceBookCodeDao;
}
/**
* @return timeZone
*/
public String getTimeZone() {
return timeZone;
}
}
