Error: The supplied data appears to be in the Office 2007+ XML

Jak
Jak
Member
364 Points
152 Posts

I have following code for excel file processing

string fileBase64 =// fileBase64 format data 
string fileName = "mySheet.xlsx"; // "mySheet.xls"
byte[] bytes = Convert.FromBase64String(fileBase64);
if (fileName.IndexOf(".xlsx") > -1 || fileName.IndexOf(".xls") > -1)
{
  using (MemoryStream ms = new MemoryStream(bytes))
  {
    NPOI.HSSF.UserModel.HSSFWorkbook hSSFWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(ms);
    NPOI.SS.UserModel.ISheet sheet = hSSFWorkbook.GetSheetAt(0);
    for (int row = 1; row <= sheet.LastRowNum; row++)
    {
       var sheetRow = sheet.GetRow(row);
       if (sheetRow != null)
       {
         var cell = sheetRow.GetCell(0);
         //Other processing on this cell
      }
    }
  }
}

It is working fine for .xls file. But when the excel with extension '.xlsx' giving following error:

The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)

Have any one know how to solve it?

Views: 10358
Total Answered: 3
Total Marked As Answer: 2
Posted On: 06-Oct-2017 04:45

Share:   fb twitter linkedin
I'm getting same error...
 - beginer  06-Oct-2017 06:05
see on stackoverflow https://stackoverflow.com/questions/31844308/java-poi-the-supplied-data-appears-to-be-in-the-office-2007-xml
 - hackj  06-Oct-2017 06:12
Your code uses HSSF, so it will only work with the older versions .xls (binary) files only.
 - hambi  11-Oct-2017 03:44
Answers
Brian
Brian
Participant
570 Points
75 Posts
         

Use WorkbookFactory as:

using NPOI.SS.UserModel;
string fileBase64 =// fileBase64 format data 
string fileName = "mySheet.xlsx"; // "mySheet.xls"
byte[] bytes = Convert.FromBase64String(fileBase64);
if (fileName.IndexOf(".xlsx") > -1 || fileName.IndexOf(".xls") > -1)
{
  using (MemoryStream ms = new MemoryStream(bytes))
  {
      IWorkbook wb = NPOI.SS.UserModel.WorkbookFactory.Create(ms);
      NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
      for (int row = 1; row <= sheet.LastRowNum; row++)
      {
        var sheetRow = sheet.GetRow(row);
        if (sheetRow != null)
        {
            var cell = sheetRow.GetCell(0);
            //Other processing on this cell
        }
      }
  }
}
Posted On: 06-Oct-2017 06:15
hackj
hackj
12 Points
1 Posts
         

The code explicitly requests HSSF, so it will only work with the older versions .xls (binary) files.

If you want, you can code POI to auto-detect which file type you have, and pick the appropriate one of HSSF or XSSF for your case. However, to do that you need to change your code slightly, and use interfaces rather than concrete classes (so your code works whether you get a HSSF or XSSF object)

Replace following line of code

NPOI.HSSF.UserModel.HSSFWorkbook hSSFWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(ms);

with

IWorkbook hSSFWorkbook = NPOI.SS.UserModel.WorkbookFactory.Create(ms);
Posted On: 11-Oct-2017 03:51
Kaith
Kaith
4 Points
2 Posts
         

Thanks, it works for me.

Posted On: 19-Feb-2019 07:57
 Log In to Chat