001: /*
002: * SpreadsheetBootstrapParser.java
003: *
004: * Created on April 3, 2007, 12:56 PM
005: *
006: * To change this template, choose Tools | Template Manager
007: * and open the template in the editor.
008: */
009:
010: package org.netbeans.modules.mashup.db.bootstrap;
011:
012: import com.sun.sql.framework.utils.StringUtil;
013: import java.util.ArrayList;
014: import java.util.Collections;
015: import java.util.List;
016:
017: import java.io.File;
018: import java.io.FileInputStream;
019: import java.io.InputStream;
020: import java.net.URL;
021: import java.sql.Types;
022:
023: import org.netbeans.modules.mashup.db.common.FlatfileDBException;
024: import org.netbeans.modules.mashup.db.common.PropertyKeys;
025: import org.netbeans.modules.mashup.db.common.SQLUtils;
026: import org.netbeans.modules.mashup.db.model.FlatfileDBColumn;
027: import org.netbeans.modules.mashup.db.model.FlatfileDBTable;
028: import org.netbeans.modules.mashup.db.model.impl.FlatfileDBColumnImpl;
029:
030: import jxl.Sheet;
031: import jxl.Workbook;
032: import jxl.WorkbookSettings;
033:
034: /**
035: *
036: * @author ks161616
037: */
038: public class SpreadsheetBootstrapParser implements
039: FlatfileBootstrapParser {
040:
041: /** Creates a new instance of SpreadsheetBootstrapParser */
042: public SpreadsheetBootstrapParser() {
043: }
044:
045: public List buildFlatfileDBColumns(FlatfileDBTable table)
046: throws FlatfileDBException {
047: int defaultPrecision = 60;
048: int jdbcType = SQLUtils.getStdJdbcType(table
049: .getProperty(PropertyKeys.WIZARDDEFAULTSQLTYPE));
050: if (jdbcType == SQLUtils.JDBCSQL_TYPE_UNDEFINED) {
051: jdbcType = Types.VARCHAR;
052: }
053: try {
054: defaultPrecision = Integer
055: .valueOf(
056: table
057: .getProperty(PropertyKeys.WIZARDDEFAULTPRECISION))
058: .intValue();
059: } catch (Exception e) {
060: defaultPrecision = 60;
061: }
062: FlatfileDBColumn[] columns = getColumns(table);
063: List<FlatfileDBColumn> colList = new ArrayList<FlatfileDBColumn>(
064: columns.length);
065: Workbook workbook = null;
066: try {
067: workbook = getWorkBook(table.getProperty(PropertyKeys.URL));
068: } catch (Exception ex) {
069: //ignore
070: }
071: if (workbook != null) {
072: Sheet sheet = workbook.getSheet(table
073: .getProperty(PropertyKeys.SHEET));
074: for (int i = 1; i <= sheet.getColumns(); i++) {
075: FlatfileDBColumn column = null;
076: if (columns != null && i <= columns.length) {
077: column = columns[i - 1];
078: }
079: if (column == null) {
080: column = new FlatfileDBColumnImpl("FIELD_"
081: + String.valueOf(i), jdbcType,
082: defaultPrecision, 0, true);
083: column.setCardinalPosition(i);
084: }
085: colList.add(column);
086: }
087: }
088: return colList;
089: }
090:
091: public void makeGuess(FlatfileDBTable table)
092: throws FlatfileDBException {
093: }
094:
095: public boolean acceptable(FlatfileDBTable table)
096: throws FlatfileDBException {
097: try {
098: getWorkBook(table.getProperty(PropertyKeys.URL));
099: } catch (Exception ex) {
100: return false;
101: }
102: return true;
103: }
104:
105: private WorkbookSettings getWorkbookSettings() {
106: WorkbookSettings settings = new WorkbookSettings();
107: settings.setDrawingsDisabled(true);
108: settings.setAutoFilterDisabled(true);
109: settings.setSuppressWarnings(true);
110: settings.setNamesDisabled(true);
111: settings.setIgnoreBlanks(true);
112: settings.setCellValidationDisabled(true);
113: settings.setFormulaAdjust(false);
114: settings.setPropertySets(false);
115: return settings;
116: }
117:
118: private FlatfileDBColumn[] getColumns(FlatfileDBTable table) {
119: FlatfileDBColumn[] columns = new FlatfileDBColumn[0];
120: if (table.getColumnList().size() > 0) {
121: columns = (FlatfileDBColumn[]) table.getColumnList()
122: .toArray(columns);
123: }
124: return columns;
125: }
126:
127: private Workbook getWorkBook(String fileName) throws Exception {
128: fileName = StringUtil.escapeControlChars(fileName);
129: InputStream in = null;
130: File f = new File(fileName);
131: if (f.exists()) {
132: in = new FileInputStream(f);
133: } else {
134: in = new URL(fileName).openStream();
135: }
136: Workbook spreadsheetData = Workbook.getWorkbook(in,
137: getWorkbookSettings());
138: return spreadsheetData;
139: }
140: }
|