JXL操作Excel的例子

在实际项目中经常需要对一些汇总进行导出到Excel表格中，jxl是其中一种方式，下面以jxl导出Excel表格为例做一个导出的例子，代码如下所示：

[code]
package com.myclover.utils.file;

import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;

public class ExcelStyleUtils {

/**
* 功能描述：创建Excel文档标题字体对象
* 修改日期：2011-04-28
* @author myclover
* @param size 字体大小
* @return
* @throws Exception
*/
public static WritableFont titleFont(int size) throws Exception{
WritableFont titleFont = new WritableFont(WritableFont.TAHOMA);
titleFont.setBoldStyle(WritableFont.BOLD);
titleFont.setColour(Colour.BLACK);
titleFont.setPointSize(size);
return titleFont;
}

/**
* 功能描述：创建Excel文档内容字体对象
* 修改日期：2011-04-28
* @author myclover
* @param size 字体大小
* @return
* @throws Exception
*/
public static WritableFont contentFont(int size) throws Exception{
WritableFont titleFont = new WritableFont(WritableFont.TAHOMA);
titleFont.setColour(Colour.BLACK);
titleFont.setPointSize(size);
return titleFont;
}

/**
* 功能描述：创建Excel文档标题单元格样式
* 修改日期：2011-04-28
* @author myclover
* @param align 对齐方式
* @param border 是否有边框
* @param size 字体大小
* @return
* @throws Exception
*/
public static WritableCellFormat titleCellFormat(Alignment align, boolean border, int size) throws Exception{
WritableCellFormat titleFormat = new WritableCellFormat();
titleFormat.setAlignment(null == align?Alignment.CENTRE:align);
titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
if(border){
titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
}
titleFormat.setFont(titleFont(size));
titleFormat.setWrap(true);
return titleFormat;
}

/**
* 功能描述：创建Excel文档内容单元格样式
* 修改日期：2011-04-28
* @author myclover
* @param align 对齐方式
* @param border 是否有边框
* @param size 字体大小
* @return
* @throws Exception
*/
public static WritableCellFormat contentCellFormat(Alignment align, boolean border, int size) throws Exception{
WritableCellFormat contentFormat = new WritableCellFormat();
contentFormat.setAlignment(null == align?Alignment.CENTRE:align);
contentFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
if(border){
contentFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
}
contentFormat.setFont(contentFont(size));
contentFormat.setWrap(true);
contentFormat.setShrinkToFit(true);
return contentFormat;
}

}

[/code]


测试类如下所示：

[code]

package com.myclover.utils.file;

import java.io.File;
import java.io.FileOutputStream;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.lowagie.text.Document;
import com.lowagie.text.Font;
import com.lowagie.text.Paragraph;
import com.lowagie.text.rtf.RtfWriter2;

