/// <summary>
/// Creates the excel report.
/// </summary>
/// <param name="templatePath">The report template file path.</param>
/// <param name="destinationPath">The destination path of the generated report.</param>
/// <param name="sessionIds">The session ids.</param>
public static void GenerateReport(string templatePath, string destinationPath, IList <string> sessionIds)
{
// Build the list of selected session ID's into a comma delimited string.
for (int i = 0; i < sessionIds.Count; i++)
{
sessionIds[i] = "'" + sessionIds[i] + "'";
}
string sessionIdList = string.Join(",", sessionIds);
// Create a temporary staging Excel file.
string appDir = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
string stagedFilename = Path.Combine(appDir, Path.GetFileName(destinationPath));
using (SpreadsheetDocument outputWorkbook = SpreadsheetDocument.Create(stagedFilename, SpreadsheetDocumentType.Workbook, true))
{
// Make sure the new workbook is clear.
outputWorkbook.DeleteParts(outputWorkbook.GetPartsOfType <OpenXmlPart>());
// Copy all the parts from the report template to the output workbook.
using (SpreadsheetDocument templateWorkbook = SpreadsheetDocument.Open(templatePath, false))
{
foreach (OpenXmlPart part in templateWorkbook.GetPartsOfType <OpenXmlPart>())
{
outputWorkbook.AddPart(part);
}
}
// Process each of the connections in the output workbook.
if (outputWorkbook?.WorkbookPart?.ConnectionsPart?.Connections != null)
{
foreach (Connection connection in outputWorkbook.WorkbookPart.ConnectionsPart.Connections.Elements <Connection>())
{
// If this connection points to a database link then perform some additional processing.
if (connection?.DatabaseProperties != null && connection.DatabaseProperties.Connection.Value.StartsWith("Provider="))
{
// Get the database connection string.
OleDbConnectionStringBuilder oleDbConnString = new OleDbConnectionStringBuilder(connection.DatabaseProperties.Connection.Value);
// Change the user name and password.
oleDbConnString.Add("User ID", "report_viewer");
oleDbConnString.Add("Password", "report_viewer");
// Change the data source and the initial catalog (database).
oleDbConnString.Add("Data Source", GlobalSettings.Items[Setting.ReportingDatabaseServer]);
oleDbConnString.Add("Initial Catalog", GlobalSettings.Items[Setting.ReportingDatabase]);
string appName = GlobalSettings.IsDistributedSystem ? "STF" : "STB";
oleDbConnString.Add("Application Name", appName);
// Put the modified connection string back into the output workbook.
connection.DatabaseProperties.Connection.Value = oleDbConnString.ToString();
// Modify the SQL command to include the list of session IDs.
string sqlCommand = connection.DatabaseProperties.Command.Value;
connection.DatabaseProperties.Command.Value = sqlCommand.Replace("'{SessionId}'", sessionIdList);
}
if (connection?.DatabaseProperties != null &&
connection.DatabaseProperties.Connection.Value.StartsWith("DRIVER="))
{
DbConnectionStringBuilder dbConnectionString = new DbConnectionStringBuilder(true);
dbConnectionString.ConnectionString = connection.DatabaseProperties.Connection.Value;
dbConnectionString["SERVER"] = GlobalSettings.Items[Setting.ReportingDatabaseServer];
dbConnectionString["UID"] = "report_viewer";
dbConnectionString["PWD"] = "report_viewer";
connection.DatabaseProperties.Connection.Value = dbConnectionString.ConnectionString;
string sqlCommand = connection.DatabaseProperties.Command.Value;
connection.DatabaseProperties.Command.Value = sqlCommand.Replace("'{SessionId}'", sessionIdList);
}
}
}
// Modify a few of the workbook package properties.
outputWorkbook.PackageProperties.Created = DateTime.Now;
outputWorkbook.PackageProperties.Modified = DateTime.Now;
outputWorkbook.PackageProperties.Creator = string.Empty;
outputWorkbook.PackageProperties.LastModifiedBy = string.Empty;
if (outputWorkbook?.WorkbookPart?.Workbook?.AbsolutePath != null)
{
outputWorkbook.WorkbookPart.Workbook.AbsolutePath = null;
}
// Save the output workbook.
outputWorkbook.WorkbookPart.Workbook.Save();
}
// Save the staged Excel file.
File.Copy(stagedFilename, destinationPath, true);
// Try to delete the temporary staged Excel file.
try
{
File.Delete(stagedFilename);
}
catch (Exception)
{
}
}