Recasting a STRING into a VALUE in LibreOffice Calc with python UNO

I have a python class that does some currency conversion and string formatting of numbers. It takes polymorphic input, but only spits out a stringified number.

I can push those stringified numbers up to a LibreOffice Calc in python easy enough:

stringifiednumber = str("1.01")

cell_a1 = sheet1.getCellRange("A1")

cell_a1.String = stringifiednumber

This actually works nicely since the builtin currency formats in Calc work just fine with stringified numbers.

What doesn't work is formulas. Or sortof doesn't work. Calling SUM(A1:A2) will not see the stringified A1. There is a workaround (forgive me it is late and I forget it exactly but it is similar to:) =SUMRECORD(VALUE(A1:A2))

As I understand it, each cell has a memory location for a number, a string, and a formula. The formula only acts on the VALUE memory location.

Through the spreadsheet UI, I can convert one cell type to another during a copy. To do that I just put the following formula in A2, and it converts STRING(A1) to VALUE( A2):

# forumula placed in A2



    =VALUE(A1)

But that only works by copying one cell to another. Obviously there is an internal recasting function within the spreadsheet that is doing the conversion during the copy.

What I want to do, is write a stringified number to the spreadsheet (as above) and then call the spreadsheets native recasting function in place from python, so that VALUE(A1) is recast from STRING(A1).

If I knew what the recasting function was I could just call it after every string write. This would make macros in the UI work like the user expects them to work.

If you're answer is: "do type conversion python-side", I've already considered that, and it is not the solution I'm looking for.

Thanks in advance!

asked Dec 31 '18 at 3:34

James Aanderson

26329

Please clarify where sheet1 is coming from. According to Calc API there is not a method getCellRange in a spreadsheet. There are only getCellByPosition to get a single cell and getCellRangeByName to get a cell range. And the single cell provides setValue and setFormula additional to setString.

– Axel Richter
Jan 1 at 10:06

add a comment |

I have a python class that does some currency conversion and string formatting of numbers. It takes polymorphic input, but only spits out a stringified number.

I can push those stringified numbers up to a LibreOffice Calc in python easy enough:

stringifiednumber = str("1.01")

cell_a1 = sheet1.getCellRange("A1")

cell_a1.String = stringifiednumber

This actually works nicely since the builtin currency formats in Calc work just fine with stringified numbers.

As I understand it, each cell has a memory location for a number, a string, and a formula. The formula only acts on the VALUE memory location.

Through the spreadsheet UI, I can convert one cell type to another during a copy. To do that I just put the following formula in A2, and it converts STRING(A1) to VALUE( A2):

# forumula placed in A2



    =VALUE(A1)

But that only works by copying one cell to another. Obviously there is an internal recasting function within the spreadsheet that is doing the conversion during the copy.

If I knew what the recasting function was I could just call it after every string write. This would make macros in the UI work like the user expects them to work.

If you're answer is: "do type conversion python-side", I've already considered that, and it is not the solution I'm looking for.

Thanks in advance!

asked Dec 31 '18 at 3:34

James Aanderson

26329

Please clarify where sheet1 is coming from. According to Calc API there is not a method getCellRange in a spreadsheet. There are only getCellByPosition to get a single cell and getCellRangeByName to get a cell range. And the single cell provides setValue and setFormula additional to setString.

– Axel Richter
Jan 1 at 10:06

add a comment |

I have a python class that does some currency conversion and string formatting of numbers. It takes polymorphic input, but only spits out a stringified number.

I can push those stringified numbers up to a LibreOffice Calc in python easy enough:

stringifiednumber = str("1.01")

cell_a1 = sheet1.getCellRange("A1")

cell_a1.String = stringifiednumber

This actually works nicely since the builtin currency formats in Calc work just fine with stringified numbers.

As I understand it, each cell has a memory location for a number, a string, and a formula. The formula only acts on the VALUE memory location.

Through the spreadsheet UI, I can convert one cell type to another during a copy. To do that I just put the following formula in A2, and it converts STRING(A1) to VALUE( A2):

# forumula placed in A2



    =VALUE(A1)

But that only works by copying one cell to another. Obviously there is an internal recasting function within the spreadsheet that is doing the conversion during the copy.

If I knew what the recasting function was I could just call it after every string write. This would make macros in the UI work like the user expects them to work.

If you're answer is: "do type conversion python-side", I've already considered that, and it is not the solution I'm looking for.

Thanks in advance!

asked Dec 31 '18 at 3:34

James Aanderson

26329

I have a python class that does some currency conversion and string formatting of numbers. It takes polymorphic input, but only spits out a stringified number.

I can push those stringified numbers up to a LibreOffice Calc in python easy enough:

stringifiednumber = str("1.01")

