How to Export List Data using NPOI

Question

I'm using NPOI to export data into excel. So I created a List that will pull data from my database. Now My question is how can I read my list data and write the data on my excel Sheet. The following is my part of my code:

            IWorkbook workbook;
            workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
            ISheet excelSheet = workbook.CreateSheet("Candidates");
            IRow row = excelSheet.CreateRow(0);


            foreach (var data in ApplicationList)
            {

            }
            workbook.Write(fs);

So basically I need help on foreach (var data in ApplicationList)

Answer 1

While writing data cells can be created and SetCellValue can help set the data.

Below I have tried to iterate over a single column and list of strings. This works fine on my system.

IWorkbook workbook = new HSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet("Candidates");
IRow row = excelSheet.CreateRow(0);
var applicantList = new List<string> { "David", "Paul" };
var excelColumns = new[] { "Name" };
IRow headerRow = excelSheet.CreateRow(0);
var headerColumn = 0;
excelColumns.ToList().ForEach(excelColumn =>
{
    var cell = headerRow.CreateCell(headerColumn);
    cell.SetCellValue(excelColumn);
    headerColumn++;
 });
var rowCount = 1;
applicantList.ForEach(applicant => {
    var row = excelSheet.CreateRow(rowCount);
    var cellCount = 0;
    excelColumns.ToList().ForEach(column => {
        var cell = row.CreateCell(cellCount);
        cell.SetCellValue(applicant);
        cellCount++;
    });
    rowCount++;
});
var stream = new MemoryStream();
workbook.Write(stream);
string FilePath = "/Users/hemkumar/hem.xls"; //path to download
FileStream file = new FileStream(FilePath, FileMode.CreateNew, 
                   FileAccess.Write);
stream.WriteTo(file);
file.Close();
stream.Close();

I hope it helps.

Answer 2

I know I am a little late here but I think it may help others

I have developed an excel utility with the use of the NPOI package, which can

1. Simply takes your data table or the collection
2. And Returns you excel while maintaining all the data table/list data type intact in the excel.

Github Code repo.: https://github.com/ansaridawood/.NET-Generic-Excel-Export-Sample/tree/master/GenericExcelExport/ExcelExport

Looking for a code explanation, you can find it here: https://www.codeproject.com/Articles/1241654/Export-to-Excel-using-NPOI-Csharp-and-WEB-API

It uses NPOI DLL and it has 2 cs files to include and then you are good to go

Below is the first file for reference AbstractDataExport.cs:

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;

namespace GenericExcelExport.ExcelExport
{
    public interface IAbstractDataExport
    {
        HttpResponseMessage Export(List exportData, string fileName, string sheetName);
    }

    public abstract class AbstractDataExport : IAbstractDataExport
    {
        protected string _sheetName;
        protected string _fileName;
        protected List _headers;
        protected List _type;
        protected IWorkbook _workbook;
        protected ISheet _sheet;
        private const string DefaultSheetName = "Sheet1";

        public HttpResponseMessage Export
              (List exportData, string fileName, string sheetName = DefaultSheetName)
        {
            _fileName = fileName;
            _sheetName = sheetName;

            _workbook = new XSSFWorkbook(); //Creating New Excel object
            _sheet = _workbook.CreateSheet(_sheetName); //Creating New Excel Sheet object

            var headerStyle = _workbook.CreateCellStyle(); //Formatting
            var headerFont = _workbook.CreateFont();
            headerFont.IsBold = true;
            headerStyle.SetFont(headerFont);

            WriteData(exportData); //your list object to NPOI excel conversion happens here

            //Header
            var header = _sheet.CreateRow(0);
            for (var i = 0; i < _headers.Count; i++)
            {
                var cell = header.CreateCell(i);
                cell.SetCellValue(_headers[i]);
                cell.CellStyle = headerStyle;
            }

            for (var i = 0; i < _headers.Count; i++)
            {
                _sheet.AutoSizeColumn(i);
            }

            using (var memoryStream = new MemoryStream()) //creating memoryStream
            {
                _workbook.Write(memoryStream);
                var response = new HttpResponseMessage(HttpStatusCode.OK)
                {
                    Content = new ByteArrayContent(memoryStream.ToArray())
                };

                response.Content.Headers.ContentType = new MediaTypeHeaderValue
                       ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.Content.Headers.ContentDisposition = 
                       new ContentDispositionHeaderValue("attachment")
                {
                    FileName = $"{_fileName}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
                };

                return response;
            }
        }

