//TODO: quite a bit of duplication with the noindex version private bool readWholeWorkSheetWithIndex(XlsBiffIndex idx, bool triggerCreateColumns, DataTable table) { m_dbCellAddrs = idx.DbCellAddresses; for (int index = 0; index < m_dbCellAddrs.Length; index++) { if (m_depth == m_maxRow) break; // init reading data m_cellOffset = findFirstDataCellOffset((int) m_dbCellAddrs[index]); if (m_cellOffset == -2) { return false; } if (m_cellOffset < 0) { return true; } //DataTable columns if (triggerCreateColumns) { if (_isFirstRowAsColumnNames && readWorkSheetRow() || (_isFirstRowAsColumnNames && m_maxRow == 1)) { for (int i = 0; i < m_maxCol; i++) { if (m_cellsValues[i] != null && m_cellsValues[i].ToString().Length > 0) { table.AddColumnHandleDuplicate(m_cellsValues[i].ToString()); } else { table.AddColumnHandleDuplicate(string.Concat(COLUMN, i)); } } } else { for (int i = 0; i < m_maxCol; i++) { table.Columns.Add(null, typeof (Object)); } } triggerCreateColumns = false; table.BeginLoadData(); } while (readWorkSheetRow()) { table.Rows.Add(m_cellsValues); } //add the row if (m_depth > 0 && !(_isFirstRowAsColumnNames && m_maxRow == 1)) { table.Rows.Add(m_cellsValues); } } return true; }
public System.Data.DataSet AsDataSet(bool convertOADateTime) { if (!m_isValid) return null; DataSet dataset = new DataSet(); for (int sheetIndex = 0; sheetIndex < m_workbook.Sheets.Count; sheetIndex++) { DataTable table = new DataTable(m_workbook.Sheets[sheetIndex].Name); ReadSheetGlobals(m_workbook.Sheets[sheetIndex]); if (m_workbook.Sheets[sheetIndex].Dimension == null) continue; m_depth = 0; m_emptyRowCount = 0; // Reada Columns //Console.WriteLine("Read Columns"); if (!m_isFirstRowAsColumnNames) { // No Sheet Columns for (int i = 0; i < m_workbook.Sheets[sheetIndex].ColumnsCount; i++) { table.Columns.Add(null, typeof(Object)); } } else if (ReadSheetRow(m_workbook.Sheets[sheetIndex])) { // Read Sheet Columns //Console.WriteLine("Read Sheet Columns"); for (int index = 0; index < m_cellsValues.Length; index++) { if (m_cellsValues[index] != null && m_cellsValues[index].ToString().Length > 0) { table.AddColumnHandleDuplicate(m_cellsValues[index].ToString()); } else { table.AddColumnHandleDuplicate(string.Concat(COLUMN, index)); } } } else { continue; } // Read Sheet Rows //Console.WriteLine("Read Sheet Rows"); table.BeginLoadData(); while (ReadSheetRow(m_workbook.Sheets[sheetIndex])) { table.Rows.Add(m_cellsValues); } if (table.Rows.Count > 0) { dataset.Tables.Add(table); } // Read HyperLinks //Console.WriteLine("Read Sheet HyperLinks:{0}",table.Rows.Count); ReadHyperLinks(m_workbook.Sheets[sheetIndex],table); table.EndLoadData(); } dataset.AcceptChanges(); dataset.FixDataTypes(); return dataset; }
private void readWholeWorkSheetNoIndex(bool triggerCreateColumns, DataTable table) { while (Read()) { if (m_depth == m_maxRow) break; bool justAddedColumns = false; //DataTable columns if (triggerCreateColumns) { if (_isFirstRowAsColumnNames || (_isFirstRowAsColumnNames && m_maxRow == 1)) { for (int i = 0; i < m_maxCol; i++) { if (m_cellsValues[i] != null && m_cellsValues[i].ToString().Length > 0) table.AddColumnHandleDuplicate(m_cellsValues[i].ToString()); else table.AddColumnHandleDuplicate(string.Concat(COLUMN, i)); } } else { for (int i = 0; i < m_maxCol; i++) { table.Columns.Add(null, typeof(Object)); } } triggerCreateColumns = false; justAddedColumns = true; table.BeginLoadData(); } if (!justAddedColumns && m_depth > 0 && !(_isFirstRowAsColumnNames && m_maxRow == 1)) { table.Rows.Add(m_cellsValues); } } if (m_depth > 0 && !(_isFirstRowAsColumnNames && m_maxRow == 1)) { table.Rows.Add(m_cellsValues); } }
private System.Data.DataSet ReadDataSet() { DataSet dataset=new DataSet(); var demensionDict=DetectDemension(); for (int sheetIndex=0; sheetIndex<m_workbook.Sheets.Count; sheetIndex++) { var sheet=m_workbook.Sheets[sheetIndex]; var table=new DataTable(m_workbook.Sheets[sheetIndex].Name); ReadSheetGlobals(sheet); sheet.Dimension=demensionDict[sheetIndex]; if (sheet.Dimension==null) { continue; } m_depth=0; m_emptyRowCount=0; // Reada Columns //Console.WriteLine("Read Columns"); if (!m_isFirstRowAsColumnNames) { // No Sheet Columns //Console.WriteLine("SheetName:{0}, ColumnCount:{1}", sheet.Name, sheet.ColumnsCount); for (int i=0; i<sheet.ColumnsCount; i++) { table.Columns.Add(null, typeof(Object)); } } else if (ReadSheetRow(sheet)) { // Read Sheet Columns //Console.WriteLine("Read Sheet Columns"); for (int index=0; index<m_cellsValues.Length; index++) { if (m_cellsValues[index]!=null&&m_cellsValues[index].ToString().Length>0) { table.AddColumnHandleDuplicate(m_cellsValues[index].ToString()); } else { table.AddColumnHandleDuplicate(string.Concat(COLUMN, index)); } } } else { continue; } // Read Sheet Rows //Console.WriteLine("Read Sheet Rows"); table.BeginLoadData(); //Console.WriteLine("SheetIndex Is:{0},Name:{1}",sheetIndex,sheet.Name); while (ReadSheetRow(sheet)) { table.Rows.Add(m_cellsValues); } if (table.Rows.Count>0) { dataset.Tables.Add(table); } // Read HyperLinks //Console.WriteLine("Read Sheet HyperLinks:{0}",table.Rows.Count); ReadHyperLinks(sheet, table); table.EndLoadData(); } dataset.AcceptChanges(); dataset.FixDataTypes(); return dataset; }