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.
You can use any of the following methods,
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.