Show Data in a Tooltip

Question

1.00/5 (3 votes)

See more:

Friends,

I found in Http://www.roseindia.net/javascript/javascriptexamples/javascript-get-excel-file-data.shtml, a routine to access Excel data.

What I need is the same routine with adjusts.

The Excel data should be shown as follows: Hovering the mouse over the button the cell information must appear in tooltip next to the button.

Please, pay attention. I need a tooltip with excel data inside.

Thanks

Posted 19-Jul-12 16:32pm

ZecaPOA

Updated 19-Jul-12 16:54pm

Wes Aday

v2

Add a Solution

3 solutions

Solution 3

Renato, no worries mate. It's been some interesting learning for me..

I've re-written the source to make it neater, clearer and more functional.
I've made a new function that reads in a specified excel file, copying the data in the desired cells into a html table. The cell colours are copied from the spreadsheet.

You may reference the same file multiple times, you may reference several files.
Each call to the function returns a new table.

The code is still fairly generalized - you may want to alter the logic if you are reading from several different sections of the same file - at the moment, each call to the function

starts excel
opens the specified file
reads the data
quits excel

It does seem rather slow - I suspect that it's just the interaction between javascript and the COM object - you could try to read a large section of data from 1 file to gague the effect of (a) regular operations (b) initializing/quiting excel.

Hope it's useful! :)

Code:

HTML

<html>
<head>
<title>Get data from excel sheet(s)</title>
<script language="javascript" >
var excelApp=null, excelFile=null, excelSheet=null;

  function initExcel(filename)
  {
	excelApp = new ActiveXObject("Excel.Application");
	excelFile = excelApp.Workbooks.Open(filename);
	excelSheet = excelApp.Worksheets('Sheet1');
  }

  function myShutdownExcel()
  {
	excelApp.Quit();
	excelApp=null;
	excelFile=null;
	excelSheet=null;
  }
  
  function myGetData(column, row)
  {
	return excelSheet.Cells(column, row).Value;
  }
  
  function myGetCellColor(column, row)
  {
	return excelSheet.Cells(column, row).Interior.Color;
  }
  
  function byId(e) {return document.getElementById(e);}
  
  
// takes a 24 bit number that represents a colour in excel
//	Excel represents colours an int. When displayed in hex, the fmt is BBGGRR 
//	Html represents colours as RRGGBB
function excelToHtmlColor(decNum)
{
	var hexStrVal, r, g, b

	// get a hexidecimal string representation of the number
	hexStrVal = decNum.toString(16);
	
	// pad to 6 bytes long
	while (hexStrVal.length < 6)
	{
		hexStrVal = "0" + hexStrVal; //hexStrVal += "0";
	}
	
	// extract the 3 components
	r = hexStrVal.substr(4,2);
	g = hexStrVal.substr(2,2);
	b = hexStrVal.substr(0,2);
	
	// return them in reverse order
	return r+g+b;
}


// reads an excel file
//	- creates a table using all cells between and including those specified
//  - copies the colour of the excel cell to the html cell's style.backgroundColor attribute
// Note: the order of the args - excel notation is Row,Col (y,x)  - while we use Col,Row (x,y)
function createTableFromExcel(excelFilename, tableId, firstCol,firstRow, lastCol,lastRow)
{
	var table, tbody, tRow, tCell;
	var curRow, curCol;
	
	initExcel(excelFilename);
	table = document.createElement('table');
	table.setAttribute('id', tableId);
	
	tbody = document.createElement('tbody');
	table.appendChild(tbody);
	
	for (curRow=firstRow; curRow<=lastRow; curRow++)
	{
		tRow = document.createElement('tr');
		for (curCol=firstCol; curCol<=lastCol; curCol++)
		{
			// create a td element
			tCell = document.createElement('td');
			
			// grab the color and value of the excel cell
			curVal = myGetData(curRow, curCol);
			if (curVal==undefined)
				curVal = " ";
			curColor = myGetCellColor(curRow, curCol);
			
			// set the style of this cell such that it's background is the same color as the excel cell it corresponds to
			curColorStr = "#" + excelToHtmlColor(curColor);
			tCell.style.backgroundColor = curColorStr;

			// set the text of the cell
			cellText = document.createTextNode(curVal);
			tCell.appendChild(cellText);
		
			tRow.appendChild(tCell);
		}
		tbody.appendChild(tRow);
	}
	myShutdownExcel();
	return table;
}
  
