Java操作excel的综合应用(jxl)

根据excel模板生成excel报表文件--用于报表打印 

jxl修改excel模板文件，实现动态数据分页打印 

1.支持公式运算 
2.支持对合并的单元格复制 

Java代码  

1. package mcfeng.util.excel;  
2.   
3. import java.io.File;  
4. import java.io.IOException;  
5.   
6. import jxl.CellType;  
7. import jxl.Workbook;  
8. import jxl.format.CellFormat;  
9. import jxl.read.biff.BiffException;  
10. import jxl.write.Label;  
11. import jxl.write.WritableCell;  
12. import jxl.write.WritableSheet;  
13. import jxl.write.WritableWorkbook;  
14. import jxl.write.WriteException;  
15. import jxl.write.biff.RowsExceededException;  
16. import mcfeng.util.ExcelDataSource;  
17. import mcfeng.util.MoneyUtil;  
18. import mcfeng.util.StringUtil;  
19.   
20. public class ExcelEditByModel {  
21.   
22.     //list中取数据  
23.     private final static String LIST_FLAG = "##";  
24.       
25.     //map中取数据  
26.     private final static String MAP_FLAG = "#&";  
27.       
28.     //数字类型处理，支持公式  
29.     private final static String NUM_FLAG = "#_&";  
30.       
31.     //大写金额处理  
32.     private final static String DX_FLAG = "##D&";  
33.   
34.     public static void editExcel(int totalPage,String sourcefile, String targetfile) {  
35.         File file1 = new File(sourcefile);  
36.         File file2 = new File(targetfile);  
37.         editExcel(totalPage,file1, file2);  
38.     }  
39.   
40.     public static void editExcel(int totalPage,File sourcefile, File targetfile) {  
41.         String mycellValue = null;  
42.         Workbook wb = null;  
43.         try {  
44.             // 构造Workbook（工作薄）对象  
45.             wb = Workbook.getWorkbook(sourcefile);  
46.         } catch (BiffException e) {  
47.             e.printStackTrace();  
48.         } catch (IOException e) {  
49.             e.printStackTrace();  
50.         }  
51.   
52.         WritableWorkbook wwb = null;  
53.         try {  
54.             // 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象  
55.             wwb = Workbook.createWorkbook(targetfile, wb);  
56.         } catch (IOException e) {  
57.             e.printStackTrace();  
58.         }  
59.   
60.         if (wwb != null) {  
61.             // 读取第一张工作表  
62.             // Workbook的getSheet方法的参数，表示工作表在工作薄中的位置  
63.   
64.             WritableSheet ws = wwb.getSheet(0);  
65.   
66.             int scale = ws.getSettings().getScaleFactor();// 获取页面缩放比例  
67.               
68.             int rowNum = ws.getRows();  
69.             int colNum = ws.getColumns();  
70.               
71.             //计算出每页行数  
72.             int pageNum = rowNum/totalPage;  
73.   
74.             for (int j = 0; j < rowNum; j++) {  
75.                 // 得到当前行的所有单元格  
76.   
77.                 //计算出取数据的位置  
78.                 int dataNum = j/pageNum;  
79.                   
80.                 for (int k = 0; k < colNum; k++) {  
81.                     // 对每个单元格进行循环  
82.   
83.                     WritableCell mywc = ws.getWritableCell(k, j);  
84.   
85.                     System.out.println("mywc.getType(): " + mywc.getType());  
86.                     if (mywc.getType() == CellType.LABEL) {  
87.   
88.                         Label l = (Label) mywc;  
89.   
90.                         String cellValue = l.getContents();  
91.                         //处理后的值  
92.                         String opValue = null;  
93.                           
94.                         System.out.println("cellValue: " + cellValue);  
95.   
96.                         // 处理excel单元格中#开头的字符串  
97.                         if (cellValue != null && cellValue.startsWith("#")) {  
98.   
99.                             if (cellValue.startsWith(LIST_FLAG)) {  
100.                                   
101.                                 if(cellValue.startsWith(DX_FLAG))  
102.                                 {  
103.                                     opValue = cellValue.replaceAll(DX_FLAG, "");  
104.                                 }  
105.                                 else  
106.                                 {  
107.                                     opValue = cellValue.replaceAll(LIST_FLAG, "");  
108.                                 }  
109.                                   
110.                                   
111.                                 if (StringUtil.isNumeric(opValue)) {  
112.                                     mycellValue = ExcelDataSource.getData(opValue,dataNum);  
113.                                     if(cellValue.startsWith(DX_FLAG))  
114.                                     {  
115.                                         mycellValue = MoneyUtil.amountToChinese(mycellValue);  
116.                                     }  
117.                                       
118.                                 }  
119.   
120.                             } else if (cellValue.startsWith(MAP_FLAG)) {  
121.                                 opValue = cellValue.replaceAll(MAP_FLAG, "");  
122.   
123.                                 mycellValue = ExcelDataSource.getData(opValue,dataNum);  
124.                             }  
125.                             else if (cellValue.startsWith(NUM_FLAG)) {  
126.                                 //支持公式运算  
127.                                 opValue = cellValue.replaceAll(NUM_FLAG, "");  
128.   
129.                                 mycellValue = ExcelDataSource.getData(opValue,dataNum);  
130.                                 System.out.println("mycellValue: " + mycellValue);  
131.                                   
132.                                 //获取字体，重新设置  
133.                                 CellFormat wcff = mywc.getCellFormat();  
134.                                   
135.                                 jxl.write.Number num = new jxl.write.Number(k,j,Double.valueOf(mycellValue),wcff);  
136.                                   
137.                                   
138.                                 try {  
139.                                     ws.addCell(num);  
140.                                 } catch (RowsExceededException e) {  
141.                                       
142.                                     e.printStackTrace();  
143.                                 } catch (WriteException e) {  
144.                                       
145.                                     e.printStackTrace();  
146.                                 }  
147.                                 continue;  
148.                             }  
149.                             l.setString(mycellValue);  
150.                         }  
151.                     }  
152.                 }  
153.             }  
154.               
155.             //设置页面缩放比例  
156.             ws.getSettings().setScaleFactor(scale);  
157.   
158.             try {  
159.                 // 写入 Excel 对象  
160.                 wwb.write();  
161.                 // 关闭可写入的 Excel 对象  
162.                 wwb.close();  
163.                 // 关闭只读的 Excel 对象  
164.                 wb.close();  
165.             } catch (IOException e) {  
166.                 e.printStackTrace();  
167.             } catch (WriteException e) {  
168.                 e.printStackTrace();  
169.             }  
170.   
171.         }  
172.     }  
173.   
174. }  

