Export the values in List of class type to excel format c#

hi friend

1- addference of Microsoft Exel 12.0 Object library from com group

2-use some name space in your page or controller method like bellow

using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Data;
using System.IO;

the below method follow

public ActionResult ImportExcel(FormCollection frm)
{
// OleDbConnection objConn = null;
System.Data.DataTable dt = new System.Data.DataTable();
var file = Request.Files[0];
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
// File Pfile = Request.Files[0];
string excelFile = file.FileName;
string filesavepath = Server.MapPath("exl") + "//" + "ex.xls";
file.SaveAs(filesavepath);
string filepath = filesavepath;
//pass that to workbook object
Microsoft.Office.Interop.Excel.Workbook WB = oExcel.Workbooks.Open(filepath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// statement get the workbookname
string ExcelWorkbookname = WB.Name;
// statement get the worksheet count
int worksheetcount = WB.Worksheets.Count;
//int shetno = sheet.SelectedIndex;
int shetno = 0;
Microsoft.Office.Interop.Excel.Worksheet wks = (Microsoft.Office.Interop.Excel.Worksheet)WB.Worksheets[shetno + 1];
Microsoft.Office.Interop.Excel.Range range;
// statement get the firstworksheetname
try
{
string firstworksheetname = wks.Name;
string str;
int rCnt;
int cCnt;
int rw = 0;
int cl = 0;
range = wks.UsedRange;
rw = range.Rows.Count;
cl = range.Columns.Count;
for (int i = 0; i < cl; i++)
{
string cna = "Field" + Convert.ToString(i + 1);
dt.Columns.Add(cna);
}
for (rCnt = 0; rCnt < rw; rCnt++)
{
DataRow dr = dt.NewRow();
for (cCnt = 0; cCnt < cl; cCnt++)
{
string s = range.Value2[rCnt, cCnt];
if (String.IsNullOrEmpty(s))
dr[cCnt] = "";
else
dr[cCnt] = s;
}
dt.Rows.Add(dr);
}
//grid.DataSource = dt;
//grid.DataBind();
WB.Close(true, null, null);
oExcel.Quit();
}
catch (Exception ex)
{
// return null;
}
finally
{
Marshal.ReleaseComObject(wks);
Marshal.ReleaseComObject(WB);
Marshal.ReleaseComObject(oExcel);
}
return RedirectToAction("ImportExcel");
}

1 Reply

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

Laxmidhar Sahoo8y ago
hi friend

1- addference of Microsoft Exel 12.0 Object library from com group

2-use some name space in your page or controller method like bellow
using Microsoft.Office.Interop;

using Microsoft.Office.Interop.Excel;

using System.Runtime.InteropServices;

using System.Reflection;

using System.Data;

using System.IO;

the below method follow
public ActionResult ImportExcel(FormCollection frm)

     {

        // OleDbConnection objConn = null;

         System.Data.DataTable dt = new System.Data.DataTable();





         var file = Request.Files[0];



         Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();

       //  File Pfile = Request.Files[0];



         string excelFile = file.FileName;

         string filesavepath = Server.MapPath("exl") + "//" + "ex.xls";

         file.SaveAs(filesavepath);





         string filepath = filesavepath;

         //pass that to workbook object

         Microsoft.Office.Interop.Excel.Workbook WB = oExcel.Workbooks.Open(filepath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);





         // statement get the workbookname

         string ExcelWorkbookname = WB.Name;



         // statement get the worksheet count

         int worksheetcount = WB.Worksheets.Count;

         //int shetno = sheet.SelectedIndex;

         int shetno = 0;

         Microsoft.Office.Interop.Excel.Worksheet wks = (Microsoft.Office.Interop.Excel.Worksheet)WB.Worksheets[shetno + 1];

         Microsoft.Office.Interop.Excel.Range range;

         // statement get the firstworksheetname

         try

         {





             string firstworksheetname = wks.Name;



             string str;

             int rCnt;

             int cCnt;

             int rw = 0;

             int cl = 0;



             range = wks.UsedRange;

             rw = range.Rows.Count;

             cl = range.Columns.Count;

             for (int i = 0; i < cl; i++)

             {

                 string cna = "Field" + Convert.ToString(i + 1);

                 dt.Columns.Add(cna);

             }



             for (rCnt = 0; rCnt < rw; rCnt++)

             {

                 DataRow dr = dt.NewRow();

                 for (cCnt = 0; cCnt < cl; cCnt++)

                 {







                     string s = range.Value2[rCnt, cCnt];



                     if (String.IsNullOrEmpty(s))

                         dr[cCnt] = "";

                     else

                         dr[cCnt] = s;

                 }

                 dt.Rows.Add(dr);

             }



             //grid.DataSource = dt;

             //grid.DataBind();

             WB.Close(true, null, null);

             oExcel.Quit();



         }

         catch (Exception ex)

         {

             // return null;

         }

         finally

         {



             Marshal.ReleaseComObject(wks);

             Marshal.ReleaseComObject(WB);

             Marshal.ReleaseComObject(oExcel);

         }

         return RedirectToAction("ImportExcel");

     }