Android Tutorial Read / Write Excel files on Android

Status
Not open for further replies.

gawie007

Member
Licensed User
InitializeFormula

At the end of a For...Next loop of numbers, I am trying to reference the last value written so that I do not get a spike in my Calculated data.

In Excel this works: =OFFSET(INDIRECT("RC",FALSE),-1,0) - it gets the Cell value above it.
To insert this from code, I use:
B4X:
cell.InitializeFormula(3, i+4, "=OFFSET(INDIRECT(""RC"",FALSE),-1,0)")
editSheet.AddCell(cell)
Note the double quotes around RC - to result in single, double quotes in final string.
I also tried substituting the string for a string variable:
B4X:
Dim FormulaString As String = "=OFFSET(INDIRECT(" &QUOTE & "RC" &QUOTE & ",FALSE),-1,0)"
The following error occurs when executing editSheet.AddCell(cell)

java.util.EmptyStackException
at java.util.Stack.pop(Stack.java:73)...


The same problem applies to:
B4X:
cell.InitializeFormula(3, i+4, "=SUM(B5+B6)")
cell.InitializeFormula(3, i+5, "=SUM(D6:D8)")
When using this (single quotes) it goes past .AddCell(cell) but fails in the spreadsheet when opened:
B4X:
Dim FormulaString As String = "=OFFSET(INDIRECT('RC',FALSE),-1,0)"
When using cell.InitializeNumber there are no problems (where LastCausticLevel is an integer variable):
B4X:
cell.InitializeNumber(3, i+4, LastCausticLevel)
Excel Library Ver 1.00

Any help would be appreciated.
 
Last edited:

gawie007

Member
Licensed User
Hi Erel,

Code used to generate error:
B4X:
Dim FormulaString As String = "=OFFSET(INDIRECT(" &QUOTE & "RC" &QUOTE & ",FALSE),-1,0)"
cell.InitializeFormula(3, i+4, FormulaString)
editSheet.AddCell(cell)
sendgraph_writedata (B4A line: 91)


editSheet.AddCell(cell)
java.util.EmptyStackException


at java.util.Stack.pop(Stack.java:73)
at jxl.biff.formula.BinaryOperator.getOperands(BinaryOperator.java:61)
at jxl.biff.formula.StringFormulaParser.parseCurrent(StringFormulaParser.java:240)
at jxl.biff.formula.StringFormulaParser.parse(StringFormulaParser.java:113)
at jxl.biff.formula.FormulaParser.parse(FormulaParser.java:161)
at jxl.write.biff.FormulaRecord.initialize(FormulaRecord.java:160)
at jxl.write.biff.FormulaRecord.setCellDetails(FormulaRecord.java:243)
at jxl.write.biff.WritableSheetImpl.addCell(WritableSheetImpl.java:1199)
at anywheresoftware.b4a.objects.WorkbookWrapper$WritableSheetWrapper.AddCell(WorkbookWrapper.java:199)
at com.imf.gsm1401.sendgraph._writedata(sendgraph.java:215)
at com.imf.gsm1401.main._btnsendgraph_click(main.java:518)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:170)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:158)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:154)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:64)
at android.view.View.performClick(View.java:4162)
at android.view.View$PerformClick.run(View.java:17082)
at android.os.Handler.handleCallback(Handler.java:615)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:4867)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1007)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:774)
at dalvik.system.NativeStart.main(Native Method)

Thank you for your time!
 

gawie007

Member
Licensed User
Thank you Erel,
Removed the "=" sign.
That gets the formula (it looks exactly as it should, including the = which it automatically appends) into the cell which now raises some other issues.
It gives me an arbitrary result of 4 which is not in the column (or spreadsheet for that matter)and a circular reference error.
I physically entered the SAME formula in the SAME position and the expected value was displayed.

What I have observed is that when I open the spreadsheet, the original file's data is displayed and then all the new data that I have written (via Excel lib) to the new file writes over it as it is opening. (Refresh?)
I removed protected view from Excel, thinking that could be the problem but without any success.

I found this so far:
When you add a formula, this formula will be computed, but only in Excel, when you open the generated file in Excel. The cell is not computed by JExcel.
On this page: http://stackoverflow.com/questions/10872112/jexcel-formula-calculation-error?rq=1

Conclusion:
I eventually just wrote a blank string over the existing cell's calculation. This got rid of the spike at the end of the graph.

Thank you for your help!
 
Last edited:

qsrtech

Active Member
Licensed User
Does this library allow importing HTML into a new sheet? For example I grab a report that is formatted in html (table) and I want to put it in excel format so user can so whatever they want with it.
 

gawie007

Member
Licensed User
Does this library allow importing HTML into a new sheet? For example I grab a report that is formatted in html (table) and I want to put it in excel format so user can so whatever they want with it.
As far as I know, you can only put numbers(double), strings and formulae(Excel) into a spreadsheet.

I use a sheet called "data" where I write all my values to in b4a.
I then refer to the "data" from other sheets, or even the same sheet in the spreadsheet to display the data in a meaningful way i.e. graphs /different formats / calculations etc.

Even though the library is limited, a lot can be achieved using Excel - you could even use VBA or a macro to automate tasks after acquiring the data.

if you want to extract the data from the HTML first, then copy it to the spreadsheet, start here:
http://www.basic4ppc.com/android/fo...web-page-into-a-text-variable.8024/#post45261
or better still:
http://www.basic4ppc.com/android/forum/threads/parse-html-code.13629/
 
Last edited:

tdocs2

Well-Known Member
Licensed User
Greetings, all.

Would this be the best way to print an xls file?

B4X:
Try
  Dim i As Intent
  i.Initialize(i.ACTION_VIEW, "file://" & File.Combine(File.DirRootExternal, "1.xls"))
  i.SetType("text/xls")
  StartActivity(i)
Catch
  ToastMessageShow("No matching application.", True)
  Log(LastException)
End Try
Is the SetType - i.SetType("text/xls") - correct?

I presume the user then would get choices to open the xls with another app or print with whatever printing app they have?

Second question:

In 4.4, Print Services are included - like Cloud and HP Plugin. What happens then?

Any and all help welcomed.

Thank you in advance.
 
Status
Not open for further replies.
Top