Add Sheet Dynamically while generating Report from ASP.Net in Excel Format.
-
Hi, I am using following code to generate report. I am using template for report header.It works fine. I want report should generate on individual sheet for each office. Currently Data of all sheet display on single sheet. How i will add sheet dynamically for each office. Private Sub CreateConsolidatedXLSheet(ByVal RptDate As String, ByVal endtime As String, ByVal conDate As String, ByRef ConsRptDS As DataSet) Dim rpttype As String = "SummaryRpt" Response.ClearContent() 'Response.AddHeader("Content-Disposition", "attachment; filename=" + FileName + ".xls") Response.AddHeader("Content-Disposition", "attachment; filename=" + FileName + ".xls") Response.ContentType = "application/vnd.ms-excel" Dim headerfile As String = Server.MapPath("Reports\Header_" + rpttype + ".html") Dim hreader As System.IO.StreamReader = New System.IO.StreamReader(headerfile) Dim headerdata As String = hreader.ReadToEnd() hreader.Close() Dim username As String = TTUser.GetDisplayNameFromDB(context.User.Identity.Name) headerdata = headerdata.Replace("##curdate##", Format(CDate(RptDate), "MMMM-dd,yyyy")) Response.Write(headerdata) Dim drow As DataRow Dim dcolum As DataColumn Dim numcols As New ArrayList() Dim slno As Int16 = 1 Dim LRow As String = ConsRptDS.Tables(0).Rows.Count.ToString For Each drow In ConsRptDS.Tables(0).Rows Response.Write("<tr>") Dim colix As Int16 For colix = 0 To drow.ItemArray.Length - 1 Dim strnum As String = "" If colix = 0 Then Response.Write("<td x:str>") Response.Write(drow(colix)) 'tz.GetPST(drow(colix))) Response.Write("</td>") Else Response.Write("<td x:num >") Response.Write(drow(colix)) Response.Write("</td>") End If Next Next Response.End() End Sub
Binod K. (Miles to go before I sleep)
-
Hi, I am using following code to generate report. I am using template for report header.It works fine. I want report should generate on individual sheet for each office. Currently Data of all sheet display on single sheet. How i will add sheet dynamically for each office. Private Sub CreateConsolidatedXLSheet(ByVal RptDate As String, ByVal endtime As String, ByVal conDate As String, ByRef ConsRptDS As DataSet) Dim rpttype As String = "SummaryRpt" Response.ClearContent() 'Response.AddHeader("Content-Disposition", "attachment; filename=" + FileName + ".xls") Response.AddHeader("Content-Disposition", "attachment; filename=" + FileName + ".xls") Response.ContentType = "application/vnd.ms-excel" Dim headerfile As String = Server.MapPath("Reports\Header_" + rpttype + ".html") Dim hreader As System.IO.StreamReader = New System.IO.StreamReader(headerfile) Dim headerdata As String = hreader.ReadToEnd() hreader.Close() Dim username As String = TTUser.GetDisplayNameFromDB(context.User.Identity.Name) headerdata = headerdata.Replace("##curdate##", Format(CDate(RptDate), "MMMM-dd,yyyy")) Response.Write(headerdata) Dim drow As DataRow Dim dcolum As DataColumn Dim numcols As New ArrayList() Dim slno As Int16 = 1 Dim LRow As String = ConsRptDS.Tables(0).Rows.Count.ToString For Each drow In ConsRptDS.Tables(0).Rows Response.Write("<tr>") Dim colix As Int16 For colix = 0 To drow.ItemArray.Length - 1 Dim strnum As String = "" If colix = 0 Then Response.Write("<td x:str>") Response.Write(drow(colix)) 'tz.GetPST(drow(colix))) Response.Write("</td>") Else Response.Write("<td x:num >") Response.Write(drow(colix)) Response.Write("</td>") End If Next Next Response.End() End Sub
Binod K. (Miles to go before I sleep)
This is the sample to create differenct sheets in same excel sheet try this Microsoft.Office.Interop.Excel.Application _excel; Microsoft.Office.Interop.Excel._Workbook _wrkBk; Microsoft.Office.Interop.Excel._Worksheet _excelSheet; Microsoft.Office.Interop.Excel.Range _oRng; //create an object og Excel Application. _excel = new Microsoft.Office.Interop.Excel.Application(); _excel.Visible = false; //Get a new workbook. _wrkBk = (Microsoft.Office.Interop.Excel._Workbook)(_excel.Workbooks.Add(Missing.Value)); _excelSheet = (Microsoft.Office.Interop.Excel._Worksheet)_wrkBk.ActiveSheet; Microsoft.Office.Interop.Excel._Worksheet _tempSheet = (Microsoft.Office.Interop.Excel.Worksheet)_wrkBk.Worksheets["Sheet1"]; int _defaultSheetCount = _wrkBk.Worksheets.Count; for (int j = 0; j < dt.Rows.Count; j++) { if ((j + 1) <= _defaultSheetCount) { _excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)_wrkBk.Worksheets["Sheet" + (j + 1)]; } else { _excelSheet = (Microsoft.Office.Interop.Excel._Worksheet)_wrkBk.Sheets.Add(Type.Missing, _tempSheet, Type.Missing, Type.Missing); } _tempSheet = _excelSheet; //write the data in the predefined format to the Excel Sheet. _excelSheet.Cells[1, 3] = "test";
Thanks & Regards, NeW OnE, please don't forget to vote on the post