Excel Exports in C# using NPOI

Okay, I’m waiting for software to install, so here we go:

The c# port of NPOI is quite amazing and doesn’t require you to install Office on your server. Unfortunately the documentation is…not as robust as I’d like. Here’s a bit to get you started.

Environment: Use Visual Studio – I’m using 2005 in this example and targeting .net 2.0. This will work for higher versions, in fact, I recommend a higher .net framework to get extended features.

To get started, you need to create a new Visual Studio project and add references to NPOI. To get the dll’s you need to reference, go here: http://code.google.com/p/npoi/downloads/list. You need the binary files. Download the files and save them in the bin folder of your project (if you do not have one already [unlikely], you need to create one). Then, right click references and reference each of the downloaded dll’s in your references. You do not need the xml files, but you can hold onto them in the bin folder.

For reference, you need:

  • NPOI
  • NPOI.DDF
  • NPOI.HPSF
  • NPOI.HSSF
  • NPOI.Util

Finally, on any class you use the library on, you need to add these in using statements (described below).

 

Got that? FANTASIC.

 

If you are pulling data from a database using a stored procedure, you can store the output in a DataTable and convert the DataTable to Excel.

Using Statements:

using System.IO;

using NPOI.HSSF.UserModel;

using NPOI.HPSF;

using NPOI.POIFS.FileSystem;

using NPOI.HSSF.Util;

using System.Data; //For DataTable

 

Conversion Method:

////////////NPOI DATATABLE CONVERSION METHOD////////////

//To make this as reusable as possible, I am giving you the option of

//passing in a DataTable and file name.

 

public static void DataTableToExcel(DataTable dt, String fileName)

{

//Make a new npoi workbook

HSSFWorkbook hssfworkbook = new HSSFWorkbook();

 

//Here I am making sure that I am giving the file name the right

//extension:

string filename = “”;

if (fileName.EndsWith(“.xls”))

filename = fileName;

else

filename = fileName + “.xls”;

 

//This starts the dialogue box that allows the user to download the file

 

System.Web.HttpResponse Response =        System.Web.HttpContext.Current.Response;

 

Response.ContentType = “application/vnd.ms-excel”;

Response.AddHeader(“Content-Disposition”, string.Format(“attachment;filename={0}”, filename));

Response.Clear();

 

//make a new sheet – name it any excel-compliant string you want

HSSFSheet sheet1 = hssfworkbook.CreateSheet(“Sheet 1”);

 

//make a header row

HSSFRow row1 = sheet1.CreateRow(0);

 

//Puts in headers (these are table row headers, omit if you

//just need a straight data dump

for (int j = 0; j < dt.Columns.Count; j++)

{

HSSFCell cell = row1.CreateCell(j);

String columnName = dt.Columns[j].ToString();

cell.SetCellValue(columnName);

 

}

//loops through data

for (int i = 0; i < dt.Rows.Count; i++)

{

HSSFRow row = sheet1.CreateRow(i+1);

for (int j = 0; j < dt.Columns.Count; j++)

{

HSSFCell cell = row.CreateCell(j);

String columnName = dt.Columns[j].ToString();

cell.SetCellValue(dt.Rows[i][columnName].ToString());

 

}

}

 

//writing the data to binary from memory

Response.BinaryWrite(WriteToStream(hssfworkbook).GetBuffer());

Response.End();

 

}

 

static MemoryStream WriteToStream(HSSFWorkbook hssfworkbook)

{

//Write the stream data of workbook to the root directory

MemoryStream file = new MemoryStream();

hssfworkbook.Write(file);

return file;

}

//The above two methods are designed to be reused with any DataTable assuming you do not need special formatting.

 

Advertisements

2 comments on “Excel Exports in C# using NPOI

    • The download should come up as a popup on the site you download from since this is asp.net and I have a response header that I’m using…you could make the package save locally too.

      If you have a request for some different use cases, let me know in a comment what you’re trying to do and I’ll show you what you need.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s