locked
Check Box is blank when exporting to excel using Excel Interop RRS feed

  • Question

  • User-223648687 posted

    Hi,

    I have spent few hours trying to figure out why my code doesnt work, and gues what :) still no luck.

    My Code works with StringWriter, but now I have to export excel document to multiple Excel Sheets. I have done this bit, but for some reason Checkboxes in Excel are blank.

    I have prepared GridView for export by replacing all Checkboxes to True or False, so its Text, but when I import Grid View cells to Excel its "". It changes all Checkboxes to True or Fales in Grid View, but when Exporting to Excel its just "".

    Can you please advise ?


    Please find code below:


    protected void BtnExport_OnClick(object sender, EventArgs e)
        {
            PrepareGridViewForExport(GridView1);
            Excel.Application MyExcel = new Excel.Application();       
            Excel.Workbook MyWorkbook = MyExcel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            
            int SheetNo = 1;
            
            if (GridView1.Rows.Count > 0)
            {
                PrepareGridViewForExport(GridView1);
                GridViewToExcel(GridView1, "Wireless", MyWorkbook, SheetNo);
                SheetNo++;
            }


    protected void GridViewToExcel(GridView GV, String name, Excel.Workbook MyWorkbook, int WorksheetNo)
        {
            
            Excel.Worksheet ActualSheet;
            ActualSheet = (Excel.Worksheet)MyWorkbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);      
            ActualSheet.Name = name;
            ActualSheet.Columns.ColumnWidth = 15;
           
            // Convert Serial No Column to Text
            ((Excel.Range)ActualSheet.Cells[1, 1]).EntireColumn.NumberFormat = "00";
            
            for (int i = 0; i < GV.Columns.Count; i++)
            {

                ActualSheet.Cells[1, i + 1] = GV.Columns[i].HeaderText;

                for (int j = 0; j < GV.Rows.Count; j++)
                {                
                        ActualSheet.Cells[j + 3, i + 1] = GV.Rows[j].Cells[i].Text;   
                }                  
                
            }


    private void PrepareGridViewForExport(Control gv)
        {
            
            Literal l = new Literal();        
            for (int i = 0; i < gv.Controls.Count; i++)
            {            
                {
                    l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }
                if (gv.Controls[i].HasControls())
                {
                    PrepareGridViewForExport(gv.Controls[i]);
                }

            }
        }

    Monday, May 24, 2010 10:46 AM

Answers

  • User-223648687 posted

    Hi,

    I finally figured it out. I had to go to every cell individually and then check the type of that cell and if it was a check box then I checked checkbox status and then returned True or False.


    Please find the code below:

    protected void BtnExport_OnClick(object sender, EventArgs e)
        {
            
            Excel.Application MyExcel = new Excel.Application();
            //MyWorkbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
            Excel.Workbook MyWorkbook = MyExcel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            
            int SheetNo = 1;
            
            if (GridView20.Rows.Count > 0)
            {
                //PrepareGridViewForExport(GridView20);
                GridViewToExcel(GridView20, "Wireless", MyWorkbook, SheetNo);
                SheetNo++;
            }

    }

    protected void GridViewToExcel(GridView GV, String name, Excel.Workbook MyWorkbook, int WorksheetNo)
        {
            
            Excel.Worksheet ActualSheet;
            ActualSheet = (Excel.Worksheet)MyWorkbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
            ActualSheet.Name = name;
            ActualSheet.Columns.ColumnWidth = 15;
           
            // Convert Serial No Column to Text

            ((Excel.Range)ActualSheet.Cells[1, 1]).EntireColumn.NumberFormat = "00";
            
            for (int i = 0; i < GV.Columns.Count; i++)
            {
                ActualSheet.Cells[1, i + 1] = GV.Columns[i].HeaderText;
                for (int j = 0; j < GV.Rows.Count; j++)
                {
                    if (GV.Rows[j].Cells[i].HasControls())
                    {
                        if (GV.Rows[j].Cells[i].Controls[0].GetType() == typeof(CheckBox))
                        {
                          if ((GV.Rows[j].Cells[i].Controls[0] as CheckBox).Checked == true)
                          {
                              ActualSheet.Cells[j + 3, i + 1] = "True";
                          }
                          if ((GV.Rows[j].Cells[i].Controls[0] as CheckBox).Checked == false)
                           {
                              ActualSheet.Cells[j + 3, i + 1] = "False";
                          }
                        }
                    }
                    else
                    {
                        ActualSheet.Cells[j + 3, i + 1] = GV.Rows[j].Cells[i].Text.ToString();
                    }
                    
                }  
                    
                
            }

        }


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 27, 2010 6:24 AM