How to use .xml spreadsheet in LibreOffice

English
, ,
#1

My granddaughter has a college class and they use xml spreadsheets with formulas. How does she convert these files to a format that libre office can work with?

Update 2019-05-09T18:55+02:00 (erAck: moved the following section from Answer to this Question)

The spreadsheet was created with Exxel. My granddaughter uses Libre Office Calc. When she opens the .xml spreadsheet, she gets errors in the cells that have formulas. In the formula composition line above, the formulas have an =of in front of the normal formula. If I go to each individual cell that contains a formula and delete that prefix, it works for multiplication functions. But for formulas that output an average of the numbers in three cells, it still does not work.Here is one of her files.link text

#2

What do you mean by xml spreadsheet exactly? MS Excel can produce XML Spreadsheets, LibreOffice can produce Flat XML ODF Spreadsheets (.fods), so your usage is a bit unspecific to provide an answer. Which application produced these .xml spreadsheets you are talking about ?

#3

According to your answer, I think the only way to give a useful answer requires to have an anonymized version of your Excel xml. Please consider to upload a testfile (use the clip icon when editing your original question)

But from my point of view, it sounds a bit strange that you try a way which requires the use of 2 format conversions from Excel to xml and from xml into LibreOffice, while LibreOffice is capable to use Excel’s native format .xlsx. Every conversion is prone to leak information, since no support of a foreign format is hardly ever 100%, and a single conversion (from xls/xlsx into LibreOffice) probably is less prone than a double conversion.

#4

The =of appears to be the OpenFormula namespace used in ODF spreadsheet documents; however, if that shows up as content there’s something wrong. Which Excel version and what filter / file format was used to create the file? Without seeing the file there’s not much we can do other than guessing. Please attach the sample file if it doesn’t have sensitive content. Otherwise you’d want to replace sensitive content with XXX or such and save again (in that Excel version!) before uploading.

#5

I have the file to attach to my question, but the forum software will not allow for an .xml attachment. Should I post it as text in a comment?

#6

Rename the file to an allowed extension (e.g. .odt) and make a comment, that it is an xml file and needs to be renamed to .xml before using/investigating.

#7

I have changed the file extension to .xls, and made note that it is an .xml file.

#8

@ElFundidor

Upload the original file in a cloud and post the link here at ask.

The xls formula is gone.

#9

The link to the .xml file is:

link text

#10

My Excel 2007 does not open?

Opened just like LibreOffice, without the formulas.

#11

That is the problem. She is working with formulas in her class.

#12

But obviously already the .xml from Excel is already somehow corrupted and please read my first comment regarding alien formats. Please give the original native Excel .xls file.

#13

Ops. @anon73440385, you’re right xlm is not native to Excel, I confused it with xlsm.

#14

When I try to open the file in MS Excel 2010, I get an error message about a broken table. So, it looks like the file is not a valid Excel XML file. Thus, there is hardly anything to do.

#15

Here is another .xml file that she has as an assignment. Page one is a simple multiplication formula, and page three is a simple average formula. link text

#16

Here is another .xml file…

I tried to open it with Microsoft Excel 2010, and it entirely failed reporting a damaged file. Are you sure the file was created in Excel? By the way, LibreOffice Calc 6.1.2.1 opened the file without any error, so I can’t see what is a problem there if any.

#17

@ElFundidor - why does your granddaughter insist in using .xml format, though it should be clear that the format produced does not work with Calc? Go for .xls or .xlsx. Even Excel (2003 is what I have) cannot open the file at all.

@gabix - The problem is, that there are no formulas showing up in the import, though there should be some. The xml looks like to contain the formula and the results (values) and Calc imports the results but not the formula (e.g. ...ss:Formula="of:=AVERAGE([.E18];[.F18];[.G18])"><Data ss:Type="Number">9.33333333333333< - See also answer from @erAck.

#18

XML is OK when it is a valid XML. Neither of the samples uploaded here is, however, valid. I suspect that the file is generated by some program that is not genuine Excel and is not compliant with the Excel XML specs.

#19

If your suspection is right, then I’d exepect OQ to clarify each involved party about this misinterpretation of using “Exxel” above, which made everybody here assuming a typo and supposing to deal with a native Excel file. Furthermore I’d immediately state that this is not a question for this Q&A site.

#20

Why, the original question is OK for the site, namely:

How does she convert these files to a format that libre office can work with?

And the answer is: no way to do it in LibreOffice, because the files are not in a valid format supported by LibreOffice.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.