OpenXML - Excel - .NET - Rows and Cells

Hi,

I'm using OpnXML to manipulate an existing Excel file. I'm using Visual 
Studio 2005, and referencing the .NET framework 3, but I cannot use LINQ 
syntax, I have to navigate thru the structures by using loops.

I have 3 questions:

1) Is there a better way to access cells in my case that LINQ is unavailable 
than looping?

2) When iterating thru the rows, and the cells in each row as below:

For Each oRow As Spreadsheet.Row In oRows
   'aCells is defined before, outside the loops.
   aCells = oRow.Descendants(Of Spreadsheet.Cell)()

      For Each oCell As Spreadsheet.Cel in aCells
         <do something>
     Next
Next

I noticed some cells are skipped. For example, row 1 starts, and the 1st 
iteration in the loop that processes all the cells for that row. The 1st cell 
we looped thru showed cell column "A" (in the oCell.CellReference.Value 
member), and the 2nd iteration had column "I". It seems that some columns are 
being skipped although the skipped columns are visible in the spreadsheet. 
Any ideas of why is that happening?

3) When looping thru the rows and columns using for example:

For Each oRow As Spreadsheet.Row In oRows ... Next

do the OpenXML library return the rows ordered by their row number and/or 
the cells ordered by their column name (letters)? They seem to come in order 
by stepping thru the code, but is that guaranteed?

Thank you in advance,

Richard


0
Utf
12/15/2009 9:43:01 PM
excel.programming 6508 articles. 2 followers. Follow

0 Replies
1413 Views

Similar Articles

[PageSpeed] 24

Reply:

Similar Artilces:

XmlNamespaceManager problem with Office OpenXML format
Hi, I'm trying to read a simple Word 2007 document with the contents of "This is a test." Could someone please enlighten me about why the following code doesn't work... Dim strXMLDocument As String = "<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><w:document xmlns:ve=""http://schemas.openxmlformats.org/markup-compatibility/2006"" xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:r=""http://schemas.openxmlformats.org/officeDocume...

Copy cells from another workbook
I have been trying to get this to work for the past few days. What I am trying to do is: -have a button for a macro to open select file dialog box and pick file -then copy data from that file starting at A:9 - I:9 -the sheet calling for this information would put the information in the first blank row in column A The range I am copying from and to are identical A-I. Any help would be greatly appreciated. Gerald -- Message posted via http://www.officekb.com You didn't specify which sheets in the workbook you are using so I chose the first tab in each work...

Remove portion of text from cells
When subtotating, the subtotal row shows as "John Smith Total". How do you remove the word Total from all the subtotal rows? Select that column and do edit|Replace what: _total (_ = space bar) with: (leave blank) replace all glenlee wrote: > > When subtotating, the subtotal row shows as "John Smith Total". How > do you remove the word Total from all the subtotal rows? -- Dave Peterson ...

Is it possible to link a textbox to a cell?
Hi Is it possible to link a textbox to a cell? Cheer -- hc ----------------------------------------------------------------------- hce's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=351 View this thread: http://www.excelforum.com/showthread.php?threadid=26460 Hi you can put a formula in a text box that will return the value in a cell if that's what you mean draw your text box click in the formula bar with the text box selected type =A1 press enter in A1 type Hi this will appear in the text box also. if this isn't what you're after please pos...

Arrays in a cell
I am using a circular function to keep a running total. A B 1 =a1+b1 I would however like to keep the values that I put into a1 so that I can use them later. Any suggestions how I can do this? If you entered them in column A of a different sheet (say, Sheet2), you could use this formula instead of =A1+B1: =SUM(Sheet2!A:A) Otherwise you'll need a VBA solution. Perhaps something like this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) ...

Unable to move cell selection using keyboard
For unknown reason, I can't move cell selection using the left,right,up,down key scroll lock key ?? -- Don Guillett SalesAid Software dguillett1@austin.rr.com "crapit" <biggercrap@yahoo.com> wrote in message news:umFtFtNfGHA.3996@TK2MSFTNGP04.phx.gbl... > For unknown reason, I can't move cell selection using the > left,right,up,down key > ...

colouring cells
I never have a problem with colouring/filling cells, but this time seems to only fill colour within a cell surrounding text - please help!! Please help us to help you, by describing clearly what steps you usually use, what you did differently this time, and exactly how the result is different from what you expected. Also mention what version of Word you have. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. Joey wrote: > I never have a problem wit...

