Export .xls and .xlsx with NPOI

HSSF vs XSSF in NPOI

What is NPOI ?

NPOI is the .NET version of POI Java project at http://poi.apache.org/. POI is an open source project which can help you read/write xls, doc, ppt files. It has a wide application.

For example, you can use it to

• generate a Excel report without Microsoft Office suite installed on your server and more efficient than call Microsoft Excel ActiveX at background;
• extract text from Office documents to help you implement full-text indexing feature (most of time this feature is used to create search engines).
• extract images from Office documents
• generate Excel sheets that contains formulas

What is different between HSSF and XSSF?

HSSF can export .xls and XSSF can export .xlsx.

HSSF is the POI Project’s pure Java implementation of the Excel ‘97(-2007) file format. XSSF is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

HSSF and XSSF provides ways to read spreadsheets create, modify, read and write XLS spreadsheets. They provide:

• low level structures for those with special needs
• an eventmodel api for efficient read-only access
• a full usermodel api for creating, reading and modifying XLS files

For people converting from pure HSSF usermodel, who wish to use the joint SS Usermodel for HSSF and XSSF support, then see the ss usermodel converting guide.

Install with Nuget

Install-Package NPOI

OR

dotnet add package NPOI --version 2.6.0

HSSF Sample

        protected ActionResult ExcelView(DataTable table,List<Type> types)
        {
            SetHeaders("application/vnd.ms-excel; charset=UTF-8", "xls");
            string folderPath = Server.MapPath("/temp");
            string filePath = Path.Combine(folderPath, "{0}.xls".Fill(Guid.NewGuid().ToString()));
            if (!Directory.Exists(folderPath))
            {
                throw new Exception("Folder {0} doesn't exist. This folder is required for temporary server-side document storage. Please create this document in your application root.".Fill(folderPath));
            }
            var workbook = new HSSFWorkbook();

            //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
            var sheet = workbook.CreateSheet("Report");

            var headerRow = sheet.CreateRow(0);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                headerRow.CreateCell(i).SetCellValue(table.Columns[i].Caption);
            }

            // Populate worksheet with the table's data
            for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
            {
                var dataRow = sheet.CreateRow(rowIndex + 1);
                for (int column = 0; column < table.Columns.Count; column++)
                {
                    if (types !=null && types.Count == table.Columns.Count)
                    {
                        var cell = dataRow.CreateCell(column);
                        var type = GetCellType(types[column]);
                        cell.SetCellType(type);
                        if ( type == NPOI.SS.UserModel.CellType.Numeric)
                        {
                            cell.CellStyle.DataFormat= HSSFDataFormat.GetBuiltinFormat("0");
                            cell.SetCellValue(double.Parse(table.Rows[rowIndex][column].ToString()));
                        }
                        else
                        {
                            cell.SetCellValue(table.Rows[rowIndex][column].ToString());
                        }
                        
                    }else
                        dataRow.CreateCell(column).SetCellValue(table.Rows[rowIndex][column].ToString());
                }
            }

            FileStream fs = new FileStream(filePath, FileMode.Create);
            workbook.Write(fs);
            fs.Close();

            return new FileStreamResult(new FileStream(filePath, FileMode.Open), "application/vnd.ms-excel; charset=UTF-8");
        }

XSSF Sample

        protected ActionResult ExcelView_XSSF(DataTable table, List<Type> types)
        {
            SetHeaders("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "xlsx");
            string folderPath = Server.MapPath("/temp");
            string filePath = Path.Combine(folderPath, "{0}.xlsx".Fill(Guid.NewGuid().ToString()));
            if (!Directory.Exists(folderPath))
            {
                throw new Exception("Folder {0} doesn't exist. This folder is required for temporary server-side document storage. Please create this document in your application root.".Fill(folderPath));
            }

            var workbook = new XSSFWorkbook();
            //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
            var sheet = workbook.CreateSheet("Report");

            var headerRow = sheet.CreateRow(0);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                headerRow.CreateCell(i).SetCellValue(table.Columns[i].Caption);
            }

            // Populate worksheet with the table's data
            for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
            {
                var dataRow = sheet.CreateRow(rowIndex + 1);
                for (int column = 0; column < table.Columns.Count; column++)
                {
                    if (types != null && types.Count == table.Columns.Count)
                    {
                        var cell = dataRow.CreateCell(column);
                        var type = GetCellType(types[column]);
                        cell.SetCellType(type);
                        if (type == NPOI.SS.UserModel.CellType.Numeric)
                        {
                            cell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
                            cell.SetCellValue(double.Parse(table.Rows[rowIndex][column].ToString()));
                        }
                        else
                        {
                            cell.SetCellValue(table.Rows[rowIndex][column].ToString());
                        }
                    }
                    else
                        dataRow.CreateCell(column).SetCellValue(table.Rows[rowIndex][column].ToString());
                }
            }

            FileStream fs = new FileStream(filePath, FileMode.Create);
            workbook.Write(fs);
            fs.Close();

            return new FileStreamResult(new FileStream(filePath, FileMode.Open), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        }

Conclusion

You can use this code for export .xls and .xlsx.