problem in data after exporting to excel

i have an employee detail which im generating excel sheet on a button click reportviewer is generated from which user can take excel as well as pdf file.

but i have problem in excel i have a column of Account number which can be ranging from 15 to 16 digit account number.

Excel doesnt allow digit more then 15 the 16th digit will be replaced with 0 if we try to enter 16th digit.

so the account number with 16 digit is displayed but when clicked it changes to some exponential form

how to overcome this, any changes that can be made in RDLC ?

It is an Excel limitation since it can only handle 15 digits of precision for numbers. When a number exceeds 15 digits, Excel converts it to scientific notation or truncates the 16th digit to zero.

In your RDLC report, you can format the account number column as text, right-click on the account number field in the RDLC report, select "Text Box Properties," and then go to the "Number" tab. Select "Text" from the category list.

Another approach is to prepend an apostrophe (`'`) to the account number in the RDLC report. This forces Excel to treat the content as text.
You can do this by modifying the expression for the account number field in the RDLC report. For example:

= "'" & Fields!AccountNumber.Value

If you have control over the export process, you can create a custom function to export the data to Excel while ensuring that the account number column is formatted as text.
This can be done using libraries like EPPlus or ClosedXML in .NET, which allow you to programmatically create Excel files and set cell formats.Example using ClosedXML:

using ClosedXML.Excel;

public void ExportToExcel(DataTable dataTable, string filePath)
{
    using (var workbook = new XLWorkbook())
    {
        var worksheet = workbook.Worksheets.Add(dataTable, "EmployeeDetails");
        
        // Format the account number column as text
        worksheet.Column("AccountNumberColumn").Style.NumberFormat.Format = "@";
        
        workbook.SaveAs(filePath);
    }
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amira Bedhiafi1y ago
It is an Excel limitation since it can only handle 15 digits of precision for numbers. When a number exceeds 15 digits, Excel converts it to scientific notation or truncates the 16th digit to zero.

In your RDLC report, you can format the account number column as text, right-click on the account number field in the RDLC report, select "Text Box Properties," and then go to the "Number" tab. Select "Text" from the category list.

Another approach is to prepend an apostrophe (`'`) to the account number in the RDLC report. This forces Excel to treat the content as text.
You can do this by modifying the expression for the account number field in the RDLC report. For example:
```
= "'" & Fields!AccountNumber.Value
```
If you have control over the export process, you can create a custom function to export the data to Excel while ensuring that the account number column is formatted as text.
This can be done using libraries like EPPlus or ClosedXML in .NET, which allow you to programmatically create Excel files and set cell formats.Example using ClosedXML:
```
using ClosedXML.Excel;

public void ExportToExcel(DataTable dataTable, string filePath)
{
    using (var workbook = new XLWorkbook())
    {
        var worksheet = workbook.Worksheets.Add(dataTable, "EmployeeDetails");
        
        // Format the account number column as text
        worksheet.Column("AccountNumberColumn").Style.NumberFormat.Format = "@";
        
        workbook.SaveAs(filePath);
    }
}
```