Hi,
I am trying to run an excel formula :-
string Formula = "=LEFT(TRIM(A2)&REPT(\" \",100),100) &LEFT(TRIM(B2)&REPT(\" \",100),100)";
But when I tries to get the value from the formula and dumping it to txt file, my text file just contains only "unknown formula REPT" in C#:-
I have used :-
1.
xlWorkBook.CalculateAllValue();
str2 = range[rCnt, cl + 1].FormulaStringValue;
2.
str = xlWorkBook.CaculateFormulaValue(formula).ToString();
My Code Snippet is as follows (Spire_Excel_To_Txt_E_Cube.cs):-
-------------------------------------------------------------------------------------------
using System;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.IO;
using System.Data.OleDb;
using Spire.Xls;
using Spire.License;
namespace WindowsFormsApplication4
{
public class Form1
{
static void Main(string[] args)
{
System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\u6025479\Documents\Visual Studio 2010\Projects\Project1\SimpleReport.txt");
Workbook xlWorkBook = new Workbook();
int rCnt ;
int rw = 0;
int cl = 0;
xlWorkBook.LoadFromFile("C:\\Users\\u6025479\\Documents\\Visual Studio 2010\\Projects\\Project1\\SimpleReport_Formula.xlsx");
Worksheet xlWorkSheet = xlWorkBook.Worksheets[0];
CellRange range = xlWorkSheet.Range;
rw = range.RowCount;
cl=range.ColumnCount;
string Formula = "=LEFT(TRIM(A2)&REPT(\" \",100),100) &LEFT(TRIM(B2)&REPT(\" \",100),100)";
CellRange to = xlWorkSheet.Range[2, cl + 1, rw, cl + 1];
to.Formula = Formula;
xlWorkBook.CalculateAllValue();
string str2;
for (rCnt = 2; rCnt <= rw; rCnt++)
{
str2 = range[rCnt, cl + 1].FormulaStringValue;
if (rCnt < rw)
{
file.WriteLine(str2);
}
else if (rCnt == rw)
{
file.Write(str2);
}
}
xlWorkBook.Save();
xlWorkBook.SaveToFile("C:\\Users\\u6025479\\Documents\\Visual Studio 2010\\Projects\\Project1\\SimpleReport.tmp.xlsx");
file.Close();
}
}
}
------------------------------------------------------------------------------------------
Thanks and Regards,
Rakesh Chaturvedi