How to Export Webcontrol table to excel in asp.net mvc

Question

List<Table> t = (List<Table>)GenerateLayout(dtDPList, dtBOList, dpl);

In the list i am getting the 3 Webcontrol table and i want to export those webconrol tables to excel.. so please help me..

Fixed code indent.

phd
– phd

2017-07-05 12:03:01 +00:00
Commented Jul 5, 2017 at 12:03 — phd
– phd, Commented Jul 5, 2017 at 12:03

Aman Sahni · Accepted Answer · 2017-07-05 11:44:52Z

0

You can use the below generic method to convert datatable to excel :

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelDemo
{
    public class ExcelUtility
    {
    public static void CreateExcel(DataSet ds, string excelPath)
    {
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        try
        {
            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                {
                    xlWorkSheet.Cells[i + 1, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                }
            }

            xlWorkBook.SaveAs(excelPath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlApp);
            releaseObject(xlWorkBook);
            releaseObject(xlWorkSheet);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    private static void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch
        {
            obj = null;
        }
        finally
        {
            GC.Collect();
        }
    } 
}

}

You can directly apply the style properties to a cell as given below:

workSheet.Cells[2, 1].Font.Bold = true

You can find more style tags using the link below:

http://csharp.net-informations.com/excel/csharp-format-excel.htm

edited Jul 5, 2017 at 11:44

answered Jul 5, 2017 at 4:59

Aman Sahni

2121 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Siddu Biradar Over a year ago

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

Siddu Biradar Over a year ago

Brother, How to apply styles?? I M Using xlWorkSheet.Rows[i].Style = t[0].Rows[i].Style; in the for loop but its showing error, so please help me..

Aman Sahni Over a year ago

You need to create a style object and then needs to define style properties. And then you can assign style object to workbook. Please see the update answer. Hope it will help you.

Siddu Biradar Over a year ago

you can find more details in - techbrij.com/export-excel-xls-xlsx-asp-net-npoi-epplus

Siddu Biradar · Accepted Answer · 2017-07-11 06:14:04Z

List list = GenerateLayout(dt1,dt2,dpl); Response.ClearContent(); Response.AddHeader("content-disposition", "attachment;filename=DeliveryPickList.xls"); Response.AddHeader("Content-Type", "application/vnd.ms-excel");

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                list[0].RenderControl(htw);
                TextWriter output= Response.Output;
                output.Write(sw.ToString());
            }
        }
        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                list[1].RenderControl(htw);
                TextWriter output = Response.Output;
                output.Write(sw.ToString());
            }
        }
        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                list[2].RenderControl(htw);
                TextWriter output = Response.Output;
                output.Write(sw.ToString());
            }
        }
        Response.End();

Collectives™ on Stack Overflow

How to Export Webcontrol table to excel in asp.net mvc

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related