Last post May 28, 2012 08:14 AM by VorlonShadow
May 23, 2012 03:39 PM|VorlonShadow|LINK
After 2 days of trying to learn Open XML, I have the following small piece of code so far:
Dim SD As SpreadsheetDocument = SpreadsheetDocument.Open(DocName, True)
Dim sheets As Sheets = SD.WorkbookPart.Workbook.Sheets
Dim SESheet As Sheet
For Each sh As Sheet In sheets
if sh.Name = "Score Entry" then
SESheet = sh
Dim C As New Cell()
C.CellReference = "B3"
Dim CV As New CellValue()
CV.Text = "123456"
C.CellValue = CV
This code runs without any errors, but when I look at the spreadsheet, the cell has not been changed. I'm sure I've done something wrong or forgotten something. Could someone people me in the right direction?
May 24, 2012 09:23 PM|decker dong - msft|LINK
Not use the Open xml——a 3-rd party thing yet and considering it that Microsoft cannot do much comment to other products……But as far as I see，whether you've forgotten something like Save method to save the modified value？
For more about Open xml，you can pay a visit at：http://openxmldeveloper.org/f/
May 25, 2012 07:41 AM|VorlonShadow|LINK
I spent alot of my time over the last few days on the OpenXMLDeveloper.org web site, and learned quite a bit. I leared yesterday morning some time that one of my problems was I was trying to hand-code the page (without code behind and hand-coded web.config's,
etc). I have a whole site that I hand coded in asp.net and did not use Visual Studios. Just for kicks to see if all the sample code I had waded through actually worked, I started a Vistual Studios project copied the code, and amazingly it worked. I don't
know what the difference was between what I had hand written and what I ended up with, but it now works. I learned shortly after writing this post that the .Save was necessary, but a lot more than that was as well.
May 25, 2012 09:49 PM|decker dong - msft|LINK
what I ended up with, but it now works.
congratulation！If possible，would you mind showing us your full sample codes and point out the key points？
May 28, 2012 08:14 AM|VorlonShadow|LINK
Sure. It's still a work in progress and I've started to adapt it to my special needs, but here's what I've done so far:
Protected Sub CreateRubric(ByVal cEventID As String)
Dim WbP As WorkbookPart
Dim Sht, ShtSettings As Sheet
Dim WsP, WsPSettings As WorksheetPart
Dim Shd, ShdSettings As SheetData
Dim Row As Row
Dim Cell As Cell
Dim nPage, nRow, nNumCells, nMaxTeams, nColsUsed As Integer
Dim Target, OutFile, cStartingCell, cPageNoCell, cCol, cRubricFileName As String
Dim Cmd As MySqlCommand, Dr As Data.DataRow
Dim Da As MySqlDataAdapter, Ds As New Data.DataSet
Dim cEventType As String
' First, get the Rubric file name
cRubricFileName = ""
Results.Text = ""
nMaxTeams = 1
cEventType = ""
Using Conn As MySqlConnection = New MySqlConnection(ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString)
Cmd = New MySqlCommand("SELECT Description, Lvl, ScoringSheetFileName, MaxTeams, EventType FROM Events WHERE ID=@ID", Conn)
Da = New MySqlDataAdapter(Cmd)
If Ds.Tables("E").Rows.Count > 0 Then
Dr = Ds.Tables("E").Rows(0)
cRubricFileName = Dr("ScoringSheetFileName").ToString
Results.Text = "<h3>" & Dr("Description").ToString & " (Lvl " & Dr("Lvl").ToString & ")</h3>"
cEventType = Dr("EventType").ToString
nMaxTeams = CInt(Dr("MaxTeams").ToString)
Target = Server.MapPath("Default.aspx")
Target = Replace(LCase(Target), "default.aspx", "sheet\" & cRubricFileName)
OutFile = Replace(Target, "sheet\" & cRubricFileName, "Out\" & cRubricFileName)
' Copy the Target file to an OutFile
If File.Exists(OutFile) Then
Using SSD As SpreadsheetDocument = SpreadsheetDocument.Open(OutFile, True)
WbP = SSD.WorkbookPart
Sht = WbP.Workbook.Descendants(Of Sheet)().Where(Function(S) S.Name = "Score Entry").FirstOrDefault()
WsP = WbP.GetPartById(Sht.Id)
ShtSettings = WbP.Workbook.Descendants(Of Sheet)().Where(Function(S) S.Name = "Settings").FirstOrDefault()
WsPSettings = WbP.GetPartById(ShtSettings.Id)
cStartingCell = ""
cPageNoCell = ""
nPage = 1
' First, get the settings.
If Not WsPSettings Is Nothing Then
ShdSettings = WsPSettings.Worksheet.Descendants(Of SheetData)().FirstOrDefault()
cStartingCell = GetSharedStringValue(ShdSettings, 2, "A2", WbP)
nNumCells = CInt(GetCellValue(ShdSettings, 3, "A3"))
cPageNoCell = GetSharedStringValue(ShdSettings, 5, "A5", WbP)
' Now, update the cells.
If Not WsP Is Nothing Then
Shd = WsP.Worksheet.Descendants(Of SheetData)().FirstOrDefault()
nRow = Mid(cStartingCell, 2, Len(cStartingCell))
Row = Shd.Descendants(Of Row)().Where(Function(R) R.RowIndex.Value = nRow).FirstOrDefault()
If Not Row Is Nothing Then
cCol = Left(cStartingCell, 1)
' Get the list of participants and update the cells.
If cEventType = "Team" Then
Cmd.CommandText = _
"SELECT CONCAT('T',RIGHT(CONCAT('0000',S.ID),4)) AS TID, S.ID,S.Name, TES.ID As TESID, S.State, Sum(TES.NumTeams) As NumTeams, " & _
"CASE WHEN RegFinishedDate IS NULL AND TES.InvNo IS NOT NULL THEN '<b>No</b>' ELSE 'Yes' END As IsSubmitted " & _
"FROM TeamEventSel TES JOIN Schools S On S.ID=TES.ChapterID " & _
" LEFT JOIN InvHead I On I.InvNo=TES.InvNo " & _
"WHERE TES.EventID=" & cEventID & " " & _
"GROUP BY S.ID,S.Name, S.State " & _
"ORDER BY State, Name"
Cmd.CommandText = _
"SELECT P.ID, P.UID,CONCAT(P.LastName,', ',P.FirstName) As Name, S.Name As SchoolName, S.State, " & _
"CASE WHEN RegFinishedDate IS NULL THEN '<b>No</b>' ELSE 'Yes' END As IsSubmitted, E.ID As EID " & _
"FROM Entries E JOIN Participants P On P.ID=E.ParticipantID " & _
" JOIN Schools S ON S.ID=P.ChapterID " & _
" JOIN InvHead I On I.InvNo=P.InvNo " & _
"WHERE E.ContestID=" & cEventID & " " & _
"ORDER BY S.State, S.Name, P.LastName, P.FirstName"
nColsUsed = 0
For Each Dr2 As Data.DataRow In Ds.Tables("Ent").Rows
' FOR NOW: I'm just discontinuing once I've finished this page, I actually need to copy in another
' sheet and continue with a 2nd page
If nColsUsed < nNumCells Then
Cell = Row.Descendants(Of Cell)().Where(Function(C) C.CellReference = cCol & CStr(nRow)).FirstOrDefault()
If Not Cell Is Nothing Then
Cell.CellValue = New CellValue
Cell.DataType = CellValues.[String]
If cEventType = "Team" Then
Cell.CellValue.Text = Dr2("TID").ToString
Cell.CellValue.Text = Dr2("UID").ToString
cCol = Chr(Asc(cCol) + 1)
nColsUsed += 1
GridView1.Visible = False
Results.Text = "<p><b>Scoring Sheet generated</b></p>"
Protected Function GetCellValue(ByVal Sheet As SheetData, ByVal nRow As Integer, ByVal cCell As String) As String
Dim cResult As String, Row As Row, Cell As Cell
cResult = ""
Row = Sheet.Descendants(Of Row)().Where(Function(R) R.RowIndex.Value = nRow).FirstOrDefault()
If Not Row Is Nothing Then
Cell = Row.Descendants(Of Cell)().Where(Function(C) C.CellReference = cCell).FirstOrDefault()
If Not Cell.CellValue Is Nothing Then
cResult = Cell.InnerText
GetCellValue = cResult
Protected Function GetSharedStringValue(ByVal Sheet As SheetData, ByVal nRow As Integer, ByVal cCell As String, ByVal Workbook As WorkbookPart) As String
Dim cResult As String = ""
Dim nSSIndex As Integer
nSSIndex = CStr(GetCellValue(Sheet, nRow, cCell))
cResult = Workbook.SharedStringTablePart.SharedStringTable.ChildElements.ElementAt(nSSIndex).InnerText
GetSharedStringValue = cResult
Like I said, it's a work in progress, in fact, I'm going to have to re-write the whole routine to handle multiple pages, and there are some oddities in the code because of some things that I haven't completely finished yet. But what is there works.