Office.Interop Excel关于单元格验证的注意事项 [英] Office.Interop Excel Care about Validation of Cells

问题描述

Hi Code Project社区，

首先，sr是我的英语不好，不是我以前的语言，但是我会尽力向您解释我的问题.

我正在使用Microsoft.Office.Interop.Excel.Application()访​​问现有的Excel"* .xls".

在这里我要填写一些数据.

作为示例，我想填写"-1.00"，并填写另一个"1.00".一切正常，没有问题.

我可以用
实现
1)选择一个范围，编辑位置，然后将修改后的范围设置为excel
2)直接编辑单元格

一切正常.

但是现在我想要以下内容.我有一个excel，每个单元格中都有一些验证.作为一个例子，一个单元格具有一个验证，即只允许使用正数.当我尝试在该单元格中填写值"-1.00"时，我将在Excel 2007中得到通知，该单元格需要一个正值.

当我尝试通过我的代码在该单元格中填充"-1.00"时，即使使用范围或直接编辑该单元格，进度也可以正常工作，将"-1.00"写入该单元格并成功保存工作表.

当我用Excel 2007打开Excel时，我看到了值"-1.00".然后，当我尝试直接在Excel 2007中直接执行此操作时，再次表示该值是不可能的，它必须为正.

因此，我现在需要的是如何在代码中获得相同的响应.我需要在范围简化或直接单元格编辑中做出这样的响应，即不允许这样做.或者，如果还有其他方法可以解决所有问题，那么我也只需要对代码中的这种行为做出反应即可.

也许可以通过任何COM对象读取这些规则，或者其他写入方法将获得此响应.我不知道.

因此，请帮助解决此问题，如果您在理解我时遇到困难，请说出来，我会尽力解释一下.

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

TIA Synergi

推荐答案

首先，请阅读以下文章:
http://msdn. microsoft.com/en-us/library/microsoft.office.interop.excel.range.validation%28v=office.11​​%29.aspx [ http://msdn.microsoft. com/en-us/library/microsoft.office.interop.excel.validation_properties％28v = office.11​​％29.aspx [

First of all, read these articles:
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.validation%28v=office.11%29.aspx[^]
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.validation_properties%28v=office.11%29.aspx[^]

If you would like to use cell validation, use Validation object.

FileInfo fi = new FileInfo(@"C:\test.xls");

var oApp = new Microsoft.Office.Interop.Excel.Application();
var oWbk = oApp.Workbooks.Open(fi.FullName);
var oWsh = oWbk.Worksheets("Test");
var oVal = oApp.Range.Validation

oWsh.Cells[4,5] = -1.0;
oVal = oWsh.Cells[4,5].Validation;
if (Not oVal.Value) //Value returns TRUE or FALSE; FALSE -> inserted value is not valid
{
    //insert correct value or use ClearContents method, if blanks are allowed
    //oWsh.Cells[4,5] = ""; 
    oWsh.Cells[4,5].ClearContents();
}

workbook.Save();
workbook.Close();
Marshal.ReleaseCommObject(workbook);

使用尝试...捕获...结束尝试 [ ^ ]块以捕获错误.

Use Try... Catch... End Try[^] block to catch errors.

这篇关于Office.Interop Excel关于单元格验证的注意事项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！