Site Root

Forum Post: Update Cell not working

VorlonShadow — Wed, 23 May 2012 19:40:00 GMT

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

Forum Post: Re: Copying Slide layouts from one presentation to another

twalberti — Wed, 23 May 2012 15:23:00 GMT

I'm having trouble seeing how the sample code above will help copy a slidelayout from

presentation A (template) to presentation B (blank or existing presentation). If you could give me some explanation it would help a lot.

Forum Post: Re: Mark Image in Open Xml

Fanski — Wed, 23 May 2012 13:56:00 GMT

Hi pramodhegde,

thank you. That’s what I was looking for.I tried it out, and it works great. (I split the document into parts (also documents) and only what is left are content controls with id and names of the content documents and images)

Best Regards

Peter Stefanski

Forum Post: Getting the Spreadsheet

VorlonShadow — Wed, 23 May 2012 12:55:00 GMT

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.WorkbookPart
Dim WSP As WorksheetPart = WBP.WorksheetParts.First() ' Blows up here

Results.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

Forum Post: Advice - Windows Service

rocky050371 — Wed, 23 May 2012 12:41:00 GMT

Due to have trouble automating word through a windows service (vb.net), I am contemplating moving over to Open XML. The service loads a word document, loops through merge fields, replaces them with appropriate data, saves the files then prints it.

Is this achievable in Open XML, are there any resources / examples available?

Thanks for any help in advance

Forum Post: Re: How to manipulate this word document?

aroshlakshan — Wed, 23 May 2012 10:24:00 GMT

Only 3 things i gotta know here...

1. How to make it to type right in the next line after OBSERVATIONS

2. How to make the letters Unbold in the list under OBSERVATIONS.

3. How to make the check boxes work.

Forum Post: Using Open XML to update an existing Spreadsheet

VorlonShadow — Tue, 22 May 2012 18:48:00 GMT

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

Forum Post: Re: Section properties when merging documents

davesol — Tue, 22 May 2012 11:21:00 GMT

I thought about using Document Builder instead of altChunks, however I can't as some of my docs that i am using may themselves contain altChunks and Document Builder does not support this so I really am stuck using altChunk. Does anyone know a way to get Word to recognise section properties within the altChunks?

Thanks

Dave

Forum Post: Re: Implementing Barcodes with Apache Tomcat

Luther Clinton — Tue, 22 May 2012 09:15:00 GMT

1. Place the.jar file from what you have used inside the package in the /WEB-INF/lib directory of the WebApp.

2. Install or verify use of Java developmental environment

3. Be sure Java.awt GUI functions are available in the environment.

4. Edit the web.xml in the appropriate /WEB-INF/ directory for the WebApp.

5. Restart the Apache Tomcat server by either restarting the service or executing the appropriate control scripts per system configuration.

6. After the servlet server is started, type the following code into a browser and specify applet parameters to create the barcode.

To be more detailed you can refer to a java barcode tutorial guide with detailed information on barcode generation aspects including how to integrate with tomcat using Java servlet.

Forum Post: MS Excel - vs - ECMA-376 sheetProtection hashing algorithm?

randym — Mon, 21 May 2012 23:34:00 GMT

The ECMA spec uses the following three attributes to generate a password hash for sheet protection:

18.3.1.85 sheetProtection (Sheet Protection Options)

algorithmName

(defaults to SHA-1)

saltValue

Specifies the salt which was prepended to the user-supplied password before it was hashed using the hashing algorithm defined by the preceding attribute values to generate the hashValue attribute, and which shall also be prepended to the user-supplied password before attempting to generate a hash value for comparison.

spinCount

