package com.report;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import jxl.Cell;
import jxl.CellType;
import jxl.CellView;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class JXLMergerWriteExcel {
	private WritableCellFormat times;
	private String createFilePath;//合成文件存放路径
	private int beginMergerColumnIndex=0;//开始合并的列
	private int endMerGerColumnIndex;//结束的合并的列
	//private long headRowIndex;//列头所在位置
	private int beginMergerRow=1;//开始合并的行标
	private int pageCount=0;
	private List<String> columns=null;
	private String dirPath;
	private int mergerRowBeginIndex=0;
	public JXLMergerWriteExcel(String createFilePath,String path){
		this.createFilePath = createFilePath;
		this.dirPath=path;
	}
	public JXLMergerWriteExcel(String createFilePath,String path,int beginMergerRow){
		this.createFilePath = createFilePath;
		this.dirPath=path;
		this.beginMergerRow=beginMergerRow;
	}
	public JXLMergerWriteExcel(String createFilePath,String path,int beginMergerColumnIndex,int endMerGerColumnIndex,int beginMergerRow){
		this.createFilePath = createFilePath;
		this.dirPath=path;
		this.beginMergerColumnIndex=beginMergerColumnIndex;
		this.endMerGerColumnIndex=endMerGerColumnIndex;
		this.beginMergerRow=beginMergerRow;
	}    
	/*******
	 * 写文件
	 * @throws IOException 
	 * @throws WriteException 
	 * @throws Exception
	 */
	public String mergerExcel()   {
		WritableWorkbook workbook=null;
		try{
			if(createFilePath==null){
				return "请输入创建文件路径";
			}
			if(dirPath==null){
				return "请输入被合并文件夹的路径";
			}
			File file = new File(createFilePath);
			WorkbookSettings wbSettings = new WorkbookSettings();
			wbSettings.setLocale(new Locale("en", "EN"));
			workbook = Workbook.createWorkbook(file, wbSettings);
			workbook.createSheet("Report_"+pageCount, pageCount);
			WritableSheet excelSheet = workbook.getSheet(pageCount);
			this.beginMergerColumn(excelSheet);
			workbook.write();
			return "合并成功";    
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(workbook!=null){
				try {
					workbook.close();
				} catch (WriteException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	/*****
	 * 开始合并文件
	 * @param excelSheet
	 * @param dirPath
	 */
	private void beginMergerColumn(WritableSheet excelSheet ){
		File srcFile = new File(dirPath);
		boolean bFile = srcFile.exists();        

		if (!bFile || !srcFile.isDirectory() || !srcFile.canRead()) {
			try {
				srcFile.createNewFile();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}else{
			File[] file = srcFile.listFiles();
			for(int i=0;i<file.length;i++){
				String path=    file[i].getAbsolutePath();          
				if(path.indexOf(".xls")>0){
					if(columns==null){
						this.createHeader(excelSheet, path);
					}
					System.out.println("正在读入第"+i+"个文件:"+path+"----请稍等");        
					try {                        
						this.readSingleExcel(excelSheet,path);
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}            
				}
			}             
		}        
	}
	/*****
	 * 读取单个文件
	 * @param excelSheet
	 * @param singFilePath
	 */
	public void readSingleExcel(WritableSheet excelSheet,String singFilePath){
		File inputWorkbook = new File(singFilePath);
		Workbook w = null;
		try {
			w = Workbook.getWorkbook(inputWorkbook);
			// Get the first sheet
			Sheet sheet = w.getSheet(0);
			// Loop over first 10 column and lines    
			mergerRowBeginIndex=excelSheet.getRows();
			for (int i = beginMergerRow; i < sheet.getRows()-1; i++) {            
				for (int j = beginMergerColumnIndex; j < endMerGerColumnIndex; j++) {
					Cell cell = sheet.getCell(j, i);
					String cell_value=cell.getContents();    
					this.addLabel(excelSheet, j, mergerRowBeginIndex, cell_value);
				}
				this.addLabel(excelSheet, excelSheet.getColumns()-1, mergerRowBeginIndex, inputWorkbook.getName());
				mergerRowBeginIndex++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(w!=null){
				w.close();
			}
		}
	}
	/*****
	 * 创建表头
	 * @param excelSheet
	 * @param firstFilePath
	 */
	private void createHeader(WritableSheet excelSheet,String firstFilePath){
		int column=0;
		try{
			this.readFirstFileGetHeaders(firstFilePath);
			for(int i=beginMergerColumnIndex;i<endMerGerColumnIndex;i++){
				this.addLabel(excelSheet, column++, 0, columns.get(i));
			}
			this.addLabel(excelSheet, excelSheet.getColumns()-1, 0, "来源文件名称");
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	/*****
	 * 读取单个文件获取文件的表头信息
	 * @param filePath
	 */
	private void readFirstFileGetHeaders(String filePath){
		File inputWorkbook = new File(filePath);
		Workbook w = null;
		try {
			w = Workbook.getWorkbook(inputWorkbook);
			// Get the first sheet
			Sheet sheet = w.getSheet(0);
			// Loop over first 10 column and lines    
			columns=new ArrayList<String>();
			for (int i = 0; i < sheet.getRows(); i++) {
				for (int j = 0; j < sheet.getColumns(); j++) {
					Cell cell = sheet.getCell(j, i);
					String cell_value=cell.getContents();
					columns.add(cell_value);
				}

				endMerGerColumnIndex=sheet.getColumns();
				break;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(w!=null){
				w.close();
			}
		}
	}
	/*****
	 * 添加信息到excel中
	 * @param sheet
	 * @param column
	 * @param row
	 * @param s
	 * @throws WriteException
	 * @throws RowsExceededException
	 */
	private void addLabel(WritableSheet sheet, int column, int row, String s)
			throws WriteException, RowsExceededException {
		Label label;
		// Lets create a times font
		WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
		// Define the cell format
		times = new WritableCellFormat(times10pt);
		label = new Label(column, row, s, times);
		sheet.addCell(label);
	}

	public static void main(String[] args) throws Exception {
		//        for(int i=17;i<29;i++){
		//          JXLWriteExcel mergerExcel = new JXLWriteExcel("d:/合并文件/merger_"+i+".xls","D:/scopus20111124/"+i,2);
		//          String inf=mergerExcel.mergerExcel();
		//          System.out.println("第"+i+"批次文件"+inf);
		//        }
		//for(int i=4;i<15;i++){
		//	JXLMergerWriteExcel mergerExcel = new JXLMergerWriteExcel("d:/合并文件/merger_2_"+i+".xls","d:/合并文件/"+i,2);
		//	String inf=mergerExcel.mergerExcel();
		//	System.out.println("第"+i+"批次文件"+inf);
		//}
		//JXLMergerWriteExcelUtil mergerExcel = new JXLMergerWriteExcelUtil("F://sqlrun//bfny//内转四股泉原煤.xls","F://sqlrun//bfny//内转四股泉原煤综合.xls",1);
		JXLMergerWriteExcel mergerExcel = new JXLMergerWriteExcel("F:/sqlrun/bfny/合并文件.xls","F:/sqlrun/bfny",2);
		String inf=mergerExcel.mergerExcel();
		//String inf=mergerExcel.mergerExcel();
	}
}