public static DataSet AsDataSet(this ExcelOpenXmlReader self, DataSetConfig config) //DONE
{
Log("Extracting Sheets to DataSet");
Log(@"config: {{
TableSelect = {0},
DataTableConfigSelect = {1}
}}",
string.Join(".", config.TableSelect.Method.DeclaringType?.Namespace, config.TableSelect.Method.DeclaringType?.Name, config.TableSelect.Method.Name).TrimStart('.'),
string.Join(".", config.DataTableConfigSelect.Method.DeclaringType?.Namespace, config.DataTableConfigSelect.Method.DeclaringType?.Name, config.DataTableConfigSelect.Method.Name).TrimStart('.')
);
var result = new DataSet();
do
{
try
{
config.TableSelect(self, config);
}
catch
{
break;
}
result.Tables.Add(AsDataTable(self, config.DataTableConfigSelect(self, config)));
} while (self.NextResult());
result.AcceptChanges();
Log($"Finished Extracting {result.Tables.Count} Sheet(s) to DataSet");
return(result);
}
static void AddSheetList(byte[] xlsxFile, List <SheetData> list)
{
list.AddRange(ExcelOpenXmlReader.ReadSheets(xlsxFile));
}
static public List <SheetData> Read(byte[] xlsxFile)
{
return(ExcelOpenXmlReader.ReadSheets(xlsxFile));
}
void ReadSheetList(byte[] xlsxFile)
{
mSheets = ExcelOpenXmlReader.ReadSheets(xlsxFile);
}
public static DataTable AsDataTable(this ExcelOpenXmlReader self, DataTableConfig config = null) //DONE
{
Log($"Extracting {self.Name} to DataTable");
Log(@"config: {{
UseHeaderRow = {0},
DetectColumnTypes = {1},
ColumnNameFormat = {2},
HeaderRowSelect = {3},
HeaderSelect = {4},
RowSelect = {5},
ColumnTypeSelect = {6}
}}",
config.UseHeaderRow, config.DetectColumnDataTypes, config.ColumnNameFomat,
string.Join(".", config.HeaderRowSelect.Method.DeclaringType?.Namespace, config.HeaderRowSelect.Method.DeclaringType?.Name, config.HeaderRowSelect.Method.Name).TrimStart('.'),
string.Join(".", config.HeaderSelect.Method.DeclaringType?.Namespace, config.HeaderSelect.Method.DeclaringType?.Name, config.HeaderSelect.Method.Name).TrimStart('.'),
string.Join(".", config.RowSelect.Method.DeclaringType?.Namespace, config.RowSelect.Method.DeclaringType?.Name, config.RowSelect.Method.Name).TrimStart('.'),
string.Join(".", config.ColumnTypeSelect.Method.DeclaringType?.Namespace, config.ColumnTypeSelect.Method.DeclaringType?.Name, config.ColumnTypeSelect.Method.Name).TrimStart('.')
);
if (config == null)
{
Log("Using default DataTableConfig");
config = new DataTableConfig();
}
var table = new DataTable {
TableName = self.Name
};
self.Read(); // Position at first row
Log($"UseHeaderRow = {config.UseHeaderRow}");
if (config.UseHeaderRow)
{
Log("Extracting Headers");
config.HeaderRowSelect(self, config);
foreach (var header in config.HeaderSelect(self, config))
{
table.Columns.Add(header);
}
self.Read(); // skip header row
}
else
{
Log($"Generating {self.FieldCount} header(s)");
for (int i = 0; i < self.FieldCount; i++)
{
table.Columns.Add(string.Format(config.ColumnNameFomat, i));
}
}
table.BeginLoadData();
Log("Begin reading Rows");
do
{
var row = table.NewRow();
try
{
config.RowSelect(self, config, row);
}
catch
{
break;
}
table.Rows.Add(row);
} while (self.Read());
Log($"End reading {table.Rows.Count} Row(s)");
table.EndLoadData();
Log($"DetectColumnTypes = {config.DetectColumnDataTypes}");
if (config.DetectColumnDataTypes)
{
Log("Detecting Column data types");
DataTable new_table = null;
for (int colIdx = 0; colIdx < table.Columns.Count; colIdx++)
{
var column = table.Columns[colIdx];
Log($"Checking data type for Column[{colIdx}]({column.ColumnName})");
var col_type = column.DataType;
var new_type = config.ColumnTypeSelect(column, config) ?? col_type;
if (col_type != new_type)
{
Log($"Found new data type for Column[{colIdx}]({column.ColumnName}) {col_type.Name} => {new_type.Name}");
new_table = new_table ?? table.Clone();
new_table.Columns[colIdx].DataType = new_type;
}
}
Log("Data types changed: {0}", new_table != null);
if (new_table != null)
{
Log($"Correcting data types for Table({table.TableName})");
new_table.BeginLoadData();
foreach (DataRow row in table.Rows)
{
new_table.ImportRow(row);
}
new_table.EndLoadData();
table.Dispose();
table = new_table;
}
}
Log($"Findished Extracting {self.Name} to DataTable({table.TableName})");
return(table);
}
public static DataSet AsDataSetExt(this ExcelOpenXmlReader self, DataSetConfig config = null) => AsDataSet(self, config ?? new DataSetConfig());