【轉貼】使用java 轉成excel 文件

【转帖】使用java类生成excel文件范例 http://www.mmug.cn/blogs/index.cfm?sPart=SB&iFid=203&sDayNow=2006-03-20&sAuthor=tianji


在台湾mmug的關於java的POI讀取excel文件的例子，發表后點擊率倍增，很高興有這麽多人關注我的帖子，今天在自己的blog中決定分享給大家一個關餘POI與資料庫交互，生成excel文件的例子，廢話少說，我們開始吧！

輸入簡體中文寫成csv檔，使用excel開啟顯示簡體

CFMX：
<cfcontent type="text/html; charset=UTF-8">
<cfif form.t_name neq "">
 <cfset csv_path="show/" >
 <cfset tmp_output ="浙江天台县今日通报发现天台县下路王村">

         <cfscript>
           writeFile = ("#csv_path#test_js3.csv");

           jFile = createobject("java", "java.io.File").init(writeFile);

           jStream = createobject("java", "java.io.FileOutputStream").init(jFile);

           jStream.write(239);

           jStream.write(187);

           jStream.write(191);

           jWriter = createobject("java", "java.io.OutputStreamWriter");

           jWriter.init(jStream, "UTF-8");

           jWriter.write(tmp_output);

           jWriter.flush();

           jWriter.close();

           jStream.close();

           </cfscript>

</cfif>
 =======================CF 5===================================

  <cftry>
   <cfset jStream=createobject("java","java.io.FileOutputStream")>
   <cfset jStream=jStream.init("show_u8.js")>
   <cfset jWriter=createobject("java","java.io.OutputStreamWriter")>
   <cfset jWriter=jWriter.init(jStream,"UTF-8")>
   <cfset a=jWriter.write(Chr(239))>
   <cfset a=jWriter.write(Chr(187))>
   <cfset a=jWriter.write(Chr(191))>
   <cfset a=jWriter.write(jstring)>
   <cfset a=jWriter.flush()>
   <cfset a=jWriter.close()>
   <cfset a=jStream.close()>
  <cfcatch></cfcatch>
  </cftry>

取query某欄位所有內容值存入list

<cfquery name="sel_page" datasource="#odbc#">

 select id,title  from show

</cfquery>

 

 <cfset title_list = #ValueList(sel_page.title)#>

 

<cfoutput> title_list = # title_list#</cfoutput>

網頁下載檔案

 <cfheader name="Content-disposition" value="attachment;filename=info_pps/#sel_file.pps_file#">
<cfhttp url="#expandPath('goods_A1.htm')#" method="GET" resolveurl="YES">
<cfcontent type="application/x-mspowerpoint" file = "#expandPath('info_pps/#sel_file.pps_file#')#">

匯出至excel 檔案 網頁header設定

MX:

<cfheader name="Content-Disposition" value="attachment; filename=OneMoreSpreadSheet.xls">
<cfcontent type="application/x-msexcel; charset=utf-16" reset="No">

 

5:

<CFCONTENT TYPE="application/vnd.ms-excel" >

 

XML子節點判斷是否有內容

<cfset nxml_content = xml>
<cfset nxml = XmlParse(nxml_content)>
<cfif IsDefined("nxml.ma.image")>
    <cfset n_image64 = Trim(nxml.ma.image.xmltext)>
    <cfset nxml_length = ArrayLen(nxml.ma.itemn)>
</cfif>

讀取xml

<!---使用java元件讀取xml --->
 <cfscript>
  xmlReaderPool = CreateObject("JAVA", "XMLReaderPool");
  poolXMLReader = XMLReaderPool.getInstance();
  xmlList = poolXMLReader.getXMLReader();
  xmlList.doParse(returnvalue);
  xmlRecordCount= xmlList.getAttributes("ROOT.RECORDCOUNT");
  pageRowCount = xmlList.getItemNums("ROOT.ITEM");
 </cfscript>

判斷get參數的正則式

<cfdump var='#REFindNoCase("([A-Za-z]+=[0-9]+&)+([A-Za-z]+=[0-9]+)$","#cgi.query_string#")#'>
 

<cfoutput>#REFindNoCase("^[A-Za-z]+=[0-9]+$","tas=78911&ddd=2342")#</cfoutput>
判斷get參數的正則式

email判斷正則式

cfm 5 email 正則式: (兩個是一樣的)

<cfset reg = "[[:alnum:]]+([-+.’][a-zA-Z0-9]+)*@[a-zA-Z0-9]+([-+.’][[:alpha:]]+)*\.[[:alpha:]]{2,}([-+.’][[:alpha:]]+)*" > 
<cfset reg = "[[:alnum:]]+([-+.’][[:alnum:]]+)*@[[:alnum:]]+([-+.’][[:alpha:]]+)*\.[[:alpha:]]{2,}([-+.’][[:alpha:]]+)*" >

CFQUERYPARAM使用說明

CFQUERYPARAM使用說明

 

1.文字                         
     <cfset indcat = "電子">
     <cfquery name="select" DATASOURCE="database">
          ...
          where indcat = <CFQUERYPARAM value = "#indcat#" CFSQLType = "CF_SQL_VARCHAR">
     </cfquery>
 

讀csv檔並做(中文)筆劃排序

<cffile action = "read" file = "#expandpath('csv.csv')#" variable = "csv_show" >
<cfset myQuery = QueryNew("name,main, joblist, bloglink ,sowf")><!----定義新的Query---->
<cfset newRow = QueryAddRow(myQuery,ListLen(csv_show,chr(13)&chr(10)))> <!----給新的Query預設Row大小----->

計算程式所執行的時間

 <cfset tickBeginTime = gettickcount()>
 .................(預計算時間的程式)
 <cfset tickEndTime = gettickcount()>
 <cfset tickTime =tickEndTime-tickBeginTime>
 Time #tickTime#<br>

計算目前已使用的記憶體

<cfset mysys = CreateObject("java", "java.lang.Runtime")>
 <cfset runtime = mysys.getRuntime()>
 <cfset freeMemory = runtime.freeMemory() / 1024 / 1024> 


 <cfset totalMemory = runtime.totalMemory() / 1024 / 1024>
 <cfset free_str="Free=#Round(freeMemory)#&nbsp;&nbsp;Total=#Round(totalMemory)#">
 Memory #free_str#<br>

傳值至Oracle 型別為date

日期:

to_date('#DateFormat(BIRTHDAY,'YYYY/MM/DD')#','YYYY/MM/DD')

日期+時間

appear_date between

to_date('#DateFormat(form.app_startDate,'YYYY/MM/DD')# 00:00:00','YYYY/MM/DD HH24:MI:ss')

and

to_date('#DateFormat(form.app_endDate,'YYYY/MM/DD')# 23:59:59','YYYY/MM/DD HH24:MI:ss')

取消換行符號

取消換行符號 --
Chr(13)為歸位字元
Chr(10)為換行字元



 <cfset tagtext = replace(tagtext,chr(13)&chr(10),"","all")>

cfm出現亂碼

注意網頁編碼方式!!

可加入 

<cfcontent type="text/html; charset=big5">

CMYK圖檔格式轉為JPG

設定圖片大小，CMYK轉成RBG

<cfset width = 110>
<cfset height = 140>