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.

Thu Jan 18, 2018 9:02 am

Hello E-ICEBLUE Team,
i have a strange bug in my application written in C#.
In my application i copy the formulas from one row into another row with the FormulaR1C1-Property to ensure that the formulas keep their relative relationships.

Now the strange part: If i copy the formula:
=(SUMIF('Listungsveränderung'!R22C5:R621C5,'Customer_monthly'!RC3,'Listungsveränderung'!R22C16:R621C16))

into another cell it results to (GERMAN):
=(SUMMEWENN(Listungsveränderung!$E$405:$E$621;Customer_monthly!$C22;Listungsveränderung!$P$405:$P$621))

whereas it should be:
=(SUMMEWENN(Listungsveränderung!$E$22:$E$621;Customer_monthly!$C22;Listungsveränderung!$P$22:$P$621))

i makred the relevant parts in bold. It changes the row from 22 to 405 for no reason. :cry:

Thanks in advance.
Oliver Haase

efuture
 
Posts: 7
Joined: Mon Dec 18, 2017 8:19 am

Thu Jan 18, 2018 9:28 am

Hello,

Thanks for your inquiry. To help us investigate the issue, would you please share us with your Excel file and code you are using?

Best regards,
Simon
E-iceblue support team
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Thu Jan 18, 2018 2:22 pm

Hello,
unfortunately i can not post the excel-sheet because it is from a customer and contains sensible data.
Also the software is confidential and complex. I know it is hard for you to help me in this situation, but i try to gather all strange events:

If i manipulate ANY cell in the sheet and calling InsertRow all formulas with =SUMIF('Listungsveränderung'!R22C5:R621C5,'Customer_monthly'!RC3,'Listungsveränderung'!R22C16:R621C16))
will turn into:
=(SUMIF('Listungsveränderung'!R405C5:R621C5,'Customer_monthly'!RC3,'Listungsveränderung'!R22C16:R621C16))
the row will set to the last filled cell in the row?!

So why does changing a cell in the worksheet a different cell too?

i tried everything from repairing the excel fill to downgrading the Spire.XLS library to different versions (currently using the latest stable via NuGet).

