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

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.

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.

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.

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.

I use NPOI for all my Excel needs.

http://npoi.codeplex.com/

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

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

**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();