This project is read-only.

Can't open and change data in xls file using NPOI. C#

Feb 17, 2012 at 12:35 PM

Hi,

I am trying to open one simple exel document and to change data in it. I tryed a lot ways but nothing. I made simple project only to test my code but nothing happened. My code is:

using System;
using System.Text;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                FileStream fs = new FileStream(@"D:\Book1.xls", FileMode.Open, FileAccess.Read);
                HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs);

                HSSFSheet sheet = templateWorkbook.GetSheet("Sheet1");
               
                sheet.GetRow(0).GetCell(0).SetCellValue("Drago");

                sheet.ForceFormulaRecalculation = true;
                MemoryStream ms = new MemoryStream();
                templateWorkbook.Write(ms);
            }           
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                if (null != ex.InnerException)
                {
                    Console.WriteLine("Inner Exception: " + ex.InnerException.Message);
                }
            }
            Console.ReadLine();
        }
    }
}

I only one to write string "Drago" on the sheet. I am sure that is easy but why nothing happened here?

Thanks a lot.

Feb 17, 2012 at 8:30 PM

A few things:

FileStream fs = new FileStream(@"D:\Book1.xls", FileMode.Open, FileAccess.ReadWrite);

After you initialize the workbook, make a call to fs.Close();

When you're ready to save, reopen the filestream, then do:  templateWorkbook(fs);

Then close the filestream again

Feb 19, 2012 at 12:28 PM

FileStream fs = new FileStream(@"D:\Book1.xls", FileMode.Open, FileAccess.Read);
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs);
HSSFSheet sheet = templateWorkbook.GetSheet("Sheet1");
fs.Close();
sheet.GetRow(0).GetCell(0).SetCellValue("Drago");
sheet.ForceFormulaRecalculation = true;
MemoryStream ms = new MemoryStream();
templateWorkbook.Write(ms);
ms.Close();

OK, I did it but nothing again. Can you give me correct example?

Feb 20, 2012 at 4:33 PM

I don't use MemoryStream objects for this type of stuff - I rely exclusively on FileStream.  Below is code I got to work - just changed a couple things...

FileStream fs = new FileStream(@"H:\Book2.xls", FileMode.Open, FileAccess.ReadWrite);
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs);
HSSFSheet sheet = (HSSFSheet)templateWorkbook.GetSheet("Sheet1");
fs.Close();
sheet.GetRow(0).GetCell(0).SetCellValue("Drago");
sheet.ForceFormulaRecalculation = true;
fs = new FileStream(@"H:\Book2.xls", FileMode.Open, FileAccess.ReadWrite);
templateWorkbook.Write(fs);
fs.Close();
Feb 20, 2012 at 6:39 PM

BTW, I've built a NPOI wrapper that you may find useful, either for examples, or actually to use.  I've got a bit of corresponding documentation, and a link to download at http://oopstruggles.blogspot.com/2011/12/npoi-wrapper.html

 

May 25, 2012 at 4:08 AM

@timclasson Thanks for your solution. It works. But I just don't get it why declare fs twice?

May 25, 2012 at 2:44 PM

In the code I put, I close the FileStream before specifying any changes in the HSSFSheet object.  I then reinitialize it before having the workbook object write to the Stream.  The workbook object can't write to a closed stream, and I'm not sure if FileStream has any corresponding method that opens it again, so I concluded the safest alternative was to simply reinitialize the filestream.  I'm sure there are more eloquent ways to do it, but in the wrapper I wrote for NPOI, I've been doing it like that for over a year, and haven't had any problems with it.

Feb 12, 2013 at 5:21 AM
Edited Feb 12, 2013 at 5:21 AM
@timclaason,
I really want to thank you for this information. Kept getting cannot write to closed file. This worked like magic and I truly appreciate this - enough that I registered to say thanks LOL!

P.S. I went this route because I tried 5 other methods to access excel before this and wow I found this. I did the java version yesterday, so don't know why I did not find this .net version first. Maybe need some links on the Apache POI site for this .net implementation.

Anyway thanks!
Jul 10, 2014 at 8:36 AM
This solution is still good even after 2 and a half years. Thanks timclaason