c# pdf image preview: Change font in pdf form field control software platform web page windows wpf web browser 7053154973-Excel%202007%20VBA%20Programmers%20Reference%20-%20Wrox%20200756-part712

Opening Web Pages as Workbooks

The simplest solution is to open the entire web page as if it were a workbook, then scan the sheet for the

required information, such the USD/GBPexchange rate:

Sub OpenUSDRatesPage()

Dim oBk As Workbook

Dim oRng As Range

‘Open the rates pages as a workbook

Set oBk = Workbooks.Open(“http://www.x-rates.com/d/USD/table.html”)

‘Find the British Pounds entry

Set oRng = oBk.Worksheets(1).Cells.Find(“British Pound”)

‘Read off the exchange rate

MsgBox “The USD/GBP exchange rate is “ & oRng.Offset(0, 1).Value

End Sub

The problem with using this approach is that you have to load the entire web page (including graphics,

banners, and so on), which may have much more information than you want. The irrelevant data can

greatly slow down the speed of data retrieval.

Using Web Queries

Web Queries were introduced in Excel 97 and have been enhanced in each subsequent version of Excel.

They enable you to retrieve a single table of information from a web page, with options to automatically

refresh the data each time the workbook is opened, or at frequent intervals.

One of the problems with Web Queries is that Excel uses the thousands and decimal separators specified

in the Windows Regional Settings when attempting to recognize numbers in the page. If the exchange

rate web page were retrieved in many European countries, the period would be treated as a thousands

separator, not a decimal separator, resulting in exchange rates that are many times too large. In Excel

2002, Microsoft added three properties to the Applicationobject to temporarily override the settings

used when recognizing numbers:

❑

Application.DecimalSeparator—The character to use for the decimal separator

❑

Application.ThousandsSeparator—The same for the thousands separator

❑

Application.UseSystemSeparators—Whether to use the Windows separators or Excel’s

Using these properties, you can set Excel’s separators to match those on the web page, perform the

query, and then set them back again. Web Queries could not be used reliably in versions prior to Excel

2002 in countries that used non-U.S. decimal and thousands separators. If you want to use the Web

Query’s automatic refreshing options, you have to set these separators in the BeforeRefreshevent, and

set them back in the AfterRefreshevent. This requires advanced VBAtechniques, using class modules

to trap events, as discussed in Chapter 16.

528

Chapter 24: Excel and the Internet

Change font in pdf form field - C# PDF Field Edit Library: insert, delete, update pdf form field in C#.net, ASP.NET, MVC, Ajax, WPF

Online C# Tutorial to Insert, Delete and Update Fields in PDF Document

add email button to pdf form; pdf create fillable form

Change font in pdf form field - VB.NET PDF Field Edit library: insert, delete, update pdf form field in vb.net, ASP.NET, MVC, Ajax, WPF

How to Insert, Delete and Update Fields in PDF Document with VB.NET Demo Code

create a pdf form that can be filled out; add text field to pdf

In this case, you can retrieve just the table of exchange rates, using the following code to create and

execute a new Web Query. In practice, it’s easiest to use the macro recorder to ensure the selections are

correct:

‘Retrieve USD exchange rates using a Web Query

Sub GetRatesWithWebQuery()

Dim oBk As Workbook

Dim oQT As QueryTable

‘Store the current settings of Excel’s number formatting

Dim sDecimal As String

Dim sThousand As String

Dim bUseSystem As Boolean

‘Create a new workbook

Set oBk = Workbooks.Add

‘Create a query table to download USD rates

With oBk.Worksheets(1)

Set oQT = .QueryTables.Add( _

Connection:=”URL;http://www.x-rates.com/d/USD/table.html”, _

Destination:=.Range(“A1”))

End With

‘Set the QueryTable’s properties

With oQT

.Name = “USD”

‘State that we’re selecting a specific table

.WebSelectionType = xlSpecifiedTables

‘Import the 14th table on the page

.WebTables = “14”

‘Ignore the web page’s formatting

.WebFormatting = xlWebFormattingNone

‘Do not try to recognize dates

.WebDisableDateRecognition = True

‘Don’t automatically refresh the query each time the file is opened

.RefreshOnFileOpen = False

‘Waiting for the query to complete before continuing

.BackgroundQuery = True

‘Save the query data with the workbook

.SaveData = True

‘Adjust column widths to autofit new data

529

Chapter 24: Excel and the Internet

C# PDF File Compress Library: Compress reduce PDF size in C#.net

RasterEdge.Imaging.Font.dll. Program.RootPath + "\\" 3_optimized.pdf"; // create optimizing options TargetResolution = 150F; // to change image compression

create a fillable pdf form from a word document; add image to pdf form

VB.NET PDF File Compress Library: Compress reduce PDF size in vb.

Reduce font resources: Font resources will also take up too Program.RootPath + "\\" 3_optimized.pdf"; 'create optimizing 150.0F 'to change image compression

convert word to editable pdf form; adding text fields to pdf acrobat

.AdjustColumnWidth = True

End With

With Application

‘Remember Excel’s current number format settings

sDecimal = .DecimalSeparator

sThousand = .ThousandsSeparator

bUseSystem = .UseSystemSeparators

‘Set Excel’s separators to match those of the web site

.DecimalSeparator = “.”

.ThousandsSeparator = “,”

.UseSystemSeparators = True

‘Ignore any errors raised by the query failing

On Error Resume Next

‘Perform the query, waiting for it to complete

oQT.Refresh BackgroundQuery:=False

‘Reset Excel’s number format settings

.DecimalSeparator = sDecimal

.ThousandsSeparator = sThousand

.UseSystemSeparators = bUseSystem

End With

End Sub

The .WebTables=“14”line in this example tells Excel that you want the 14th table on the page.

Literally, this is the 14th occurrence of a <TABLE>tag in the source HTMLfor the page.

Parsing Web Pages for Specific Information

Web Queries are an excellent way of retrieving tables of information from web pages, but they are a little

cumbersome if you are only interested in one or two items of information and are extremely susceptible

to minor changes in the web page layout (such as adding an extra <table>tag at the top of the page).

Another way is to read the page using a hidden instance of Internet Explorer, search within the page for

the required information (using either the HTMLor plain text representations), and then return the

result. The following code requires a reference to the Microsoft Internet Controls object library:

Sub GetUSDtoGBPRateUsingIE()

Dim oIE As SHDocVw.InternetExplorer

Dim sPage As String

Dim iGBP As Long, iDec As Long

Dim iStart As Long, iEnd As Long

Dim dRate As Double

‘Create a new (hidden) instance of IE

Set oIE = New SHDocVw.InternetExplorer

‘Open the web page

530

Chapter 24: Excel and the Internet

C# PDF insert text Library: insert text into PDF content in C#.net

Powerful .NET PDF edit control allows modify existing scanned PDF text. Ability to change text font, color, size and location and output a new PDF document.

changing font size in pdf form field; android edit pdf forms

C# PDF Sticky Note Library: add, delete, update PDF note in C#.net

Allow users to add comments online in ASPX webpage. Able to change font size in PDF comment box. Able to save and print sticky notes in PDF file.

change pdf to fillable form; add attachment to pdf form

oIE.Navigate “http://www.x-rates.com/d/USD/table.html”

‘Wait for the page to complete loading

Do Until oIE.readyState = READYSTATE_COMPLETE

DoEvents

Loop

‘Retrieve the text of the web page into a variable

sPage = oIE.Document.body.InnerText

‘**********************************************************************

‘ Brazilian Real 2.1397 0.467355

‘ British Pound 0.524934 1.905

‘ Canadian Dollar 1.1056 0.904486

‘**********************************************************************

‘To find the exchange rate, we have to find the entry for British

‘Pounds, then work forwards to find the exchange rate

‘Find the entry for British Pounds in the web page text.

iGBP = InStr(1, sPage, “British Pound”)

‘Find the next decimal, which will be in the middle of the

‘exchange rate number

iDec = InStr(iGBP, sPage, “.”)

‘Find the start and end of the number

iStart = InStrRev(sPage, “ “, iDec) + 1

iEnd = InStr(iDec, sPage, “ “)

‘Evaluate the number, knowing that it’s in US format

dRate = Val(Mid$(sPage, iStart, iEnd - iStart))

‘Display the rate

MsgBox “The USD/GBP exchange rate is “ & dRate

End Sub

The most appropriate method to use will depend on the precise circumstances, and how much data is

required. For single items, it is probably easier to use the last approach. For more than a few items, it will

be easier to use a Web Query to read the page or table into a workbook, and then find the required items

on the sheet.

Using the Internet to Publish Results

Aweb server can be used as a repository of information, storing your application’s results and present-

ing them to a wider audience than can be achieved with printed reports. By presenting results as web

pages, the reader of those pages can easily use the results as sources of data for their own analysis, and

easily pass those results to other interested parties.

531

Chapter 24: Excel and the Internet

VB.NET PDF Password Library: add, remove, edit PDF file password

RasterEdge.Imaging.Font.dll. passwordSetting.IsHighReso = True ' Allow to change document. passwordSetting.IsAssemble = True ' Add password to PDF file.

change font in pdf fillable form; adding text to pdf form

C# PDF Annotate Library: Draw, edit PDF annotation, markups in C#.

Support to add text, text box, text field and crop marks to PDF document. Able to edit and change PDF annotation properties such as font size or color.

create pdf form; chrome pdf save form data

Setting Up a Web Server

For all the examples from now on, you will require write access to a web server. Because later examples

use Active Server Pages (ASP), here you will use Microsoft’s IIS 5.0. Open IIS, and right-click the Default

Web Site node. Select Properties and click the HomeDirectory tab. You will be presented with various

configuration options for the default web site. Make sure that the Read and Write checkboxes are

selected (see Figure 24-2) and click OK.

Figure 24-2

Notice the Local Path: box. This is where the root of your web server is located. By default, it is

C:\inetpub\wwwroot\. Any web pages placed in this directory are published at the URL http://

localhost/PageName.html.

Saving Worksheets as Web Pages

The easiest way to present results as a web page is to create a template workbook that has all the format-

ting and links that you’d like to show. When your application produces its results, it is then a simple task

to copy the relevant numbers to the template, then save the template direct to the web server (assuming

the server is configured to allow this—if not, you may have to save the HTMLfile to a network location):

Sub PublishResultsToWeb()

Dim oBk As Workbook

Dim oSht As Worksheet

‘Create a new copy of the Web Template workbook

532

Chapter 24: Excel and the Internet

C# PDF Password Library: add, remove, edit PDF file password in C#

RasterEdge.Imaging.Font.dll. passwordSetting.IsHighReso = true; // Allow to change document. passwordSetting.IsAssemble = true; // Add password to PDF file.

adding a text field to a pdf; add signature field to pdf

VB.NET PDF Form Data fill-in library: auto fill-in PDF form data

RasterEdge.Imaging.Font.dll. field.Name, input, outputFilePath + cnt.ToString() + ".pdf") ElseIf TypeOf AFTextBox Then ' fill a TextBox field, change content to

