Updating excel using ado net
To accomplish this I wrote a bit of code that exports the contents of any ol’ Data Table into an Excel spreadsheet using NPOI.
It’s pretty straightforward, looping through the rows of the Data Table and adding each as a row to the Excel spreadsheet.
This is because in our first piece of code that retrieves data, we use an Ole Db Data Adapter which when the Fill method is called handles the opening and closing of a connection for us.
This is not however the case with an Ole Db Command object so we need to do this work ourselves.
Add a button to your form and call it write Data Button and set its Text property to "Write Data".This has worked well enough over the years, but unfortunately such data cannot be viewed from the i Pad; however, the i Pad can display a native Excel file (.xls).The solution, then, was to update the code to use NPOI to return an actual Excel spreadsheet rather than a CSV file.This tutorial will demonstrate how to use Microsoft Excel as a data store.That is, how to read, write and update data in a Microsoft Excel Worksheet as if it were a database. Well, admittedly, if you are doing any real data processing then you will definitely want to use a proper database, but there are occassions where you already have data in an Excel file that you simply want to read or you may need to store some simple data that you want to pass around making an Excel file a lot easier.