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);
}