pdf form save with reader; create a pdf form online

Set oBk = Workbooks.Add(“c:\mydir\WebTemplate.xlsx”)

‘Get the first sheet in the workbook

Set oSht = oBk.Worksheets(1)

‘Populate the results

oSht.Range(“Profits”).Value = Workbooks(“Results.xlsx”) _

.Worksheets(“Financials”).Range(“Profits”).Value

‘Save as a web page, direct to the server

oSht.SaveAs “http://localhost/ResultsJuly2001.htm”, xlHtml

‘Close the workbook

oBk.Close False

End Sub

Prior to Excel 2007, the resulting HTMLwas horrible to modify, because it contained a large amount of

extraneous information that could be used to “round-trip” a worksheet through the HTMLformat.

Round-tripping through HTMLnever worked well, always lost data or formatting, and was almost

never used. With the introduction of the XMLfile formats in Excel 2007, the requirement to round-trip

through HTMLno longer exists, and the Save to HTMLfeature has been redesigned as a simple publish-

ing mechanism—resulting in much cleaner HTML.

Creating Interactive Web Pages

The previous example saved a static rendition of the worksheet in HTMLformat to the web server. In

Excel 2000, Microsoft introduced the Office Web Components to create interactive web pages. When sav-

ing a worksheet in interactive form, the worksheet was converted to a set of objects collectively known