I also noticed that Spire.XLS can not parse R1C1 formulas without row index for example:
Code: Select all
2018-01-18 15:04:16,523 [6] DEBUG Smitty.Services.Impl.Mutations.AddProductCustomerMonthly [(null)] - Kopiere R1C1 Column=Z: =+IFERROR(VLOOKUP(RC3,'ACT18'!C6:C19,MATCH('Customer_monthly'!R15C[-5],'ACT18'!R5C6:R5C19,False),False),0)
Exception thrown: 'spr2087' in Spire.XLS.dll
2018-01-18 15:04:16,535 [6] ERROR Smitty.Services.Impl.Mutations.AddProductCustomerMonthly [(null)] - Formula-Error
spr2087: C6:C19 is not valid named range
   bei spr4785.0(Int32 A_0, String A_1, ParseParameters A_2, Dictionary`2 A_3, Int32 A_4, ParseFormulaOptions A_5)
   bei spr4785.0(String A_0, ParseParameters A_1, Dictionary`2 A_2, Int32 A_3, ParseFormulaOptions A_4, Int32 A_5)
   bei spr4785.0(String A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
   bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions& A_3, ParseParameters A_4)
   bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
   bei spr4785.0(ParseFormulaOptions A_0, Dictionary`2 A_1, ParseParameters A_2, ExcelFunction A_3)
   bei spr4785.0(ExcelFunction A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
   bei spr4785.0(Dictionary`2 A_0, Int32 A_1, ParseFormulaOptions A_2, ParseParameters A_3)
   bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions& A_3, ParseParameters A_4)
   bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
   bei spr4785.0(ParseFormulaOptions A_0, Dictionary`2 A_1, ParseParameters A_2, ExcelFunction A_3)
   bei spr4785.0(ExcelFunction A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
   bei spr4785.0(Dictionary`2 A_0, Int32 A_1, ParseFormulaOptions A_2, ParseParameters A_3)
   bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions& A_3, ParseParameters A_4)
   bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
   bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions& A_3, ParseParameters A_4)
   bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
   bei spr4785.1(String A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
   bei Spire.Xls.Core.Spreadsheet.FormulaUtil.0(String A_0, IWorksheet A_1, Dictionary`2 A_2, Int32 A_3, Int32 A_4, Boolean A_5)
   bei Spire.Xls.Core.Spreadsheet.XlsWorksheet.0(Int32 A_0, Int32 A_1, String A_2, Boolean A_3)
   bei Spire.Xls.Core.Spreadsheet.XlsRange.set_FormulaR1C1(String value)

efuture
 
Posts: 7
Joined: Mon Dec 18, 2017 8:19 am

Fri Jan 19, 2018 9:43 am

Hello,

Kindly note that Spire.Xls mimics the same behavior as MS Excel does, if you do the same behavior in Excel, the row number in the sumif fomular will change.
But for the issue that Spire.XLS can not parse R1C1 formulas without row index, could you please share us the document and the codes that can demonstrate the issue you were trying?
You could send the information to us(Support@e-iceblue.com) via Email.

Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Fri Jan 19, 2018 10:06 am

Hello,
i created a minimal C#-Project for demonstrating the bug.
Inside the zip you will find the "Mappe1.xlsx"-Excel file.
Inside the application i inserting a row and the absolute formula inside N21 is changed. Also note that other cells changed.

:!: NOTE: IT DOES NOT SHOWING THE R1C1-Parsing BUG. I shows the wrong behaviour of adding a row into the sheet.
It should not change the ABSOLUTE rows inside the Formulas.

Output:
Code: Select all
Before: =SUMIF(R22C3:R79C3,RC2,R22C:R79C)
After: =SUMIF(R77C3:R79C3,RC2,R77C:R79C)
Attachments
SpireBug.zip
(360.57 KiB) Downloaded 359 times

efuture
 
Posts: 7
Joined: Mon Dec 18, 2017 8:19 am

Mon Jan 22, 2018 2:43 am

Hello,

Thanks for your information. I have noticed the issue and logged it into our bug tracking system. If there is any update, we will let you know. We apologize for the inconvenience.

Sincerely,
Simon
E-iceblue support team
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Fri Feb 02, 2018 6:47 am

Hello,

Glad to inform you that the formula issue has been fixed. Welcome to download Spire.XLS Pack Hotfix Version:7.12.150.
On the other hand, we find that there will be a warning when open the result Excel file and our DEV team are looking into it. If any update, we will inform you.

Best regards,
Simon
E-iceblue support team
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Fri Feb 09, 2018 7:44 am

Hello,

Greeting from E-iceblue.
Has the hotfix resolved your issue?
Your feedback will be greatly appreciated.

Best regards,
Simon
E-iceblue support team
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Fri Feb 09, 2018 8:04 am

Hello,
thanks for your response and your support.
I'm glad that your dev team fixed this issue that fast.
I will test the hotfix and will report the results to you.
Thanks.

efuture
 
Posts: 7
Joined: Mon Dec 18, 2017 8:19 am

Fri Feb 09, 2018 9:05 am

Hello,

Thanks for your reply. I am looking forward to your feedback.

Best regards,
Simon
E-iceblue support team
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Fri Feb 09, 2018 3:30 pm

Dear E-ICEBLUE team,

I am using excel Price formula but it return same value for different bond.
For example
setlment date 30/12/2017
Maturity date 30/08/2018
coupon rate 12%
yield 12.53% , 11%, 10% etc
redumption value 100
frequency 2
basis 1
all the iteration i am having same price 99.656093
if i change coupon and yield but having same price.

my example code is
using System.Drawing;
using Spire.Xls;
using System;

namespace Create_Excel_File
{
class Program

{
static void Main(string[] args)
{
DateTime setl = Convert.ToDateTime("30/12/2017");
DateTime mat = Convert.ToDateTime("30/08/2018");
double cpn = Convert.ToDouble((Convert.ToDecimal(12) / 100));//copRate
double yld = Convert.ToDouble((Convert.ToDecimal(12.49) / 100));
//Initialize a new Workboook object
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xls");
//Get the first worksheet

Worksheet sheet = workbook.Worksheets[0];

//Write string values in a cell

sheet.Range["A1"].DateTimeValue = setl;
sheet.Range["A2"].DateTimeValue = mat;
sheet.Range["A3"].NumberValue = cpn;
sheet.Range["A4"].NumberValue = yld;
sheet.Range["A5"].NumberValue = 100;
sheet.Range["A6"].NumberValue = 2;
sheet.Range["A7"].NumberValue = 1;
//sheet.Range["A8"].Formula= "=PRICE(A1,A2,A3,A4,A5,A6,A7)";
//Save workbook to disk
//var formule = "=PRICE(A1,A2,A3,A4,A5,A6,A7)";
//workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003);

try

{
workbook.InitCalcEngine();
workbook.CalculationMode = ExcelCalculationMode.Auto;
// workbook.CalculateAllValue();
Console.WriteLine(workbook.ActiveSheet.GetFormulaNumberValue(8,1));

}

catch(Exception ex) {

throw new Exception(ex.Message);
}


}

}

}
Attachments
Sample.7z
(4.72 KiB) Downloaded 241 times

syed7866
 
Posts: 6
Joined: Thu Feb 08, 2018 9:04 am

Sun Feb 11, 2018 10:07 am

Dear syed7866,

Thanks for your inquiry.
Note the two codes below were used for old calculative engine.
Code: Select all
workbook.InitCalcEngine();
workbook.CalculationMode = ExcelCalculationMode.Auto;

We had adjusted the calculation method, now please use the new method workbook.CalculateAllValue() before getting the formula value. I have tested your case with the latest Spire.XLS Pack Version:8.0. The code workbook.ActiveSheet.GetFormulaNumberValue(8, 1) could return correct value, please try to use this version.
Code snippet:
Code: Select all
            try
            {
                //workbook.InitCalcEngine();
                //workbook.CalculationMode = ExcelCalculationMode.Auto;
                workbook.CalculateAllValue();
                Console.WriteLine(workbook.ActiveSheet.GetFormulaNumberValue(8, 1));
            }


Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Mon Feb 12, 2018 8:34 am

Dear Team,

Thanks alot for your reply. I am facing issue in DateTime format.



Regards,
Syed Salahuddin

syed7866
 
Posts: 6
Joined: Thu Feb 08, 2018 9:04 am

Mon Feb 12, 2018 8:52 am

Dear syed7866,

Thanks for your information.
What is DateTime format issue ? Could you please describe your issue in detail and provide the expected result ?

Thanks,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Mon Feb 12, 2018 9:00 am

Dear Team,

i am using dd/mm/yyyy as date format following are the varibales values.
setlDate ="30/12/2017"
maturityDate="30/08/2018"
cpn=0.12
yield =0.1195
redumption =100
frequency =2
intbasis =1

i got output as NAN from program, but actual result is (price =99.99247412)

Regards,
Syed Salahuddin

syed7866
 
Posts: 6
Joined: Thu Feb 08, 2018 9:04 am

Return to Spire.XLS