How to copy DataTable to Excel File using OLEDB?

Question

I am writing a program send data to excel by using oledb. I used Update statement like next:

OleDbConnection MyConnection = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + GeneralData.excelPath + "';Extended Properties=Excel 8.0;")
MyConnection.Open();
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = MyConnection;
myCommand.CommandType = System.Data.CommandType.Text;
 string sql = "Update [test$] set press = " + pointsProperties[i].Pressure + ", temp = " + pointsProperties[i].Temperature + " where id= " + id;
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();

The problem is I will use the sql statement more than 100 times, which takes much time, so I thought that using Data Table will take less time, so I wrote a code saving my data in data table like next:

public static System.Data.DataTable ExcelDataTable = new System.Data.DataTable("Steam Properties");

static System.Data.DataColumn columnID = new System.Data.DataColumn("ID", System.Type.GetType("System.Int32"));
static System.Data.DataColumn columnPress = new System.Data.DataColumn("Press", System.Type.GetType("System.Int32"));
static System.Data.DataColumn columnTemp = new System.Data.DataColumn("Temp", System.Type.GetType("System.Int32"));

public static void IntializeDataTable() // Called one time in MDIParent1.Load()
        {
            columnID.DefaultValue = 0;
            columnPress.DefaultValue = 0;
            columnTemp.DefaultValue = 0;

            ExcelDataTable.Columns.Add(columnID);
            ExcelDataTable.Columns.Add(columnPress);
            ExcelDataTable.Columns.Add(columnTemp);
        }

public static void setPointInDataTable(StreamProperties Point)
        {
            System.Data.DataRow ExcelDataRow = ExcelDataTable.NewRow(); // Must be decleared inside the function
                                                                        // It will raise exception if decleared outside the function
            ExcelDataRow["ID"] = Point.ID;
            ExcelDataRow["Press"] = Point.Pressure;
            ExcelDataRow["Temp"] = Point.Temperature;

            ExcelDataTable.Rows.Add(ExcelDataRow);
        }

The problem is I don’t know :

1- Is the second way is faster?

2- How to copy the Data Table to the excel file?

Thanks.

Jeremy Thompson · Answer 1 · 2013-06-03T09:55:05.623

//Dump the datatable onto the sheet in one operation
public void InsertDataTableIntoExcel(Application xlApp, DataTable dt, Reectangle QueryDataArea)
{   
    TurnOnOffApplicationSettings(false);
    using (var rn = xlApp.Range[ColumnNumberToName(QueryDataArea.X) + QueryDataArea.Y + ":" + ColumnNumberToName(QueryDataArea.X + QueryDataArea.Width - 1) + (QueryDataArea.Y + QueryDataArea.Height)].WithComCleanup())
    {
        rn.Resource.Value2 = Populate2DArray(dt);
    }
    TurnOnOffApplicationSettings(true);
}

private object[,] Populate2DArray(DataTable dt)
{
    object[,] values = (object[,])Array.CreateInstance(typeof(object), new int[2] { dt.Rows.Count + 1, dt.Columns.Count + 1}, new int[2] { 1, 1 });

    for (int i = 0; i < dt.Rows.Count; i++)
    {
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            values[i + 1, j + 1] = dt.Rows[i][j] == DBNull.Value ? "" : dt.Rows[i][j];
        }
    }
    return values;
}

public static string ColumnNumberToName(Int32 columnNumber)
{
    Int32 dividend = columnNumber;
    String columnName = String.Empty;
    Int32 modulo;    
    while (dividend > 0)
    {
        modulo = (dividend - 1)%26;
        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
        dividend = (Int32) ((dividend - modulo)/26);
    }    
    return columnName;
}

public static Int32 ColumnNameToNumber(String columnName)
{
    if (String.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");    
    char[] characters = columnName.ToUpperInvariant().ToCharArray();    
    Int32 sum = 0;    
    for (Int32 i = 0; i < characters.Length; i++)
    {
        sum *= 26;
        sum += (characters[i] - 'A' + 1);
    }    
    return sum;
}

private static XlCalculation xlCalculation = XlCalculation.xlCalculationAutomatic;
public void TurnOnOffApplicationSettings(Excel.Application xlApp, bool on)
{
    xlApp.ScreenUpdating = on;
    xlApp.DisplayAlerts = on;
    if (on)
    {       
        xlApp.Calculation = xlCalculation;
    }
    else
    {
        xlCalculation = xlApp.Calculation;
        xlApp.Calculation = XlCalculation.xlCalculationManual;
    }
    xlApp.UserControl = on;
    xlApp.EnableEvents = on;
}

WithComCleanup() is the VSTO Conrtib Libraries.

How to copy DataTable to Excel File using OLEDB?

1 Answers1