복붙노트

[SPRING] Excel 스프레드 시트를 javax.sql.DataSource로 어떻게 구성 할 수 있습니까?

SPRING

Excel 스프레드 시트를 javax.sql.DataSource로 어떻게 구성 할 수 있습니까?

데이터 소스로 Excel을 사용해야하는 Spring Boot 1.3.0 (Java 8) 응용 프로그램이 있습니다. (POI 및 JXL은 Excel 파일 유형으로 인해 작동하지 않습니다.) 응용 프로그램은 유닉스 환경에서 실행해야합니다. yml 파일을 통해 datasource url 및 driver-class-name을 설정하도록 애플리케이션을 구성했습니다.

Excel을 데이터 소스로 사용할 수있는 드라이버가 있습니까? URL에 대해 어떤 값을 지정해야합니까?

편집하다

다음은 Excel 스프레드 시트를 javax.sql.DataSource로 사용하려는 코드입니다.

src / main / resources / 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 / test / 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 / main / java / com / test / batch / 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 task 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]

해결법

  1. ==============================

    1.Excel 파일 c : /temp/test1.xlsx 및 'Table'abc. 이 양식에 드라이버 이름을 정확하게 써야합니다!

    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) {
                }
            }
        }
    }
}
  2. ==============================

    2.Excel에서 읽을 ODBC 브리지가 필요합니다. P 다음 표본을 살펴보십시오. http://www.java2s.com/Tutorial/Java/0340__Database/UseJDBCODBCbridgetoreadfromExcel.htm

    Excel에서 읽을 ODBC 브리지가 필요합니다. P 다음 표본을 살펴보십시오. http://www.java2s.com/Tutorial/Java/0340__Database/UseJDBCODBCbridgetoreadfromExcel.htm

  3. ==============================

    3.Excel을 데이터 소스로 사용하려면 Apache POI를 사용하십시오.

    Excel을 데이터 소스로 사용하려면 Apache POI를 사용하십시오.

    다음은 사용 방법의 예입니다.

    /**
 * This example shows how to use the event API for reading a file.
 */
public class EventExample
        implements HSSFListener
{
    private SSTRecord sstrec;

    /**
     * This method listens for incoming records and handles them as required.
     * @param record    The record that was found while reading.
     */
    public void processRecord(Record record)
    {
        switch (record.getSid())
        {
            // the BOFRecord can represent either the beginning of a sheet or the workbook
            case BOFRecord.sid:
                BOFRecord bof = (BOFRecord) record;
                if (bof.getType() == bof.TYPE_WORKBOOK)
                {
                    System.out.println("Encountered workbook");
                    // assigned to the class level member
                } else if (bof.getType() == bof.TYPE_WORKSHEET)
                {
                    System.out.println("Encountered sheet reference");
                }
                break;
            case BoundSheetRecord.sid:
                BoundSheetRecord bsr = (BoundSheetRecord) record;
                System.out.println("New sheet named: " + bsr.getSheetname());
                break;
            case RowRecord.sid:
                RowRecord rowrec = (RowRecord) record;
                System.out.println("Row found, first column at "
                        + rowrec.getFirstCol() + " last column at " + rowrec.getLastCol());
                break;
            case NumberRecord.sid:
                NumberRecord numrec = (NumberRecord) record;
                System.out.println("Cell found with value " + numrec.getValue()
                        + " at row " + numrec.getRow() + " and column " + numrec.getColumn());
                break;
                // SSTRecords store a array of unique strings used in Excel.
            case SSTRecord.sid:
                sstrec = (SSTRecord) record;
                for (int k = 0; k < sstrec.getNumUniqueStrings(); k++)
                {
                    System.out.println("String table value " + k + " = " + sstrec.getString(k));
                }
                break;
            case LabelSSTRecord.sid:
                LabelSSTRecord lrec = (LabelSSTRecord) record;
                System.out.println("String cell found with value "
                        + sstrec.getString(lrec.getSSTIndex()));
                break;
        }
    }

    /**
     * Read an excel file and spit out what we find.
     *
     * @param args      Expect one argument that is the file to read.
     * @throws IOException  When there is an error processing the file.
     */
    public static void main(String[] args) throws IOException
    {
        // create a new file input stream with the input file specified
        // at the command line
        FileInputStream fin = new FileInputStream(args[0]);
        // create a new org.apache.poi.poifs.filesystem.Filesystem
        POIFSFileSystem poifs = new POIFSFileSystem(fin);
        // get the Workbook (excel part) stream in a InputStream
        InputStream din = poifs.createDocumentInputStream("Workbook");
        // construct out HSSFRequest object
        HSSFRequest req = new HSSFRequest();
        // lazy listen for ALL records with the listener shown above
        req.addListenerForAllRecords(new EventExample());
        // create our event factory
        HSSFEventFactory factory = new HSSFEventFactory();
        // process our events based on the document input stream
        factory.processEvents(req, din);
        // once all the events are processed close our file input stream
        fin.close();
        // and our document input stream (don't want to leak these!)
        din.close();
        System.out.println("done.");
    }
}

    가져온 것 : https://poi.apache.org/spreadsheet/how-to.html

    이 방법을 javax.sql.DataSource로 사용하는 방법에 대한 귀하의 질문에 답변하지는 못하지만, 이렇게하는 것이 잘 지원되는 방법입니다.

    JDBC를 통해이를 사용하려면 http://www.jguru.com/faq/view.jsp?EID=32876을보십시오.

    sun.jdbc.odbc.JdbcOdbcDriver는 Java 8부터 제거되었으므로 다른 대안을 사용하거나 Java 7을 사용해야합니다. Java 8에서 좋은 Jdbc Obdc 브리지를 인식하지 못했습니다.

    4. from https://stackoverflow.com/questions/34345743/how-can-i-configure-an-excel-spreadsheet-as-a-javax-sql-datasource by cc-by-sa and MIT license

'SPRING' 카테고리의 다른 글

[SPRING] Appender 클래스 로깅에서 Spring 빈 액세스  (0) 2019.05.11
[SPRING] 완성 가능한 미래 대 봄 거래  (0) 2019.05.11
[SPRING] jar 파일에서 실행하려고 할 때 스프링 부팅 응용 프로그램이 실행되지 않습니다.  (0) 2019.05.11
[SPRING] Tomcat이 war 파일에서 jsp를 찾을 수 없습니다.  (0) 2019.05.11
[SPRING] Spring 3.2에서 경로 변수의 공백을 자르지 않는다.  (0) 2019.05.11
,

티스토리툴바