Import from html to excel macro. Convert HTML to Microsoft Excel formats

0

Requirement:

1. I need to export a table to an Excel file.

2.I first render it on an html page. I have a button to export to html.

My opinion:

1.I get HTML from the page:

Document.getElementById("content").value = document.getElementById("containerId").innerHTML; form1.submit();

2.I receive from the server, Response.ContentType = "application/vnd.ms-Excel;" // requires the client to install Microsoft Excel.

3.I received the right Excel file "XXXX.xls".

4.But BUT, BUT, when I open it, it alerts Waring to tell me how

"it"s not the right format of Excel, are you confirm to open it?"

I feel sorry to see this.

So I want to import the HTML section into an Excel file and then respond to the correct Excel file in USER-AGENT.

I have Aspose.Cells library in my project, I don't know how to use it to complete the task, or any other solution to solve it?

  • 1 answer

    • Sorting:

    Activity

1

If you need to parse the html tags/part of an Excel table using Aspose.Cells for .NET, you can use the Cell.HtmlString attribute to set the desired html code segment in the cell, it will be parsed accordingly into the generated Excel file. Please be aware that not all html tags are supported at this time.

Aspose.Cells for .NET also supports converting Excel file to HTML file directly, see the documents on which file formats are supported for conversion, it can help you for your reference.

If you've created a nice spreadsheet in Excel and now want to publish it as a web page, the easiest way to do this is to export it to a good old HTML file. In this article, we will look at several ways to convert data from Excel to HTML, determine the pros and cons of each method, and work with you to complete this conversion step by step.

Convert Excel Spreadsheets to HTML Using the Save As Web Page Tool

Let's say you've created a rich report in Excel and now want to export all this information, along with a chart and a pivot table, to your company website so that your colleagues can see it online through web browsers without having to open Excel.

To convert Excel data to HTML, follow these steps. These instructions apply to Excel 2013, 2010, and 2007.


Advice: If this is your first time converting an Excel workbook to an HTML file, it may be wise to first save the web page to your hard drive so that you can make edits if necessary before publishing the page online or on your local network.

Comment: The HTML code generated by Excel is not very clean! It will be great when, having converted a large table with a complex design, you open it in any HTML editor and clean up the code before publishing. As a result, the page on the site will load noticeably faster.

5 Things to Remember When Converting Excel Files to HTML

When using the Save As Web Page tool, it is important to understand how its main options work so you don't make the most common mistakes and avoid the most common error messages. In this section, you will find a brief overview of the options that you need to pay special attention to when converting Excel files to HTML.

1. Supporting files and hyperlinks

As you know, web pages often contain pictures and other supporting files, as well as hyperlinks to other websites. By converting an Excel file into a web page, the application automatically collects related files and hyperlinks for you and saves them in a supporting folder.

When you save supporting files, such as charts and background textures, to the same Web server, Excel creates all the links relative. A relative link (URL) points to a file within the same website; it specifies the file name or root folder instead of the full site name (for example, href=”/images/001.png”). When you delete any item saved as a relative link, Microsoft Excel automatically deletes the associated file from the supporting folder.

So, the main rule is Always save the web page and supporting files in one place, otherwise the web page will not display correctly. If you move or copy your web page to another location, make sure that the supporting folder is copied to the same location, otherwise the links will not be correct. If you resave the web page to another location, Microsoft Excel will copy the supporting folder automatically.

If you save web pages to different locations or if the Excel file contains hyperlinks to external websites, then in such cases absolute links. Absolute links contain the full path to a file or web page, which can be accessed from anywhere, for example: www.your-domain/section/page.htm.

2. Making changes and resaving the web page

In theory, you can save an Excel workbook as a web page, then open the resulting web page in Excel, make changes, and resave the file. However, in this case, some Excel features will not be available. For example, any charts contained in your workbook will turn into independent drawings, and you will not be able to edit them in Excel as you could previously.

Therefore, the best way is to first update the original Excel workbook with some changes, then save it as an Excel workbook (.xlsx), and only then convert it back to a web page.

3. Auto-republish a web page

If you have checked the box next to the option AutoRepublish(Auto-Republish) in the dialog box Publish As Web Page(Publish a Web Page) which we mentioned earlier in this article, then your web page will be automatically updated every time you save a workbook. This feature is very useful and allows you to always keep an online copy of your Excel spreadsheet up to date.

If you enable the option AutoRepublish(Auto-Republish), each time you save a workbook, a message will appear asking you to confirm whether you want to enable or disable Auto-Republish. If you want the Excel sheet to be automatically published, then select Enable…(Enable...) and click OK.

