How to Convert Visual FoxPro DBF File to Excel in C#

The very first thing to do is to make sure you've downloaded and installed the Visual FoxPro Driver to your host machine. Here is the download link from Microsoft.com:

Microsoft OLE DB Provider for Visual FoxPro

Use OleDbConnection to get the records into a DataTable and later export/write the DataTable to Excel.

Here is the complete code:

using System;

using System.Data;

using System.Data.OleDb;

using System.IO;

 

namespace Convert_DBF_to_Excel

{

    class Program

    {

        static Missing mv = Missing.Value;

 

        static void Main(string[] args)

        {

            string dbfFileName = @"D:\myData.dbf";

            string constr = "Provider=VFPOLEDB.1;Data Source=" + Directory.GetParent(dbfFileName).FullName;

            string ExcelFileName = AppDomain.CurrentDomain.BaseDirectory + "converted_file.xls";

            using (OleDbConnection con = new OleDbConnection(constr))

            {

                var sql = "select * from " + Path.GetFileName(dbfFileName) + ";";

                OleDbCommand cmd = new OleDbCommand(sql, con);

                DataTable dt = new DataTable();

                try

                {

                    con.Open();

                }

                catch (Exception ex)

                {

                    Console.WriteLine("Error connecting database: " + ex.Message);

                    return;

                }

                if (con.State == ConnectionState.Open)

                {

                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

                    Console.Write("Reading database...  ");

                    da.Fill(dt);

                    Console.WriteLine("Completed.");

                }

                if (con.State == ConnectionState.Open)

                {

                    try

                    {

                        con.Close();

                    }

                    catch { }

                }

 

                if (dt != null && dt.Rows.Count > 0)

                {

                    GenerateExcel(dt, ExcelFileName);

                }

            }

        }

 

        static void GenerateExcel(DataTable sourceDataTable, string ExcelFileName)

        {

            Console.Write("Generating Excel File...");

 

            Excel.Application excelApp = new Excel.Application();

            Excel.Workbook wkb = excelApp.Workbooks.Add(mv);

            Excel.Worksheet wks = wkb.Sheets[1];

 

            for (int i = 0; i < sourceDataTable.Columns.Count; ++i)

            {

                ((Excel.Range)wks.Cells[1, i + 1]).Value = sourceDataTable.Columns[i].ColumnName;

            }

            Excel.Range header = wks.get_Range((object)wks.Cells[1, 1], (object)wks.Cells[1, sourceDataTable.Columns.Count]);

            header.EntireColumn.NumberFormat = "@";

 

            object[,] sourceDataTableObjectArray = new object[sourceDataTable.Rows.Count, sourceDataTable.Columns.Count];

            for (int row = 0; row < sourceDataTable.Rows.Count; ++row)

            {

                for (int col = 0; col < sourceDataTable.Columns.Count; ++col)

                {

                    sourceDataTableObjectArray[row, col] = sourceDataTable.Rows[row][col].ToString();

                }

            }

            ((Excel.Range)wks.get_Range((object)wks.Cells[2, 1], (object)wks.Cells[sourceDataTable.Rows.Count, sourceDataTable.Columns.Count])).Value2 = sourceDataTableObjectArray;

            header.EntireColumn.AutoFit();

            header.Font.Bold = true;

            wks.Application.ActiveWindow.SplitRow = 1;

            wks.Application.ActiveWindow.FreezePanes = true;

            wks.SaveAs(ExcelFileName, Excel.XlFileFormat.xlExcel8, mv, mv, mv, mv, mv, mv, mv, mv);

            wks = null;

            wkb = null;

            excelApp.Quit();

            Console.WriteLine("Completed.");

        }

    }

}