Writing and Reading excel files in C#

Question

I am writing a program that takes data from a website via selenium web driver. I am trying to create football fixture for our projects. I am so far, I accomplished to take date and time, team names and scores from the website. Also still trying writing on txt file, but it gets little messy while writing on txt file

How do I accomplish writing on excel file, and reading? I want to write like that

Date-Time     First-Team   Second-Team    Score    Statistics
28/07 19:00   AM           AVB            2-1      Shot 13123 Pass 65465 ...
28/07 20:00   BM           BVB            2-2      Shot 13123 Pass 65465 ...
28/07 20:00   CM           CVB            2-3      Shot 13123 Pass 65465 ...

And this is my part of my code :

StreamWriter file = new StreamWriter(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\Test" + "\\" + pathName + "\\" + subFile + "\\" + pathName + ".txt", false);

for (int k = 2; k > 1; k--)
{ 
     //doing some stuff
}

Writing part:

for (int x = 0; x <dT.Count; x++)
{
     file.Write(dateTime[x] + " " + firstTeam[x] + " "
                       + secondTeam[x] + " " + firstHalf[x] + " " + secondHalf[x] + " ")
     for (int i = 0; i < total_FS.Count(); i++)
     {
           int index = total_FS[i].Length;
           if (total_FS[i][index-1].ToString() != " " && total_FS[i] != "-")
           {
                 file.Write(total_FS[i]);
           }
           else
           {
                 SpaceC++;
                 if (total_FS[i][index - 1].ToString() == " ")
                 file.Write(total_FS[i]);
           }
           if (SpaceC == 9)
           {
                 file.Write("\n");
                 SpaceC = 0;
                 break;
           }
      }


}

Answer 1

There are few cool libraries that you can use to easy read and write excel files. You can reference them in your project and easily create/modify spreadsheets.

EPPlus Very developer friendly and easy to use.

• EPPlus - codeplex source
• Simple tutorial

NOPI

• NOPI - codeplex source

DocumentFormat.OpenXml  It provides strongly typed classes for spreadsheet objects and seems to be fairly easy to work with.

• DocumentFormat.OpenXml site

• DocumentFormat.OpenXml tutorial

Open XML SDK 2.0 for Microsoft Office Provides strongly typed classes / easy to work with.

• Open XML SDK 2.0 - MSDN

ClosedXML - The easy way to OpenXML ClosedXML makes it easier for developers to create (.xlsx, .xlsm, etc) files.

• ClosedXML - repository hosted at GitHub

• ClosedXML - codeplex source

SpreadsheetGear *Paid - library to import / export Excel workbooks in ASP.NET

• SpreadsheetGear site

Answer 2

Instead of creating XLS file, make a CSV text file that can be opened with Excel. Fields are comma separated and each line represents a record.

field11,field12
field21,field22

If a field contains inner commas, it needs to be wrapped in double quotation marks.

"field11(row1,column1)", field12
field21, field22

If a field contains double quotation marks, they need to be escaped. But you can use CsvHelper to do the job. Grab it from Nuget

PM> Install-Package CsvHelper

An example on how to use it.

using(var textWriter = new StreamWriter(@"C:\mypath\myfile.csv")
{
    var writer = new CsvWriter(textWriter);
    writer.Configuration.Delimiter = ",";

    foreach (var item in list)
    {
        csv.WriteField("field11");
        csv.WriteField("field12");
        csv.NextRecord();
    }
}

Full documentation can be found here.