The technique of transferring HTML format to Excel using ASP

Last Update:2017-02-28 Source: Internet

Author: User

Tags implement

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

excel| Skills | data

Learn how to build an ASP page to spread HTML data to Execl spreadsheets and display execl spreadsheets in IE.

So far, there are several ways to use ASP technology to create Excel data tables, and you can also use server-side Excel 8.0 VBA components to build spreadsheets. However, in some cases the server may not be able to handle the amount of information involved or be unable to assume the resulting workload, so you have to sacrifice some of the processing performance provided to the client. This article will focus on the client-side data table processing issues. Excel 97 has some special properties that can handle HTML-formatted data streams just as you do with spreadsheets, and this particular ability is only available in Excel 97 or later, and users need to build similar Excel spreadsheet data streams to handle them more effectively.

So does the browser have a similar function? Internet Explorer (IE) 3.0 or later can display Microsoft Excel worksheets like Excel, provided the client needs to load Excel. If the user opens the IE browser and enters the Excel worksheet file name as Url,ie, the worksheet opens. IE uses the file name extension to identify whether it is a MIME format file and launches the appropriate application, IE converts the file to Application/vnd.ms-excel and starts Excel for the user to view the relevant tabular data when IE processes the Excel MIME format file.

This article discusses how to build an ASP home page that spreads HTML data to an Excel spreadsheet. With this technology, IE browsers can display HTML data tables like Excel. In the following example, the ASP home page launches a component that processes the employee table records stored in Nwind sqlsever 6.5 and displays the table as an HTML table.

Create an HTML table
First set up the ASP home page, add the home page to the Visual InterDev project, the file name is contenttype.asp, as shown in Figure A. Then add the code shown by code A to the home page, and the first line of code converts the MIME type of the page involved to Excel MIME type. The following lines of code will start the component we established earlier to get the data for the Ador record area. The code, as shown in Listinga, loops through the record area and creates an HTML table that includes records for each row in the employee table.

Code A:code for adding new ASP page to Visual InterDev Project
<%@ Language=vbscript%>

<%response.contenttype = "Application/vnd.ms-excel"%>

<%
Dim Objemployee, RS

Set Objemployee=server. CreateObject ("Empreport.employee")

Set rs = Objemployee.getemployee ()

' Draw a Table

%>
<TABLE>
<TR>
<td><strong><font face= "" Size=4>employee
Report</font></strong></td>
</TR>
<TR>
<td>employee id</td>
<td>first name</td>
<td>last name</td>
<TD>Title</TD>
</TR>
<%
Do as not rs.eof
%>
<TR>
<TD>
<%=rs ("EmployeeId")%></td>
<TD>
<%=rs ("LastName")%></td>
<TD>
<%=rs ("FirstName")%></td>
<TD>
<%=rs ("Title")%></td>
</TR>
<%
Rs.movenext
Loop
%></table>

When you have finished entering your code, save and browse the home page. The home page is like Figure B. Note that all the rows in the spreadsheet do not have any formatting. When we use the Excel VBA component to create a work table, we can use VBA to adjust the table. However, it is more difficult to format a table normally using HTML, unless you can easily display and read the data about the HTML table. We can do this with ASP home technology, and we can achieve all the features currently supported by Microsoft Excel, including the sum feature. It should be noted that the work table created using HTML does not include some standard notation, such as <HTML> and <BODY>. When we create an HTML table that swaps data with an Excel worksheet, try not to use the <HTML>,<BODY>,<HEAD> notation in the ASP home page, because Excel worksheets usually don't have the right markings.

Use standard Excel features in HTML tables
Excel 97 allows users to use Excel's operational formulas to implement standard calculations in HTML tables, such as using operations such as Total and sum. The notable advantage of this feature is that the work tables requiring computational processing are moved to client processing, which greatly reduces the footprint of valuable server resources and reduces the network burden of information delivery between servers and clients. For example, as shown in Figure C, a home page named sumit.asp, an HTML table is created that shows the sum of two columns in an Excel worksheet. Notice the code in code B, where the first line of code converts the content format in the table to Excel format, so the browser will know which type of application is passing data for the HTML table. We created a table with two columns, and the second column contains two sets of values: 2 and 3. The value in the third row is the sum of the first two values, which uses the =sum (B1:B2) calculation feature of Excel.

Code B:code for creating a HTML table that displays sum of two columns in Excel worksheet

<%@ Language=vbscript%>

<%response.contenttype = "Application/vnd.ms-excel"%>

Conclusion There are several different ways to use the browser application to display Excel worksheets. The specific way to use it depends on the circumstances: Do you want to implement the data processing on the client or in the server? Is there a need for a variety of formatting processes during program execution? Is the end user using IE browser? If your work table is complex and important, you'd better consider creating an Excel template and then filling in the values with server-side VBA com. If your work table is simpler or is only running on the client side, you can consider using HTML data stream processing. On the other hand, if your users are using Netscape Navigator browsers, all of your data processing must be run on the server side and use COM VBA components.

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More