Java tutorial
/* * Copyright 2012 pcal.net * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package net.pcal.sqlsheet; import com.googlecode.sqlsheet.stream.XlsStreamConnection; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.*; import java.net.URL; import java.net.URLDecoder; import java.sql.*; import java.util.Properties; import java.util.logging.Level; import java.util.logging.Logger; /** * SqlSheet implementation of java.sql.Driver. * * @author <a href='http://www.pcal.net'>pcal</a> * @author <a href='http://code.google.com/p/sqlsheet'>sqlsheet</a> */ public class XlsDriver implements Driver { private static final String URL_SCHEME = "jdbc:xls:"; static final String READ_STREAMING = "readStreaming"; static final String WRITE_STREAMING = "writeStreaming"; static final String HEADLINE = "headLine"; private static final Logger logger = Logger.getLogger(XlsDriver.class.getName()); static { try { DriverManager.registerDriver(new XlsDriver()); } catch (SQLException e) { logger.log(Level.SEVERE, "Couldn't register " + XlsDriver.class.getName(), e); } } public DriverPropertyInfo[] getPropertyInfo(String url, Properties info) { return new DriverPropertyInfo[0]; } public Connection connect(String jdbcUrl, Properties info) throws SQLException { if (jdbcUrl == null) throw new IllegalArgumentException("Null url"); if (!acceptsURL(jdbcUrl)) return null; // why is this necessary? if (!jdbcUrl.toLowerCase().startsWith(URL_SCHEME)) { throw new IllegalArgumentException("URL is not " + URL_SCHEME + " (" + jdbcUrl + ")"); } // strip any properties from end of URL and set them as additional properties String urlProperties; int questionIndex = jdbcUrl.indexOf('?'); if (questionIndex >= 0) { info = new Properties(info); urlProperties = jdbcUrl.substring(questionIndex); String[] split = urlProperties.substring(1).split("&"); for (String each : split) { String[] property = each.split("="); try { if (property.length == 2) { String key = URLDecoder.decode(property[0], "UTF-8"); String value = URLDecoder.decode(property[1], "UTF-8"); info.setProperty(key, value); } else if (property.length == 1) { String key = URLDecoder.decode(property[0], "UTF-8"); info.setProperty(key, Boolean.TRUE.toString()); } else { throw new SQLException("Invalid property: " + each); } } catch (UnsupportedEncodingException e) { // we know UTF-8 is available } } jdbcUrl = jdbcUrl.substring(0, questionIndex); } try { URL workbookUrl = new URL(jdbcUrl.substring(URL_SCHEME.length())); //If streaming requested for read if (has(info, READ_STREAMING)) { return new XlsStreamConnection(workbookUrl); } else if (workbookUrl.getProtocol().equalsIgnoreCase("file")) { //If streaming requested for write if (has(info, WRITE_STREAMING)) { return new XlsConnection(getOrCreateXlsxStream(workbookUrl), new File(workbookUrl.getPath()), info); } return new XlsConnection(getOrCreateWorkbook(workbookUrl), new File(workbookUrl.getPath()), info); } else { //If plain url provided return new XlsConnection(WorkbookFactory.create(workbookUrl.openStream()), info); } } catch (Exception e) { SQLException sqe = new SQLException(e.getMessage()); sqe.initCause(e); throw sqe; } } boolean has(Properties info, String key) { Object value = info.get(key); if (value == null) { return false; } return value.equals(Boolean.TRUE.toString()); } private SXSSFWorkbook getOrCreateXlsxStream(URL workbookUrl) throws IOException, InvalidFormatException { if (workbookUrl.getProtocol().equalsIgnoreCase("file")) { File source = new File(workbookUrl.getPath()); if (source.exists() || (source.length() != 0)) { logger.warning("File " + source.getPath() + " is not empty, and will parsed to memory!"); } else { Workbook workbook = new XSSFWorkbook(); flushWorkbook(workbook, source); } } return new SXSSFWorkbook(new XSSFWorkbook(workbookUrl.openStream()), 1000, false); } private Workbook getOrCreateWorkbook(URL workbookUrl) throws IOException, InvalidFormatException { if (workbookUrl.getProtocol().equalsIgnoreCase("file")) { File file = new File(workbookUrl.getPath()); if (!file.exists() || (file.length() == 0)) { Workbook workbook; if (file.getPath().toLowerCase().endsWith("x")) { workbook = new XSSFWorkbook(); } else { workbook = new HSSFWorkbook(); } flushWorkbook(workbook, file); } } return WorkbookFactory.create(workbookUrl.openStream()); } private void flushWorkbook(Workbook workbook, File file) throws IOException { FileOutputStream fileOut = null; try { fileOut = new FileOutputStream(file); workbook.write(fileOut); fileOut.flush(); } finally { if (fileOut != null) { try { fileOut.close(); } catch (IOException e) { logger.log(Level.WARNING, e.getMessage(), e); } } } } public boolean acceptsURL(String url) throws SQLException { return url != null && url.trim().toLowerCase().startsWith(URL_SCHEME); } public boolean jdbcCompliant() { // LOLZ! return false; } public int getMajorVersion() { return 1; } public int getMinorVersion() { return 0; } public Logger getParentLogger() throws SQLFeatureNotSupportedException { throw new UnsupportedOperationException("Not supported yet."); } }