如何使用OLEDB将DataTable复制到Excel文件？ [英] How to copy DataTable to Excel File using OLEDB?

问题描述

我正在编写一个程序，使用oledb将数据发送到excel。我使用下一个更新语句：

 OleDbConnection MyConnection =  new  OleDbConnection（ @  provider = Microsoft.Jet.OLEDB.4.0; Data Source =' + GeneralData.excelPath +  ';扩展属性= Excel 8.0;）
 MyConnection.Open（）; 
 OleDbCommand myCommand =  new  OleDbCommand（）; 
 myCommand.Connection = MyConnection; 
 myCommand.CommandType = System.Data.CommandType.Text; 
  string  sql =  更新[test $ ] set press = + pointsProperties [i] .Pressure +  ，temp = + pointsProperties [i] .Temperature +  其中id = + id; 
 myCommand.CommandText = sql; 
 myCommand.ExecuteNonQuery（）; 

问题是我将使用sql语句超过100次，这需要花费很多时间，所以我认为使用Data表格会花费更少的时间，所以我写了一个代码，将数据保存在数据表中，如下：

  public   static  System.Data.DataTable ExcelDataTable =  new  System.Data.DataTable（  Steam属性）; 
 
  static  System.Data.DataColumn columnID =  new  System.Data.DataColumn （  ID，System.Type.GetType（   System.Int32））; 
  static  System.Data.DataColumn columnPress =  new  System.Data.DataColumn（ 按，System.Type.GetType（  System.Int32））; 
  static  System.Data.DataColumn columnTemp =  new  System.Data.DataColumn（  Temp，System.Type.GetType（  System.Int32））; 
 
  public   static   void  IntializeDataTable（） //  在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（）;  //  必须在函数内删除 
  //  如果在函数外部删除它将引发异常 
 ExcelDataRow [  ID] = Point.ID; 
 ExcelDataRow [ 按] = Point.Pressure; 
 ExcelDataRow [  Temp] = Point.Temperature; 
 
 ExcelDataTable.Rows.Add（ExcelDataRow）; 
} 

问题是我不知道：



1-是第二个方式更快？



2-如何将数据表复制到excel文件？



谢谢。

解决方案

set press = + pointsProperties [i] .Pressure + < span class =code-string>，temp = + pointsProperties [i] .Temperature + 其中id = + id;
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery（）;

问题是我将使用sql语句超过100次，这需要花费很多时间，所以我认为使用数据表将花费更少的时间，所以我写了一个代码将数据保存在数据表中，如下：

< pre lang =c＃> public static System.Data.DataTable ExcelDataTable = 新 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（ 按，System.Type.GetType（ System.Int32））;
static System.Data.DataColumn columnTemp = new System.Data.DataColumn（ Temp，System.Type.GetType（ System.Int32））;

public static void IntializeDataTable（） // 在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（）; // 必须在函数内删除
// 如果在函数外部删除它将引发异常
ExcelDataRow [ ID] = Point.ID;
ExcelDataRow [ 按] = Point.Pressure;
ExcelDataRow [ Temp] = Point.Temperature;

ExcelDataTable.Rows.Add（ExcelDataRow）;
}

问题是我不知道：



1-是第二个方式更快？



2-如何将数据表复制到excel文件？



谢谢

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.

解决方案

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.

这篇关于如何使用OLEDB将DataTable复制到Excel文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！