Solved

php parse excel color

Posted on 2011-09-22
19
246 Views
Last Modified: 2012-05-12
Is there anyway to parse a CSV by cell background color in php?
0
Comment
Question by:BTMExpert
  • 8
  • 7
  • 2
  • +1
19 Comments
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 200 total points
ID: 36582095
CSV is a text format without any formatting.  There is nothing to parse if it really is a CSV file.
0
 

Author Comment

by:BTMExpert
ID: 36582123
what about making a macro to delete columns with a certain background color
0
 
LVL 3

Accepted Solution

by:
dkellner earned 300 total points
ID: 36582135
There is no way since Excel doesn't store the background color information in CSV file.

BUT.

If you extend your Excel table with an additional column and put the color codes there (as string), you can achieve what you probably need.

http://office.microsoft.com/en-us/excel-help/cell-function-HP010062392.aspx

With this function you can do it.
0
 
LVL 3

Expert Comment

by:dkellner
ID: 36582152
(Wait a sec. Maybe it's not that simple...? I'm checking it)
0
 
LVL 3

Expert Comment

by:dkellner
ID: 36582177
No, CELL can't return such information.
Damn it.

What's the real problem?  What do you want to achieve?
0
 

Author Comment

by:BTMExpert
ID: 36582191
i have a csv that has different background color for different cells.  I only need a certain color values to show up.  I others can be deleted.  So i was trying to think of a way to save time rather than me going through and deleting the ones i don't need and saving it as a csv and then parsing it
0
 
LVL 3

Expert Comment

by:dkellner
ID: 36582199
> "i have a csv that has different background color for different cells"

You mean you have an XLSX that has different bg colors?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 36582221
CSV stands for 'Comma Separated Variables' and is a plain text format.  It does not carry any formatting info so you must be talking about something else like an Excel or OpenOffice spreadsheet.  Those have a different format than CSV.
0
 

Author Comment

by:BTMExpert
ID: 36582232
it's a XLSX that i save as a csv
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Expert Comment

by:dkellner
ID: 36582236
You can sort things by color.
Have you tried that?
Then it's a contiguous area that you can delete easily.
0
 

Author Comment

by:BTMExpert
ID: 36582244
that's why i was thinking of a macro that maybe could delete the correct cells in the XLSX file and then save it as a CSV
0
 
LVL 3

Expert Comment

by:dkellner
ID: 36582272
Okay I see.
Now if you sort the Excel, there are options for the sorting criteria and one of them is "color".  If you order by color, same colored rows go next to each other.  Doesn't that help you?
0
 
LVL 33

Expert Comment

by:Norie
ID: 36582327
Do you know the colours associated with each cell?

Is there any other way to identify them, or even identify roughly where they are?

eg row/column in worksheet
0
 

Author Comment

by:BTMExpert
ID: 36582343
sorting them wouldn't work in this case.  I need to keep them in the same column and row, but thanks for the help anyways
0
 

Author Closing Comment

by:BTMExpert
ID: 36582358
Guess I need a macro
0
 
LVL 33

Expert Comment

by:Norie
ID: 36582389
The code for this should be straightforward if you do know the colours involved.
0
 
LVL 3

Expert Comment

by:dkellner
ID: 36582488
If you want them to stay where they are, make a plus column sequentially numbered from 1 to N so you can sort them back after you made the deletion.  Color sorting only helps your hand.  Sequential numbering will help you sort back in a jiffy and they'll be in the original order - with the colored fields missing.

(I'm not pushing it, just giving an idea.)
0
 

Author Comment

by:BTMExpert
ID: 36582553
lol i appreciate the help.  the parsing by color is a small part of the big project. I have to do more than just that.  Right now i'm just parsing the whole csv and from there taking out the values that need to be taken out.  I was just trying to do that before i get to that step.  
0
 
LVL 3

Expert Comment

by:dkellner
ID: 36582575
:) okay I let it go.
Thanks for the points.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now