沃梦达 / 编程技术 / 编程语言 / Java开发 / 正文

如何将Excel电子表格配置为javax.sql.DataSource？

Java开发
2023-11-07

我有一个Spring Boot 1.3.0(Java 8)应用程序,需要使用Excel作为数据源. (由于Excel文件的类型,POI和JXL不起作用.)应用程序需要在unix环境中运行.我将应用程序配置为通过yml文件设置数据源url和driver-class-name.是...

我有一个Spring Boot 1.3.0(Java 8)应用程序,需要使用Excel作为数据源. (由于Excel文件的类型,POI和JXL不起作用.)应用程序需要在unix环境中运行.我将应用程序配置为通过yml文件设置数据源url和driver-class-name.

是否有可以使用Excel作为数据源的驱动程序(最好在maven仓库中可用)？我应该为网址指定什么值？

编辑

以下是尝试将Excel电子表格用作javax.sql.DataSource的代码：

SRC /主/资源/ application.yml

---
spring:
    profiles:
        active: development
---        
spring:
    profiles: development
    datasource:
        url: jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/dev/testproj/src/main/resources/test.xls
        driver-class-name: sun.jdbc.odbc.JdbcOdbcDriver

的src / main / JAVA / COM /测试/ TestApplication.java

package com.test;

    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;

    @SpringBootApplication
    public class TestApplication {

        public static void main(String[] args) {
            SpringApplication.run(TestApplication.class, args);
        }
    }

的src /主/ JAVA / COM /测试/批次/ BatchConfiguration.java

package com.test.batch;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.batch.item.ItemProcessor;
import org.springframework.batch.item.ItemReader;
import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.database.JdbcCursorItemReader;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.RowMapper;

@Configuration
@EnableBatchProcessing
public class BatchConfiguration {

    @Bean
    public ItemReader<String> reader(DataSource dataSource) {

        JdbcCursorItemReader<String> itemReader = new JdbcCursorItemReader<String>();
        itemReader.setDataSource(dataSource);
        System.out.println(dataSource);
        itemReader.setSql("SELECT a from Sheet1");
        itemReader.setRowMapper(new RowMapper<String>() { 
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                String here= rs.getString(0);
                System.out.println(here);
                return here;
            }
        });

       return itemReader; 
    }

    @Bean
    public ItemProcessor<String, String> processor() {
        return new ItemProcessor<String, String>() { 
            public String process(final String data) throws Exception {
                return data;
            }
        };
    }

    @Bean
    public ItemWriter<String> writer(DataSource dataSource) {
        return new ItemWriter<String>() {
            public void write(List<? extends String> items) throws Exception {
            }
        };
    }

    @Bean
    public Step step(StepBuilderFactory stepBuilderFactory, ItemReader<String> reader,  ItemProcessor<String, String> processor, ItemWriter<String> writer) {
        return stepBuilderFactory.get("step")
                .<String, String> chunk(1)
                .reader(reader)
                .processor(processor)
                .writer(writer)
                .build();
    }


    @Bean
    public Job importUserJob(JobBuilderFactory jobs, Step step) {
        return jobs.get("importUserJob")
                .incrementer(new RunIdIncrementer())
                .flow(step)
                .end()
                .build();
    }   

}

的build.gradle

buildscript {
    ext {
        springBootVersion = '1.3.0.RELEASE'
    }
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}") 
    }
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'spring-boot' 

jar {
    baseName = 'test-load'
    version = '1.0'
}
sourceCompatibility = 1.8
targetCompatibility = 1.8

repositories {
    mavenCentral()
}


dependencies {
    compile('org.springframework.boot:spring-boot-starter-batch')
    compile('org.springframework.boot:spring-boot-starter-integration')
    testCompile('org.springframework.boot:spring-boot-starter-test') 
}


eclipse {
    classpath {
         containers.remove('org.eclipse.jdt.launching.JRE_CONTAINER')
         containers 'org.eclipse.jdt.launching.JRE_CONTAINER/org.eclipse.jdt.internal.debug.ui.launcher.StandardVMType/JavaSE-1.8'
    }
}