However, there may be circumstances when you do not want to automatically publish an Excel worksheet or its elements, for example, if the file contains confidential information or was modified by someone who is not a trusted person. In this case, you can temporarily or permanently disable auto-republishing.

To temporarily disable auto-republishing, select the first option given in the above post – Disable the AutoRepublish feature while this workbook is open(Disable Auto-Republish feature when this book is open.) This will disable automatic publishing for the current Excel session, but will enable it again the next time you open the workbook.

To turn off auto-republish permanently for all selected items, open your Excel workbook, go to the dialog box Publish As Web Page(Publish Web Page) and click the button Publish(Publish). In chapter Items to publish(Published items) in the list Choose(Select) select the item you do not want to publish and click the button Remove(Delete).

4. Excel features that are not supported on web pages

Unfortunately, some very useful and popular Excel features become unavailable when you convert your Excel sheets to HTML:

  • Uword formatting not supported when saving Excel sheet as Single File Web Page(Web page in this file) so make sure you save it as Web Page(Webpage). Histograms, color scales, and icon sets are not supported by both web page formats.
  • Rotated or vevertical text is not supported when exporting data from Excel to a web page format. Any rotated or vertical text in your workbook will be converted to horizontal text.

5. The most common difficulties encountered when converting Excel files to HTML

When converting an Excel workbook to a web page, you may encounter the following known difficulties.

The need to convert a table with an HTML extension to Excel formats may arise in various cases. You may need to convert web page data from the Internet or HTML files that were used locally for other purposes by special programs. Quite often conversion is carried out in transit. That is, they first convert the table from HTML to XLS or XLSX, then process or edit it, and then convert it back into a file with the same extension to perform its original function. This is due to the fact that it is much easier to work with tables in Excel. Let's find out how you can convert a table from HTML format to Excel.

The HTML format is a hypertext markup language. Objects with this extension are most often used on the Internet, as static web pages. But often they can be used for local needs, for example, as help documents for various programs.

If the question arises of converting data from HTML to Excel formats, namely XLS, XLSX, XLSB or XLSM, then an inexperienced user can take his head off. But in reality there is nothing terrible here. Conversion in modern versions of Excel using the program's built-in tools is quite simple and, in most cases, relatively correct. In addition, we can say that the process itself is intuitive. However, in difficult cases, you can use third-party utilities for conversion. Let's look at the different options for converting HTML to Excel.

Method 1: Using Third Party Programs

Let's immediately focus on using third-party programs to convert files from HTML to Excel. The advantages of this option are that specialized utilities can handle converting even very complex objects. The disadvantage is that the vast majority of them are paid. In addition, at the moment, almost all decent options are in English without Russification. Let's look at the algorithm of work in one of the most convenient programs for performing the above conversion direction - Abex HTML to Excel Converter.

  1. After the Abex HTML to Excel Converter program installer has been downloaded, launch it by double-clicking on it with the left mouse button. The installer's welcome window opens. Click on the button "Next" ("Further").

  2. Following this, a window with a license agreement opens. In order to agree with it, you should put the switch in the position "I accept the agreement" and click on the button "Next".

  3. After this, a window opens indicating where exactly the program will be installed. Of course, you can change the directory if you wish, but it is not recommended to do this unless absolutely necessary. So just press the button "Next".

  4. The next window indicates what the name of the program will be, displayed in the start menu. Here, too, you can simply click on the “Next” button.

  5. In the next window, you are asked to install the utility icon on the desktop (on by default) and on the Quick Launch panel by checking the boxes. We set these settings according to our preferences and click on the button "Next".

  6. After this, a window opens, which summarizes all the information about all the program installation settings that the user made earlier. If the user is not satisfied with something, he can click on the button "Back" and edit the settings accordingly. If he agrees with everything, then to start the installation you should click on the button "Install".

  7. The utility installation procedure is in progress.

  8. After its completion, a window is launched in which it is reported about this. If the user wants to immediately start the program automatically, then he must ensure that next to the parameter "Launch Abex HTML to Excel Converter" the checkbox has been checked. Otherwise, you need to remove it. To exit the installation window, click on the button "Finish".

  9. It is important to know that before launching the Launch Abex HTML to Excel Converter utility, no matter how it is done, manually or immediately after installing the application, you should shut down and close all programs in the Microsoft Office suite. If you do not do this, then when you try to open Abex HTML to Excel Converter, a window will open in which you will be informed about the need to perform this procedure. To start working with the utility, you need to click on the button in this window "Yes". If office documents are open at the same time, work in them will be forcibly completed, and all unsaved data will be lost.

  10. The registration window will then launch. If you purchased a registration key, then in the appropriate fields you need to enter its number and your name (you can use an alias), and then click on the button "Register". If you have not purchased a key yet and want to try a stripped-down version of the application, then just click on the button "Remind me later".

  11. After completing the above steps, the Abex HTML to Excel Converter program window opens directly. To add a file in HTML format for conversion, click on the button "Add Files".

  12. After this, the add file window opens. In it you need to go to the category where the objects intended for conversion are located. Then you need to select them. The advantage of this method over the standard HTML to Excel conversion is that you can select and convert several objects at once. After the files are selected, click on the button "Open".

  13. The selected objects will be displayed in the main utility window. After that, click on the lower left margin to select one of three Excel formats into which you can convert the file:
    • XLS (default);
    • XLSX;
    • XLSM (with macro support).

    We make a choice.

  14. After that, go to the settings block "Output setting" ("Output Settings"). Here you should indicate exactly where the converted objects will be saved. If you set the switch to position "Save target file(s) in source folder", then the table will be saved in the same directory where the source in HTML format is located. If you want to save files in a separate folder, then to do this you should move the switch to the position "Customize". In this case, by default the objects will be saved in the folder "Output", which in turn is located in the root directory of the disk C.

    If you want to specify the location where the object will be saved, you should click on the button located to the right of the address field.

  15. After this, a window opens with an overview of folders. You need to move to the directory that you want to designate as the save location. Then you should click on the button "OK".

  16. After this, you can proceed directly to the conversion procedure. To do this, click on the button on the top panel "Convert".

  17. Then the conversion procedure will be performed. After its completion, a small window will open informing you about this, and will also automatically launch Windows Explorer in the directory where the converted Excel files are located. Now you can carry out any further manipulations with them.

