txttoxlslocal.java

运行于linux下的一个文本文件到excel文件和转换包,可用于参考

/**Copyright @ 2007 Shenzhen HGC
 * All right reserved.
 * witer by victor li 2007-5-21
 */

/**
 * this class TxtToXls is used for copy content from txt format file to xls format file. 
 * so you must specify the txt file path(sourceFile) and xls file path(targetFile)
 * if the xls file not exist,it will create new xls file
 * else the old xls file will be instand of new xls file
 * */
import java.io.*;

import jxl.*;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.apache.log4j.*;

public class TxtToXlsLocal {
	int[] UnitLong = null;

	/**
	 * how long of the fileld in the txt file, if it's not the last fileld , it
	 * must include the separator blank,the separator blank ocuppy one character
	 */
	Logger log = null;

	/**
	 * the constructor of this class specify the log's parameters
	 */
	public TxtToXlsLocal() {
		PropertyConfigurator.configure(this.getClass().getClassLoader()
				.getResource("log4j.properties"));
		log = Logger.getLogger(TxtToXlsLocal.class);
	}

	/**
	 * this method is important, before you invoke the CreateXls method you must
	 * specify it's UnitLong
	 */
	public void ColumnSetting(int[] setting) {
		UnitLong = setting;
	}

	/**
	 * this method not be used
	 * 
	 * @param fileName:
	 *            the name of txt file belong
	 * @return full path of the txt file
	 */
	public String getTXTpath(String fileName) {
		String path = null;
		path = "/home/oiss/matrix";// this.getClass().getClassLoader().getResource("txt").getPath();
		path += "/" + fileName;
		log.info("\ngetTXTpath:" + path + "\n");
		return path;
	}

	/**
	 * 
	 * @param fileName:
	 *            the name of xls file
	 * @return full path of the xls file
	 */
	public String getXLSpath(String fileName) {
		String path = null;
		// path=this.getClass().getClassLoader().getResource("xls").getPath();
		path = "/home/javapro/pro/txt2xls/xls/" + fileName;
		log.info("\ngetXLSpath:" + path + "\n");
		return path;
	}

	/**
	 * creat cell, and then set value to the cell, then add it to appoited
	 * jxl.write.WritableSheet
	 * 
	 * @param iftitle:
	 *            if title:ture else : false
	 * @param ws :
	 *            the jxl.write.WritableSheet that you want add the cell to
	 * @param column:
	 *            column no
	 * @param row:
	 *            row no
	 * @param value:the
	 *            cell's value
	 */
	private final void CreatCell(boolean iftitle,int columnLong, jxl.write.WritableSheet ws,
			int column, int row, String value) {
		/**
		 * set the formate of the cell it just for the head of xls table, the
		 * content's format just set nothing, it's default
		 */
		jxl.write.WritableFont FontFormat = null;
		jxl.write.WritableCellFormat CellFormat = null;
		if (iftitle) {
			try {
				FontFormat = new jxl.write.WritableFont(
						jxl.write.WritableFont.ARIAL, 10,
						jxl.write.WritableFont.BOLD, false);
				FontFormat.setColour(jxl.format.Colour.BLACK);
				
				CellFormat = new jxl.write.WritableCellFormat(FontFormat);
				CellFormat.setBackground(jxl.format.Colour.GRAY_25);			
			ws.setColumnView(column, columnLong);//set column long
			} catch (WriteException e1) {
				log.error("\nerror in Creat Cell Format:" + e1.getMessage());
			}
		} else {
			FontFormat = new jxl.write.WritableFont(
					jxl.write.WritableFont.ARIAL, 10,
					jxl.write.WritableFont.NO_BOLD, false);
			CellFormat = new jxl.write.WritableCellFormat(FontFormat);
		}
		/** Creat a Label used upwards cellformat */
		jxl.write.Label l = new jxl.write.Label(column, row, value, CellFormat);
		/** add Label */
		try {
			ws.addCell(l);
		} catch (RowsExceededException e) {
			log.error("\nerror in add cell RowsExceededException:"
					+ e.getMessage());
		} catch (WriteException e) {
			log.error("\nerror in add cell WriteException :" + e.getMessage());
		}
	}