        //Generic Definition to handle all types of List
        public abstract void WriteData(List exportData);
    }
}

and this the second and final file AbstractDataExportBridge.cs:

using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text.RegularExpressions;

namespace GenericExcelExport.ExcelExport
{
    public class AbstractDataExportBridge : AbstractDataExport
    {
        public AbstractDataExportBridge()
        {
            _headers = new List<string>();
            _type = new List<string>();
        }

        public override void WriteData<T>(List<T> exportData)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));

            DataTable table = new DataTable();

            foreach (PropertyDescriptor prop in properties)
            {
                var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                _type.Add(type.Name);
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? 
                                  prop.PropertyType);
                string name = Regex.Replace(prop.Name, "([A-Z])", " $1").Trim(); //space separated 
                                                                           //name by caps for header
                _headers.Add(name);
            }

            foreach (T item in exportData)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }

            IRow sheetRow = null;

            for (int i = 0; i < table.Rows.Count; i++)
            {
                sheetRow = _sheet.CreateRow(i + 1);
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    ICell Row1 = sheetRow.CreateCell(j);

                    string type = _type[j].ToLower();
                    var currentCellValue = table.Rows[i][j];

                    if (currentCellValue != null && 
                        !string.IsNullOrEmpty(Convert.ToString(currentCellValue)))
                    {
                        if (type == "string")
                        {
                            Row1.SetCellValue(Convert.ToString(currentCellValue));
                        }
                        else if (type == "int32")
                        {
                            Row1.SetCellValue(Convert.ToInt32(currentCellValue));
                        }
                        else if (type == "double")
                        {
                            Row1.SetCellValue(Convert.ToDouble(currentCellValue));
                        }
                    }
                    else
                    {
                        Row1.SetCellValue(string.Empty);
                    }
                }
            }
        }
    }
}

For a detailed explanation, refer link provided in the beginning.

Answer 3

I'm using NPOI to export data into excel too.

But I have a list that will pull data from another excel file that created by NPOI.

Anyway, I think my solution to solve this problem, which is not much different from yours, can be effective.

After you see the code sample below, read the description.

await using var stream = new FileStream(@"C:\Users\Sina\Desktop\TestExcel.xlsx", FileMode.OpenOrCreate, FileAccess.Write);

IWorkbook workbook = new XSSFWorkbook();

var excelSheet = workbook.CreateSheet("TestSheet");

for (var i = 0; i < MyDataList.Count(); i++)
{
    var row = excelSheet.CreateRow(i);

    for (var j = 0; j < MyDataList[i].Cells.Count(); j++)
    {
        var cell = row.CreateCell(j);

        cell.SetCellValue(MyDataList[i].Cells[j].ToString());
    }
}

workbook.Write(stream);

As I said, instead of the list you got the data from your database, I've used a list that has data from another excel file that I pulled through NPOI.

You can see it in the code snippet above (MyDataList). It is of type (List<IRow>).

You have to create as many rows as there are data in your list, so create it in a loop each time. var row = excelSheet.CreateRow(i)

Now notice that each row has several cells and I fill the cells with another loop and you need to create any number of cells in your row, so create it in this loop each time. var cell = row.CreateCell(j)

You can now use cell.SetCellValue() to set each cell data then use the data in your list instead of MyDataList[i].Cells[j] in that.

Note that the input type of the SetCellValue() method must be a string.

Now I want to add that I also used the AddRange() method instead of the second loop (like this - row.Cells.AddRange(FailedRowList[i].Cells)) but it didn't work, so if you can use that I would appreciate if you say it and let me know more. I hope my answer was helpful. Thanks