Using C# to read/write Excel files (.xls/.xlsx)

Question

How can I read and write Excel files in C#? I've already added the Excel Objects Library to my project, but I'm not getting a clear enough description of what needs to be done to access the files.

Please help me understand and, when explaining, please keep in mind I'm kind of new to this but I'm not a complete newbie. I study a lot, so I'm not totally ignorant.

Answer 1

I'm a big fan of using EPPlus to perform these types of actions. EPPlus is a library you can reference in your project and easily create/modify spreadsheets on a server. I use it for any project that requires an export function.

Here's a nice blog entry that shows how to use the library, though the library itself should come with some samples that explain how to use it.

Third party libraries are a lot easier to use than Microsoft COM objects, in my opinion. I would suggest giving it a try.

Answer 2

I use NPOI for all my Excel needs.

https://github.com/dotnetcore/NPOI

Comes with a solution of examples for many common Excel tasks.

Answer 3

Straightforward yet powerful solution is ClosedXML library. It it extremely simple to read and write from/to cells of a 'closed' Excel file, main logic is as follow:

using Excel = ClosedXML.Excel;

namespace Excel_Tests
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

     private void button1_Click(object sender, EventArgs e)
     {
        //create 'workbook' object
        var wb = new Excel.XLWorkbook("E:\\test1.xlsx");
        
        //create 'worksheet' object
        var ws = wb.Worksheets.Worksheet("Sheet1");

        //read cells
        var a = ws.Cell("A1").Value;
        var b = ws.Cell("B1").Value;

        Console.WriteLine(a.ToString());
        Console.WriteLine(b.ToString());

        //write cells
        ws.Cell("F2").Value = 9999999;
        ws.Cell("F2").Value = 2.333;
        ws.Cell("G2").Value = "This is cell G2";
        
        wb.Save();

    }
  }
}

Answer 4

You can use Excel Automation (it is basically a COM Base stuff) e.g:

Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;


xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open("1.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

Link to the full tutorial

Answer 5

If you want easy to use libraries, you can use the NUGET packages:

• ExcelDataReader - to read Excel files (most file formats)
• SwiftExcel - to write Excel files (.xlsx)

Note these are 3rd-Party packages - you can use them for basic functionality for free, but if you want more features there might be a "pro" version.

They are using a two-dimensional object array (i.e. object[][] cells) to read / write data.

Answer 6

Reading the Excel File:

string filePath = @"d:\MyExcel.xlsx";
Excel.Application xlApp = new Excel.Application();  
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath);  
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);  
  
Excel.Range xlRange = xlWorkSheet.UsedRange;  
int totalRows = xlRange.Rows.Count;  
int totalColumns = xlRange.Columns.Count;  
  
string firstValue, secondValue;   
for (int rowCount = 1; rowCount <= totalRows; rowCount++)  
{  
    firstValue = Convert.ToString((xlRange.Cells[rowCount, 1] as Excel.Range).Text);  
    secondValue = Convert.ToString((xlRange.Cells[rowCount, 2] as Excel.Range).Text);  
    Console.WriteLine(firstValue + "\t" + secondValue);  
}  
xlWorkBook.Close();  
xlApp.Quit(); 

Writting the Excel File:

Excel.Application xlApp = new Excel.Application();
object misValue = System.Reflection.Missing.Value;  
  
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);  
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);  
  
xlWorkSheet.Cells[1, 1] = "ID";  
xlWorkSheet.Cells[1, 2] = "Name";  
xlWorkSheet.Cells[2, 1] = "100";  
xlWorkSheet.Cells[2, 2] = "John";  
xlWorkSheet.Cells[3, 1] = "101";  
xlWorkSheet.Cells[3, 2] = "Herry";  
  
xlWorkBook.SaveAs(filePath, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue,  
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);  
  


xlWorkBook.Close();  
xlApp.Quit();  

Answer 7

If you are doing simple manipulation and can tie yourself to xlsx then you can look into manipulating the XML yourself. I have done it and found it to be faster than grokking the excel libs.

There are also 3rd party libs that can be easier to use... and can be used on the server which MS's can't.