Cách cấu hình MS Excel 20xx ứng dụng trên Web App


Trong quá trình coding các ứng dụng App trên nền Web service làm tính năng Export ra Excel tôi phát hiện lỗi sau:

1. Microsoft Office Excel cannot access the file ‘c:\inetpub\wwwroot\Timesheet\App_Data\Template.xlsx’

2. Nếu dùng hàm test để kiểm tra lỗi: test3.aspx (code behind test3.aspx.vb)

======Test3.aspx code ========

Imports Microsoft.Office.Interop.Excel
Imports System.IO
Imports System.Linq
Imports SystemFrameWork

Partial Class Test3
Inherits System.Web.UI.Page

Protected Sub btnTest_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnTest.Click
Dim strFile As String

strFile = SystemFrameWork.SystemConfig.AppRoot() + “App_Data\Template.xlsx”
Me.labMess.Text = strFile
If OpenExcel(strFile) Then
CloseExcel()
Me.labMess.Text = “OK OK”
End If

End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
‘Me.labMess.Text = “OK OK”

‘Dim fileName As String = SystemConfig.AppRoot + “\Template.xlsx”

‘Try
‘ Dim openExcel As New SMCOpenXML.OpenExcel
‘ Me.labMess.Text = openExcel.Open(fileName)

‘Catch ex As Exception
‘ Me.labMess.Text = ex.Message
‘End Try
‘Dim objDatabase As New DataAccess.Database

‘Dim strSQL As String

‘strSQL = txtSQL.Text.Trim ‘ “ALTER TABLE dbo.CongVan ADD TamDung int NULL”

‘If objDatabase.ExecuteSQL(strSQL) Then
‘ Me.labMess.Text = “OK”
‘Else
‘ Me.labMess.Text = objDatabase.GetException.Message
‘End If
End Sub
Protected mxlApp As Excel.Application
Protected mxlWorkBook As Excel.Workbook
Protected mxlWorkSheet As Excel.Worksheet
Protected mxlWorkSheetTemp As Excel.Worksheet

Protected _OutputPath As String
Protected _OutputFileName As String
Protected _OutputFile As String ‘ Full Name = Path & FileName

Protected _TemplatePath As String
Protected _TemplateFileName As String
Protected _TemplateFile As String ‘ Full Name = Path & FileName

Protected _LogFile As String

Public Sub writeLog(ByVal logMess As String, ByVal logContent As String)

‘If (String.IsNullOrEmpty(_LogFile)) Then
‘ Exit Sub
‘End If

Dim strFileName As String = String.Empty
Dim strLogString As String = String.Empty
‘get filename
strFileName = _LogFile
strLogString = vbCrLf
strLogString += “==== ” + Date.Now.ToShortDateString + ” ” + Date.Now.ToShortTimeString + ” ===<br />” + vbCrLf
strLogString += logMess + “<br/>” + vbCrLf
strLogString += logContent + “<br/>” + vbCrLf
strLogString += “=======================================================”

Me.labMess.Text += “<br/>” + strLogString

Exit Sub

‘Try
‘ Dim charset = System.Text.Encoding.GetEncoding(“UTF-8”)
‘ If Not File.Exists(strFileName) Then
‘ Dim oFile As FileStream
‘ oFile = File.Create(strFileName)
‘ Dim oReader As StreamWriter = New StreamWriter(oFile, charset)
‘ oReader.WriteLine(strLogString)
‘ oReader.Close()
‘ oFile.Close()
‘ Else
‘ ‘ Append text in file when file exitsed
‘ Dim oFile1 As FileStream = New FileStream(strFileName, FileMode.Open, FileAccess.Read, FileShare.Read)
‘ If oFile1.Length > 1048576 Then
‘ oFile1.Close()
‘ File.Delete(strFileName)
‘ Else
‘ oFile1.Close()
‘ End If
‘ Dim oReader As StreamWriter = New StreamWriter(strFileName, True, charset)
‘ oReader.WriteLine(strLogString)
‘ oReader.Close()
‘ End If
‘Catch ex As Exception
‘End Try
End Sub

Public Property LogFile() As String
Get
Return _LogFile
End Get
Set(ByVal value As String)
_LogFile = value
End Set
End Property

Public Property OutputPath() As String
Get
Return _OutputPath
End Get
Set(ByVal value As String)
If value.EndsWith(“\”) Then
_OutputPath = value
Else
_OutputPath = value + “\”
End If

