[不是问题]使用OpenXML读取excel到XML [英] [Not a question] Reading excel to XML using OpenXML

问题描述

您好我正在使用Open XML-SDK 2.0搜索EXCEL表格到XML格式，我只能将表存储在数据库中并检索到XML格式。但我需要所有东西（图表，表格，公式）来XML格式并在我的jQuery Spreadsheet中显示。请帮助我快速解决它。


$ b $bwice®ards，

Rajasekhar Reddy。 k

[ Email Removed ]

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]

推荐答案

首先，如果你可以使用OpenXML库打开一个阅读电子表格，它已经存储为XML文件的zip包。您可以通过将文件扩展名更改为.zip并浏览其内容来证明这一点。



您可以使用Imports System.IO.Packaging命名空间打开并检索这些文件。由于我真的不知道你试图提取什么，我将向你展示一个简单的方法来列出内容以及如何将一个给定的文件作为字符串提取。



创建一个新的WinForm项目。将项目引用添加到WindowsBase，然后将此代码粘贴到Form1.vb。修改filepath的定义以指向其中一个Excel电子表格并运行代码。这将显示包含文件的列表。双击文件名和消息框将取代其内容。



希望这将为您提供足够的开始，以便能够提取您的内容。



有关Excel文件格式的信息，请参阅： http://msdn.microsoft.com/en-us/library/office/aa338205%28v=office.12%29.aspx#office2007aboutnewfileformat_structureoftheofficexmlformats [ ^ ]

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

这篇关于[不是问题]使用OpenXML读取excel到XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！