But you need to keep in mind that if you use the free trial version of the utility, only part of the document will be converted.

Method 2: Convert using standard Excel tools

You can also convert an HTML file to any Excel format quite easily using the standard tools of this application.


There is also another option to go to the document saving window.


As you can see, converting a file from HTML to one of the Excel formats is quite simple, using the standard tools of this program. But for those users who want to get additional capabilities, for example, mass conversion of objects in a specified direction, we can recommend purchasing one of the specialized paid utilities.

The CHAR function in excel is used to insert a symbol into a cell or formula that cannot be entered from the keyboard or may result in an error.

Earlier, this time I decided to check what else he was capable of.

Today I will show you how to create HTML markup in Excel without any web editors.

Excel + HTML connection example

To quickly create pages of the same type on my website, I needed to create an html layout on an Excel sheet, with tags, styles, and the values ​​between the tags were drawn from a separate sheet. On this sheet, the cells corresponded to the fields I needed for the final layout, text samples were prepared in advance and inserted one by one into the first sheet, and on the second sheet, formulas placed the data in the right place.

I'll give you part of the HTML code

Page title

My page number times!

Here is some long text for example



Formula code in Excel cells

="" ="" =""&Sheet1!B1&"" ="" ="" ="

"&Sheet1!B2&"

" ="

"&Sheet1!B3&"

" ="

" =""

As you can see from the code, the values ​​of the title, h2, p tags are obtained from Sheet3 via the link, being variables. All you have to do is change the values ​​on Sheet1, and copy all the cells from Sheet2, paste into a text file, save in html - and the web page is ready. But when using h2 tag with inline style style="text-align: center;" and span style="color: #ff0000;" An error occurred in the formula, since the number of double quotes “teared” the formula into parts that were incompatible with each other.

My page no.

Solving the problem using CHAR(34) in Excel

The following construct was written before the double quote:

& CHAR(34) & // (ampersand CHAR(34) ampersand)

Why (34) you ask? The CHAR function takes a number from 1 to 255 inclusive as an argument, and 34 is just a double quote("). You say that you could write ="h2 style=" " "text-align: center;", but alas - this trick doesn't work. CHAR(34) escapes the subsequent quotation mark and the formula does not generate an error.

How it turned out for me:

="

&Sheet1!B2&"

"

Final text Excel=HTML+CHAR(34)

="" ="" =""&Sheet1!B1&"" ="" ="" ="

&Sheet1!B2&"

" ="

"&Sheet1!B3&"

" ="

" =""

In order to view all values ​​of the CHAR function, write 1 in cell A1, A2 2, extend the values ​​up to 255 inclusive, write =CHAR(A1) in cell B1 and extend the formula.



 

It might be useful to read: