Ask Your Question
1

Opening Excel .xls file with Charts in Calc [closed]

asked 2013-05-07 12:40:23 +0200

EmmaW gravatar image

updated 2015-09-10 11:01:56 +0200

Alex Kemp gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-05 18:50:54.356008

3 Answers

Sort by » oldest newest most voted
1

answered 2013-05-07 16:37:15 +0200

EmmaW gravatar image

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

edit flag offensive delete link more

Comments

Thank you for posting back. I'm glad you solved the problem ;)

Pedro gravatar imagePedro ( 2013-05-07 16:48:34 +0200 )edit
2

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.

Pedro gravatar imagePedro ( 2013-05-07 16:52:04 +0200 )edit

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.).

salisburyk gravatar imagesalisburyk ( 2013-06-13 21:21:33 +0200 )edit
3

answered 2013-05-07 13:00:28 +0200

Pedro gravatar image

updated 2013-05-07 13:02:09 +0200

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 ;)

edit flag offensive delete link more
0

answered 2013-06-14 03:02:19 +0200

ROSt52 gravatar image

@Pedro - 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.

edit flag offensive delete link more

Comments

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.

oweng gravatar imageoweng ( 2013-06-14 03:18:23 +0200 )edit

@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.

ROSt52 gravatar imageROSt52 ( 2013-06-14 07:54:27 +0200 )edit

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.

oweng gravatar imageoweng ( 2013-06-14 11:51:34 +0200 )edit

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.

ROSt52 gravatar imageROSt52 ( 2013-06-14 15:43:28 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2013-05-07 12:40:23 +0200

Seen: 5,224 times

Last updated: Aug 02 '13