Friday, 25 January 2013 13:56

how to export to excel xls format using NPOI

Written by 
Rate this item
(0 votes)

to properly export website data to previous version of excel
use the following example as a proper starting.one can export to
using 'Response object + gridview' but its not efficient and have some
lackings..

to export to xls format i used NPOI which is available in web.
first make reference of NPOI.dll.then need to add in code behind file -

using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;

following are the codes -

// Create a new workbook
var workbook = new HSSFWorkbook();

var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER;
headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerLabelCellStyle.SetFont(headerLabelFont);

var sheet = workbook.CreateSheet("Bids");
var rowProject = sheet.CreateRow(1);
var cell4 = rowProject.CreateCell(0);
cell4.SetCellValue("Project Name");
var rowCom = sheet.CreateRow(2);
var cell5 = rowCom.CreateCell(0);
cell5.SetCellValue("Bid");
var rowEst = sheet.CreateRow(3);
var cell6 = rowEst.CreateCell(0);
cell6.SetCellValue("Date");

//Auto-size each column
for (var j = 0; j < sheet.GetRow(5).LastCellNum; j++)
{
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(j, sheet.GetColumnWidth(j) + 2100);
}
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
//string saveAsFileName = string.Format("Bids.xls");
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "Bids.xls"));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}

Read 5333 times
Super User

Email This email address is being protected from spambots. You need JavaScript enabled to view it.
Login to post comments