Last post May 28, 2012 11:15 PM by EricWhiteDev
May 23, 2012 08:53 AM|VorlonShadow|LINK
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"%>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
Dim SD As SpreadsheetDocument = SpreadsheetDocument.Open("C:\temp\MyFile.xlsx", True)
Dim WBP As WorkbookPart = SD.WorkbookPart
Dim WSP As WorksheetPart = WBP.WorksheetParts.First() ' Blows up here
Results.Text = "Should be done"
<asp:Label id="Results" runat="server"/>
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.
May 26, 2012 10:03 PM|EricWhiteDev|LINK
May 28, 2012 08:17 AM|VorlonShadow|LINK
Ok, that's why it works for me now. I actually abandoned the hand-coded version I was working on and started a new VS08 project and it suddently started working. It's using .net 3.5. I am not including the Linq namespace, though, but it seems to be working
May 28, 2012 11:15 PM|EricWhiteDev|LINK
VB.NET from VS08 and onwards automatically imports the LINQ namespace. Also, because of the XML literals feature of VB.NET, it automatically imports the LINQ to XML namespace. Those features in VB.NET are more tightly integrated than with C#.