public bool Load(DataTable data_source, IWorksheet worksheet) { if (data_source.Rows.Count <= 0) { // No data was found in the data source table. This will be an empty worksheet. Logger.Write("Worksheet.Process", "DISCARDING WORKSHEET: NO DATA ", System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory); return(false); } else { Logger.Write("Worksheet.Process", " LOADING DATA: ROW COUNT " + data_source.Rows.Count, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory); } if (string.IsNullOrEmpty(StartColumn)) { throw new Exception(string.Format("SheetNumber [{0}] SheetTableName [{1}] does not contain a StartDataColumn", SheetNumber, SourceDataTable.Name)); } if (string.IsNullOrEmpty(StartRow)) { throw new Exception(string.Format("SheetNumber [{0}] SheetTableName [{1}] does not contain a StartDataRow", SheetNumber, SourceDataTable.Name)); } // Set the sheets header. if (!string.IsNullOrEmpty(Header)) { worksheet.PageSetup.CenterHeader = TextParser.Parse(Header, DrivingData, SharedData, ModuleCommands); } // Set the sheets footer. if (!string.IsNullOrEmpty(Footer)) { worksheet.PageSetup.CenterFooter = TextParser.Parse(Footer, DrivingData, SharedData, ModuleCommands); } // Check the start row for commands. if (StartRow.ToLower() == "%lastusedrow%") { StartRow = worksheet.UsedRange.RowCount.ToString(); } else if (StartRow.ToLower() == "%firstnewrow%") { StartRow = (worksheet.UsedRange.RowCount + 1).ToString(); } // Check to see if only certain columns are desired. if (SourceDataTable.CacheColumnCollection != null && SourceDataTable.CacheColumnCollection.Count > 0) { object[,] values = null; if (!string.IsNullOrEmpty(SourceDataTable.AggregateExpression)) { values = new object[0, SourceDataTable.CacheColumnCollection.Length]; if (SourceDataTable.Aggregate.Expression.ToLower() == "sum") { for (int i = 0; i < SourceDataTable.CacheColumnCollection.Length; i++) { values[0, i] = 0; } for (int row = 0; row < data_source.Rows.Count; row++) { for (int col = 0; col < SourceDataTable.CacheColumnCollection.Count; col++) { values[0, col] = Convert.ToInt32(values[0, col]) + Convert.ToInt32(data_source.Rows[row][SourceDataTable.CacheColumnCollection.GetColumn(col).Name]); } } } else { throw new Exception("DataTableColumnAggregate [" + SourceDataTable.Aggregate.Expression + "] is unknown."); } } else if (SourceDataTable.CacheColumnCollection != null && SourceDataTable.CacheColumnCollection.Count > 0) { values = new object[data_source.Rows.Count, SourceDataTable.CacheColumnCollection.Count]; for (int row = 0; row < data_source.Rows.Count; row++) { for (int col = 0; col < SourceDataTable.CacheColumnCollection.Count; col++) { values[row, col] = data_source.Rows[row][SourceDataTable.CacheColumnCollection.GetColumn(col).Name]; } } } else { values = new object[data_source.Rows.Count - 1, SharedData.Data.Tables(TextParser.Parse(SourceDataTable.Name, DrivingData, SharedData, ModuleCommands)).Columns.Count - 1]; for (int row = 0; row < data_source.Rows.Count; row++) { for (int col = 0; col < SharedData.Data.Tables(TextParser.Parse(SourceDataTable.Name, DrivingData, SharedData, ModuleCommands)).Columns.Count; col++) { values[row, col] = data_source.Rows[row][col]; } } } int column_index = worksheet.Cells[StartColumn + StartRow].Column; if (values.GetUpperBound(0) == 0 && values.GetUpperBound(1) == 0) { worksheet.Cells[Convert.ToInt32(StartRow) - 1, column_index, (Convert.ToInt32(StartRow) - 1) + values.GetUpperBound(0), column_index + values.GetUpperBound(1)].Value = values[0, 0]; } else { worksheet.Cells[Convert.ToInt32(StartRow) - 1, column_index, (Convert.ToInt32(StartRow) - 1) + values.GetUpperBound(0), column_index + values.GetUpperBound(1)].Value = values; } } else { // Set the worksheet to wrap to a new sheet if there is too much data for one sheet. worksheet.Cells[StartColumn + StartRow].CopyFromDataTable(data_source, SpreadsheetGear.Data.SetDataFlags.WrapToNewWorksheet | SpreadsheetGear.Data.SetDataFlags.NoColumnHeaders); } return(true); }
public bool Process(IWorksheet worksheet) { bool contains_data = false; int start_row = -1; try { Logger.Aquire(); Logger.Write("Worksheet.Process", "", System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory); Logger.Write("Worksheet.Process", "PROCESSING WORKSHEET: " + worksheet.Name, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory); Logger.Write("Worksheet.Process", " SOURCE MODULE: " + SourceDataTable.Name, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory); Logger.Write("Worksheet.Process", " FILTER: " + SourceDataTable.FilterExpression, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory); if (StartRow.ToLower() == "%lastusedrow%") { start_row = worksheet.UsedRange.RowCount; } else if (StartRow.ToLower() == "%firstnewrow%") { start_row = worksheet.UsedRange.Row + 1; } else { start_row = Convert.ToInt32(StartRow); } Logger.Write("Worksheet.Process", " START COLUMN|ROW: " + StartColumn + start_row, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory); } catch (Exception ex) { throw ex; } finally { Logger.Release(); } // Rename the worksheet if applicable. if (!string.IsNullOrEmpty(NewSheetName)) { Logger.WriteLine("Worksheet.Process", " CHANGING NAME: " + TextParser.Parse(NewSheetName, DrivingData, SharedData, ModuleCommands), System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory); worksheet.Name = TextParser.Parse(NewSheetName, DrivingData, SharedData, ModuleCommands); if (worksheet.Name.Length > 31) { throw new Exception("The worksheet name (" + worksheet.Name + ") cannot have more than 31 characters."); } } // If the worksheet does not have a source table defined then return false. // Without a source table the worksheet will be empty. if (HasSourceTable) { if (!SharedData.Data.Contains(SourceDataTable.Name)) { throw new Exception(string.Format("The source data table '{0}' for sheet at index '{1}' was not found in the global cache set.", SourceDataTable.Name, SheetNumber)); } // Load the worksheet with the source table's data. if (!Load(SourceDataTable.Process(ConditionalTables), worksheet)) { // No data wasloaded into the worksheet. contains_data = false; } else { // Perform any required formatting. Format(worksheet); contains_data = true; } } else { contains_data = false; } return(contains_data); }