如何使用Java 8访问Excel作为数据库

Question

我正在使用Java 8。

当我尝试通过jdbc-odbc访问Excel数据（基本上这是我的测试数据）时，我遇到了“java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver”错误。

我也尝试以非DSN的方式访问数据。

我浏览了互联网，发现Oracle已停止支持jdbc-odbc。

那么，使用Java访问此Excel数据的最简单方法是什么？

Connection con=null;
    Statement stmt=null;
    ResultSet rs=null;
    String query = "select TestScript from [TS 360 Scripts$]";

    try
    {
        Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
        con = DriverManager.getConnection("jdbc:odbc:;Driver={Microsoft Excel Driver(*.xlsx)};DBQ=D://TS 360 Script with Count.xlsx");


        stmt=con.createStatement();
        rs=stmt.executeQuery(query);
        while(rs.next())
        {
            System.out.println(rs.getString("TestScript"));
        }

        con.close();
        rs.close();
        stmt.close();
    }
    catch(Exception e)
    {
        e.printStackTrace();
    }

- Uday

---

请查看此链接：http://stackoverflow.com/a/21481078/4385913 - Skizo-ozᴉʞS ツ

Joan Colmenero，正如我在帖子中提到的，我正在使用Java 8。我在连接创建语句处遇到了异常。con = DriverManager.getConnection（ "jdbc：odbc：" + "Driver = {Microsoft Excel Driver（* .xls，* .xlsx，* .xlsm，* .xlsb）};" + "DBQ = C：\ Users \ Gord \ Desktop \ test.xlsx;"+ "ReadOnly = 0;"）; - Uday

请查看此答案链接... - Skizo-ozᴉʞS ツ

Joan Colmenero，正如我所提到的，我正在尝试将Excel作为数据库进行访问。当我尝试使用UcanAccess时，我遇到了“net.ucanaccess.jdbc.UcanaccessSQLException: Decoding not supported. Please choose a CodecProvider which supports reading the current database encoding”异常。 - Uday

Answer 1

不要将Excel文件作为数据库访问。相反，使用像Apache POI这样的jar来处理Microsoft文档。

下载链接：Apache POI For MS Docs- Jar 一个使用此API的示例：

注意：在运行之前，您必须将apache poi jar添加到构建路径中。

package com.dd.selenium;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.concurrent.TimeUnit;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.firefox.FirefoxDriver;


public class PerformDDTest {

    private static HSSFWorkbook xlWBook;

    private static HSSFSheet xlSheet;

    private static HSSFRow xlRow;

    private static HSSFCell xlCell;

    private static String filePath = "/home/dinesh/";

    private static String fileName = "test.xls";

    private static String url = "http://store.demoqa.com/"; 

    private static String result = "Pass";


    public static void main(String[] args) throws InterruptedException {

        try {
            FileInputStream xlFile = 
                    new FileInputStream(filePath+fileName);

            //Access the required test data sheet

            xlWBook =  new HSSFWorkbook(xlFile);

            xlSheet = xlWBook.getSheet("Sheet1");

            xlRow = xlSheet.getRow(1);

            String username = xlRow.getCell(1).getStringCellValue();
            String password = xlRow.getCell(2).getStringCellValue();

            FirefoxDriver driver = new FirefoxDriver();

            driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);

            driver.manage().window().maximize();


            driver.get(url);

            driver.findElement(By.xpath(".//*[@id='account']/a")).click();

            driver.findElement(By.id("log")).sendKeys(username);
            driver.findElement(By.id("pwd")).sendKeys(password);

            driver.findElement(By.id("login")).click();



            driver.findElement(By.xpath(".//*[@id='account_logout']/a")).click();

            Thread.sleep(5000);

            driver.quit();

            setResultCell();


            FileOutputStream fout = new FileOutputStream(filePath+fileName);

            xlWBook.write(fout);

            fout.flush();
            fout.close();



        } catch (IOException e) {
            // TODO Auto-generated catch block
            result = "Failed";
            setResultCell();
            e.printStackTrace();
        }



    }


    private static void setResultCell() {
        xlCell = xlRow.getCell(3, xlRow.RETURN_BLANK_AS_NULL);

        if(xlCell == null ){
            xlCell = xlRow.createCell(3);
            xlCell.setCellValue(result);
        }else{
            xlCell.setCellValue(result);
        }
    }

}

