Rename Excel Sheets Generated from SSRS using Document Map

• Download source - 3.41 KB

What Problem Does This Solution Solve?

IN SSRS, you can generate any multisheet Excel report containing document map as the first sheet. The limitation of SSRS is that it does not name sheets according to the document map. The sheet names are always default such as sheet1, sheet2, sheet3... In many of the forums, developers are looking for this solution. The article deals with rather a workaround to this problem.

How Does This Help Someone Else?

In projects involving SSRs reports, there often arises the need to rename Excel sheets according to the document map generated.

Document Map is the first sheet generated from SSRS which contains hyperlinks to different sheets in the Excel. For better readability of individual sheets, Excel sheets are renamed according to the document map. This approach is the simplest one considering it is configurable and easy to maintain.

How Does the Code Actually Work?

Here we have generated the scheduled SSRS report as an Excel file. The rename_excel utility renames all the sheets of this report, as per the names of the sheets in the document map, after which it mails it to the intended recipients. This utility is basically a console application which can also be scheduled after SSRS scheduler. I have tried to make this utility configurable using an XML file.

In the Main() function, after reading the configuration parameters from the config.xml, open the Excel sheet and change the hyperlink in the document map to link it to individual sheets.

Note: Excel sheet name supports less than 31 characters only. Sheet renaming is ignored if there are more than 31 characters. This limitation is taken care of in the code.

Once the sheets are renamed, save the Excel file as new Excel file and send the Excel as attachment to the intended recipients. Configure the SMTP server accordingly to send mails.

Using the Code

The zip file contains a class file RenameExcel.vb and a Configuration XML file Config.xml.

Config file elements:

• Path: Path where the report is located
• SaveTo: Path where the newly renamed Excel sheet will be saved
• FileName: File name which you want to give
• Row: Excel sheet Row number from where the document map starts
• Column: Excel sheet column number from where the document map starts
• ErrorLogfile: Log file which will write all the errors encountered, if any
• SMTPServer: SMTP server IP which will be used to send mails
• Subject: Subject of the mail
• Body: Body of the mail
• Body1: Extra body message if you need to add
• MailTo: Recipients of the mail
• MailFrom: Sender of the mail

Once you have configured according to your requirements, you can run the RenameExcel.exe.

You can also schedule the EXE.

Imports System.Web.Mail
Imports System.IO
Imports System.Xml
Imports Excel
Imports System.Text

Module RenameExcel

#Region "Declaration"
     Dim strErrorLog As String
    Dim strFileName As String = DateTime.Now().ToString("yyyyMMdd") + ".xls"
    Dim strSMTPServer, strSubject, strMailTo, strMailFrom, strBody As String

#End Region

