how export Array of a complex class to Excel using Interop

Sign in to answer this question.

• 

  Vishal Jadav8y agoPosted May 19, 2018, 1:43 AM

  Accepted answer

  Hi,

   

  I have one you can try this.

   Add reference of Excel interop and 

   //import 

  using Excel = Microsoft.Office.Interop.Excel; 

   

   

  //code  

  1. void GridToExcel()  
  2.        {  
  3.            try  
  4.            {  
  5.                Excel.Application oXL;  
  6.                Excel._Workbook oWB;  
  7.                Excel._Worksheet oSheet;  
  8.                oXL = new Excel.Application();  
  9.                oXL.Visible = true;  
  10.   
  11.                oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));  
  12.                oSheet = (Excel._Worksheet)oWB.ActiveSheet;  
  13.                oSheet.Rows.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);  
  14.   
  15.                int iRowCoutnter = 15;  
  16.                int ChColStart = 65, ChColChar2 = 65;  
  17.                string NextColChar = string.Empty, NextRowChar = string.Empty;  
  18.                for (int i = 0; i < CurrentGridView.ColumnCount; i++)  
  19.                {  
  20.                     
  21.                    {  
  22.                        NextColChar = (Char)ChColStart + "1";  
  23.                        if (i > 25)  
  24.                        {  
  25.                            if (ChColStart == 91)  
  26.                                ChColStart = 65;  
  27.                            else  
  28.                                ChColStart += 1;  
  29.                            NextColChar = "A" + (Char)ChColStart + "" + "1";  
  30.                        }  
  31.   
  32.                        oSheet.get_Range(NextColChar).Value = CurrentGridView.Columns[i].HeaderText;  
  33.                        if (i <= 25)  
  34.                            ChColStart += 1;  
  35.                        iRowCoutnter++;  
  36.                    }  
  37.                }  
  38.   
  39.                for (int j = 0; j < CurrentGridView.Rows.Count; j++)  
  40.                {  
  41.                    for (int i = 0; i < CurrentGridView.ColumnCount; i++)  
  42.                    {  
  43.                        
  44.                        {  
  45.                            NextRowChar = (Char)ChColChar2 + (j + 2).ToString();  
  46.                            if (i > 25)  
  47.                            {  
  48.                                if (ChColChar2 == 91)  
  49.                                    ChColChar2 = 65;  
  50.                                else  
  51.                                    ChColChar2 += 1;  
  52.                                NextRowChar = "A" + (Char)ChColChar2 + (j + 2).ToString();  
  53.                            }  
  54.   
  55.                            oSheet.get_Range(NextRowChar).Value = CurrentGridView.Rows[j].Cells[i].Value;  
  56.                            if (i <= 25)  
  57.                                ChColChar2 += 1;  
  58.                            iRowCoutnter++;  
  59.                        }  
  60.                    }  
  61.                    ChColChar2 = 65;  
  62.                }  
  63.   
  64.                int GridRows = (CurrentGridView.Rows.Count + 2);  
  65.                string RangeLastChar = NextColChar.Remove(NextColChar.Length - 1, 1);  
  66.                oSheet.get_Range("A1", RangeLastChar + "1").Font.Bold = true;  
  67.   
  68.                oSheet.get_Range("A1", RangeLastChar + "1").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);  
  69.                oSheet.get_Range("A1", RangeLastChar + "1").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);  
  70.   
  71.                Microsoft.Office.Interop.Excel.Borders border = oSheet.get_Range("A1:A" + (GridRows + 2).ToString(), RangeLastChar + "1:" + RangeLastChar + (GridRows + 2).ToString()).Borders;  
  72.   
  73.                border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;  
  74.                border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;  
  75.                border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;  
  76.                border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;  
  77.                border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;  
  78.                border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;  
  79.                //UPDATED ON 25-2-2017  
  80.                oSheet.Columns.Font.Size = 10;  
  81.                oSheet.Columns.AutoFit();  
  82.                oWB.SaveAs(Application.StartupPath + string.Format(@"\PPExl{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")), AccessMode: Excel.XlSaveAsAccessMode.xlShared);  
  83.   
  84.                //MessageBox.Show("Excel Generated Successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  85.            }  
  86.            catch (Exception ex)  
  87.            {  
  88.                MessageBox.Show("Error while creating Excel." + Environment.NewLine + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);  
  89.            }  
  90.        }  

   

   Hope it helps.

   

  Thanks. 

   

  Cheers..