function mOnLoadFunc()
{
	var filename1 = "C:\\Users\\enhzflep\\Documents\\Book2.xlsm";
	var parent = byId('tableHolder');

	var child = createTableFromExcel(filename1, 'table1', 1,1, 7,11);
	parent.appendChild(child);
	parent.appendChild(document.createElement('br'));
	
	child = createTableFromExcel(filename1, 'table2', 1,7, 3,11);
	parent.appendChild(child);
	parent.appendChild(document.createElement('br'));

	child = createTableFromExcel(filename1, 'table3', 4,7, 4,11);
	parent.appendChild(child);
	parent.appendChild(document.createElement('br'));

	child = createTableFromExcel(filename1, 'table4', 5,7, 7,11);
	parent.appendChild(child);
}
</script>
<style>
table
{
	border: solid 1px #555;
}
td
{
	width: 32px;
	border: solid 1px #aaa;
	height: 16px;
}
</style>

</head>
<body onload="mOnLoadFunc();">
<b>Get data from excel sheets</b>
<div id="tableHolder"></div>
</body>
</html>

Result:

	B	C	D	E	F	G
2	b	c	d	e	f	g
3	9	10	11	12	13	14
4	i	j	k	l	m	n
5	16	17	18	19	20	21
6
7	B	C	D	E	F	G
8	13	12	11	10	9	8
9	I	J	K	L	M	N
10	6	5	4	3	2	1
11	P	Q	R	S	T	U

7	B	C
8	13	12
9	I	J
10	6	5
11	P	Q

D

11

K

4

R

E	F	G
10	9	8
L	M	N
3	2	1
S	T	U

Posted 2-Aug-12 8:58am

enhzflep

Comments

ZecaPOA 2-Aug-12 18:05pm

The code you wrote to contemplate the situation where I had told him.

The issue is that sheet excel comes from the financial area and I am the sales area. The sheet data that comes from the financial information does not have colors. Is the sales staff that puts the colors in the cells.

Hence we use HTML to view the developments.

Another question: each project has its own spreadsheet, where are the buildings (1 or more).

What worries me in this new scenario is to have the independence that would provide me HTML, where I could change colors all the time without interfering with the financial worksheet data.

I remembered that early in the design I had sketched a screen where I put HTML excel data manually into the HTML header of each cell. It worked and gave the highest job.

Then I start looking for the integration of HTML table that I had with the tooltip with the data from excel.

Your suggestions were great and either automated the creation of the HTML table and assigns to each cell HTML its due EXCEL table, but is seems to me that the difficulty I have to manipulate the HTML details I have to get back the initial idea.

I wanted to attach the initial model I wrote, but have not found where he could do it. Do not want to publicly display the code as it is immensely primitive compared to his. If you have any way to get my file would indicate.

Accept other suggestions if you have.

Hug

enhzflep 2-Aug-12 23:44pm

Just woken up recently. Still half asleep. Please, by all means feel free to email the hand-made table &/or JS code. Will make things easier..

I have yahoo (dot com dot AU) account - username is same as username here.
Or you can find a regular post (I.e not QA answer) of mine and click the Email button on bottom of it this will give me your email, I can reply to that, you can send attachment back to that addr. :thumbs-up:

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

enhzflep · Accepted Answer · 2012-07-19T20:45:00

Solution 1

So what have you tried, where are you stuck?
Simply put, you need to call the function that's called for the button in order to retrieve the data. You then need to attach the data to the button in the form of a tool-tip.

To set an HTML object's tooltip, you just set it's title.

There's a number of interesting gotchas with the code you've linked to.

