java 用jxl将多个excel文件合并成一个多sheet的excel文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
	String url="F:/test/temp1404713514264.xls"+","+"F:/test/temp1404887145242.xls"+","+"F:/test/temp1404887150705.xls";
String[] fList=url.split(",");
try {
if(fList.length > 0){
//生成文件路径
tempFile="F:/test/test.xls";
//合并生成
newWb = Workbook.createWorkbook(new File(tempFile));
//输出流
for(int i=0;i<fList.length;i++){
//文件名
String fileName=fList[i];
is=new FileInputStream("F:/test/test.xls");
is=new FileInputStream(fileName);
wb=null;
wb=Workbook.getWorkbook(is);
Sheet st =null;
st=wb.getSheet(0);
newWb.importSheet(st.getCell(0, 0).getContents(), i, st);
WritableSheet sheet = null;
sheet=newWb.getSheet(i);
for (int row = 0; row < st.getRows(); row++) {
for (int col = 0; col < st.getColumns(); col++) {
//单元格值
String value=st.getCell(col, row).getContents();
//判断在需要的表格范围内,添加样式
if(ElaneUtil.isNotEmpty(value)){
//判断是否为数字
if(ElaneUtil.isNumeric(value)){
//数字格式
jxl.write.Number labelNF;
//格式化数值(数字)
labelNF = new jxl.write.Number(col, row, UtilFunction.parseDouble(value));
WritableCellFormat format1 = new WritableCellFormat();
//填充白色(单元格背景颜色)
format1.setBackground(jxl.format.Colour.WHITE);
//设置边框颜色
format1.setBorder(Border.ALL, BorderLineStyle.THIN,Colour.GRAY_25);
labelNF.setCellFormat(format1);
sheet.addCell(labelNF);
}
}else{
//单元格
标签
Label label = new jxl.write.Label(col, row, value);
//没有值的地方,设置样式
WritableCellFormat format2 = new WritableCellFormat();
//填充白色(单元格背景颜色)
format2.setBackground(jxl.format.Colour.WHITE);
//设置边框颜色
format2.setBorder(Border.ALL, BorderLineStyle.THIN,Colour.GRAY_25);
label.setCellFormat(format2);
sheet.addCell(label);
}
}
}
is.close();
wb.close();
}
newWb.write();
newWb.close();
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}finally{
try {
if(is!=null){
is.close();
}
if(wb!=null){
wb.close();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}