Thanks for your reply. Attached are the "template" file (i.e. the one that's filled in with data using XLSIO) and the resulting file after the data is filled in. Here's my C# code for populating the data in case I'm doing something unorthodox:
public string Export(Report report, int? AsLoginID)
{
ILog log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType.FullName);
string path = Path.Combine(ConfigurationManager.AppSettings["SpreadsheetTemplatePath"], report.SourceTemplate);
string connString = report.GetConnectionString();
ExcelEngine excelEngine = new ExcelEngine();
IApplication xl = excelEngine.Excel;
IWorkbook wb = xl.Workbooks.Open(path, ExcelOpenType.Automatic);
var worksheets = Data.GetReportWorksheets(report.ReportID);
foreach (var worksheet in worksheets)
{
var worksheetParams = Data.GetWorksheetParameters(worksheet.WorksheetID);
// always add a @CurrentUserLoginID parameter for reports that need to know who the report was run as
worksheetParams.Add(new WorksheetParameter { ParameterName = "@CurrentUserLoginID", ParameterType = "int", ParameterValue = AsLoginID.HasValue ? AsLoginID.ToString() : string.Empty });
DataTable dt = Data.ReturnDataTable(connString, string.Format("{0} {1}", report.CommonSQL, worksheet.SQL), worksheetParams.ToList());
Syncfusion.XlsIO.IWorksheet ws;
ws = wb.Worksheets[worksheet.WorksheetName];
if (ws == null)
{
ws = wb.Worksheets.Create(worksheet.WorksheetName);
ws.ImportDataTable(dt, true, 1, 1);
}
else
{
var includeHeader = (worksheet.IncludeHeader.HasValue ? worksheet.IncludeHeader.Value : false);
ws.ImportDataTable(dt, includeHeader, includeHeader ? 1 : 2, 1);
}
ws.Rows[0].FreezePanes();
if (worksheet.FormatForPrinting == true)
{
IListObject table = ws.ListObjects.Create(string.Format("Table{0}", wb.Worksheets[worksheet.WorksheetName].Index + 1), ws.UsedRange);
table.BuiltInTableStyle = TableBuiltInStyles.TableStyleLight9;
if (ws.UsedRange.LastColumn < 15 || ws.UsedRange.LastRow < 2500)
ws.UsedRange.AutofitColumns();
}
}
foreach (IWorksheet ws in wb.Worksheets)
{
IPivotTables pTables = ws.PivotTables;
for (int i = 0; i < pTables.Count; i++)
{
ws.PivotTables[i].Options.ShowDrillIndicators = false;
}
}
string savedReport = Path.Combine(ConfigurationManager.AppSettings["SavedSpreadsheetPath"], report.GetFileName());
try
{
wb.Worksheets[0].Activate();
wb.SaveAs(savedReport);
wb.Close();
log.Info(string.Format("Report exported successfully - {0} using {1}", savedReport, report.GetServerAndDatabase()));
}
catch (Exception ex)
{
log.Error(string.Format("Report export failed - {0} using {1}" + Environment.NewLine + "Error:{2}", savedReport, report.GetServerAndDatabase(), ex.Message));
savedReport = string.Empty;
}
finally
{
excelEngine.Dispose();
}
return savedReport;
}
Attachment:
Phantom_Data_fd81cf06.7z