生成分页模板 

Java代码  

1. package mcfeng.util.excel;  
2.   
3. import java.io.File;  
4. import java.io.IOException;  
5.   
6. import jxl.Range;  
7. import jxl.Workbook;  
8. import jxl.read.biff.BiffException;  
9. import jxl.write.WritableCell;  
10. import jxl.write.WritableSheet;  
11. import jxl.write.WritableWorkbook;  
12. import jxl.write.WriteException;  
13. import jxl.write.biff.RowsExceededException;  
14.   
15. public class ExcelEditByModelPage {  
16.   
17.     public static void editExceltoModel(int totalPage,String sourcefile, String targetfile) {  
18.         File file1 = new File(sourcefile);  
19.         File file2 = new File(targetfile);  
20.         editExceltoModel(totalPage, file1,file2);  
21.     }  
22.       
23.     public static void editExcelbyModelPage(int totalPage,String sourcefile,String tempfile, String targetfile) {  
24.         File file1 = new File(sourcefile);  
25.         File file2 = new File(tempfile);  
26.         File file3 = new File(targetfile);  
27.         editExcelbyModelPage(totalPage, file1,file2,file3);  
28.     }  
29.   
30.     public static void editExcelbyModelPage(int totalPage, File sourcefile,File tempfile,  
31.             File targetfile)  
32.     {  
33.         if(totalPage == 1)  
34.         {  
35.             ExcelEditByModel.editExcel(totalPage, sourcefile, targetfile);  
36.             return;  
37.         }  
38.         //需要分页时，生成中间模板文件  
39.         ExcelEditByModel.editExcel(totalPage,editExceltoModel(totalPage,sourcefile,tempfile), targetfile);  
40.     }  
41.       
42.     // 生成分页模板  
43.     public static File editExceltoModel(int totalPage, File sourcefile,File targetfile) {  
44.           
45.         Workbook wb = null;  
46.         try {  
47.             // 构造Workbook（工作薄）对象  
48.             wb = Workbook.getWorkbook(sourcefile);  
49.         } catch (BiffException e) {  
50.             e.printStackTrace();  
51.         } catch (IOException e) {  
52.             e.printStackTrace();  
53.         }  
54.   
55.         WritableWorkbook wwb = null;  
56.         try {  
57.             // 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象  
58.             wwb = Workbook.createWorkbook(targetfile, wb);  
59.         } catch (IOException e) {  
60.             e.printStackTrace();  
61.         }  
62.   
63.         if (wwb != null) {  
64.             // 读取第一张工作表  
65.             // Workbook的getSheet方法的参数，表示工作表在工作薄中的位置  
66.   
67.             WritableSheet ws = wwb.getSheet(0);  
68.   
69.             int scale = ws.getSettings().getScaleFactor();// 获取页面缩放比例  
70.   
71.             int rowNum = ws.getRows();  
72.             int colNum = ws.getColumns();  
73.   
74.             System.out.println("rowNum: " + rowNum);  
75.             System.out.println("colNum: " + colNum);  
76.               
77.             //找出合并的单元格  
78.             Range[] ranges = ws.getMergedCells();  
79.             for(int rnum = 0;rnum < ranges.length;rnum++)  
80.             {  
81.                 System.out.println("左上行数" + ranges[rnum].getTopLeft().getRow());  
82.                 System.out.println("左上列数" + ranges[rnum].getTopLeft().getColumn());  
83.                 System.out.println("右下行数" + ranges[rnum].getBottomRight().getRow());  
84.                 System.out.println("右下列数" + ranges[rnum].getBottomRight().getColumn());  
85.             }  
86.   
87.             int i = 1;  
88.             while (i < totalPage) {  
89.   
90.                 for (int row = 0; row < rowNum; row++) {  
91.                     // 得到当前行的所有单元格  
92.   
93.                     for (int col = 0; col < colNum; col++) {  
94.                         // 对每个单元格进行循环  
95.                         // 复制单元格  
96.                         WritableCell cell = ws.getWritableCell(col, row)  
97.                                 .copyTo(col, row + (rowNum*i));  
98.   
99.                         try {  
100.                             ws.addCell(cell);  
101.                         } catch (RowsExceededException e) {  
102.                             e.printStackTrace();  
103.                         } catch (WriteException e) {  
104.                             e.printStackTrace();  
105.                         }  
106.                     }  
107.                 }  
108.                   
109.                 //按照模板合并单元格  
110.                 for(int rnum = 0;rnum < ranges.length;rnum++)  
111.                 {  
112.                     int lcol = ranges[rnum].getTopLeft().getColumn();  
113.                     int lrow = ranges[rnum].getTopLeft().getRow() + (rowNum*i);  
114.                     int rcol = ranges[rnum].getBottomRight().getColumn();  
115.                     int rrow = ranges[rnum].getBottomRight().getRow() + (rowNum*i);  
116.                       
117.                     try {  
118.                         ws.mergeCells(lcol, lrow, rcol, rrow);  
119.                     } catch (RowsExceededException e) {  
120.                           
121.                         e.printStackTrace();  
122.                     } catch (WriteException e) {  
123.                           
124.                         e.printStackTrace();  
125.                     }  
126.                       
127.                 }  
128.                 i++;  
129.             }  
130.   
131.             //设置页面缩放比例  
132.             ws.getSettings().setScaleFactor(scale);  
133.         }  
134.   
135.         try {  
136.             // 写入 Excel 对象  
137.             wwb.write();  
138.             // 关闭可写入的 Excel 对象  
139.             wwb.close();  
140.             // 关闭只读的 Excel 对象  
141.             wb.close();  
142.         } catch (IOException e) {  
143.             e.printStackTrace();  
144.         } catch (WriteException e) {  
145.             e.printStackTrace();  
146.         }  
147.           
148.         return targetfile;  
149.     }  
150. }