使用excel.interop替换excel中的单元格值
我想扫描一个Excel表格,并用零代替社会安全号码的任何出现...我想使用Excel.Interop做到这一点,如果可能的话,但我现在对任何事情都是开放的......这里是我的一些代码...在过去的几个月里,我在桌子上敲着头...
// Get range and convert it to a string variable Excel.Range _range =(Excel.Range)_excelApp.get_Range("A1:K1",Type.Missing); // convert the value of our cells in our range // to the string variable string myString = _range.Cells.Value2.ToString(); // match any SSN e.g. 1236780909, 123-33-2445 if (Regex.IsMatch(myString, @"bd{3}bd{2}bd{4}")); { _range.Cells.Value2 = replaceSSN; } // save our workbook with a new name and create a backup _excelWorkbook.SaveAs("Results.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // close workbook _excelWorkbook.Close(false, Type.Missing, Type.Missing); // send quit signal to app _excelApp.Quit(); // report success MessageBox.Show("File masked successfully.", "Mask Data", MessageBoxButtons.OK); // release memory //System.Runtime.InteropServices.Marshal.ReleaseComObject(_excelApp); // change label back to being blank lblActivity.Text = ""; }
问题在这里
string myString = _range.Cells.Value2.ToString();
和这里
// match any SSN e.g. 1236780909, 123-33-2445
if (Regex.IsMatch(myString, @"bd{3}bd{2}bd{4}"));
{
_range.Cells.Value2 = replaceSSN;
}
我认为你误解了Value2是什么,它是你在上面定义的范围的阵列重新布置。
Excel.Range _range =(Excel.Range)_excelApp.get_Range("A1:K1",Type.Missing);
Range2的Range属性返回值的数组。 你需要做的可能是声明一个空数组并获取该范围的Value2,循环数组中的每个项并运行正则表达式,如果它发现匹配替换数组中的项。 然后,您可以将数组设置回Range的Value2,它将更新单元格值。
编辑:请在下面找到一些示例代码
var excelApp = new Application();
excelApp.Workbooks.Open("c:Test.xls",Type.Missing,Type.Missing,
Type.Missing,Type.Missing,
Type.Missing,Type.Missing,
Type.Missing,Type.Missing,
Type.Missing,Type.Missing,
Type.Missing,Type.Missing,
Type.Missing,Type.Missing);
var ws = excelApp.Worksheets;
var worksheet =(Worksheet) ws.get_Item("Sheet1");
Range range = worksheet.UsedRange;
// In the following cases Value2 returns different types
// 1. the range variable points to a single cell
// Value2 returns a object
// 2. the range variable points to many cells
// Value2 returns object[,]
object[,] values = (object[,])range.Value2;
for (int row = 1; row <= values.GetUpperBound(0); row++)
for (int col = 1; col <= values.GetUpperBound(1); col++)
{
string value = Convert.ToString(values[row, col]);
//Also used a different regex, found yours not to match on your given criteria
if (Regex.IsMatch(value, @"^d{3}-d{2}-d{4}$"))
{
range.Cells.set_Item(row,col,"0");
}
}
excelApp.Save("C:Out.xls");
excelApp.Quit();
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(ws);
Marshal.ReleaseComObject(excelApp);
在这里你可以对代码片段有一个小的理解,但我没有尝试过,所以它可能包含语法错误。
Excel.WorkSheet currentSheet = ApplicationInstance.ActiveSheet as Excel.Worksheet;
foreach(Exce.Range r1 in currentSheet.UsedRange)
{
if(Regex.IsMatch(r1.Value2.ToString(), @"bd{3}bd{2}bd{4}")) // check r1.Value2 should not be null
{
r1.Value2 = Your_New_Value;
}
}
链接地址: http://www.djcxy.com/p/35685.html