adjusting cell properties
Is there a way to adjust cell properties in the whole table when changing the size? Seems there not the same size, when adjusting the length. (can't do this in word, because it will only go to 22") thanks Gee, Bruce, I thought you had this all figured out along time ago. Even though Word can only do 22 inches, you can adjust your table in Word, copy and paste, then paste again. If there is no text in the Publisher table, the cells will be all the same when you adjust the size. How large is your publication? Publisher can only do 128 rows and columns. -- Mary Sauer MSFT M...

Value of sheet A cell = sheet B cell => fill in sheet name
I have sheet A and three sheets N1, N2, N3 How to have a value "name of sheet" filled in a cell in sheet A when a value in a cel in A and a cell in one of the sheets N1, N2, N3 is the same? Sheet A 10 | | 11 | | X 12 | | Sheet N1 ... ... ... Sheet N3 ... 11 ... X -> N3 I cound't find it out via the Help section. Bart Assume data to be compared in sheets: N1, N2, N3 are in col A. Data is assumed unique in each sheet and across all 3 sheets, ie there are no duplicates Then in sheet: A, with the data in A1 down, Put in B1: =IF(ISNUMBER(MATCH(A1,'N1'!A:A,0)...

Useing IF statements to change a cell color
I know this has been beat to death but I just cant seem to figure it out. This is what I want to do =IF(94<D8<97,[change cell color to red],[do nothing]) I have tried the conditional formatting but the value in D8 is referenced from another page in the same worksheet. How can I get this to work? Thanks. It will be a conditional format. A cf wants either a true or false answer. So for your example this formula should do =and(D8>94, D8<97) which will colour the cell based on your formatting choice when D8 is between 94 and 97... -- HTH... Jim Thomli...

Going crazy wil cell protection
I am running Excell 2002/SP3 on a machine using Vista Home. Every time I try to do something the Global worksheet protection utility tells me I have a protection error in most cells. I have looked at "Tools/Protection" in the pulldown menu and find there is NO WAY to turn protection OFF. How do I tell Excell I don't need cell protection? PS - I tried Office 2007 and I HATE it! -- -charles- ...

Cell Format #14
I would like to be able to enter 150 and have it read and be able to calculate 150,000. I have a sheet set up with the costs of homes that are for sale. I am looking to do some "what if" and would like to not have to enter the last three zeros. Just trying to get good at being lazy :-). Thanks for the help!!! Mike Tools>options>edit and set fixed decimal places to -3 note that this will change other workbooks as well so turn it off when you don't need it, another option would be an event macro that will mulply all values with 1000 -- Regards, Peo Sjoblom (No ...

How do I select a few cells of a column?
How do I select a few cells of a column (and not the whole column)? Avrohom wrote: > How do I select a few cells of a column (and not the whole column)? This can not be done. ON <> Excel et al. Rainald There is a standard Windows copy tactic that will work. To select the items in a column, you can double click each item in the column while holding the CTRL key. When you paste, it will paste as a table column. -- Hope this works, John Guin OneNote Test Team http://blogs.msdn.com/johnguin "Rainald Taesler" wrote: > Avrohom wrote: >...

Missing Text in Cell
When I put text into a cell, sometimes I can't see the bottom line of text unless I use Print Preview mode. Is there any way to see the exact WYSIWYG of a sheet and be able to edit it at the same time? (Page Break Preview mode also doesn't work.) I've never seen a 100% way to make sure that the stuff you see is what gets printed. But if you adjust the rowheight, doesn't it look ok? David Godinger wrote: > > When I put text into a cell, sometimes I can't see the bottom line of > text unless I use Print Preview mode. > > Is there any way to see the exa...

Help using a formula when pointing to a cell #2
NOTE: To clarify what I have described below I have attached screenshot. I am trying to fill a table (18 rows by 50 columns) on a seperate shee (called 'Data') from another sheet (called 'Sheet1') containing column of 900 cells of data. The thing is each of the 900 cells is actually 4 merged rows (so ther are actually 3600 rows). So the cells containing the data are labele as $J7, $J11, $J15, $J19...... etc. The table must be filled sequentially (i.e. from left to right the onto the next line) from the column of 900 cells containing the data. How can I do this ? Is i...

Name Cell, Copy Named Cell, And Add All Named Cells
I'd like to be able to have a cell that is able to be copied and then have another location that adds the original cell and any copies of that cell. In my situation, I have a group of cells that get copied different numbers of times each time I use the document. There is one cell in that group and all the copies that need to be added, but I never know how many times I'll be copy that group. Is there a trick for doing this? Thanks. There may be a trick but I don't understand your request. Can you use some cell references for examples. Gord Dibben MS Excel MV...

