001: package com.avaje.util.seedgen;
002:
003: import java.util.ArrayList;
004: import java.util.Iterator;
005:
006: import jxl.Cell;
007: import jxl.Sheet;
008:
009: /**
010: * The excel sheet containing seed information.
011: */
012: public class XlSeedDataSheet {
013:
014: /**
015: * The underlying sheet.
016: */
017: Sheet sheet;
018:
019: /**
020: * The data columns.
021: */
022: ArrayList columnList = new ArrayList();
023:
024: /**
025: * The number of gap columns to the left.
026: */
027: int columnIndexOffset = 0;
028:
029: /**
030: * Row where the datatype info is set.
031: */
032: int columnDatatypeRowIndex = 0;
033:
034: /**
035: * Row where the column names are set.
036: */
037: int columnNameRowIndex = 1;
038:
039: /**
040: * The row where the data starts.
041: */
042: int firstRowIndex = 2;
043:
044: /**
045: * The length of the null string.
046: */
047: int nullDataWidth = 4;
048:
049: /**
050: * The derived number of rows of data.
051: */
052: int rowCount;
053:
054: /**
055: * The derived number of columns.
056: */
057: int columnCount;
058:
059: /**
060: * The database table name.
061: */
062: String tableName;
063:
064: /**
065: * Flag to indicate the sheet is empty.
066: */
067: boolean isEmpty = false;
068:
069: public XlSeedDataSheet(Sheet sheet) {
070: this .sheet = sheet;
071: if (sheet.getColumns() == 0 || sheet.getRows() < 3) {
072: isEmpty = true;
073:
074: } else {
075: tableName = sheet.getName();
076:
077: //tableName = determineTableName();
078:
079: // parse the columns...
080: columnCount = determineColumns();
081:
082: // find the last row of data
083: rowCount = determineRowCount();
084:
085: derivedMaxColumnWidths();
086: }
087: }
088:
089: /**
090: * Return true if the sheet is empty and should be ignored.
091: */
092: public boolean isEmpty() {
093: return isEmpty;
094: }
095:
096: public String toString() {
097: StringBuffer sb = new StringBuffer();
098: sb.append(tableName);
099: int gap = 20 - tableName.length();
100: if (gap > 0) {
101: for (int i = 0; i < gap; i++) {
102: sb.append(" ");
103: }
104: }
105: Iterator it = columnList.iterator();
106: while (it.hasNext()) {
107: XlSeedDataColumn col = (XlSeedDataColumn) it.next();
108: sb.append(" [").append(col.getName()).append(",");
109: sb.append(col.getBaseCellType()).append("]");
110: }
111: return sb.toString();
112: }
113:
114: /**
115: * Return the database table name for this sheet.
116: */
117: public String getTableName() {
118: return tableName;
119: }
120:
121: /**
122: * Return the content for a cell.
123: */
124: public String getContent(int dataCol, int dataRow) {
125:
126: Cell cell = getCell(dataCol, dataRow);
127: return cell.getContents();
128: }
129:
130: /**
131: * Return the cell. These indexes are relative
132: * to the row data rather than the sheet itself.
133: */
134: public Cell getCell(int dataCol, int dataRow) {
135:
136: if (dataRow >= rowCount) {
137: return null;
138: }
139: if (dataCol >= getColumnCount()) {
140: return null;
141: }
142: int ci = dataCol + columnIndexOffset;
143: int ri = dataRow + firstRowIndex;
144: return sheet.getCell(ci, ri);
145: }
146:
147: /**
148: * Return the number of data rows in this sheet.
149: */
150: public int getRowCount() {
151: return rowCount;
152: }
153:
154: /**
155: * Return the number of data columns in this sheet.
156: */
157: public int getColumnCount() {
158: return columnCount;
159: }
160:
161: /**
162: * Return the DataColumn.
163: */
164: public XlSeedDataColumn getDataColumn(int index) {
165: return (XlSeedDataColumn) columnList.get(index);
166: }
167:
168: /**
169: * Return all the XlSeedDataColumn.
170: */
171: public Iterator columns() {
172: return columnList.iterator();
173: }
174:
175: private int determineRowCount() {
176: // data starts one row after the column names
177: int rowIndex = firstRowIndex;
178:
179: // the first column is assumed to be always not null
180: int columnIndex = columnIndexOffset;
181:
182: boolean moreRows = true;
183: while (moreRows && rowIndex < sheet.getRows()) {
184: String data = sheet.getCell(columnIndex, rowIndex)
185: .getContents();
186: if (data == null || data.trim().length() == 0) {
187: moreRows = false;
188: } else {
189: rowIndex++;
190: }
191: }
192: return (rowIndex - firstRowIndex);
193: }
194:
195: private int determineColumns() {
196:
197: int columnIndex = columnIndexOffset;
198: boolean moreColumns = true;
199:
200: while (moreColumns && columnIndex < sheet.getColumns()) {
201: Cell cell = sheet.getCell(columnIndex, columnNameRowIndex);
202: String colName = cell.getContents();
203: if (colName == null || colName.trim().length() == 0) {
204: moreColumns = false;
205:
206: } else {
207: String dataTypeCode = sheet.getCell(columnIndex,
208: columnDatatypeRowIndex).getContents();
209:
210: XlSeedDataColumn dataColumn = new XlSeedDataColumn();
211: dataColumn.setSpreadSheetColumnIndex(columnIndex);
212: dataColumn.setName(colName);
213: dataColumn.setDatatypeCode(dataTypeCode);
214:
215: columnList.add(dataColumn);
216:
217: columnIndex++;
218: }
219: }
220: return columnList.size();
221: }
222:
223: private void derivedMaxColumnWidths() {
224: for (int i = 0; i < getColumnCount(); i++) {
225:
226: XlSeedDataColumn dataColumn = getDataColumn(i);
227: for (int j = 0; j < getRowCount(); j++) {
228: Cell cell = getCell(i, j);
229: dataColumn.containsCell(cell);
230: }
231: }
232: }
233:
234: /**
235: * Print out the data in a csv like format.
236: */
237: public void debugPrint() {
238: for (int i = 0; i < getRowCount(); i++) {
239: StringBuffer row = new StringBuffer();
240: for (int j = 0; j < getColumnCount(); j++) {
241: Cell c = getCell(j, i);
242: String content = c.getContents();
243: row.append(content).append(",");
244: }
245: System.out.println(row.toString());
246: }
247: }
248: }
|