Stringbuilder skips rows

Question

4.00/5 (1 vote)

See more:

Hi,

I have created a function that exports a data from gridview to an excel spreadsheet. My problem is that it skips row when I export large number of rows. Here is my code, any inputs are greatly appreciated.

C#

private void ToExcel()
        {
            string sep = "";


            //grdExcel.DataBind();
            System.Text.StringBuilder strbldr = new System.Text.StringBuilder();

            for (int i = 0; i < grdExcel.Columns.Count; i++)
            {
                sep = "\t";
                //separting header columns text with comma operator
                strbldr.Append(grdExcel.Columns[i].HeaderText + sep);
            }

            //appending new line for gridview header row
            strbldr.Append("\n");

            for (int j = 0; j < grdExcel.Rows.Count; j++)
            {
                sep = "";
                for (int k = 0; k < grdExcel.Columns.Count; k++)
                {
                    //separating gridview columns with comma
                    if (grdExcel.Rows[j].Cells[k].Text == "&nbsp;" || grdExcel.Rows[j].Cells[k].Text == "")
                    {
                        //InsertFormula(j, k);//insert formula in excel
                        strbldr.Append(sep + InsertFormula(j, k));
                    }
                    else
                    {
                        strbldr.Append(sep + grdExcel.Rows[j].Cells[k].Text);
                    }
                    sep = " ";
                }
                //appending new line for gridview rows
                strbldr.Append("\n");
            }

            Response.Clear();
            Response.ClearContent();
            Response.AddHeader("content-disposition", "attachment; filename=ADS_Detailed_reports_"+System.DateTime.Now.ToString("d-M-yyyy HH:mm:ss").Replace(":", "-")+".xls");
            Response.ContentType = "application/vnd.ms-excel";
            Response.Charset = "";
            Response.Buffer = true;
            grdExcel.AllowPaging = false;
            Response.Write(strbldr.ToString());
            Response.Flush();
            Response.End();

Thanks,

Franco

Posted 18-Sep-13 21:50pm

Franco Cipriano

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Franco Cipriano · Answer 1 · 2013-09-19T02:11:00

Solution 2

Hi,

This is kind of stupid of me, but I was able to get a solution. When I was debugging, i found out that some of the text has a carriage return character ("\r\n") on the end which causes the lines to go down. I just removed them and it's working fine now.

Thanks,

Posted 19-Sep-13 2:11am

Franco Cipriano

superselector · Answer 2 · 2013-09-18T23:28:00

C#

try
  {
      Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
      ExcelApp.Application.Workbooks.Add(Type.Missing);
      int i;
      int ii;
      int j;
      for (i = 1; i < dgvAssetResult.Columns.Count + 1; i++)
      {
          ExcelApp.Cells[1, i] = dgvAssetResult.Columns[i - 1].HeaderText;
      }
      for (ii = 0; ii < dgvAssetResult.Rows.Count; ii++)
      {
          for (j = 0; j < dgvAssetResult.Columns.Count; j++)
          {
              ExcelApp.Cells[ii + 2, j + 1] = dgvAssetResult.Rows[ii].Cells[j].Value.ToString();
          }
      }
      string ls_FileName ="E:\Test.xls";
      ExcelApp.ActiveWorkbook.SaveCopyAs(ls_FileName);
      ExcelApp.ActiveWorkbook.Saved = true;
      ExcelApp.Quit();
  }
  catch
  {

  }

Using this method on button click. Add the proper references

Stringbuilder skips rows

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Stringbuilder skips rows

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us