You must Sign In to post a response.
  • Category: ASP.NET

    Inserting data to Formatted Excel after First row (Column header)

    I am trying to add the grid view rows data to formatted excel, my formatted excel contains 6 column named col1, col2, col3, total1, total2 and total3, I made some calculation on total1,total2 and total3. My grid view contains 8 rows but I am trying to add first three fetched data to Excel, data are inserting perfectly except the data are inserted after some range i.e., non used area, but the calculation are available from row 2,

    Please find my code below.

    protected void Export_Click(object sender, EventArgs e)
    {
    int gridi,j,value1, value2, value3;
    int rowcount = GridView1.Rows.Count;
    int Colcount = 3;
    String[] cellText;

    string path = @"D:\ExcelTemplate\template.xlsx";
    constr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
    constr = string.Format(constr, path);
    using (OleDbConnection excel_con = new OleDbConnection(constr))
    {
    excel_con.Open();
    string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
    DataTable dtExcelData = new DataTable();
    dtExcelData = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    String[] excelSheets = new String[dtExcelData.Rows.Count];
    int i = 0;
    foreach (DataRow row in dtExcelData.Rows)
    {
    excelSheets[i] = row["TABLE_NAME"].ToString();
    i++;

    }
    sheetinfo = excelSheets[2];


    try
    {


    foreach (GridViewRow row in GridView1.Rows)
    {

    int count = 2;
    value1 = Convert.ToInt32(row.Cells[0].Text.Trim());
    value2 = Convert.ToInt32(row.Cells[1].Text.Trim());
    value3 = Convert.ToInt32(row.Cells[2].Text.Trim());
    //string excelquery = "Insert into ["+sheetinfo+"A2:C20000] (Column1,Column2,Column3) values(" + value1 + "," + value2 + "," + value3 + ")";
    string excelquery = "Insert into [sheet1$](A" + count + ",B" + count + ",C" + count + ") values(" + value1 + "," + value2 + "," + value3 + ")";
    using (OleDbDataAdapter oda = new OleDbDataAdapter(excelquery, excel_con))
    {
    oda.Fill(dtExcelData);
    }
    excel_con.Close();
    Label1.Visible = true;
    Label1.Text = "Insert to the Excel [[[Success]]]";
    count += 1;

    }

    }

    catch (Exception ex)
    {
    //Label1.Visible = true;
    //Label1.Text = ex.ToString();

    }
    }
    kindly suggest the solution for my issue.
  • #761417
    Hi Nachiappan ,

    i think your problem is in the foreach loop as per my understanding you are getting the results correctly but after some empty rows you got count.

    if this is not the case please give more details about your requirement.

    Rayala HariKishore

    try..try..try...you achieved it.
    http://rayalaharikishore.wordpress.com/

  • #761574
    for loop working good, but the data are not insert to the existing rows which formula existing rows. Any way thanks I tried to a new solution by finding the each cell and inserted the data.


Sign In to post your comments