locked
insert data into Excel sheet RRS feed

  • Question

  • User-121981121 posted

    hey everybody,

    i'm using this code to insert some values into my Excel table and it is working when i insert them in a simple columns, what i want is to insert these values into columns which are under one title (a number of cells merged together to give one cell for the parent title).

    this the code i use and wich doesn't do what i need:

    sql = "Insert into [Feuil1$] (N,M) values('18','16')";             
                    myCommand.CommandText = sql;
                    myCommand.ExecuteNonQuery();

    i want to insert data in a particular cell, ive tried
    sql = "Insert into [Feuil1$A2:B6]  values('18','16')";    
    but it's not working! :(
    Sunday, September 4, 2011 3:17 PM

Answers

  • User-121981121 posted

    Hello;

    i finally did open excel form ASP.Net page and insert data into a specified cell from a SQL server table.here is the code:

    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Data.SqlClient;
    //using System.Xml.Linq;
    using System.Configuration;
    using System.Data;
    
    namespace xlboucleajout
    {
        public partial class _Default : System.Web.UI.Page
        {
            public string GetConnectionString()
            {
                return System.Configuration.ConfigurationManager.ConnectionStrings["MyConsString"].ConnectionString;
            }
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                // open workbook and add data to cells (woooooooooorksssss)
                Excel.Application excelApp = new Excel.Application();
                excelApp.Visible = true;
                Excel.Workbook newWorkbook = excelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                string workbookPath = @"c:/EXCEL.xls";
                Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                    0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                    true, false, 0, true, false, false);
                //-----jusqu ici il marche, il a ouvert excel------
                Excel.Sheets excelSheets = excelWorkbook.Worksheets;
               string currentSheet = "Sheet1";
               Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
                // ici c'est l'etablissement de la conx avc la base
               SqlConnection con = new SqlConnection(GetConnectionString()); 
               con.Open();
               SqlDataAdapter sda = new SqlDataAdapter("SELECT id FROM MyTable", con);
               DataSet ds = new DataSet();
               sda.Fill(ds);
               //recuperer resultat requete
                int i;
                String resultatreq = ds.Tables[0].Rows[i]["MyTable"].ToString().Trim();
                //ecrire le resultat dans excel 
                Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A"+i, "A"+i);
                excelCell.Value2 = resultatreq;
               
                }
    
            }
        }
    }
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 7, 2011 1:59 PM

All replies

  • User269602965 posted

    show the full code for forum user review

    Monday, September 5, 2011 10:22 AM
  • User-121981121 posted
    Here is it :)

    using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using Microsoft.Office.Interop.Excel; namespace testword { public partial class WebForm2 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } private void MessageBox(string msg) { System.Web.UI.WebControls.Label lbl = new System.Web.UI.WebControls.Label(); lbl.Text = "<script language='javascript'>window.alert('" + msg + "')</script>"; Page.Controls.Add(lbl); } protected void Button1_Click(object sender, EventArgs e) { //inserer des valeurs ds fichier excel try { System.Data.OleDb.OleDbConnection MyConnection = new System.Data.OleDb.OleDbConnection(); System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(); string sql = null; string fileName = @"C:\test.xlsx"; MyConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source= '" + fileName + "';Extended Properties='Excel 8.0;HDR=YES;'"; MyConnection.Open(); myCommand.Connection = MyConnection; sql = "Insert into [Feuil1$] (id,name) values('8','H')"; myCommand.CommandText = sql; myCommand.ExecuteNonQuery(); MessageBox("la commande sql est executée"); MyConnection.Close(); MessageBox("la connexion est fermée"); } catch (Exception ) { } } } }
    Monday, September 5, 2011 11:25 AM
  • User269602965 posted

    Try inserting in a NAMED RANGE that you create in the spreadsheet

    or a specified cell RANGE

    like:

    SQL = "INSERT INTO [Feuil1$D3:E100] VALUES ('18','16')";

    It will insert the two values beginning at Cell D3 below your merged label cells in row 2

    and you can insert up to absolute speadsheet row 100

    Monday, September 5, 2011 11:57 AM
  • User-121981121 posted

    Hello;

    i finally did open excel form ASP.Net page and insert data into a specified cell from a SQL server table.here is the code:

    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Data.SqlClient;
    //using System.Xml.Linq;
    using System.Configuration;
    using System.Data;
    
    namespace xlboucleajout
    {
        public partial class _Default : System.Web.UI.Page
        {
            public string GetConnectionString()
            {
                return System.Configuration.ConfigurationManager.ConnectionStrings["MyConsString"].ConnectionString;
            }
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                // open workbook and add data to cells (woooooooooorksssss)
                Excel.Application excelApp = new Excel.Application();
                excelApp.Visible = true;
                Excel.Workbook newWorkbook = excelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                string workbookPath = @"c:/EXCEL.xls";
                Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                    0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                    true, false, 0, true, false, false);
                //-----jusqu ici il marche, il a ouvert excel------
                Excel.Sheets excelSheets = excelWorkbook.Worksheets;
               string currentSheet = "Sheet1";
               Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
                // ici c'est l'etablissement de la conx avc la base
               SqlConnection con = new SqlConnection(GetConnectionString()); 
               con.Open();
               SqlDataAdapter sda = new SqlDataAdapter("SELECT id FROM MyTable", con);
               DataSet ds = new DataSet();
               sda.Fill(ds);
               //recuperer resultat requete
                int i;
                String resultatreq = ds.Tables[0].Rows[i]["MyTable"].ToString().Trim();
                //ecrire le resultat dans excel 
                Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A"+i, "A"+i);
                excelCell.Value2 = resultatreq;
               
                }
    
            }
        }
    }
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 7, 2011 1:59 PM