We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Getting rounded up values for demical data

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

@amishra385,

Thanks for the code segment and details.

We cannot evaluate your issue using your sample code segment as you are importing data table and performing other tasks for which we are not sure. We appreciate if you could create a sample (runnable) project (preferably a simple console application), you may zip the project and post us here to reproduce the issue. Please remove any inter dependencies for external database or data source and create dynamic Dataset/DataTable in your code, so we could compile/execute your project without any error. Furthermore, in the sample project, you may remove unnecessary tasks to only concentrate on the issue for demonstration. This will help us really to evaluate your issue precisely to consequently figure it out soon.