C# and excel: Reading excel files into MS SqlServer.

Question

I am a beginner in C#.
I need examplecode of how to read excel files into c#, and then how to use a dataset to store the excel columns into the database wich is an MS Sql 2000 db.

Any suggestions.

Thanks!

Francois Steyn · Answer

Hi! When I try to compile it gives me an error: the type or namespace 'Office' does not exist in the namespace 'Microsoft'. How can I fix this?

Craig Murphy · Answer

Here's a small Excel class that will help you get started with reading an Excel file and working with it. using System; using System.Runtime.InteropServices; using Excel = Microsoft.Office.Interop.Excel; namespace cmExcelEngine { public class ExcelEngine { private System. Object missingValue = System.Reflection. Missing .Value; private const int cA = 1; // thru cZ = 26; as required private Excel.Application excelApp = null ; private Excel.Workbook excelWorkbook = null ; private Excel.Sheets excelSheets = null ; private Excel.Worksheet excelWorksheet = null ; private Excel.Workbooks excelWorkbooks = null ; public ExcelEngine() { string cd = System. Environment .CurrentDirectory; excelApp = new Excel.ApplicationClass(); // Open an Excel template, create a new "document" excelApp.Workbooks.Add(cd + "\template.xlt" ); // or load a file of your choosing //excelApp.Workbooks.Open(cd + "\template.xlt", false, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "	", // true, false, null, false, null, false); } public void Show() { excelApp.Visible = true ; } public Excel.Worksheet SelectSheet( string sheetName) { Excel.Worksheet returnSheet = null ; foreach (Excel.Worksheet workSheet in excelWorkbook.Worksheets) { if (workSheet.Name == sheetName) returnSheet = workSheet; } return returnSheet; } // Read/Write to/from the Excel sheet... public void PopulateSummary; { excelWorksheet.Cells[17, cC] = 1000; } public void Quit() { excelWorkbook.Close( false , missingValue, missingValue); excelWorkbooks.Close(); excelApp.Quit(); Marshal .ReleaseComObject(excelWorksheet); Marshal .ReleaseComObject(excelSheets); Marshal .ReleaseComObject(excelWorkbooks); Marshal .ReleaseComObject(excelWorkbook); Marshal .ReleaseComObject(excelApp); excelWorksheet = null ; excelSheets = null ; excelWorkbooks = null ; excelWorkbook = null ; excelApp = null ; GC .GetTotalMemory( false ); GC .Collect(); GC .WaitForPendingFinalizers(); GC .Collect(); GC .GetTotalMemory( true ); } } } It is invoked like this: ExcelEngine ee = new ExcelEngine(); ee.PopulateSummary; ee.Show(); This example demonstrates how to open an Excel spreadsheet and populate it - but reading from the spreadsheet uses the same code: SummaryCost = excelWorksheet.Cells[17, cC]; Once you have got the Excel aspect working, post back here with some code/data examples...may be somebody will be able to help with your database question then... HTH

li · Answer

??????

davidh1 · Answer

Try this: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2005_ta/html/OfficeVSTOExcelObj.asp The Office Tools for visual studio should give you what you need. I haven't used them myself yet, though. Dave

C# and excel: Reading excel files into MS SqlServer.

Answers (4)