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
end if
Next
Dim C As New Cell()
C.CellReference = "B3"
Dim CV As New CellValue()
CV.Text = "123456"
C.CellValue = CV
SD.Close()
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?
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？
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.
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)
Conn.Open()
Cmd = New MySqlCommand("SELECT Description, Lvl, ScoringSheetFileName, MaxTeams, EventType FROM Events WHERE ID=@ID", Conn)
Cmd.Parameters.AddWithValue("@ID", cEventID)
Da = New MySqlDataAdapter(Cmd)
Da.Fill(Ds, "E")
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)
End If
Conn.Close()
End Using
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
File.Delete(OutFile)
End If
File.Copy(Target, OutFile)
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)
End If
' 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"
Else
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"
End If
Da.Fill(Ds, "Ent")
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
Else
Cell.CellValue.Text = Dr2("UID").ToString
End If
End If
cCol = Chr(Asc(cCol) + 1)
End If
nColsUsed += 1
Next
End If
WbP.Workbook.Save()
End If
End Using
GridView1.Visible = False
Results.Text = "<p><b>Scoring Sheet generated</b></p>"
End Sub
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
End If
End If
GetCellValue = cResult
End Function
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
End Function
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.
VorlonShadow
Member
7 Points
73 Posts
Open XML Issue
May 23, 2012 07:39 PM|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
end if
Next
Dim C As New Cell()
C.CellReference = "B3"
Dim CV As New CellValue()
CV.Text = "123456"
C.CellValue = CV
SD.Close()
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?
Thanks,
Jesse
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Open XML Issue
May 25, 2012 01:23 AM|LINK
Hello：）
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/
Thanks！
VorlonShadow
Member
7 Points
73 Posts
Re: Open XML Issue
May 25, 2012 11:41 AM|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.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Open XML Issue
May 26, 2012 01:49 AM|LINK
congratulation！If possible，would you mind showing us your full sample codes and point out the key points？
Reguards！
VorlonShadow
Member
7 Points
73 Posts
Re: Open XML Issue
May 28, 2012 12:14 PM|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) Conn.Open() Cmd = New MySqlCommand("SELECT Description, Lvl, ScoringSheetFileName, MaxTeams, EventType FROM Events WHERE ID=@ID", Conn) Cmd.Parameters.AddWithValue("@ID", cEventID) Da = New MySqlDataAdapter(Cmd) Da.Fill(Ds, "E") 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) End If Conn.Close() End Using 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 File.Delete(OutFile) End If File.Copy(Target, OutFile) 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) End If ' 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" Else 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" End If Da.Fill(Ds, "Ent") 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 pageIf 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 Else Cell.CellValue.Text = Dr2("UID").ToString End If End If cCol = Chr(Asc(cCol) + 1) End If nColsUsed += 1 Next End If WbP.Workbook.Save() End If End Using GridView1.Visible = False Results.Text = "<p><b>Scoring Sheet generated</b></p>" End Sub 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 End If End If GetCellValue = cResult End Function 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 End FunctionLike 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.
Thanks,
Jesse