/tst/org/diffkit/diff/sns/tst/TestSpreadSheetSource.groovy

  1/**
  2 * Copyright 2010-2011 Joseph Panico
  3 *
  4 * Licensed under the Apache License, Version 2.0 (the "License");
  5 * you may not use this file except in compliance with the License.
  6 * You may obtain a copy of the License at
  7 *
  8 *   http://www.apache.org/licenses/LICENSE-2.0
  9 *
 10 * Unless required by applicable law or agreed to in writing, software
 11 * distributed under the License is distributed on an "AS IS" BASIS,
 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 13 * See the License for the specific language governing permissions and
 14 * limitations under the License.
 15 */
 16package org.diffkit.diff.sns.tst
 17
 18
 19
 20
 21import groovy.util.GroovyTestCase;
 22import java.sql.Time 
 23import java.sql.Timestamp 
 24
 25import org.diffkit.diff.engine.DKColumnModel;
 26import org.diffkit.diff.engine.DKTableModel 
 27import org.diffkit.diff.engine.DKColumnModel.Type 
 28import org.diffkit.diff.sns.DKSpreadSheetFileSource 
 29import org.diffkit.util.DKResourceUtil 
 30import org.diffkit.util.DKTimeUtil 
 31
 32
 33/**
 34 * @author jpanico
 35 */
 36public class TestSpreadSheetSource extends GroovyTestCase {
 37   
 38   public void testReadHard() {
 39      def sourceFile = DKResourceUtil.findResourceAsFile('xcel_test.xls', this)
 40      println "sourceFile->$sourceFile"
 41      assert sourceFile
 42      def source = new DKSpreadSheetFileSource(sourceFile.absolutePath, 'Sheet1', null, null, null, true, true, false)
 43      source.open(null)
 44      def aRow = source.nextRow
 45      assert aRow
 46      assert aRow.class == Object[].class
 47      // first row should be ROW_NUM=2, because there is a header
 48      assert aRow[0] == 2
 49      source.nextRow
 50      source.nextRow
 51      aRow = source.nextRow
 52      assert aRow
 53      assert aRow[0] == 5
 54      assert aRow[0].class == Integer.class
 55      assert aRow[1] == -2222
 56      assert aRow[1].class == Long.class
 57      assert aRow[2] == '       '
 58      assert aRow[2].class == String.class
 59      assert aRow[3] == 0.0
 60      assert aRow[3].class == BigDecimal.class
 61      assert aRow[4] == 3.0
 62      assert aRow[4].class == BigDecimal.class
 63      assert aRow[5] == '-1.0'
 64      assert aRow[5].class == String.class
 65      // ????
 66      assert aRow[6].toString() == '31-Dec-1899'
 67      assert aRow[6].class == String.class
 68      assert aRow[7] == 14.2
 69      assert aRow[7].class == Double.class
 70      assert aRow[8] == 'FALSE'
 71      assert aRow[8].class == String.class
 72      assert ! source.nextRow
 73      assert ! source.nextRow
 74      assert source.lastIndex == 3
 75   }
 76   
 77   public void testReadEasy() {
 78      def sourceFile = DKResourceUtil.findResourceAsFile('xcel_test.xls', this)
 79      println "sourceFile->$sourceFile"
 80      assert sourceFile
 81      def source = new DKSpreadSheetFileSource(sourceFile.absolutePath, 'easy sheet', null, null, null, true, false, false)
 82      source.open(null)
 83      def aRow = source.nextRow
 84      assert aRow
 85      assert aRow.class == Object[].class
 86      assert aRow[0] == 1
 87      assert aRow[0].class == Integer.class
 88      assert aRow[1] == 'aaaa'
 89      assert aRow[1].class == String.class
 90      assert aRow[2] == 1111
 91      assert aRow[2].class == BigDecimal.class
 92      assert aRow[3] == DKTimeUtil.createDate( 2008, 0, 1)
 93      assert aRow[3].class == Date.class
 94      assert aRow[4] == 'zzzz'
 95      assert aRow[4].class == String.class
 96      assert aRow[5].toString() == '00:31:31'
 97      assert aRow[5].class == Time.class
 98      assert aRow[6] == 1234
 99      assert aRow[6].class == BigDecimal.class
100      assert aRow[7] == 123456.78
101      assert aRow[7].class == BigDecimal.class
102      assert aRow[8] == 1234.5678
103      assert aRow[8].class == BigDecimal.class
104      assert aRow[9] == 1234.5678
105      assert aRow[9].class == BigDecimal.class
106      assert aRow[10].toString() == '2004-05-23 14:25:10.487'
107      assert aRow[10].class == Timestamp.class
108      assert aRow[11] == 'TRUE'
109      assert aRow[11].class == String.class
110      assert aRow[12] == 10
111      assert aRow[12].class == Long.class
112      assert aRow[13] == 12345
113      assert aRow[13].class == BigDecimal.class
114      assert aRow[14] == 'column14'
115      assert aRow[14].class == String.class
116      assert aRow[15] == 'column15'
117      assert aRow[15].class == String.class
118      assert aRow[16] == 'my clobby text'
119      assert aRow[16].class == String.class
120      assert source.lastIndex ==0
121      (1..19).each { assert source.nextRow }
122      assert ! source.nextRow
123      assert source.lastIndex == 19
124   }
125   
126   public void testModelColumns() {
127      def sourceFile = DKResourceUtil.findResourceAsFile('xcel_test.xls', this)
128      println "sourceFile->$sourceFile"
129      assert sourceFile
130      assert sourceFile.canRead()
131      
132      // explicitly specified sheet name happens to be first sheet, no model, with header
133      def source = new DKSpreadSheetFileSource(sourceFile.absolutePath, 'Sheet1', null, null, null, true, true, false)
134      def model = source.model
135      assert model
136      assert model.name == 'Sheet1'
137      assert model.keyColumnNames == (String[])['<ROW_NUM>']
138      assert model.columns.length == 9
139      assert model.columns[0].name == '<ROW_NUM>'
140      assert model.columns[0].type == Type.INTEGER
141      assert model.columns[1].name == 'COLUMN1'
142      assert model.columns[1].type == Type.INTEGER
143      assert model.columns[2].name == 'COLUMN2'
144      assert model.columns[2].type == Type.STRING
145      assert model.columns[3].name == 'COLUMN3'
146      assert model.columns[3].type == Type.DECIMAL
147      assert model.columns[5].name == 'COLUMN5'
148      assert model.columns[5].type == Type.MIXED
149      assert model.columns[7].name == 'COLUMN7'
150      assert model.columns[7].type == Type.REAL
151      
152      source.open(null)
153      source.close(null)
154      
155      // no sheet name supplied, default sheet is first sheet (Sheet1)
156      source = new DKSpreadSheetFileSource(sourceFile.absolutePath, null, null, null, null, true, true, false)
157      model = source.model
158      assert model
159      assert model.name == 'Sheet1'
160      
161      // explicitly specified sheet name is not same as default, no header so get default header names
162      source = new DKSpreadSheetFileSource(sourceFile.absolutePath, 'easy sheet', null, null, null, true, false, false)
163      model = source.model
164      assert model
165      assert model.name == 'easy sheet'
166      assert model.keyColumnNames == (String[])['<ROW_NUM>']
167      assert model.columns.length == 17
168      assert model.columns[0].name == '<ROW_NUM>'
169      assert model.columns[0].type == Type.INTEGER
170      assert model.columns[1].name == 'A'
171      assert model.columns[1].type == Type.STRING
172      assert model.columns[2].name == 'B'
173      assert model.columns[2].type == Type.DECIMAL
174      assert model.columns[3].name == 'C'
175      assert model.columns[3].type == Type.DATE
176      assert model.columns[5].name == 'E'
177      assert model.columns[5].type == Type.TIME
178      assert model.columns[10].name == 'J'
179      assert model.columns[10].type == Type.TIMESTAMP
180      assert model.columns[11].name == 'K'
181      assert model.columns[11].type == Type.STRING
182      
183      // automatic Model extraction, but user-supplied key
184      source = new DKSpreadSheetFileSource(sourceFile.absolutePath, 'Sheet1', null, (String[])['COLUMN1'], null, true, true, false)
185      model = source.model
186      assert model
187      assert model.name == 'Sheet1'
188      assert model.keyColumnNames == (String[])['COLUMN1']
189      // NOTE that when have user-supplied key, the ROW_NUM is no longer included
190      assert model.columns.length == 8
191      
192      // supply own model
193      DKColumnModel col1 = [0, 'col1', DKColumnModel.Type.DECIMAL]
194      DKColumnModel col2 = [1, 'col2', DKColumnModel.Type.STRING]
195      DKTableModel myModel = ['myModel', (DKColumnModel[])[col1, col2], (int[])[0]]
196      source = new DKSpreadSheetFileSource(sourceFile.absolutePath, 'Sheet1', myModel, null, null, true, true, false)
197      model = source.model
198      assert model
199      assert model.name == 'myModel'
200      assert model.keyColumnNames == (String[])['col1']
201      assert model.columns.length == 2
202      assert model.columns[0].name == 'col1'
203      assert model.columns[1].name == 'col2'
204   }
205}