Beispiel #1
0
        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);
        }
Beispiel #2
0
 static void AddSheetList(byte[] xlsxFile, List <SheetData> list)
 {
     list.AddRange(ExcelOpenXmlReader.ReadSheets(xlsxFile));
 }
Beispiel #3
0
 static public List <SheetData> Read(byte[] xlsxFile)
 {
     return(ExcelOpenXmlReader.ReadSheets(xlsxFile));
 }
Beispiel #4
0
 void ReadSheetList(byte[] xlsxFile)
 {
     mSheets = ExcelOpenXmlReader.ReadSheets(xlsxFile);
 }
Beispiel #5
0
        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);
        }
Beispiel #6
0
 public static DataSet AsDataSetExt(this ExcelOpenXmlReader self, DataSetConfig config = null) => AsDataSet(self, config ?? new DataSetConfig());