根据table导出Excel

最近写一个html的table表格导出excel的功能，由于表格是定制的，即表格的行列都不是固定的，包括单元格跨行跨列。用js导出excel需要依赖于IE浏览器，兼容性不太好。于是就写了个java类来导出html的table到excel。

代码如下：

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

/**
 * 根据Table生成Excel
 * @author zhy 
 * @version 1.0
 * @since 2015-3-20
 * 
 */
public class Excel {

	private static final Logger	log		= Logger.getLogger( Excel.class );
	/*导出文件名*/
	private String				name;
	private String				html;
	private Table				table;
	private HttpServletResponse	response;
	/*数据集*/
	private List<List<Node>>	data	= new ArrayList<List<Node>>();

	public Excel ( String html, HttpServletResponse response ) {
		super();
		this.html = html;
		this.response = response;
		parse();
	}

	public Excel ( String name, String html, HttpServletResponse response ) {
		super();
		this.name = name;
		this.html = html;
		this.response = response;
		parse();
	}

	public Excel ( String name, Table table, HttpServletResponse response ) {
		super();
		this.name = name;
		this.table = table;
		this.response = response;
		parseTable();
	}

	/**
	 * 解析html
	 */
	void parse() {
		// 补数据 合并单元格用 可以补html也可以补node结果集
		if ( log.isDebugEnabled() )
			log.debug( "/- Excel - html :" + html );
		Document doc = Jsoup.parse( html );
		// 遍历thead
		Elements trs = doc.select( "thead tr" );
		List<Node> thead = null;
		for ( int i = 0; i < trs.size(); i++ ) {
			thead = new ArrayList<Node>();
			Node cell;
			Elements ths = trs.get( i ).select( "th" );
			for ( int j = 0; j < ths.size(); j++ ) {
				Element th = ths.get( j );
				int rs = span( th, "rowspan" );
				int cs = span( th, "colspan" );
				cell = new Node( rs, cs, th.text() );
				thead.add( cell );
			}
			if ( thead != null )
				data.add( thead );
		}
		// 遍历tbody
		trs = doc.select( "tbody tr" );
		List<Node> tbody = null;
		for ( int i = 0; i < trs.size(); i++ ) {
			tbody = new ArrayList<Node>();
			Node cell;
			Elements tds = trs.get( i ).select( "td" );
			for ( int j = 0; j < tds.size(); j++ ) {
				Element td = tds.get( j );
				int rs = span( td, "rowspan" );
				int cs = span( td, "colspan" );
				cell = new Node( rs, cs, td.text() );
				tbody.add( cell );
			}
			if ( tbody != null )
				data.add( tbody );
		}
		// repair 导出excel合并单元格用
		for ( int i = 0; i < data.size(); i++ ) {
			List<Node> records = data.get( i );
			for ( int j = 0; j < records.size(); j++ ) {
				Node node = records.get( j );
				if ( node.cs > 1 ) {
					// 补列
					for ( int k = 1; k < node.cs; k++ ) {
						Node _node = new Node( node.value );
						records.add( j + k, _node );
					}
				}
				if ( node.rs > 1 ) {
					// 补行
					for ( int k = 1; k < node.rs; k++ ) {
						Node _node = new Node( node.value );
						data.get( i + k ).add( j, _node );
					}
				}
			}
		}
		if ( log.isDebugEnabled() )
			log.debug( "/- Excel - data:" + data );
	}

	/**
	 * 解析Table
	 */
	void parseTable() {
	}

	/**
	 * 表格的单元格
	 */
	class Node {

		/*跨行*/
		private int		rs	= 1;
		/*跨列*/
		private int		cs	= 1;
		/*value*/
		private String	value;

		/*是否数字*/
		// private boolean isNumber = false;
		public Node ( String value ) {
			super();
			this.value = value;
		}

		public Node ( int rs, int cs, String value ) {
			super();
			this.rs = rs;
			this.cs = cs;
			this.value = value;
		}

		public int rs() {
			return rs;
		}

		public int cs() {
			return cs;
		}

		public boolean isNumber() {
			return value.matches( "[0-9]+" );
		}

		public String value() {
			return value;
		}