cell_a1 = sheet1.getCellRange("A1")

cell_a1.String = stringifiednumber

This actually works nicely since the builtin currency formats in Calc work just fine with stringified numbers.

As I understand it, each cell has a memory location for a number, a string, and a formula. The formula only acts on the VALUE memory location.

Through the spreadsheet UI, I can convert one cell type to another during a copy. To do that I just put the following formula in A2, and it converts STRING(A1) to VALUE( A2):

# forumula placed in A2



    =VALUE(A1)

But that only works by copying one cell to another. Obviously there is an internal recasting function within the spreadsheet that is doing the conversion during the copy.

If I knew what the recasting function was I could just call it after every string write. This would make macros in the UI work like the user expects them to work.

If you're answer is: "do type conversion python-side", I've already considered that, and it is not the solution I'm looking for.

Thanks in advance!

python python-3.x openoffice-calc libreoffice-calc

asked Dec 31 '18 at 3:34

James Aanderson

26329

asked Dec 31 '18 at 3:34

James Aanderson

26329

asked Dec 31 '18 at 3:34

James Aanderson

26329

asked Dec 31 '18 at 3:34

James Aanderson

26329

asked Dec 31 '18 at 3:34

James Aanderson

26329

Please clarify where sheet1 is coming from. According to Calc API there is not a method getCellRange in a spreadsheet. There are only getCellByPosition to get a single cell and getCellRangeByName to get a cell range. And the single cell provides setValue and setFormula additional to setString.

– Axel Richter
Jan 1 at 10:06

add a comment |

Please clarify where sheet1 is coming from. According to Calc API there is not a method getCellRange in a spreadsheet. There are only getCellByPosition to get a single cell and getCellRangeByName to get a cell range. And the single cell provides setValue and setFormula additional to setString.

– Axel Richter
Jan 1 at 10:06

Please clarify where sheet1 is coming from. According to Calc API there is not a method getCellRange in a spreadsheet. There are only getCellByPosition to get a single cell and getCellRangeByName to get a cell range. And the single cell provides setValue and setFormula additional to setString.

– Axel Richter
Jan 1 at 10:06

add a comment |

1 Answer
1

active

oldest

votes

Based on your Title, multiply by 1.

answered Dec 31 '18 at 5:10

pnuts

48.5k76297

Thanks for your reply. Forgive my ignorance, but can you give me an example of telling Calc to multiply a cell by 1, in python. Or basic. I don't know whether you bind a forumula, or whether there is an API specific function or operator or what.

– James Aanderson
Dec 31 '18 at 18:30

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53983345%2frecasting-a-string-into-a-value-in-libreoffice-calc-with-python-uno%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Based on your Title, multiply by 1.

answered Dec 31 '18 at 5:10

pnuts

48.5k76297

Thanks for your reply. Forgive my ignorance, but can you give me an example of telling Calc to multiply a cell by 1, in python. Or basic. I don't know whether you bind a forumula, or whether there is an API specific function or operator or what.

– James Aanderson
Dec 31 '18 at 18:30

add a comment |

Based on your Title, multiply by 1.

answered Dec 31 '18 at 5:10

pnuts

48.5k76297

Thanks for your reply. Forgive my ignorance, but can you give me an example of telling Calc to multiply a cell by 1, in python. Or basic. I don't know whether you bind a forumula, or whether there is an API specific function or operator or what.

– James Aanderson
Dec 31 '18 at 18:30

add a comment |

Based on your Title, multiply by 1.

answered Dec 31 '18 at 5:10

pnuts

48.5k76297

Based on your Title, multiply by 1.

answered Dec 31 '18 at 5:10

pnuts

48.5k76297

answered Dec 31 '18 at 5:10

pnuts

48.5k76297

answered Dec 31 '18 at 5:10

pnuts

48.5k76297

answered Dec 31 '18 at 5:10

pnuts

48.5k76297

Thanks for your reply. Forgive my ignorance, but can you give me an example of telling Calc to multiply a cell by 1, in python. Or basic. I don't know whether you bind a forumula, or whether there is an API specific function or operator or what.

– James Aanderson
Dec 31 '18 at 18:30

add a comment |

Thanks for your reply. Forgive my ignorance, but can you give me an example of telling Calc to multiply a cell by 1, in python. Or basic. I don't know whether you bind a forumula, or whether there is an API specific function or operator or what.

– James Aanderson
Dec 31 '18 at 18:30

Thanks for your reply. Forgive my ignorance, but can you give me an example of telling Calc to multiply a cell by 1, in python. Or basic. I don't know whether you bind a forumula, or whether there is an API specific function or operator or what.

– James Aanderson
Dec 31 '18 at 18:30

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

J,geI0cq1M9ikpvBNHw

搜尋此網誌

Bdtjtk