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-05 19:11:18Z

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 5, 2017 at 19:11

answered Sep 2, 2017 at 12:50

Krzysztof Lach

1,3801 gold badge12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kaan Taha Köken Over a year ago

As soon as possible, I will look at it and try it. Thank you very much

Kevin Over a year ago

I highly recommend EPPlus

Krzysztof Lach Over a year ago

@Kevin yes me too! EPPlus is nice and very easy to use.

Kaan Taha Köken Over a year ago

thank you very much. I tried it. It is very easy to use.

Scott Chamberlain Over a year ago

The MS COM objects have been deprecated for years. You are supposed to use the Open XML SDK to work with the modern office file formats.

Daniel Manta · Accepted Answer · 2017-09-02 13:32:30Z

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 2, 2017 at 13:32

answered Sep 2, 2017 at 13:02

Daniel Manta

6,76317 gold badges43 silver badges49 bronze badges

3 Comments

Kaan Taha Köken Over a year ago

Can't it be hard if I have lots of data. For example if I had 1,000 Team names, date-time @derloopkat

Daniel Manta Over a year ago

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 stackoverflow.com/a/44728910/2516718

Kaan Taha Köken Over a year ago

Okay, I will try that. Thank you very much @derlookat

Collectives™ on Stack Overflow

Writing and Reading excel files in C#

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related