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;
           }
      }


}

Krzysztof Lach · Accepted Answer · 2017-09-05T19:11:18.397

7

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.

NOPI

NOPI - codeplex source

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

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.

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

SpreadsheetGear site

edited Sep 05 '17 at 19:11

answered Sep 02 '17 at 12:50

Krzysztof Lach

1,280
1
12
19

1

As soon as possible, I will look at it and try it. Thank you very much – Kaan Taha Köken Sep 02 '17 at 12:51
1

I highly recommend EPPlus – Kevin Sep 02 '17 at 13:46
@Kevin yes me too! EPPlus is nice and very easy to use. – Krzysztof Lach Sep 02 '17 at 14:04
thank you very much. I tried it. It is very easy to use. – Kaan Taha Köken Sep 02 '17 at 15:18
3

The MS COM objects have been deprecated for years. You are supposed to use the [Open XML SDK](https://www.nuget.org/packages/DocumentFormat.OpenXml) to work with the modern office file formats. – Scott Chamberlain Sep 02 '17 at 16:24

derloopkat · Answer 2 · 2017-09-02T13:32:30.053

4

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.

edited Sep 02 '17 at 13:32

answered Sep 02 '17 at 13:02

derloopkat

6,232
16
38
45

Can't it be hard if I have lots of data. For example if I had 1,000 Team names, date-time @derloopkat – Kaan Taha Köken Sep 02 '17 at 13:11
In that case you can iterate through rows first and for each row iterate through columns. Otherwise according to some code samples you can pass complex object representing a collection of rows and write them directly to the file with `writer.WriteRecords(rows);` However I haven't tried that. See https://stackoverflow.com/a/44728910/2516718 – derloopkat Sep 02 '17 at 13:31
Okay, I will try that. Thank you very much @derlookat – Kaan Taha Köken Sep 02 '17 at 13:37

Writing and Reading excel files in C#

2 Answers2