public void CreateTimeSliceTables(List <string> days, IEnumerable <string> periods, DataType tableType, List <string> selectedColumns) { if (ETableDictionary.Count() != 0) { ClearAll(); } _headerTable = new EDataTable(1, _queryId, false, true); _headerTable.Columns.Add("Date"); _headerTable.Columns[0].DataType = typeof(string); _headerTable.Columns.Add("Day"); _headerTable.Columns[0].DataType = typeof(string); ETableDictionary.Add(_internalTable.TableName + " TimeSlice" , _headerTable); _days = days; ParsePeriods(periods); foreach (var period in _periodStructs) { var edataTable = new EDataTable(_queryId, false, true); if (!_internalTable.AsEnumerable().Any()) { return; } AddTimeSliceTableColumns(edataTable, selectedColumns); foreach (var day in _days) { _sortedList.Clear(); if (!_internalTable.AsEnumerable().Any()) { return; } var currentDayInDT = from rows in _internalTable.AsEnumerable() where rows.Field <DateTime>("BarTime").DayOfWeek.ToString() == day select rows; if (!currentDayInDT.Any()) { continue; } var daysWithStartPeriod = from rowss in currentDayInDT.OrderBy(oo => oo.Field <DateTime>("BarTime").DayOfYear) where rowss.Field <DateTime>("BarTime").TimeOfDay > period.StartPeriod select rowss; if (!daysWithStartPeriod.Any()) { continue; } var daysInPeriod = from rowsss in daysWithStartPeriod where rowsss.Field <DateTime>("BarTime").TimeOfDay < period.EndPeriod select rowsss; if (!daysInPeriod.Any()) { continue; } var sday = daysInPeriod.ToList()[0].Field <DateTime>("BarTime").DayOfYear; _tempList = from rowsss in daysInPeriod where rowsss.Field <DateTime>("BarTime").TimeOfDay < period.EndPeriod select rowsss; DispositionDays(sday); var sortedList = _sortedList; if (!_sortedList.Any()) { continue; } foreach (var varday in sortedList) { var drow = edataTable.NewRow(); AddTimeSliceResults(varday, tableType, @drow); edataTable.Rows.Add(drow); } } var key = period.StartPeriod.ToString() + "-" + period.EndPeriod.ToString(); _eDataTableDictionary.Add(key, edataTable); } NormalizeEDataTables(tableType); }
public void ExportDataToExcel(EDataTableDictionary dsData, ExportStyle style, string profileName) { _eventRow = 1; foreach (var item in dsData) { _progressRowCount += item.Value.Rows.Count; } string appPath = AppDomain.CurrentDomain.BaseDirectory; var path = Path.GetFullPath(appPath + @"\" + "DataExportFiles"); var iExists = Directory.Exists(path); if (!iExists) { Directory.CreateDirectory(path); } var fullPath = Path.GetFullPath(path + @"\" + profileName); var isExists = Directory.Exists(fullPath); if (!isExists) { Directory.CreateDirectory(fullPath); } var finko = new FileStream(fullPath + @"\" + profileName + " " + DateTime.Now.Month + "_" + DateTime.Now.Day + " " + DateTime.Now.Hour + "_" + DateTime.Now.Minute + "_" + DateTime.Now.Second + "_" + DateTime.Now.Millisecond + ".xlsx", FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite); using (var excel = new ExcelPackage(finko)) { var workbooks = excel.Workbook; workbooks.Worksheets.Add("Sheet"); var myDataList = new List <EDataTable>(); var myNameList = new List <string>(); #region SnapShotExport var snapshots = from items in dsData where items.Value.IsSnapShotTable select items; var keyValuePairs = snapshots as List <KeyValuePair <string, EDataTable> > ?? snapshots.ToList(); //export snapsots foreach (var items in keyValuePairs) { myDataList.Clear(); myNameList.Clear(); myDataList.Add(items.Value); myNameList.Add(items.Key); _tableCounter++; var items1 = items; var list = dsData.Where(tbl => tbl.Value.SnapshotRelationID == items1.Value.SnapShotID); foreach (var eDataTable in list) { myDataList.Add(eDataTable.Value); myNameList.Add(eDataTable.Key); _tableCounter++; } ExportCurrentData(excel, myDataList, style); //export the queries FormatExcelSheet(excel, myDataList, myNameList); excel.Workbook.Worksheets.Add("Sheet"); } #endregion #region TimeSliceExport var timeslices = from items in dsData where items.Value.IsTimeSliceTable select items; var timslicevalues = timeslices as List <KeyValuePair <string, EDataTable> > ?? timeslices.ToList(); foreach (var items in timslicevalues) { myDataList.Clear(); myNameList.Clear(); myDataList.Add(items.Value); myNameList.Add(items.Key); _tableCounter++; var items1 = items; var list = dsData.Where(tbl => tbl.Value.TimeSliceRelationID == items1.Value.TimeSliceID); foreach (var eDataTable in list) { myDataList.Add(eDataTable.Value); myNameList.Add(eDataTable.Key); _tableCounter++; } ExportCurrentData(excel, myDataList, style); //export the queries FormatExcelSheet(excel, myDataList, myNameList); if (_tableCounter < dsData.Count()) { excel.Workbook.Worksheets.Add("Sheet"); } } #endregion excel.Save(); excel.Dispose(); } }