Writing Text data File using C# to excel

Question

Hello,

New at C# using visual studio 2008 and trying to load an excel sheet with a text file. My current program puts the complete file in one cell. Is there a way to put each data point in its own cell. Having issues interfacing with excel to accomplish this task.

Thanks

Joe

Answer 1

't Should be doable to read the Excel Spreadsheet using ADO.NET's DbDataReader. This link should help you greatly: http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx .

Answer 2

Microsoft Excel 11.0 Object Library might help you. You must add a referenct to it in your project and include the namespace:

  using Microsoft.Office.Interop.Excel;

Take a look at http://support.microsoft.com/?scid=kb%3Ben-us%3B302096&x=10&y=10. Theres further information.

Answer 3

One easy way would be to output your text file as a CSV file (comma separated values). Each row is a single line, and each column has commas between them. The Wikipedia link has good examples. CSV files are easily read by Excel.

If you are looking to do more than just get data into Excel and need to format the cells, then using the Excel interop with C# is the way to go as suggested by phimuemue.

Answer 4

Microsoft defined an XML Spreadsheet format that is compatible with Excel 2002 and later. I've used and have been very happy with it. On MSDN there is a gentle introduction and some nitty-gritty documentation.

Here's a brief piece of code illustrating how you would use it:

public void WriteExcelDocument(string filename, string[,] values)
{
    using (var writer = XmlWriter.Create(filename))
    {
        const string ss = "urn:schemas-microsoft-com:office:spreadsheet";

        writer.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");
        writer.WriteStartElement("ss", "Workbook", ss);
        writer.WriteStartElement("Worksheet", ss);
        writer.WriteAttributeString("Name", ss, "Sheet1");
        writer.WriteStartElement("Table", ss);

        for (var i = 0; i < values.GetLength(0); i++)
        {
            writer.WriteStartElement("Row", ss);

            for (var j = 0; j < values.GetLength(1); j++)
            {
                writer.WriteStartElement("Cell", ss);
                writer.WriteStartElement("Data", ss);

                // Valid types are: Number, DateTime, Boolean, String, Error.
                // To keep the example simple, I'm just doing strings.
                writer.WriteAttributeString("Type", ss, "String");

                // If the cell contains a boolean, be sure to write "0" or "1" here, not
                // "false" or "true".  Again, I'm just doing strings, so it doesn't matter.
                writer.WriteString(values[i, j]);

                writer.WriteEndElement();
                writer.WriteEndElement();
            }

            writer.WriteEndElement();
        }

        writer.WriteEndElement();
        writer.WriteEndElement();
        writer.WriteEndElement();
    }
}

Feel free to take this code and use it or modify it as you see fit. I'm releasing it under the MIT license. This is just a starting point; you could modify it to handle different data types, column widths, styling, multiple worksheets, etc.

Answer 5

Heres a nice library that you can use to write excel xml documents as mentioned above. Its actually a very nice wrapper around most of the functionality you need. Also, theres no need for interop libraries which is great if you're doing this in a web app where excel is not installed on the server.

http://www.carlosag.net/Tools/ExcelXmlWriter/