task wrapper(type: Wrapper) {
    gradleVersion = '2.9'
}

tasks.withType(Test) {
    scanForTestClasses = false
    include "**/*Test.class" 
}

执行gradle任务bootRun时会出现以下错误：

2015-12-28 16:01:18.530 ERROR 1356 --- [           main] o.s.batch.core.step.AbstractStep         : Encountered an error executing step step in job importUserJob

org.springframework.batch.item.ItemStreamException: Failed to initialize the reader
    at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:147) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.batch.item.support.CompositeItemStream.open(CompositeItemStream.java:96) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep.open(TaskletStep.java:310) ~[spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:197) ~[spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:67) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:169) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:144) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:134) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) [spring-core-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:128) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_66]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_66]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_66]
    at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_66]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at com.sun.proxy.$Proxy45.run(Unknown Source) [na:na]
    at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.execute(JobLauncherCommandLineRunner.java:215) [spring-boot-autoconfigure-1.3.0.RELEASE.jar:1.3.0.RELEASE]
    at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.executeLocalJobs(JobLauncherCommandLineRunner.java:232) [spring-boot-autoconfigure-1.3.0.RELEASE.jar:1.3.0.RELEASE]
    at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.launchJobFromProperties(JobLauncherCommandLineRunner.java:124) [spring-boot-autoconfigure-1.3.0.RELEASE.jar:1.3.0.RELEASE]
    at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.run(JobLauncherCommandLineRunner.java:118) [spring-boot-autoconfigure-1.3.0.RELEASE.jar:1.3.0.RELEASE]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:792) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:776) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
    at org.springframework.boot.SpringApplication.afterRefresh(SpringApplication.java:763) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
    at org.springframework.boot.SpringApplication.doRun(SpringApplication.java:356) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:295) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1112) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1101) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
    at com.test.TestApplication.main(TestApplication.java:10) [bin/:na]
Caused by: org.springframework.jdbc.BadSqlGrammarException: Executing query; bad SQL grammar [SELECT a from Sheet1]; nested exception is java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: SHEET1
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
    at org.springframework.batch.item.database.JdbcCursorItemReader.openCursor(JdbcCursorItemReader.java:131) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.batch.item.database.AbstractCursorItemReader.doOpen(AbstractCursorItemReader.java:406) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:144) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    ... 36 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: SHEET1
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.springframework.batch.item.database.JdbcCursorItemReader.openCursor(JdbcCursorItemReader.java:120) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
    ... 38 common frames omitted
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: SHEET1
    at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserDQL.readTableName(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserCommand.compilePart(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.ParserCommand.compileStatement(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.Session.compileStatement(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.StatementManager.compile(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
    at org.hsqldb.Session.execute(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]

解决方法:

对于Excel文件c：/temp/test1.xlsx和’Table’abc.您必须完全以此形式编写驱动程序名称！

对于Java8,您需要将所有sun包类从jre7复制到新的jar文件,再从项目文件夹中的jre7 bin文件夹复制JdbcOdbc.dll.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCExcelConnection {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            connection = DriverManager
                            .getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=c:\\Temp\\test1.xlsx;readOnly=false");
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM [abc$]");
        } catch (ClassNotFoundException e) {
            System.err.println("ClassNotFoundException by driver load - " + e);
        } catch (SQLException e) {
            System.err.println("SQLException by connect - " + e);
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (Exception e) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (Exception e) {
                }
            }
        }
    }
}

本文标题为：如何将Excel电子表格配置为javax.sql.DataSource？

上一篇： java – 执行查询后修改MongoDB结果集
下一篇： java – Hibernate比sql查询慢1000倍

基础教程推荐

学习HTML
学习jQuery
学习Laravel
学习CSS3
学习Vue.js
学习Bootstrap5
学习ThinkPHP
学习AJAX
猜你喜欢
最新文章
热门文章
编程基础
热门标签
网站首页 HTML/CSS 菜单 Layui Yii2