insert data into Excel sheet

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! :(

Answer 1

User269602965 posted

show the full code for forum user review

Answer 2

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 )
            {
              
            }         
        }
       
    }
}

Answer 3

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

Answer 4

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;
           
            }

        }
    }
}