working with excel
-
http://www.c-sharpcorner.com/winforms/ExcelReadMG.asp[^] _____________________ Proud to be Albanian _____________________
-
Errors and Excel, I recently have resolved a major pain when using OLE DB. If you are using the Excel Object Model then ignore this. Basically I was glad to finally discover some info buried on MSDN that helped me resovle reading (importing) data from Excel via OLE DB. Two things: In your Connection String Extended Options there is a setting called IMEX. It can have one of the following values:
0 = Export 1 = Import 2 = UpdateThus, let's say you want to read an Excel file that has a few questionable cells (i.e. IMEX=1). Well without some further intervenion in the Registry, OLE DB still may not fetch the records properly because it uses the first 8 cells in the column to guess the data type. Easy to fix but you have to know about this to make it work. Nip into REGEDIT and goto to:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ExcelThen you have to tweak the value of a setting called no less thanTypeGuessRows. You can choose a value that makes sense in your context, say it might take 100 cells to determine the type in your estimation. Then here it's definitely OK to try 100 as the value. This is an execise in tuning verus trial and error. If you have a humongous spreadsheet, you can use 0 which causes the Type Guess to scan up to 16,000 rows. That's a tad excessive for most apps, so like I said trial and error. BUT Wait there's more.... when you finally go to deploy your application MAKE SURE your MSI updates the Registry! Mike Luster CTI/IVR/Telephony SME