#Region "Main"
     Sub Main()

        Dim xmlConfig As XmlTextReader
        Dim xmlMap As XmlTextReader
        Dim strPath, strSaveTo, strFile, strRow, strCol As String
        Dim strRange As String
        Dim strRangeArray As Array
        Dim xl As New Excel.ApplicationClass
        Dim xlBook As Excel.Workbook

        Try

            xmlConfig = New XmlTextReader("Config.xml")
            xmlConfig.Read()

            'Get the Config values from config file config.xml
            While Not xmlConfig.EOF
                xmlConfig.Read()
                If Not xmlConfig.IsStartElement Then
                    Exit While
                End If
                xmlConfig.Read()
                strPath = xmlConfig.ReadElementString("Path")
                strSaveTo = xmlConfig.ReadElementString("SaveTo")
                strFile = xmlConfig.ReadElementString("FileName")
                strRow = xmlConfig.ReadElementString("Row")
                strCol = xmlConfig.ReadElementString("Column")
                strErrorLog = xmlConfig.ReadElementString("ErrorLogFile")
                strSMTPServer = xmlConfig.ReadElementString("SMTPServer")
                strSubject = xmlConfig.ReadElementString("Subject") + _
			" " + DateTime.Now().ToString("MM/dd/yyyy")
                strBody = xmlConfig.ReadElementString("Body") + " " + _
			DateTime.Now().ToString("MM/dd/yyyy")
                strBody = strBody + xmlConfig.ReadElementString("Body1")
                strMailTo = xmlConfig.ReadElementString("MailTo")
                strMailFrom = xmlConfig.ReadElementString("MailFrom")
            End While

            Dim oMissing As Object
            oMissing = System.Reflection.Missing.Value

            Dim xlSheet As Excel.Worksheet
            Dim rnRange As Range
            Dim strSheetName As String

            'open the excel sheet from the given path configured in config.xml
            xlBook = xl.Workbooks.Open(strPath + strFile, oMissing, _
		oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, _
		oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing)

            xlSheet = xlBook.Worksheets(1)
            rnRange = xlSheet.UsedRange()
            strRangeArray = rnRange.Value2

            Dim iRow, iColumn As Integer

            iRow = strRow
            iColumn = strCol

            Dim strColumn As String
            strColumn = Convert.ToChar(iColumn + 64)

            'Code to rename the excel sheets and editing the hyperlink
            For iRow = iRow To xlBook.Worksheets.Count
                strSheetName = strRangeArray(iRow, iColumn)
                If strSheetName.Length > 31 Then
                    strSheetName = strSheetName.Substring(0, 31)
                End If
                xlBook.Sheets(iRow).Name = strSheetName.Split("/").GetValue(0)
                xlSheet.Range(strColumn + _
		iRow.ToString()).Hyperlinks(1).SubAddress = "'" & _
		xlBook.Sheets(iRow).Name & "'!A1"

            Next

            strFileName = strSaveTo + strFile + "_" + strFileName

            'Check if the daily report is already created 
            If FileExists(strFileName) Then
                Dim fiFile As New FileInfo(strFileName)
                fiFile.Delete()
            End If

            'save the new daily report generated at the location 
            'mentioned in the config file config.xml
            xlBook.SaveAs(strFileName)
            xlBook.Close()
            xl.Quit()

            'Send mail with attachment to users configured in the config.xml
            SendMailOneAttachment(strMailFrom, strMailTo, _
		strSubject, strBody, strFileName, "", "", strSMTPServer)

        Catch ex As Exception
            LogError(ex)
        End Try
    End Sub

#End Region

#Region "SendMailOneAttachment: Send the mail to users configured in the 
 onfig.xml along with attachment "

    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Send the mail to users configured in the config.xml
    ''' also send the daily report generated as new attachment 
    ''' </summary>
    ''' <param name="From"></param>
    ''' <param name="sendTo"></param>
    ''' <param name="Subject"></param>
    ''' <param name="Body"></param>
    ''' <param name="AttachmentFile"></param>
    ''' <param name="CC"></param>
    ''' <param name="BCC"></param>
    ''' <param name="SMTPServer"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[harshit]	7/13/2010	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub SendMailOneAttachment(ByVal From As String, _
      ByVal sendTo As String, ByVal Subject As String, _
      ByVal Body As String, _
      Optional ByVal AttachmentFile As String = "", _
      Optional ByVal CC As String = "", _
      Optional ByVal BCC As String = "", _
      Optional ByVal SMTPServer As String = "")

        Dim myMessage As MailMessage
        Dim oAttach As System.Web.Mail.MailAttachment
        Try
            myMessage = New MailMessage

            With myMessage
                .To = sendTo
                .From = From
                .Subject = Subject
                .Body = Body
                .BodyFormat = MailFormat.Text
                'CAN USER MAILFORMAT.HTML if you prefer

                If CC <> "" Then .Cc = CC
                If BCC <> "" Then .Bcc = ""

                If FileExists(AttachmentFile) Then _
                 .Attachments.Add(New System.Web.Mail.MailAttachment(strFileName))

            End With

            If SMTPServer <> "" Then _
               SmtpMail.SmtpServer = strSMTPServer
            SmtpMail.Send(myMessage)

        Catch ex As Exception
            LogError(ex)
        End Try

    End Sub
#End Region

<?xml version="1.0" encoding="utf-8" ?> 
<FilePath>
	<Path>D:\Report\</Path>
	<SaveTo>D:\</SaveTo>
	<FileName>Report</FileName>
	<Row>2</Row>
	<Column>2</Column>
	<ErrorLogFile>D:\Report\Error.log</ErrorLogFile>
	<SMTPServer>10.1.1.1</SMTPServer>
	<Subject>Report for</Subject>
	<Body>Attached is the Report for</Body> 
	<Body1>Please respond to this email if you have any questions.
Thank you
</Body1>
	<MailTo>a@a.com</MailTo>
	<MailFrom>a@a.com</MailFrom>
</FilePath>	

History

• 30^th July, 2010: Initial post