jxl操作Excel简单封装

很多时候我们都会去操作一下excel文件，或者将数据专为excel供用户下载。
用的最多的就是将 一个报表生成excel文件供用户下载。而报表基本上就是标题，标题下显示内容。

如果你的报表也是这样的，就能用下面的类方便的生成excel文件，并输出到用户端。

view plain copy to clipboard print ?

1. /**  
2. *  
3. */   
4. package com.royalstone.workbook;   
5.   
6. import java.io.IOException;   
7. import java.io.OutputStream;   
8. import java.sql.ResultSet;   
9. import java.sql.ResultSetMetaData;   
10. import java.sql.SQLException;   
11. import java.sql.Types;   
12.   
13. import javax.servlet.http.HttpServletResponse;   
14.   
15. import jxl.write.DateTime;   
16. import jxl.write.Label;   
17. import jxl.write.WritableCell;   
18. import jxl.write.WritableCellFormat;   
19. import jxl.write.WritableFont;   
20. import jxl.write.WritableSheet;   
21. import jxl.write.WritableWorkbook;   
22. import jxl.write.WriteException;   
23. import jxl.write.biff.RowsExceededException;   
24.   
25. import com.royalstone.util.daemon.LangAdapter;   
26.   
27. /**  
28. * @author 造币机器  
29. * 为兼容以前的excel导入而写的替代类  
30. */   
31. public class Workbook {   
32.     public Workbook(OutputStream os) throws IOException{   
33.         book = jxl.Workbook.createWorkbook(os);   
34.     }   
35.        
36.     /**  
37.      * 增加一个工作表格  
38.      * @param rs 记录结果集  
39.      * @param sheetName 表格名  
40.      * @param title 标题  
41.      * @throws RowsExceededException  
42.      * @throws WriteException  
43.      * @throws SQLException  
44.      */   
45.     public void addSheet(ResultSet rs, String sheetName, String[] title) throws RowsExceededException, WriteException, SQLException{   
46.         WritableSheet sheet = book.createSheet(sheetName, sheetCount++);   
47.         addTitle(title, sheet);   
48.         addBody(rs, sheet);   
49.     }   
50.        
51.     /**  
52.      * 输出到客户端  
53.      * @param response 这个就不详细解释了  
54.      * @param fileName 客户下载时的文件名  
55.      * @throws IOException  
56.      * @throws WriteException  
57.      */   
58.     public void output(HttpServletResponse response,String fileName) throws IOException, WriteException{   
59.         response.reset();   
60.         response.setContentType( "application/vnd.ms-excel" );   
61.         response.setHeader( "Content-disposition", "attachment; filename=" + fileName );   
62.         book.write();   
63.         book.close();   
64.     }   
65.        
66.     /**  
67.      * 添加标题  
68.      * @param title  
69.      * @param sheet  
70.      * @throws RowsExceededException  
71.      * @throws WriteException  
72.      */   
73.     private void addTitle(String[] title,WritableSheet sheet) throws RowsExceededException, WriteException{   
74.         for (int i = 0; i < title.length; i++) {   
75.             Label label=new Label(i,0,title[i],formatTitle);   
76.             sheet.addCell(label);   
77.         }   
78.     }   
79.        
80.     /**  
81.      * 添加主体内容  
82.      * @param rs  
83.      * @param sheet  
84.      * @throws SQLException  
85.      * @throws RowsExceededException  
86.      * @throws WriteException  
87.      */   
88.     private void addBody(ResultSet rs,WritableSheet sheet) throws SQLException, RowsExceededException, WriteException{   
89.         ResultSetMetaData meta = rs.getMetaData();   
90.         int row = 1;   
91.         while(rs.next()){   
92.             for(int i=1;i<=meta.getColumnCount();i++){   
93.                 sheet.addCell((WritableCell) parseMetaData(rs, meta, i, row));   
94.             }   
95.             row++;   
96.         }   
97.     }   
98.        
99.        
100.     /**  
101.      * 根据字段类型自动生成格式  
102.      * @param rs  
103.      * @param meta  
104.      * @param col  
105.      * @param row  
106.      * @return  
107.      * @throws SQLException  
108.      */   
109.     private Object parseMetaData(ResultSet rs,ResultSetMetaData meta,int col,int row) throws SQLException{   
110.         Object o = null;   
111.         switch(meta.getColumnType(col)){   
112.         case Types.DATE:   
113.         case Types.TIME:   
114.         case Types.TIMESTAMP:   
115.             o = new DateTime(col-1,row,rs.getDate(col),formatDate);   
116.             break;   
117.         case Types.DECIMAL:   
118.         case Types.DOUBLE:   
119.             o = new jxl.write.Number(col-1,row,rs.getDouble(col),formatNumber);   
120.             break;   
121.         case Types.SMALLINT:   
122.         case Types.INTEGER:   
123.         case Types.NUMERIC:   
124.             o = new jxl.write.Number(col-1,row,rs.getInt(col),formatNumber);   
125.             break;   
126.         default:   
127.             o = new Label(col-1,row,adapter.fromLocal(rs.getString(col)));   
128.         }   
129.         return o;   
130.     }   
131.        
132.     final private LangAdapter adapter = new LangAdapter();   
133.     private int sheetCount=0;   
134.     private WritableWorkbook book ;   
135.     final static WritableCellFormat formatTitle=new WritableCellFormat(new WritableFont(WritableFont.COURIER,12,WritableFont.BOLD));   
136.     final static WritableCellFormat formatNumber=new WritableCellFormat(new WritableFont(WritableFont.TAHOMA,10));   
137.     final static WritableCellFormat formatDate=new WritableCellFormat(new WritableFont(WritableFont.TIMES,10));   
138. } 
     

