如何使用c#在excel中使用范围公式 [英] how to aplly formulas on range in excel using c#
问题描述
您好,
我有一个类似下面给出的VB代码。
我正在尝试在c#中实现相同的功能。但无法在VB中找到替换功能。
VB代码
<前lang =vb>索引= 2;
formula = 8.09331951577158-3.45596769548775E-02 * X ^ 1 + 1.79729745455284E-05 * X ^ 2 -3.31696353974908E-09 * X ^ 3 + 2.77566533348736E-13 * X ^ 4-8.40169194251658E-18 * X ^ 5\" 跨度>;
设置 xRange = vSheet.Range(vSheet.Cells( 1 ,(索引* 4 ) - 1 ),vSheet.Cells( 500 ,(索引* 4 ) - 1 ))
设置 yRange = vSheet.Range(vSheet.Cells( 1 ,(Index * 4 )),vSheet.Cells( 500 ,(索引* 4 )))
xMinVal = 390
xMaxVal = 13400
step =(xMaxVal - xMinVal)/(500- 1 )
Xvalue( 1 , 1 )= xMinVal
对于 i = 2 要 500 - 1
Xvalue(i, 1 ) = Xvalue(i - 1 , 1 )+ step
下一步 i
Xvalue( 500 , 1 )= xMaxVal
xRange.value = Xvalue
vSheet.Names.Add name:= X&指数& P,RefersTo:= xRange
yRange = =&替换(公式, X, X& Index& P)
c#代码
xRange =(Excel.Range )newWorksheet.get_Range((Excel.Range)newWorksheet.Cells [ 1 ,( 2 * 4 ) - 1 ],(Excel.Range)newWorksheet.Cells [NB_POINTS_POLYNOME,( 2 * 4 ) - 1 ]);
yRange =(Excel.Range)newWorksheet.get_Range((Excel.Range)newWorksheet.Cells [ 1 ,( 2 * 4 )],(Excel.Range)newWorksheet.Cells [NB_POINTS_POLYNOME,( 2 * 4 )]);
step =(xmax - xmin)/(NB_POINTS_POLYNOME - 1 );
xValue [ 1 , 0 ] = xmin;
for ( int i = 2 ; i < 500 ; i ++)
{
xValue [i, 0 ] = xValue [(i - 1 ), 0 ] +步;
}
xValue [NB_POINTS_POLYNOME-1, 0 ] = xmax;
xRange.Value2 = xValue;
newWorksheet.Names.Add( X,xRange);
yRange =(Excel.Range)newWorksheet.Range.Replace(formula, X , X, 2 ,类型.Missing,Type.Missing,Type.Missing,Type.Missing);
我无法检索Y范围值。我没有得到如何在VB中替换公式,以便正确检索Yrange。
提前谢谢。
Jyoti X&指数& P是基本的字符串连接
http://msdn.microsoft.com/en-us/library/te2585xw.aspx [ ^ ]
鉴于Index = 2字符串是X2P,在两个地方更改它使用它
替换:
yRange =(Excel.Range)newWorksheet.Range.Replace(formula, X, X, 2 ,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
with:
yRange = = +替换(公式, X,跨度> <温泉n class =code-string> X + Index.ToString()+ P);
会给你新的公式
Hello,
I have a VB code like the one given below.
I'm trying to achieve the same functionality in c#. But could not find the Replace function as in VB.
VB Code
Index = 2;
formula = "8.09331951577158-3.45596769548775E-02*X^1+1.79729745455284E-05*X^2-3.31696353974908E-09*X^3+2.77566533348736E-13*X^4-8.40169194251658E-18*X^5";
Set xRange = vSheet.Range(vSheet.Cells(1, (Index * 4) - 1), vSheet.Cells(500, (Index * 4) - 1))
Set yRange = vSheet.Range(vSheet.Cells(1, (Index * 4)), vSheet.Cells(500, (Index * 4)))
xMinVal = 390
xMaxVal = 13400
step = (xMaxVal - xMinVal) / (500- 1)
Xvalue(1, 1) = xMinVal
For i = 2 To 500 - 1
Xvalue(i, 1) = Xvalue(i - 1, 1) + step
Next i
Xvalue(500, 1) = xMaxVal
xRange.value = Xvalue
vSheet.Names.Add name:="X" & Index & "P", RefersTo:=xRange
yRange = "=" & Replace(Formula, "X", "X" & Index & "P")
c# Code
xRange = (Excel.Range)newWorksheet.get_Range((Excel.Range)newWorksheet.Cells[1, (2 * 4) - 1], (Excel.Range)newWorksheet.Cells[NB_POINTS_POLYNOME, (2 * 4) - 1]);
yRange = (Excel.Range)newWorksheet.get_Range((Excel.Range)newWorksheet.Cells[1, (2 * 4)], (Excel.Range)newWorksheet.Cells[NB_POINTS_POLYNOME, (2 * 4)]);
step = (xmax - xmin) / (NB_POINTS_POLYNOME - 1);
xValue[1,0] = xmin;
for (int i = 2; i < 500; i++)
{
xValue[i,0] = xValue[(i - 1),0] + step;
}
xValue[NB_POINTS_POLYNOME-1,0] = xmax;
xRange.Value2 = xValue;
newWorksheet.Names.Add("X", xRange);
yRange = (Excel.Range)newWorksheet.Range.Replace(formula, "X", "X", 2, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
I am unable to retrieve Y range values. I am not getting how to replace the formula as in VB so that Yrange is retrieved correctly.
Thanks in advance.
Jyoti
"X" & Index & "P" is a string concatention in basic
http://msdn.microsoft.com/en-us/library/te2585xw.aspx[^]
Given Index = 2 the string is "X2P" change it in the two places it is used
Replace:
yRange = (Excel.Range)newWorksheet.Range.Replace(formula, "X", "X", 2, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
with:
yRange = "=" + Replace(formula, "X", "X" + Index.ToString() + "P");
which will give you new formula
这篇关于如何使用c#在excel中使用范围公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!