473,287 Members | 1,463 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Reading data in excel using c# with merged (spanned?) rows...

3
I have to read data from an excel spreadsheet. the first column has a variable number of rows that are associated with one value (which is in only one of the rows, and it's variable about which one). In excel the rows of column 1 are outlined by a nice border and it's easy to see that this particular value corresponds to the 1..n rows in the following columns. It's basically a 1 to many relationship across the spreadsheet.

how can I read the data and be able to associate the column 1 value to all the cells in the following columns? as I read things now I get empty cells for the column1 cells except for the specific one that the data is in... and there isn't any way (that I've found) to know which ones to apply to the other columns.

???
Jul 30 '07 #1
5 6755
SammyB
807 Expert 512MB
Before you start to read the data, execute the following macro:
Expand|Select|Wrap|Line Numbers
  1. Sub FixIt()
  2.     Cells.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
  3. End Sub
  4.  
This will fill in all of the blanks.

You can execute this with C# using Robbie Morris's example at http://www.eggheadcafe.com/articles/..._in_dotnet.asp
Jul 30 '07 #2
aamax
3
Before you start to read the data, execute the following macro:
Expand|Select|Wrap|Line Numbers
  1. Sub FixIt()
  2.     Cells.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
  3. End Sub
  4.  
This will fill in all of the blanks.

You can execute this with C# using Robbie Morris's example at http://www.eggheadcafe.com/articles/..._in_dotnet.asp

I couldn't find anything usefull on Robbie's site but was able to get some code working with your example. after I ran it I had the blanks filled with #REF!.... I'll dig more into the special cells usage and see what else I can come up with. thanks!
Jul 31 '07 #3
aamax
3
Ok, after reviewing I don't think this is going to help because it still has no idea of what the boundaries of the "cell" are... the column 1 values can span 1 to 5 rows (it varies) and unfortunately column 2 spans rows too.... (2 rows for each entry) ... excel knows how to display it all so it must be doable... but i can't seem to find out how... any other ideas?
Jul 31 '07 #4
SammyB
807 Expert 512MB
Ok, after reviewing I don't think this is going to help because it still has no idea of what the boundaries of the "cell" are... the column 1 values can span 1 to 5 rows (it varies) and unfortunately column 2 spans rows too.... (2 rows for each entry) ... excel knows how to display it all so it must be doable... but i can't seem to find out how... any other ideas?
I'm confused as to the sheet's contents. Can you make up a fake sheet that has the same sort of "spanning" and attach it to your next post? To do that, create your spreadsheet and then zip up the workbook. Next, make a regular post. Immediately after posting, edit your post and press the manage attachments button and attach the zipped spreadsheet. Thanks! --Sam
Aug 1 '07 #5
SammyB
807 Expert 512MB
I may have figured out what your worksheet looks like: the cells that are a single value are merged together, centered & bordered. All that you need to do is unmerge the cells and fill them as in my first post, then each row will have all of the data. The two lines of code in VBA are:
Expand|Select|Wrap|Line Numbers
  1. Cells.MergeCells = False
  2. Cells.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
  3.  
If you need help translating this into C#, I can help, but only in the evening: I don't have XL Interop installed at work. I have attached the workbook that I was using. HTH --Sam
Attached Files
File Type: zip spanned.zip (6.2 KB, 235 views)
Aug 1 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: Julie Miles | last post by:
I need to pull several tables of data from Excel into a web page, but when I use Excel's "Save as web page" function, I get an enormous file containing a massive amount of css formatting. I'd like...
2
by: Klatuu | last post by:
Whew, I've struggled my way through figuring out how to use XML to transport data..now I can imagine what having a baby is like :) But, I'm stuck now. I generate the XML (single table, no...
7
by: jamait | last post by:
Hi all, I m trying to read in a text file into a datatable... Not sure on how to split up the information though, regex or substrings...? sample: Col1 Col2 ...
2
by: Vanessa | last post by:
I need to read/extract data from an Excel file using ASP. However, the Excel file is not in regular tabular format; instead, it is actually a form. Therefore, it contains many checkbox and merged...
1
by: Glen Vermeylen | last post by:
Hi, For a project at school we have to automate the assignment of seats in classrooms to students during the exams. The lady who previously did everything manually kept the layouts of the...
5
by: Scott M. Lyon | last post by:
I've just discovered a bug in some code I wrote a little while ago, and I need you guys' help to fix it. My program imports data from a standard Excel Spreadsheet (just with specific column...
9
by: dba123 | last post by:
I need some help and direction on what classes and an example or two (article) on how to read an Excel Worksheet and insert one column into a database table column. I am using .NET 2.0 only. What...
0
by: yongsheng | last post by:
Hi, i've searched through the discussions but haven't got a clue to solve my problem. Im new to VBA, maybe its a simple task but any help is welcomed. The Problem: Every month, I need to sort...
1
by: ebenl555 | last post by:
i am writing code to import data from an .xls file into a db using an oledbadapter to fill a datatable. I can access most of the values easily using this method. i do however have problems with a...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.