public class TestDoc {

/**
* @param args
*/
public static void main(String[] args) {
exportExcel("D:\\text.xls");
}

public static void exportExcel(String fileName){
try{
WritableWorkbook book = Workbook.createWorkbook(new File(fileName));
WritableSheet sheet = book.createSheet("开发人员名单", 0);
// 设置各列宽度
sheet.setColumnView(0, 10);
sheet.setColumnView(1, 10);
sheet.setColumnView(2, 15);
sheet.setColumnView(3, 15);
sheet.setColumnView(4, 15);
sheet.setColumnView(5, 25);
sheet.setColumnView(6, 15);
sheet.setColumnView(7, 15);
sheet.setColumnView(8, 20);
sheet.setColumnView(9, 20);
sheet.setColumnView(10, 10);

// 设置行高
sheet.setRowView(0, 500);
sheet.setRowView(1, 500);

// 第一行
sheet.mergeCells(0, 0, 10, 0);
Label label = new Label(0,0,"测试JXL操作Excel表格");
label.setCellFormat(ExcelStyleUtils.titleCellFormat(null, false, 16));
sheet.addCell(label);

// 第二行
sheet.mergeCells(0, 1, 10, 1);
Label line2 = new Label(0,1,"2011年4月28日");
line2.setCellFormat(ExcelStyleUtils.titleCellFormat(Alignment.RIGHT, false, 14));
sheet.addCell(line2);

// 第三行
sheet.addCell(new Label(0, 2, "姓名", ExcelStyleUtils.titleCellFormat(null, true, 12)));
sheet.addCell(new Label(1, 2, "性别", ExcelStyleUtils.titleCellFormat(null, true, 12)));
sheet.addCell(new Label(2, 2, "出生年月", ExcelStyleUtils.titleCellFormat(null, true, 12)));
sheet.addCell(new Label(3, 2, "参加工作时间", ExcelStyleUtils.titleCellFormat(null, true, 12)));
sheet.addCell(new Label(4, 2, "毕业时间", ExcelStyleUtils.titleCellFormat(null, true, 12)));
sheet.addCell(new Label(5, 2, "毕业院校及专业", ExcelStyleUtils.titleCellFormat(null, true, 12)));
sheet.addCell(new Label(6, 2, "专业职称", ExcelStyleUtils.titleCellFormat(null, true, 12)));
sheet.addCell(new Label(7, 2, "工作职称", ExcelStyleUtils.titleCellFormat(null, true, 12)));
sheet.addCell(new Label(8, 2, "工作职务", ExcelStyleUtils.titleCellFormat(null, true, 12)));
sheet.addCell(new Label(9, 2, "特长", ExcelStyleUtils.titleCellFormat(null, true, 12)));
sheet.addCell(new Label(10, 2, "备注", ExcelStyleUtils.titleCellFormat(null, true, 12)));

// 循环输出内容
int line = 3;
int counts = 0;
for(int i = 1 ; i <= 15 ; i++){
sheet.mergeCells(0, line, 10, line);
sheet.addCell(new Label(0, line, ++counts + "、" + "开发部门_" + i, ExcelStyleUtils.titleCellFormat(Alignment.LEFT, true, 12)));
line++;
for(int j = 0 ; j < 3 ; j++){
sheet.addCell(new Label(0, line, "开发_" + j, ExcelStyleUtils.contentCellFormat(null, true, 10)));
String sex = "男";
if(j == 2 || i % 2 == 0){
sex = "女";
}
sheet.addCell(new Label(1, line, sex, ExcelStyleUtils.contentCellFormat(null, true, 10)));
sheet.addCell(new Label(2, line, 1980+i-j+"年" + (3+ j) +"月"+(i + j)+"日", ExcelStyleUtils.contentCellFormat(null, true, 10)));
sheet.addCell(new Label(3, line, 2000+i-j+"年" + (3+ j) +"月"+(i + j)+"日", ExcelStyleUtils.contentCellFormat(null, true, 10)));
sheet.addCell(new Label(4, line, 1996+i-j+"年7月1日", ExcelStyleUtils.contentCellFormat(null, true, 10)));
sheet.addCell(new Label(5, line, "大学本科", ExcelStyleUtils.contentCellFormat(null, true, 10)));
sheet.addCell(new Label(6, line, "java开发工程师", ExcelStyleUtils.contentCellFormat(null, true, 10)));
sheet.addCell(new Label(7, line, "Java研发", ExcelStyleUtils.contentCellFormat(null, true, 10)));
sheet.addCell(new Label(8, line, "各种PM", ExcelStyleUtils.contentCellFormat(null, true, 10)));
sheet.addCell(new Label(9, line, "Java、J2EE、SQL、SSH、JBPM", ExcelStyleUtils.contentCellFormat(null, true, 10)));
sheet.addCell(new Label(10, line, "各种技术牛人", ExcelStyleUtils.contentCellFormat(null, true, 10)));
line++;
}
}

book.write();
book.close();
}catch(Exception e){
e.printStackTrace();
}
}

}

[/code]