1) Will only work with IE (may possibly be achievable using the <object> tag along with the classID of excel, but ActiveX only works with IE.

2) Each time a button is pressed, a new instance of excel is started. You keep idle zombi-threads in the background since the code doesn't call excelApp.Quit()

3) If excel is set to Run As Administrator on the client pc, the code won't run unless the browser has also been run using admin privileges.

I'll also point out, that you're better off using excelApp as a global so you only have to start it once and you also have the opportunity to close it. The browser should close all activeX objects when it's closed, but it doesn't (IE8)

Anyway, here's some code that will create buttons on the fly, labelling them, setting their tool-tip and also setting a rudimentary onclick function. You can modify to suit your needs. Most notably by deciding whether or not to load the data again when the button is pressed, (need to call initExcel first, since I closed it in the init function) or whether to simply use the value that was loaded when the buttons were created.

You may wonder what's with the funny function notation inside the loop - that's called a closure and is there to make sure that each button's onclick function gets the right value for curVal. If you don't do it - all the buttons refer back to the same orignal variable which gets left holding the last value put into it during an execution of the loop.

HTML

<html>
<head>
<title>
Get data from excel sheet
</title>
<script language="javascript" >
var excelApp=null, excelFile=null, excelSheet=null;
var filename = "C:\\Users\\enhzflep\\Documents\\Book2.xlsx";

  function initExcel(filename)
  {
	excelApp = new ActiveXObject("Excel.Application");
	excelFile = excelApp.Workbooks.Open(filename);
	excelSheet = excelApp.Worksheets('Sheet1');
  }

  function myShutdownExcel()
  {
	excelApp.Quit();
	excelApp=null;
	excelFile=null;
	excelSheet=null;
  }
  
  function myGetData(column, row)
  {
	return excelSheet.Cells(column, row).Value;
  }
  
  function byId(e) {return document.getElementById(e);}
  
  function createButton(id, value, tooltip)
  {
	var btn = document.createElement('input');
	btn.setAttribute('type', 'button');
	btn.setAttribute('value', value);
	btn.setAttribute('id', id);
	btn.setAttribute('title', tooltip+"");
	btn.setAttribute('click', onclickfn);
	return btn;
  }
  function myOnLoad()
  {
	var btn, val, holder;
	var col=1, row=1;

	byId('filenameTgt').innerHTML = filename;
	
	holder = byId('controlHolder');
	initExcel(filename);

	for (row=1; row<=4; row++)
	{
		for (col=1; col<=4; col++)
		{
			(
				function()
				{
					var curVal;
					val = myGetData(row,col);
					
					curVal = val;
					btn = createButton('btn1', 
									   'Col('+col+'),Row('+row+')', 
									   'The value of cell['+col+','+row+']\nis: '+val);
					btn.onclick = function()
					{
						alert(curVal);
					}
					holder.appendChild(btn);
				}
			)();
		}
		holder.appendChild(document.createElement('br'));
	}
	myShutdownExcel();
}
  </script>
</head>
<body onload="myOnLoad();">
<p> </p>
<div style="background: #009955; width:'100%';" align="center">
  <font color="#000080" size="12pt">
<b>Get data from excel sheets</b>
  </font>
</div>
<center>
<p>&nbsp;</p>
<div id="div1" style="background: #DFDFFF; width:'100%';" align="center">
Click buttons to fetch data from <div id="filenameTgt"></div>
</div>
	<div id="controlHolder">
	</div>
</center>
</body>
</html>

Posted 19-Jul-12 20:45pm

enhzflep

Updated 19-Jul-12 20:47pm

v2

Comments

Sandeep Mewara 20-Jul-12 18:03pm

My 5!

ZecaPOA 22-Jul-12 17:16pm

friend,

I want to thank the time you lost trying to help me. I expected a simple code, I could understand. Unfortunately I do not understand your code and I'm giving up on finding a tooltip with data in Excel. I will start to see if I can find a pure Excel solution. I would not want to use it in excel, but as I find another way ... it will be anyway.

thank you

enhzflep 22-Jul-12 19:03pm

