Hi Im converting DataTable To Excel Format using oledb im using following code
private void btnexcel_Click(object sender, EventArgs e)
{
Dictionary<String, String> schema = dac.DatasourceSchema(tablename); //data tables Columns Headers And Data types
string[] values = schema.Values.ToArray(); // column Headers
string[] keys = schema.Keys.ToArray(); // datatypes
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
if (saveFileDialog.FileName != null)
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
try
{
string pathOfFileToCreate = saveFileDialog.FileName.ToString();
conn.ConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES"";", pathOfFileToCreate);
conn.Open();
var cmd = conn.CreateCommand();
//should create below command with schema key value pairs
cmd.CommandText = "CREATE TABLE sheet1 (ID INTEGER,NAME NVARCHAR(100))"; /
cmd.ExecuteNonQuery();
for (int i = 0; i < 1000; i++) // Sample Data Insert
{
cmd.CommandText = String.Format("INSERT INTO sheet1 (ID,NAME) VALUES({0},'{1}')", i, "Name" + i.ToString());
cmd.ExecuteNonQuery(); // Execute insert query against excel file.
}
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
}
}
}
i want to insert my Dictinary key value pairs in below command
cmd.CommandText = "CREATE TABLE sheet1 (ID INTEGER,NAME NVARCHAR(100))"; /
cmd.ExecuteNonQuery();