public ActionResult <List <KPIDataSource> > ProcessInput([FromForm] KPIInput input) { using (var stream = input.KPIInputExcel.OpenReadStream()) using (var reader = ExcelReaderFactory.CreateReader(stream)) { var result = reader.AsDataSet(); List <KPIDataSource> kpiDataSources = new List <KPIDataSource>(); foreach (DataTable table in result.Tables) { string dsName = ""; List <int> enumToLookFor = new List <int>(); KPIDataSource kpiDataSource = new KPIDataSource(); try { dsName = table.Rows[0].ItemArray[0].ToString(); if (dsName.EndsWith("Table", StringComparison.InvariantCultureIgnoreCase)) { dsName = dsName.Substring(0, dsName.LastIndexOf("Table", StringComparison.InvariantCultureIgnoreCase)).Trim(); //colNum = int.Parse(table.Rows[0].ItemArray[1].ToString()); kpiDataSource.Name = dsName; // process headers for (int col = 0; col < table.Rows[1].ItemArray.Length; col++) { string label = table.Rows[1].ItemArray[col].ToString(); if (string.IsNullOrEmpty(label)) { continue; } ColumnDetails colDetails = new ColumnDetails(); kpiDataSource.Columns.Add(colDetails); if (label.EndsWith("List")) { colDetails.IsEnumeration = true; colDetails.Label = label.Substring(0, label.LastIndexOf("List", StringComparison.InvariantCultureIgnoreCase)).Trim(); enumToLookFor.Add(col); } else { colDetails.Label = label; } } kpiDataSources.Add(kpiDataSource); if (table.Rows.Count < 3) { continue; } // process data for (int dataRowIndex = 2; dataRowIndex < table.Rows.Count; dataRowIndex++) { for (int dataCol = 0; dataCol < table.Rows[dataRowIndex].ItemArray.Length; dataCol++) { if (enumToLookFor.IndexOf(dataCol) > -1) { kpiDataSource.Columns[dataCol].Enumeration.Add( table.Rows[dataRowIndex].ItemArray[dataCol].ToString()); } } } foreach (var enumCol in enumToLookFor) { kpiDataSource.Columns[enumCol].Enumeration = kpiDataSource.Columns[enumCol].Enumeration.Distinct().ToList(); } } } catch { continue; } } return(kpiDataSources); } }
private static void Main(string[] args) { Encoding.RegisterProvider(CodePagesEncodingProvider.Instance); Console.WriteLine("Hello World!"); string excelLocation = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Assets", "TestData.xlsx"); using (var stream = File.Open(excelLocation, FileMode.Open, FileAccess.Read)) // Auto-detect format, supports: // - Binary Excel files (2.0-2003 format; *.xls) // - OpenXml Excel files (2007 format; *.xlsx) using (var reader = ExcelReaderFactory.CreateReader(stream)) { // Choose one of either 1 or 2: var result = reader.AsDataSet(); List <KPIDataSource> kpiDataSources = new List <KPIDataSource>(); foreach (DataTable table in result.Tables) { string dsName = ""; List <int> enumToLookFor = new List <int>(); KPIDataSource kpiDataSource = new KPIDataSource(); try { dsName = table.Rows[0].ItemArray[0].ToString(); if (dsName.EndsWith("Table", StringComparison.InvariantCultureIgnoreCase)) { dsName = dsName.Substring(0, dsName.LastIndexOf("Table", StringComparison.InvariantCultureIgnoreCase)).Trim(); //colNum = int.Parse(table.Rows[0].ItemArray[1].ToString()); kpiDataSource.Name = dsName; // process headers for (int col = 0; col < table.Rows[1].ItemArray.Length; col++) { string label = table.Rows[1].ItemArray[col].ToString(); if (string.IsNullOrEmpty(label)) { continue; } ColumnDetails colDetails = new ColumnDetails(); kpiDataSource.Columns.Add(colDetails); if (label.EndsWith("List")) { colDetails.IsEnumeration = true; colDetails.Label = label.Substring(0, label.LastIndexOf("List", StringComparison.InvariantCultureIgnoreCase)).Trim(); enumToLookFor.Add(col); } else { colDetails.Label = label; } } kpiDataSources.Add(kpiDataSource); if (table.Rows.Count < 3) { continue; } // process data for (int dataRowIndex = 2; dataRowIndex < table.Rows.Count; dataRowIndex++) { for (int dataCol = 0; dataCol < table.Rows[dataRowIndex].ItemArray.Length; dataCol++) { if (enumToLookFor.IndexOf(dataCol) > -1) { kpiDataSource.Columns[dataCol].Enumeration.Add( table.Rows[dataRowIndex].ItemArray[dataCol].ToString()); } } } } } catch { continue; } } } }