Getting an HTML Table Into a Spreadsheet in OpenOffice Calc

I blogged here about how to get a spreadsheet into a Writer table.

As for the opposite, getting a Writer table into a spreadsheet, you just copy and paste. Very easy.

How do you get an HTML table into a spreadsheet?

Or, frankly, how do you just suck some HTML content into a spreadsheet?

You should be able to copy and paste--when I paste a table, it comes into the spreadsheet nicely with the rows and columns  retained correctly. However, I understand that doesn't work for everyone. So you can use this approach, instead.

Also, if you are so inclined, how do you get it to update regularly? Maybe you want to display stock quotes from a Web page--you can choose to put those in a spreadsheet and have them update every 30 or 300 seconds.  (This feature works correctly with links to files and it really should work, in theory, to link your spreadsheet to a web page on the net but I can't get it to work with a URL, just with a file. If anyone else can, write and let me know how.)

In this case, you might always want to use this approach.

1. Go to the web page containing the table that you want to use. Save the file to your computerif it's not there already. The window and the online help indicate that you can just link to a URL starting with HTTP but I can't make it work.

Also, take a look at the tables if there are more than one. You're going to need to figure out which table you want by knowing what order the tables appear in.

2. Go to your spreadsheet. Click in the cell at the upper left range of where you want the table(s) to appear.

3. Choose Insert > Link to External Date.

4. In the window that appears, browse to the file that you saved to your computer. If you want to try making an HTTP URL work, then paste in that URL instead. The online help says to paste it in, then press Enter.

5. Select the content you want to insert. HTML_All inserts everything in the HTML page. HTML_Tables inserts all the tables. HTML_1 inserts the first table, and so on.

6. Select the Update Every option, if you want to update the content, and specify how often in seconds. This does work, but it has a drawback that we'll see later.

7. Click OK.

8. The HTML content you selected will appear.

9. To format the content to wrap within the cell, select the cells, choose Format > Cells, and in the Alignment tab select the Wrap option shown. Click OK.

10. Whenever the HTML file changes, the spreadsheet updates. However, as you will see, all your beautiful wrap formatting goes away.

If your content in the table is fairly wide, then:
- Resign yourself to it looking like this in the spreadsheet
- Widen the cells in the spreadsheet
- Don't choose to update the content

 

---

Comments

[Olof Andersson]

I'll try this and hope to learn something to solve the problem I'm working on at the moment: How to get "real" news from a web page or rss-feed into the Impress presentation that we are running at my school. I've tried to find help but nothing so far. Perhaps you know of a solution.

[KAILASH]

I need to get a web table from a site, designed locally. When tried, it asks username/ password. How can I cross this screen. Can I write the VBA code incorporating the password of the site.
awaiting.. would be thankful...

[Solveig Haugland]

Hi Kailash,

I'm not sure what's causing you to be prompted for the password--sounds like that is beyond OpenOffice.org.

VBA coding is outside my area of expertise, but you might try contacting dev@openoffice.org, and ask them to write to you directly with the answer.

Solveig

[Robajz.info]

Hi,

When you are trying to get the XHTML table, it seems there is a problem if the HTML contains the XML declaration:

so you will need to get rid of it first.

HTH, Robajz

[Sandy]

I would like to ask if anyone could help me with a question I have about the linux spreadsheet.

I want to change the default movement when the enter key is pressed. Right now when entering a series of numbers or other info in a row of cells, for ex., A1, A2, A3, when the enter key is then pushed, the cursor will go to B3. I want it to default so that it goes back to the beginning of the next row, in my example B1. Is there a way to change that default setting?

Thanks for any help you can share.

[Solveig]

Hi Sandy,

Choose Tools > Options > OpenOffice.org Calc > General. Under Input Options, you can change what happens.

Solveig

[Ryan N.]

This threw me slightly, but the menu item you meant was "Link to External Data", not "Date." You can then load a table from an HTML file or I guess a URL (though I haven't tried that part).

[yay!]

Thanks so much! I've encountered many times over the last few years where I tried to copy a single column from a website table (using ctrl-select) and ended up with a row...

I always thought it was an issue of some option to turn a column into a row. Thus the limitations of google versus fellow humans =)

I'm still wondering if there's any way to do so?
I can think of other instances (besides websites) where doing so would come in handy, and save a lot of time.

[ajmacaro]

Hi, thanks for the good post.
Anyway, i have this little problem. I create a "styled" spreadsheet.
like a "beauty" reports. you know, theres a border on columns, etc.
Now, what i did is File > Preview in browseer. and it is all OK.

So i save the file as html. then load it again in calc. (file type
Open Office Calc (*htm)). But all of the style in column are
all gone. Is there a way to retain the border or style of the columns?
when the data came from html, or via cut and paste (from browser to calc)

thanks.
-ace