I have some existing Excel files that I have created. I have updated these files to be .xlsx files. The worksheets are protected. There are certain areas that I want the user to be able to enter numeric values and other cells I don't want them to touch. I need to copy these files out to a new copy, unprotect the worksheets, update certain cells, then re-protect the worksheets. I have spent hours researching how to do this, and this web site seems to be the best resource I've found for figuring this out. Since this application will be deployed on a server that does not have Excel installed, my understanding is it's better to use OpenXML to do what I need to do. So, as a test to make sure I can do this, I simply want to open up a file that I have on my machine with a physical path, unprotect it, update a cell, then re-protect it and save it. Once I know how to do this, I can probably figure out the rest.
I have downloaded and installed the OpenXML 2.0 SDK on my Windows 7 Development machine. I have the following code (only partial is listed):
<!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 spreadsheetDocument As DocumentFormat.OpenXml.Packaging.SpreadsheetDocument = spreadsheetDocument.Create("C:\temp\MyFile.xlsx", SpreadsheetDocumentType.Workbook) ...end sub
When it gets to the line that starts "Dim spreadsheetDocument As Document..." I get the error "Type 'DocumentFormat.OpenXml.Packaging.SpreadsheetDocument' is not defined.". I think someone had a similar problem and someone suggested that it's having trouble accessing the .DLL, but my question is "what DLL", and how can I show it where it should be? I thought there needed to be a web.config file change, but I can't figure out what the change should be, and the ones I've tried have all failed.
This is an asp.net (VB) application. This is all hand coded. I'm not using Visual Studios for this application. I'm developing on a Windows 7 machine and it will be deployed to a Windows Server 2003 web server. Any help is greatly appreciated.
Thanks,Jesse
Hi Jesse,
Yes, you need to reference the Open XML SDK assembly. The following screen-cast (while not directly about using the Open XML SDK from VB) shows how to do this:
openxmldeveloper.org/.../query-open-xml-spreadsheets-in-vb-net-using-linq.aspx
I have two other resources that will probably help you out.
First, a screen-cast on How to Create Protected Spreadsheets with Locked and Unlocked Cells:
openxmldeveloper.org/.../139098.aspx
Next a screen-cast on building an ASP.NET application with Open XML functionality:
openxmldeveloper.org/.../building-an-asp-net-web-application-with-open-xml-functionality.aspx
-Eric
Eric White www.ericwhite.com Follow on Twitter
I was kind of confused about the protected spreadsheets. The one I'm working with is protected, yet I was able to open it, write to it and save it out all without un-protecting it. I thought that I was going to have to do something special, but to my surprise, it opened up the spreadsheet, updated a series of protected cells and saved it back all without me having to un-protect it first. I didn't understand why and this seems to be a security hole to me. But for now that's ok, because it's not that big of a deal with my project. You'd have to have enough technical expertise to use Open XML to write a routine to update spreadsheet cells, which over the past few weeks I've found you need a considerable amount of expertise to do that.