- Dinesh Dontha

---

Dinesh，我非常需要它来完成项目。原因是，我在Excel中有我的测试用例和数据，并且有一个名为“IsExecute”的标志，其值为Yes或No。我需要查找具有Yes的测试用例。虽然我可以使用POI编写代码，但如果我们将上述情况视为数据库，则更容易处理Excel。 - Uday

Answer 2

Uday- 您可以轻松使用Apache POI jar完成您想要做的任何事情。

根据您提到的要求：所有行都具有isExecuted字符串Yes。我尝试使用了这个jar文件。

请尝试此方法。

package com.dd.selenium;

import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class PerformDDTest {

    private static HSSFWorkbook xlWBook;
    private static HSSFSheet xlSheet;
    private static HSSFRow xlRow;
    private static HSSFCell xlCell;
    private static String filePath = "/home/dinesh/";
    private static String fileName = "test.xls";

    public static void main(String[] args) throws InterruptedException {

        try {

            FileInputStream xlFile = new FileInputStream(filePath + fileName);

            // Access the required test data sheet

            xlWBook = new HSSFWorkbook(xlFile);

            // Assuming your data is in Sheet1- if not use your own sheet name
            xlSheet = xlWBook.getSheet("Sheet1");

            // gives row count in sheet
            int noOfRows = xlSheet.getPhysicalNumberOfRows();

            // gives column count in sheet
            xlRow = xlSheet.getRow(0);
            int noOfColumns = xlRow.getLastCellNum();

            // excelData - 2 dimm array - stores all the excel data -Sheet1 only
            String[][] excelData = new String[noOfRows][noOfColumns];

            // r - row c- column
            for (int r = 1; r < noOfRows; r++) {
                for (int c = 0; c < noOfColumns; c++) {
                    xlRow = xlSheet.getRow(r);
                    xlCell = xlRow.getCell(c);

                    // Here we have complete excel data in an array -excelData-

                    excelData[r][c] = xlCell.getStringCellValue();

                    // System.out.println("row: " + r + " column: " + c);
                    // System.out.println(excelData[r][c]);
                }
            }

            // creating an array to store isExected column
            String[][] isExecuted = new String[noOfRows][1];

            for (int row = 1; row < noOfRows; row++) {
                // here column is always only one
                // so c=0

                // extracting a isExecuted column - and considering it as last
                // column in sheet
                // in your case it is not then - count the column position : use
                // position-1
                // ex: if column position is 7 then use 6 as below
                // isExecuted[row][0]= excelData[row][6];

                isExecuted[row][0] = excelData[row][noOfColumns - 1];

                if (isExecuted[row][0].equalsIgnoreCase("yes")) {

                    // accessing complete row -which isExecuted=Yes

                    // *********IMPORTANT*****
                    for (int col = 0; col < noOfColumns; col++) {
                        // prints all the rows where isExecuted column has Yes
                        System.out.println(excelData[row][col]);
                    }
                }

                // System.out.println(isExecuted[row][0]);

            }

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}

我使用了这个Excel数据：

Test Case Name  Username    Password    Results IsExecute
APACHE_POI_TC   testuser_1  Test@123    Pass    Yes
APACHE_POI_TC   testuser_2  Test@124    Pass    No
APACHE_POI_TC   testuser_3  Test@125    Pass    Yes
APACHE_POI_TC   testuser_4  Test@126    Pass    Yes
APACHE_POI_TC   testuser_5  Test@127    Pass    No
APACHE_POI_TC   testuser_6  Test@128    Pass    Yes

- Dinesh Dontha

Answer 3

或许有些晚了，但如果您仍然遇到这个问题，您可以使用Fillo在Java 8中将Excel作为数据库访问：http://codoid.com/fillo/

- Sai

---

Sai：这不是一个可以在社区许可下使用的开源工具。 - Sam

@Sam：链接显示它是在Apache许可证下发布的。但不幸的是，它不包含JDBC驱动程序。 - user330315