How to create an excel spreadsheet using a dataset with Npoi

Question

I am completely new to Npoi and I am struggling trying to find some information on how to create a basic spreadsheet from a DataSet. I have a DataSet being returned that will only ever contain one table I am trying to get the columns and values from these columns into an dynamically generated excel file that I can allow users to download.

So far I have been able to create the workbook and sheet but I can't figure out how to properly populate sheet using the Npoi.dll

My code for creating the workbook (so far) is as follows:

private void CreateWorkbook(DataSet ds)
{
    var table = ds.Tables[0];
    var workbook = new HSSFWorkbook();
    var sheet = workbook.CreateSheet();
    foreach (DataColumn col in table.Columns)
    {
        //seems like I should create the columns for the sheet here

        foreach (DataRow row in table.Rows)
        {
            sheet.CreateRow(); //then populate each column with the approriate data
        }
    }         
}

In addition, and I may be missing something but where is all the documentation Npoi on codeplex mentions, I cannot find anything relevant for Mvc apps dynamically creating a sheet and returning it to the client? What I am trying to accomplish is to create create the file and pass it to the client without storing it on the server.

I'm sure this is basic but I can't figure out where to look for information. I'd appreciate any suggestions.

-cheers

Answer 1

A couple links that got me started:

How to read in XLSX data for editing with NPOI

Creating Excel spreadsheets .XLS and .XLSX in C#

And finally at quick overview of basic functions from the POI documentation like reading a named range, collapsing rows, etc. As NPOI is a close match to POI the guide is fairly effective.

Busy Developers' Guide to HSSF and XSSF Features