文章目录

• 下载jxl包并引用
• 基本使用
• • 多sheet页使用并与MySQL/Oracle数据库连接
• 报错
• • excel文件读写报错：jxl.read.biff.BiffException: Unable to recognize OLE stream
  • • 原因：文件版本不兼容，jxl只支持excecl03版
    • 解决办法

下载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();  
    }  
  
}  

多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("操作完成！");
    }
}

报错

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

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

解决办法

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