problem in importing table into Excel
-
hi In my application i want to import table from Access to Excel.I have include Microsoft Web Browser to display the excel sheet .When i am running the application.It display the sheet.But One error is pop up"The file can not access,it being used by another process" Can anyone give the sol.here is my code [code] Try Dim excel As New Excel.ApplicationClass Dim wBook As Excel.Workbook Dim wSheet As Excel.Worksheet wBook = excel.Workbooks.Add() wSheet = wBook.ActiveSheet() filltemp() Cmd.CommandType = CommandType.Text 'Cmd.CommandText = "Select * from SalRpt_temp" Cmd.CommandText = "SELECT a.srno as Srno,a.ename as 'Name Of Employee',a.pdays as 'Present Day',a.desig as Designation ,a.bs as Basic,a.dp as DP,a.total as Total,a.hra as HRA,a.cla as CLA,a.ta as TA,a.npa as NPA,a.other as'Other Allowance',a.tal as 'Total Allowance',a.net as 'Net Claim',a.pt as 'PTax',a.it as 'Income Tax',a.sal as 'SalAdv',a.pf as PF,a.lic as LIC,a.bank as 'Bank Recov',a.tdeduct as 'Total Deduction',a.netsal as 'Net Salary' FROM SalRpt_temp a" Cmd.Connection = con adp.SelectCommand = Cmd adp.Fill(ds) Dim dt As System.Data.DataTable = ds.Tables(0) Dim dc As System.Data.DataColumn Dim dr As System.Data.DataRow Dim colIndex As Integer = 0 Dim rowIndex As Integer = 1 For Each dc In dt.Columns colIndex = colIndex + 1 excel.Cells(1, colIndex) = dc.ColumnName Next For Each dr In dt.Rows rowIndex = rowIndex + 1 colIndex = 0 For Each dc In dt.Columns colIndex = colIndex + 1 excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName) Next Next 'AxWebBrowser1.Navigate("G:\Salary Package\salary.xls") wSheet.Columns.AutoFit() Dim strFileName As String = "G:\Salary Package\salary.xls" AxWebBrowser1.Navigate(strFileName) Dim blnFileOpen As Boolean = False Try Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName) fileTemp.Close() Catch ex As Exception blnFileOpen = False End Try If System.IO.File.Exists(strFileName) Then
-
hi In my application i want to import table from Access to Excel.I have include Microsoft Web Browser to display the excel sheet .When i am running the application.It display the sheet.But One error is pop up"The file can not access,it being used by another process" Can anyone give the sol.here is my code [code] Try Dim excel As New Excel.ApplicationClass Dim wBook As Excel.Workbook Dim wSheet As Excel.Worksheet wBook = excel.Workbooks.Add() wSheet = wBook.ActiveSheet() filltemp() Cmd.CommandType = CommandType.Text 'Cmd.CommandText = "Select * from SalRpt_temp" Cmd.CommandText = "SELECT a.srno as Srno,a.ename as 'Name Of Employee',a.pdays as 'Present Day',a.desig as Designation ,a.bs as Basic,a.dp as DP,a.total as Total,a.hra as HRA,a.cla as CLA,a.ta as TA,a.npa as NPA,a.other as'Other Allowance',a.tal as 'Total Allowance',a.net as 'Net Claim',a.pt as 'PTax',a.it as 'Income Tax',a.sal as 'SalAdv',a.pf as PF,a.lic as LIC,a.bank as 'Bank Recov',a.tdeduct as 'Total Deduction',a.netsal as 'Net Salary' FROM SalRpt_temp a" Cmd.Connection = con adp.SelectCommand = Cmd adp.Fill(ds) Dim dt As System.Data.DataTable = ds.Tables(0) Dim dc As System.Data.DataColumn Dim dr As System.Data.DataRow Dim colIndex As Integer = 0 Dim rowIndex As Integer = 1 For Each dc In dt.Columns colIndex = colIndex + 1 excel.Cells(1, colIndex) = dc.ColumnName Next For Each dr In dt.Rows rowIndex = rowIndex + 1 colIndex = 0 For Each dc In dt.Columns colIndex = colIndex + 1 excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName) Next Next 'AxWebBrowser1.Navigate("G:\Salary Package\salary.xls") wSheet.Columns.AutoFit() Dim strFileName As String = "G:\Salary Package\salary.xls" AxWebBrowser1.Navigate(strFileName) Dim blnFileOpen As Boolean = False Try Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName) fileTemp.Close() Catch ex As Exception blnFileOpen = False End Try If System.IO.File.Exists(strFileName) Then
Hi, I am also using same thing in mine project, however I don't have enough time to look into your code but I am sending u mine code so it will be easy for u to check it out what was wrong with your code.mine code is error free. let u try it......... Imports Microsoft.VisualBasic Imports Excel Imports System Imports com.lowagie.text Imports com.lowagie.text.Image Imports pdfWriter = com.lowagie.text.pdf.PdfWriter Imports com.lowagie.text.pdf.PdfPTable Imports java.io Public Const vbCenter = 2 Public Const vbRightJustify = 1 Public Const vbLeftJustify = 0 '/* Master Report Section Start * / Public Function BranchMaintenanceReport(ByVal strSearchText As String, Optional ByVal strUserNo As String = "") As String Dim objXL As New Excel.Application, wbXL As Excel.Workbook, wsXL As Excel.Worksheet Dim intRow As Long, intCol As Long, intColCount As Long, intSNo As Long Dim rs As New ADODB.Recordset, SQL As String 'Dim st As New ADODB.Stream Dim psUtil As New PensionSystemUtil Dim ctr As Integer Dim tempDir As String, fileName As String, filePath As String fileName = "" intRow = 0 intColCount = 3 wbXL = objXL.Workbooks.Add Try 'intRow = intRow + 1 'wsXL.Range(wsXL.Cells(1, 1), wsXL.Cells(1, intColCount)).Merge() 'SQL = "select * from sa_company_logo where company_code='" & p_company & "'" 'psUtil.ConnectToHDDB() 'rs = New ADODB.Recordset 'rs.Open(SQL, psUtil.conHD, 2, 3) 'If Not rs.EOF Then ' st = New ADODB.Stream ' st.Open() ' st.Type = ADODB.StreamTypeEnum.adTypeBinary ' st.Write(rs.Fields("company_logo").Value) ' If psUtil.IsPathExists(tempDir + "\CompanyLogo.jpg") Then ' psUtil.KillFile(tempDir + "\CompanyLogo.jpg") ' End If ' st.SaveToFile(tempDir + "\CompanyLogo.jpg", ADODB.SaveOptionsEnum.adSaveCreateOverWrite) ' wsXL.Shapes.AddPicture(tempDir + "\CompanyLogo.jpg", _ ' Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 650, 100) ' wsXL.Rows(1).RowHeight = wsXL.Shapes.Item(1).Height ' st = Nothing 'Else 'End If 'intRow = intRow + 1 ''-> Cell 3,1 Report Name and Report