Jarloo

Menu

Excel to CSV

[box type=”warning”]This code is designed for .NET 4 and uses the optional parameters enhancements. If you want to use it in a previous version of .NET you will need to specify the additional missing parameters.[/box] Using the Excel libraries it’s possible to convert an Excel file to a CSV file. The result of this operation would be the same as if you opened the Excel file and selected “Save As” and then picked as CSV format to save it in, except it’s all automated in your code.

Reference the Microsoft Excel Libraries

[box type=”info”]You will need to have Microsoft Excel installed on your development computer for this to work.[/box]

In Visual Studio you need to goto your ProjectReferences. Right-click and choose Add Reference.

In the window that appears select the COM tab, and pick Microsoft Excel and click ok.

When you do this Visual Studio will find the Interop Assemblies for Office and locate the Excel one and use that instead of a direct reference to the COM.

In your references you should now see Microsoft.Office.Interop.Excel.

ExcelHelper C# Class

This class has a single static method called ConvertExcelToCsv, which takes an Excel file and creates a CSV file. One thing to note is that it does NOT delete the original Excel file, that is up to you if you want to do that.

Since Excel files can have multiple sheets, there is the optional parameter of specifying what sheet to write as the CSV file. If you leave it blank it will default to the first one in the workbook.

using System.IO;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace jarloo
{
    public static class ExcelHelper
    {
        public static void ConvertExcelToCsv(string source, string destination, int sheetNumber=1)
        {
            if (File.Exists(destination)) File.Delete(destination);

            Application xl = new Application();

            try
            {
                Workbook workbook = xl.Workbooks.Open(source);
                Worksheet ws = (Worksheet) workbook.Sheets[sheetNumber];
                ws.SaveAs(destination, XlFileFormat.xlCSV);

                Marshal.ReleaseComObject(ws);
            }
            finally
            {
                xl.DisplayAlerts = false;
                xl.Quit();

                Marshal.ReleaseComObject(xl);
            }
        }
    }
}

Example Converting Excel to CSV in C#

All you need to do to convert the file after you’ve added the ExcelHelper class is execute the method like so:

namespace Jarloo
{
    class Program
    {
        static void Main(string[] args)
        {
            ExcelHelper.ConvertExcelToCsv(@"c:tempsample.xlsx",@"c:tempsample.csv");
        }
    }
}

Categories:   Code

Tags:  , , , ,

Comments

Sorry, comments are closed for this item.