Mr.Gaara 's Blog

package net.moonstart.web.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.CellFormat;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import net.moonstart.web.model.Ms_alert;
import net.moonstart.web.model.device.Ms_device;
import net.moonstart.web.model.workshop.Ms_workshop;

public class ExcelUtils {
	static DecimalFormat decimalFormat = new DecimalFormat("###0.00");//格式化设置
 
	public static WritableWorkbook createTemplate(OutputStream output,
			int type, List<Ms_workshop> listw, List<Ms_device> listd,
			List<Ms_alert> lista) throws IOException, WriteException {
		WritableWorkbook writableWorkbook = Workbook.createWorkbook(output);
		WritableSheet wsheet = null;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		if (type == 1) {
			wsheet = writableWorkbook.createSheet("区域备份表", 0);
		} else if (type == 2) {
			wsheet = writableWorkbook.createSheet("设备备份表", 0);
		} else if (type == 3) {
			wsheet = writableWorkbook.createSheet("报警详情", 0);
		}

		CellFormat cf = writableWorkbook.getSheet(0).getCell(1, 0)
				.getCellFormat();
		WritableCellFormat wc = new WritableCellFormat();
		// 设置居中
		wc.setAlignment(Alignment.CENTRE);
		// 设置边框线
		// wc.setBorder(Border.ALL, BorderLineStyle.THIN);
		wc.setBackground(jxl.format.Colour.GREEN);

		if (type == 1) {
			for (int i = 0; i < listw.size(); i++) {
				Label nc0 = new Label(0, i, listw.get(i).getId() + "");
				Label nc1 = new Label(1, i, listw.get(i).getTag() + "");
				Label nc2 = new Label(2, i, listw.get(i).getTagLevel() + "");
				Label nc3 = new Label(3, i, listw.get(i).getParentId() + "");
				Label nc4 = new Label(4, i, listw.get(i).getIsDeleted() + "");
				Label nc5 = new Label(5, i, listw.get(i).getWarning() + "");
				Label nc6 = new Label(6, i, listw.get(i).getVarc1() + "");
				Label nc7 = new Label(7, i, listw.get(i).getVarc2() + "");
				Label nc8 = new Label(8, i, listw.get(i).getVarc3() + "");
				Label nc9 = new Label(9, i, listw.get(i).getVarc4() + "");
				Label nc10 = new Label(10, i, listw.get(i).getVarc5() + "");
				Label nc11 = new Label(11, i, listw.get(i).getno1() + "");
				Label nc12 = new Label(12, i, listw.get(i).getno2() + "");
				Label nc13 = new Label(13, i, format.format(listw.get(i)
						.getData1()) + "");
				Label nc14 = new Label(14, i, format.format(listw.get(i)
						.getData2()) + "");
				Label nc15 = new Label(15, i, format.format(listw.get(i)
						.getData1()) + "");
				Label nc16 = new Label(16, i, decimalFormat.format(listw.get(i).getNum1()) + "");
				Label nc17 = new Label(17, i, decimalFormat.format(listw.get(i).getNum2()) + "");
				Label nc18 = new Label(18, i, decimalFormat.format(listw.get(i).getNum3()) + "");
				Label nc19 = new Label(19, i, decimalFormat.format(listw.get(i).getNum4()) + "");
				Label nc20 = new Label(20, i, decimalFormat.format(listw.get(i).getNum5()) + "");
				Label nc21 = new Label(21, i, decimalFormat.format(listw.get(i).getNum6()) + "");
				Label nc22 = new Label(22, i, decimalFormat.format(listw.get(i).getNum7()) + "");
				Label nc23 = new Label(23, i, listw.get(i).getNum8() + "");
				Label nc24 = new Label(24, i, listw.get(i).getNum9() + "");
				Label nc25 = new Label(25, i, listw.get(i).getNum10() + "");
				Label nc26 = new Label(26, i, listw.get(i).getNum11() + "");
				Label nc27 = new Label(27, i, listw.get(i).getNum12() + "");
				wsheet.addCell(nc0);
				wsheet.addCell(nc1);
				wsheet.addCell(nc2);
				wsheet.addCell(nc3);
				wsheet.addCell(nc4);
				wsheet.addCell(nc5);
				wsheet.addCell(nc6);
				wsheet.addCell(nc7);
				wsheet.addCell(nc8);
				wsheet.addCell(nc9);
				wsheet.addCell(nc10);
				wsheet.addCell(nc11);
				wsheet.addCell(nc12);
				wsheet.addCell(nc13);
				wsheet.addCell(nc14);
				wsheet.addCell(nc15);
				wsheet.addCell(nc16);
				wsheet.addCell(nc17);
				wsheet.addCell(nc18);
				wsheet.addCell(nc19);
				wsheet.addCell(nc20);
				wsheet.addCell(nc21);
				wsheet.addCell(nc22);
				wsheet.addCell(nc23);
				wsheet.addCell(nc24);
				wsheet.addCell(nc25);
				wsheet.addCell(nc26);
				wsheet.addCell(nc27);

			}
		} else if (type == 2) {
			for (int i = 0; i < listd.size(); i++) {
				Label nc0 = new Label(0, i, listd.get(i).getTag() + "");
				Label nc1 = new Label(1, i, listd.get(i).getCategory() + "");
				Label nc2 = new Label(2, i, listd.get(i).getSn() + "");
				Label nc3 = new Label(3, i, listd.get(i).getState() + "");
				Label nc4 = new Label(4, i, listd.get(i).getIsDeleted() + "");
				Label nc5 = new Label(5, i, decimalFormat.format(listd.get(i).getWarning()) + "");
				Label nc6 = new Label(6, i, listd.get(i).getLevelWarning() + "");
				Label nc7 = new Label(7, i, listd.get(i).getTypeWarning() + "");
				Label nc8 = new Label(8, i, listd.get(i).getIsWarning() + "");
				Label nc9 = new Label(9, i, listd.get(i).getSubnetId() + "");
				Label nc10 = new Label(10, i, listd.get(i).getWorkshopID() + "");
				Label nc11 = new Label(11, i, listd.get(i).getSeqid() + "");
				Label nc12 = new Label(12, i, listd.get(i).getProtocol() + "");
				Label nc13 = new Label(13, i, listd.get(i).getParentSn() + "");
				Label nc14 = new Label(14, i, listd.get(i).getIsGw() + "");
				Label nc15 = new Label(15, i, listd.get(i).getAddress() + "");
				Label nc16 = new Label(16, i, listd.get(i).getBaud() + "");
				Label nc17 = new Label(17, i, listd.get(i).getRegisterType()
						+ "");
				Label nc18 = new Label(18, i, listd.get(i).getSensorFactory()
						+ "");
				Label nc19 = new Label(19, i, listd.get(i).getRegisterAddress()
						+ "");
				Label nc20 = new Label(20, i, listd.get(i).getSensorModel()
						+ "");
				Label nc21 = new Label(21, i, listd.get(i).getRegisterLength()
						+ "");
				Label nc22 = new Label(22, i, format.format(listd.get(i)
						.getCreatetime()) + "");
				Label nc23 = new Label(23, i, listd.get(i).getVarc1() + "");
				Label nc24 = new Label(24, i, listd.get(i).getVarc2() + "");
				Label nc25 = new Label(25, i, listd.get(i).getVarc3() + "");
				Label nc26 = new Label(26, i, listd.get(i).getVarc4() + "");
				Label nc27 = new Label(27, i, listd.get(i).getVarc5() + "");
				Label nc28 = new Label(28, i, listd.get(i).getno1() + "");
				Label nc29 = new Label(29, i, listd.get(i).getno2() + "");
				Label nc30 = new Label(30, i, format.format(listd.get(i)
						.getData1()) + "");
				Label nc31 = new Label(31, i, format.format(listd.get(i)
						.getData2()) + "");
				Label nc32 = new Label(32, i, format.format(listd.get(i)
						.getData3()) + "");
				Label nc33 = new Label(33, i, listd.get(i).getNum1() + "");
				Label nc34 = new Label(34, i, listd.get(i).getNum2() + "");
				Label nc35 = new Label(35, i, listd.get(i).getNum3() + "");
				Label nc36 = new Label(36, i, listd.get(i).getNum4() + "");
				Label nc37 = new Label(37, i, listd.get(i).getNum5() + "");
				Label nc38 = new Label(38, i, listd.get(i).getNum6() + "");
				Label nc39 = new Label(39, i, listd.get(i).getNum7() + "");
				wsheet.addCell(nc0);
				wsheet.addCell(nc1);
				wsheet.addCell(nc2);
				wsheet.addCell(nc3);
				wsheet.addCell(nc4);
				wsheet.addCell(nc5);
				wsheet.addCell(nc6);
				wsheet.addCell(nc7);
				wsheet.addCell(nc8);
				wsheet.addCell(nc9);
				wsheet.addCell(nc10);
				wsheet.addCell(nc11);
				wsheet.addCell(nc12);
				wsheet.addCell(nc13);
				wsheet.addCell(nc14);
				wsheet.addCell(nc15);
				wsheet.addCell(nc16);
				wsheet.addCell(nc17);
				wsheet.addCell(nc18);
				wsheet.addCell(nc19);
				wsheet.addCell(nc20);
				wsheet.addCell(nc21);
				wsheet.addCell(nc22);
				wsheet.addCell(nc23);
				wsheet.addCell(nc24);
				wsheet.addCell(nc25);
				wsheet.addCell(nc26);
				wsheet.addCell(nc27);
				wsheet.addCell(nc28);
				wsheet.addCell(nc29);
				wsheet.addCell(nc30);
				wsheet.addCell(nc31);
				wsheet.addCell(nc32);
				wsheet.addCell(nc33);
				wsheet.addCell(nc34);
				wsheet.addCell(nc35);
				wsheet.addCell(nc36);
				wsheet.addCell(nc37);
				wsheet.addCell(nc38);
				wsheet.addCell(nc39);
			}
		} else if (type == 3) {
			Label nc0 = new Label(0, 0, "ID");
			Label nc1 = new Label(1, 0, "SN号/区域");
			Label nc2 = new Label(2, 0, "报警信息");
			Label nc3 = new Label(3, 0, "报警时间");
			Label nc4 = new Label(4, 0, "设备名称");
			Label nc5 = new Label(5, 0, "报警种类");
			Label nc6 = new Label(6, 0, "报警删除标志");
			Label nc7 = new Label(7, 0, "设备/区域标志");
			Label nc8 = new Label(8, 0, "累计值");
			Label nc9 = new Label(9, 0, "预警值");
			wsheet.addCell(nc0);
			wsheet.addCell(nc1);
			wsheet.addCell(nc2);
			wsheet.addCell(nc3);
			wsheet.addCell(nc4);
			wsheet.addCell(nc5);
			wsheet.addCell(nc6);
			wsheet.addCell(nc7);
			wsheet.addCell(nc8);
			wsheet.addCell(nc9);
			for (int i = 0; i < lista.size(); i++) {
				Label nc00 = new Label(0, i + 1, lista.get(i).getId() + "");
				Label nc11 = new Label(1, i + 1, lista.get(i).getSn() + "");
				Label nc22 = new Label(2, i + 1, lista.get(i).getMessage() + "");
				Label nc33 = new Label(3, i + 1, lista.get(i).getSigntime()
						+ "");
				Label nc44 = new Label(4, i + 1, lista.get(i).getLocation()
						+ "");
				Label nc55 = new Label(5, i + 1, lista.get(i).getCategory()
						+ "" + "");
				Label nc66 = null;
				if (lista.get(i).getNo1() == 1) {
					nc66 = new Label(6, i + 1, "正常");
				} else {
					nc66 = new Label(6, i + 1, "已删除");
				}
				Label nc77 = null;
				if (lista.get(i).getNo2() == 1) {
					nc77 = new Label(7, i + 1, "设备报警");
				} else {
					nc77 = new Label(7, i + 1, "区域报警");
				}

				Label nc88 = new Label(8, i + 1, decimalFormat.format(lista.get(i).getNum1()) + "");
				Label nc99 = new Label(9, i + 1, decimalFormat.format(lista.get(i).getNum2()) + "");
				wsheet.addCell(nc00);
				wsheet.addCell(nc11);
				wsheet.addCell(nc22);
				wsheet.addCell(nc33);
				wsheet.addCell(nc44);
				wsheet.addCell(nc55);
				wsheet.addCell(nc66);
				wsheet.addCell(nc77);
				wsheet.addCell(nc88);
				wsheet.addCell(nc99);
			}
		}
		return writableWorkbook;
	}
}