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.WriteFile(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***********@hotmail.com.
pls see below code.
regards,
Hari.
Public Function PrintExcel(ByRef 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.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "filename=" + strDir &
strFileName + ".xls")
Response.Write("<html
xmlns:x=""urn:schemas-microsoft-com:office:excel"">")
Response.Write("<head>")
Response.Write("<xml>")
Response.Write("<x:ExcelWorkbook>")
Response.Write("<x:ExcelWorksheets>")
Response.Write("<x:ExcelWorksheet>")
Response.Write("<x:Name>" & strFileName & "</x:Name>")
Response.Write("<x:WorksheetOptions>")
Response.Write("<x:Print>")
Response.Write("<x:ValidPrinterInfo/>")
Response.Write("</x:Print>")
Response.Write("</x:WorksheetOptions>")
Response.Write("</x:ExcelWorksheet>")
Response.Write("</x:ExcelWorksheets>")
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.PrintExcel(dr(strGroupHeaderGroupBy),
Response, intRow, intCol, strErrPath)
intRow = intRow + 1
oSecHeader.PrintExcel(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(strGroupHeaderGroupBy) & "</td></tr>"
Response.Write(strPrintMsg)
'myExcel.Cells(intRow, intCol) = "AE Code :" &
dr(strGroupHeaderGroupBy)
End If
If strReportId = 5 Then
'intRow = intRow + 2
intRow = intRow + 1
intCol = 1
strPrintMsg = "<tr><td>Dealer : </td><td> " &
dr(strGroupHeaderGroupBy) & "</td></tr>"
Response.Write(strPrintMsg)
'myExcel.Cells(intRow, intCol) = "Dealer :" &
dr(strGroupHeaderGroupBy)
End If
If strReportId = 16 Or strReportId = 17 Then
intRow = intRow + 1
intCol = 1
strPrintMsg = "<tr><td>Account Status :
</td><td> " & dr(strGroupHeaderGroupBy) & "</td></tr>"
Response.Write(strPrintMsg)
'myExcel.Cells(intRow, intCol) = "Account Status
:" & dr(strGroupHeaderGroupBy)
End If
strTempGpBy = dr(strGroupHeaderGroupBy)
End If
'print End of List statement after each section is over
If strTempGpBy <> dr(strGroupHeaderGroupBy) And
strReportId = 5 Then
intRow = intRow + 2
intCol = 1
strPrintMsg = "<tr><td>End 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>End of List : Dealer :
</td><td> " & strTempGpBy & "</td></tr>"
Response.Write(strPrintMsg)
'myExcel.Cells(intRow, intCol) = "Dealer :" &
dr(strGroupHeaderGroupBy)
End If
strTempGpBy = dr(strGroupHeaderGroupBy)
End If
If strTempGpBy <> dr(strGroupHeaderGroupBy) And
(strReportId = 6 Or strReportId = 7 Or strReportId = 8) Then
'intRow = intRow + 2
intRow = intRow + 1
intCol = 1
strPrintMsg = "<tr><td>End of List : AE Code :
</td><td> " & strTempGpBy & "</td></tr>"
Response.Write(strPrintMsg)
'myExcel.Cells(intRow, intCol) = "End of List : AE
Code : " & strTempGpBy
strTempGpBy = dr(strGroupHeaderGroupBy)
End If
If strTempGpBy <> dr(strGroupHeaderGroupBy) And
strReportId = 9 Then
'intRow = intRow + 2
intRow = intRow + 1
intCol = 1
If strTempGpBy <> "" Then
strPrintMsg = "<tr><td>End 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(strGroupHeaderGroupBy) & "</td></tr>"
Response.Write(strPrintMsg)
'myExcel.Cells(intRow, intCol) = "AE Code :"
& dr(strGroupHeaderGroupBy)
End If
End If
strTempGpBy = dr(strGroupHeaderGroupBy)
End If
If strTempGpBy <> dr(strGroupHeaderGroupBy) And
(strReportId = 16 Or strReportId = 17) Then
'intRow = intRow + 2
intRow = intRow + 1
intCol = 1
If strTempGpBy <> "" Then
strPrintMsg = "<tr><td>End 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>Account Status :
</td><td> " & dr(strGroupHeaderGroupBy) & "</td></tr>"
Response.Write(strPrintMsg)
'myExcel.Cells(intRow, intCol) = "Account
Status :" & dr(strGroupHeaderGroupBy)
End If
End If
strTempGpBy = dr(strGroupHeaderGroupBy)
End If
'frame the filter
If strGroupHeaderFilter = "" Then
strCondition = strGroupHeaderGroupBy & "='" &
dr(strGroupHeaderGroupBy) & "'"
Else
strCondition = strGroupHeaderFilter & "='" &
dr(strGroupHeaderFilter) & "'"
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(strGroupHeaderGroupBy) & "</td></tr>"
Response.Write(strPrintMsg)
'myExcel.Cells(intRow, intCol) = "AE Code :" &
dr(strGroupHeaderGroupBy)
intRow = intRow + 1
intCol = 1
End If
If strReportId = 5 Then
intRow = intRow + 2
intCol = 1
strPrintMsg = "<tr><td>Dealer :</td><td> " &
dr(strGroupHeaderGroupBy) & "</td></tr>"
Response.Write(strPrintMsg)
'myExcel.Cells(intRow, intCol) = "Dealer :" &
dr(strGroupHeaderGroupBy)
intRow = intRow + 1
intCol = 1
End If
If strReportId = 16 Or strReportId = 17 Then
intRow = intRow + 2
intCol = 1
strPrintMsg = "<tr><td>Account Status :
</td><td> " & dr(strGroupHeaderGroupBy) & "</td></tr>"
Response.Write(strPrintMsg)
'myExcel.Cells(intRow, intCol) = "Account Status
:" & dr(strGroupHeaderGroupBy)
intRow = intRow + 1
intCol = 1
End If
strTempGpBy = dr(strGroupHeaderGroupBy)
End If
'print section detail for each section
Dim strGp As String
strGp = arrGpBy(intSecid)
If strGroupHeaderFilter = "" Then
oSecDtl.PrintExcel(Integer.Parse(strReportId.ToStr ing()), intSecid, strGp,
strCondition, oSecHeader, oSecST, PairingSecId, dr(strGroupHeaderGroupBy),
intSPCountOnEachSB, "", Response, intRow, intCol, False, strErrPath)
Else
oSecDtl.PrintExcel(Integer.Parse(strReportId.ToStr ing()), intSecid, strGp,
strCondition, oSecHeader, oSecST, PairingSecId, dr(strGroupHeaderGroupBy),
intSPCountOnEachSB, dr(strGroupHeaderFilter), 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(strGroupHeaderGroupBy) And
intTmpCount >= dtClientDetails.Rows.Count And intSecid = SectionCount Then
If strReportId = 5 Then
intRow = intRow + 2
intCol = 1
strPrintMsg = "<tr><td>End 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>End 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>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.</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>End 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.GroupHeaderForExcelSummary(Response, intRow,
intCol, strErrPath)
intRow = intRow + 1
End If
Dim tmpCount As Integer
Dim strGp1 As String
For intTmpCount = 0 To intSummaryCount - 1
strGp1 = arrGpBy(ArSummarySB(intTmpCount))
tmpCount =
Integer.Parse(ArSummarySB(intTmpCount).ToString()) - 1
'print section header
oSecHeader.PrintExcel(Response, ArSummarySB(intTmpCount),
intRow, intCol, strErrPath)
'print section detail
oSecDtl.PrintExcel(Integer.Parse(strReportId.ToStr ing()),
ArSummarySB(intTmpCount), strGp1, "", oSecHeader, oSecST, PairingSecId, "",
intSPCountOnEachSB, "", Response, intRow, intCol, True, strErrPath)
intRow = intRow + 1 'after printing each section leave 2
blank rows
Next
strFileName = strFileName.Replace("DATE", strDate)
'Calculate.GenerateDate())
Response.Write("</table></body></html>")
strDir = strDir & strDate & "\"
'strDir = "c:\Reports\DailyReports\20040812"
If Not Directory.Exists(strDir) Then
Directory.CreateDirectory(strDir)
End If
If Not File.Exists(strDir & "\" & strFileName & ".xls") Then
File.Create(strDir & "\" & strFileName & ".xls")
End If
Response.WriteFile(strDir & "\" & strFileName & ".xls")
Response.Flush()
Response.End()
Catch ex As Exception
ErrorLog.Log(strErrPath, ex.Message, ex.Source, ex.StackTrace)
End Try
End Function
