Introduction
This is my first article to the Tech World.
<o:p>
This document will help programmers to dynamically create documents using word template without generating any word object on the server.
<o:p>
Background
<o:p>
In some web applications it is required to create a document generated dynamically, like an Offer letter given to the candidate, some other official letters used during the interviewing purpose, or mark sheet templates etc.
<o:p>
In such type of documents most of the things are similar except for few changes.
Let’s take some examples.
<o:p>
1.Mark Sheet: Name, Marks Scored, Seat Number etc will change from student to student but other details like the Exam name, stream , university name etc. will remain same.
<o:p>
2. Offer letter: The name, address, post date of offer and date of joining, designation and pay structure etc. will change from candidate to candidate. But other details will remain same.
<o:p>
<o:p>
Solution
<o:p>
To generate O/P for such type of requirement follow a step wise procedure given below.
<o:p>
Step1: Create A Word Template
<o:p>
Why Word template?
I. It is very easy to generate formatted O/P in word.
II. Sub scripts and super scripts e.g. ™ can be easily written.
III. Maintenance of such documents can be easily done, if changes arise.
<o:p>
<o:p>
<o:p>
<o:p>
How to Create Template?
<o:p>
e.g.
1. Offer Letter:
<o:p>
##Address##, ##DOF##
<o:p>
Dear ##Name##,
<o:p>
We are glad to appoint you for the designation ## desig ##.
<o:p>
- Mark sheet:
<o:p>
Name: ##NAME##
<o:p>
Seat Number: ##Number##
<o:p>
Subject1 ##SUB1##
Subject2 ##SUB2##
Subject3 ##SUB3##
Subject4 ##SUB4##
Subject5 ##SUB5##
<o:p>
Total ##Total##
Percentage: ##PER##
<o:p>
<o:p>
In the e.g.1 we see ##Address##, ##DOF##, ##NAME## and ##desig##. All these will be replaced by dynamic data selected by the user. Whereas the static data will remain the same.
<o:p>
Similarly in example 2. for Mark sheet
##SUB1## , ##SUB2## , ##SUB3## etc. will be dynamic.
<o:p>
This dynamic data can be made available from the database.
<o:p>
Once you have formatted your template file in word as per the guidelines outlined above save the file. Provide appropriate name to the file.
<o:p>
Note: The saving format should be Rich Text Format(RTF). Click on File – select save as and then select the path and save the file as .rtf extension file.
<o:p>
<o:p>
<o:p>
Step2: Proceed with the code & instructions given below
<o:p>
Make use of file scripting object to copy the template file into some temporary file.
<o:p>
Note: The path of the temporary file should be fixed and it should also have a .rtf extension as the template file.<o:p>
<o:p>
‘ Declare a scripting object.<o:p>
Dim td = Server.CreateObject("Scripting.FileSystemObject")<o:p>
<o:p>
‘Copy File<o:p>
td.CopyFile(Server.MapPath(".") & "Template.rtf", Server.MapPath(".") & "Temporary.rtf")<o:p>
<o:p>
‘ set the file path in a string variable.<o:p>
strFilePath = Server.MapPath(".") & "Temporary.rtf"<o:p>
<o:p>
<o:p>
<o:p>
‘Now open the termporary file in Read mode.<o:p>
Dim fs2 As New FileStream(strFilePath, FileMode.Open, FileAccess.Read)<o:p>
<o:p>
'Declare d as a stream Reader<o:p>
Dim d As New StreamReader(fs2)<o:p>
<o:p>
Dim swrtarget As String<o:p>
<o:p>
'Initialise the stream reader d to the begining of the file.<o:p>
d.BaseStream.Seek(0, SeekOrigin.Begin)<o:p>
swrtarget = d.ReadToEnd 'Read the file from Start to End in one go.<o:p>
d.Close() ' Close the Stream Reader<o:p>
<o:p>
<o:p>
Declare string variables say,<o:p>
Str1ans strvalue1.<o:p>
Now get the values from database.
Use can use a select query and a data reader to get the values.
<o:p>
Now set the file path to the Temporary file
'Set the path to open Temporary<o:p>
strFilePath = Server.MapPath(".") & "Temporary.rtf"
<o:p>
‘Open the temporary file in Read-Write Mode.<o:p>
Dim fs1 As New FileStream(strFilePath, FileMode.Open, FileAccess.ReadWrite)<o:p>
<o:p>
Dim s As New StreamWriter(fs1) 'Declare a Stream Writer.<o:p>
<o:p>
'Replace the values with the values in the string read from Temporary.<o:p>
While myReader.Read<o:p>
<o:p>
Strvalue1= myReader.GetValue(0).ToString()<o:p>
Str1= Replace(swrtarget.ToString, ##Address##", Trim(strvalue1)<o:p>
Strvalue1= myReader.GetValue(1).ToString()<o:p>
str1= Replace(str.ToString, ##Name##", Trim(strvalue1)<o:p>
<o:p>
Similarly it will come for ##DOF## and ##desig###.
<o:p>
End While<o:p>
<o:p>
Close the Reader.
Close the Connection.
<o:p>
' After replacing all the values write these values in the file2.rtf via the file write (s).<o:p>
s.WriteLine(str)<o:p>
'After writing the data in the stream writer the values must be flushed.<o:p>
s.Flush()<o:p>
'Close the stream writer.<o:p>
s.Close()<o:p>
fs1.Close()<o:p>
fs2.Close()<o:p>
<o:p>
<o:p>
Now copy the details into the final file.<o:p>
This file can be a .dc file.<o:p>
<o:p>
Dim td1 = Server.CreateObject("Scripting.FileSystemObject")<o:p>
td1.CopyFile(Server.MapPath(".") & "Temporary.rtf", Server.MapPath(".") & "final.doc")<o:p>
'Open a new window in the browser and display final.doc file to the user.<o:p>
strtargetFilepath = " finalletter.doc"<o:p>
Response.Write("<script> window.open('" & strtargetFilepath & "') </script>")<o:p>
<o:p>
<o:p> Main Code Sippnet
******************************************************************************************************************************************************
Public Sub GetOutput()<o:p>
Dim strFilePath As String 'Declare string variable to store file path.<o:p>
Try<o:p>
<o:p>
Dim strvalue As String<o:p>
Dim myReader As OleDb.OleDbDataReader<o:p>
Dim SqlStr1 As String<o:p>
Dim Srefno As String<o:p>
Srefno = Nothing<o:p>
' Declare a file system object say td.<o:p>
Dim td = Server.CreateObject("Scripting.FileSystemObject")<o:p>
' Copy the template file temp2.rtf into file2.rtf.<o:p>
td.CopyFile(Server.MapPath(".") & "/temp2.rtf", Server.MapPath(".") & "/file2.rtf")<o:p>
<o:p>
<o:p>
' Set file path to file2.rtf.<o:p>
strFilePath = Server.MapPath(".") & "file2.rtf"<o:p>
' Get the refno into Srefno variable.<o:p>
Srefno = Session("RefNo")<o:p>
Dim strString As String<o:p>
Dim cmd As OleDbCommand<o:p>
'Open file2.rtf in read mode.<o:p>
Dim fs2 As New FileStream(strFilePath, FileMode.Open, FileAccess.Read)<o:p>
'Declare d as a stream Reader<o:p>
Dim d As New StreamReader(fs2)<o:p>
'A string variable used to store all the values received from the file fiel2.rtf.<o:p>
Dim swrtarget As String<o:p>
' This variable str will store all the values from the 2nm till final.<o:p>
' Finally the string writer will write the values stored in the str variable.<o:p>
Dim str As String<o:p>
<o:p>
'Initialise the stream reader d to the begining of the file.<o:p>
d.BaseStream.Seek(0, SeekOrigin.Begin)<o:p>
swrtarget = d.ReadToEnd 'Read the file from Start to End in one go.<o:p>
d.Close() ' Close the Stream Reader<o:p>
<o:p>
<o:p>
Dim SqlStr As String<o:p>
Dim srno As String<o:p>
<o:p>
<o:p>
SqlStr = " Your Query HERE"<o:p>
clsCon.OpenCon(ConfigurationSettings.AppSettings("strConnection").ToString)<o:p>
cmd = New OleDb.OleDbCommand(SqlStr, clsCon.Con)<o:p>
myReader = cmd.ExecuteReader<o:p>
<o:p>
<o:p>
'Set the path to open file2.rtf<o:p>
strFilePath = Server.MapPath(".") & "file2.rtf"<o:p>
'Open file file2.rtf in Read Write Mode<o:p>
Dim fs1 As New FileStream(strFilePath, FileMode.Open, FileAccess.ReadWrite)<o:p>
<o:p>
Dim s As New StreamWriter(fs1) 'Declare a Stream Writer.<o:p>
'Now Read values from database i.e. table--> tblofferletter in the string variable strvalue.<o:p>
'Replace the values with the values in the string read from file2.rtf<o:p>
While myReader.Read<o:p>
strvalue = myReader.GetValue(0).ToString()<o:p>
str = Replace(swrtarget.ToString, "##refno##", Trim(strvalue))<o:p>
strvalue = myReader.GetValue(1).ToString()<o:p>
str = Replace(str.ToString, "##Title##", Trim(strvalue))<o:p>
strvalue = myReader.GetValue(2).ToString()<o:p>
str = Replace(str.ToString, "##FirstName##", Trim(strvalue))<o:p>
strvalue = myReader.GetValue(3).ToString()<o:p>
str = Replace(str.ToString, "##LName##", Trim(strvalue))<o:p>
strvalue = myReader.GetValue(4).ToString()<o:p>
<o:p>
End While<o:p>
myReader.Close()<o:p>
cmd.Dispose()<o:p>
<o:p>
' After replacing all the values write these values in the file2.rtf via the file write (s).<o:p>
s.WriteLine(str)<o:p>
'After writing the data in the stream writer the values must be flushed.<o:p>
s.Flush()<o:p>
'Close the stream writer.<o:p>
s.Close()<o:p>
fs1.Close()<o:p>
fs2.Close()<o:p>
<o:p>
'Initialise the srefno to nothing so that when new report is requested it will have the latest value.<o:p>
Srefno = Nothing<o:p>
<o:p>
<o:p>
Catch ex As Exception<o:p>
Response.Write("" + ex.Message)<o:p>
Response.Write("")<o:p>
<o:p>
Finally<o:p>
' Response.Write("OFFER LETTER GENERATED SUCCESSFULLY.")<o:p>
clsCon.closeCon()<o:p>
<o:p>
End Try<o:p>
<o:p>
End Sub
************************************************************************************
Advantages
<o:p>
- Easy maintainability – Changes in the template can be made easily and updated.
- Just Few Lines of code.