How can I create a table readable in excel? C#

Question

I have some data in c#, and I want to put this data into Excel. I figure the easiest way is to export it as XML since Excel reads xml files.

What's the easiest way to do this? I don't know what format the xml needs to be in for Excel to read it. I want a simple table of data (headers, data, and totals, although the totals I can simply generate in c# rather than have excel do it).

There might also be a better way without using xml, but simplest is best and definitely no 3rd party stuff...

Ideas?

Answer 1

The easiest way is to dump data into a CSV format, and open it with Excel!

This can take 10 lines of code in Python.

I am sure you can figure this out:

http://en.wikipedia.org/wiki/Comma-separated_values

Answer 2

This article explains how to open an manipulate XML data in Excel 2003, and this video explains the same for 2007.

Or, as lpthnc has suggested, you can export the data as CSV which Excel has no problem handling.

Answer 3

Write a CSV reader writer if you want a good compromise between human and machine readable in a Windows environment

Loads into Excel too.

There's a discussion about it here:

http://knab.ws/blog/index.php?/archives/3-CSV-file-parser-and-writer-in-C-Part-1.html

That is a C# article not a C one.

Answer 4

There's a nice intro at http://www.codeproject.com/KB/office/excelxmlspreadsheet.aspx that should get you started writing basic XML spreadsheets.

You may want to reconsider using 3rd party tools; there are some nice ones out there with good licenses that will make your life a lot easier.

I've used http://www.carlosag.net/Tools/ExcelXmlWriter/ previously to write simple data to an Excel spreadsheet. It outputs right into the Excel XML format you mention.

I've also had success using Apache POI HSSF, though not its most recent incarnation (it lacked Office 2007 support at the time). You can use IKVM to recompile the Java library into a .NET library, and you're good to go.

Answer 5

An alternate approach if your needs are basic: you can put it into HTML with a table representing your spreadsheet and just name your html file .xls. That way you don't need to worry about what a comma will do to you and can provide a basic level of formatting as well.

Answer 6

I have had to do similar things in the past. The easiest way I have found to do it is to export directly from my application to excel:

Microsoft.Office.Interop.Excel

Populate a datatable, datagrid, dataset, etc. and iteerate through the results and output to excel. The exmaple below is in VB but can be easily altered for your purposes and uses a dataset:

Public Sub ExcelExport(ByVal objDataSet As System.Data.DataSet)

        Try
            Dim l_ofd As New SaveFileDialog

            l_ofd.Filter = "Microsoft 2007 Excel File (*.xls)|*.xls"

            If (l_ofd.ShowDialog = System.Windows.Forms.DialogResult.OK) Then
                If (objDataSet.Tables.Count > 0) Then

                    Dim myExcel As ApplicationClass = New ApplicationClass()

                    myExcel.Visible = _ShowWorkBook

                    Dim wb1 As Workbook = myExcel.Workbooks.Add("")

                    Dim ws1 As Worksheet = CType(wb1.Worksheets.Add, Microsoft.Office.Interop.Excel.Worksheet)

                    ws1.Name = _WorkSheetName

                    For indx As Integer = 0 To objDataSet.Tables(_DataSetName).Columns.Count - 1
                        ws1.Cells(1, indx + 1) = objDataSet.Tables(_DataSetName).Columns.Item(indx).ToString
                    Next

                    Dim rowID As Integer = 2
                    Dim dr As DataRow
                    For Each dr In objDataSet.Tables(_DataSetName).Rows
                        Dim colID As Integer = 1
                        Dim data As Object
                        For Each data In dr.ItemArray
                            ws1.Cells(rowID, colID) = data.ToString()
                            colID += 1
                        Next
                        rowID += 1
                    Next

                    Dim fileName As String = l_ofd.FileName

                    wb1.SaveAs(fileName)
                    wb1.Close()

                    myExcel.Workbooks.Close()
                    myExcel.Quit()

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(ws1)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(wb1)

                    ws1 = Nothing
                    wb1 = Nothing
                    myExcel = Nothing
                Else
                    MessageBox.Show("No data present to export")
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        End Try

    End Sub

Answer 7

if you have a DataTable, I just wrote some code to transfer it to Excel. First you need an Excel reference.

        //Just a test DataTable
        System.Data.DataTable table = new System.Data.DataTable();
        table.Columns.AddRange(new DataColumn[] { new DataColumn("Col1"),new DataColumn("Col2") });
        table.Rows.Add("row1col1", "row1col2");
        table.Rows.Add("row2col1", "row2col2");

        //create Excel Application
        Microsoft.Office.Interop.Excel.ApplicationClass s = new ApplicationClass();
        Workbook w = s.Workbooks.Add(""); //create new Workbook
        Worksheet ws = w.ActiveSheet as Worksheet;  //get active sheet.

        //COPY COLUMN HEADERS
        int startCol = 1;
        int startRow = 1;
        foreach (DataColumn col in table.Columns)
        {
            //copy columns in the first row
            ((Range)ws.Cells[startRow, startCol]).Value2 = col.ColumnName;
            startCol++;
        }

        //COPY ROWS
        startRow = 2; //start 2nd row
        foreach (DataRow row in table.Rows)
        {
            startCol = 1;
            foreach (DataColumn col in table.Columns)
            {
                ((Range)ws.Cells[startRow, startCol]).Value2 = row[col].ToString();
                startCol++;
            }
            startRow++;
        }

        //start EXCEL
        s.Visible = true;

Answer 8

Here's an article that presents another approach using the OpenXML SDK 2.0:

http://openxmldeveloper.org/articles/7937.aspx

It demonstrates the use of ExtremeML - a 100% managed code extension library that greatly simplifies the programmatic creation of Excel content from .NET languages. The tutorial series covers a broad range of scenarios.

Disclaimer: I am the creator of ExtremeML.