It's a pleasure my friend. :)
I loose no time trying to help - I learn more while I trying to answer your question. Thanks & good luck. :)

ZecaPOA 31-Jul-12 11:08am

Dear friend,
I returned to the initial project. I try to understand the code and test it. Here the buttons were not created. Apparently the code creates them, but do not come to me. Am I doing something wrong?
I tried to create them using the routine Roseindia and neither worked.

enhzflep 31-Jul-12 11:22am

G'day Mate.
That sounds interesting - that the buttons were not created. Perhaps there is an error in your code, perhaps there is some other factor that prevents success coming to you.

Just so I am clear and understand you - did you try the code I pasted, with an appropriate value for filename?
Also, which version of Internet Explorer are you using?

ZecaPOA 31-Jul-12 12:27pm

Hello friend.
You are very efficient and fast. I was studying your code to understand and could capture your reasoning. Everything is correct, but I could not make it work even adapting some details. I made the changes in location, file name and specification sheet (this is Plan1). Everything is correct. I am using the updated Internet Explorer 9 to test. Any suggestions?
Many thanks for your commitment.

enhzflep 31-Jul-12 12:38pm

:DIf I understand correctly, you have a worksheet named "Plan1" in your workbook. This is okay as long as you change "Sheet1" in initExcel function.

Okay then, I would try either/both of the following

1) Run the code using IE's debugger (f12, or Tools->Developer Tools)
2) Try the following code and tell me if it opens Excel

<!DOCTYPE html>
<html>
<head>
<title>Control Excel from browser</title>
<script language="javascript" >
var excelApp=0, excelFile=0, excelSheet=0;

function startExcel()
{
excelApp = new ActiveXObject("Excel.Application");
excelApp.visible = true;
}

function closeExcel()
{
excelApp.Quit();
excelApp=nothing;
}

function byId(e) {return document.getElementById(e);}
</script>
</head>

<body>
<h1>Control Excel from IE Browser</h1>
<div id="controlHolder">
<button id="startBtn" önclick='startExcel();'>Start Excel</button>
<button id="closeBtn" önclick='closeExcel();'>Close Excel</button>
</div>
</body>
</html>

You should be able to start/quit excel using these 2 buttons

EDIT: Make SURE you change önclick to onclick (notice the funny ö)

ZecaPOA 31-Jul-12 12:50pm

It's working perfectly. Opens and closes Excel. A small correction in the code. önclick = onclick.

enhzflep 31-Jul-12 13:00pm

Okay, great :)

