0
private DataTable ExcelToDataTable(IFormFile file)
    {
        using (var stream = file.OpenReadStream())
        {
            using (var reader = ExcelReaderFactory.CreateReader(stream))
            {
                var result = reader.AsDataSet(new ExcelDataSetConfiguration()
                {
                    ConfigureDataTable = (data) => new ExcelDataTableConfiguration()
                    {
                        UseHeaderRow = true,
                    }
                });

                DataTableCollection table = result.Tables;
                DataTable resultTable = table["Employee Information"];

                return resultTable;
            }
        }
    }

Hi All, There is a column named uniquenumber in excel which contains the value 0002, when the data is converted to DataSet, the column value is changed to 2.

Can anyone help me fixing it ?

Amit
  • 167
  • 1
  • 2
  • 14
  • 1
    Is the cell formatted as 'Text'? – Hmax Dec 12 '18 at 13:39
  • There's no truncation if the field's/column's type is numeric. 0002 is equal to 2 and 00000002. If you want the number to be displayed in a specific way, format it during display, eg with a format string or style – Panagiotis Kanavos Dec 12 '18 at 13:40
  • @Hmax : format of the cell is General. – Amit Dec 12 '18 at 13:42
  • @PanagiotisKanavos: The problem is I have to store the value in DB. And the datatype of the column in the db is nvarchar so '2' <> '0002' . – Amit Dec 12 '18 at 13:44
  • 1
    Have you checked the [AsDataSet configuration options](https://github.com/ExcelDataReader/ExcelDataReader#asdataset-configuration-options) ? Have you tried setting `UseColumnDataType = true,` ? – Panagiotis Kanavos Dec 12 '18 at 13:44
  • @Amit if you want to treat that as text you have to *read* it as text. Unless you specify in a column's/cell's style that it's text, it's considered a number. – Panagiotis Kanavos Dec 12 '18 at 13:46
  • @PanagiotisKanavos: I just changed cell format from general to text and it worked. And actually when it was general, though I was entering 0002 it was considering it as 2. My bad. Thanks mate! – Amit Dec 12 '18 at 13:53

1 Answers1

1

Make sure the cell is formatted as 'Text' when the cell is formatted as 'General' and begins with a zero the value is considered a number and all leading zeros are scrapped.

Hmax
  • 314
  • 6
  • 16