/// <summary> /// 1 0 /// 2 0.00 /// 3 #,##0 /// 4 #,##0.00 /// 5 $#,##0_);($#,##0) /// 6 $#,##0_);[Red]($#,##0) /// 7 $#,##0.00_);($#,##0.00) /// 8 $#,##0.00_);[Red]($#,##0.00) /// 9 0% /// 10 0.00% /// 11 0.00E+00 /// 12 # ?/? /// 13 # ??/?? /// 14 m/d/yyyy /// 15 d-mmm-yy /// 16 d-mmm /// 17 mmm-yy /// 18 h:mm AM/PM /// 19 h:mm:ss AM/PM /// 20 h:mm /// 21 h:mm:ss /// 22 m/d/yyyy h:mm /// 37 #,##0_);(#,##0) /// 38 #,##0_);[Red](#,##0) /// 39 #,##0.00_);(#,##0.00) /// 40 #,##0.00_);[Red](#,##0.00) /// 45 mm:ss /// 46 [h]:mm:ss /// 47 mm:ss.0 /// 48 ##0.0E+0 /// 49 @ /// </summary> /// <remarks></remarks> /// <seealso cref=""/> /// <param name="spreadsheetDocument"></param> private void generateStyle(SpreadsheetDocument spreadsheetDocument) { ExcelHelper.UpdateStylesheet(spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet, out styleIndex); }
/// <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); }
/// <summary> /// Convert a VariableValue to Cell /// </summary> /// <remarks></remarks> /// <seealso cref=""/> /// <param name="variableValue"></param> /// <param name="rowIndex"></param> /// <param name="columnIndex"></param> /// <returns></returns> protected Cell VariableValueToCell(VariableValue variableValue, int rowIndex, int columnIndex) { using (var uow = this.GetUnitOfWork()) { DataAttribute dataAttribute = uow.GetReadOnlyRepository <Variable>().Query(p => p.Id == variableValue.VariableId).Select(p => p.DataAttribute).FirstOrDefault(); string message = "row :" + rowIndex + "column:" + columnIndex; Debug.WriteLine(message); string cellRef = getColumnIndex(columnIndex); Cell cell = new Cell(); cell.CellReference = cellRef; cell.StyleIndex = ExcelHelper.GetExcelStyleIndex(dataAttribute.DataType, styleIndex); //cell.DataType = new EnumValue<CellValues>(getExcelType(dataAttribute.DataType.SystemType)); //cell.CellValue = new CellValue(variableValue.Value.ToString()); CellValues cellValueType = getExcelType(dataAttribute.DataType.SystemType); object value = variableValue.Value; if (value != null && !(value is DBNull) && cellValueType == CellValues.Number) { cell.DataType = new EnumValue <CellValues>(CellValues.Number); try { if (value.ToString() != "") { double d = Convert.ToDouble(value, System.Globalization.CultureInfo.InvariantCulture); cell.CellValue = new CellValue(d.ToString(System.Globalization.CultureInfo.InvariantCulture)); } } catch (Exception ex) { throw new Exception(ex.Message + "\n|" + message); } return(cell); } else { if (value != null && !(value is DBNull) && cellValueType == CellValues.Date) { cell.DataType = new EnumValue <CellValues>(CellValues.Number); //CultureInfo provider = CultureInfo.InvariantCulture; try { if (value.ToString() != "") { DateTime dt; if (dataAttribute.DataType != null && dataAttribute.DataType.Extra != null) { DataTypeDisplayPattern pattern = DataTypeDisplayPattern.Materialize(dataAttribute.DataType.Extra); if (!string.IsNullOrEmpty(pattern.StringPattern)) { IOUtility.ExportDateTimeString(value.ToString(), pattern.StringPattern, out dt); cell.CellValue = new CellValue(dt.ToOADate().ToString()); } else { if (IOUtility.IsDate(value.ToString(), out dt)) { cell.CellValue = new CellValue(dt.ToOADate().ToString()); } } } else { if (IOUtility.IsDate(value.ToString(), out dt)) { cell.CellValue = new CellValue(dt.ToOADate().ToString()); } } } } catch (Exception ex) { throw new Exception(ex.Message + "|" + message); } } else { cell.DataType = new EnumValue <CellValues>(CellValues.String); if (value == null) { cell.CellValue = new CellValue(""); } else { cell.CellValue = new CellValue(value.ToString()); } } } return(cell); } }