(Codeproject changes onclick to önclick in pasted code that isn't wrapped in <pre> blocks)

Now I suggest that you load the page that doesn't work.

You should use the debugger. Set a breakpoint on the first line of code inside the myOnLoad function. This line - byId('filenameTgt').innerHTML = filename;

You can step through the code line by line to see where the error is.

You need to select the 'Script' tab. Then you must hit the 'Start Debugging' button. To set a breakpoint, click on or slightly to the left of the line-number (for me it's line 47)
Then you just have to step line by line (f10 or f11 - f11 takes longer but will hit every single line)

This should tell you where abouts the code is failing.

ZecaPOA 31-Jul-12 13:19pm

Errror 5009: 'onclickfn' not defined

enhzflep 31-Jul-12 13:24pm

Okay. The code I've pasted works just fine in IE8 (win7)

Can you post your code for myOnLoad() function?

ZecaPOA 31-Jul-12 13:50pm

I put all the code as it is right now.

<html>
<head>
<title>
Captura dados do Excel
</title>
<script language="javascript" >
var excelApp=null, excelFile=null, excelSheet=null;
var filename = "C:\\Users\\Pai\\Desktop\\Espelho Teste\\Score.xlsx";

function initExcel(filename)
{
excelApp = new ActiveXObject("Excel.Application");
excelFile = excelApp.Workbooks.Open(filename);
excelSheet = excelApp.Worksheets('plan1');
}

function myShutdownExcel()
{
excelApp.Quit();
excelApp=null;
excelFile=null;
excelSheet=null;
}

function myGetData(column, row)
{
return excelSheet.Cells(column, row).Value;
}

function byId(e) {return document.getElementById(e);}

function createButton(id, value, tooltip)
{
var btn = document.createElement('input');
btn.setAttribute('type', 'button');
btn.setAttribute('value', value);
btn.setAttribute('id', id);
btn.setAttribute('title', tooltip+"");
btn.setAttribute('click', onclickfn);
return btn;
}
function myOnLoad()
{
var btn, val, holder;
var col=1, row=1;

byId('filenameTgt').innerHTML = filename;

holder = byId('controlHolder');
initExcel(filename);

for (row=1; row<=4; row++)
{
for (col=1; col<=4; col++)
{
(
function()
{
var curVal;
val = myGetData(row,col);

curVal = val;
btn = createButton('btn1',
'Col('+col+'),Row('+row+')',
'O valor da célula['+col+','+row+']\nis: '+val);
btn. önclick = function()
{
alert(curVal);
}
holder.appendChild(btn);
}
)();
}
holder.appendChild(document.createElement('br'));
}
myShutdownExcel();
}
</script>
</head>



<body önLoad="myOnLoad();">
<p>
</p>
<div style="background: #009955; width:'100%';" align="center">

Captura dados do Excel

</div>

<p> </p>
<div id="div1" style="background: #DFDFFF; width:'100%';" align="center">
Clique nos botões para pegar os dados de <div id="filenameTgt"></div>

</div>
<div id="controlHolder">
</div>

</body>
</html>

enhzflep 31-Jul-12 13:40pm

I just remembered something else!!

You should change this function:

function createButton(id, value, tooltip, onclickfn)
{
var btn = document.createElement('input');
btn.setAttribute('type', 'button');
btn.setAttribute('value', value);
btn.setAttribute('id', id);
btn.setAttribute('title', tooltip+"");
btn.setAttribute('click', onclickfn);
return btn;
}
to this:

function createButton(id, value, tooltip, onclickfn)
{
var btn = document.createElement('input');
btn.setAttribute('type', 'button');
btn.setAttribute('value', value);
btn.setAttribute('id', id);
btn.setAttribute('title', tooltip+"");
return btn;
}

ZecaPOA 31-Jul-12 14:01pm

Dude, you're the greatest. It worked. Finally. I do not know how to thank.

ZecaPOA 31-Jul-12 14:08pm

For you understand the greatness of what you did ... I posted this question in about 50 forums over the world and NO ONE answered! Congratulations and many, many thanks indeed!.

enhzflep 31-Jul-12 14:14pm

It is truly my pleasure!
I am so glad that the code now works for you. I am so sorry that my mistake cause you so much grief..

You should also remove the onclickfn var from function header - i.e
Change:
function createButton(id, value, tooltip, onclickfn)
To
function createButton(id, value, tooltip)

Also, change the part where the function is called from

btn = createButton('btn1',
'Col('+col+'),Row('+row+')',
'The value of cell['+col+','+row+']\nis: '+val,
null);
To:

btn = createButton('btn1',
'Col('+col+'),Row('+row+')',
'The value of cell['+col+','+row+']\nis: '+val);

It is totally my fault and mistake. I am so sorry!

ZecaPOA 31-Jul-12 17:18pm

Friend, if you allow me I would post this solution you created in other forums where I put the question. I want to put your credit in the post. Do not know how you mention.

A second question, and please only answer if you find that is not abuse.

How can I associate these buttons created by Excel to an item on a table inside the html. Actually I want to show the data excel when you mouse over the HTML table.

I am writing a code here, but the excel button keeps appearing and I would just html table were visible with the tooltip excel.

Thanks

enhzflep 31-Jul-12 17:27pm

Mate, yes of course - by all means post my answer anywhere you see fit. You don't need to attribute it to me, but if you do - just attribute to enhzflep. That's the username I use internet-wide.

Well, if you would like to put values to a table cell, you just need to create td elements instead of button elements. If you give me 10 - 15 mins I will present an alternative solution. I hope I understand question correctly and give you an answer that you like.

Be back in 10-15 mins. :)

Simon.

ZecaPOA 31-Jul-12 17:36pm

Simon,
Thanks for everything. You have every time in the world. Actually you solve 99% of an application that was stalled for months.

After resolving the issue of HTML table, I can return the application and complete the missing part and that is much simpler because it is just some html and javascript.

Renato from Brazil (Rio Grande do Sul)

enhzflep · Accepted Answer · 2012-07-31T11:51:00

Here you go - I hope it's what you're interested in. If not, let me know and I'll do the best I can. :)

HTML

<html>
<head>
<title>Get data from excel sheet</title>
<script language="javascript" >
var excelApp=null, excelFile=null, excelSheet=null;
var filename = "C:\\Users\\enhzflep\\Documents\\Book2.xlsx";

  function initExcel(filename)
  {
	excelApp = new ActiveXObject("Excel.Application");
	excelFile = excelApp.Workbooks.Open(filename);
	excelSheet = excelApp.Worksheets('Sheet1');
  }

  function myShutdownExcel()
  {
	excelApp.Quit();
	excelApp=null;
	excelFile=null;
	excelSheet=null;
  }
  
  function myGetData(column, row)
  {
	return excelSheet.Cells(column, row).Value;
  }
  
  function byId(e) {return document.getElementById(e);}
  
function myOnLoad2()
{
	var numRows = 5, numCols = 7;
	var tBody = byId('dataTableBody');
	var rowIndex, colIndex, curVal;
	var curRow, curCell, curCellText;
	initExcel(filename);
	
	for (rowIndex=1; rowIndex<=numRows; rowIndex++)
	{
		curRow = document.createElement('tr');
		for (colIndex=1; colIndex<=numCols; colIndex++)
		{
			curVal = myGetData(rowIndex, colIndex);
			curCell = document.createElement('td');
			curCell.setAttribute('title', 'The value of cell [' + rowIndex + ',' + colIndex +']\nis: ' + curVal);
			curCellText = document.createTextNode(curVal);
			curCell.appendChild(curCellText);
			curRow.appendChild(curCell);
		}
		tBody.appendChild(curRow);
	}
	myShutdownExcel();
}
</script>
<style>
table
{
	border: solid 1px #555;
}
td
{
	width: 32px;
	border: solid 1px #aaa;
}
</style>

</head>
<body onload="myOnLoad2();">
<b>Get data from excel sheets</b>
<table id='dataTable'>
 <tbody id='dataTableBody'>
 </tbody>
</table>
</body>
</html>

Output:
Actual output has borders around each cell, also has wider cells.
The tool-tips still work here though - try mousing over the table.

1	2	3	4	5	6	7
a	b	c	d	e	f	g
8	9	10	11	12	13	14
h	i	j	k	l	m	n
15	16	17	18	19	20	21

	B	C	D	E	F	G
2	b	c	d	e	f	g
3	9	10	11	12	13	14
4	i	j	k	l	m	n
5	16	17	18	19	20	21
6
7	B	C	D	E	F	G
8	13	12	11	10	9	8
9	I	J	K	L	M	N
10	6	5	4	3	2	1
11	P	Q	R	S	T	U

	B	C	D	E	F	G
2	b	c	d	e	f	g
3	9	10	11	12	13	14
4	i	j	k	l	m	n
5	16	17	18	19	20	21
6
7	B	C	D	E	F	G
8	13	12	11	10	9	8
9	I	J	K	L	M	N
10	6	5	4	3	2	1
11	P	Q	R	S	T	U

Show Data in a Tooltip

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us

	B	C	D	E	F	G
2	b	c	d	e	f	g
3	9	10	11	12	13	14
4	i	j	k	l	m	n
5	16	17	18	19	20	21
6
7	B	C	D	E	F	G
8	13	12	11	10	9	8
9	I	J	K	L	M	N
10	6	5	4	3	2	1
11	P	Q	R	S	T	U