Writing a collection to Excel

Question

I'm working in VS2005 and I need the C# syntax write to write collection values in an Excel sheet. I initialize the collection like this:

Reports oReports =new Reports();//oReports is a collection

Assuming that this collection has values, how would I write them to Excel?

Answer 1

Loop through the collection and add the collection values to cells in your spreadsheet.

Here's a couple links on how to work with an excel spreadsheet in .Net

http://www.c-sharpcorner.com/UploadFile/thiagu304/ExcelAutomation01052007080910AM/ExcelAutomation.aspx

http://support.microsoft.com/default.aspx?scid=kb;EN-US;302084

Answer 2

Convert your reports to an object array (two dimensional), and assign the array to an excel range

Answer 3

You could do something quick and dirty if you don't want to faff around learning how to write to Excel - just write out to a .csv file, open it yourself in excel and save as Excel format.

Just output in a stream, such as: (will work if your report values have commas in them too)

using (StreamWriter sw = new StreamWriter(@"C:\file.csv"))
{
    StringBuilder csvLine = new StringBuilder();

    //Enter your header row here:
    csvLine.Append("Header1,Header2,Header3");
    sw.WriteLine(csvLine.ToString());

    foreach (Report report in Reports)
    {
        csvLine = new StringBuilder();
        csvLine.Append(string.Format("\"{0}\",", report.Value1));
        csvLine.Append(string.Format("\"{0}\",", report.Value2));
        csvLine.Append(string.Format("\"{0}\"", report.Value3)); //etc
        sw.WriteLine(csvLine.ToString());
    }
    sw.Flush();
}