Transform range of cells into email addresses
I'have got a column of a sheet in Excel 2000 filled with email addresses but these are not recognized as email, despite they are in the right form (user@domain.xxx), I mean that they are not linked as an URL (mailto:.....) I think that this had occured because the sheet wasn't manually edited but it could be imported from a database. In fact, no sooner as I edit one with F2 and I press Enter, it becomes linked. Since they are about 5000 addresses, how can I manage to transform them automatically? Hy, William William, Assuming your email addresses are in column A, insert a new ...

Splitting Cell Data
I have about 900 cells and all of them appear in the following way A Town, State, 99999 Town1, State1, 11111 I need to have the zip code (99999 in the example) in one column, and then town and state in another column so it would look like this A B Town, State 99999 Town 1, State 1 11111 I've been experimenting with the RIGHT and LEFT formula-- =RIGHT (A1, 5) which would give me the zip code. The problem is i can only do this for individual cells...

Changing items in cells that are filled using Data Validation List
Please let me know if there is any way to use formulas to modify cells that are populated with drop down boxes from the Data Validation menu. One of our users now wants to change all cells in a range to N/A if the First cell in the range contains the value "No". Thanks for all the help!! Ken -- akkrug Hi, You can do that in 2007 but not in 2003. Well in 2003 you probably could write code to do it, but in 2007 you can use conditional formatting. Let me know if you want VBA code or if you are using 2007. -- Cheers, Shane Devenshire "akkrug" wrote: > Plea...

Copying last cell's datal in one column to first cell in next column?
I have several columns of data. For instance: Col B Col C Col D Col E Col F Col G Row 1 250 125 89 Row 2 125 217 144 Row 3 166 144 105 Row 4 122 110 Row 5 155 Row 6 What I need is to be able to copy the last item in Col B (166 in Row 3) into C1 and the last item from Column D (155 in Row 5) into E1 etc. I have three colums I need to ...

cell size #2
How can I make all of the cells the same size? Select the entire worksheet and then Format > Column > Width and then Format > Row > Height -- Gary's Student "JHI" wrote: > How can I make all of the cells the same size? > > > And your definition of IS, is? -- Don Guillett SalesAid Software dguillett1@austin.rr.com "JHI" <jeffreyi@bellsouth.net> wrote in message news:Xj8lh.16197$_X.9986@bigfe9... > How can I make all of the cells the same size? > ...

Need to have cell fill when cell is selected
I would like to have a cell either fill with a color or have an x inserted or something. It doesn't matter what goes into the cell. I need to set up a column with cells that when I click on them they change. I am trying to get out of having to type the word yes in every cell. Whatever goes into the cell represents the word yes. See response in public.excel newsgroup. Don't cross-post, please. HTH Otto "Bob" <rlabonte@verizon.net> wrote in message news:%23owMZrsSFHA.616@TK2MSFTNGP12.phx.gbl... >I would like to have a cell either fill with a color or have ...

Required Cell
In any given row, if the cell in column A of that row has a value entered, I want it required that a user choose something from a dropdown menu in column D. How can I do this in Excel? Tue, 19 Feb 2008 15:53:00 -0800 from Bryan <Bryan@discussions.microsoft.com>: > In any given row, if the cell in column A of that row has a value > entered, I want it required that a user choose something from a > dropdown menu in column D. How can I do this in Excel? You'll have to write a VBA macro, I think for the Worksheet_Change or Worksheet_Open event depending on what you mean by...

VBA to insert .xlborder if cell value not equal to previous cell
I've got a worksheet and I'm wondering whether it is possible to insert a line when a value in Column A, B, C & D does not equal the values in the row above or below it. I've currently got a formula in Column A that reads.... =IF(AND(B3=B2,C3=C2,D3=D2,E3=E2),"","IL") and a conditional format that if the cell value is equal to "IL" then put a border. Wondering if there is a better way to do this via VBA or is that the better way? In your conditional formatting formula, instead of =A1="IL" (which I assume is what you've...

delete drop-down box in cell
I have a problem removing a drop-down list box in a cell and have trie using clear contents, delete cell, the delete button on the keyboard Can someone please tell me how to remove this box from the cell. Thank -- Message posted from http://www.ExcelForum.com Hi might be one created by data / validation ... click on the cell choose data / validation choose Clear All regards JulieD "brain77 >" <<brain77.1apguz@excelforum-nospam.com> wrote in message news:brain77.1apguz@excelforum-nospam.com... > I have a problem removing a drop-down list box in a cell and have tr...