package com.vci.client.common.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 void writeExcelDocument(String path, String name, SheetDataSet dataSet) throws VCIError, IOException {
		// 打开工作簿
		Workbook workBook = makeWorkbook(name);
		// sheet

		// 创建sheet
		Sheet sheet = workBook.createSheet();
		// 设置sheet名称
		workBook.setSheetName(0, 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]);
			}
		}

		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 getCellValue(row.getCell(column));
	}

	public static String getCellValue(Cell cell) {
		String cellValueString = "";
		if (cell == null)
			return cellValueString;
		int cellType = cell.getCellType();
		SimpleDateFormat sdfDateTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		// add by xchao 2012.11.22 处理日期类型和Cell
		if (cellType == Cell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)) {
			Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
			cellValueString = sdfDateTime.format(date);
		} else if (cellType == Cell.CELL_TYPE_BLANK) {
			cellValueString = "";
		} else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
			cellValueString = cell.getBooleanCellValue() ? "1" : "0";
		} else if (cellType == Cell.CELL_TYPE_ERROR) {
			cellValueString = "";
		} else if (cellType == Cell.CELL_TYPE_FORMULA) {
			try {
				cellValueString = String.valueOf(cell.getNumericCellValue());
			} catch (Exception e) {
				cellValueString = cell.getRichStringCellValue().getString();
			}
		} else if (cellType == Cell.CELL_TYPE_NUMERIC) {
//			String[] dataFormatStrings = cell.getCellStyle().getDataFormatString().replace("_", "").trim().split("\\.");
//			int maxFracDigits = 0;
//			if(dataFormatStrings.length == 2){
//				maxFracDigits = dataFormatStrings[1].length();
//				numFormat.setMaximumFractionDigits(maxFracDigits);
//				cellValueString = numFormat.format(cell.getNumericCellValue()).replace(",", "").trim();
//			} else {
//				cellValueString = cell.toString();
//			}
			// cell.setCellType(Cell.CELL_TYPE_STRING);
			// cellValueString = cell.getStringCellValue();
			DecimalFormat df = new DecimalFormat("#.#########");
			cellValueString = df.format(cell.getNumericCellValue());
		} else if (cellType == Cell.CELL_TYPE_STRING) {
			// 移除数据列中前可在存在的"'"
			String val = cell.getStringCellValue();
			while (val.startsWith("'")) {
				val = val.substring(1);
			}
			cellValueString = val;
		}
		return cellValueString;
	}

	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();
		}
	}

}