txttoxls.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 TxtToXls {
	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 TxtToXls() {
		PropertyConfigurator.configure(this.getClass().getClassLoader()
				.getResource("./lib/log4j.properties"));
		log = Logger.getLogger(TxtToXls.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 {
		
		/**
		 * 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) {
					if(content.substring(StringBegin, StringBegin+ StringEnd).indexOf("rows selected")<0){
						CreatCell(false, StringEnd, wsheet, columnId, RowId,
								content.substring(StringBegin, StringBegin
										+ StringEnd));
	                  }
				
				} else if(content.length()>StringBegin) {// if it is the last row of the table
					 if(content.substring(StringBegin, content.length()).indexOf("rows selected")<0){
						 CreatCell(false, StringEnd, wsheet, columnId, RowId,
									content.substring(StringBegin, content.length()));
	                  }
					
				}
				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
	 */
	public 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");
	}

	/**
	 * input the paramter will creat a new xls file to oppinted path
	 * 
	 * @param unitLong
	 *            the column long setting
	 * @param sourceFilePath
	 *            the txt file's full path
	 * @param targetFilePath
	 *            the xls file's full path
	 */
	public final void txtToxls(int[] unitLong, String sourceFilePath,
			String targetFilePath) {
		ColumnSetting(unitLong);
		try {
			CreateXls(sourceFilePath, targetFilePath);
		} catch (IOException e) {
			log.error("\nerror:" + e.getMessage());
		}
	}
 
	public static void main(String args[]) {
		TxtToXls TxtToXlsInstance = new TxtToXls();
		int[] alasunitLong = { 16, 31, 50 };
		TxtToXlsInstance.txtToxls(alasunitLong,
				"/home/oiss/matrix/alas_security_matrix.txt",
				"/home/javapro/pro/txt2xls/xls/ALAS User Role Security Matrix.xls");
		alasunitLong = null;
		int[] atomsunitLong = { 31, 101, 101, 100 };
		TxtToXlsInstance.txtToxls(atomsunitLong,
				"/home/oiss/matrix/atoms_security_matrix.txt",
				"/home/javapro/pro/txt2xls/xls/HGC-OISS-D0047 ATOMS User Role Security Matrix.xls");
		atomsunitLong = null;
		int[] tnssunitLong = { 16, 31, 50 };
		TxtToXlsInstance.txtToxls(tnssunitLong,
				"/home/oiss/matrix/tnss_security_matrix.txt",
				"/home/javapro/pro/txt2xls/xls/HGC-OISS-D0052 TNSS User Role Security Matrix.xls");
		tnssunitLong = null;
		
	}
}