JXL操作Excel的例子

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

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;
	}
	
}

测试类如下所示：

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();
		}
	}

}