private static void AddGradient(SpreadsheetDocument spreadSheet, string dummyColorCode, string firstColorCode, string secondColorCode)
{
var wbPart = spreadSheet.GetPartsOfType <WorkbookPart>().FirstOrDefault();
var wbStylePart = wbPart?.GetPartsOfType <WorkbookStylesPart>().FirstOrDefault();
var stylesheet = wbStylePart?.Stylesheet;
var oldFill =
stylesheet?.Fills.FirstOrDefault(f => f.OuterXml.Contains(dummyColorCode)); // find the fill that uses your unique color
if (oldFill == null)
{
return;
}
var gradientFill = new GradientFill {
Degree = 0
};
gradientFill.Append(new GradientStop {
Position = 0D, Color = new Color {
Rgb = firstColorCode
}
});
gradientFill.Append(new GradientStop {
Position = 1D, Color = new Color {
Rgb = secondColorCode
}
});
oldFill.ReplaceChild(gradientFill, oldFill.FirstChild); // inside the fill replace the patternFill with your gradientFill
}
public IEnumerable <ISheet> ReadSheets()
{
return(workbook
.GetPartsOfType <WorkbookPart>()
.SelectMany(
(workbookPart) => workbookPart.Workbook
.Descendants <Sheet>()
.Select(
(worksheet) =>
{
var wsPart = (WorksheetPart)(workbookPart.GetPartById(worksheet.Id));
var worksheetData = wsPart.Worksheet;
return new OpenXmlSheet(workbook, workbookPart, worksheet, worksheetData);
}))
.Select(openXmlSheet => (ISheet)openXmlSheet));
}
/// <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);
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document)
{
var context = new SaveContext();
var workbookPart = document.WorkbookPart ?? document.AddWorkbookPart();
var worksheets = WorksheetsInternal;
var partsToRemove = workbookPart.Parts.Where(s => worksheets.Deleted.Contains(s.RelationshipId)).ToList();
partsToRemove.ForEach(s => workbookPart.DeletePart(s.OpenXmlPart));
context.RelIdGenerator.AddValues(workbookPart.Parts.Select(p => p.RelationshipId).ToList(), RelType.Workbook);
var extendedFilePropertiesPart = document.ExtendedFilePropertiesPart ??
document.AddNewPart<ExtendedFilePropertiesPart>(
context.RelIdGenerator.GetNext(RelType.Workbook));
GenerateExtendedFilePropertiesPartContent(extendedFilePropertiesPart);
GenerateWorkbookPartContent(workbookPart, context);
var sharedStringTablePart = workbookPart.SharedStringTablePart ??
workbookPart.AddNewPart<SharedStringTablePart>(
context.RelIdGenerator.GetNext(RelType.Workbook));
GenerateSharedStringTablePartContent(sharedStringTablePart, context);
var workbookStylesPart = workbookPart.WorkbookStylesPart ??
workbookPart.AddNewPart<WorkbookStylesPart>(
context.RelIdGenerator.GetNext(RelType.Workbook));
GenerateWorkbookStylesPartContent(workbookStylesPart, context);
foreach (var worksheet in WorksheetsInternal.Cast<XLWorksheet>().OrderBy(w => w.Position))
{
//context.RelIdGenerator.Reset(RelType.);
WorksheetPart worksheetPart;
var wsRelId = worksheet.RelId;
if (workbookPart.Parts.Any(p => p.RelationshipId == wsRelId))
{
worksheetPart = (WorksheetPart)workbookPart.GetPartById(wsRelId);
var wsPartsToRemove = worksheetPart.TableDefinitionParts.ToList();
wsPartsToRemove.ForEach(tdp => worksheetPart.DeletePart(tdp));
}
else
worksheetPart = workbookPart.AddNewPart<WorksheetPart>(wsRelId);
context.RelIdGenerator.AddValues(worksheetPart.HyperlinkRelationships.Select(hr => hr.Id).ToList(),
RelType.Workbook);
context.RelIdGenerator.AddValues(worksheetPart.Parts.Select(p => p.RelationshipId).ToList(),
RelType.Workbook);
if (worksheetPart.DrawingsPart != null)
context.RelIdGenerator.AddValues(
worksheetPart.DrawingsPart.Parts.Select(p => p.RelationshipId).ToList(), RelType.Workbook);
// delete comment related parts (todo: review)
DeleteComments(worksheetPart, worksheet, context);
if (worksheet.Internals.CellsCollection.GetCells(c => c.HasComment).Any())
{
var worksheetCommentsPart =
worksheetPart.AddNewPart<WorksheetCommentsPart>(context.RelIdGenerator.GetNext(RelType.Workbook));
GenerateWorksheetCommentsPartContent(worksheetCommentsPart, worksheet);
//VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>(worksheet.LegacyDrawingId);
var vmlDrawingPart = worksheetPart.VmlDrawingParts.FirstOrDefault();
if (vmlDrawingPart == null)
{
if (XLHelper.IsNullOrWhiteSpace(worksheet.LegacyDrawingId))
{
worksheet.LegacyDrawingId = context.RelIdGenerator.GetNext(RelType.Workbook);
worksheet.LegacyDrawingIsNew = true;
}
vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>(worksheet.LegacyDrawingId);
}
GenerateVmlDrawingPartContent(vmlDrawingPart, worksheet, context);
}
GenerateWorksheetPartContent(worksheetPart, worksheet, context);
if (worksheet.PivotTables.Any())
{
GeneratePivotTables(workbookPart, worksheetPart, worksheet, context);
}
//DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>("rId1");
//GenerateDrawingsPartContent(drawingsPart, worksheet);
//foreach (var chart in worksheet.Charts)
//{
// ChartPart chartPart = drawingsPart.AddNewPart<ChartPart>("rId1");
// GenerateChartPartContent(chartPart, (XLChart)chart);
//}
}
GenerateCalculationChainPartContent(workbookPart, context);
if (workbookPart.ThemePart == null)
{
var themePart = workbookPart.AddNewPart<ThemePart>(context.RelIdGenerator.GetNext(RelType.Workbook));
GenerateThemePartContent(themePart);
}
if (CustomProperties.Any())
{
document.GetPartsOfType<CustomFilePropertiesPart>().ToList().ForEach(p => document.DeletePart(p));
var customFilePropertiesPart =
document.AddNewPart<CustomFilePropertiesPart>(context.RelIdGenerator.GetNext(RelType.Workbook));
GenerateCustomFilePropertiesPartContent(customFilePropertiesPart);
}
SetPackageProperties(document);
}
/// <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);
}
public static void AddDataSpreadsheetWorkBook(string filepath, string col, uint rowNum, string text, CellValues type = CellValues.Number)
{
OpenSettings openSettings = new OpenSettings();
openSettings.MarkupCompatibilityProcessSettings =
new MarkupCompatibilityProcessSettings(
MarkupCompatibilityProcessMode.ProcessAllParts,
FileFormatVersions.Office2013
);
SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filepath, true, openSettings);
WorkbookPart workbookPart = spreadsheet.GetPartsOfType <WorkbookPart>().First();
//workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.GetPartsOfType <WorksheetPart>().First();
//worksheetPart.Worksheet = new Worksheet(new SheetData());
//Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
//Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
//sheets.Append(sheet);
// Adding data:
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
// Add a row:
Row row;
//row = new Row() { RowIndex = 1 };
//sheetData.Append(row);
try
{
row = (Row)sheetData.ElementAt((int)rowNum);
}
catch (ArgumentOutOfRangeException ex)
{
row = new Row()
{
RowIndex = new UInt32Value((uint)rowNum)
};
sheetData.Append(row);
}
//Row Row2nd = (Row)sheetData.ElementAt(100);
// Find the A1 cell in the first Cell
Cell refCell = null;
string cellLocation = col + rowNum.ToString();
foreach (Cell cell in row.Elements <Cell>())
{
if (string.Compare(cell.CellReference.Value, cellLocation, true) > 0)
{
refCell = cell;
break;
}
}
Cell newCell = new Cell()
{
CellReference = cellLocation
};
row.InsertBefore(newCell, refCell);
//row.InsertBefore()
// Set the cell value:
newCell.CellValue = new CellValue(text);
//Can be Shared String here:
newCell.DataType = new EnumValue <CellValues>(type);
workbookPart.Workbook.Save();
spreadsheet.Close();
}
