Sudhanshum
asked on
Microsoft.Office.Interop not working from IIS
Hi I am using Microsoft.Office.Interop, I have added its DLL on BIN folder and using its object, which is working fine when I run code through Visual Studio but when I run exact same code from IIS then It gets failed, I tried to find solution but its not working, Please suggest what I can do to fix that. Below is my code:
Dim xlapp As Excel.Application
Dim xlworkbook As Excel.Workbook
Dim xlworksheet As Excel.Worksheet
Dim misvalue As Object = Reflection.Missing.Value
Dim sheetIndex As Integer = 0
xlapp = New Excel.Application
xlworkbook = xlapp.Workbooks.Add(misval ue)
xlworksheet = xlworkbook.Sheets("Sheet1" )
sheetIndex += 1
' Copy the DataTable to an object array
Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object
' Copy the column names to the first row of the object array
For col = 0 To dt.Columns.Count - 1
rawData(0, col) = dt.Columns(col).ColumnName
Next
' Copy the values to the object array
For col = 0 To dt.Columns.Count - 1
For row = 0 To dt.Rows.Count - 1
rawData(row + 1, col) = dt.Rows(row).ItemArray(col )
Next
Next
' Calculate the final column letter
Dim finalColLetter As String = String.Empty
Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXY Z"
Dim colCharsetLen As Integer = colCharset.Length
If dt.Columns.Count > colCharsetLen Then
finalColLetter = colCharset.Substring( _
(dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
End If
finalColLetter += colCharset.Substring( _
(dt.Columns.Count - 1) Mod colCharsetLen, 1)
' Create a new Sheet
xlworksheet = CType( _
xlworkbook.Sheets.Add(xlwo rkbook.She ets(sheetI ndex), _
Type.Missing, 1, Excel.XlSheetType.xlWorksh eet), Excel.Worksheet)
xlworksheet.Name = "Test"
' Fast data export to Excel
Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1)
xlworksheet.Range(excelRan ge, Type.Missing).Value2 = rawData
' Mark the first row as BOLD
CType(xlworksheet.Rows(1, Type.Missing), Excel.Range).Font.Bold = True
xlworksheet = Nothing
xlworkbook.SaveAs("C:\Log\ " + txtName.Text + ".xlsx")
xlworkbook.Close(True, Type.Missing, Type.Missing)
xlworkbook = Nothing
' Release the Application object
xlapp.Quit()
xlapp = Nothing
Dim xlapp As Excel.Application
Dim xlworkbook As Excel.Workbook
Dim xlworksheet As Excel.Worksheet
Dim misvalue As Object = Reflection.Missing.Value
Dim sheetIndex As Integer = 0
xlapp = New Excel.Application
xlworkbook = xlapp.Workbooks.Add(misval
xlworksheet = xlworkbook.Sheets("Sheet1"
sheetIndex += 1
' Copy the DataTable to an object array
Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object
' Copy the column names to the first row of the object array
For col = 0 To dt.Columns.Count - 1
rawData(0, col) = dt.Columns(col).ColumnName
Next
' Copy the values to the object array
For col = 0 To dt.Columns.Count - 1
For row = 0 To dt.Rows.Count - 1
rawData(row + 1, col) = dt.Rows(row).ItemArray(col
Next
Next
' Calculate the final column letter
Dim finalColLetter As String = String.Empty
Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXY
Dim colCharsetLen As Integer = colCharset.Length
If dt.Columns.Count > colCharsetLen Then
finalColLetter = colCharset.Substring( _
(dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
End If
finalColLetter += colCharset.Substring( _
(dt.Columns.Count - 1) Mod colCharsetLen, 1)
' Create a new Sheet
xlworksheet = CType( _
xlworkbook.Sheets.Add(xlwo
Type.Missing, 1, Excel.XlSheetType.xlWorksh
xlworksheet.Name = "Test"
' Fast data export to Excel
Dim excelRange As String = String.Format("A1:{0}{1}",
xlworksheet.Range(excelRan
' Mark the first row as BOLD
CType(xlworksheet.Rows(1, Type.Missing), Excel.Range).Font.Bold = True
xlworksheet = Nothing
xlworkbook.SaveAs("C:\Log\
xlworkbook.Close(True, Type.Missing, Type.Missing)
xlworkbook = Nothing
' Release the Application object
xlapp.Quit()
xlapp = Nothing
Hi Sudhanshum,
For Server Side manipulation of Office documents, I strongly recommend that you use OpenXML SDK https://docs.microsoft.com /en-us/off ice/open-x ml/open-xm l-sdk
This SDK is provided by Microsoft at free of cost and is used in many applications world wide. It has a little learning curve but it is a low-risk - high-gain case. Once you have understood how it works, you can manipulate all three - Word, Excel and PowerPoint files.
They have couple of Excel examples listed here: https://docs.microsoft.com /en-us/off ice/open-x ml/spreads heets
If you want to explore this option, please have a look at my article: https://www.experts-exchan ge.com/art icles/3304 8/A-helper -class-to- perform-CR UD-on-Micr osoft-Offi ce-documen ts-Custom- Properties -without-h eadache.ht ml. It has steps which you can use to get started with OpenXml SDK.
All the best.
Regards,
Chinmay.
For Server Side manipulation of Office documents, I strongly recommend that you use OpenXML SDK https://docs.microsoft.com
This SDK is provided by Microsoft at free of cost and is used in many applications world wide. It has a little learning curve but it is a low-risk - high-gain case. Once you have understood how it works, you can manipulate all three - Word, Excel and PowerPoint files.
They have couple of Excel examples listed here: https://docs.microsoft.com
If you want to explore this option, please have a look at my article: https://www.experts-exchan
All the best.
Regards,
Chinmay.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Since it is for a server and you shouldn't install on a computer without a license, and Office does not really support multithreaded apps, you should rely on 3rd party components like https://products.aspose.com/total/net
From there site: