package com.vci.client.utils.excel;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.vci.corba.common.VCIError;

public class ExcelDocumentUtils {
	/**
	 *写回到excel文档
	 * 
	 * @param sheetDataSet
	 *            <P>
	 *           数据集合
	 * @param path
	 *            <P>
	 *            excel文档的路径
	 * @param name
	 *            <P>
	 *            excel文档的名称
	 * @throws VCIError
	 * @throws IOException 
	 * */
	public static void writeExcelDocument(String path, String name,
			List<SheetDataSet> sheetDataSet) throws VCIError, IOException {
		// 打开工作簿
		Workbook workBook = makeWorkbook(name);
		// sheet
		for (int sheetIndex = 0; sheetIndex < sheetDataSet.size(); sheetIndex++) {
			// 创建sheet
			Sheet sheet = workBook.createSheet();
			SheetDataSet dataSet = sheetDataSet.get(sheetIndex);
			// 设置sheet名称
			workBook.setSheetName(sheetIndex, dataSet.getSheet());

			List<String[]> rowDataSet = dataSet.getDataSet();
			// 遍历
			for (int rowIndex = 0; rowIndex < rowDataSet.size(); rowIndex++) {
				// ��row
				Row row = sheet.getRow(rowIndex);
				if (row == null) {
					row = sheet.createRow(rowIndex);
				}
				String[] rowData = rowDataSet.get(rowIndex);
				//row
				for (int columnIndex = 0; columnIndex < rowData.length; columnIndex++) {
					// cell
					Cell cell = row.getCell(columnIndex);
					if (cell == null) {
						cell = row.createCell(columnIndex);
					}
					//赋ֵ
					cell.setCellValue(rowData[columnIndex]);
				}
			}
		}// end
		FileOutputStream fileOutputStream = makeFileOutputStream(path, name);
		workBook.write(fileOutputStream);
		fileOutputStream.close();
	}
	
	public static List<SheetDataSet> readExcelDocument(String name,InputStream fileInputStream) throws VCIError, IOException {
		return readExcelDocument(name, fileInputStream, false);
	}

	/**
	 * 读取excel文档的数据
	 * 
	 * @param FileInputStream
	 *            <P>
	 *            excel文档
	 * @param name
	 *            <P>
	 *            excel文档的名称
	 * @throws IOException 
	 * */
	public static List<SheetDataSet> readExcelDocument(String name,InputStream fileInputStream, boolean isDelNullRow)
			throws VCIError, IOException {
		InputStream bufferInputStream = getBufferInputStream(fileInputStream);
		// 打开工作簿
		Workbook workBook = getWorkbook(fileInputStream, bufferInputStream,
				name);
		// row
		int maxRow=0;
		List<SheetDataSet> dataSet = new ArrayList<SheetDataSet>();
		for (int sheetIndex = 0; sheetIndex < workBook.getNumberOfSheets(); sheetIndex++) {
			Sheet sheet = workBook.getSheetAt(sheetIndex);
			SheetDataSet sheetDataSet = new SheetDataSet();
			sheetDataSet.setSheet(sheet.getSheetName());
			dataSet.add(sheetDataSet);
			//遍历
			for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
				Row rowDataSet = sheet.getRow(rowIndex);

				// sheet
				if (rowDataSet == null) {
					if (isDelNullRow) {
						sheetDataSet.getDataSet().add(new String[0]);
					}
					continue;
				}
				int currentcolumnSize = rowDataSet.getLastCellNum();
				if(maxRow<currentcolumnSize){
					maxRow=currentcolumnSize;
				}
				// 
				String[] values = new String[maxRow];
				Arrays.fill(values, "");
				boolean hasValue = false;
				//遍历
				for (int columnIndex = 0; columnIndex < rowDataSet
						.getLastCellNum(); columnIndex++) {
					String value = getCellValue(columnIndex, rowDataSet);
					values[columnIndex] = rightTrim(value);
					hasValue = true;
				}
				if (hasValue) {
					sheetDataSet.getDataSet().add(values);
				}
			}
		}
		bufferInputStream.close();
		return dataSet;
	}

	
	/**
	 * 读取excel文档的数据
	 * 
	 * @param path
	 *            <P>
	 *            excel文档的路径
	 * @param name
	 *            <P>
	 *            excel文档的名称
	 * @throws IOException 
	 * */
	public static List<SheetDataSet> readExcelDocument(String path, String name)
			throws VCIError, IOException {
		FileInputStream fileInputStream = getFileInputStream(path, name);
		InputStream bufferInputStream = getBufferInputStream(fileInputStream);
		// 打开工作簿
		Workbook workBook = getWorkbook(fileInputStream, bufferInputStream,
				name);
		// row
		int maxRow=0;
		List<SheetDataSet> dataSet = new ArrayList<SheetDataSet>();
		for (int sheetIndex = 0; sheetIndex < workBook.getNumberOfSheets(); sheetIndex++) {
			Sheet sheet = workBook.getSheetAt(sheetIndex);
			SheetDataSet sheetDataSet = new SheetDataSet();
			sheetDataSet.setSheet(sheet.getSheetName());
			dataSet.add(sheetDataSet);
			//遍历
			for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
				Row rowDataSet = sheet.getRow(rowIndex);

				// sheet
				if (rowDataSet == null) {
					continue;
				}
				int currentcolumnSize = rowDataSet.getLastCellNum();
				// 
				if(maxRow<currentcolumnSize){
					maxRow=currentcolumnSize;
				}
				String[] values = new String[maxRow];
				Arrays.fill(values, "");
				boolean hasValue = false;
				//遍历
				for (int columnIndex = 0; columnIndex < rowDataSet
						.getLastCellNum(); columnIndex++) {
					String value = getCellValue(columnIndex, rowDataSet);
					if (columnIndex == 0 && value.trim().equals("")) {
						break;
					}
					values[columnIndex] = rightTrim(value);
					hasValue = true;
				}
				if (hasValue) {
					sheetDataSet.getDataSet().add(values);
				}
			}
		}
		bufferInputStream.close();
		return dataSet;
	}

	
	private static Workbook makeWorkbook(String name) throws VCIError {

		// 创建
		Workbook workBook = null;
		if (getSuffix(name).equals(".xls")) {
			workBook = new HSSFWorkbook();
		} else if (getSuffix(name).equals(".xlsx")) {
			workBook = new XSSFWorkbook();
		}
		return workBook;

	}

	private static FileOutputStream makeFileOutputStream(String path,
			String name) throws VCIError, FileNotFoundException {
		FileOutputStream fileOutputStream = new FileOutputStream(path + "\\"
				+ name);
		return fileOutputStream;
	}

	private static FileInputStream getFileInputStream(String path, String name)
			throws VCIError, FileNotFoundException {
		File xlsFile = new File(path + "\\" + name);
		FileInputStream fileInputStream = new FileInputStream(xlsFile);
		return fileInputStream;
	}

	private static BufferedInputStream getBufferInputStream(
			InputStream fileInputStream) throws VCIError {
		BufferedInputStream bufferInputStream = new BufferedInputStream(
				fileInputStream);
		return bufferInputStream;
	}

	/**
	 * 读取单元格的值
	 * */
	private static String getCellValue(int column, Row row) {
		return ExcelFileOperation.getCellValue(row.getCell(column));
//		Cell cell = row.getCell(column);
//		String value = "";
//		if (cell != null) {
//			// 依据类型
//			switch (cell.getCellType()) {
//			case Cell.CELL_TYPE_STRING:
//				value = cell.getStringCellValue();
//				break;
//			case Cell.CELL_TYPE_NUMERIC:
//				if (HSSFDateUtil.isCellDateFormatted(cell)) {
//					Date date = cell.getDateCellValue();
//					// 
//					if (date != null) {
//						value = new SimpleDateFormat("yyyy-MM-dd").format(date);
//					} else {
//						value = "";
//					}
//				} else {
//					value = new DecimalFormat("0").format(cell
//							.getNumericCellValue());
//				}
//				break;
//			case Cell.CELL_TYPE_FORMULA:
//				// 日期
//				if (cell.getStringCellValue().equals("")) {
//					value = cell.getNumericCellValue() + "";
//				} else {
//					value = cell.getStringCellValue();
//				}
//				break;
//			case Cell.CELL_TYPE_BLANK:
//				break;
//			case Cell.CELL_TYPE_ERROR:
//				value = "";
//				break;
//			case Cell.CELL_TYPE_BOOLEAN:
//				value = cell.getBooleanCellValue() == true ? "Y" : "N";
//				break;
//			default:
//				value = "";
//			}
//		}// cell
//		return value;
	}

	private static Workbook getWorkbook(InputStream fileInputStream,
			InputStream bufferInputStream, String name) throws VCIError, IOException {

		// 打开工作簿
		Workbook workBook = null;
		if (getSuffix(name).equals(".xls")) {
			POIFSFileSystem fileSystem = new POIFSFileSystem(bufferInputStream);
			workBook = new HSSFWorkbook(fileSystem);
		} else if (getSuffix(name).equals(".xlsx")) {
			workBook = new XSSFWorkbook(fileInputStream);
		}
		return workBook;

	}

	private static String getSuffix(String name) {
		int index = name.lastIndexOf(".");
		return name.substring(index, (name.length()));
	}

	/**
	 * ȥ���ַ��ұߵĿո�
	 * */
	private static String rightTrim(String value) {
		if (value == null) {
			return "";
		}
		int length = value.length();
		for (int i = length - 1; i >= 0; i--) {
			if (value.charAt(i) != 0x20) {
				break;
			}
			length--;
		}
		return value.substring(0, length);
	}

	public static void main(String args[]) throws IOException {
		try {
			List<SheetDataSet> sheetDataSet = readExcelDocument("D:\\",
					"a.xlsx");
			writeExcelDocument("D:\\", "b.xlsx", sheetDataSet);
		} catch (VCIError e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}