mardi 4 août 2015

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.

Aucun commentaire:

Enregistrer un commentaire