[Not a question] Reading excel to XML using OpenXML

Question

1.00/5 (1 vote)

See more:

Hi i am searching to read EXCEL sheet to XML format using Open XML-SDK 2.0 ,i am getting only for tables to store in DB and retrieving to XML format.But i need every thing(charts,tables,formulas) to XML format and to display in my jQuery Spreadsheet.so please help me t osolve it quickly.

thanks®ards,
Rajasekhar Reddy.k
[~~Email Removed~~]

Posted 8-Nov-13 18:43pm

Member 10015124

Updated 17-Nov-13 8:26am

Sergey Alexandrovich Kryukov

v3

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 9-Nov-13 12:23pm

Please don't post email. It will lead you spams.

Morgan Estes 12-Nov-13 11:26am

What have you tried so far?

Member 10015124 14-Nov-13 1:02am

Imports System.IO
Imports System.Text.RegularExpressions
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Public Class ConvertExcelToXml

'''

''' Read Data from selected excel file into DataTable
'''

''' <param name="filename">Excel File Path</param>
''' <returns>
Private Function ReadExcelFile(filename As String) As DataTable
' Initialize an instance of DataTable
Dim dt As New DataTable()

Try
' Use SpreadSheetDocument class of Open XML SDK to open excel file
Using spreadsheetDocument__1 As SpreadsheetDocument = SpreadsheetDocument.Open(filename, False)
' Get Workbook Part of Spread Sheet Document
Dim workbookPart As WorkbookPart = spreadsheetDocument__1.WorkbookPart

' Get all sheets in spread sheet document
Dim sheetcollection As IEnumerable(Of Sheet) = spreadsheetDocument__1.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)()

' Get relationship Id
Dim relationshipId As String = sheetcollection.First().Id.Value

' Get sheet1 Part of Spread Sheet Document
Dim worksheetPart As WorksheetPart = DirectCast(spreadsheetDocument__1.WorkbookPart.GetPartById(relationshipId), WorksheetPart)

' Get Data in Excel file
Dim sheetData As SheetData = worksheetPart.Worksheet.Elements(Of SheetData)().First()
Dim rowcollection As IEnumerable(Of Row) = sheetData.Descendants(Of Row)()

If rowcollection.Count() = 0 Then
Return dt
End If

' Add columns
For Each cell As Cell In rowcollection.ElementAt(0)
dt.Columns.Add(GetValueOfCell(spreadsheetDocument__1, cell))
Next

' Add rows into DataTable
For Each row As Row In rowcollection
Dim temprow As DataRow = dt.NewRow()
Dim columnIndex As Integer = 0
For Each cell As Cell In row.Descendants(Of Cell)()
' Get Cell Column Index
Dim cellColumnIndex As Integer = GetColumnIndex(GetColumnName(cell.CellReference))

If columnIndex < cellColumnIndex Then
Do
temprow(columnIndex) = String.Empty
columnIndex += 1

Loop While columnIndex < cellColumnIndex
End If

temprow(columnIndex) = GetValueOfCell(spreadsheetDocument__1, cell)
columnIndex += 1
Next

' Add the row to DataTable
' the rows include header row
dt.Rows.Add(temprow)
Next
End Using

' Here remove header row
dt.Rows.RemoveAt(0)
Return dt
Catch ex As IOException
Throw New IOException(ex.Message)
End Try
End Function

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

TnTinMn · Answer 1 · 2013-11-17T14:04:00

First off, if you can use the OpenXML library to open an read the spreadsheet, it is already stored as a zip package of XML files. You can prove this to yourself by changing the file extension to ".zip" and explore its contents.

You can use the "Imports System.IO.Packaging" namespace to open and retrieve these files. As I really have no idea what it is that you trying to extract, I will show you a simple method to list the contents and how to extract a given file as a String.

Create a new WinForm project. Add a project reference to "WindowsBase" and then paste this code to "Form1.vb". Modify the definition of "filepath" to point to one of your Excel spreadsheets and run the code. This will display a list of the contained files. Double-Click on a file name and a message box will displace its content.

Hopefully this will give you enough of a start to be able to extract what you are after.

For information on the Excel file format, see: http://msdn.microsoft.com/en-us/library/office/aa338205%28v=office.12%29.aspx#office2007aboutnewfileformat_structureoftheofficexmlformats[^]

' Add project ref: WindowsBase.dll (.Net Component)
Imports System.IO.Packaging
Public Class Form1

   Private WithEvents DataGridView1 As New DataGridView With {.Dock = DockStyle.Fill, .Parent = Me, .RowHeadersVisible = False}

   ' replace filepath with the path to your XML spreadsheet
   Private filepath As String = IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Spreadsheets\Book1.xlsx")

   Private paths As New List(Of PathItem) ' backing storage for Uri's in zippackage to display in datagridview

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      ' fill paths list with the 
      Using zp As System.IO.Packaging.ZipPackage = DirectCast(ZipPackage.Open(filepath, IO.FileMode.Open), ZipPackage)
         For Each pp As PackagePart In zp.GetParts
            paths.Add(New PathItem() With {.Path = pp.Uri})
         Next
         zp.Close()
      End Using

      DataGridView1.DataSource = paths
      DataGridView1.Columns("Path").ReadOnly = True
      DataGridView1.Columns("Path").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

   End Sub

   ' helper class to display the package part paths in a datagridview
   Private Class PathItem
      Private _Path As Uri
      Public Property Path() As Uri
         Get
            Return _Path
         End Get
         Set(ByVal value As Uri)
            _Path = value
         End Set
      End Property 'Path
   End Class 'PathItem

   Private Sub DataGridView1_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseDoubleClick
      Dim hti As DataGridView.HitTestInfo = DataGridView1.HitTest(e.X, e.Y)
      If hti.RowIndex > -1 Then
         Dim pp_path As Uri = CType(DataGridView1(hti.ColumnIndex, hti.RowIndex).Value, Uri)
         Using zp As ZipPackage = DirectCast(Package.Open(filepath, IO.FileMode.Open), ZipPackage)
            ' use GetXML to retrieve the the packagepart content
            ' for this demo, put it in a messagebox
            MsgBox(GetXML(zp.GetPart(pp_path)))
         End Using
      End If
   End Sub

   Private Function GetXML(ByVal pp As PackagePart) As String
      Using sr As New IO.StreamReader(pp.GetStream(IO.FileMode.Open, IO.FileAccess.Read))
         GetXML = sr.ReadToEnd()
         sr.Close()
      End Using
   End Function

End Class