	/**
	 * this method use for write content to a WritableShee
	 * 
	 * @param wsheet
	 *            the jxl.write.WritableSheet you want to write
	 * @param txtSourceFileBuffer
	 *            BufferedReader for read txt source file
	 * @throws IOException
	 */
	private final void writeContent(jxl.write.WritableSheet wsheet,
			BufferedReader txtSourceFileBuffer) throws IOException {
		ColumnSetting(getColumnLong(txtSourceFileBuffer)); 
		/**
		 * StringBegin: start char index of column StringEnd: end char index of
		 * column
		 */
		jxl.SheetSettings st=wsheet.getSettings();//write as :new jxl.SheetSettings(wsheet) it's wrong
          st.setVerticalFreeze(1);
         // st.setDefaultColumnWidth(arg0)
		int columnId = 0, StringBegin = 0, StringEnd = 0;
		int RowId = 1;// Row Id of the content of xls table
		String content = null; // the content read from txt file
		txtSourceFileBuffer.readLine(); // read the frist line,but it is
		
		// empty,so doff it
		String header = txtSourceFileBuffer.readLine(); // the row2 is store the
		// head of the column
		for (columnId = 0; columnId < UnitLong.length; columnId++) {
			StringEnd = UnitLong[columnId];
			if (header.length() >= StringBegin + StringEnd) {
				CreatCell(true,StringEnd, wsheet, columnId, 0, header.substring(StringBegin,
						StringBegin + StringEnd));
				StringBegin += StringEnd; //
			} else {// if it is the last row of the table
				CreatCell(true,StringEnd, wsheet, columnId, 0, header.substring(StringBegin,
						header.length()));
			}
		}

		txtSourceFileBuffer.readLine();// doff the row3
		String tempString=null;
		while (txtSourceFileBuffer.ready()) {
			content = txtSourceFileBuffer.readLine();
			StringBegin = 0;
			/** this for loop is the some with the for loop before */
			for (columnId = 0; columnId < UnitLong.length; columnId++) {
				StringEnd = UnitLong[columnId];
				if (content.length() >= StringBegin + StringEnd) {
					tempString=content.substring(StringBegin, StringBegin+ StringEnd);
					if(tempString.indexOf("rows selected")<0){
						CreatCell(false, StringEnd, wsheet, columnId, RowId,
								tempString);
	                  }
				} else if(content.length()>StringBegin) {// if it is the last row of the table
					tempString=content.substring(StringBegin, content.length());
					 if(tempString.indexOf("rows selected")<0){
						 CreatCell(false, StringEnd, wsheet, columnId, RowId,
								 tempString);
	                  }
				}
				StringBegin += StringEnd;
			}
			RowId++;

		}
		log.info("\ncreat sum row:" + RowId);
	}

