Excel cell formatting special

Question

I have an interesting dilemma, and I was wondering if anyone could find out if this is possible. I am creating a CSV file using java and for one of the comma-separated entries for each line is a url.

Example:

item1, item2, item3, http://myurl.com

Initially, when I loaded the CSV file into Excel, it only loaded the text for the URL and did not make it interactive. I was able to solve this by simply linking the entry to this line when creating the file:

= HYPERLINK (" http://myurl.com ")

When Excel loads, the link is clickable and the mouse pointer changes to a hand icon. The only problem is that when you just look at it and not hang a cell, it looks like black text on a white background. Usually, when you see a link, it is blue with an underline, meaning it is available. I would like to have this visual queue, but I'm not sure if there is a way to do this by simply changing my function above using some kind of cell formatting function or something like that.

And yes, I know what conditional formatting is and how to apply it to a cell in Excel. However, this will not work for me, because I just need it to work when the file is uploaded. A file is always created new when the user uploads it, because the content is always changing. Any ideas would be greatly appreciated.

Answer 1

The reason hyperlinks entered by double-clicking. The CSV files that are "cold" (not clickable), is that the part of the user interface that makes the "hot" hyperlink from the entered text has been circled. If .csv has raw URLs and not = HYPERLINK (), you can make them hot by doing the following:

Sub HyperIgniter() Dim r As Range For Each r In ActiveSheet.UsedRange If Left(r.Text, 4) = "http" Then r.Select Application.SendKeys "{F2}" Application.SendKeys "{ENTER}" DoEvents End If Next r End Sub 

This is the equivalent of reprinting each of the hyperlinks, which makes them “hot” and gives them the usual format.

Answer 2

You can create excel template files using the jXLS library. It is very easy to use. Sample

Answer 3

This can be done absolutely, but it can be harder than you expected.

First, you will need to follow the steps described in this post to create a new custom number format that you can use in all new books, as well as your customers.

Adding cell format options to the user list

I would name this list, but I would prefer not to make any decision regarding the HansV solution.

The parameter you want to add will be "[blue] General"

Your CSV file should now save the line as shown below: "= text (hyperlink (" " http://www.google.com " ")," "[blue] General" ")"

So now you are applying a new format (which controls the font color). Just replace google.com with your url. You will notice that there are many quotes as a product of complex formatting of CSV forces, so that it will add quotes where necessary, and also do not split the desired comma (for the TEXT function) into two cells.