Example of Reading Data and Writing Directly Into Excel

Question

I currently have a routine which reads rows/columns and writes into a SQL table from a SELECT statement.

We were thinking of then reading the SQL table and updating an empty Excel worksheet. What I would like to do is read and then update the data directly into Excel.

My program currently does the following:

Opening the workbook.
Accessing the different worksheets.
Saving As a new workbook name.
Closing Excel.

I just need an example that:

will allow the first row to be the column names
then read out the data into the various cells for each row.

http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp — granadaCoder, Jul 14 '14 at 14:30
I would strongly recommend NOT using the excel com-interop assemblies. They will haunt you. — granadaCoder, Jul 14 '14 at 14:30
There are many tools to achieve what you want what was previously suggested by granadaCoder is one option, don't expect to get any code here unless you have shown your efforts towards your goal. — Prix, Jul 14 '14 at 14:33
@granadaCoder unless you're writing an Add-in. Then you have no choice :( — Pete Garafano, Jul 14 '14 at 15:34

score 0 · Answer 1 · answered Jul 15 '14 at 08:22

0

I do not recommend using COM Interop either.

You can find here a sample about exporting dataset to Excel in C#.

It requires an Excel tool named EasyXLS.

answered Jul 15 '14 at 08:22

alex.pulver

2,107
2
31
31

score 0 · Answer 2 · answered Jul 15 '14 at 08:29

to open the file you can do it like that:

Dim exAppl As New Excel.Application    
exAppl.Visible = True
Dim exMappe As Excel.Workbook = exAppl.Workbooks.Open("C:\... .xlsx", , False)

You can acces to a Cell with

Console.WriteLine(exAppl.Range("A2").Value())

or when you whant to go throw it use something like that:

For i As Integer = 1 To exAppl.Rows.Count

    Dim a As Excel.Range = exAppl.Rows(i)

    For j As Integer = 1 To a.Columns.Count
        Dim b As Excel.Range = a.Columns(j)
        Console.WriteLine(b.Value)
    Next
Next

sorry that was vb.net, you can convert it easy using http://converter.telerik.com/ — deru, Jul 15 '14 at 08:34

Example of Reading Data and Writing Directly Into Excel

2 Answers2