My current console application project uses office.interop.excel to create and update excel files. This project is being upgraded and is moving to new servers where office will not be installed.
I have seen many questions asking about creating excel files without office installed and saw many answers to use EPPlus, ClosedXml etc. But just curious to see whether an excel file can be created using office developer extensions?
If yes, could anyone provide me an example?
My current application's code is similar to below. I removed most of the code and the below code is not compiled. It was shown just to give an idea.
Imports Excel = Microsoft.Office.Interop.Excel
Public Class MyExcelClass
Private Sub Export()
Dim workbooks As Excel.Workbooks = exc.Workbooks
Dim myArrayList = GetData() 'Records in ArrayList
Dim workbook As Excel.Workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
''one sheet
Dim sheets As Excel.Sheets = workbook.Worksheets
Dim worksheet1 As Excel.Worksheet = CType(sheets.Item(1), Excel.Worksheet)
If worksheet1 Is Nothing Then
WriteLog("ERROR: worksheet1 == null")
Exit Sub
Else
worksheet1.Name = "My excel sheet"
End If
''create one sheet
Me.CreateSheet(worksheet1, 1, myArrayList)
''save the spreadsheet!
Dim sFilename As String = "C:/1.xlsx"
workbook.SaveAs(sFilename)
Try
exc.Quit()
exc = Nothing
Catch ex As Exception
'ignore
End Try
End Sub
Private Sub CreateSheet(ByRef sheet As Excel.Worksheet, ByVal iSheetID As Integer, ByVal myArrayList As ArrayList)
With sheet
.Range("A1:Z10000").Font.Size = 10
'.... More code with range to set the alignment etc.
'.... Manipulate the arylRecords and modify the sheet
End With 'With sheet
End Sub
End Class
By "Offic Developer extensions" I assume you mean VSTO and possibly the newer JavaScript APIs. These are both technologies for interacting with the user inside the Office application interface. So 1) They don't work in a server environment and 2) they require the licensed installation of Office.
From the Microsoft/Visual Studio side the Office Open XMl file formats were designed for creating Office documents in a server environment and the Open XML SDK is the "developer friendly" way to do it. It does have performance issues when it comes to very large Excel workbooks, however.
I imagine the tools you mention also leverage the OOXML file format, although I'm not familiar with them.
One place to discuss tools and approaches for working with the Open XML file formats is OpenXmlDeveloper.org.