【Java】-【使用jxl操作excel】

下载jxl包并引用

jxl.jar：java操作excel表格的工具类库。这是一个.jar类型的类库，这套API是纯Java的，并不依赖Windows系统，它集成封装了操作处理excel表格的一些类和方法。

在IDEA中点击file->project structure->libraries->添加下载好的jxl.jar

基本使用

package test;  
  
import java.io.File;  
import java.io.IOException;  
import java.net.MalformedURLException;  
import java.net.URL;  
import java.util.ArrayList;  
import java.util.Calendar;  
import java.util.Date;  
  
import jxl.CellType;  
import jxl.Workbook;  
import jxl.format.Alignment;  
import jxl.format.Border;  
import jxl.format.BorderLineStyle;  
import jxl.format.Colour;  
import jxl.format.ScriptStyle;  
import jxl.format.UnderlineStyle;  
import jxl.format.VerticalAlignment;  
import jxl.read.biff.BiffException;  
import jxl.write.Blank;  
import jxl.write.DateFormat;  
import jxl.write.DateFormats;  
import jxl.write.DateTime;  
import jxl.write.Formula;  
import jxl.write.Label;  
import jxl.write.Number;  
import jxl.write.NumberFormat;  
import jxl.write.WritableCell;  
import jxl.write.WritableCellFeatures;  
import jxl.write.WritableCellFormat;  
import jxl.write.WritableFont;  
import jxl.write.WritableHyperlink;  
import jxl.write.WritableImage;  
import jxl.write.WritableSheet;  
import jxl.write.WritableWorkbook;  
import jxl.write.WriteException;  

public class ExcelTest {  
    public static void main(String[] args) throws IOException, BiffException, WriteException {  
        Workbook wb = Workbook.getWorkbook(new File("src\\test\\test.xls")); // 获得原始文档  
        WritableWorkbook workbook = Workbook.createWorkbook(new File("d:\\test_modified.xls"),wb); // 创建一个可读写的副本  
          
          
        /** 
         * 定义与设置Sheet 
         */  
        WritableSheet sheet = workbook.getSheet(0);  
        sheet.setName("修改后"); // 给sheet页改名  
        workbook.removeSheet(2); // 移除多余的标签页  
        workbook.removeSheet(3);  
          
        sheet.mergeCells(0, 0, 4, 0); // 合并单元格  
        sheet.setRowView(0, 600); // 设置行的高度  
        sheet.setColumnView(0, 30); // 设置列的宽度  
        sheet.setColumnView(1, 20); // 设置列的宽度  
          
         WritableCell cell = null;  
         WritableCellFormat wcf = null;  
         Label label = null;  
         WritableCellFeatures wcfeatures = null;  
          
         // 更改标题字体  
         cell = sheet.getWritableCell(0,0);  
         WritableFont titleWf = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体  
                                                 20,//WritableFont.DEFAULT_POINT_SIZE,  // 字号  
                                                 WritableFont.NO_BOLD,                  // 粗体  
                                                 false,                                 // 斜体  
                                                 UnderlineStyle.NO_UNDERLINE,           // 下划线  
                                                 Colour.BLUE2,                          // 字体颜色  
                                                 ScriptStyle.NORMAL_SCRIPT);  
         wcf = new WritableCellFormat(titleWf);  
         wcf.setBackground(Colour.GRAY_25);// 设置单元格的背景颜色  
         wcf.setAlignment(Alignment.CENTRE); // 设置对齐方式  
         wcf.setBorder(Border.ALL, BorderLineStyle.THICK); // 添加边框  
         cell.setCellFormat(wcf);  
          
         // 将B3的字体改为仿宋_GB2312  
         cell = sheet.getWritableCell(1,2);  
         WritableFont fs = new WritableFont(WritableFont.createFont("仿宋_GB2312"),  
                                           11);  
         wcf = new WritableCellFormat(fs);  
         cell.setCellFormat(wcf);  
          
         // 将B4的字号改为20  
         cell = sheet.getWritableCell(1,3);  
         WritableFont size20 = new WritableFont(WritableFont.createFont("宋体"),   
                                               20);  
         wcf = new WritableCellFormat(size20);  
         cell.setCellFormat(wcf);  
          
