Solved

Unable to create Excel file in vb.net using Open XML / OpenDocument SDK

Posted on 2014-09-30
4
700 Views
Last Modified: 2014-10-10
I'm struggling with a  vb.Net project to create a simple Excel file using Open XML.  I have installed Open XML 2.5 and have added WindowsBase 4.0.0.0 and DocumentFormat.OpenXml 2.5.5631.0 to the project and the code below compiles fine.

The problem is the resulting file is corrupt.  I would upload it but Experts Exchange is reporting an error when I try.

Is anything obviously wrong with the code below?  I have found posts referencing a OpenDocument Tool I should be able to use to view the file but I don't seem to have it on my system and cannot find where to download it.

        Dim excelStream As New MemoryStream()

        Using spreadSheet = SpreadsheetDocument.Create(excelStream, SpreadsheetDocumentType.Workbook)
            'Create workbook
            spreadSheet.AddWorkbookPart()
            spreadSheet.WorkbookPart.Workbook = New Workbook()

            'Create worksheet
            spreadSheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
            spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = New Worksheet()

            'Create worksheet
            spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(New SheetData())

            'Create Header Row
            Dim row = spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(New Row())
            row.Append(New Cell With {.DataType = CellValues.String, .CellValue = New CellValue("TestHeader")})

            'Save worksheet
            spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save()

            'Create the worksheet to workbook relation
            spreadSheet.WorkbookPart.Workbook.AppendChild(New Sheets())
            spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets).AppendChild(
                New Sheet() With
                {
                    .Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),
                    .SheetId = 1,
                    .Name = "StockNbr"
                }
            )

            'Save and reset stream position
            spreadSheet.WorkbookPart.Workbook.Save()
        End Using

        excelStream.Position = 0
        Dim fileOut As New FileStream("C:\test.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite)
        excelStream.CopyTo(fileOut)
        fileOut.Flush()
        fileOut.Close()

Open in new window

0
Comment
Question by:canuckconsulting
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 40355061
When you try to open the file with Excel, is there an option to view the log?  In the past, I have seen a log that tries to show the issues with opening the file.
0
 

Author Comment

by:canuckconsulting
ID: 40361255
No, I don't get any option.  I'm just advised that the file is corrupt.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40361552
I am thinking that it you shouldn't write to an .xlsx file since that is a specialize compressed package file structure, and Excel would expect that structure.
0
 

Author Closing Comment

by:canuckconsulting
ID: 40373172
It ended up being something local on my machine as compiling the same code and running it on another box worked fine.  I'm marking this as the answer to close the question though in my case it wasn't the issue.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question