Export to excel 2003 File Formate

Down Load Dll File http://npoi.codeplex.com/releases/49524/download/140879

Create  .Cs as Class file

using System;
using System.IO;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;

public class NpoiExport : IDisposable
{
const int MaximumNumberOfRowsPerSheet = 65500;
const int MaximumSheetNameLength = 25;
protected Workbook Workbook { get; set; }

public NpoiExport()
{
this.Workbook = new HSSFWorkbook();
}

protected string EscapeSheetName(string sheetName)
{
var escapedSheetName = sheetName
.Replace(“/”, “-“)
.Replace(“\\”, ” “)
.Replace(“?”, string.Empty)
.Replace(“*”, string.Empty)
.Replace(“[“, string.Empty)
.Replace(“]”, string.Empty)
.Replace(“:”, string.Empty);

if (escapedSheetName.Length > MaximumSheetNameLength)
escapedSheetName = escapedSheetName.Substring(0, MaximumSheetNameLength);

return escapedSheetName;
}

protected Sheet CreateExportDataTableSheetAndHeaderRow(DataTable exportData, string sheetName, CellStyle headerRowStyle)
{
var sheet = this.Workbook.CreateSheet(EscapeSheetName(sheetName));

// Create the header row
var row = sheet.CreateRow(0);

for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
{
var cell = row.CreateCell(colIndex);
cell.SetCellValue(exportData.Columns[colIndex].ColumnName);

if (headerRowStyle != null)
cell.CellStyle = headerRowStyle;
}

return sheet;
}

public void ExportDataTableToWorkbook(DataTable[] exportData, string sheetName)
{
// Create the header row cell style
var headerLabelCellStyle = this.Workbook.CreateCellStyle();
headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
var headerLabelFont = this.Workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerLabelCellStyle.SetFont(headerLabelFont);
foreach (DataTable dt in exportData)
{
var sheet = CreateExportDataTableSheetAndHeaderRow(dt, dt.TableName, headerLabelCellStyle);

var currentNPOIRowIndex = 1;
var sheetCount = 1;

for (var rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
{
if (currentNPOIRowIndex >= MaximumNumberOfRowsPerSheet)
{
sheetCount++;
currentNPOIRowIndex = 1;

sheet = CreateExportDataTableSheetAndHeaderRow(dt,
sheetName + ” – ” + sheetCount,
headerLabelCellStyle);
}

var row = sheet.CreateRow(currentNPOIRowIndex++);

for (var colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
var cell = row.CreateCell(colIndex);
cell.SetCellValue(dt.Rows[rowIndex][colIndex].ToString());
}
}
}
}

public byte[] GetBytes()
{
using (var buffer = new MemoryStream())
{
this.Workbook.Write(buffer);
return buffer.GetBuffer();
}
}

public void Dispose()
{
if (this.Workbook != null)
this.Workbook.Dispose();
}
}

use this method and pass datatable with different name

protected void exportexcel(DataTable[] dt)
{
using (var exporter = new NpoiExport())
{
exporter.ExportDataTableToWorkbook(dt, “Results”);

string saveAsFileName = string.Format(“QueryResults-{0:d}.xls”, DateTime.Now);

Response.ContentType = “application/vnd.ms-excel”;
Response.AddHeader(“Content-Disposition”, string.Format(“attachment;filename={0}”, saveAsFileName));
Response.Clear();
Response.BinaryWrite(exporter.GetBytes());
Response.End();
}
}