使用jxl导出excel（带边栏合并）

项目上要求根据一个List，导出Excel文件。因为jxl只能支持到excel2003，所以本程序也只支持excel2003(行列数限制很大).

文件的上边栏（主）跟左边栏（宾）如果有相同单元格的话，要合并。

下面这个文件是调用方式，dataList就是要输出的List。fileName是中文名，要导出的文件是UTF-8格式的，所以文件名要用自己写的转换方法先转换一下。

	public void output() throws WriteException, IOException
	{	
		String fileName = "统计画面";
		OutputStream os = response().getOutputStream();
		response().reset();  
		response().setHeader("Content-disposition", "attachment; filename=" +StatTools.toUtf8String(fileName + ".xls"));  
		response().setContentType("application/msexcel");  
		ExcelOutput.OutPut(os, dataList);
	}

ExcelOutput.java

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class ExcelOutput {
	//主宾栏样式
	private static WritableFont headerFont = null;
	private static WritableCellFormat headerFormat = null;
	//数据栏样式
	private static WritableFont dataFont = null;
	private static WritableCellFormat dataFormat = null;
	
	private static void init() throws WriteException
	{
		//构造格式：ARIAL字体、10号、粗体、非斜体、无下划线、黑色
		headerFont = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
	    headerFormat = new WritableCellFormat(headerFont);
	   //文字垂直居中对齐
	    headerFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); 
	   //文字水平居中对齐 
	    headerFormat.setAlignment(jxl.format.Alignment.CENTRE);   
	    //边框深蓝色
	    headerFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN,jxl.format.Colour.DARK_BLUE);
	    //设置底色为灰色
	    headerFormat.setBackground(jxl.format.Colour.GREY_25_PERCENT);
	    
	    //构造格式：ARIAL字体、10号、非粗体、非斜体、无下划线、黑色
	    dataFont = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
	    dataFormat = new WritableCellFormat(dataFont);
	    //文字垂直居中对齐
	    dataFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); 
	    //文字水平居中对齐 
	    dataFormat.setAlignment(jxl.format.Alignment.CENTRE);   
	    //边框深蓝色
	    dataFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN,jxl.format.Colour.DARK_BLUE);
	}
	
	/**
	 * 
	 * @param os 页面输出流
	 * @param excelList 导出数据
	 * @throws IOException
	 * @throws WriteException
	 */
	public static void OutPut(OutputStream os, List excelList) throws IOException, WriteException
	{
		//单元格样式初始化
		init();

		jxl.write.WritableWorkbook wbook = Workbook.createWorkbook(os);  
		
		//通过Workbook的静态方法createWorkbook创建Excel文件
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		//通过Workbook的createSheet方法新建工作簿
		WritableSheet sheet = workbook.createSheet("统计信息", 0);
		//通过Sheet方法的getWritableCell方法选择单元格（两个参数都从0开始）
		Label label = null;
		String context = null;
		for(int rowNum = 0; excelList != null &&rowNum < excelList.size(); rowNum++)
		{
			List rowList = (List)excelList.get(rowNum);
			
			for(int colNum = 0; rowList != null && colNum < rowList.size(); colNum++)
			{
				context = rowList.get(colNum).toString();
				WritableCell cell = sheet.getWritableCell(rowNum, colNum);
				if(NumberCheck.isNumber(context))
				{
					label = new Label(colNum, rowNum,context,dataFormat);
				}
				else
				{
					label = new Label(colNum, rowNum,context,headerFormat);
				}
				sheet.addCell(label);
			}
		}
		
		//主栏项目合并
		int mergeRowStart = 0;
		int mergeRowEnd = 0;
		int mergeColStart;
		int mergeColEnd;
		String tmpStr = null;
		for(int rowNum = 0; rowNum < excelList.size(); rowNum++)
		{
			tmpStr = "";
			mergeColStart = 0;
			mergeColEnd = 0;
			List rowList = (List)excelList.get(rowNum);
			
			//循环终止条件
			if (rowList == null || rowList.size() == 0
					|| NumberCheck.isNumber((String) rowList.get(rowList.size()-1))) {
				break;
			}
			for(int colNum = 0; colNum < rowList.size(); colNum++)
			{
				if(tmpStr.equals((String)rowList.get(colNum)))
				{
					mergeColEnd = colNum;
					//本行最后一列数据的处理
					if(colNum == rowList.size() - 1)
					{
						if(mergeColStart != mergeColEnd)
						{
							sheet.mergeCells(mergeColStart, rowNum, mergeColEnd, rowNum);
						}
					}
				}
				else
				{
					tmpStr = (String)rowList.get(colNum);
					//合并单元格
					if(mergeColStart != mergeColEnd)
					{
						sheet.mergeCells(mergeColStart, rowNum, mergeColEnd, rowNum);
					}
					mergeColStart = colNum;
					mergeColEnd = colNum;
				}
			}
		}
		
		//宾栏项目合并
		if(excelList.size()>0)
		{
			int rowSize = excelList.size();
			int colSize = ((List)excelList.get(0)).size();
			for(int colNum = 0; colNum < colSize; colNum++)
			{
				tmpStr = "";
				mergeRowStart = 0;
				mergeRowEnd = 0;
				//循环终止条件
				String checkStr = (String)((List)excelList.get(rowSize-1)).get(colNum);
				if(NumberCheck.isNumber(checkStr))
				{
					break;
				}
				
				for(int rowNum = 0; rowNum < rowSize; rowNum++)
				{
					List rowList = (List)excelList.get(rowNum);
					if(tmpStr.equals(rowList.get(colNum)))
					{
						mergeRowEnd = rowNum;
						//本列最后一行数据的处理
						if(rowNum == rowSize - 1)
						{
							if(mergeRowStart != mergeRowEnd)
							{
								sheet.mergeCells(colNum, mergeRowStart, colNum, mergeRowEnd);
							}
						}
					}
					else
					{
						tmpStr = (String)rowList.get(colNum);
						//合并单元格
						if(mergeRowStart != mergeRowEnd)
						{
							sheet.mergeCells(colNum, mergeRowStart, colNum, mergeRowEnd);
						}
						mergeRowStart = rowNum;
						mergeRowEnd = rowNum;
					}
				}
			}
		}
		
		//写Workbook
		workbook.write();
		//关闭Workbook
		workbook.close();
	}
}

NumberCheck.java

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class NumberCheck {
	public static boolean isNumber(String str) {
		if(str == null || "".equals(str))
		{
			return false;
		}
		
		String regEx = "^[0-9]+$|^[0-9]+\\.[0-9]+{1}quot;;
		Pattern p = Pattern.compile(regEx);
		Matcher m = p.matcher(str);
		return m.matches();
	}
}

StatTools.java

public class StatTools {
	public static String toUtf8String(String s) {
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < s.length(); i++) {
			char c = s.charAt(i);
			if (c >= 0 && c <= 255) {
				sb.append(c);
			} else {
				byte[] b;
				try {
					b = Character.toString(c).getBytes("utf-8");
				} catch (Exception ex) {
					System.out.println(ex);
					b = new byte[0];
				}
				for (int j = 0; j < b.length; j++) {
					int k = b[j];
					if (k < 0)
						k += 256;
					sb.append("%" + Integer.toHexString(k).toUpperCase());
				}
			}
		}
		return sb.toString();
	}
}

效果如下图所示：