/// <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);
}
}
