0

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
user007
  • 1,504
  • 2
  • 18
  • 51
  • The ACE driver which is used typically for working with data from MS Access can also read-write data for MS Excel and it does NOT require office to be installed but you are working with DATA, not pretty formatting and the like. You can also purchase some third party tools that will allow you to manipulate excel as well. – Steve Feb 08 '16 at 18:52

1 Answers1

0

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.

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43