as the Office Web Components. In Excel 2007, the Office Web Components have been dropped and

replaced by a dedicated server-side Excel component. Known as Excel Services, the component runs on

top of the SharePoint architecture and is able to open Excel workbooks, update data, perform calcula-

tions, and render the results as plain HTMLfor display in a SharePoint portal. Unfortunately, Excel

Services is beyond the scope of this book.

Using the Internet as a Communication

Channel

Retrieving data from web pages and publishing results as web pages is in many ways a passive use of

the Internet; the web server is being used primarily as a storage medium. Web servers are also able to

host applications, with which you can interact in a more dynamic manner. The server application acts as

a single point of contact for all the client workbooks, to perform the following functions:

❑

Acentralized data store

❑

Collation of data from multiple clients

❑

Presentation of that data back to other clients

533

Chapter 24: Excel and the Internet

C# PDF Form Data fill-in Library: auto fill-in PDF form data in C#

RasterEdge.Imaging.Font.dll. FillFormField(inputFilePath, "AF_CheckBox_01", input, outputFilePath + "2.pdf"); } { fill a TextBox field, change content to

adding signature to pdf form; change font size in pdf fillable form

❑

Workflow management

❑

Calculation engines

As an example, consider a timesheet reporting system, where each member of staff has an Excel work-

book to enter their time on a daily basis. At the end of each month, they connect to the Internet and send

their timesheets to an application running on a web server. That application stores the submitted data in

a central database. Some time later, a manager connects to the server and is sent the submitted hours for

her staff. She checks the numbers and authorizes payment, sending her authorization code back to the

server. The payroll department retrieves the authorized timesheet data from the same web server

directly into its accounting system and processes the payments.

In this business process, Excel is used for the front-end client, providing a rich and powerful user inter-

face, yet it only fulfils a specific part of the overall process. The server application maintains the data

(the completed timesheets) and presents it in whichever format is appropriate for the specific part of the

process.

By using the Internet and standard data formats for this two-way communication, you can easily inte-

grate Excel clients with completely separate systems, as in the payroll system in the example, and allow

the business process to operate outside of the corporate network.

Communicating with a Web Server

Within a corporate network, nearly all data transfer takes place using proprietary binary formats, rang-

ing from transferring files to performing remote database queries. Due primarily to security considera-

tions, communication across the Internet has evolved to use textual formats, such as HTMLand more

recently XML. XMLis covered in detail in Chapter 12.

To be able to communicate with an application running on a web server, you need to be able to pass

information to, and receive information from, that application.

In Excel 2007, the Workbookobject’s FollowHyperlinkmethod can be used to communicate with a

web server. There a few problems with using this, including:

❑

If an error occurs during the connection, Excel will freeze.

❑

Any data returned from the hyperlink is automatically displayed as a new workbook.

❑

You have very little control over the communication.

Amuch more flexible alternative is provided by the Microsoft Internet Transfer Control, msinet.ocx.

This ActiveX control, often referred to as the ITC, is an easy-to-use wrapper for the wininet.dllfile,

which provides low-level Internet-related services for the Windows platform.

Sending Data from the Client to the Server Application

Two mechanisms can be used to send information to a web server. You can either include the informa-

tion as part of the URLstring or send it as a separate section of the HTTPrequest.

534

Chapter 24: Excel and the Internet

URL Encoding

Parameters can be included within the URLstring by appending them to the end of the URL, with a

question mark (?) between the URLand the first parameter and an ampersand (&) between each

parameter:

http://www.MySite.com/MyPage.asp?param1=value1&param2=value2&param2=value3

This has the advantage that the parameters form part of the URLand hence can be stored in the user’s

Favorites list or typed directly into the browser. It has the disadvantage that there is a limit to the total

length of a URL(2,083 characters in Internet Explorer), restricting the amount of information than can be

passed in this way.

POSTing Data

Whenever a request for a web page is sent to a web server, the request contains a large amount of infor-

mation in various header records. This includes things like the client application type and version, the

communication protocol and version, and user IDs and passwords. It also contains a POSTfield that can

be used to send information to the server application.

Because there is virtually no limit to the amount of data that can be put in a POSTfield, it is the preferred

way of transferring information to the server, and is the method used in most applications.

Most web page forms use the POSTmechanism to send the completed form to the server for processing.

Excel can mimic the web-based form by sending the same data to the same server in the same POSTfor-

mat, with the result being sent back as the new web page. The names of the parameters can be found by

examining the source of the real web page form. The form controls will be surrounded by a pair of

<form>and </form>tags, and each control within the form has a name attribute; those names are used

as the parameter names when the form’s data is sent to the server:

Sub SendDataUsingPOST()

Dim oInet As Inet

Dim lContent As Long

Dim sData As String

Dim sHeader As String

Dim sResult As String

‘Create a new instance of the Internet Transfer Control

Set oInet = New Inet

‘Build the POST string with the data to submit

sData = “Param1=Value1” & “&” & _

“Param2=Value2” & “&” & _

“Param3=Value3”

‘Spaces must be replaced with + signs

sData = Replace(sData, “ “, “+”)

‘Tell the POST that we’re sending an encoded parameter list

sHeader = “Content-Type: application/x-www-form-urlencoded”

‘Send the error information to the server

535

Chapter 24: Excel and the Internet

oInet.AccessType = icDirect

oInet.Execute “http://www.mysite.com/mypage.asp”, “POST”, _

sData, sHeader

‘Wait for the server to complete its work

Do While oInet.StillExecuting

DoEvents

Loop

‘Retrieve the returned text

lContent = oInet.GetHeader(“content-length”)

sResult = oInet.GetChunk(lContent + 100)

End Sub

In this example, you’re simply sending a simple list of name/value pairs to the server. If you need to

send more complex data, you can send it as XMLby providing the XMLin the sDatavariable, with a

header set to “Content-Type:text/xml”.

Summary

In Excel 2007, Microsoft has enabled the Excel developer to use the Internet as an integral part of an

application solution in the following ways:

❑

Workbooks can be opened from and saved to web servers running the FrontPage Server

Extensions.

❑

Excel can open HTMLpages as though they were workbooks.

❑

Web Queries can be used to extract tables of data from web pages.

❑

The Internet Explorer object library can be automated to retrieve individual items of data from a

web page, without the overhead of using a workbook.

❑

Excel workbooks can be saved as content-rich web pages, using either HTMLor XML.

❑

The ability to publish interactive web pages using the Office Web Components has been

removed from Excel 2007 and replaced with the Excel Services server-side component.

❑

The Microsoft Internet Transfer Control can be used to exchange data between Excel applica-

tions and web servers, using either a simple parameter list or more structured XML.

Together, these tools enable you to develop new types of business solutions, where Excel is one key part

of a larger business process that may span multiple organizations and geographical locations.

536

Chapter 24: Excel and the Internet

International Issues

If you think that your application may be used internationally, it has to work with any choice of

Windows Regional Setting, on any language version of Windows, and with any language choice

for the Excel user interface.

If you are very lucky, all your potential users will have exactly the same settings as your develop-

ment machine and you won’t need to worry about international issues. However, a more likely

scenario is that you will not even know who all your users are going to be, let alone where in the

world they will live or the settings they will use.

Any bugs in your application that arise from the disregarding or ignoring of international issues

will not occur on your development machine unless you explicitly test for them. However, they

will be found immediately by your clients.

The combination of Regional Settings and Excel language is called the user’s locale, and the aim of

this chapter is to show you how to write locale-independent VBAapplications. To do this, we

include an explanation of the features in Excel that deal with locale-related issues, and highlight

areas within Excel where locale support is absent or limited. Workarounds are provided for most of

these limitations, but some are so problematic that the only solution is to not use the feature at all.

The rules provided in this chapter should be included in your coding standards and used by you

and your colleagues. It is easy to write locale-independent code from scratch; it is much more diffi-

cult to make existing code compatible with the many different locales in the world today.

Changing Windows Regional Settings and

the Office 2007 UI Language

Throughout this chapter, the potential errors will be demonstrated by using the three locales out-

lined in the following table.

Documents you may be interested

c# pdf image preview : Change font in pdf form field control software platform web page windows wpf web browser 7053154973-Excel%202007%20VBA%20Programmers%20Reference%20-%20Wrox%20200756-part712