Hi Code Project community,
first of all, sr for my bad english, its not my former language, but i'll try my best to explain to u my problem.
I'm accessing an existing Excel "*.xls" with the Microsoft.Office.Interop.Excel.Application().
Here i want to fill in a Cell some Data.
As Example i want to fill in "-1.00", and in another one "1.00". That all works fine and ist no Problem.
I can achieve this with
1) Selecting a range, edit the rande, and set the modified range into the excel
2) Directly edit the cell
Works all fine.
But now i want the following. I have an excel where are some validations in each cell. As excample a Cell has a validation, that only positiv numbers are allowed. When i try to fill in this cell the value "-1.00", i will get notified in Excel 2007 that this cell needs a positiv value.
When i try to fill in "-1.00" in this cell via my code, even with range or directly edit the cell, the progress works fine, "-1.00" is written into the cell and the sheet is successfully saved.
When i open the excel then with Excel 2007, i see the value "-1.00". When i then try to do the same directly in Excel 2007, it says again that this value isn't possible, it needs to be positive.
So what do i need now is, how can i get the same response in my code. I need to have on either the range modiffication or the direct cell editing a response that this isn't allowed. Or if there are any other ways to get this all is possible i just need to react on this behavior in my code too.
Maybe these rules can be read through any COM Object or another write method would get this response. I Don't know.
So please help solving this problem, and if u are having troubles understanding me just say it, i'll try to explain it better.
FileInfo fi = new FileInfo(@"C:\test.xls");
var application = new Microsoft.Office.Interop.Excel.Application();
var workbook = application.Workbooks.Open(fi.FullName);
foreach(Worksheet worksheet in workbook.Worksheets)
{
if(worksheet.Name.Equals("Test"))
{
worksheet.Cells[4,5] = -1.0;
break;
}
}
workbook.Save();
workbook.Close();
Marshal.ReleaseCommObject(workbook);
TIA Synergi