public ExcelDocument(Stream stream, IExcelValueConverter valueConverter)
{
if (stream == null)
{
throw new ArgumentNullException(nameof(stream));
}
_valueConverter = valueConverter ?? throw new ArgumentNullException(nameof(valueConverter));
_document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
_document.AddPart(_document.AddWorkbookPart());
_document.WorkbookPart.Workbook = new Workbook {
Sheets = new Sheets()
};
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="datasetId"></param>
/// <param name="datasetVersionOrderNr"></param>
/// <param name="dataStructureId"></param>
/// <param name="title"></param>
/// <param name="extention"></param>
/// <returns></returns>
public string CreateFile(long datasetId, long datasetVersionOrderNr, long dataStructureId, string title, string extention)
{
string dataPath = GetFullStorePath(datasetId, datasetVersionOrderNr, title, extention);
//Template will not be filtered by columns
if (this.VisibleColumns == null)
{
#region generate file with full datastructure
string dataStructureFilePath = GetDataStructureTemplatePath(dataStructureId, extention);
//dataPath = GetStorePath(datasetId, datasetVersionOrderNr, title, extention);
try
{
SpreadsheetDocument dataStructureFile = SpreadsheetDocument.Open(dataStructureFilePath, true);
SpreadsheetDocument dataFile = SpreadsheetDocument.Create(dataPath,
dataStructureFile.DocumentType);
foreach (OpenXmlPart part in dataStructureFile.GetPartsOfType <OpenXmlPart>())
{
OpenXmlPart newPart = dataFile.AddPart <OpenXmlPart>(part);
}
dataFile.WorkbookPart.Workbook.Save();
dataStructureFile.Dispose();
dataFile.Dispose();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
#endregion
}
// create a file with a subset of variables
if (this.VisibleColumns != null)
{
/// call templateprovider from rpm
ExcelTemplateProvider provider = new ExcelTemplateProvider();
string path = GetStorePath(datasetId, datasetVersionOrderNr);
string newTitle = GetNewTitle(datasetId, datasetVersionOrderNr, title, extention);
provider.CreateTemplate(getVariableIds(this.VisibleColumns), dataStructureId, path, newTitle);
}
return(dataPath);
}
/// <summary>
///
/// </summary>
/// <param name="ns"></param>
/// <param name="table"></param>
/// <param name="title"></param>
/// <param name="extention"></param>
/// <returns></returns>
public string CreateFile(string ns, long dataStructureId, string title, string extension)
{
string dataPath = GetFullStorePath(ns, title, extension);
//Template will not be filtered by columns
if (this.VisibleColumns == null)
{
#region generate file with full datastructure
string dataStructureFilePath = GetDataStructureTemplatePath(dataStructureId, extension);
try
{
SpreadsheetDocument dataStructureFile = SpreadsheetDocument.Open(dataStructureFilePath, true);
SpreadsheetDocument dataFile = SpreadsheetDocument.Create(dataPath,
dataStructureFile.DocumentType);
foreach (OpenXmlPart part in dataStructureFile.GetPartsOfType <OpenXmlPart>())
{
OpenXmlPart newPart = dataFile.AddPart <OpenXmlPart>(part);
}
dataFile.WorkbookPart.Workbook.Save();
dataStructureFile.Dispose();
dataFile.Dispose();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
#endregion
}
return(dataPath);
}
/// <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)
{
}
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="variables"></param>
/// <param name="path"></param>
/// <param name="filename"></param>
/// <returns></returns>
public SpreadsheetDocument CreateTemplate(List <Variable> variables, string path, string filename)
{
if (!Directory.Exists(Path.Combine(AppConfiguration.DataPath, path)))
{
Directory.CreateDirectory(Path.Combine(AppConfiguration.DataPath, path));
}
SpreadsheetDocument template = SpreadsheetDocument.Open(Path.Combine(AppConfiguration.GetModuleWorkspacePath("RPM"), "Template", _fileName), true);
SpreadsheetDocument dataStructureFile = SpreadsheetDocument.Create(Path.Combine(AppConfiguration.DataPath, path, filename), template.DocumentType);
//dataStructureFile = SpreadsheetDocument.Open(Path.Combine(AppConfiguration.GetModuleWorkspacePath("RPM"), "Template", filename), true);
foreach (OpenXmlPart part in template.GetPartsOfType <OpenXmlPart>())
{
OpenXmlPart newPart = dataStructureFile.AddPart <OpenXmlPart>(part);
}
template.Close();
// get worksheet
List <StyleIndexStruct> styleIndex = new List <StyleIndexStruct>();
CellFormats cellFormats = dataStructureFile.WorkbookPart.WorkbookStylesPart.Stylesheet.Elements <CellFormats>().First();
//number 0,00
CellFormat cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)2U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
};
cellFormat.Protection = new Protection();
cellFormat.Protection.Locked = false;
cellFormats.Append(cellFormat);
styleIndex.Add(new StyleIndexStruct()
{
Name = "Decimal", Index = (uint)cellFormats.Count++, DisplayPattern = null
});
//number 0
cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)1U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
};
cellFormat.Protection = new Protection();
cellFormat.Protection.Locked = false;
cellFormats.Append(cellFormat);
styleIndex.Add(new StyleIndexStruct()
{
Name = "Number", Index = (uint)cellFormats.Count++, DisplayPattern = null
});
//text
cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)49U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
};
cellFormat.Protection = new Protection();
cellFormat.Protection.Locked = false;
cellFormats.Append(cellFormat);
styleIndex.Add(new StyleIndexStruct()
{
Name = "Text", Index = (uint)cellFormats.Count++, DisplayPattern = null
});
//DateTime
cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)22U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
};
cellFormat.Protection = new Protection();
cellFormat.Protection.Locked = false;
cellFormats.Append(cellFormat);
styleIndex.Add(new StyleIndexStruct()
{
Name = "DateTime", Index = (uint)cellFormats.Count++, DisplayPattern = DataTypeDisplayPattern.Pattern.Where(p => p.Systemtype.Equals(DataTypeCode.DateTime) && p.Name.Equals("DateTime")).FirstOrDefault()
});
//Date
cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
};
cellFormat.Protection = new Protection();
cellFormat.Protection.Locked = false;
cellFormats.Append(cellFormat);
styleIndex.Add(new StyleIndexStruct()
{
Name = "Date", Index = (uint)cellFormats.Count++, DisplayPattern = DataTypeDisplayPattern.Pattern.Where(p => p.Systemtype.Equals(DataTypeCode.DateTime) && p.Name.Equals("Date")).FirstOrDefault()
});
//Time
cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)21U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
};
cellFormat.Protection = new Protection();
cellFormat.Protection.Locked = false;
cellFormats.Append(cellFormat);
styleIndex.Add(new StyleIndexStruct()
{
Name = "Time", Index = (uint)cellFormats.Count++, DisplayPattern = DataTypeDisplayPattern.Pattern.Where(p => p.Systemtype.Equals(DataTypeCode.DateTime) && p.Name.Equals("Time")).FirstOrDefault()
});
Worksheet worksheet = dataStructureFile.WorkbookPart.WorksheetParts.First().Worksheet;
List <Row> rows = GetRows(worksheet, 1, 11);
foreach (Variable var in variables)
{
DataContainerManager CM = new DataContainerManager();
DataAttribute dataAttribute = CM.DataAttributeRepo.Get(var.DataAttribute.Id);
int indexVar = variables.ToList().IndexOf(var) + 1;
string columnIndex = GetClomunIndex(indexVar);
string cellRef = columnIndex + 1;
Cell cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(var.Label)
};
rows.ElementAt(0).AppendChild(cell);
cellRef = columnIndex + 2;
cell = new Cell()
{
CellReference = cellRef,
DataType = CellValues.String,
StyleIndex = getExcelStyleIndex(dataAttribute.DataType, styleIndex),
CellValue = new CellValue("")
};
rows.ElementAt(1).AppendChild(cell);
cellRef = columnIndex + 3;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(var.Id.ToString())
};
rows.ElementAt(2).AppendChild(cell);
cellRef = columnIndex + 4;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(dataAttribute.ShortName)
};
rows.ElementAt(3).AppendChild(cell);
// description from variable
// if not then from attribute
string description = "";
description = String.IsNullOrEmpty(var.Description) ? dataAttribute.Description : var.Description;
cellRef = columnIndex + 5;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(description)
};
rows.ElementAt(4).AppendChild(cell);
string classification = "";
if (dataAttribute.Classification != null)
{
classification = dataAttribute.Classification.Name;
}
cellRef = columnIndex + 6;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(classification)
};
rows.ElementAt(5).AppendChild(cell);
string unit = "";
if (var.Unit != null)
{
unit = var.Unit.Name;
}
cellRef = columnIndex + 7;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(unit)
};
rows.ElementAt(6).AppendChild(cell);
string dataType = "";
if (dataAttribute.DataType != null)
{
dataType = dataAttribute.DataType.Name;
}
cellRef = columnIndex + 8;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(dataType)
};
rows.ElementAt(7).AppendChild(cell);
cellRef = columnIndex + 9;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(var.IsValueOptional.ToString())
};
rows.ElementAt(8).AppendChild(cell);
cellRef = columnIndex + 10;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(dataAttribute.IsMultiValue.ToString())
};
rows.ElementAt(9).AppendChild(cell);
}
foreach (DefinedName name in dataStructureFile.WorkbookPart.Workbook.GetFirstChild <DefinedNames>())
{
if (name.Name == "Data" || name.Name == "VariableIdentifiers")
{
string[] tempArr = name.InnerText.Split('$');
string temp = "";
tempArr[tempArr.Count() - 2] = GetClomunIndex(variables.Count());
foreach (string t in tempArr)
{
if (t == tempArr.First())
{
temp = temp + t;
}
else
{
temp = temp + "$" + t;
}
}
name.Text = temp;
}
}
//WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
//WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
dataStructureFile.WorkbookPart.Workbook.Save();
dataStructureFile.Close();
return(dataStructureFile);
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="variables"></param>
/// <param name="path"></param>
/// <param name="filename"></param>
/// <returns></returns>
public SpreadsheetDocument CreateTemplate(List <long> variableIds, string path, string filename)
{
if (!Directory.Exists(Path.Combine(AppConfiguration.DataPath, path)))
{
Directory.CreateDirectory(Path.Combine(AppConfiguration.DataPath, path));
}
SpreadsheetDocument template = SpreadsheetDocument.Open(Path.Combine(AppConfiguration.GetModuleWorkspacePath("RPM"), "Template", _fileName), true);
SpreadsheetDocument dataStructureFile = SpreadsheetDocument.Create(Path.Combine(AppConfiguration.DataPath, path, filename), template.DocumentType);
//dataStructureFile = SpreadsheetDocument.Open(Path.Combine(AppConfiguration.GetModuleWorkspacePath("RPM"), "Template", filename), true);
foreach (OpenXmlPart part in template.GetPartsOfType <OpenXmlPart>())
{
OpenXmlPart newPart = dataStructureFile.AddPart <OpenXmlPart>(part);
}
template.Close();
//uint iExcelIndex = 164;
List <StyleIndexStruct> styleIndex = new List <StyleIndexStruct>();
ExcelHelper.UpdateStylesheet(dataStructureFile.WorkbookPart.WorkbookStylesPart.Stylesheet, out styleIndex);
Worksheet worksheet = dataStructureFile.WorkbookPart.WorksheetParts.First().Worksheet;
List <Row> rows = GetRows(worksheet, 1, 11);
List <Variable> variables = this.GetUnitOfWork().GetReadOnlyRepository <Variable>()
.Query(p => variableIds.Contains(p.Id))
.OrderBy(p => p.OrderNo)
.ToList();
foreach (Variable var in variables)
{
DataContainerManager CM = null;
try
{
CM = new DataContainerManager();
DataAttribute dataAttribute = CM.DataAttributeRepo.Get(var.DataAttribute.Id);
int indexVar = variables.ToList().IndexOf(var) + 1;
string columnIndex = GetClomunIndex(indexVar);
string cellRef = columnIndex + 1;
Cell cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(var.Label)
};
rows.ElementAt(0).AppendChild(cell);
cellRef = columnIndex + 2;
cell = new Cell()
{
CellReference = cellRef,
DataType = CellValues.String,
StyleIndex = ExcelHelper.GetExcelStyleIndex(dataAttribute.DataType, styleIndex),
CellValue = new CellValue("")
};
rows.ElementAt(1).AppendChild(cell);
cellRef = columnIndex + 3;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(var.Id.ToString())
};
rows.ElementAt(2).AppendChild(cell);
cellRef = columnIndex + 4;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(dataAttribute.ShortName)
};
rows.ElementAt(3).AppendChild(cell);
// description from variable
// if not then from attribute
string description = "";
description = String.IsNullOrEmpty(var.Description) ? dataAttribute.Description : var.Description;
cellRef = columnIndex + 5;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(description)
};
rows.ElementAt(4).AppendChild(cell);
string classification = "";
if (dataAttribute.Classification != null)
{
classification = dataAttribute.Classification.Name;
}
cellRef = columnIndex + 6;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(classification)
};
rows.ElementAt(5).AppendChild(cell);
string unit = "";
if (var.Unit != null)
{
unit = var.Unit.Name;
}
cellRef = columnIndex + 7;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(unit)
};
rows.ElementAt(6).AppendChild(cell);
string dataType = "";
if (dataAttribute.DataType != null)
{
dataType = dataAttribute.DataType.Name;
}
cellRef = columnIndex + 8;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(dataType)
};
rows.ElementAt(7).AppendChild(cell);
cellRef = columnIndex + 9;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(var.IsValueOptional.ToString())
};
rows.ElementAt(8).AppendChild(cell);
cellRef = columnIndex + 10;
cell = new Cell()
{
CellReference = cellRef,
StyleIndex = (UInt32Value)4U,
DataType = CellValues.String,
CellValue = new CellValue(dataAttribute.IsMultiValue.ToString())
};
rows.ElementAt(9).AppendChild(cell);
}
finally
{
CM.Dispose();
}
}
foreach (DefinedName name in dataStructureFile.WorkbookPart.Workbook.GetFirstChild <DefinedNames>())
{
if (name.Name == "Data" || name.Name == "VariableIdentifiers")
{
string[] tempArr = name.InnerText.Split('$');
string temp = "";
tempArr[tempArr.Count() - 2] = GetClomunIndex(variables.Count());
foreach (string t in tempArr)
{
if (t == tempArr.First())
{
temp = temp + t;
}
else
{
temp = temp + "$" + t;
}
}
name.Text = temp;
}
}
//WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
//WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
dataStructureFile.WorkbookPart.Workbook.Save();
dataStructureFile.Close();
return(dataStructureFile);
}
