I am having to piece together my code from whatever I can find on the web and am taking this one step at a time. I need to open an existing Excel spreadsheet, unprotect it, write something to some of the cells, re-protect it, then save it back. Taking this one step at a time, here is what I've come up with so far:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1"%><%@ Import Namespace="DocumentFormat.OpenXml"%><%@ Import Namespace="DocumentFormat.OpenXml.Packaging"%><%@ Import Namespace="DocumentFormat.OpenXml.Spreadsheet"%><html><head><title>Test App</title><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/></head><script runat="server">Sub Page_Load()Dim SD As SpreadsheetDocument = SpreadsheetDocument.Open("C:\temp\MyFile.xlsx", True)Dim WBP As WorkbookPart = SD.WorkbookPartDim WSP As WorksheetPart = WBP.WorksheetParts.First() ' Blows up hereResults.Text = "Should be done"End Sub
</script><body><asp:Label id="Results" runat="server"/></body></html>
On the line above indicating "Blows up here", I get the error " 'First' is not a member of 'System.Collections.Generic.IEnumerable(Of DocumentFormat.OpenXml.Packaging.WorksheetPart)'" I'm doing this because of an example I found. Also, at some point I may actually need to open the 2nd worksheet in the document so I can read in some settings. how do I do that? Also, is there a way to get the sheet by it's name? I found some example VB code on the MS web site, but it won't compile.
Thanks,Jesse
Hi,
You can use any of the following methods,
1. WBP.WorksheetParts.ToList().First()
2. WBP.WorksheetParts.GetFirstChild()
These methods were not working for me. per a previous post, I was hand-coding a page (asp.net, VB) without a code-behind page. This works fine for the rest of the application, but for some reason did not work. Once I created a full blown Visual Studios application and brought sample code in, these methods then worked.