how to write a dictionary to excel file using OLEDB in c#

Question

I have to write a dictionary (Key, value pair) to an excel file. I have tried using Interop.Excel and had written like this :

try
{
    string file =@”D:\Someexcel.xls”
    Interop.Excel.Application excelApp = new Interop.Excel.Application();
    excelApp.Visible = true;
    Interop.Excel.Workbook wb = excelApp.Workbooks.Open(file, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing);
    Interop.Excel.Worksheet ws = wb.ActiveSheet as Interop.Excel.Worksheet;
    Interop.Excel.Range rng = ws.get_Range("A1", Type.Missing);

    //Write Key, Value as header
    ws.Cells[1, 1] = "Key";
    ws.Cells[1, 2] = “Value”;

    int row = 2; // Initialize for keys.
    foreach (string key in dict.Keys)
    {
        int column = 1; // Initialize for values in key.
        ws.Cells[row, column] = key;
        column++;
        ws.Cells[row, column] = dict[key];
        row++;
    }

    if (ws != null)
        Marshal.ReleaseComObject(ws);
    if (wb != null)
        Marshal.ReleaseComObject(wb);
    if (excelApp != null)
        Marshal.ReleaseComObject(excelApp);
    DialogResult result = MessageBox.Show(string.Format("Excel file created , you can find the file @" + file));
    if (result == DialogResult.OK)
        this.Close();
}
catch (Exception err)
{
    string msg;
    msg = "Error:";
    msg = string.Concat(msg, err.Message);
    msg = string.Concat(msg, "Line:");
    msg = string.Concat(msg, err.Source);
    MessageBox.Show(msg);
}

But, This code seems to break more often then not saying that the excel file is corrupted on the server location. So, I would like to try out with OLEDB. I am new to OLEDB. Searched everywhere on Internet but no info how to write a dictionary. Could you help me out with Insert commands?

Answer 1

Check this example for writing into excel.
http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB
OLEDB is similar to your ADO.NET SqlDataAdapter wherey ou can specify your INSERT and UPDATE commands and use a DataTable to insert or update. For the dictionary you would do something similar make Key,Value as your DataColumns and add the entries as rows.
Havent' checked but you might be also be able to execute OledbCommand as INSERT and UPDATE and execute as non query. Hope this helps.