export data to excel file in an asp.net application

Question

Can someone provide a link with a tutorial about exporting data to an excel file using c# in an asp.net web application.I searched the internet but I didn't find any tutorials that will explain how they do it.

Answer 1

There is a easy way to use npoi.mapper with just below 2 lines

var mapper = new Mapper();
mapper.Save("test.xlsx",  objects, "newSheet");

Answer 2

Pass List to below method, that will convert the list to buffer and then return buffer, a file will be downloaded.

List<T> resultList = New List<T>();
  byte[] buffer = Write(resultList, true, "AttendenceSummary");
            return File(buffer, "application/excel", reportTitle + ".xlsx");

public static byte[] Write<T>(IEnumerable<T> list, bool xlsxExtension = true, string sheetName = "ExportData")
        {
            if (list == null)
            {
                throw new ArgumentNullException("list");
            }

            XSSFWorkbook hssfworkbook = new XSSFWorkbook();
            int Rowspersheet = 15000;
            int TotalRows = list.Count();
            int TotalSheets = TotalRows / Rowspersheet;

            for (int i = 0; i <= TotalSheets; i++)
            {
                ISheet sheet1 = hssfworkbook.CreateSheet(sheetName + "_" + i);
                IRow row = sheet1.CreateRow(0);
                int index = 0;
                foreach (PropertyInfo property in typeof(T).GetProperties())
                {
                    ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
                    IFont cellFont = hssfworkbook.CreateFont();

                    cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                    cellStyle.SetFont(cellFont);

                    ICell cell = row.CreateCell(index++);
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue(property.Name);
                }

                int rowIndex = 1;
                // int rowIndex2 = 1;

                foreach (T obj in list.Skip(Rowspersheet * i).Take(Rowspersheet))
                {

                    row = sheet1.CreateRow(rowIndex++);
                    index = 0;

                    foreach (PropertyInfo property in typeof(T).GetProperties())
                    {
                        ICell cell = row.CreateCell(index++);
                        cell.SetCellValue(Convert.ToString(property.GetValue(obj)));
                    }

                }
            }

            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);
            return file.ToArray();

        }

Answer 3

You can use Interop http://www.c-sharpcorner.com/UploadFile/Globalking/datasettoexcel02272006232336PM/datasettoexcel.aspx

Or if you don't want to install Microsoft Office on a webserver I recommend using CarlosAg.ExcelXmlWriter which can be found here: http://www.carlosag.net/tools/excelxmlwriter/

code sample for ExcelXmlWriter:

using CarlosAg.ExcelXmlWriter;

class TestApp {
    static void Main(string[] args) {
        Workbook book = new Workbook();
        Worksheet sheet = book.Worksheets.Add("Sample");
        WorksheetRow row =  sheet.Table.Rows.Add();
        row.Cells.Add("Hello World");
        book.Save(@"c:\test.xls");
    }
}

Answer 4

I've written a C# class, which lets you write your DataSet, DataTable or List<> data directly into a Excel .xlsx file using the OpenXML libraries.

http://mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

It's completely free to download, and very ASP.Net friendly.

Just pass my C# function the data to be written, the name of the file you want to create, and your page's "Response" variable, and it'll create the Excel file for you, and write it straight to the Page, ready for the user to Save/Open.

class Employee;
List<Employee> listOfEmployees = new List<Employee>();


// The following ASP.Net code gets run when I click on my "Export to Excel" button.
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
    // It doesn't get much easier than this...
    CreateExcelFile.CreateExcelDocument(listOfEmployees, "Employees.xlsx", Response);
}

(I work for a finanical company, and we'd be lost without this functionality in every one of our apps !!)

Answer 5

You can try the following links :

http://www.codeproject.com/Articles/164582/8-Solutions-to-Export-Data-to-Excel-for-ASP-NET

Export data as Excel file from ASP.NET

http://codeissue.com/issues/i14e20993075634/how-to-export-gridview-control-data-to-excel-file-using-asp-net