0

I'm trying to convert a file from XLS to XLSX using NPOI. As I'm not aware of an explicit conversion, I wrote this first implementation going through the rows and cells and copying from one to another:

public string ConvertToXlsx(string xlsPath)
    {
        var oldWorkbook = new HSSFWorkbook(new FileStream(xlsPath, FileMode.Open));
        var oldWorkSheet = oldWorkbook.GetSheetAt(0);
        var newExcelPath = xlsPath.Replace("xls", "xlsx");
        using (var fileStream = new FileStream(newExcelPath, FileMode.Create))
        {
            var newWorkBook = new XSSFWorkbook();
            var newWorkSheet = new XSSFSheet();
            newWorkBook.Add(newWorkSheet);

            foreach (HSSFRow oldRow in oldWorkSheet)
            {
                var newRow = newWorkSheet.CreateRow(oldRow.RowNum);

                for (int ii = oldRow.FirstCellNum; ii < oldRow.LastCellNum; ii++)
                {
                    var newCell = newRow.CreateCell(ii);
                    newCell = oldRow.Cells[ii];
                }
            }

            newWorkBook.Write(fileStream);
        }

        return newExcelPath;
    }

Yet, on line var newCell = newRow.CreateCell(ii); NPOI throws a NullReferenceException With the following stack trace:

at NPOI.XSSF.UserModel.XSSFCell..ctor(XSSFRow row, CT_Cell cell)
at NPOI.XSSF.UserModel.XSSFRow.CreateCell(Int32 columnIndex, CellType type)
at NPOI.XSSF.UserModel.XSSFRow.CreateCell(Int32 columnIndex)
at Ing2Ynab.Excel.IngExcelConverter.ConvertToXlsx(String xlsPath)

Which I don't get why it's happening, as XSSFRow should be in charge of creating the CT_Cell that gets passed on to XSSFCell constructor, from what I could read in NPOIs code.

Has anyone else tried to do this and/or has fixed it?

Thanks.

danielrozo
  • 1,442
  • 1
  • 10
  • 22

1 Answers1

1

Looks like you have to explicitly call the Workbooks CreateSheet() method instead of calling .Add(). Additionally, you seem to have some out of range exceptions on your loop so keep an eye out for that.

public string ConvertToXlsx(string xlsPath)
    {
        var oldWorkbook = new HSSFWorkbook(new FileStream(xlsPath, FileMode.Open));
        var oldWorkSheet = oldWorkbook.GetSheetAt(0);
        var newExcelPath = xlsPath.Replace("xls", "xlsx");
        using (var fileStream = new FileStream(newExcelPath, FileMode.Create))
        {
            var newWorkBook = new XSSFWorkbook();
            var newWorkSheet = newWorkBook.CreateSheet("Sheet1");

            foreach (HSSFRow oldRow in oldWorkSheet)
            {
                var newRow = newWorkSheet.CreateRow(oldRow.RowNum);

                for (int ii = oldRow.FirstCellNum; ii < oldRow.LastCellNum; ii++)
                {
                    var newCell = newRow.CreateCell(ii);
                    newCell = oldRow.Cells[ii];
                }
            }

            newWorkBook.Write(fileStream);
        }

        return newExcelPath;
    }
Dustin Bui
  • 11
  • 1