Unable to get column with MID formula

Aspose.Cells Product Family
1

I have a column in an excel file with the MID formula ( i.e. =MID(T:T,FIND(" ",T:T),LEN(T:T)) ) and when I am trying to get the data into a DataTable using ExportDataTableAsString, none of the cells have a value; it’s blank. However, when I pull up my excel file, the values are there. Can someone please help?


I don’t have to use the MID formula if it’s not supported. My goal is just to extract the second word of a string. For example column1 has “first last”, i want to add column2 and extract the word “last” into this new column (column2). Then, export my worksheet to a datatable.
2

Hi,


Please try to call Workbook.CalculateFormula() method before exporting to data table, this method will calculate all the formulas in the spreadsheet to get the calculated values.

If you still find the issue, kindly do post your template file and sample code (runnable) to reproduce the issue on our end, we will check it soon.

Thank you.
3

Hi Amjad,



I tried the Workbook.CAlculateFormula() method and I got #Value! It is still not the actual calculated value when I look in the DataTable. See below for a sample code. Please advise.

Thanks.



Code:



string filePath = “C:\Users\Test\TestFile.xlsx”;

FileStream fstream = new FileStream(filePath, FileMode.Open);

Workbook workbook = new Workbook();

workbook.Open(fstream);



Worksheet worksheet = workbook.Worksheets[0];

int newColPos = worksheet.Cells.MaxColumn + 1;

worksheet.Cells.InsertColumn(newColPos);

string colName = CellsHelper.ColumnIndexToName(newColPos);

string fullNameCol = CellsHelper.ColumnIndexToName(0);

string formula = “=MID(” + fullNameCol + “:” + fullNameCol + “, FIND(” + “”" + " “” + “,” + fullNameCol + “:” + fullNameCol + “), LEN(” + fullNameCol + “:” + fullNameCol + “))”;



colName = colName + “2”; //get second row (skip header row)

worksheet.Cells[colName].SetSharedFormula(formula, 5, 1);

workbook.CalculateFormula();



DataTable dataTable = new DataTable();

dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxRow, worksheet.Cells.MaxColumn + 1, true);

4

Hi,

Thanks for your posting and using Aspose.Cells.

I have tested your code after some modifications with the latest version: Aspose.Cells
for .NET v7.7.2.3 using your sample file and observed this issue. The MID formula is not calculated correctly.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-42447.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\TestFile.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];

int newColPos = worksheet.Cells.MaxColumn + 1;

worksheet.Cells.InsertColumn(newColPos);

string colName = CellsHelper.ColumnIndexToName(newColPos);

string fullNameCol = CellsHelper.ColumnIndexToName(0);

string formula = “=MID(” + fullNameCol + “:” + fullNameCol + “, FIND(” + “"” + " "" + “,” + fullNameCol + “:” + fullNameCol + “), LEN(” + fullNameCol + “:” + fullNameCol + “))”;


colName = colName + “2”; //get second row (skip header row)

worksheet.Cells[colName].SetSharedFormula(formula, 5, 1);

workbook.CalculateFormula();


//Save the workbook for checking

workbook.Save(“out.xlsx”);


DataTable dataTable = new DataTable();

dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxRow, worksheet.Cells.MaxColumn + 1, true);


5

I appreciate you’re looking into the issue. Thank you for your help. Keep me posted with the investigation. Thanks!

6

I also just wanted to let you know that I am trying to find alternative solutions to this problem by using LEFT/RIGHT formula instead and had no luck. For example =RIGHT(A:A, SEARCH(" ",A:A))

I’m not picky on which approach to take as long as I can get the word (2nd word) after the space into a new column. I thought I would mention this in case a fix for LEFT/RIGHT is simpler than MID. Please also let me know if you have any alternative solutions to this problem.

Thanks.

7

Hi,

Thanks for using Aspose.Cells.

As you are using shared formula, please change the formula :

string formula = “=MID(” +
fullNameCol + “2” + “, FIND(” + “”" + " “” + “,” + fullNameCol + “2” + “),
LEN(” + fullNameCol + “2” + “))”;

Everything will work fine now. And we will
also fix this issue soon.

8

Thank you. It works!. However, this formula give me a #VALUE! in the cell if there isn’t any " " found. Is there a away to find and replace #VALUE! in a column with null when this happens?

9
Hi,

paam:
Is there a away to find and replace #VALUE! in a column with null when this happens?

I think you may try to use Search options provided by Aspose.Cells to find the cells with "#VALUE!" as calculated value and then replace it with your desired value accordingly, see the document for your reference:
http://www.aspose.com/docs/display/cellsnet/Find+or+Search+Data


Also, as we are working over the issue, so we will fix the original issue soon.

Thank you.

10

Thank you, Amjad.

11

The issues you have found earlier (filed as CELLSNET-42447) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.