Excel Workbook to XML or Database

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace ReadExcelFile
{
class Program
{
static void Main(string[] args)
{
OleDbConnection connExcelFile = new OleDbConnection();
DataTable dt = null;
DataTable tblFileTables;
string currentTbl = string.Empty;
int tblCount = 0;
connExcelFile.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=fileName.xls;Extended Properties=\"EXCEL 8.0;HDR=NO\";";
try
{
connExcelFile.Open();
dt = connExcelFile.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
tblFileTables = connExcelFile.GetSchema("Tables");
tblCount = tblFileTables.Rows.Count;
if (!(dt == null))
{
foreach (DataRow dr in dt.Rows)
{
currentTbl = dr["TABLE_NAME"].ToString();
if (string.IsNullOrEmpty(currentTbl))
{
continue;
}
currentTbl.Replace("'", "");
if (currentTbl.EndsWith("$'") || currentTbl.EndsWith("$"))
{
string testString = "Select * FROM [" + currentTbl + "]";
OleDbCommand cmd = new OleDbCommand(testString, connExcelFile);
try
{
//// If the query can not execute due to sheet error igore the sheet.
//// Else add to sheet list.
OleDbDataAdapter tblAdpt = new OleDbDataAdapter(cmd);
tblAdpt.Fill(tblFileTables);
tblFileTables.WriteXml(".\\" + currentTbl + ".xml");
break;
}
catch (InvalidOperationException e)
{
throw e;
}
catch (OleDbException e)
{
throw e;
// If sheet invalid ignore sheet.
}
}

}
}


}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
}
}

No comments:

Post a Comment