         // 将B5的字体改为加粗  
         cell = sheet.getWritableCell(1,4);  
         WritableFont bold = new WritableFont(WritableFont.createFont("宋体"),   
                                               11,  
                                               WritableFont.BOLD);  
         wcf = new WritableCellFormat(bold);  
         cell.setCellFormat(wcf);  
          
         // 将B6的字体改为倾斜  
         cell = sheet.getWritableCell(1,5);  
         WritableFont italic = new WritableFont(WritableFont.createFont("宋体"),   
                                                11,  
                                                WritableFont.NO_BOLD,  
                                                true);  
         wcf = new WritableCellFormat(italic);  
         cell.setCellFormat(wcf);  
          
         // 将B7字体加下划线  
         cell = sheet.getWritableCell(1,6);  
         WritableFont underline = new WritableFont(WritableFont.createFont("宋体"),   
                                                   11,  
                                                   WritableFont.NO_BOLD,  
                                                   false,  
                                                   UnderlineStyle.SINGLE);  
         wcf = new WritableCellFormat(underline);  
         cell.setCellFormat(wcf);  
          
         // 将B8的文字改为“待修改文字-已修改”  
         cell = sheet.getWritableCell(1,7);  
         if (cell.getType() == CellType.LABEL)  
         {  
             Label lc = (Label) cell;  
             lc.setString(lc.getString() + " - 已修改");  
         }  
          
         // 将B9文字对齐方式改为垂直居中、右对齐  
         cell = sheet.getWritableCell(1,8);  
         WritableFont align = new WritableFont(WritableFont.createFont("宋体"),   
                                                  11);  
         wcf = new WritableCellFormat(align);  
         wcf.setAlignment(Alignment.RIGHT); // 设置为右对齐  
         wcf.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置为垂直居中  
         cell.setCellFormat(wcf);  
          
         // 将E3文字改为自动换行  
         cell = sheet.getWritableCell(4,2);  
         WritableFont justify = new WritableFont(WritableFont.createFont("宋体"),   
                                                  11);  
         wcf = new WritableCellFormat(justify);  
         wcf.setAlignment(Alignment.JUSTIFY);  
         cell.setCellFormat(wcf);  
          
          
         // 将B12的数字有效位数从5位改为7位  
         cell = sheet.getWritableCell(1,11);  
         NumberFormat sevendps = new NumberFormat("#.0000000");  
         wcf = new WritableCellFormat(sevendps);  
         cell.setCellFormat(wcf);  
          
         // 将B13改为4位科学计数法表示  
         cell = sheet.getWritableCell(1,12);  
         NumberFormat exp4 = new NumberFormat("0.####E0");  
         wcf = new WritableCellFormat(exp4);  
         cell.setCellFormat(wcf);  
          
         // 将B14改为默认数字表示  
         cell = sheet.getWritableCell(1,13);  
         cell.setCellFormat(WritableWorkbook.NORMAL_STYLE);  
          
         // 将B15数字类型的值17改为22  
         cell = sheet.getWritableCell(1,14);  
         if (cell.getType() == CellType.NUMBER)  
         {  
             Number n = (Number) cell;  
             n.setValue(42);  
         }  
          
         // 将B16的值2.71进行加法运算2.71 + 0.1  
         cell = sheet.getWritableCell(1,15);  
         if (cell.getType() == CellType.NUMBER)  
         {  
             Number n = (Number) cell;  
             n.setValue(n.getValue() + 0.1);  
         }  
          
         // 将B19日期格式改为默认  
         cell = sheet.getWritableCell(1,18);  
         wcf = new WritableCellFormat(DateFormats.FORMAT9);  
         cell.setCellFormat(wcf);  
          
         // 将B20日期格式改为dd MMM yyyy HH:mm:ss  
         cell = sheet.getWritableCell(1,19);  
         DateFormat df = new DateFormat("dd MMM yyyy HH:mm:ss");  
         wcf = new WritableCellFormat(df);  
         cell.setCellFormat(wcf);  
          
         // 将B21的日期设置为 2011-6-1 11:18:50  
         cell = sheet.getWritableCell(1,20);  
         if (cell.getType() == CellType.DATE)  
         {  
             DateTime dt = (DateTime) cell;  
             Calendar cal = Calendar.getInstance();  
             cal.set(2011, 5, 1, 11, 18, 50);  
             Date d = cal.getTime();  
             dt.setDate(d);  
         }  
          
          
         // 将B24文字添加链接http://www.baidu.com  
         WritableHyperlink link = new WritableHyperlink(1, 23, new URL("http://www.baidu.com"));  
         sheet.addHyperlink(link);  
          
         // 更改URL链接  
         WritableHyperlink hyperlinks[] = sheet.getWritableHyperlinks();  
         for (int i = 0; i < hyperlinks.length; i++) {  
             WritableHyperlink wh = hyperlinks[i];  
             if (wh.getColumn() == 1 && wh.getRow() == 24) {  
                 // 将B25文字链接取消  
                 sheet.removeHyperlink(wh,true);//true：保留文字；false：删除文字  
             }else if(wh.getColumn() == 1 && wh.getRow() == 25){  
                 try {  
                     // 将B26链接更改为http://wuhongyu.javaeye.com  
                     wh.setURL(new URL("http://wuhongyu.javaeye.com"));  
                 } catch (MalformedURLException e) {  
                     e.printStackTrace();  
                 }  
             }  
         }  
          
          
         // 利用公式取得B29、B30的值  
         Formula f1 = new Formula(1, 28, "SUM(C29:D29)");  
         sheet.addCell(f1);  
         Formula f2 = new Formula(1, 29, "AVERAGE(C30:G30)");  
         sheet.addCell(f2);  
          
         // 在B32处添加图片，图片大小占10行3列，只支持png格式  
         File file = new File("d:\\shu05.png");  
         WritableImage image = new WritableImage(1, 31, 3, 10, file);  
         sheet.addImage(image);  
          
         // 在A44出添加内容"Added drop down validation"，并为其添加注释  
         label = new Label(0, 43, "Added drop down validation");  
         wcfeatures = new WritableCellFeatures();  
         wcfeatures.setComment("右边列是个下拉列表");  
         label.setCellFeatures(wcfeatures);  
         sheet.addCell(label);  
           
         // 在B44处添加一个下拉列表并添加注释  
         Blank b = new Blank(1, 43);  
         wcfeatures = new WritableCellFeatures();  
         ArrayList al = new ArrayList();  
         al.add("why");  
         al.add("landor");  
         al.add("tjm");  
         wcfeatures.setDataValidationList(al);  
         wcfeatures.setComment("这是一个注释");  
         b.setCellFeatures(wcfeatures);  
         sheet.addCell(b);  
           
         // 为A46添加注释。  
         // 此处比较麻烦，试了多次发现必须将cell强制类型转换、添加CellFeatures再修改注释才可用，不知有没有更好的办法。  
         cell = sheet.getWritableCell(0,45);  
         wcfeatures = new WritableCellFeatures();  
         wcfeatures.setComment("这个注释不会被显示，删了这行还不行,MD");  
         cell.setCellFeatures(wcfeatures);  
           
         label = (Label) cell;  
//       label.setCellFeatures(wcfeatures);// 直接这样写会报一个警告（“注释已存在”），但那个注释仍会被显示。  
         label.addCellFeatures();  
         label.getWritableCellFeatures().setComment("终于加上注释了，哈哈哈哈");  
           
           
//      if (cell instanceof Number) {  
//          Number num = (Number) cell;  
//          num.setCellFeatures(wcfeatures);  
//      } else if (cell instanceof jxl.write.Boolean) {  
//          jxl.write.Boolean bool = (jxl.write.Boolean) cell;  
//          bool.setCellFeatures(wcfeatures);  
//      } else if (cell instanceof jxl.write.DateTime) {  
//          jxl.write.DateTime dt = (jxl.write.DateTime) cell;  
//          dt.setCellFeatures(wcfeatures);  
//      } else {  
//          Label _label = (Label) cell;  
//          _label.setCellFeatures(wcfeatures);  
//      }  
           