/** * */ package com.royalstone.workbook; import java.io.IOException; import java.io.OutputStream; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Types; import javax.servlet.http.HttpServletResponse; import jxl.write.DateTime; 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; import jxl.write.biff.RowsExceededException; import com.royalstone.util.daemon.LangAdapter; /** * @author 造币机器 * 为兼容以前的excel导入而写的替代类 */ public class Workbook { public Workbook(OutputStream os) throws IOException{ book = jxl.Workbook.createWorkbook(os); } /** * 增加一个工作表格 * @param rs 记录结果集 * @param sheetName 表格名 * @param title 标题 * @throws RowsExceededException * @throws WriteException * @throws SQLException */ public void addSheet(ResultSet rs, String sheetName, String[] title) throws RowsExceededException, WriteException, SQLException{ WritableSheet sheet = book.createSheet(sheetName, sheetCount++); addTitle(title, sheet); addBody(rs, sheet); } /** * 输出到客户端 * @param response 这个就不详细解释了 * @param fileName 客户下载时的文件名 * @throws IOException * @throws WriteException */ public void output(HttpServletResponse response,String fileName) throws IOException, WriteException{ response.reset(); response.setContentType( "application/vnd.ms-excel" ); response.setHeader( "Content-disposition", "attachment; filename=" + fileName ); book.write(); book.close(); } /** * 添加标题 * @param title * @param sheet * @throws RowsExceededException * @throws WriteException */ private void addTitle(String[] title,WritableSheet sheet) throws RowsExceededException, WriteException{ for (int i = 0; i < title.length; i++) { Label label=new Label(i,0,title[i],formatTitle); sheet.addCell(label); } } /** * 添加主体内容 * @param rs * @param sheet * @throws SQLException * @throws RowsExceededException * @throws WriteException */ private void addBody(ResultSet rs,WritableSheet sheet) throws SQLException, RowsExceededException, WriteException{ ResultSetMetaData meta = rs.getMetaData(); int row = 1; while(rs.next()){ for(int i=1;i<=meta.getColumnCount();i++){ sheet.addCell((WritableCell) parseMetaData(rs, meta, i, row)); } row++; } } /** * 根据字段类型自动生成格式 * @param rs * @param meta * @param col * @param row * @return * @throws SQLException */ private Object parseMetaData(ResultSet rs,ResultSetMetaData meta,int col,int row) throws SQLException{ Object o = null; switch(meta.getColumnType(col)){ case Types.DATE: case Types.TIME: case Types.TIMESTAMP: o = new DateTime(col-1,row,rs.getDate(col),formatDate); break; case Types.DECIMAL: case Types.DOUBLE: o = new jxl.write.Number(col-1,row,rs.getDouble(col),formatNumber); break; case Types.SMALLINT: case Types.INTEGER: case Types.NUMERIC: o = new jxl.write.Number(col-1,row,rs.getInt(col),formatNumber); break; default: o = new Label(col-1,row,adapter.fromLocal(rs.getString(col))); } return o; } final private LangAdapter adapter = new LangAdapter(); private int sheetCount=0; private WritableWorkbook book ; final static WritableCellFormat formatTitle=new WritableCellFormat(new WritableFont(WritableFont.COURIER,12,WritableFont.BOLD)); final static WritableCellFormat formatNumber=new WritableCellFormat(new WritableFont(WritableFont.TAHOMA,10)); final static WritableCellFormat formatDate=new WritableCellFormat(new WritableFont(WritableFont.TIMES,10)); }