jxl处理Excel文档

package com.zhaopin.xx;

import java.io.File;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.CellType;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.read.biff.BiffException;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;

public class Test {

	/**
	 * Get source string to rows. String[] is a row and element is cell.
	 * 
	 * @param sourceStr
	 * @return
	 */
	public List<String[]> getRows(String sourceStr) {
		List<String[]> result = new ArrayList<String[]>();
		String[] rows = StringUtils.split(sourceStr, "\n");
		if (!ArrayUtils.isEmpty(rows)) {
			for (String row : rows) {
				result.add(StringUtils.split(row, "\t"));
			}
		}
		return result;
	}

	public String process(String sourceStr, String readXlsFile, String writeXlsFile, int skipNum) throws IOException, BiffException, RowsExceededException, WriteException, ParseException {
		// Parameter check.
		if (StringUtils.isBlank(sourceStr)) {
			return "Parameter [sourceStr] must not be blank. need check.";
		}
		if (StringUtils.isBlank(readXlsFile)) {
			return "Parameter [readXlsFile] must not be blank. need check.";
		}
		if (StringUtils.isBlank(writeXlsFile)) {
			return "Parameter [writeXlsFile] must not be blank. need check.";
		}
		if (skipNum < 0) {
			return "Parameter [skipNum] must not be Less than zero. need check.";
		}

		// make a read only.
		jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(readXlsFile));
		// make a writable workbook.
		WritableWorkbook wwb = Workbook.createWorkbook(new File(writeXlsFile), rwb);
		// all sheets number.
		int numberOfSheets = wwb.getNumberOfSheets();
		if (numberOfSheets < 1) {
			return "Read Xls file:[" + readXlsFile + "] sheet is null or size is zero. need check.";
		}

		// Warp source rows data
		List<String[]> rowDatas = getRows(sourceStr);
		if (rowDatas.size() != (numberOfSheets - skipNum)) {
			return "Read Xls file:[" + readXlsFile + "] sheetSize:[" + numberOfSheets + "]-SkipNum:[" + skipNum + "] is not equal source dataRows:[" + rowDatas.size() + "]. need check.";
		}

		// if source rows size is same sheet size. begin process
		for (int i = 0; i < rowDatas.size(); i++) {
			String[] rowCellDatas = rowDatas.get(i);

			WritableSheet sheet = wwb.getSheet(skipNum + i);
			int totalRowNum = sheet.getRows();
			int totalColumnsNum = sheet.getColumns();

			for (int column = 0; column < totalColumnsNum; column++) {
				Cell frontCell = sheet.getCell(column, totalRowNum - 1);
				CellType frontCellType = frontCell.getType();
				CellFormat frontCellFormat = frontCell.getCellFormat();
				System.out.println("row:[" + (totalRowNum - 1) + "] column:[" + column + "] frontCell.type:[" + frontCellType + "]content:" + frontCell.getContents());
				if (frontCellType == CellType.DATE) {
					SimpleDateFormat dateFormat = new SimpleDateFormat("yy-m-d");
					jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(new jxl.write.DateFormat("yy-m-d"));
					jxl.write.DateTime labelDateTime = new jxl.write.DateTime(column, totalRowNum, dateFormat.parse(rowCellDatas[column]), wcfDF);
					labelDateTime.setCellFormat(frontCellFormat);
					sheet.addCell(labelDateTime);
				} else if (frontCellType == CellType.NUMBER) {
					String procAfter = StringUtils.remove(rowCellDatas[column], ',');
					jxl.write.Number labelNumber = new jxl.write.Number(column, totalRowNum, Double.parseDouble(procAfter));
					labelNumber.setCellFormat(frontCellFormat);
					sheet.addCell(labelNumber);
				} else if (frontCellType == CellType.LABEL) {
					jxl.write.Label label = new jxl.write.Label(column, totalRowNum, rowCellDatas[column]);
					label.setCellFormat(frontCellFormat);
					sheet.addCell(label);
				}
			}
		}

		wwb.write();
		if (wwb != null) {
			wwb.close();
		}

		return "SUCCESS";
	}

	/**
	 * @param args
	 * @throws IOException
	 * @throws BiffException
	 * @throws ParseException
	 * @throws WriteException
	 * @throws RowsExceededException
	 */
	public static void main(String[] args) throws BiffException, IOException, RowsExceededException, WriteException, ParseException {
		 Test test = new Test();
		
		 StringBuffer sourceBuffer = new StringBuffer();
		 sourceBuffer.append("10-3-11	1,026	20.3\n");
		 sourceBuffer.append("10-11-12	11,025	28.3\n");
		 sourceBuffer.append("10-10-12	21,085	20.35\n");
		
		 String readFile = "c:\\read.xls";
		 String writeFile = "c:\\write.xls";
		 test.process(sourceBuffer.toString(), readFile, writeFile, 0);

	}
}