none
OLEDB to Excel

    Question

  • I want to perform an "INSERT INTO" command on an Excel spreadsheet.  I want the "FROM [MyTable]" to be a DataTable dt that I have passed to the method.  How do I reference dt as the table that I am getting the data from?  the code that follows is what I have so far and it opens a connection to the Excel worksheet but does not know what [MyTable] is. 

     

       public void WriteTableToExcel(DataTable dt)
            {
                string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\PCDIFolder\\ModbusMapExcel.xlsx;Extended Properties='Excel 12.0;IMEX=1;HDR=YES;'";

                OleDbConnection connectionToDatabase = new OleDbConnection(connString);

                connectionToDatabase.Open();

                DataTable MyTable = dt;

                try
                {

                    string nonQuery = "INSERT INTO [Sheet1$] IN 'c:\\PCDIFolder\\ModbusMapExcel.xlsx' 'Excel 12.0;' SELECT * FROM [MyTable]";
                   
                     //set up first query
                    OleDbCommand selectCommand = new OleDbCommand(nonQuery);
                    selectCommand.Connection = connectionToDatabase;
                    selectCommand.CommandTimeout = 60;

                    if (nonQuery != "")
                    {

                        selectCommand.ExecuteNonQuery();
                        CurrentMessage = "SUCCESS - Excel Populated";
                        UpdateComFromMessages(false, true);
                    }
                }

                catch (Exception ex)
                {
                    CurrentMessage = "ERROR " + ex.Message;
                    UpdateComFromMessages(false, true);

                }


            }

    • Moved by Andrew.Wu Friday, October 14, 2011 6:16 AM (From:Visual C# General)
    Thursday, October 13, 2011 5:05 PM

Answers

  • from KB - 316934  --this is an example for using VB and select strings are commands to the OLEDBDataAdapter object which would act the same in either library (I would assume).

     

    • Parameterized INSERT and UPDATE commands are required because the OleDbDataAdapter does not supply key/index information for Excel workbooks; without key/index fields, the CommandBuilder cannot automatically generate the commands for you.
    • Export data from another data source into an Excel workbook provided that the other data source can be used with the Jet OLE DB Provider. Data sources that you can use with the Jet OLE DB Provider in this manner include Text files, Microsoft Access databases, and, of course, other Excel workbooks. With a single INSERT INTO command, you can export data from another table/query into your workbook:
      INSERT INTO [Sheet1$] IN 'C:\Book1.xls' 'Excel 8.0;' SELECT * FROM MyTable"
      INSERT INTO requires that the target table (or worksheet) already exist; data is appended to the target table.

      You may also use SELECT..INTO to export your table/query to a workbook:
      SELECT * INTO [Excel 8.0;Database=C:\Book1.xls].[Sheet1] FROM [MyTable]
      When you use SELECT..INTO, if the target table or workbook does not exist, it will be created for you. If the table already exists before the SELECT..INTO command is issued, you will receive an error.
    • Proposed as answer by YosrJ Thursday, October 13, 2011 8:46 PM
    • Marked as answer by Liliane Teng Monday, October 24, 2011 9:41 AM
    Thursday, October 13, 2011 8:12 PM

All replies

  • You are mixing things.

    There is no Insert and Select in one query. Or select, or insert.

    SELECT: "SELECT Column1, Column2 FROM myTable"

    INSERT: "INSERT INTO MyTable (Column1, Column2) VALUES (@param1, @param2)"

     

    What do you wanna do, insert, or select now? You can do both, but seperated (seperated commands).

     


    Mitja
    Thursday, October 13, 2011 6:29 PM
  • hi

     

    As i understood,you want to insert data in excel sheet with data provided from datatable (mytable)

    you can loop throw the datatable and loop throw excel sheet cells to insert in values

    I dont know from where you extract your data( sql server or other provider) so you can customize it to your purpose

    and for sure it dont recognize mytable because you pass by value a datable already filled (you dont pass the query)

    //get data from database
    string strQuery = "Select * FROM myTable";
    	SqlCommand cmd = new SqlCommand(strQuery);
    DataTable dt = new DataTable();
    
    	SqlConnection con = new SqlConnection(strConnString);
    	SqlDataAdapter sda = new SqlDataAdapter();
    	cmd.CommandType = CommandType.Text;
    	con.Open();
    	cmd.Connection = con;
    	sda.SelectCommand = cmd;
    	sda.Fill(dt);
    // insert data in your sheet excel
    int nbrows = 1; 
    foreach (DataRow dr in dt.Rows) 
    { 
        nbrows += 1; 
        for (int i = 1; i < dt.Columns.Count+1; i++) 
        { 
          
            if (nbrows==2) 
            { 
                ursheet.Cells[1, i] = dt.Columns[i - 1].ColumnName; 
            } 
            ursheet.Cells[nbrows, i] = dr[i - 1].ToString(); 
        } 
    }
    

    hope it helps

     


    Best Regards...Please mark as answer if my post is helpful
    • Edited by YosrJ Thursday, October 13, 2011 7:53 PM
    Thursday, October 13, 2011 7:49 PM
  • from KB - 316934  --this is an example for using VB and select strings are commands to the OLEDBDataAdapter object which would act the same in either library (I would assume).

     

    • Parameterized INSERT and UPDATE commands are required because the OleDbDataAdapter does not supply key/index information for Excel workbooks; without key/index fields, the CommandBuilder cannot automatically generate the commands for you.
    • Export data from another data source into an Excel workbook provided that the other data source can be used with the Jet OLE DB Provider. Data sources that you can use with the Jet OLE DB Provider in this manner include Text files, Microsoft Access databases, and, of course, other Excel workbooks. With a single INSERT INTO command, you can export data from another table/query into your workbook:
      INSERT INTO [Sheet1$] IN 'C:\Book1.xls' 'Excel 8.0;' SELECT * FROM MyTable"
      INSERT INTO requires that the target table (or worksheet) already exist; data is appended to the target table.

      You may also use SELECT..INTO to export your table/query to a workbook:
      SELECT * INTO [Excel 8.0;Database=C:\Book1.xls].[Sheet1] FROM [MyTable]
      When you use SELECT..INTO, if the target table or workbook does not exist, it will be created for you. If the table already exists before the SELECT..INTO command is issued, you will receive an error.
    • Proposed as answer by YosrJ Thursday, October 13, 2011 8:46 PM
    • Marked as answer by Liliane Teng Monday, October 24, 2011 9:41 AM
    Thursday, October 13, 2011 8:12 PM
  • I can do row by row yes, but it is not efficient for my project.  - thanks though.
    Thursday, October 13, 2011 8:13 PM