private static DataTable AsDataTable(IExcel self, ExcelDataTableConfiguration configuration)
{
var result = new DataTable {
TableName = self.Name
};
result.ExtendedProperties.Add("visiblestate", self.VisibleState);
var first = true;
var emptyRows = 0;
var columnIndices = new List <int>();
while (self.Read())
{
if (first)
{
if (configuration.UseHeaderRow && configuration.ReadHeaderRow != null)
{
configuration.ReadHeaderRow(self);
}
for (var i = 0; i < self.FieldCount; i++)
{
if (configuration.FilterColumn != null && !configuration.FilterColumn(self, i))
{
continue;
}
var name = configuration.UseHeaderRow
? Convert.ToString(self.GetValue(i))
: null;
if (string.IsNullOrEmpty(name))
{
name = configuration.EmptyColumnNamePrefix + i;
}
// if a column already exists with the name append _i to the duplicates
var columnName = GetUniqueColumnName(result, name);
var column = new DataColumn(columnName, typeof(object))
{
Caption = name
};
result.Columns.Add(column);
columnIndices.Add(i);
}
result.BeginLoadData();
first = false;
if (configuration.UseHeaderRow)
{
continue;
}
}
if (configuration.FilterRow != null && !configuration.FilterRow(self))
{
continue;
}
if (IsEmptyRow(self, configuration))
{
emptyRows++;
continue;
}
for (var i = 0; i < emptyRows; i++)
{
result.Rows.Add(result.NewRow());
}
emptyRows = 0;
var row = result.NewRow();
for (var i = 0; i < columnIndices.Count; i++)
{
var columnIndex = columnIndices[i];
var value = self.GetValue(columnIndex);
if (configuration.TransformValue != null)
{
var transformedValue = configuration.TransformValue(self, i, value);
if (transformedValue != null)
{
value = transformedValue;
}
}
row[i] = value;
}
result.Rows.Add(row);
}
result.EndLoadData();
return(result);
}
private static void FixDataTypes(DataSet dataset)
{
var tables = new List <DataTable>(dataset.Tables.Count);
bool convert = false;
foreach (DataTable table in dataset.Tables)
{
if (table.Rows.Count == 0)
{
tables.Add(table);
continue;
}
DataTable newTable = null;
for (int i = 0; i < table.Columns.Count; i++)
{
Type type = null;
foreach (DataRow row in table.Rows)
{
if (row.IsNull(i))
{
continue;
}
var curType = row[i].GetType();
if (curType != type)
{
if (type == null)
{
type = curType;
}
else
{
type = null;
break;
}
}
}
if (type == null)
{
continue;
}
convert = true;
if (newTable == null)
{
newTable = table.Clone();
}
newTable.Columns[i].DataType = type;
}
if (newTable != null)
{
newTable.BeginLoadData();
foreach (DataRow row in table.Rows)
{
newTable.ImportRow(row);
}
newTable.EndLoadData();
tables.Add(newTable);
}
else
{
tables.Add(table);
}
}
if (convert)
{
dataset.Tables.Clear();
dataset.Tables.AddRange(tables.ToArray());
}
}
private bool ReadWorksheet(XlsWorksheet sheet)
{
m_stream.Seek((int)sheet.DataOffset, SeekOrigin.Begin);
XlsBiffBOF bof = m_stream.Read() as XlsBiffBOF;
if (bof == null || bof.Type != BIFFTYPE.Worksheet)
{
return(false);
}
XlsBiffIndex idx = m_stream.Read() as XlsBiffIndex;
if (null == idx)
{
return(false);
}
idx.IsV8 = IsV8();
DataTable dt = new DataTable(sheet.Name);
XlsBiffRecord trec;
XlsBiffDimensions dims = null;
do
{
trec = m_stream.Read();
if (trec.ID == BIFFRECORDTYPE.DIMENSIONS)
{
dims = (XlsBiffDimensions)trec;
break;
}
} while (trec != null && trec.ID != BIFFRECORDTYPE.ROW);
int maxCol = 256;
if (dims != null)
{
dims.IsV8 = IsV8();
maxCol = dims.LastColumn - 1;
sheet.Dimensions = dims;
}
InitializeColumns(ref dt, maxCol);
sheet.Data = dt;
uint maxRow = idx.LastExistingRow;
if (idx.LastExistingRow <= idx.FirstExistingRow)
{
return(true);
}
dt.BeginLoadData();
for (int i = 0; i < maxRow; i++)
{
dt.Rows.Add(dt.NewRow());
}
uint[] dbCellAddrs = idx.DbCellAddresses;
for (int i = 0; i < dbCellAddrs.Length; i++)
{
XlsBiffDbCell dbCell = (XlsBiffDbCell)m_stream.ReadAt((int)dbCellAddrs[i]);
XlsBiffRow row = null;
int offs = dbCell.RowAddress;
do
{
row = m_stream.ReadAt(offs) as XlsBiffRow;
if (row == null)
{
break;
}
offs += row.Size;
} while (null != row);
while (true)
{
XlsBiffRecord rec = m_stream.ReadAt(offs);
offs += rec.Size;
if (rec is XlsBiffDbCell)
{
break;
}
if (rec is XlsBiffEOF)
{
break;
}
XlsBiffBlankCell cell = rec as XlsBiffBlankCell;
if (cell == null)
{
continue;
}
if (cell.ColumnIndex >= maxCol)
{
continue;
}
if (cell.RowIndex > maxRow)
{
continue;
}
string _sValue;
double _dValue;
switch (cell.ID)
{
case BIFFRECORDTYPE.INTEGER:
case BIFFRECORDTYPE.INTEGER_OLD:
dt.Rows[cell.RowIndex][cell.ColumnIndex] = ((XlsBiffIntegerCell)cell).Value.ToString();
break;
case BIFFRECORDTYPE.NUMBER:
case BIFFRECORDTYPE.NUMBER_OLD:
_dValue = ((XlsBiffNumberCell)cell).Value;
if ((_sValue = TryConvertOADate(_dValue, cell.XFormat)) != null)
{
dt.Rows[cell.RowIndex][cell.ColumnIndex] = _sValue;
}
else
{
dt.Rows[cell.RowIndex][cell.ColumnIndex] = _dValue;
}
break;
case BIFFRECORDTYPE.LABEL:
case BIFFRECORDTYPE.LABEL_OLD:
case BIFFRECORDTYPE.RSTRING:
dt.Rows[cell.RowIndex][cell.ColumnIndex] = ((XlsBiffLabelCell)cell).Value;
break;
case BIFFRECORDTYPE.LABELSST:
string tmp = m_globals.SST.GetString(((XlsBiffLabelSSTCell)cell).SSTIndex);
dt.Rows[cell.RowIndex][cell.ColumnIndex] = tmp;
break;
case BIFFRECORDTYPE.RK:
_dValue = ((XlsBiffRKCell)cell).Value;
if ((_sValue = TryConvertOADate(_dValue, cell.XFormat)) != null)
{
dt.Rows[cell.RowIndex][cell.ColumnIndex] = _sValue;
}
else
{
dt.Rows[cell.RowIndex][cell.ColumnIndex] = _dValue;
}
break;
case BIFFRECORDTYPE.MULRK:
XlsBiffMulRKCell _rkCell = (XlsBiffMulRKCell)cell;
for (ushort j = cell.ColumnIndex; j <= _rkCell.LastColumnIndex; j++)
{
dt.Rows[cell.RowIndex][j] = _rkCell.GetValue(j);
}
break;
case BIFFRECORDTYPE.BLANK:
case BIFFRECORDTYPE.BLANK_OLD:
case BIFFRECORDTYPE.MULBLANK:
// Skip blank cells
break;
case BIFFRECORDTYPE.FORMULA:
case BIFFRECORDTYPE.FORMULA_OLD:
object _oValue = ((XlsBiffFormulaCell)cell).Value;
if (null != _oValue && _oValue is FORMULAERROR)
{
_oValue = null;
}
if (null != _oValue &&
(_sValue = TryConvertOADate(_oValue, cell.XFormat)) != null)
{
dt.Rows[cell.RowIndex][cell.ColumnIndex] = _sValue;
}
else
{
dt.Rows[cell.RowIndex][cell.ColumnIndex] = _oValue;
}
break;
default:
break;
}
}
}
dt.EndLoadData();
if (m_PromoteToColumns)
{
RemapColumnsNames(ref dt, dt.Rows[0].ItemArray);
dt.Rows.RemoveAt(0);
dt.AcceptChanges();
}
return(true);
}
public System.Data.DataSet AsDataSet(bool convertOADateTime)
{
if (!_isValid)
{
return(null);
}
DataSet dataset = new DataSet();
for (int ind = 0; ind < _workbook.Sheets.Count; ind++)
{
DataTable table = new DataTable(_workbook.Sheets[ind].Name);
table.ExtendedProperties.Add("visiblestate", _workbook.Sheets[ind].VisibleState);
ReadSheetGlobals(_workbook.Sheets[ind]);
if (_workbook.Sheets[ind].Dimension == null)
{
continue;
}
_depth = 0;
_emptyRowCount = 0;
//DataTable columns
if (!_isFirstRowAsColumnNames)
{
for (int i = 0; i < _workbook.Sheets[ind].ColumnsCount; i++)
{
table.Columns.Add(null, typeof(Object));
}
}
else if (ReadSheetRow(_workbook.Sheets[ind]))
{
for (int index = 0; index < _cellsValues.Length; index++)
{
if (_cellsValues[index] != null && _cellsValues[index].ToString().Length > 0)
{
Helpers.AddColumnHandleDuplicate(table, _cellsValues[index].ToString());
}
else
{
Helpers.AddColumnHandleDuplicate(table, string.Concat(COLUMN, index));
}
}
}
else
{
continue;
}
table.BeginLoadData();
while (ReadSheetRow(_workbook.Sheets[ind]))
{
table.Rows.Add(_cellsValues);
}
if (table.Rows.Count > 0)
{
dataset.Tables.Add(table);
}
table.EndLoadData();
}
dataset.AcceptChanges();
Helpers.FixDataTypes(dataset);
return(dataset);
}
private DataTable readWholeWorkSheet(XlsWorksheet sheet)
{
XlsBiffIndex idx;
if (!readWorkSheetGlobals(sheet, out idx))
{
return(null);
}
DataTable table = new DataTable(sheet.Name);
bool triggerCreateColumns = true;
m_dbCellAddrs = idx.DbCellAddresses;
for (int index = 0; index < m_dbCellAddrs.Length; index++)
{
if (m_depht == m_maxRow)
{
break;
}
// init reading data
m_cellOffset = findFirstDataCellOffset((int)m_dbCellAddrs[index]);
//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.Columns.Add(m_cellsValues[i].ToString());
}
else
{
table.Columns.Add(string.Concat(COLUMN, i));
}
}
}
else
{
for (int i = 0; i < m_maxCol; i++)
{
table.Columns.Add();
}
}
triggerCreateColumns = false;
table.BeginLoadData();
}
while (readWorkSheetRow())
{
table.Rows.Add(m_cellsValues);
}
if (m_depht > 0 && !(_isFirstRowAsColumnNames && m_maxRow == 1))
{
table.Rows.Add(m_cellsValues);
}
}
table.EndLoadData();
return(table);
}