Hi,
We have an exported excel sheet that is created using c# code.
The incoming data from DB has decimal values in it but after the export, the excel contains all rounded values.
We tried to debug but unable to find the issue. below are the functions used.
private static HttpResponseMessage PrepareTargetSetupExcel(BaselineTargetSettingResponse baselineTargetSettingResponse, DataSet targetLookupData, List dataColumns, bool? canSeeRegularTarget, bool? canSeeStretchTarget, PublishedTargetVersionDetail publishedVersionDetail = null)
{
baselineTargetSettingResponse.Data.Columns.Remove(Constant.TOTALCOUNTCASESENSITIVECOLUMN);
baselineTargetSettingResponse.Data.Columns.Remove(Constant.CUSTOMCATEGORYECOLUMN);
baselineTargetSettingResponse.Data.Columns.Remove(Constant.ISLINEITEMDELETEDCOLUMN);
baselineTargetSettingResponse.Data.Columns.Remove(Constant.LINEITEMIDCOLUMN);
if (targetLookupData != null && baselineTargetSettingResponse.Data.Columns[Constant.DELETECOLUMN] != null)
baselineTargetSettingResponse.Data.Columns.Remove(Constant.DELETECOLUMN);
List<string> tableColumnNames = (from dc in baselineTargetSettingResponse.Data.Columns.Cast<DataColumn>()
select dc.ColumnName).ToList();
var extraTableColumns = tableColumnNames.Except(dataColumns.Select(item => item.Field)).ToList();
extraTableColumns.ForEach(item =>
{
baselineTargetSettingResponse.Data.Columns.Remove(item);
});
foreach (DataColumn tableColumn in baselineTargetSettingResponse.Data.Columns)
{
var column = dataColumns.Where(item => item.Field == tableColumn.ColumnName).FirstOrDefault();
if (column != null)
{
if (column.IsValueField)
tableColumn.ColumnName = column.ColumnTypeText + column.Title;
else if (column.IsPercentageField)
tableColumn.ColumnName = column.ColumnTypeText + column.Title;
else
tableColumn.ColumnName = column.Title;
}
}
baselineTargetSettingResponse.Data.AcceptChanges();
Utility.SetAsposeLicense();
Workbook targetWorkbook = new Workbook();
Worksheet targetSetupSheet = targetWorkbook.Worksheets[0];
targetSetupSheet.Name = targetLookupData != null ? ExportSheetName.TargetSetup.ToDescriptionString() :
ExportSheetName.PublishedTarget.ToDescriptionString();
targetSetupSheet.Cells.ImportData(baselineTargetSettingResponse.Data, 0, 0, new ImportTableOptions { IsFieldNameShown = true });
if (baselineTargetSettingResponse.Data != null && baselineTargetSettingResponse.Data.Rows.Count > 0)
{
#region lookup sheets
if (!canSeeRegularTarget.HasValue && !canSeeStretchTarget.HasValue && targetLookupData != null && targetLookupData.Tables.Count > 0)
{
ListObject listObject;
for (int count = 0; count < targetLookupData.Tables.Count; count++)
{
DataTable table = targetLookupData.Tables[count];
var sheetName = count == 0 ? Constant.EXCELINTTEAMTEXT : count == 1 ? Constant.EXCELCATEGORYTEXT : Constant.EXCELLOOKUPTEXT;
targetWorkbook.Worksheets.Add(sheetName);
Worksheet sheet = targetWorkbook.Worksheets[sheetName];
sheet.Cells.ImportData(table, 0, 0, new ImportTableOptions { IsFieldNameShown = count == targetLookupData.Tables.Count - 1 ? true : false });
if (count == targetLookupData.Tables.Count - 1)
{
var valueColumn = sheet.Cells.LastCell.Column;
for (int row = 2; row <= sheet.Cells.LastCell.Row; row++)
{
var cellStyle = sheet.Cells[row, valueColumn].GetStyle();
cellStyle.Number = 2;
sheet.Cells[row, valueColumn].SetStyle(cellStyle);
}
//Adding a new List Object to the worksheet
listObject = sheet.ListObjects[sheet.ListObjects.Add(0, 0, sheet.Cells.LastCell.Row, sheet.Cells.LastCell.Column, true)];
listObject.DisplayName = Constant.EXCELLOOKUPTEXT;
}
sheet.VisibilityType = VisibilityType.Hidden;
}
}
#endregion
var headerRow = targetSetupSheet.Cells.Rows[0];
for (int count = 0; count < headerRow.LastCell.Column + 1; count++)
{
string value = headerRow[count].StringValue;
headerRow[count].PutValue(value.Replace(Constant.REGULARTARGETTEXT, string.Empty).Replace(Constant.STRETCHTARGETTEXT, string.Empty));
}
#region Header Merge rows
Range dataHeader = targetSetupSheet.Cells.CreateRange(0, 0, 1, targetSetupSheet.Cells.LastCell.Column + 1);
Style styleHeader = targetWorkbook.CreateStyle();
SetFontStyleAndSize(dataHeader, styleHeader, ReportsExport.HeaderFontSize, true, true, true);
Range dataRangeHeader = targetSetupSheet.Cells.CreateRange(1, 0, targetSetupSheet.Cells.Rows.Count,
targetSetupSheet.Cells.LastCell.Column + 1);
Style style = targetWorkbook.CreateStyle();
SetFontStyleAndSize(dataRangeHeader, style, ReportsExport.DataFontSize, false, false, false);
targetSetupSheet.Cells.InsertRows(0, 1);
Range rangeHeaderField = targetSetupSheet.Cells.CreateRange(0, ReportsExport.FromDateRangeStart, 1, ReportsExport.FromDateRangeEnd);
rangeHeaderField.Merge();
rangeHeaderField.PutValue(Constant.METAFIELD, true, true);
var regularCount = dataColumns.Where(item => item.ColumnTypeText == Constant.REGULARTARGETTEXT).Count();
if (canSeeRegularTarget.HasValue && canSeeRegularTarget.Value || !canSeeRegularTarget.HasValue)
{
rangeHeaderField = targetSetupSheet.Cells.CreateRange(0, ReportsExport.FromDateRangeEnd, 1, regularCount);
rangeHeaderField.Merge();
rangeHeaderField.PutValue(Constant.REGULARTARGET, true, true);
}
var StretchCount = dataColumns.Where(item => item.ColumnTypeText == Constant.STRETCHTARGETTEXT).Count();
if (canSeeStretchTarget.HasValue && canSeeStretchTarget.Value || !canSeeStretchTarget.HasValue)
{
rangeHeaderField = targetSetupSheet.Cells.CreateRange(0, ReportsExport.FromDateRangeEnd + regularCount, 1, StretchCount);
rangeHeaderField.Merge();
rangeHeaderField.PutValue(Constant.STRETCHTARGET, true, true);
}
targetSetupSheet.Cells.Rows[0].Height = targetSetupSheet.Cells.Rows[1].Height = ReportsExport.TargetHeaderRowHeight;
#endregion
SummarySheetHeaderStyle(targetSetupSheet);
if (!canSeeRegularTarget.HasValue && !canSeeStretchTarget.HasValue && targetLookupData != null && targetLookupData.Tables.Count > 0)
{
CategoryAndIntTeamLookup(targetWorkbook, targetSetupSheet);
}
if (publishedVersionDetail != null)
{
targetSetupSheet.Cells.InsertRows(0, 3);
targetSetupSheet.Cells[Constant.A1CELL].Value = Constant.VERSIONNUMBERTEXT;
targetSetupSheet.Cells[Constant.B1CELL].Value = publishedVersionDetail.Version;
targetSetupSheet.Cells[Constant.D1CELL].Value = Constant.VERSIONNAMETEXT;
targetSetupSheet.Cells[Constant.E1CELL].Value = publishedVersionDetail.VersionName;
targetSetupSheet.Cells[Constant.G1CELL].Value = Constant.VERSIONDATETEXT + Constant.OPENINGBRACKET + AppConstant.Configuration.ApplicationTimeZone + Constant.CLOSINGBRACKET;
targetSetupSheet.Cells[Constant.H1CELL].Value = publishedVersionDetail.FormattedCreateDate;
targetSetupSheet.Cells[Constant.A2CELL].Value = Constant.VERSIONCOMMENTTEXT;
targetSetupSheet.Cells[Constant.B2CELL].Value = publishedVersionDetail.Comments;
}
NumericValidationRuleForExcelColumn(targetSetupSheet, ReportsExport.FromDateRangeEnd - 1,
targetSetupSheet.Cells.Rows[0].LastCell.Column > targetSetupSheet.Cells.LastCell.Column ? targetSetupSheet.Cells.Rows[0].LastCell.Column : targetSetupSheet.Cells.LastCell.Column, 2, Int16.MaxValue, Int64.MinValue.ToString(), Int64.MaxValue.ToString());
}
var path = HttpContext.Current.Server.MapPath(string.Format(Constant.COMPANYFINANCIALMAPPINGDATATMPFILEPATH, Constant.TILDESYMBOL + Constant.UPLOADTEMPFOLDER, Guid.NewGuid()));
targetWorkbook.CalculateFormula();
targetSetupSheet.AutoFitColumns();
targetWorkbook.Save(path, SaveFormat.Xlsx);
HttpResponseMessage response = Utility.GenerateExcel(path);
return response;
}