How To Convert Visual FoxPro DBF File To Excel In C#

Introduction

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.

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.");
        }
    }
}