	/**
	 * the main method from txt to xls
	 * 
	 * @param sourceFile
	 *            the txt source file name(String)
	 * @param targetFile
	 *            the xls target file name(String)
	 * @throws IOException
	 */
	private final void CreateXls(String sourceFile, String targetFile)
			throws IOException {
		log.info("\n begin the job:");

		log.info("\n Create the target XLS file:" + targetFile);
		OutputStream targetOs = new FileOutputStream(targetFile); // target
		// xls file
		// OutputStream
		jxl.write.WritableWorkbook XlsWorkbook = Workbook // create new
				// workbook form
				// targetos
				.createWorkbook(targetOs);
		jxl.write.WritableSheet wsheet = XlsWorkbook.createSheet("sheet1", 0); // create
		// new
		// XlsWorkbook
		// from
		// XlsWorkbook
		FileReader txtSourceFileReader = null;
		txtSourceFileReader = new FileReader(sourceFile); // read the txt
		// source file
		BufferedReader txtSourceFileBuffer = new BufferedReader(
				txtSourceFileReader);// create BufferedReader form
		// SourceFileReader
		log.info("\nread the source txt file :" + sourceFile);
		writeContent(wsheet, txtSourceFileBuffer);
		/**
		 * release the memory
		 */

		XlsWorkbook.write();
		try {
			XlsWorkbook.close();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		targetOs.close();
		txtSourceFileBuffer.close();
		txtSourceFileReader.close();
		log.info("\njob finished");
	}
	
	 private int[] getColumnLong(BufferedReader txtSourceFileBuffer) throws IOException{
		System.out.print("\ngetColumnLong begin:");
		 int columnCount=0;
		 txtSourceFileBuffer.mark(1);		 
		 txtSourceFileBuffer.readLine();
		 txtSourceFileBuffer.readLine();
		 String line3 =txtSourceFileBuffer.readLine();
		for(int i=0;i<line3.length();i++){
			if(line3.charAt(i)==' ')
			{columnCount++;}
		}
		columnCount++;
		int[] columnLong=new int[columnCount];
		int beginIndex=0,endIndex=0;
		int ColumnNo=0;//current 
		while(ColumnNo<columnCount)
		{
			if(endIndex==line3.length())
			{columnLong[ColumnNo]=endIndex-beginIndex;
			break;
			}
			else if(line3.charAt(endIndex)==' ')
			{   endIndex++;
				columnLong[ColumnNo]=endIndex-beginIndex;
				ColumnNo++;
			beginIndex=endIndex;
			}
			endIndex++;
		}			
	 for(int i=0;i<columnLong.length;i++){System.out.print("\ncolumnLong:"+columnLong[i]);}
		  txtSourceFileBuffer.reset();
		  return columnLong;
	  }
	 /**
		 * input the paramter will creat a new xls file to oppinted path
		 * 		
		 * @param sourceFilePath
		 *            the txt file's full path
		 * @param targetFilePath
		 *            the xls file's full path
		 */
		public final void txtToxls( String sourceFilePath,
				String targetFilePath) {			
			try {
				CreateXls(sourceFilePath, targetFilePath);
			} catch (IOException e) {
				log.error("\nerror:" + e.getMessage());
			}
		}
		
		public final void  ColumnLong(BufferedReader txtSourceFileBuffer) throws IOException{
			txtSourceFileBuffer.mark(1);
			txtSourceFileBuffer.readLine();
			txtSourceFileBuffer.readLine();
			txtSourceFileBuffer.readLine();
			txtSourceFileBuffer.reset();
			txtSourceFileBuffer.readLine();
			System.out.print("\ntxtSourceFileBuffer.readLine();"+txtSourceFileBuffer.readLine());
			txtSourceFileBuffer.reset();
		}
	public static void main(String args[]) {
		TxtToXlsLocal TxtToXlsInstance = new TxtToXlsLocal();
		//int[] alasunitLong = { 16, 31, 50 };
		TxtToXlsInstance.txtToxls("D:\\project\\txt_to_xls\\alas_security_matrix.txt",
				"F:\\eclipse\\myproject\\TxtToXls\\xls\\ALAS User Role Security Matrix.xls");
		
		
		TxtToXlsInstance.txtToxls(
				"D:\\project\\txt_to_xls\\atoms_security_matrix.txt",
				"F:\\eclipse\\myproject\\TxtToXls\\xls\\HGC-OISS-D0047 ATOMS User Role Security Matrix.xls");
		
		//int[] tnssunitLong = { 16, 31, 50 };
		TxtToXlsInstance.txtToxls(
				"D:\\project\\txt_to_xls\\tnss_security_matrix.txt",
				"F:\\eclipse\\myproject\\TxtToXls\\xls\\HGC-OISS-D0052 TNSS User Role Security Matrix.xls");
		
		
	}
}