Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Mon Jul 10, 2017 1:46 pm

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

raka_12
 
Posts: 2
Joined: Thu Jul 06, 2017 1:28 pm

Tue Jul 11, 2017 2:52 am

Hello Rakesh Chaturvedi,

Thanks for your inquiry.
After an initial test, I have got the correct .txt file here on my side. Considering this, could you share your sample .xlsx file with us for a better investigation? You could send to us via email(support@e-iceblue.com).

Many Thanks,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Return to Spire.XLS