Specifies the number of times the hashing function shall be iteratively run (runs using each iteration's result plus a 4 byte value (0-based, little endian) containing the number of the iteration as the input for the next iteration) when attempting to compare a user- supplied password with the value stored in the hashValue attribute.

hashValue

Those attributes, in addition to the user supplied password are used to generated the hash value used for comparison when disabling sheet protection.

However after a frustrating day of implementing this and watching excel happily tell us that we must have the caps lock key enabled as the password entered is certainly not what is stored in the document we ended up going with Daniel Rentz' fairly old writeup here: http://www.openoffice.org/sc/excelfileformat.pdf.

(Un?)Fortunately that actually seems to work - but it is a FAR cry from what is actually documented

Wondering if anyone has been able to implement the specification, and actually get it to work with a modern (2007 and up) version MS Excel?

Forum Post: Re: Export to XML

ashish.rajbanshi — Mon, 21 May 2012 21:19:00 GMT

Hi Eric,

Thanks for the links Eric. I looked at the screen cast of how to convert Excel sheet to HTML but I have a scenario where there are no tables or table headers in the spreadsheet and instead it is only made of lines, text boxes and other such elements. How can I convert the spreadsheet like that to HTML?

Forum Post: Re: Search and replace rectangle on Excel Sheet

nix — Mon, 21 May 2012 10:51:00 GMT

To be more precise I need do it for all pages for all sheets.

Forum Post: Re: How to insert a Word table into a content control?

Jure.L — Mon, 21 May 2012 07:41:00 GMT

Why don't you create tables in templates and use content controls to mark repeating rows in tables and placeholders this way you can create a more generic data driven document generation solution. You can check this toolkit to get the idea.

And yes, you can nest tables inside content controls.

Forum Post: Re: Inserting comments in a excel spreadsheet

randym — Sun, 20 May 2012 03:08:00 GMT

I've a functional version here which might be enough to get you started.

in addition to specifying the lecgacyDrawing in your worksheet, you will be adding in a comments document that lists the authors and comments:

github.com/.../comments.rb

github.com/.../comment.rb

as well as a bit of vml:

github.com/.../vml_drawing.rb

github.com/.../vml_shape.rb

Hope this helps!

Forum Post: Re: Document merge

Eric White — Fri, 18 May 2012 03:48:00 GMT

Hi hcoco,

I need more information in order to be able to comment on this. What is the problem you are facing?

-Eric

Forum Post: Re: IsolatedStorageException in System.IO.Packaging

Vishalakshi.g — Thu, 17 May 2012 11:46:00 GMT

I have the same error in .Net with c# when try to create a word 2010 document with Table contain large string of data in size 14875967

Forum Post: Re: Read .docx page by page

nix — Thu, 17 May 2012 10:40:00 GMT

Hello Jim,

Thanks for the answer. I can not use header, footer and watermark. I need to put some labels to top and footer for all pages. I do it so

IEnumerable<Paragraph> topP = wdDoc.MainDocumentPart.Document.Body.Elements<Paragraph>();

bool skip = false;

foreach (Paragraph p in topP)

{

if (!ContainLastRenderedPageBreak(p))

{

if (!skip)

{

p.Append(runElementFooter.CloneNode(true));

p.Append(runElementHeader.CloneNode(true));

skip = true;

}

else skip = false;

if (ContainPageBreak(p))

{

p.Append(runElementFooter.CloneNode(true));

p.Append(runElementHeader.CloneNode(true));

skip = false;

}

public bool ContainPageBreak(Paragraph p)

{

return p.Elements<Run>().FirstOrDefault(r => r.Elements<Break>().FirstOrDefault(b => b.Type == BreakValues.Page) != null) != null;

}

public bool ContainLastRenderedPageBreak(Paragraph p)

{

return p.Elements<Run>().FirstOrDefault(r => r.Elements<LastRenderedPageBreak>().FirstOrDefault() != null) != null;

}

And its work for me now, but for some documtns bottom label is bad positioning and I don't know why its happen.

Aleksey

Forum Post: How to insert a table inside a content control.

nishant.sharma.jecrc — Thu, 17 May 2012 10:02:00 GMT

Hi All,

I am trying to append the a table inside a content control.

For this first i created a table and when i am trying to append.

DocumentFormat.OpenXml.Packaging.MainDocumentPart mainPart= file.MainDocumentPart;

SdtBlock cc = mainPart.Document.Body.Descendants().Where(r => r.SdtProperties.GetFirstChild().Val == "TagName").Single();

-- Create table logic

-- Table name is theTable

cc.Append(theTable)

please let me know where i am going wrong.

The word generate agfter saving the file is not opening.

Regards

Nishant Sharma

Forum Post: Supporting all Chart types in Spreadsheet..

pranay — Thu, 17 May 2012 07:31:00 GMT

Hi,

I just stuck on a problem. Let me first explane the problem.

Here it is: I need to create different types of charts on spreadsheet by fetching data from database. I have a spreadsheet which will contain one many sheets e.g.

1. Data Sheet: It will contain the data fetch from data base.

2. DataRange Sheet: It will contain the range of data filled in first sheet(data sheet). e.g. In cell


Data Sheet	A2	C6

3. many sheets of different chart types. : e.g. Bar chart,Bar3DChart,PieChart,Line Chart, Area Chart, Bubble chart etc.

And these sheets will have currosponding chart templates also. But it will not have any set data reference becoz it will be generated dynamicaly.

I need to draw the the charts on the basis of data in first sheet.

The way I did it is just using a switch case with different chart type.

And create a function for each chart type. e.g. CreateBarChart(), CreatePieChart().

But I don't think its a good way of emplimentation.

Is there any generic way to create different types of charts. like factory patterns etc.

Please help me with some nice sample code.

Pranay

Forum Post: Re: problem with boxlot pptx chart

yaccast — Wed, 16 May 2012 15:04:00 GMT

we didn't find the solution for this problem, but the customer wants to generate pdf instead of pptx.

How can I do that with openxml ?