hi all,
My requirement is to generate xl reports throu Asp.Net without installing
xl on web server computer.
i am using Response object and wrtifile method as below. i dont know
whether it is correct, but giving error says " file is accessed by other
process cannot access"
I have some logic inbetween and write the info using response.write in to
html format Ex: Response.Write( "<td>" & "client_no" & "</td>").
I am getting the error at :
Response.WriteF ile(strDir & "\" & strFileName & ".xls")
line. says " xls file is accessed by other process and cannot access"
pls help me how to do this. if possible pls mail the code to
vh***********@h otmail.com.
pls see below code.
regards,
Hari.
Public Function PrintExcel(ByRe f Response As HttpResponse)
Dim intSecid As Integer
Dim strCondition As String
Dim strTempGpBy As String
Dim intTmpCount As Integer
Dim IsHdrFirstTime As Boolean
Dim intCol As Integer
Dim intRow As Integer
Dim intRecCount As Integer
Dim strPrintMsg As String
intTmpCount = 0
IsHdrFirstTime = True
intRow = 1
intCol = 1
Response.Conten tType = "applicatio n/vnd.ms-excel"
Response.AddHea der("content-disposition", "filename=" + strDir &
strFileName + ".xls")
Response.Write( "<html
xmlns:x=""urn:s chemas-microsoft-com:office:exce l"">")
Response.Write( "<head>")
Response.Write( "<xml>")
Response.Write( "<x:ExcelWorkbo ok>")
Response.Write( "<x:ExcelWorksh eets>")
Response.Write( "<x:ExcelWorksh eet>")
Response.Write( "<x:Name>" & strFileName & "</x:Name>")
Response.Write( "<x:WorksheetOp tions>")
Response.Write( "<x:Print>" )
Response.Write( "<x:ValidPrinte rInfo/>")
Response.Write( "</x:Print>")
Response.Write( "</x:WorksheetOpti ons>")
Response.Write( "</x:ExcelWorkshee t>")
Response.Write( "</x:ExcelWorkshee ts>")
Response.Write( "</x:ExcelWorkbook >")
Response.Write( "</xml>")
Response.Write( "</head>")
Response.Write( "<body>")
Response.Write( "<table>")
Response.Write( "<tr></tr>")
Try
intRecCount = dtClientDetails .Rows.Count
For Each dr As DataRow In dtClientDetails .Rows 'group header loop
intTmpCount = intTmpCount + 1
'for each sub report
For intSecid = 1 To SectionCount
'print section header
If IsHdrFirstTime = True Then ' IsHdrFirstTime= true =>
only one section header should come on each page
oGrpHeader.Prin tExcel(dr(strGr oupHeaderGroupB y),
Response, intRow, intCol, strErrPath)
intRow = intRow + 1
oSecHeader.Prin tExcel(Response , intSecid, intRow,
intCol, strErrPath)
IsHdrFirstTime = False
End If
'frame the beginning text for each section
If strTempGpBy = "" Then
If strReportId = 9 Then
'intRow = intRow + 2
intRow = intRow + 1
intCol = 1
strPrintMsg = "<tr><td>AE Code : </td><td> " &
dr(strGroupHead erGroupBy) & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "AE Code :" &
dr(strGroupHead erGroupBy)
End If
If strReportId = 5 Then
'intRow = intRow + 2
intRow = intRow + 1
intCol = 1
strPrintMsg = "<tr><td>De aler : </td><td> " &
dr(strGroupHead erGroupBy) & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "Dealer :" &
dr(strGroupHead erGroupBy)
End If
If strReportId = 16 Or strReportId = 17 Then
intRow = intRow + 1
intCol = 1
strPrintMsg = "<tr><td>Accoun t Status :
</td><td> " & dr(strGroupHead erGroupBy) & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "Account Status
:" & dr(strGroupHead erGroupBy)
End If
strTempGpBy = dr(strGroupHead erGroupBy)
End If
'print End of List statement after each section is over
If strTempGpBy <> dr(strGroupHead erGroupBy) And
strReportId = 5 Then
intRow = intRow + 2
intCol = 1
strPrintMsg = "<tr><td>En d of List : Dealer :
</td><td> " & strTempGpBy & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "End of List :
Dealer : " & strTempGpBy
If intTmpCount <= intRecCount Then
'intRow = intRow + 2
intRow = intRow + 1
intCol = 1
strPrintMsg = "<tr><td>En d of List : Dealer :
</td><td> " & strTempGpBy & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "Dealer :" &
dr(strGroupHead erGroupBy)
End If
strTempGpBy = dr(strGroupHead erGroupBy)
End If
If strTempGpBy <> dr(strGroupHead erGroupBy) And
(strReportId = 6 Or strReportId = 7 Or strReportId = 8) Then
'intRow = intRow + 2
intRow = intRow + 1
intCol = 1
strPrintMsg = "<tr><td>En d of List : AE Code :
</td><td> " & strTempGpBy & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "End of List : AE
Code : " & strTempGpBy
strTempGpBy = dr(strGroupHead erGroupBy)
End If
If strTempGpBy <> dr(strGroupHead erGroupBy) And
strReportId = 9 Then
'intRow = intRow + 2
intRow = intRow + 1
intCol = 1
If strTempGpBy <> "" Then
strPrintMsg = "<tr><td>En d of List : AE Code :
</td><td> " & strTempGpBy & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "End of List :
AE Code : " & strTempGpBy
If intTmpCount <= intRecCount Then
intRow = intRow + 1
intCol = 1
strPrintMsg = "<tr><td>AE Code : </td><td> "
& dr(strGroupHead erGroupBy) & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "AE Code :"
& dr(strGroupHead erGroupBy)
End If
End If
strTempGpBy = dr(strGroupHead erGroupBy)
End If
If strTempGpBy <> dr(strGroupHead erGroupBy) And
(strReportId = 16 Or strReportId = 17) Then
'intRow = intRow + 2
intRow = intRow + 1
intCol = 1
If strTempGpBy <> "" Then
strPrintMsg = "<tr><td>En d of List : Account
Status : </td><td> " & strTempGpBy & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "End of List :
Account Status : " & strTempGpBy
If intTmpCount <= intRecCount Then
intRow = intRow + 1
intCol = 1
strPrintMsg = "<tr><td>Accoun t Status :
</td><td> " & dr(strGroupHead erGroupBy) & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "Account
Status :" & dr(strGroupHead erGroupBy)
End If
End If
strTempGpBy = dr(strGroupHead erGroupBy)
End If
'frame the filter
If strGroupHeaderF ilter = "" Then
strCondition = strGroupHeaderG roupBy & "='" &
dr(strGroupHead erGroupBy) & "'"
Else
strCondition = strGroupHeaderF ilter & "='" &
dr(strGroupHead erFilter) & "'"
End If
If strTempGpBy = "" Then
If strReportId = 9 Or strReportId = 6 Or strReportId
= 7 Then
intRow = intRow + 2
intCol = 1
strPrintMsg = "<tr><td>AE Code :</td><td> " &
dr(strGroupHead erGroupBy) & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "AE Code :" &
dr(strGroupHead erGroupBy)
intRow = intRow + 1
intCol = 1
End If
If strReportId = 5 Then
intRow = intRow + 2
intCol = 1
strPrintMsg = "<tr><td>De aler :</td><td> " &
dr(strGroupHead erGroupBy) & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "Dealer :" &
dr(strGroupHead erGroupBy)
intRow = intRow + 1
intCol = 1
End If
If strReportId = 16 Or strReportId = 17 Then
intRow = intRow + 2
intCol = 1
strPrintMsg = "<tr><td>Accoun t Status :
</td><td> " & dr(strGroupHead erGroupBy) & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "Account Status
:" & dr(strGroupHead erGroupBy)
intRow = intRow + 1
intCol = 1
End If
strTempGpBy = dr(strGroupHead erGroupBy)
End If
'print section detail for each section
Dim strGp As String
strGp = arrGpBy(intSeci d)
If strGroupHeaderF ilter = "" Then
oSecDtl.PrintEx cel(Integer.Par se(strReportId. ToString()), intSecid, strGp,
strCondition, oSecHeader, oSecST, PairingSecId, dr(strGroupHead erGroupBy),
intSPCountOnEac hSB, "", Response, intRow, intCol, False, strErrPath)
Else
oSecDtl.PrintEx cel(Integer.Par se(strReportId. ToString()), intSecid, strGp,
strCondition, oSecHeader, oSecST, PairingSecId, dr(strGroupHead erGroupBy),
intSPCountOnEac hSB, dr(strGroupHead erFilter), Response, intRow, intCol,
False, strErrPath)
End If
'print End of List statement if the printing has reached
the end of the report
If strTempGpBy = dr(strGroupHead erGroupBy) And
intTmpCount >= dtClientDetails .Rows.Count And intSecid = SectionCount Then
If strReportId = 5 Then
intRow = intRow + 2
intCol = 1
strPrintMsg = "<tr><td>En d of List : Dealer :
</td><td> " & strTempGpBy & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "End of List :
Dealer : " & strTempGpBy
End If
If strReportId = 6 Or strReportId = 7 Or strReportId
= 9 Or strReportId = 8 Then
intRow = intRow + 2
intCol = 1
strPrintMsg = "<tr><td>En d of List : AE code
:</td><td> " & strTempGpBy & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "End of List :
AE code : " & strTempGpBy
End If
If strReportId = 9 Then
intRow = intRow + 2
intCol = 1
strPrintMsg = "<tr><td>Pe r FTA 12A. We have to
immidietely notify the authority if any account is under margined by any
amount which exceeds 100% of our ANC.</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "Per FTA 12A.
We have to immidietely notify the authority if any account is under margined
by any amount which exceeds 100% of our ANC."
End If
If strReportId = 16 Or strReportId = 17 Then
intRow = intRow + 2
intCol = 1
strPrintMsg = "<tr><td>En d of List : Account
Status :</td><td> " & strTempGpBy & "</td></tr>"
Response.Write( strPrintMsg)
'myExcel.Cells( intRow, intCol) = "End of List :
Account Status :" & strTempGpBy
End If
End If
Next
Next
'print summary section body
intRow = intRow + 2
intCol = 1
If SectionCount < 1 Then
oGrpHeader.Grou pHeaderForExcel Summary(Respons e, intRow,
intCol, strErrPath)
intRow = intRow + 1
End If
Dim tmpCount As Integer
Dim strGp1 As String
For intTmpCount = 0 To intSummaryCount - 1
strGp1 = arrGpBy(ArSumma rySB(intTmpCoun t))
tmpCount =
Integer.Parse(A rSummarySB(intT mpCount).ToStri ng()) - 1
'print section header
oSecHeader.Prin tExcel(Response , ArSummarySB(int TmpCount),
intRow, intCol, strErrPath)
'print section detail
oSecDtl.PrintEx cel(Integer.Par se(strReportId. ToString()),
ArSummarySB(int TmpCount), strGp1, "", oSecHeader, oSecST, PairingSecId, "",
intSPCountOnEac hSB, "", Response, intRow, intCol, True, strErrPath)
intRow = intRow + 1 'after printing each section leave 2
blank rows
Next
strFileName = strFileName.Rep lace("DATE", strDate)
'Calculate.Gene rateDate())
Response.Write( "</table></body></html>")
strDir = strDir & strDate & "\"
'strDir = "c:\Reports\Dai lyReports\20040 812"
If Not Directory.Exist s(strDir) Then
Directory.Creat eDirectory(strD ir)
End If
If Not File.Exists(str Dir & "\" & strFileName & ".xls") Then
File.Create(str Dir & "\" & strFileName & ".xls")
End If
Response.WriteF ile(strDir & "\" & strFileName & ".xls")
Response.Flush( )
Response.End()
Catch ex As Exception
ErrorLog.Log(st rErrPath, ex.Message, ex.Source, ex.StackTrace)
End Try
End Function