End Set
End Property

Public Property OutputFileName() As String
Get
Return _OutputFileName
End Get
Set(ByVal value As String)
_OutputFileName = value
End Set
End Property

Public ReadOnly Property OutputFile() As String
Get
Return _OutputPath + _OutputFileName
End Get
End Property

Public Property TemplatePath() As String
Get
Return _TemplatePath
End Get
Set(ByVal value As String)
If value.EndsWith(“\”) Then
_TemplatePath = value
Else
_TemplatePath = value + “\”
End If

End Set
End Property

Public Property TemplateFileName() As String
Get
Return _TemplateFileName
End Get
Set(ByVal value As String)
_TemplateFileName = value
End Set
End Property

Public ReadOnly Property TemplateFile() As String
Get
Return _TemplatePath + _TemplateFileName
End Get
End Property

Public Function TemplateExist() As Boolean
Return SMCLib.SMCFile.FileExist(TemplateFile)
End Function

Public Function CopyTemplate() As Boolean
Return SMCLib.SMCFile.CopyFile(TemplateFile, OutputFile, True)
End Function

Protected Function OpenExcel(ByVal strFileName As String) As Boolean
Try
‘Start a new workbook in Excel
mxlApp = New Excel.Application ‘CreateObject(“Excel.Application”)
writeLog(“OpenExcel – FileName:”, “New Excel.Application”)
mxlApp.UserControl = True
Dim oldCI As System.Globalization.CultureInfo = _
System.Threading.Thread.CurrentThread.CurrentCulture
System.Threading.Thread.CurrentThread.CurrentCulture = _
New System.Globalization.CultureInfo(“en-US”)
mxlApp.Workbooks.Add()
‘add end
writeLog(“OpenExcel – FileName:”, strFileName)
mxlWorkBook = mxlApp.Workbooks.Open(strFileName)
mxlWorkSheet = mxlWorkBook.Worksheets(1)
mxlWorkSheetTemp = mxlWorkBook.Worksheets(1)
mxlApp.DisplayAlerts = False
Return True
Catch ex As Exception
If Not mxlWorkBook Is Nothing Then
mxlWorkBook.Close()
End If
mxlApp.Quit()
mxlApp = Nothing
writeLog(ex.Message, ex.StackTrace)
Return False
Finally
End Try
End Function

Protected Sub CloseExcel()
Try
mxlWorkSheet = Nothing
mxlWorkSheetTemp = Nothing
If Not mxlWorkBook Is Nothing Then
mxlWorkBook.Close()
End If
mxlApp.Quit()
mxlWorkBook = Nothing
mxlApp = Nothing
GC.Collect()
Catch ex As Exception
Throw ex
Finally
End Try
End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.labMess.Text = “Click button for testing Excel Automation”
Me.labMess.Text += vbCrLf + “(mxlApp = New Excel.Application)”
End Sub
End Class

==============

Màn lỗi xuất hiện:

Microsoft Office Excel cannot access the file ‘c:\inetpub\wwwroot\Timesheet\App_Data\Template.xlsx’. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.
at Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad)
at ExcelLib.ExcelBase.OpenExcel(String strFileName)

Cách giải quyết lỗi:

  1. Create directory “C:\Windows\SysWOW64\config\systemprofile\Desktop ” (for 64 bit Windows) or “C:\Windows\System32\config\systemprofile\Desktop ” (for 32 bit Windows)
  2. Set Full control permissions for directory Desktop (for example in Win7 & IIS 7 & DefaultAppPool set permissions for user “IIS AppPool\DefaultAppPool”)
  3. Thêm cấu hình DCOM cho Microsoft Excel được phép chạy cùng Application Pool trên nền Web Service:

1

2

3

4

5

6

7

8

9

10

11

 

Bây giờ chúng ta chạy lại địa chỉ web dẫn đến file test3.aspx nếu đã sửa xong

test3aspx

About thangletoan

Hallo Aloha

Posted on 16/12/2013, in Excel Online, Help 24x7, IIS 7.5, Microsoft Excel, Microsoft Office 365, Microsoft Office365, Tài chính kế toán bằng Excel, Thống kê và thuật toán, Visual Basic, Visual Basic 6, Visual Studio 2010, Web. Bookmark the permalink. Để lại bình luận.

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s

%d bloggers like this: