How to convert Visual FoxPro DBF file to Excel in C#

The very first thing as you go start doing this stuff, make sure you’ve downloaded and installed Visual FoxPro Driver on your host machine. Here the link for download:
http://www.microsoft.com/en-us/download/confirmation.aspx?id=14839

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

Here are the complete codes:

using System;
using System.Data;
using System.Data.OleDb;
using System.IO; 

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

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_" + DateTime.Now.ToString("yyyy-MM-dd") +"_"+DateTime.Now.Ticks.ToString() +".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.");
        }
   } }


(72)

Leave a Reply

Your email address will not be published. Required fields are marked *