Normally we create Excel files in coldfusion by the simple cfoutput with table structure and assigning it to a file with extension xls. But by this method we can find that sometimes the excel sheet is having special characters filled up. So here comes a old “NEW” method for the rescue. ie Excel File Creation using POI. Now this method is used as this is more efficient than the normal way of excel creation.
An example code for Excel File Creation
//creating the workbook using poi
<cfset workBook = createObject(“java”,”org.apache.poi.hssf.usermodel.HSSFWorkbook”).init()/>
//assigning style to the cells in the excel sheet
<cfset cellstyle = workbook.createCellStyle()>
//assigning font weight
<cfset fontface = workbook.createFont()>
<cfset fontface.setBoldweight(fontface.BOLDWEIGHT_BOLD)>
<cfset cellstyle.setFont(fontface)>
//this is the sheet counter for the excel work book. for our example it is 3.
<cfset sheetcounter = 0>
<cfloop from=”1″ to=”3″ index=”sheetcounter”>
<cfset newSheet = workBook.createSheet()/>
<cfset row = newSheet.createRow(0)/>
//this is the student list. We will create sheets for these students
<cfset studentlist = “balu,pinky,deepu”/>
<cfset subjectlist = “science,maths,english,computer”/>
<cfloop list = “#studentlist#” index=”student”>
<cfset workBook.setSheetName(#sheetcounter#, “#student#”)/>
//this is for creating headings for each sheet.. ie the subject names science,maths,english,computer
<cfset counter = 0>
<cfloop list=”#subjectlist#” index=”header”>
<cfset cell = row.createCell(counter)/>
<cfset cell.setCellStyle(cellstyle)/>
<cfset cell.setCellValue(#header#)/>
<cfset counter = counter+1>
</cfloop>
<cfset sheetcounter = sheetcounter+1>
</cfloop>
</cfloop>
//the resultset name is studentdetails. We are looping through the query in order to assign the values to the cells of the excel sheet.
<cfloop from = “1″ to = “#listlen(studentlist)#” index = “datacounter”>
<cfset currentsheet = workBook.getSheetAt((datacounter-1)>
<cfset row = currentsheet.createRow(1)/>
<cfset datasheetcounter = 1/>
//getting data from the table student
<cfquery name=”studentdetails” datasource=”studentdb”>
select * from studentdetails where studentname = ‘#datacounter#’
</cfquery>
<cfif studentdetails.recordcount>
//looping through the student details
<cfloop query=”studentdetails”>
<cfset row = currentsheet.createRow(datasheetcounter)/>
<cfset cell = row.createCell(0)/>
<cfset cell.setCellValue(studentdetails.science)/>
<cfset cell = row.createCell(1)/>
<cfset cell.setCellValue(studentdetails.maths)/>
<cfset cell = row.createCell(2)/>
<cfset cell.setCellValue(studentdetails.english)/>
<cfset cell = row.createCell(3)/>
<cfset cell.setCellValue(studentdetails.computer)/>
<cfset datasheetcounter = datasheetcounter + 1>
</cfloop>
</cfif>
</cfloop>
//this is the directory where we are going to save the excel file
<cfset directorypath = “d:\studentdetails”/>
<cfif NOT DirectoryExists(directorypath)>
//creating the directory if the directory is not there in the server
<cfdirectory action=”create” directory=”#directorypath#” >
</cfif>
<cfset fullpath= “d:\studentdetails\studentdetails.xls”/>
//if the file is there in the directory we are deleting that file
<cfif FileExists(fullpath)>
<cffile action=”delete” file=”#fullpath#”>
</cfif>
//writing the file
<cfset fileOutStream = createObject(“java”,”java.io.FileOutputStream”).init(#fullpath#)/>
<cfset workBook.write(fileOutStream)/>
<cfset fileOutStream.close()/>
… So thought to share..
