user007 February 2016

Can an excel file be created/manipulated using Office developer tools?

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.
        '.... Man        

Answers


Cindy Meister February 2016

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.

Post Status

Asked in February 2016
Viewed 3,923 times
Voted 14
Answered 1 times

Search




Leave an answer