Opening Excel .xls file with Charts in Calc

[EmmaW]

Hello,

I am researching Libre Office as an alternative to Microsoft Excel for my company.

We have a number of large Excel spreadsheets (.xls made in Excel 2000) that have 300+ worksheets and charts. We would like to be able to open them in Libre Office and edit them.

I can open the spreadsheets in Calc fine. However the charts created in Excel are not recognised as objects in Calc. They are only appearing as images which cannot be selected.

Is there any way of opening a .xls file with charts in Calc that gets Calc to:
a) recognise the charts as actual charts
b) lets you edit and update the charts

I would really like to not have to re-make over 600 charts in Calc!

Thanks very much,

Emma W

[Pedro1]

Calc does recognize charts in XLS files.

The only difference is that you need to double-click within the chart area to switch to Edit mode.

If you actually plan to switch to LibreOffice, I suggest that you save your modified files in ODS format (LibreOffice’s native file format)

Saving in ODS is a win-win option: it makes sure that the changes you made are preserved as you see them (saving back to XLS causes the file to be converted to XLS again, which could cause format loss) and that your information is no longer in a proprietary format (your information is Free from any company’s lock in)

Hope this helps

[EmmaW]

Hi Pedro,

Thanks for your help. I was double-clicking.

I realised that the sheets were protected in the Excel version. I unprotected the sheets in Excel before opening in Libre Office and the charts are now editable.

Emma

[Pedro1]

Thank you for posting back. I’m glad you solved the problem

[Pedro1]

More important: you don’t need to go back to Excel to unprotect the sheets. Using LibreOffice go to the Tools menu, choose Protect Document, Sheet. Type you password and it’s unprotected.

[salisburyk]

When you are testing LibreOffice, Pedro’s suggestion to convert the document to the Open Document format is an excellent one to keep in mind. Import the M$ document into LibreOffice, Save As the Open Document Format. Make your tested edits in the Open Document format. Then re-export back into M$ Office only if required/desired. I also suggest to use the excellent PDF export when M$ Office formats are not required (for viewing outside the company, etc.).

[ROSt53]

@Pedro1 - As it is related to opening xls charts in Calc, and triggered by your answer, let ask how does Calc deal with charts, which are a separate sheet in an xls workbook?

Calc currently cannot have charts as a separate sheet like xls.

[oweng]

I don’t understand your reference to charts of separate sheets. Presumably you are referring to the discussion in this thread. You can either: a) insert a chart on the same sheet and then move (cut/paste) it to another sheet or b) create a new sheet and insert a chart and point it back to the data ranges on another sheet when creating the chart. This works under GNU/Linux TDF/LOv4.0.3.3.

[ROSt53]

@oweng - the thread you indicated in your comment point exactly to the topic. When you create a chart, Excel gives you the choice to put it on the same sheet (where the data are) or on a new sheet. This new sheet contains only the chart linked to the data on a different sheet. In Calc a workaround is needed to get a similar solution.

The question is how does Calc handle a chart created in Excel on a seperate sheet. I don’t have MSO running on my PC anymore thus cannot test but need to ask.

[oweng]

Calc v4.0.3.3 has no issue opening Excel: Mac 2011 XLSX files with charts on separate sheets. As I indicated, you can also create charts on a separate sheet in Calc. Is the issue related to supporting XLSX or enhancing Calc? I ask because it would already seem to do both.

[ROSt53]

Can’t comment on XLSX - don’t know enough. An enhance request is already filed. Following your link leads to fdo#60822.

I will try to get an Excel file from a friend and test the behavior of 3.6.6.6 and 4.0.3.2. Will take a few days.