[NPOI.XSSF.UserModel] XLSX file : repair needed when using Double in SetCellValue()

Feb 22, 2013 at 4:02 PM
When exctracting data form a SQL Table, I store data in Object variables in order to be sure not to loose data type.

Then I made a method to check Object data type and transtype automatically the object to the most fitted type in order to use in SetCellValue()

this works on some point, but in most cases, when opening the XLSX file, Excel 2010 ask for a file repair. As I don't have this version on my computer, I can't check differences between files, and Excel 2003 refuses to convert and open the file.

I tried some things but the only one that is working is to transtype Double values in another type (Interger and String work)

Thanks in advance for any hint you can give me about that ! :)

Regards,
Mukuahya
Mar 8, 2013 at 7:03 AM
Hi,

i'm experiencing the same Problem. As soon as I export a double value, the file seems to get corrupted. I also noticed, that the value is then inserted as text, not as a number. Which means I get the little green icon which tells me, that I have stored a number as text.

Please provide a fix for this issue.

Thanks
Coordinator
Mar 12, 2013 at 9:51 PM
Hi Mukuahya,

Can you narrow down the issue to a Double value which may break the file via SetCellValue? This should be tested in the test cases.

Btw, why do you think this is an XSS attack? XSS attack is about stealing the authentication from a web UI. It's nothing about Excel file export.
Mar 13, 2013 at 10:04 AM
Easy way to reproduce (SetCellValueInXlsx sample modified to provoque the error) :
using System;
using System.Collections.Generic;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;

namespace NPOI.Examples.XSSF.SetCellValuesInXlsx
{
    class Program
    {
        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet1 = workbook.CreateSheet("Sheet1");
            sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
            int x = 1;
            for (int i = 1; i <= 15; i++)
            {
                IRow row = sheet1.CreateRow(i);
                for (int j = 0; j < 15; j++)
                {
                    //row.CreateCell(j).SetCellValue(x++);
                    Double dRandom = new Random(x++).NextDouble();
                    row.CreateCell(j).SetCellValue(dRandom);
                }
            }
            FileStream sw = File.Create("test.xlsx");
            workbook.Write(sw);
            sw.Close();
        }
    }
}
When one tries to open the result file, Excel 2010 asks for a repair confirmation, and Excel 2003 refuses to open the file.

Sorry for the title, I just realised that I didn't put the whole library name. I naturally refere to the XSSF namespace. I will try to change this title to make things clear for everyone ;)
Mar 18, 2013 at 12:49 PM
I have the same problem, please provide fix.
BR
Mar 27, 2013 at 6:08 PM
This seems to be internationalization problem. I got this work by rounding the numbers.

But maybe changing the current culture could be better solution (haven't tested)

Thread.CurrentThread.CurrentCulture = Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-US");
Apr 3, 2013 at 3:51 PM
Hi,

Thanks for your feedback. I will try this fix as soon as possible, but I also need to check impacts that this locale change will have on the file.

I keep you updated.
Jul 24, 2013 at 9:37 AM
Edited Jul 24, 2013 at 9:37 AM
Hello,

Sorry for the long wait, I was stuck on another big project and could not make the test.

Here what I did :
1- When instanciating the new XSSFWorkbook, I added this command :
Thread.CurrentThread.CurrentCulture = New CultureInfo("en-US")
2- In order to keep the application "clean", after writing the final file to the disk, I added this command:
Thread.CurrentThread.CurrentCulture = CultureInfo.InstalledUICulture
Everything is working alright and there is no more file corruption

Thanks for the support, I hope my thread will help some other people :)
Marked as answer by tonyqus on 11/17/2013 at 4:17 PM
Dec 6, 2013 at 2:53 PM
Edited Dec 6, 2013 at 3:01 PM
I faced the same problem and narrowed it down a bit.

On my German system with German culture info (i.e. comma as decimal separator), the XML files will be corrupt, regardless of the data type I am trying to write. Diff-ing the worksheet files revealed that this has to do with the basic sheet info being written at the end of the worksheets XML. The code generated on a German machine will be
<pageMargins left="0,7" right="0,7" top="0,75" bottom="0,75" header="0,3" footer="0,3"></pageMargins></worksheet> 
instead of
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"></pageMargins></worksheet> 
Obviously this will throw an error with OpenOffice or Excel.

So I will use a forced US culture info for writing the data streams. This should take care of all file stream issues.
May 21, 2014 at 10:28 AM
Edited May 21, 2014 at 2:59 PM
For this problem, I proposed this simple correction : https://github.com/tonyqus/npoi/pull/21/files

Meanwhile, you can do this for an XSSFWorkbook :
cell.SetCellType(CellType.Formula);
cell.SetCellValue(myDouble.ToString(CultureInfo.InvariantCulture));
cell.SetCellType(CellType.Numeric);