NPOI is a great solution for working with XLS documents, but what happens when you need to read in XLSX documents as part of your solution? The easiest solution I could think of was to use OLEDB to extract out all the data (the data, the whole data and nothing but the data) from a worksheet into a new NPOI document.

If you need more support than just reading in data from Excel 2007+ (XLSX), you should look at using the ExcelPackage project that is hosted at CodePlex.

The benefit to my approach, is that your able to use your existing NPOI solutions when data is provided in XLSX format. This is far from full Excel 2007+ support, but if your applicaitons only requirement is “reading in data” then your problem is solved. NPOI v1.6 is supposed to fully support the XLSX format, until then this function will provide basic XLSX support.

Functions used to convert XLSX document to a NPOI document
    /// <summary>
    /// Render a Excel 2007 (xlsx) Worksheet to NPOI Excel 2003 Worksheet, all excel formatting
    /// from XLSX will be lost when converted.  NPOI roadmap shows v1.6 will support Excel 2007 (xlsx).
    /// NPOI Roadmap  : http://npoi.codeplex.com/wikipage?title=NPOI%20Road%20Map&referringTitle=Documentation
    /// NPOI Homepage : http://npoi.codeplex.com/
    /// </summary>
    /// <param name="excelFileStream">XLSX FileStream</param>
    /// <param name="sheetName">Excel worksheet to convert</param>
    /// <returns>MemoryStream containing NPOI Excel workbook</returns>
    public static Stream ConvertXLSXWorksheetToXLSWorksheet(Stream excelFileStream, string sheetName)
    {
        // Temp file name
        string tempFile = HttpContext.Current.Server.MapPath("~/uploads/" + HttpContext.Current.Session.LCID + ".tmp");

        // Temp data container (using DataTable to leverage existing RenderDataTableToExcel function)
        DataTable dt = new DataTable();

        try
        {
            // Create temp XLSX file
            FileStream fileStream = new FileStream(tempFile, FileMode.Create, FileAccess.Write);

            const int length = 256;
            Byte[] buffer = new Byte[length];
            int bytesRead = excelFileStream.Read(buffer, 0, length);

            while (bytesRead > 0)
            {
                fileStream.Write(buffer, 0, bytesRead);
                bytesRead = excelFileStream.Read(buffer, 0, length);
            }

            excelFileStream.Close();
            fileStream.Close();

            // Read temp XLSX file using OLEDB
            // Tested on Vista & Windows 2008 R2
            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;Data Source=" + tempFile + @";Extended Properties=Excel 12.0;"))
            {
                con.Open();
                string sql = String.Format("SELECT * FROM [{0}$]", sheetName);
                OleDbDataAdapter da = new OleDbDataAdapter(sql, con);

                da.Fill(dt);
            }
        }
        finally
        {
            // Make sure temp file is deleted
            File.Delete(tempFile);
        }

        // Return a new POI Excel 2003 Workbook
        return RenderDataTableToExcel(dt);
    }

    /// <summary>
    /// Render DataTable to NPOI Excel 2003 MemoryStream
    /// NOTE:  Limitation of 65,536 rows suppored by XLS
    /// </summary>
    /// <param name="sourceTable">Source DataTable</param>
    /// <returns>MemoryStream containing NPOI Excel workbook</returns>
    public static Stream RenderDataTableToExcel(DataTable sourceTable)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        MemoryStream memoryStream = new MemoryStream();
        // By default NPOI creates "Sheet0" which is inconsistent with Excel using "Sheet1"
        HSSFSheet sheet = workbook.CreateSheet("Sheet1");
        HSSFRow headerRow = sheet.CreateRow(0);

        // Header Row
        foreach (DataColumn column in sourceTable.Columns)
            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

        // Detail Rows
        int rowIndex = 1;

        foreach (DataRow row in sourceTable.Rows)
        {
            HSSFRow dataRow = sheet.CreateRow(rowIndex);

            foreach (DataColumn column in sourceTable.Columns)
            {
                dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
            }

            rowIndex++;
        }

        workbook.Write(memoryStream);
        memoryStream.Flush();
        memoryStream.Position = 0;

        return memoryStream;
    }

NOTE: You could convert an entire workbook (multiple work sheets) into a new NPOI workbook if you looped over each work sheet in the XLSX document. Since all my tools are based on using a single work sheet, I’ll leave the workbook conversion up to you.

Example using XLSX function
            if (FileUpload.PostedFile.ContentLength > 0)
            {
                Stream uploadFileStream = FileUpload.PostedFile.InputStream;

                // If the file uploaded is "XLSX", convert it's Sheet1 to a NPOI document
                if (FileUpload.PostedFile.FileName.EndsWith("xlsx"))
                {
                    uploadFileStream = ExcelHelper.ConvertXLSXWorksheetToXLSWorksheet(uploadFileStream, "Sheet1");
                }
            }

** All your NPOI logic that adds columns, changes cell/row formatting, etc… will now work with the data extracted from the XLSX document.