package com.happy.Until;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import java.io.File;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Vector;

public class DBtoExcel {
	/**
	 * 导出Excel表
	 * 
	 * @param rs
	 *            数据库结果集
	 * @param filePath
	 *            要保存的路径，文件名为 fileName.xls
	 * @param sheetName
	 *            工作簿名称 工作簿名称，本方法目前只支持导出一个Excel工作簿
	 * @param columnName
	 *            列名，类型为Vector
	 */
	public void WriteExcel(ResultSet rs, String filePath, String sheetName,
			Vector columnName) {
		WritableWorkbook workbook = null;
		WritableSheet sheet = null;

		int rowNum = 1; // 从第2行开始写入
		try {
			workbook = Workbook.createWorkbook(new File(filePath)); // 创建Excel文件
			sheet = workbook.createSheet(sheetName, 0); // 创建名为 sheetName 的工作簿

			this.writeCol(sheet, columnName, 0); // 首先将列名写入第一行

			// 将结果集写入
			while (rs.next()) {
				Vector col = new Vector(); // 用以保存一行数据

				for (int i = 1; i <= columnName.size(); i++) { // 将一行内容保存在col中
					col.add(rs.getString(i));
				}
				// 写入Excel
				this.writeCol(sheet, col, rowNum++);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 关闭
				workbook.write();
				workbook.close();
				rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public void WriteExcel2(ResultSet rs, String filePath, String sheetName,
						   Vector columnName, String time1, String time2) {
		WritableWorkbook workbook = null;
		WritableSheet sheet = null;

		int rowNum = 2; // 从第一行开始写入
		try {
			workbook = Workbook.createWorkbook(new File(filePath)); // 创建Excel文件
			sheet = workbook.createSheet(sheetName, 0); // 创建名为 sheetName 的工作簿

			this.writeCol(sheet, columnName, 1); // 首先将列名写入

			// 写入时间
			Vector col2 = new Vector(); // 用以保存一行数据
			col2.add("统计时间-："+time1+"到"+time2);
			this.writeCol(sheet, col2, 0);
			// 将结果集写入
			while (rs.next()) {
				Vector col = new Vector(); // 用以保存一行数据

				for (int i = 1; i <= columnName.size(); i++) { // 将一行内容保存在col中
					col.add(rs.getString(i));
				}
				// 写入Excel
				this.writeCol2(sheet, col, rowNum++);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 关闭
				workbook.write();
				workbook.close();
				rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public void WriteExcel4(ArrayList<ArrayList> aa, String filePath, String sheetName,
							Vector columnName, String time1, String time2) {
		WritableWorkbook workbook = null;
		WritableSheet sheet = null;

		int rowNum = 2; // 从第一行开始写入
		try {
			workbook = Workbook.createWorkbook(new File(filePath)); // 创建Excel文件
			sheet = workbook.createSheet(sheetName, 0); // 创建名为 sheetName 的工作簿

			this.writeCol(sheet, columnName, 1); // 首先将列名写入

			// 写入时间
			Vector col2 = new Vector(); // 用以保存一行数据
			col2.add("统计时间-："+time1+"到"+time2);
			this.writeCol(sheet, col2, 0);
			// 将结果集写入
			for (int i=0; i<aa.size(); i++){
				Vector col = new Vector(); // 用以保存一行数据

				for (int j = 0; j < aa.get(i).size(); j++) { // 将一行内容保存在col中
					col.add(aa.get(i).get(j));
				}
				// 写入Excel
				this.writeCol2(sheet, col, rowNum++);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 关闭
				workbook.write();
				workbook.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public void WriteExcel5(ArrayList<ArrayList<String>> aa, ArrayList<String> al, ArrayList<String> al2, ArrayList<String> al_1, String filePath, String sheetName,
							Vector columnName, String time1, String time2, String place) {
		WritableWorkbook workbook = null;
		WritableSheet sheet = null;

		int rowNum = 3; // 从第四行开始写入具体数据
		try {
			workbook = Workbook.createWorkbook(new File(filePath)); // 创建Excel文件
			sheet = workbook.createSheet(sheetName, 0); // 创建名为 sheetName 的工作簿

			this.writeCol(sheet, columnName, 1); // 首先第二行将列名写入

			// 写入时间
			Vector col2 = new Vector(); // 用以保存一行数据
			col2.add("统计时间-："+time1+"到"+time2);
			col2.add(" ");
			col2.add(" ");
			col2.add(" ");
			col2.add(" ");
			col2.add("订单号："+place);
			this.writeCol(sheet, col2, 0);    // 第一行写入时间、订单号
			// 将结果集写入
			for (int i=0; i<aa.size(); i++){
				Vector col = new Vector(); // 用以保存一行数据
				for (int j = 0; j < aa.get(i).size(); j++) { // 将一行内容保存在col中
					col.add(aa.get(i).get(j));
				}
				// 写入Excel
				this.writeCol2(sheet, col, rowNum++);
			}

			Vector col3 = new Vector();
			col3.add("-价格");
			for (int j=0; j<al.size(); j++){
				col3.add(al.get(j).toString());
			}
			this.writeCol2(sheet, col3, 2);

			Vector col4 = new Vector();
			col4.add("剩余数-");
			for (int j=0; j<al2.size(); j++){
				col4.add(al2.get(j).toString());
			}
			this.writeCol2(sheet, col4, rowNum+1);

			Vector col5 = new Vector();
			col5.add("走货数-");
			for (int j=0; j<al_1.size(); j++){
				col5.add(al_1.get(j).toString());
			}
			this.writeCol2(sheet, col5, rowNum);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 关闭
				workbook.write();
				workbook.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/***
	 * 将数组写入工作簿
	 * 
	 * @param sheet
	 *            要写入的工作簿
	 * @param col
	 *            要写入的数据数组
	 * @param rowNum
	 *            要写入哪一行
	 * @throws WriteException
	 * @throws RowsExceededException
	 */
	private void writeCol(WritableSheet sheet, Vector col, int rowNum)
			throws RowsExceededException, WriteException {
		int size = col.size(); // 获取集合大小

		for (int i = 0; i < size; i++) { // 写入每一列
			Label label = new Label(i, rowNum, (String) col.get(i));
			sheet.addCell(label);
		}
	}

	// 处理工号和姓名的算法
	private void writeCol2(WritableSheet sheet, Vector col, int rowNum)
			throws RowsExceededException, WriteException {
		int size = col.size(); // 获取集合大小

		for (int i = 0; i < size; i++) { // 写入每一列
			if(i == 0) {
				String name1 = (String) col.get(i);
				int n = name1.indexOf("-");
				String name2 = name1.substring(0, n);
				Label label = new Label(i, rowNum, name2);
				sheet.addCell(label);
			}else{
				Label label = new Label(i, rowNum, (String) col.get(i));
				sheet.addCell(label);
			}
		}
	}
}