excleserviceimpl.java

SSH示范

package com.iplan.portal.order.service;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.commons.beanutils.BasicDynaBean;

import com.iplan.portal.framework.base.BaseService;

/**
 * http://www.hao-se.cn
 * 
 * @author ws
 */
public class ExcleServiceImpl extends BaseService implements IExcleService {
	/** 行 * */
	private static final int ROW = 6;

	/** 列 * */
	private static final int COL = 0;

	public int makeExcle(List okExcle, List noExcle, String sourceFile,
			String aimFile, String areaName) {
		if (okExcle.isEmpty() && noExcle.isEmpty()) {
			return -1;
		}

		int result = 1;
		try {
			Workbook workbook = Workbook.getWorkbook(new File(sourceFile));
			// 拷贝一份excle模版文件
			WritableWorkbook copy = Workbook.createWorkbook(new File(aimFile),
					workbook);
			WritableSheet sheet = null;

			// 添加的字体样式
			WritableFont wf = new WritableFont(WritableFont.TIMES, 16,
					WritableFont.BOLD, true);
			WritableCellFormat wcfF = new WritableCellFormat(wf);

			WritableFont wfTitle = new WritableFont(WritableFont.ARIAL, 12);			
			WritableCellFormat wcfFTitle = new WritableCellFormat(wfTitle);
			wcfFTitle.setBackground(Colour.PALE_BLUE);
			wcfFTitle.setBorder(Border.ALL, BorderLineStyle.THIN);
			
			// 做成处理完毕订单的excle
			sheet = copy.getSheet(1);
			Label labelOK = new Label(2, 1, areaName, wcfF);
			sheet.addCell(labelOK);
			sheet.addCell(new Label(0, 4, "单号", wcfFTitle));
			sheet.addCell(new Label(1, 4, "订单日期", wcfFTitle));
			sheet.addCell(new Label(2, 4, "客户名称（工程）", wcfFTitle));
			sheet.addCell(new Label(3, 4, "发货仓库", wcfFTitle));
			sheet.addCell(new Label(4, 4, "打印装箱单时间", wcfFTitle));

			makeOKExcle(okExcle, sheet);

			// 做成有问题订单的excle
			sheet = copy.getSheet(0);
			Label labelNO = new Label(4, 1, areaName, wcfF);
			sheet.addCell(labelNO);
			sheet.addCell(new Label(0, 4, "单号", wcfFTitle));
			sheet.addCell(new Label(1, 4, "订单日期", wcfFTitle));
			sheet.addCell(new Label(2, 4, "客户名称（工程）", wcfFTitle));
			sheet.addCell(new Label(3, 4, "发货仓库", wcfFTitle));
			sheet.addCell(new Label(4, 4, "输单日期", wcfFTitle));
			sheet.addCell(new Label(5, 4, "生效时间", wcfFTitle));
			sheet.addCell(new Label(6, 4, "打印装箱单时间", wcfFTitle));
			sheet.addCell(new Label(7, 4, "缺货情况", wcfFTitle));
			sheet.addCell(new Label(8, 4, "数量", wcfFTitle));
			sheet.addCell(new Label(9, 4, "追踪", wcfFTitle));
			sheet.addCell(new Label(10, 4, "备注", wcfFTitle));

			makeNGExcle(noExcle, sheet);

			copy.write();
			copy.close();
			workbook.close();
		} catch (Exception e) {
			result = 0;
			logger.error("Method:makeExcle(...):做成Excle失败！", e);
		}

		return result;
	}

	private void makeOKExcle(List list, WritableSheet sheet) {
		BasicDynaBean bean;
		List valueList = new ArrayList();
		Label label = null;

		String orderno = "";
		String orderdate = "";
		String customername = "";
		String warehouseid = "";
		String printingdate = "";
		String handmade = "";
		String handmadeFlg = "";

		if (list.isEmpty()) {
			return;
		}

		try {
			for (int i = 0; i < list.size(); i++) {
				bean = (BasicDynaBean) list.get(i);
				orderno = convert(bean.get("orderno"));
				orderdate = convert(bean.get("orderdate"));
				customername = convert(bean.get("customername"));
				warehouseid = convert(bean.get("warehouseid"));
				printingdate = convert(bean.get("printingdate"));
				handmade = convert(bean.get("handmade"));
				handmadeFlg = convert(bean.get("handmadeflg"));

				valueList.add(orderno);
				valueList.add(orderdate);
				valueList.add(customername);
				valueList.add(warehouseid);
				if (handmadeFlg != null && "OK".equals(handmadeFlg)) {
					valueList.add(handmade);
				} else {
					valueList.add(printingdate);
				}
				for (int j = 0; j < valueList.size(); j++) {
					label = new Label(COL + j, ROW + i, (String) valueList
							.get(j));
					sheet.addCell(label);
				}
				valueList.clear();
			}
		} catch (Exception e) {
			logger.error("Method:makeOKExcle(...):做成处理完毕的订单Excle失败！", e);
		}
	}

	private void makeNGExcle(List list, WritableSheet sheet) {
		BasicDynaBean bean;
		List valueList = new ArrayList();
		Label label = null;

		String orderno = "";
		String orderdate = "";
		String customername = "";
		String warehouseid = "";
		String inputdate = "";
		String effectiveinfo = "";
		String printingdate = "";
		String shorting = "";
		String amount = "";
		String track = "";
		String remark = "";
		String handmade = "";
		String handmadeFlg = "";

		if (list.isEmpty()) {
			return;
		}

		try {
			for (int i = 0; i < list.size(); i++) {
				bean = (BasicDynaBean) list.get(i);
				orderno = convert(bean.get("orderno"));
				orderdate = convert(bean.get("orderdate"));
				customername = convert(bean.get("customername"));
				warehouseid = convert(bean.get("warehouseid"));
				inputdate = convert(bean.get("inputdate"));
				effectiveinfo = convert(bean.get("effectiveinfo"));
				printingdate = convert(bean.get("printingdate"));
				shorting = convert(bean.get("shorting"));
				amount = convert(bean.get("amount"));
				track = convert(bean.get("track"));
				remark = convert(bean.get("remark"));
				handmade = convert(bean.get("handmade"));
				handmadeFlg = convert(bean.get("handmadeflg"));

				valueList.add(orderno);
				valueList.add(orderdate);
				valueList.add(customername);
				valueList.add(warehouseid);
				valueList.add(inputdate);
				valueList.add(effectiveinfo);
				if (handmadeFlg != null && "OK".equals(handmadeFlg)) {
					valueList.add(handmade);
				} else {
					valueList.add(printingdate);
				}
				valueList.add(shorting);
				valueList.add(amount);
				valueList.add(track);
				valueList.add(remark);

				for (int j = 0; j < valueList.size(); j++) {
					label = new Label(COL + j, ROW + i, (String) valueList
							.get(j));
					sheet.addCell(label);
				}
				valueList.clear();
			}
		} catch (Exception e) {
			logger.error("Method:makeNGExcle(...):做成尚未处理完毕的订单Excle失败！", e);
		}
	}

	private String convert(Object object) {
		String str = "";
		if (object != null && !"".equals(object)) {
			str = object.toString();
		}
		return str;
	}
}