         workbook.write();  
         workbook.close();  
         wb.close();  
    }  
  
}  
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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284

多sheet页使用并与MySQL/Oracle数据库连接

背景：
1.xls有多个sheet页，每个sheet页如下图所示，第一行为表名，第二列为字段值，现要判断该字段在该表中有值，有值则记为1，没值或字段不存在均记为0.

import com.sun.deploy.util.StringUtils;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import javax.sql.DataSource;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class sql2excel {
    public static void main(String[] args) throws Exception {
        String fileResult = "C:\\Users\\cara\\Downloads\\2.xls";// 新创建的结果表
        String file = "C:\\Users\\cara\\Downloads\\1.xls";
        Workbook workbook = Workbook.getWorkbook(new File(file));
        WritableWorkbook wwb = Workbook.createWorkbook(new File(fileResult));
        Connection conn = null;
        if (file != null) {
            try {
                int numberOfSheets = workbook.getNumberOfSheets();// sheet页的数量

                for (int n = 0; n < numberOfSheets; n++) {
                    WritableSheet ws = wwb.createSheet("sheet"+n, numberOfSheets);
                    Sheet sheet = workbook.getSheet(n);
                    int rowLen = sheet.getRows();// 数据行
                    int colLen = sheet.getColumns();// 数据列
                    String[] values = new String[rowLen];
                    // 获取所有字段
                    for (int i = 0; i < rowLen; ++i) {// 表中数据行的第几行
                        Cell cell = sheet.getCell(1,i);
                        String content = cell.getContents();
                        if (content != null || content.trim().length() > 0){
                            values[i] = content;
                            Label labelQuery = new Label(1,i,content);
                            ws.addCell(labelQuery);
                        }
                    }
                    // 所有字段写入
                    // 遍历第1行，从第3列开始，取所有表字段
                    for (int i = 2; i < colLen; i++) {
                        Cell cell = sheet.getCell(i,0);
                        String tableName = cell.getContents();
                        // 与数据库建立连接
                        try {
//                        Class. forName("com.mysql.jdbc.Driver");
//                        conn = DriverManager.getConnection("jdbc:mysql://ip:端口号/数据库名","用户名","密码");
                            Class.forName("oracle.jdbc.driver.OracleDriver");
                            conn = DriverManager.getConnection("jdbc:oracle:thin:@//ip:端口号/实例名","用户名","密码");
                            Statement stmt = null;
                            int[] result = new int[rowLen];
                            for (int j = 1; j < rowLen; j++) {
                                String sql = "select "+values[j]+" from "+tableName+" where "+values[j]+" is not null";
                                stmt = conn.createStatement();
                                ResultSet rs = null;
                                try {
                                    rs = stmt.executeQuery(sql);
                                }catch (Exception e) {
                                    continue;
                                }
                                while (rs.next()){
                                    result[j] = 1;
                                    break;
                                }
                            }
                            // 写入
                            System.out.println("---------------写入查询值-----------------------");
                            // 写入查询值：从第3列第1行开始写
                            Label tableNameLabel = new Label(i,0,tableName);
                            ws.addCell(tableNameLabel);
                            for (int j = 1; j < result.length; j++) {
                                Label labelQuery = new Label(i,j,result[j]+"");
                                ws.addCell(labelQuery);
                            }
                        } catch (Exception e) {
                            throw e;
                        }
                    }

                }
            } catch (Exception e) {
                throw e;
            }finally {
                workbook.close();
                wwb.write();
                wwb.close();
                conn.close();
            }
        } else {
            //如果文件为空则抛出异常
            throw new RuntimeException("文件为空！");
        }
        System.out.println("操作完成！");
    }
}
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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

报错

excel文件读写报错：jxl.read.biff.BiffException: Unable to recognize OLE stream

原因：文件版本不兼容，jxl只支持excecl03版

解决办法

另存文件，选择Excel 97-2004工作簿或者97-2003工作簿即可