Writing and Reading excel files in C#

11,864

Solution 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.

NOPI

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.

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

Solution 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.

Share:
11,864

Related videos on Youtube

Kaan Taha Köken
Author by

Kaan Taha Köken

Updated on September 26, 2022

Comments

  • Kaan Taha Köken
    Kaan Taha Köken over 1 year

    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;
               }
          }
    
    
    }
    
  • Kaan Taha Köken
    Kaan Taha Köken over 6 years
    As soon as possible, I will look at it and try it. Thank you very much
  • Kevin
    Kevin over 6 years
    I highly recommend EPPlus
  • Krzysztof Lach
    Krzysztof Lach over 6 years
    @Kevin yes me too! EPPlus is nice and very easy to use.
  • Kaan Taha Köken
    Kaan Taha Köken over 6 years
    thank you very much. I tried it. It is very easy to use.
  • Scott Chamberlain
    Scott Chamberlain over 6 years
    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.