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