/// <summary> /// /// </summary> /// <param name="worksheet"></param> /// <param name="tempColumn"></param> public static void CopyColumnFromTemplate(this ExcelWorksheet worksheet, ExcelColumnInfo tempColumn) { worksheet.InsertColumn(tempColumn.Index, 1); var column = worksheet.Column(tempColumn.Index); column.CopyFromTemplate(tempColumn.Column); }
protected virtual object FormatCellValue(object cellValue, ExcelColumnInfo info = null) { if (info != null) { // Boolean transformations. if (info.ExcelColumnAttribute != null && info.ExcelColumnAttribute.TrueValue != null && cellValue.Equals("True")) { return(info.ExcelColumnAttribute.TrueValue); } else if (info.ExcelColumnAttribute != null && info.ExcelColumnAttribute.FalseValue != null && cellValue.Equals("False")) { return(info.ExcelColumnAttribute.FalseValue); } else if (!string.IsNullOrWhiteSpace(info.FormatString) & string.IsNullOrEmpty(info.ExcelNumberFormat)) { return(string.Format(info.FormatString, cellValue)); } else if (cellValue.GetType() == typeof(DateTime) || (!Double.TryParse(cellValue.ToString(), out var test1) && DateTime.TryParse(cellValue.ToString(), out var test))) { return(string.Format("{0:MM/dd/yyyy}", DateTime.Parse(cellValue.ToString()))); } } return(cellValue); }
public void AppendColumnHeaderRowItem(ExcelColumnInfo column) { SheetColumns.Add(column); var headerName = column.IsExcelHeaderDefined ? column.Header : column.PropertyName; var dc = new DataColumn(headerName, typeof(ExcelCell)); if (column.IsHidden) { dc.ColumnMapping = MappingType.Hidden; } CurrentTable.Columns.Add(dc); }
private IList <ExcelSheetInfo> GetSheetInfoList(string filePath) { var sheetList = new List <ExcelSheetInfo>(); using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { var ext = Path.GetExtension(filePath); var book = ext == ".xls" ? (IWorkbook) new HSSFWorkbook(stream) : new XSSFWorkbook(stream); for (var i = 0; i < book.NumberOfSheets; ++i) { var sheet = book.GetSheetAt(i); var sheetInfo = new ExcelSheetInfo(); var sheetName = sheet.SheetName.Trim().Replace(" ", "").Replace("\t", ""); var parts = sheetName.Split(new[] { '_' }, StringSplitOptions.RemoveEmptyEntries); sheetInfo.Name = parts[0]; sheetInfo.SubName = parts.Length > 1 ? parts[1] : null; sheetList.Add(sheetInfo); if (!sheetInfo.NameValid || !sheetInfo.SubNameValid) { Debug.LogError($"Sheet Name is invalid! [{sheetName}]\neg. Name_Sub, Name, -Name_Sub, -Name"); } if (!sheetInfo.Enabled) { continue; } var titleRow = sheet.GetRow(0); // 1st row [FieldName] var typeRow = sheet.GetRow(1); // 2nd row [TypeSymbol] for (var j = 0; j < titleRow.LastCellNum; j++) { var colInfo = new ExcelColumnInfo { Index = j }; var fieldName = titleRow.GetCell(j).StringCellValue; fieldName = fieldName.Trim().Replace(" ", "").Replace("\t", ""); colInfo.Name = fieldName; var typeStr = typeRow.GetCell(j).StringCellValue; (colInfo.ValType, colInfo.IsArray, colInfo.ArraySep) = ParseValueType(typeStr); sheetInfo.ColumnInfos.Add(colInfo); } } } return(sheetList); }
private void SetDictionaryColumnInfo(List <ExcelColumnInfo> _props, object key) { var p = new ExcelColumnInfo(); p.ExcelColumnName = key?.ToString(); p.Key = key; // TODO:Dictionary value type is not fiexed //var _t = //var gt = Nullable.GetUnderlyingType(p.PropertyType); var isIgnore = false; if (_configuration.DynamicColumns != null && _configuration.DynamicColumns.Length > 0) { var dynamicColumn = _configuration.DynamicColumns.SingleOrDefault(_ => _.Key == key.ToString()); if (dynamicColumn != null) { p.Nullable = true; //p.ExcludeNullableType = item2[key]?.GetType(); if (dynamicColumn.Format != null) { p.ExcelFormat = dynamicColumn.Format; } if (dynamicColumn.Aliases != null) { p.ExcelColumnAliases = dynamicColumn.Aliases; } if (dynamicColumn.IndexName != null) { p.ExcelIndexName = dynamicColumn.IndexName; } p.ExcelColumnIndex = dynamicColumn.Index; if (dynamicColumn.Name != null) { p.ExcelColumnName = dynamicColumn.Name; } isIgnore = dynamicColumn.Ignore; p.ExcelColumnWidth = dynamicColumn.Width; } } if (!isIgnore) { _props.Add(p); } }
private void CreateReferenceSheet(IXlsxDocumentBuilder document, int id) { var builder = new XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder("Reference", isPreservationSheet: true, isHidden: true); builder.ActualRow = true; document.AppendSheet(builder); var excelinfo = new ExcelColumnInfo(id.ToString(), null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(" ", null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo("Version", null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo("2.1", null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); }
public string ToCsvString(object value, ExcelColumnInfo p) { if (value == null) { return(""); } if (value is DateTime dateTime) { if (p?.ExcelFormat != null) { return(dateTime.ToString(p.ExcelFormat, _configuration.Culture)); } return(_configuration.Culture.Equals(CultureInfo.InvariantCulture) ? dateTime.ToString("yyyy-MM-dd HH:mm:ss", _configuration.Culture) : dateTime.ToString(_configuration.Culture)); } if (p?.ExcelFormat != null && value is IFormattable formattableValue) { return(formattableValue.ToString(p.ExcelFormat, _configuration.Culture)); } return(Convert.ToString(value, _configuration.Culture)); }
private void WriteCell(MiniExcelStreamWriter writer, int rowIndex, int cellIndex, object value, ExcelColumnInfo p) { var v = string.Empty; var t = "str"; var s = "2"; if (value == null) { v = ""; } else if (value is string str) { v = ExcelOpenXmlUtils.EncodeXML(str); } else if (p?.ExcelFormat != null && value is IFormattable formattableValue) { var formattedStr = formattableValue.ToString(p.ExcelFormat, _configuration.Culture); v = ExcelOpenXmlUtils.EncodeXML(formattedStr); } else { Type type = null; if (p == null || p.Key != null) { // TODO: need to optimize // Dictionary need to check type every time, so it's slow.. type = value.GetType(); type = Nullable.GetUnderlyingType(type) ?? type; } else { type = p.ExcludeNullableType; //sometime it doesn't need to re-get type like prop } if (type.IsEnum) { t = "str"; var description = CustomPropertyHelper.DescriptionAttr(type, value); //TODO: need to optimze if (description != null) { v = description; } else { v = value.ToString(); } } else if (TypeHelper.IsNumericType(type)) { if (_configuration.Culture != CultureInfo.InvariantCulture) { t = "str"; //TODO: add style format } else { t = "n"; } if (type.IsAssignableFrom(typeof(decimal))) { v = ((decimal)value).ToString(_configuration.Culture); } else if (type.IsAssignableFrom(typeof(Int32))) { v = ((Int32)value).ToString(_configuration.Culture); } else if (type.IsAssignableFrom(typeof(Double))) { v = ((Double)value).ToString(_configuration.Culture); } else if (type.IsAssignableFrom(typeof(Int64))) { v = ((Int64)value).ToString(_configuration.Culture); } else if (type.IsAssignableFrom(typeof(UInt32))) { v = ((UInt32)value).ToString(_configuration.Culture); } else if (type.IsAssignableFrom(typeof(UInt16))) { v = ((UInt16)value).ToString(_configuration.Culture); } else if (type.IsAssignableFrom(typeof(UInt64))) { v = ((UInt64)value).ToString(_configuration.Culture); } else if (type.IsAssignableFrom(typeof(Int16))) { v = ((Int16)value).ToString(_configuration.Culture); } else if (type.IsAssignableFrom(typeof(Single))) { v = ((Single)value).ToString(_configuration.Culture); } else if (type.IsAssignableFrom(typeof(Single))) { v = ((Single)value).ToString(_configuration.Culture); } else { v = (decimal.Parse(value.ToString())).ToString(_configuration.Culture); } } else if (type == typeof(bool)) { t = "b"; v = (bool)value ? "1" : "0"; } else if (type == typeof(byte[]) && _configuration.EnableConvertByteArray) { var bytes = (byte[])value; if (bytes != null) { // TODO: Setting configuration because it might have high cost? var format = ImageHelper.GetImageFormat(bytes); //it can't insert to zip first to avoid cache image to memory //because sheet xml is opening.. https://github.com/shps951023/MiniExcel/issues/304#issuecomment-1017031691 //int rowIndex, int cellIndex var file = new FileDto() { Byte = bytes, RowIndex = rowIndex, CellIndex = cellIndex, SheetId = currentSheetIndex }; if (format != ImageFormat.unknown) { file.Extension = format.ToString(); file.IsImage = true; } else { file.Extension = "bin"; } _files.Add(file); //TODO:Convert to base64 var base64 = $"@@@fileid@@@,{file.Path}"; v = ExcelOpenXmlUtils.EncodeXML(base64); s = "4"; } } else if (type == typeof(DateTime)) { if (_configuration.Culture != CultureInfo.InvariantCulture) { t = "str"; v = ((DateTime)value).ToString(_configuration.Culture); } else if (p == null || p.ExcelFormat == null) { t = null; s = "3"; v = ((DateTime)value).ToOADate().ToString(CultureInfo.InvariantCulture); } else { // TODO: now it'll lose date type information t = "str"; v = ((DateTime)value).ToString(p.ExcelFormat, _configuration.Culture); } } else { //TODO: _configuration.Culture v = ExcelOpenXmlUtils.EncodeXML(value.ToString()); } } var columname = ExcelOpenXmlUtils.ConvertXyToCell(cellIndex, rowIndex); if (v != null && (v.StartsWith(" ", StringComparison.Ordinal) || v.EndsWith(" ", StringComparison.Ordinal))) /*Prefix and suffix blank space will lost after SaveAs #294*/ { writer.Write($"<x:c r=\"{columname}\" {(t == null ? "" : $"t =\"{t}\"")} s=\"{s}\" xml:space=\"preserve\"><x:v>{v}</x:v></x:c>");
private void AppendColumnsAndRows(SqadXlsxSheetBuilderBase sheetBuilder, ActualExport value) { var exp = "Export Properties"; var sec = " "; var excelinfo = new ExcelColumnInfo(exp, null, new ExcelColumnAttribute(), null); sheetBuilder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(sec, null, new ExcelColumnAttribute(), null); sheetBuilder.AppendColumnHeaderRowItem(excelinfo); List <ExcelCell> row = new List <ExcelCell>(); ExcelCell cell = new ExcelCell(); cell = new ExcelCell(); cell.CellHeader = exp; cell.CellValue = $"Resource Set"; row.Add(cell); cell = new ExcelCell(); cell.CellHeader = sec; cell.CellValue = $"{value.ResourceName}"; row.Add(cell); sheetBuilder.AppendRow(row); cell = new ExcelCell(); cell.CellHeader = exp; cell.CellValue = $"Client"; row.Add(cell); cell = new ExcelCell(); cell.CellHeader = sec; cell.CellValue = $"{value.ClientName}"; row.Add(cell); sheetBuilder.AppendRow(row); cell = new ExcelCell(); cell.CellHeader = exp; cell.CellValue = $"From Date"; row.Add(cell); cell = new ExcelCell(); cell.CellHeader = sec; cell.CellValue = $"{value.Request.FromDate.ToString("dddd, MMMM dd, yyyy")}"; row.Add(cell); sheetBuilder.AppendRow(row); cell = new ExcelCell(); cell.CellHeader = exp; cell.CellValue = $"To Date"; row.Add(cell); cell = new ExcelCell(); cell.CellHeader = sec; cell.CellValue = $"{value.Request.ToDate.ToString("dddd, MMMM dd, yyyy")}"; row.Add(cell); sheetBuilder.AppendRow(row); cell = new ExcelCell(); cell.CellHeader = exp; cell.CellValue = $"Created By"; row.Add(cell); cell = new ExcelCell(); cell.CellHeader = sec; cell.CellValue = $"{value.CreatedBy}"; row.Add(cell); sheetBuilder.AppendRow(row); cell = new ExcelCell(); cell.CellHeader = exp; cell.CellValue = $"Created On"; row.Add(cell); cell = new ExcelCell(); cell.CellHeader = sec; cell.CellValue = $"{value.Request.CreateDate.ToString("dddd, MMMM dd, yyyy HH:mm")}"; row.Add(cell); sheetBuilder.AppendRow(row); cell = new ExcelCell(); cell.CellHeader = exp; cell.CellValue = string.Empty; row.Add(cell); cell = new ExcelCell(); cell.CellHeader = sec; cell.CellValue = string.Empty; row.Add(cell); sheetBuilder.AppendRow(row); //PopulateNames(sheetBuilder, exp, sec, "Products", value.Flights.Select(x=>x.ProductName).Distinct().ToList()); PopulateNames(sheetBuilder, exp, sec, "Products", value.ProductNames); if (value.PlanTypeNames.Any()) { PopulateNames(sheetBuilder, exp, sec, "Plan Types", value.PlanTypeNames); } }
private void FormatWorkSheet(XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder builder, ActualWorksheet item, Dictionary <string, string> custom) { var excelinfo = new ExcelColumnInfo(item.Data, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlanName, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.VehicleName, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ProductName, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.SubtypeName, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.UnitName, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.MarketName, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.DemoName, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); if (custom != null && custom.Count > 0) { foreach (var it in custom) { excelinfo = new ExcelColumnInfo(it.Key, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); } } else { excelinfo = new ExcelColumnInfo(item.CreativeName, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.FundingSourceName, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); } excelinfo = new ExcelColumnInfo(item.StartDate, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.EndDate, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); //planned excelinfo = new ExcelColumnInfo(item.PlannedGRPs, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedTRPs, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); var hidden = !(Convert.ToBoolean(item.MediaType.EnableWeeklyReach) || Convert.ToInt16(item.MediaType.ReachType) > 0); excelinfo = new ExcelColumnInfo(item.PlannedReach, null, new ExcelColumnAttribute() { IsHidden = hidden }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedFrequency, null, new ExcelColumnAttribute() { IsHidden = hidden }, null); builder.AppendColumnHeaderRowItem(excelinfo); var shownet = Convert.ToBoolean(item.MediaType.ExternalActualsNet); excelinfo = new ExcelColumnInfo(item.PlannedGross, null, new ExcelColumnAttribute() { IsHidden = shownet }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedNet, null, new ExcelColumnAttribute() { IsHidden = !shownet }, null); builder.AppendColumnHeaderRowItem(excelinfo); PopulatePlannedActualColumns(builder, item); //actual excelinfo = new ExcelColumnInfo(item.ActualGRPs, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualTRPs, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualReach, null, new ExcelColumnAttribute() { IsHidden = hidden }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualFrequency, null, new ExcelColumnAttribute() { IsHidden = hidden }, null); builder.AppendColumnHeaderRowItem(excelinfo); //if (!shownet) //{ // excelinfo = new ExcelColumnInfo(item.ActualGross, null, new ExcelColumnAttribute(), null); // builder.AppendColumnHeaderRowItem(excelinfo); //} //else //{ // excelinfo = new ExcelColumnInfo(item.ActualNet, null, new ExcelColumnAttribute(), null); // builder.AppendColumnHeaderRowItem(excelinfo); //} excelinfo = new ExcelColumnInfo(item.ActualGross, null, new ExcelColumnAttribute() { IsHidden = shownet }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualNet, null, new ExcelColumnAttribute() { IsHidden = !shownet }, null); builder.AppendColumnHeaderRowItem(excelinfo); PopulatePlannedActualColumns(builder, item, true); excelinfo = new ExcelColumnInfo(item.DateActualized, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualizedBy, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); }
private void PopulatePlannedActualColumns(XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder builder, ActualWorksheet item, bool actual = false) { switch (item.MediaType.MediaClass) { case (int)MediaClass.Print: case (int)MediaClass.Newspaper: case (int)MediaClass.ReachBased: if (!actual) { //visible var excelinfo = new ExcelColumnInfo(item.PlannedProduction, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); //unvisible excelinfo = new ExcelColumnInfo(item.PlannedImpressions, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedClicks, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedLeads, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedRichMedia, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedAdServing, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); } else { var excelinfo = new ExcelColumnInfo(item.ActualProduction, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualImpressions, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualClicks, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualLeads, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualRichMedia, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualAdServing, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); } break; case (int)MediaClass.Interactive: if (!actual) { var excelinfo = new ExcelColumnInfo(item.PlannedProduction, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedImpressions, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedClicks, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedLeads, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedRichMedia, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedAdServing, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); } else { var excelinfo = new ExcelColumnInfo(item.ActualProduction, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualImpressions, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualClicks, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualLeads, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualRichMedia, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualAdServing, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); } break; default: if (!actual) { var excelinfo = new ExcelColumnInfo(item.PlannedProduction, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedImpressions, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedClicks, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedLeads, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedRichMedia, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.PlannedAdServing, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); } else { var excelinfo = new ExcelColumnInfo(item.ActualProduction, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualImpressions, null, new ExcelColumnAttribute(), null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualClicks, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualLeads, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualRichMedia, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); excelinfo = new ExcelColumnInfo(item.ActualAdServing, null, new ExcelColumnAttribute() { IsHidden = true }, null); builder.AppendColumnHeaderRowItem(excelinfo); } break; } }
public void CreateReferenceCell(ExcelColumnInfo info, string columnName, IXlsxDocumentBuilder document, ref ExcelCell cell) { if (_staticValuesResolver == null) { return; } DataTable columntResolveTable = null; if (info.PropertyType != null && info.PropertyType.BaseType == typeof(Enum)) { columntResolveTable = _staticValuesResolver.GetRecordsFromEnum(info.PropertyType); info.ExcelColumnAttribute.ResolveFromTable = columnName; } else if (string.IsNullOrEmpty(info.ExcelColumnAttribute.ResolveFromTable) == false) { _resolveTables.TryGetValue(info.ExcelColumnAttribute.ResolveFromTable, out columntResolveTable); if (columntResolveTable == default(DataTable)) { columntResolveTable = _staticValuesResolver.GetRecordsByTableName(info.ExcelColumnAttribute.ResolveFromTable); if (columntResolveTable != null) { _resolveTables.Add(info.ExcelColumnAttribute.ResolveFromTable, columntResolveTable); } } } if (!_isExportJsonToXls) { if (columntResolveTable != null) { columntResolveTable.TableName = info.ExcelColumnAttribute.ResolveFromTable; if (string.IsNullOrEmpty(info.ExcelColumnAttribute.OverrideResolveTableName) == false) { columntResolveTable.TableName = info.ExcelColumnAttribute.OverrideResolveTableName; } cell.DataValidationSheet = columntResolveTable.TableName; var referenceSheet = document.GetReferenceSheet() as XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder; if (referenceSheet == null) { referenceSheet = new XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder(cell.DataValidationSheet, true); document.AppendSheet(referenceSheet); } else { referenceSheet.AddAndActivateNewTable(cell.DataValidationSheet); } cell.DataValidationBeginRow = referenceSheet.GetNextAvailableRow(); this.PopulateReferenceSheet(referenceSheet, columntResolveTable); cell.DataValidationRowsCount = referenceSheet.GetCurrentRowCount; if (string.IsNullOrEmpty(info.ExcelColumnAttribute.ResolveName) == false) { cell.DataValidationNameCellIndex = referenceSheet.GetColumnIndexByColumnName(info.ExcelColumnAttribute.ResolveName); } if (string.IsNullOrEmpty(info.ExcelColumnAttribute.ResolveValue) == false) { cell.DataValidationValueCellIndex = referenceSheet.GetColumnIndexByColumnName(info.ExcelColumnAttribute.ResolveValue); } } else if (string.IsNullOrEmpty(info.ExcelColumnAttribute.ResolveFromTable) == false) { columntResolveTable = _staticValuesResolver.GetRecordsByTableName(info.ExcelColumnAttribute.ResolveFromTable); } } }
public void Serialize(Type itemType, object value, IXlsxDocumentBuilder document, string sheetName = null, string columnPrefix = null, XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder sheetBuilderOverride = null) { ExcelColumnInfoCollection columnInfo = _columnResolver.GetExcelColumnInfo(itemType, value, sheetName); XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder sheetBuilder = null; if (sheetName == null) { var sheetAttribute = itemType.GetCustomAttributes(true).SingleOrDefault(s => s is ExcelSheetAttribute); sheetName = sheetAttribute != null ? (sheetAttribute as ExcelSheetAttribute).SheetName : itemType.Name; } if (columnInfo.Any()) { if (sheetBuilderOverride == null) { sheetBuilder = document.GetSheetByName(sheetName) as XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder; } else { sheetBuilder = sheetBuilderOverride; } if (sheetBuilder == null) { sheetBuilder = new XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder(sheetName); //Move this to attribute hidden property //if (new List<string>() { "Formulas", "LeftTableColumn", "Cells" }.Contains(sheetName)) //{ // sheetBuilder.IsHidden = true; //} document.AppendSheet(sheetBuilder); } //Convert Dictionary Column foreach (var col in columnInfo) { if (col.PropertyName.EndsWith("_Dict_")) { string columnName = col.PropertyName.Replace("_Dict_", ""); object colValueDict = null; if (sheetName == col.PropertyName.Replace("_Dict_", "")) { colValueDict = value; } else { colValueDict = GetFieldOrPropertyValue(value, col.PropertyName.Replace("_Dict_", "")); } if (columnName.Contains(":") && (colValueDict == null || (colValueDict != null && string.IsNullOrEmpty(colValueDict.ToString())))) { colValueDict = GetFieldPathValue(value, columnName); } if (colValueDict == null || string.IsNullOrEmpty(colValueDict.ToString())) { continue; } object dictionaryKeys = colValueDict.GetType().GetProperty("Keys").GetValue(colValueDict); int count = 0; foreach (var key in (System.Collections.IEnumerable)dictionaryKeys) { ExcelColumnInfo temlKeyColumn = col.Clone() as ExcelColumnInfo; temlKeyColumn.PropertyName = temlKeyColumn.PropertyName.Replace("_Dict_", $":Key:{count}"); sheetBuilder.AppendColumnHeaderRowItem(temlKeyColumn); var currentItem = colValueDict.GetType().GetProperty("Item").GetValue(colValueDict, new object[] { key }); if (FormatterUtils.IsSimpleType(currentItem.GetType())) { ExcelColumnInfo temlValueColumn = col.Clone() as ExcelColumnInfo; temlValueColumn.PropertyName = temlValueColumn.PropertyName.Replace("_Dict_", $":Value:{count}"); sheetBuilder.AppendColumnHeaderRowItem(temlValueColumn); } else { string path = col.PropertyName.Replace("_Dict_", $":Value:{count}"); this.Serialize(currentItem.GetType(), value, document, sheetName, path, sheetBuilderOverride); } count++; } } else if (col.PropertyName.EndsWith("_List_")) { string columnName = col.PropertyName.Replace("_List_", ""); List <object> colListValue = GetFieldOrPropertyValue(value, col.PropertyName.Replace("_List_", "")) as List <object>; if (columnName.Contains(":") && (colListValue == null || (colListValue != null && string.IsNullOrEmpty(colListValue.ToString())))) { colListValue = GetFieldPathValue(value, columnName) as List <object>; } if (colListValue == null) { continue; } int dictColumnCount = colListValue.Count(); for (int i = 0; i < dictColumnCount; i++) { string listColumnPrefix = col.PropertyName.Replace("_List_", $":{i}"); if (FormatterUtils.IsSimpleType(colListValue[i].GetType())) { ExcelColumnInfo colToAppend = (ExcelColumnInfo)col.Clone(); colToAppend.PropertyName = listColumnPrefix; sheetBuilder.AppendColumnHeaderRowItem(colToAppend); } else { this.Serialize(colListValue[i].GetType(), colListValue[i], document, null, listColumnPrefix, sheetBuilder); } } } else if (col.PropertyName.EndsWith("_CustomField_") || col.PropertyName.EndsWith("_CustomField_Single_")) { string columnName = col.PropertyName.Replace("_CustomField_", "").Replace("Single_", ""); List <object> colCustomFields = GetFieldOrPropertyValue(value, columnName) as List <object>; if (columnName.Contains(":") && (colCustomFields == null || (colCustomFields != null && string.IsNullOrEmpty(colCustomFields.ToString())))) { colCustomFields = GetFieldPathValue(value, columnName) as List <object>; } if (colCustomFields == null) { continue; } foreach (var customField in colCustomFields) { int customFieldId = ((dynamic)customField).ID; bool isActual = ((dynamic)customField).Actual; ExcelColumnInfo temlKeyColumn = col.Clone() as ExcelColumnInfo; string propetyActual = isActual ? ":Actual" : string.Empty; string customFieldDef = _staticValuesResolver.GetCustomFieldName(customFieldId); if (col.PropertyName.EndsWith("_CustomField_Single_")) { customFieldDef = string.Empty; temlKeyColumn.PropertyName = temlKeyColumn.PropertyName.Replace("_CustomField_Single_", $"{propetyActual}"); temlKeyColumn.ExcelColumnAttribute.Header = temlKeyColumn.Header = temlKeyColumn.PropertyName; } else { temlKeyColumn.PropertyName = temlKeyColumn.PropertyName.Replace("_CustomField_", $"{propetyActual}:{customFieldId}"); temlKeyColumn.ExcelColumnAttribute.Header = temlKeyColumn.Header = temlKeyColumn.PropertyName + ":" + customFieldDef; } sheetBuilder.AppendColumnHeaderRowItem(temlKeyColumn); } } else { if (columnPrefix != null) { ExcelColumnInfo temlKeyColumn = col.Clone() as ExcelColumnInfo; temlKeyColumn.PropertyName = $"{columnPrefix}:{temlKeyColumn.PropertyName}"; sheetBuilder.AppendColumnHeaderRowItem(temlKeyColumn); } else { sheetBuilder.AppendColumnHeaderRowItem(col); } } } } //if its recursive do not populate rows and return to parent if (columnPrefix != null) { return; } if (sheetName != null && sheetBuilder == null) { sheetBuilder = (XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder)document.GetSheetByName(sheetName); } //adding rows data if (value != null) { var columns = columnInfo.Keys.ToList(); if (value is IEnumerable <object> && (value as IEnumerable <object>).Count() > 0) { foreach (var dataObj in value as IEnumerable <object> ) { PopulateRows(columns, dataObj, sheetBuilder, columnInfo, document); var deepSheetsInfo = _sheetResolver.GetExcelSheetInfo(itemType, dataObj); PopulateInnerObjectSheets(deepSheetsInfo, document, itemType); } } else if (!(value is IEnumerable <object>)) { PopulateRows(columns, value, sheetBuilder, columnInfo, document); var sheetsInfo = _sheetResolver.GetExcelSheetInfo(itemType, value); PopulateInnerObjectSheets(sheetsInfo, document, itemType); } } if (sheetBuilder != null) { sheetBuilder.ShouldAddHeaderRow = true; } }
private void PopulateRows(List <string> columns, object value, XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder sheetBuilder, ExcelColumnInfoCollection columnInfo = null, IXlsxDocumentBuilder document = null, List <ExcelCell> rowOverride = null) { if (sheetBuilder == null) { return; } List <ExcelCell> row = new List <ExcelCell>(); if (rowOverride != null) { row = rowOverride; } for (int i = 0; i <= columns.Count - 1; i++) { string columnName = columns[i]; if (columnName.EndsWith("_Dict_")) { columnName = columnName.Replace("_Dict_", ""); object dictionaryObj = null; if (sheetBuilder.GetCurrentTableName == columnName) { dictionaryObj = value; } else if (columnName.Contains(":")) { dictionaryObj = GetFieldPathValue(value, columnName); } else { dictionaryObj = (Dictionary <int, double>)GetFieldOrPropertyValue(value, columnName); } if (dictionaryObj == null || string.IsNullOrEmpty(dictionaryObj.ToString())) { continue; } object dictionaryKeys = dictionaryObj.GetType().GetProperty("Keys").GetValue(dictionaryObj); int colCount = 0; foreach (var key in (System.Collections.IEnumerable)dictionaryKeys) { ExcelCell keyCell = new ExcelCell(); keyCell.CellHeader = columnName + $":Key:{colCount}"; keyCell.CellValue = key; ExcelColumnInfo info = null; if (columnInfo != null) { info = columnInfo[i]; CreateReferenceCell(info, columnName, document, ref keyCell); } row.Add(keyCell); var currentItem = dictionaryObj.GetType().GetProperty("Item").GetValue(dictionaryObj, new object[] { key }); if (FormatterUtils.IsSimpleType(currentItem.GetType())) { ExcelCell valueCell = new ExcelCell(); valueCell.CellHeader = columnName + $":Value:{colCount}"; valueCell.CellValue = currentItem; row.Add(valueCell); } else { string path = columnName + $":Value:{colCount}"; ExcelColumnInfoCollection listInnerObjectColumnInfo = _columnResolver.GetExcelColumnInfo(currentItem.GetType(), currentItem, path, true); PopulateRows(listInnerObjectColumnInfo.Keys.ToList(), currentItem, sheetBuilder, listInnerObjectColumnInfo, document, row); } colCount++; } } else if (columnName.EndsWith("_List_")) { columnName = columnName.Replace("_List_", ""); List <object> listValue = new List <object>(); if (columnName.Contains(":")) { var valueObject = GetFieldPathValue(value, columnName); if (valueObject != null && string.IsNullOrEmpty(valueObject.ToString()) == false) { listValue = (List <object>)valueObject; } } else { listValue = (List <object>)GetFieldOrPropertyValue(value, columnName); } int colCount = 0; foreach (var kv in listValue) { string listColumnPrefix = columnName + $":{colCount}"; if (FormatterUtils.IsSimpleType(kv.GetType())) { ExcelCell listValueCell = new ExcelCell(); listValueCell.CellHeader = listColumnPrefix; listValueCell.CellValue = kv; row.Add(listValueCell); } else { ExcelColumnInfoCollection listInnerObjectColumnInfo = _columnResolver.GetExcelColumnInfo(kv.GetType(), kv, listColumnPrefix, true); PopulateRows(listInnerObjectColumnInfo.Keys.ToList(), kv, sheetBuilder, listInnerObjectColumnInfo, document, row); } colCount++; } } else if (columnName.EndsWith("_CustomField_") || columnName.EndsWith("_CustomField_Single_")) { bool isSingleValue = columnName.Contains("Single_"); columnName = columnName.Replace("_CustomField_", "").Replace("Single_", ""); List <object> customFields = null; if (columnName.Contains(":")) { var valueObject = GetFieldPathValue(value, columnName); if (valueObject != null && string.IsNullOrEmpty(valueObject.ToString()) == false) { customFields = (List <object>)valueObject; } } else { customFields = (List <object>)GetFieldOrPropertyValue(value, columnName); } if (customFields == null) { continue; } //need to get all custom columns List <ExcelColumnInfo> allCustomColumns = null; if (isSingleValue) { allCustomColumns = sheetBuilder.SheetColumns.Where(w => w.PropertyName == columnName).ToList(); } else { allCustomColumns = sheetBuilder.SheetColumns.Where(w => w.PropertyName.StartsWith(columnName)).ToList(); } var objID = GetFieldOrPropertyValue(value, "ID"); foreach (var customColumn in allCustomColumns) { object objectCustomField = customFields.Where(w => customColumn.PropertyName.EndsWith($":{((dynamic)w).ID}")).Where(w => customColumn.PropertyName.Contains("Actual") ? ((dynamic)w).Actual == true : ((dynamic)w).Actual == false).FirstOrDefault(); ExcelCell customValueHeaderCell = new ExcelCell(); if (objectCustomField == null && !isSingleValue) { customValueHeaderCell.IsLocked = true; customValueHeaderCell.CellHeader = customColumn.Header; customValueHeaderCell.CellValue = "n/a"; } else { dynamic customFieldItem = (dynamic)objectCustomField; string isActualText = string.Empty; string columnNameCombined = string.Empty; if (isSingleValue) { isActualText = columnName.Contains("Actual") ? ":Actual" : string.Empty; columnNameCombined = $"{columnName}{isActualText}"; customFieldItem = (dynamic)customFields.First(); } else { isActualText = customFieldItem.Actual ? ":Actual" : string.Empty; columnNameCombined = $"{columnName}{isActualText}:{customFieldItem.ID}"; } customValueHeaderCell.CellHeader = customColumn.Header; if (customFieldItem is CustomFieldModel) { if (!String.IsNullOrEmpty((customFieldItem as CustomFieldModel).Key)) { ExcelCell keyPreservationCell = new ExcelCell(); keyPreservationCell.CellHeader = $"{columnNameCombined}:Key:{objID}"; keyPreservationCell.CellValue = customFieldItem.Key?.ToString(); CreatePreserveCell(keyPreservationCell, document); } } ExcelCell valuePreservationCell = new ExcelCell(); valuePreservationCell.CellHeader = $"{columnNameCombined}:Value:{objID}"; if (customFieldItem != null) { valuePreservationCell.CellValue = customFieldItem.Value; customValueHeaderCell.CellValue = customFieldItem.Value; if (customFieldItem.Override != null) { customValueHeaderCell.CellValue = customFieldItem.Override; } } if (valuePreservationCell.CellValue != null && valuePreservationCell.CellValue.GetType() == typeof(DateTime)) { valuePreservationCell.CellValue = valuePreservationCell.CellValue.ToString(); } if (customValueHeaderCell.CellValue != null && customValueHeaderCell.CellValue.GetType() == typeof(DateTime)) { customValueHeaderCell.CellValue = customValueHeaderCell.CellValue.ToString(); } CreatePreserveCell(valuePreservationCell, document); ExcelCell overridePreservationCell = new ExcelCell(); overridePreservationCell.CellHeader = $"{columnNameCombined}:Override:{objID}"; overridePreservationCell.CellValue = customFieldItem.Override?.ToString(); CreatePreserveCell(overridePreservationCell, document); ExcelCell textPreservationCell = new ExcelCell(); textPreservationCell.CellHeader = $"{columnNameCombined}:Text:{objID}"; textPreservationCell.CellValue = customFieldItem.Text; CreatePreserveCell(textPreservationCell, document); ExcelCell hiddenTextPreservationCell = new ExcelCell(); hiddenTextPreservationCell.CellHeader = $"{columnNameCombined}:HiddenText:{objID}"; hiddenTextPreservationCell.CellValue = $"\"{customFieldItem.HiddenText}\""; CreatePreserveCell(hiddenTextPreservationCell, document); try { if (customFieldItem is CustomFieldModel && (customFieldItem as CustomFieldModel).ValueMix != null) { foreach (var valueM in (customFieldItem as CustomFieldModel).ValueMix) { string valueMKey = valueM.Key; decimal valueMValue = valueM.Value; ExcelCell mixedPropertyPreservationCell = new ExcelCell(); mixedPropertyPreservationCell.CellHeader = $"{columnNameCombined}:ValueMix:{valueM.Key}:{objID}"; mixedPropertyPreservationCell.CellValue = $"\"{valueM.Value.ToString()}\""; CreatePreserveCell(mixedPropertyPreservationCell, document); } } } catch (Exception ex) { string s = ex.Message; } ExcelCell commonPropertyPreservationCell = new ExcelCell(); commonPropertyPreservationCell.CellHeader = $"{columnNameCombined}:Common:{objID}"; commonPropertyPreservationCell.CellValue = $"\"{customFieldItem.Common}\""; CreatePreserveCell(commonPropertyPreservationCell, document); } row.Add(customValueHeaderCell); } } else { ExcelCell cell = new ExcelCell(); cell.CellHeader = columnName; var cellValue = GetFieldOrPropertyValue(value, columnName); if (columnName.Contains(":") && (cellValue == null || (cellValue != null && string.IsNullOrEmpty(cellValue.ToString())))) { cellValue = GetFieldPathValue(value, columnName); } ExcelColumnInfo info = null; if (columnInfo != null) { info = columnInfo[i]; CreateReferenceCell(info, columnName, document, ref cell); } if (cellValue != null) { cell.CellValue = FormatCellValue(cellValue, info); } if (info != null) { if (info.IsExcelHeaderDefined) { cell.CellHeader = info.Header; } } row.Add(cell); } } if (row.Count() > 0 && rowOverride == null) { sheetBuilder.AppendRow(row.ToList()); } }