looking for cells

Apr 13, 2011 at 10:14 AM
Edited Apr 13, 2011 at 12:33 PM

Basically I want to open an excel file, find a cell which contains the date of a certain day (chosen in the windows form), they are all in column A. Next I want to add numbers from my forms-textbox in column C but on the same row as where the date is.

My question is how can I find the cell eg column : A ,  row : 28 which contains this date ?

I have already the following to open the excelfile :

FileStream fs = new FileStream(Server.MapPath(@"z:\liste.xls"), FileMode.Open, FileAccess.Read);
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
HSSFSheet sheet = templateWorkbook.GetSheet("Mersch");                         (this gives an error though ??)
          
sheet.GetRow(1).GetCell(1).SetCellValue(15000);


Apr 14, 2011 at 9:20 PM

Have you confirmed that the templateWorkbook is actually loading properly from the fs FileStream? I'm using the same logic that you are and the sheets are loading as you expect above.

For the dates, I'm using code similar to:

           RevisionDate = Date.FromOADate(Worksheet.GetRow(1).GetCell(1).StringCellValue)

This seems to work fine, although it's tedious to loop through multiple rows this way. This should return a valid date from an Excel version.

Apr 15, 2011 at 7:47 AM

Brimars,

 

is it possible I can see all your code to compare ?

Apr 15, 2011 at 7:57 AM

the error I got/get on Getsheet is:

Error 1 Cannot implicitly convert type 'NPOI.SS.UserModel.Sheet' to 'NPOI.HSSF.UserModel.HSSFSheet'. An explicit conversion exists (are you missing a cast?)

Apr 15, 2011 at 1:04 PM

I'll try to attach a more complete block of code later.

When you're using the system, you'll need to use the Workbook and Worksheet from either the SS or HSSF namespace; they don't seem to cleanly convert. I have been declaring mine as:

dim FS as new IO.FileStream("C:\Temp\Test.xls", IO.FileMode.Open)
dim Workbook as new HSSFWorkbook(fs)
dim Worksheet as new HSSFSheet

Worksheet = Workbook.GetSheet("Sheet 1")
Hope this helps. I'll try to post some of the actual date code later. If you're used to using the Interop, the properties and methods are somewhat different.

Apr 15, 2011 at 1:05 PM

I made something work with this code.

But why does this not work :  hssfworkbook.Write(file);  (the file is closed it says ??)

And what if the excelfile is password protected ?

 

FileStream file = new FileStream(@"z:\lijst.xls", FileMode.Open, FileAccess.ReadWrite);

            hssfworkbook = new HSSFWorkbook(file);
            Sheet sheet1 = hssfworkbook.GetSheet("Sheet1");

      
            for (int iRow = 1; iRow <= 39; iRow++)
            {
                Row row = sheet1.GetRow(iRow);
                exceldatum = sheet1.GetRow(iRow).GetCell(0).DateCellValue.ToShortDateString();
                if (exceldatum == datum)
                {
                    string formula = GetCellPosition(iRow, 0);
                    textBox1.Text = formula;

                    Row hRow = sheet1.GetRow(iRow);
                   
                    hRow.CreateCell(1).SetCellValue(Int32.Parse(textBox3.Text));
                    break;

                }
            }
          
           // hssfworkbook.Write(file);
            FileStream file2 = new FileStream(@"z:\lijst.xls", FileMode.Create);
            hssfworkbook.Write(file2);
            file2.Close();


Apr 15, 2011 at 1:17 PM

I was actually having a similar problem and chose a similar solution. I think I have a test block of code that was working with the same filestream, but I believe I explicitly close the stream even though the stream seems to be automatically closed when the workbook is initially read. Otherwise, your code looks like it should load and save the file properly.

I don't have any code yet for a password protected file as I'm still working on other portions of code. I'll be into that a little later today and I'll let you know any insight...

Sorry this isn't more help yet.