		@Override
		public String toString() {
			return "{rs=" + rs + ", cs=" + cs + ", value=" + value + ", isNumber=" + isNumber()
				+ "}";
		}
	}

	/*生成excel*/
	public void write() {
		if ( data.isEmpty() ) {
			log.error( "/- Excel data is empty." );
			return;
		}
		// 创建Excel的工作书册 Workbook,对应到一个excel文档
		XSSFWorkbook wb = new XSSFWorkbook();
		// 创建Excel的工作sheet,对应到一个excel文档的tab
		XSSFSheet sheet = wb.createSheet( "sheet1" );
		XSSFCellStyle stringStyle = wb.createCellStyle(); // 样式对象
		stringStyle.setVerticalAlignment( XSSFCellStyle.VERTICAL_CENTER );// 垂直
		stringStyle.setAlignment( XSSFCellStyle.ALIGN_CENTER );// 水平
		stringStyle.setBorderTop( XSSFCellStyle.BORDER_THIN );
		stringStyle.setBorderBottom( XSSFCellStyle.BORDER_THIN );
		stringStyle.setBorderLeft( XSSFCellStyle.BORDER_THIN );
		stringStyle.setBorderRight( XSSFCellStyle.BORDER_THIN );
		XSSFCellStyle numberStyle = wb.createCellStyle(); // 样式对象
		numberStyle.setVerticalAlignment( XSSFCellStyle.VERTICAL_CENTER );// 垂直
		numberStyle.setAlignment( XSSFCellStyle.ALIGN_RIGHT );// 水平
		numberStyle.setBorderTop( XSSFCellStyle.BORDER_THIN );
		numberStyle.setBorderBottom( XSSFCellStyle.BORDER_THIN );
		numberStyle.setBorderLeft( XSSFCellStyle.BORDER_THIN );
		numberStyle.setBorderRight( XSSFCellStyle.BORDER_THIN );
		/*此处数据格式为1,234:每三位带,*/
		// XSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式
		// numberStyle.setDataFormat( df.getFormat( "#,#0" ) );
		// 创建Excel的sheet的一行
		for ( int i = 0; i < data.size(); i++ ) {
			XSSFRow row = sheet.createRow( i );
			// 创建一个Excel的单元格
			List<Node> records = data.get( i );
			for ( int j = 0; j < records.size(); j++ ) {
				Node node = records.get( j );
				if ( node.rs > 1 || node.cs > 1 ) {
					// 合并单元格(startRow，endRow，startColumn，endColumn)
					sheet.addMergedRegion( new CellRangeAddress( i, i + ( node.rs - 1 ), j, j
						+ ( node.cs - 1 ) ) );
				}
				XSSFCell cell = row.createCell( j );
				// 给Excel的单元格设置样式和赋值
				if ( node.isNumber() ) {
					cell.setCellStyle( numberStyle );
					cell.setCellValue( Integer.parseInt( node.value() ) );
				} else {
					cell.setCellStyle( stringStyle );
					cell.setCellValue( node.value() );
				}
			}
		}
		OutputStream os = null;
		try {
			response.reset(); 
			response.setContentType( "application/x-msdownload" );
			response.setHeader( "Content-Disposition", "attachment; filename="
				+ ( name == null ? "report" : name ) + ".xlsx" );
			os = response.getOutputStream();
			wb.write( os );
			os.flush();
		} catch ( IOException e ) {
			log.error( "/- Excel - error:", e );
		} finally {
			if ( os != null ) {
				try {
					os.close();
				} catch ( IOException e ) {
					e.printStackTrace();
				}
			}
		}
	}

	private int span( Element e, String attr ) {
		return "".equals( e.attr( attr ) ) ? 1 : Integer.parseInt( e.attr( attr ) );
	}

	public String getName() {
		return name;
	}

	public void setName( String name ) {
		this.name = name;
	}

	public String getHtml() {
		return html;
	}

	public void setHtml( String html ) {
		this.html = html;
	}

	public Table getTable() {
		return table;
	}

	public void setTable( Table table ) {
		this.table = table;
	}

}

注：需要依赖  jsoup   Apache POI

jsoup is a Java library for working with real-world HTML. It provides a very convenient API for extracting and manipulating data, using the best of DOM, CSS, and jquery-like methods.

Apache POI - the Java API for Microsoft Documents.