how export Array of a complex class to Excel using Interop

Hi,

I have one you can try this.

Add reference of Excel interop and

//import

using Excel = Microsoft.Office.Interop.Excel;

//code

void GridToExcel()
{
try
{
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
oXL = new Excel.Application();
oXL.Visible = true;
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oSheet.Rows.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
int iRowCoutnter = 15;
int ChColStart = 65, ChColChar2 = 65;
string NextColChar = string.Empty, NextRowChar = string.Empty;
for (int i = 0; i < CurrentGridView.ColumnCount; i++)
{
{
NextColChar = (Char)ChColStart + "1";
if (i > 25)
{
if (ChColStart == 91)
ChColStart = 65;
else
ChColStart += 1;
NextColChar = "A" + (Char)ChColStart + "" + "1";
}
oSheet.get_Range(NextColChar).Value = CurrentGridView.Columns[i].HeaderText;
if (i <= 25)
ChColStart += 1;
iRowCoutnter++;
}
}
for (int j = 0; j < CurrentGridView.Rows.Count; j++)
{
for (int i = 0; i < CurrentGridView.ColumnCount; i++)
{
{
NextRowChar = (Char)ChColChar2 + (j + 2).ToString();
if (i > 25)
{
if (ChColChar2 == 91)
ChColChar2 = 65;
else
ChColChar2 += 1;
NextRowChar = "A" + (Char)ChColChar2 + (j + 2).ToString();
}
oSheet.get_Range(NextRowChar).Value = CurrentGridView.Rows[j].Cells[i].Value;
if (i <= 25)
ChColChar2 += 1;
iRowCoutnter++;
}
}
ChColChar2 = 65;
}
int GridRows = (CurrentGridView.Rows.Count + 2);
string RangeLastChar = NextColChar.Remove(NextColChar.Length - 1, 1);
oSheet.get_Range("A1", RangeLastChar + "1").Font.Bold = true;
oSheet.get_Range("A1", RangeLastChar + "1").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
oSheet.get_Range("A1", RangeLastChar + "1").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
Microsoft.Office.Interop.Excel.Borders border = oSheet.get_Range("A1:A" + (GridRows + 2).ToString(), RangeLastChar + "1:" + RangeLastChar + (GridRows + 2).ToString()).Borders;
border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//UPDATED ON 25-2-2017
oSheet.Columns.Font.Size = 10;
oSheet.Columns.AutoFit();
oWB.SaveAs(Application.StartupPath + string.Format(@"\PPExl{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")), AccessMode: Excel.XlSaveAsAccessMode.xlShared);
//MessageBox.Show("Excel Generated Successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Error while creating Excel." + Environment.NewLine + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}

Hope it helps.

Thanks.

Cheers..

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Vishal Jadav8y ago
Accepted answer
Hi,

I have one you can try this.
Add reference of Excel interop and
//import
using Excel = Microsoft.Office.Interop.Excel;

//code
void GridToExcel()
       {
           try
           {
               Excel.Application oXL;
               Excel._Workbook oWB;
               Excel._Worksheet oSheet;
               oXL = new Excel.Application();
               oXL.Visible = true;

               oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
               oSheet = (Excel._Worksheet)oWB.ActiveSheet;
               oSheet.Rows.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);

               int iRowCoutnter = 15;
               int ChColStart = 65, ChColChar2 = 65;
               string NextColChar = string.Empty, NextRowChar = string.Empty;
               for (int i = 0; i < CurrentGridView.ColumnCount; i++)
               {

                   {
                       NextColChar = (Char)ChColStart + "1";
                       if (i > 25)
                       {
                           if (ChColStart == 91)
                               ChColStart = 65;
                           else
                               ChColStart += 1;
                           NextColChar = "A" + (Char)ChColStart + "" + "1";
                       }

                       oSheet.get_Range(NextColChar).Value = CurrentGridView.Columns[i].HeaderText;
                       if (i <= 25)
                           ChColStart += 1;
                       iRowCoutnter++;
                   }
               }

               for (int j = 0; j < CurrentGridView.Rows.Count; j++)
               {
                   for (int i = 0; i < CurrentGridView.ColumnCount; i++)
                   {

                       {
                           NextRowChar = (Char)ChColChar2 + (j + 2).ToString();
                           if (i > 25)
                           {
                               if (ChColChar2 == 91)
                                   ChColChar2 = 65;
                               else
                                   ChColChar2 += 1;
                               NextRowChar = "A" + (Char)ChColChar2 + (j + 2).ToString();
                           }

                           oSheet.get_Range(NextRowChar).Value = CurrentGridView.Rows[j].Cells[i].Value;
                           if (i <= 25)
                               ChColChar2 += 1;
                           iRowCoutnter++;
                       }
                   }
                   ChColChar2 = 65;
               }

               int GridRows = (CurrentGridView.Rows.Count + 2);
               string RangeLastChar = NextColChar.Remove(NextColChar.Length - 1, 1);
               oSheet.get_Range("A1", RangeLastChar + "1").Font.Bold = true;

               oSheet.get_Range("A1", RangeLastChar + "1").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
               oSheet.get_Range("A1", RangeLastChar + "1").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);

               Microsoft.Office.Interop.Excel.Borders border = oSheet.get_Range("A1:A" + (GridRows + 2).ToString(), RangeLastChar + "1:" + RangeLastChar + (GridRows + 2).ToString()).Borders;

               border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
               border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
               border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
               border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
               border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
               border[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
               //UPDATED ON 25-2-2017
               oSheet.Columns.Font.Size = 10;
               oSheet.Columns.AutoFit();
               oWB.SaveAs(Application.StartupPath + string.Format(@"\PPExl{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")), AccessMode: Excel.XlSaveAsAccessMode.xlShared);

               //MessageBox.Show("Excel Generated Successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
           }
           catch (Exception ex)
           {
               MessageBox.Show("Error while creating Excel." + Environment.NewLine + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
           }
       }

Hope it helps.
Thanks.

Cheers..