jxl 操作 excel

package cn.pbc.lltj.report.util;

import java.io.*;

import jxl.*;
import jxl.format.CellFormat;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.biff.RowsExceededException;

/**
* 封装Excel模板和报表的操作
*
* @author zgw
*
*/
public class Excel {
private WritableWorkbook wwb;
private WritableSheet currentSheet;
private WritableCellFormat wcfNumber;
private WritableCellFormat wcfLabel;
private WritableCellFormat wcfTitle;

/**
* 构造函数，读取模板创建Excel报表,currentSheet默认指向sheet0
*
* @param template Excel模版文档
* @param out Excel报表文件名
* @throws ExcelException
*/
public Excel(String template,String out) throws ExcelException {
InputStream ins;
Workbook sourcebook;
try {
ins = new FileInputStream(template);
} catch (FileNotFoundException e) {
throw new ExcelException("找不到Excel模版文件", e);
}

try {
sourcebook = Workbook.getWorkbook(ins);
} catch (Exception e) {
throw new ExcelException("Excel模版文件读取错误", e);
}

File outFile = new File(out);
try {
wwb = Workbook.createWorkbook(outFile, sourcebook);
NumberFormat nf = new NumberFormat("#0.00");
    wcfNumber = new WritableCellFormat(nf);
    wcfNumber.setBorder(Border.ALL, BorderLineStyle.THIN);
// 把水平对齐方式指定为居中
    wcfNumber.setAlignment(jxl.format.Alignment.CENTRE);
// 把垂直对齐方式指定为居中
    wcfNumber.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    // 设置自适应大小
    wcfNumber.setShrinkToFit(true);
   
    wcfLabel = new WritableCellFormat();
    wcfLabel.setBorder(Border.ALL, BorderLineStyle.THIN);

    wcfLabel.setVerticalAlignment(VerticalAlignment.CENTRE);
    wcfLabel.setShrinkToFit(true);
   
    WritableFont wf = new WritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD, false);
    wcfTitle = new WritableCellFormat(wf);;
    wcfTitle.setAlignment(jxl.format.Alignment.CENTRE);
    wcfTitle.setVerticalAlignment(VerticalAlignment.CENTRE);
} catch (IOException e) {
throw new ExcelException("创建Excel报表出错", e);
} catch (WriteException e) {
throw new ExcelException("设置Excel单元格错误",e);
}
currentSheet = this.wwb.getSheet(0);
}

/**
* 在报表中插入一个数字,格式不变
*
* @param col 列 从0开始
* @param row 行 从0开始
* @param value 要插入的值
* @throws ExcelException
*/
public void insert(int col, int row, double value) throws ExcelException{
Number num = new Number(col,row,value,wcfNumber);
try {
this.currentSheet.addCell(num);
} catch (RowsExceededException e) {
throw new ExcelException("行数溢出",e);
} catch (WriteException e) {
throw new ExcelException("Excel报表写入错误",e);
} catch (IndexOutOfBoundsException e) {
throw new ExcelException("Sheet溢出,超出sheet个数",e);
}
}

/**
* 在报表中插入一个字符串,格式不变
*
* @param col 列 从0开始
* @param row 行 从0开始
* @param value 要插入的值
* @throws ExcelException
*/
public void insert(int col, int row, String value) throws ExcelException{
Label label = new Label(col,row,value,wcfLabel);
try {
this.currentSheet.addCell(label);
} catch (RowsExceededException e) {
throw new ExcelException("行数溢出",e);
} catch (WriteException e) {
throw new ExcelException("Excel报表写入错误",e);
} catch (IndexOutOfBoundsException e) {
throw new ExcelException("Sheet溢出,超出sheet个数",e);
}
}

public void insertTitle(int col,int row,String title)throws ExcelException{
Label label = new Label(col,row,title,wcfTitle);
try {
this.currentSheet.addCell(label);
} catch (RowsExceededException e) {
throw new ExcelException("行数溢出",e);
} catch (WriteException e) {
throw new ExcelException("Excel报表写入错误",e);
} catch (IndexOutOfBoundsException e) {
throw new ExcelException("Sheet溢出,超出sheet个数",e);
}
}

/**
* 返回单元格的值
* @param col
* @param row
* @return
*/
public String getCellContent(int col,int row){
return this.currentSheet.getCell(col, row).getContents();
}

public void merge(int col1, int row1, int col2, int row2) throws ExcelException{

try {
this.currentSheet.mergeCells(col1, row1, col2, row2);
} catch (RowsExceededException e) {
throw new ExcelException("Excel合并单元格异常",e);
} catch (WriteException e) {
throw new ExcelException("Excel合并单元格异常",e);
}
}

/**
* 写入并关闭报表
* 1.修改表报后必须关闭，否则不能能写入文件中
* 2.报表关闭后不能再操作
*
* @throws ExcelException
*/
public void close() throws ExcelException{
try {
wwb.write();
wwb.close();
} catch (Exception e) {
throw new ExcelException("写入Excel错误", e);
}
}

/**
* 合并col列上相同的单元格
*
* @param col 从0开始的列号
* @throws ExcelException
*/
public void mergeSameOnColumn(int col,int endrow) throws ExcelException{
try {
Cell[] cells = this.currentSheet.getColumn(col);
int i = 0;
while(i<cells.length&&i<endrow){
int j = i;
String value = cells[i].getContents().trim();
while(j<cells.length&&cells[j].getContents().trim().equals(value)){
j++;
}
if(i!=j-1){
this.currentSheet.mergeCells(col, i, col, j-1);
this.insert(col, i, value);
i=j;
}else{
i++;
}

}
} catch (RowsExceededException e) {
throw new ExcelException("行数溢出",e);
} catch (WriteException e) {
throw new ExcelException("Excel报表写入错误",e);
}
}

/**
* 设置当前sheet
* @param sheet
*/
public void setCurrentSheet(int sheet){
this.currentSheet = this.wwb.getSheet(sheet);
}

/**
* 测试
*
*/
public static void main(String args[]) {
try {
String inFile = "d:/source.xls";
String outFile = "d:/test.xls";
Excel xls = new Excel(inFile, outFile);
int i=5;
while(i++<10){
xls.insert(1, i, 23.53*i);
}
xls.mergeSameOnColumn(0,10);
xls.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}