Embedded Procurement

Thu, 08 Oct 2009 05:02:00 GMT

Struggles and lessons with securing hardware

Coldusion MX7 read excel for free (Apache POI)

Sat, 30 Dec 2006 04:14:34 GMT

Recently I had the need to utilize existing spreadsheets uploaded to our Capital Appropriation applicaiton. The problems you can encounter at the enterprise level is getting approval to buy any type of software to do this, then you have to put in the requests to get everything installed and then you must fill out security documents to get the admins the access to install the software, blah, blah, blah, red tape.

I did some intial research and found all types of examples where Coldfusion uses Apache POI to create excel documents but i did not acutally find where it would read documents. I found it was a little tricky to read excel documents but I got it working in a couple of days and thought it would be good to share.

The code example I am listing is not a perfect example of how to read an excel spreadsheet but it goes a long way to get all the formatting to actually give a way to do a html conversion of an excel document.

I hope this gives some other coldfusion developers a way to start reading existing excel spreadsheets in your business environment.

Some of the ideas I had for this are:

A new querysim (Hal Helms) that uses excel instead of a text file
Security document (fusebox fuseactions) a column of fuseactions and columns for each group level of access. The matrix could have yes, no or 1,0 to show this group has or does not have access to a specific fuseaction. This would be loaded into application structure and called as a security prefuseaction.

readExcel.cfm

<br /> Reading an Excel with MX7 & Apache POI for free 


#supportNavigation { position: relative; z-index: 100; padding-left: 14px; padding-top:2px; float: left; height: 50px; clear: left; margin: 0px; } <div id="header">

<div id="appName">
Reading an Excel or Word Document with MX7 & Apache POI </div>

<div id="greeting">
Welcome </div>
<div id="divider">
</div>
</div>

#excel{ border: 1px solid #808080; background-color: #DDDDDD; text-align: center; } 




<div id="container">
<div id="content">
<table border="0" style="border-collapse: collapse">
<tbody>
<tr>
<td id="excel">
</td>
<td id="excel">
#ucase(GetAlphabetPosition(myPrePos))##ucase(GetAlphabetPosition(myColPos))# </td>
</tr>
<tr>
<td id="excel">
#myTmpRow#</td>

<td>
colspan=#myColSpan["#CC#,#DD#"]# style="padding:3px;#fnFont(myFont,myUnderline,myItalic,myStrikeout,myFontHeight,myBoldWeight)# #fnHAlignment(myHalignment)# #fnVAlignment(myValignment)# #fnFGColor(myFGColor)# #fnBorder(myLBorder,myTBorder,myRBorder,myBBorder)#"> 


#dateformat(wb.getSheetAt(0).getRow(javacast("int", CC)).getCell(javacast("int", DD)).getDateCellValue(),"mm/dd/yyyy")# #wb.getSheetAt(0).getRow(javacast("int", CC)).getCell(javacast("int", DD)).getNumericCellValue()# 
#wb.getSheetAt(0).getRow(javacast("int", CC)).getCell(javacast("int", DD)).getStringCellValue()# 
#trim(numberformat(wb.getSheetAt(0).getRow(javacast("int", CC)).getCell(javacast("int", DD)).getNumericCellValue(),"999,999,999.99"))# <a style="text-decoration: none" xhref="javascript:void(0);" mce_href="javascript:void(0);" onmouseover="return overlib('#JSStringFormat(wb.getSheetAt(0).getRow(javacast(" onmouseout="nd();">
*</a>


#wb.getSheetAt(0).getRow(javacast("int", CC)).getCell(javacast("int", DD)).getBooleanCellValue()# 
#wb.getSheetAt(0).getRow(javacast("int", CC)).getCell(javacast("int", DD)).getErrorCellValue()# Cell type is numeric value of #wb.getSheetAt(0).getRow(javacast("int",CC)).getCell(javacast("int",DD)).getCellType()# > 

</td>
</tr>
</tbody>
</table>
</div>

plugExcel.cfm

