0

I am reading excel sheet using ADO DB in C# but the problem I am facing is it is not able to copy the cell data with its format. For e.g.

One of my cell is in Currency format ($2,123.45) so when I am getting the result set I am getting the cell value as (2123.45).

Is there any way I can get exactly same cell data as it is displayed in excel sheet. Below is my code for getting the result set from excel

        string sQuery = "Select * From [" + sSheet + "$]";

        if (dtType == DataLayer.TestData)
            sWorkbook = Path.GetDirectoryName(ConfigurationManager.AppSettings["resourcesFolder"]) + @"\Data\Test Data\" + sWorkbook;

        if (dtType == DataLayer.ExpectedData)
            sWorkbook = Path.GetDirectoryName(ConfigurationManager.AppSettings["resourcesFolder"]) + @"\Data\Expected Data\" + sWorkbook;

        string cnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sWorkbook + "; Extended Properties= 'Excel 8.0; HDR=Yes; IMEX=1'";
        oConn.Open(cnStr, null, null, 0);
        oRS.Open(sQuery, cnStr, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);

        oConn.Close();

Please do provide some solution to the same.

Note : worst case scenario I can go for other options for reading Excel if my performance is not hampered.

Gerald Versluis
  • 30,492
  • 6
  • 73
  • 100
Piyush Kumar
  • 1
  • 3
  • possible duplicate of [Reading Excel data and cell formatting in C#](http://stackoverflow.com/questions/11188970/reading-excel-data-and-cell-formatting-in-c-sharp) – Gerald Versluis May 06 '15 at 09:44
  • 1
    You can get better performance by using e.g. [EPPlus](http://epplus.codeplex.com/), it's quite easy to read and write excel files. – Peter Schneider May 06 '15 at 09:45
  • I think it's not possible to read the formatted value via Jet OLEDB... you can just read the data, which is apparently 2123.45). The formatting is done at Excel Application level.... You can either use Excel automation or the OpenXML SDK to read the contents and styles... – Peter Schneider May 06 '15 at 09:48
  • @PeterSchneider In EPPlus I don't think (not very sure) we have option to read whole of excel at one go and save it in the memory and then extract data as required. – Piyush Kumar May 06 '15 at 10:10
  • Yes, it is possible... see [here](http://blog.fryhard.com/archive/2010/10/28/reading-xlsx-files-using-c-and-epplus.aspx)... – Peter Schneider May 06 '15 at 10:22
  • EPPlus is not helping too. – Piyush Kumar May 06 '15 at 11:14
  • It might not be that easy, but I really think EPPlus can be a solution for you as you can read the cell value and the cell format value. You then would need to convert the value yourself to the correct format. – Peter Schneider May 08 '15 at 08:56
  • I tried that but didn't get the desired result. If possible could you share a code for the same. – Piyush Kumar May 08 '15 at 09:41

1 Answers1

1

Here might be the problem: Via ConnectionStrings: Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

Peter Schneider
  • 2,879
  • 1
  • 14
  • 17
  • I don't think the issue is with the data type, Piyush is reading the data as a number, the issue is wanting to retrieve the _formatting_ of the number as displayed by Excel. The `IMEX` setting has no effect on that. – Tony May 06 '15 at 10:04
  • So Seems no one has solution to my problem. Please give a solution anyone its important – Piyush Kumar May 08 '15 at 07:03