/** * Returns the numeric value of a letter's position in the alphabet, or the returns matching letter of a number in the alphabet. * * @param charornum Either a character or number. * @return Returns either a character, number, or empty string on error. * @author Seth Duffey (sduffey@ci.davis.ca.us) * @version 1, January 7, 2002 */ function GetAlphabetPosition(charornum) { var a_numeric = asc("a"); charornum = lCase(trim(charornum)); if (charornum eq ""){ return ""; } if(isNumeric(charornum)) { if(charornum lte 0 OR charornum gte 27) return ""; return chr(charornum+a_numeric-1); } else { if(len(charornum) gt 1) return ""; if(REFind("[^a-z]",charornum)) return ""; return asc(charornum) - a_numeric + 1; } return 1; } function fnHAlignment(this_cell){ switch(this_cell){ case "1": return "text-align:left;padding-left:.5em;"; break; case "2": return "text-align:center;"; break; case "3": return "text-align:right;padding-right:.5em;"; break; } return ""; } function fnFont(myFont,myUnderline,myItalic,myStrikeout,myFontHeight,myBoldWeight){ var myReturnFont="font-size:"&myFontHeight&"px;"; myReturnFont=myReturnFont&"font:"&myFont&";"; myReturnFont=myReturnFont&"font-weight:"&myBoldWeight&";"; if (myUnderline){ myReturnFont=myReturnFont&"text-decoration:underline;"; } if (myItalic){ myReturnFont=myReturnFont&"font-style:italic;"; } return myReturnFont; } function fnVAlignment(this_cell){ switch(this_cell){ case "0": return "vertical-align:top;"; break; case "1": return "vertical-align:middle;"; break; case "2": return "vertical-align:bottom;"; break; } return ""; } function fnFGColor(this_color){ return "background-color:" & fnColor(this_color) & ";"; } function fnBorder(this_left, this_top, this_right, this_bottom){ var myBorder=""; if (this_left){ myBorder="border-left:1px solid ##000000;"; } if (this_top){ myBorder=myBorder&"border-top:1px solid ##000000;"; } if (this_right){ myBorder=myBorder&"border-right:1px solid ##000000;"; } if (this_bottom){ myBorder=myBorder&"border-bottom:1px solid ##000000;"; } return myBorder; } function fnColor(this_color){ switch(this_color){ case "1": return "##000000;"; break; case "2": return "##ffffff;"; break; case "10": return "##ff0000;"; break; case "4": return "##00ff00;"; break; case "18": return "##0000ff;"; break; case "13": return "##ffff00;"; break; case "7": return "##ff00ff;"; break; case "8": return "##00ffff;"; break; //9 case "60": return "##800000;"; break; case "57": return "##008000;"; break; case "11": return "##000080;"; break; case "12": return "##808000;"; break; case "11113": return "##800080;"; break; case "14": return "##ff0000;"; break; case "22": return "##c0c0c0"; break; case "16": return "##808080"; break; case "17": return "##9999ff"; break; case "11118": return "##993366"; break; case "19": return "##ffffcc"; break; case "20": return "##ccffff"; break; case "21": return "##660066"; break; case "2299": return "##ff8080"; break; case "23": return "##0066cc"; break; case "24": return "##ccccff"; break; case "25": return "##000080"; break; case "26": return "##ff00ff"; break; case "27": return "##ffff00"; break; case "28": return "##00ffff"; break; case "29": return "##800080"; break; case "30": return "##800000"; break; case "31": return "##008080"; break; case "32": return "##0000ff"; break; case "33": return "##00ccff"; break; case "34": return "##ccffff"; break; case "35": return "##ccffcc"; break; case "43": return "##ffff99"; break; case "37": return "##99ccff"; break; case "38": return "##ff99cc"; break; case "39": return "##cc99ff"; break; case "40": return "##ffcc99"; break; case "41": return "##3366ff"; break; case "42": return "##33cccc"; break; case "99": return "##99cc00"; break; case "44": return "##ffcc00"; break; case "45": return "##ff9900"; break; case "46": return "##ff6600"; break; case "47": return "##666699"; break; case "48": return "##969696"; break; case "49": return "##003366"; break; case "50": return "##339966"; break; case "51": return "##003300"; break; case "52": return "##333300"; break; case "53": return "##993300"; break; case "54": return "##993366"; break; case "55": return "##333399"; break; case "56": return "##333333"; break; } return this_color; }

RSS feed for InstantSpot site Daily Investigations

Embedded Procurement

Coldusion MX7 read excel for free (Apache POI)