[PHP]-將db資料匯出成excel檔下載

範例程式碼如下:

$dbh = new PDO('sqlite:question.db');
// 取得資料總數
$rs = null;
$sth = $dbh->query("SELECT * FROM questions");
$rs = $sth->fetchAll();

if ($rs != null) {
    header("Content-type:application/vnd.ms-excel");
    header("Content-Disposition:filename=excel.xls");
    foreach ($rs as $value) {
        $content.=$value['field1']."\t".$value['field2']."\t".$value['field3']."\n";
    }
    echo $content;
}

第一個重點是利用header("Content-type:application/vnd.ms-excel")告知瀏覽器內文是
excel格式。
第二個重點是利用header("Content-Disposition:filename=excel.xls")告知用戶端(Browser)
強制下載檔案，並使用filename屬性指定下載檔名。
其中利用\t來區隔每個欄位，其實就是csv格式。

執行時的畫面如下:

另外excel無法識別utf-8格式的中文，可以利用iconv("utf-8","big5", $value['field'])函式，
來將編碼從utf-8轉為big5輸出。

最後整理一下Content Type大全:
.* = application/octet-stream
.001 = application/x-001
.301 = application/x-301
.323 = text/h323
.906 = application/x-906
.907 = drawing/907
.a11 = application/x-a11
.acp = audio/x-mei-aac
.ai = application/postscript
.aif = audio/aiff
.aifc = audio/aiff
.aiff = audio/aiff
.anv = application/x-anv
.asa = text/asa
.asf = video/x-ms-asf
.asp = text/asp
.asx = video/x-ms-asf
.au = audio/basic
.avi = video/avi
.awf = application/vnd.adobe.workflow
.biz = text/xml
.bmp = application/x-bmp
.bot = application/x-bot
.c4t = application/x-c4t
.c90 = application/x-c90
.cal = application/x-cals
.cat = application/vnd.ms-pki.seccat
.cdf = application/x-netcdf
.cdr = application/x-cdr
.cel = application/x-cel
.cer = application/x-x509-ca-cert
.cg4 = application/x-g4
.cgm = application/x-cgm
.cit = application/x-cit
.class = java/*
.cml = text/xml
.cmp = application/x-cmp
.cmx = application/x-cmx
.cot = application/x-cot
.crl = application/pkix-crl
.crt = application/x-x509-ca-cert
.csi = application/x-csi
.css = text/css
.cut = application/x-cut
.dbf = application/x-dbf
.dbm = application/x-dbm
.dbx = application/x-dbx
.dcd = text/xml
.dcx = application/x-dcx
.der = application/x-x509-ca-cert
.dgn = application/x-dgn
.dib = application/x-dib
.dll = application/x-msdownload
.doc = application/msword
.dot = application/msword
.drw = application/x-drw
.dtd = text/xml
.dwf = Model/vnd.dwf
.dwf = application/x-dwf
.dwg = application/x-dwg
.dxb = application/x-dxb
.dxf = application/x-dxf
.edn = application/vnd.adobe.edn
.emf = application/x-emf
.eml = message/rfc822
.ent = text/xml
.epi = application/x-epi
.eps = application/x-ps
.eps = application/postscript
.etd = application/x-ebx
.exe = application/x-msdownload
.fax = image/fax
.fdf = application/vnd.fdf
.fif = application/fractals
.fo = text/xml
.frm = application/x-frm
.g4 = application/x-g4
.gbr = application/x-gbr
.gcd = application/x-gcd
.gif = image/gif
.gl2 = application/x-gl2
.gp4 = application/x-gp4
.hgl = application/x-hgl
.hmr = application/x-hmr
.hpg = application/x-hpgl
.hpl = application/x-hpl
.hqx = application/mac-binhex40
.hrf = application/x-hrf
.hta = application/hta
.htc = text/x-component
.htm = text/html
.html = text/html
.htt = text/webviewhtml
.htx = text/html
.icb = application/x-icb
.ico = image/x-icon
.ico = application/x-ico
.iff = application/x-iff
.ig4 = application/x-g4
.igs = application/x-igs
.iii = application/x-iphone
.img = application/x-img
.ins = application/x-internet-signup
.isp = application/x-internet-signup
.IVF = video/x-ivf
.java = java/*
.jfif = image/jpeg
.jpe = image/jpeg
.jpe = application/x-jpe
.jpeg = image/jpeg
.jpg = image/jpeg
.jpg = application/x-jpg
.js = application/x-javascript
.jsp = text/html
.la1 = audio/x-liquid-file
.lar = application/x-laplayer-reg
.latex = application/x-latex
.lavs = audio/x-liquid-secure
.lbm = application/x-lbm
.lmsff = audio/x-la-lms
.ls = application/x-javascript
.ltr = application/x-ltr
.m1v = video/x-mpeg
.m2v = video/x-mpeg
.m3u = audio/mpegurl
.m4e = video/mpeg4
.mac = application/x-mac
.man = application/x-troff-man
.math = text/xml
.mdb = application/msaccess
.mdb = application/x-mdb
.mfp = application/x-shockwave-flash
.mht = message/rfc822
.mhtml = message/rfc822
.mi = application/x-mi
.mid = audio/mid
.midi = audio/mid
.mil = application/x-mil
.mml = text/xml
.mnd = audio/x-musicnet-download
.mns = audio/x-musicnet-stream
.mocha = application/x-javascript
.movie = video/x-sgi-movie
.mp1 = audio/mp1
.mp2 = audio/mp2
.mp2v = video/mpeg
.mp3 = audio/mp3
.mp4 = video/mpeg4
.mpa = video/x-mpg
.mpd = application/vnd.ms-project
.mpe = video/x-mpeg
.mpeg = video/mpg
.mpg = video/mpg
.mpga = audio/rn-mpeg
.mpp = application/vnd.ms-project
.mps = video/x-mpeg
.mpt = application/vnd.ms-project
.mpv = video/mpg
.mpv2 = video/mpeg
.mpw = application/vnd.ms-project
.mpx = application/vnd.ms-project
.mtx = text/xml
.mxp = application/x-mmxp
.net = image/pnetvue
.nrf = application/x-nrf
.nws = message/rfc822
.odc = text/x-ms-odc
.out = application/x-out
.p10 = application/pkcs10
.p12 = application/x-pkcs12
.p7b = application/x-pkcs7-certificates
.p7c = application/pkcs7-mime
.p7m = application/pkcs7-mime
.p7r = application/x-pkcs7-certreqresp
.p7s = application/pkcs7-signature
.pc5 = application/x-pc5
.pci = application/x-pci
.pcl = application/x-pcl
.pcx = application/x-pcx
.pdf = application/pdf
.pdf = application/pdf
.pdx = application/vnd.adobe.pdx
.pfx = application/x-pkcs12
.pgl = application/x-pgl
.pic = application/x-pic
.pko = application/vnd.ms-pki.pko
.pl = application/x-perl
.plg = text/html
.pls = audio/scpls
.plt = application/x-plt
.png = image/png
.png = application/x-png
.pot = application/vnd.ms-powerpoint
.ppa = application/vnd.ms-powerpoint
.ppm = application/x-ppm
.pps = application/vnd.ms-powerpoint
.ppt = application/vnd.ms-powerpoint
.ppt = application/x-ppt
.pr = application/x-pr
.prf = application/pics-rules
.prn = application/x-prn
.prt = application/x-prt
.ps = application/x-ps
.ps = application/postscript
.ptn = application/x-ptn
.pwz = application/vnd.ms-powerpoint
.r3t = text/vnd.rn-realtext3d
.ra = audio/vnd.rn-realaudio
.ram = audio/x-pn-realaudio
.ras = application/x-ras
.rat = application/rat-file
.rdf = text/xml
.rec = application/vnd.rn-recording
.red = application/x-red
.rgb = application/x-rgb
.rjs = application/vnd.rn-realsystem-rjs
.rjt = application/vnd.rn-realsystem-rjt
.rlc = application/x-rlc
.rle = application/x-rle
.rm = application/vnd.rn-realmedia
.rmf = application/vnd.adobe.rmf
.rmi = audio/mid
.rmj = application/vnd.rn-realsystem-rmj
.rmm = audio/x-pn-realaudio
.rmp = application/vnd.rn-rn_music_package
.rms = application/vnd.rn-realmedia-secure
.rmvb = application/vnd.rn-realmedia-vbr
.rmx = application/vnd.rn-realsystem-rmx
.rnx = application/vnd.rn-realplayer
.rp = image/vnd.rn-realpix
.rpm = audio/x-pn-realaudio-plugin
.rsml = application/vnd.rn-rsml
.rt = text/vnd.rn-realtext
.rtf = application/msword
.rtf = application/x-rtf
.rv = video/vnd.rn-realvideo
.sam = application/x-sam
.sat = application/x-sat
.sdp = application/sdp
.sdw = application/x-sdw
.sit = application/x-stuffit
.slb = application/x-slb
.sld = application/x-sld
.slk = drawing/x-slk
.smi = application/smil
.smil = application/smil
.smk = application/x-smk
.snd = audio/basic
.sol = text/plain
.sor = text/plain
.spc = application/x-pkcs7-certificates
.spl = application/futuresplash
.spp = text/xml
.ssm = application/streamingmedia
.sst = application/vnd.ms-pki.certstore
.stl = application/vnd.ms-pki.stl
.stm = text/html
.sty = application/x-sty
.svg = text/xml
.swf = application/x-shockwave-flash
.tdf = application/x-tdf
.tg4 = application/x-tg4
.tga = application/x-tga
.tif = image/tiff
.tif = application/x-tif
.tiff = image/tiff
.tld = text/xml
.top = drawing/x-top
.torrent = application/x-bittorrent
.tsd = text/xml
.txt = text/plain
.uin = application/x-icq
.uls = text/iuls
.vcf = text/x-vcard
.vda = application/x-vda
.vdx = application/vnd.visio
.vml = text/xml
.vpg = application/x-vpeg005
.vsd = application/vnd.visio
.vsd = application/x-vsd
.vss = application/vnd.visio
.vst = application/vnd.visio
.vst = application/x-vst
.vsw = application/vnd.visio
.vsx = application/vnd.visio
.vtx = application/vnd.visio
.vxml = text/xml
.wav = audio/wav
.wax = audio/x-ms-wax
.wb1 = application/x-wb1
.wb2 = application/x-wb2
.wb3 = application/x-wb3
.wbmp = image/vnd.wap.wbmp
.wiz = application/msword
.wk3 = application/x-wk3
.wk4 = application/x-wk4
.wkq = application/x-wkq
.wks = application/x-wks
.wm = video/x-ms-wm
.wma = audio/x-ms-wma
.wmd = application/x-ms-wmd
.wmf = application/x-wmf
.wml = text/vnd.wap.wml
.wmv = video/x-ms-wmv
.wmx = video/x-ms-wmx
.wmz = application/x-ms-wmz
.wp6 = application/x-wp6
.wpd = application/x-wpd
.wpg = application/x-wpg
.wpl = application/vnd.ms-wpl
.wq1 = application/x-wq1
.wr1 = application/x-wr1
.wri = application/x-wri
.wrk = application/x-wrk
.ws = application/x-ws
.ws2 = application/x-ws
.wsc = text/scriptlet
.wsdl = text/xml
.wvx = video/x-ms-wvx
.xdp = application/vnd.adobe.xdp
.xdr = text/xml
.xfd = application/vnd.adobe.xfd
.xfdf = application/vnd.adobe.xfdf
.xhtml = text/html
.xls = application/vnd.ms-excel
.xls = application/x-xls
.xlw = application/x-xlw
.xml = text/xml
.xpl = audio/scpls
.xq = text/xml
.xql = text/xml
.xquery = text/xml
.xsd = text/xml
.xsl = text/xml
.xslt = text/xml
.xwd = application/x-xwd
.x_b = application/x-x_b
.x_t = application/x-x_t

[PHP]-上傳圖檔並等比例縮圖

首先要上傳圖檔，form的enctype屬性要設為multipart/form-data，如下:

<form name="form1" method="post" enctype="multipart/form-data" action="...">
    <input name="file1" type="file">
  </form>

並且將上傳檔案並縮圖的程式整理成兩個函式

function ImageResize($from_filename, $save_filename, 
               $in_width=400, $in_height=300, $quality=100)
{
    // 首先取得來源圖檔的資訊
    $img_info = getimagesize($from_filename);
    $width    = $img_info['0'];
    $height   = $img_info['1'];
    $imgtype  = $img_info['2'];
    $imgtag   = $img_info['3'];
    $bits     = $img_info['bits'];
    $channels = $img_info['channels'];
    $mime     = $img_info['mime'];

    $sub_name = $t = '';
    // 利用mime屬性來判斷要產生什麼格式的新image
    list($t, $sub_name) = split('/', $mime);

    // 取得縮在此範圍內的比例
    $percent = getResizePercent($width, $height, $in_width, $in_height);
    $new_width  = $width * $percent;
    $new_height = $height * $percent;

    // Resample
    $image_new = imagecreatetruecolor($new_width, $new_height);
    $image = null;
    if ($sub_name == "jpeg") {
        $image = imagecreatefromjpeg($from_filename);
        $sub_name = ".jpg";
    } else if ($sub_name == "png") {
        $image = imagecreatefrompng($from_filename);
        $sub_name = ".png";
    } else if ($sub_name == "gif") {
        $image = imagecreatefromgif($from_filename);
        $sub_name = ".gif";
    }
    // 利用imagecopyresampled函式來縮圖
    imagecopyresampled($image_new, $image, 0, 0, 0, 0, $new_width, 
                                              $new_height, $width, $height);
    // 產生圖檔到指定路徑
    imagejpeg($image_new, $save_filename.$sub_name, $quality);
}

function getResizePercent($source_w, $source_h, $inside_w, $inside_h)
{
    if ($source_w < $inside_w && $source_h < $inside_h) {
        return 1; // Percent = 1, 如果都比預計縮圖的小就不用縮
    }

    $w_percent = $inside_w / $source_w;
    $h_percent = $inside_h / $source_h;

    return ($w_percent > $h_percent) ? $h_percent : $w_percent;
}

接下來是使用函式的方法

$uploaddir = "images/";
  $uploadfile = $uploaddir.basename($_FILES['file1']['name']);
  ImageResize($_FILES['file1']['tmp_name'], $uploadfile);

上傳檔案時，上傳的檔案會先存放到伺服器上的一個暫存目錄，
$_FILES['file1']['tmp_name']會取得該暫存檔的絕對路徑，
而$_FILES['file1']['name']就是被選取上傳檔案的檔名。

這裡要註記一個重點:
只要上傳的檔案大小加上post本身的大小，超過 post_max_size 的限定，
就接收不到資料了（沒錯誤訊息，連一般post欄位的值也會都是空的!!）

[Javascript]-限制上傳檔案型態

這裡我們利用javascript的pattern來限制上傳檔案的型態，
要產生一個 RegExp 個體有兩種方式。第一種是直接以斜線 (/) 包住pattern，
RegExp 個體提供兩個主要行為，即 exec() 和 test() 。 exec() 會對引數字串進行比對運算，
並將匹配的字串內容記錄在陣列中回傳。而 test() 則僅僅測試引數字串是否符合規則，只回傳 true 或 false 。
此外，RegExp 個體也可以作為 String 個體的 match(), search(), replace(), split() 之引數。
String 的 match() 其實就是調用 RegExp 的 exec() ，兩者的結果相同。

以下是範例程式:

  var re = /\.(jpg|gif|png)$/;  //允許的圖片副檔名
  var file1 = document.form.file1;
  if (file1.value.length !=0 && !re.test(file1.value)) {
    alert("檔案1只允許上傳JPG、PNG或GIF影像檔");
    return false;
  }

簡短說明幾個常見的字元意義:
1.^
寫在 pattern 第一個位置時，表示其後一符號必須出現在字串開頭的位置。寫在 pattern 中間位置時則為否定之意，表示字串中不可有 ^ 之後一符號的內容。
2.$
寫在 pattern 最後一個位置時，表示其前一符號必須出現在字串尾端的位置。寫在 pattern 中時無特別意義。
3.*
表示字串中有 0 到無數個其前一符號的內容。
4.+
表示字串中有 1 到無數個其前一符號的內容。
5.?
表示字串中有 0 到 1個其前一符號的內容。
6.{ }
表示前一符號在字串中的重覆次數。例如 /A{2}/ 表示 'A' 重覆兩次 (即 'AA') ；/A{2,}/ 表示字串含有 2 到無數多個 'A' ；/A{2,5}/ 表示含有 2 到 5 個 'A' 。
7..
表示一個任意字元。
8.[ ]
表示字串含有括號中任一字元的內容。可以 - 表示一組連續字元，例如 /[a-z]/, /[0-9]/ 。注意， [] 僅代表一個字元，例如 /[abc]/ 表示 'a' 或 'b' 或 'c' ，而不是 'abc' 。
9.( )
表示一個 sub pattern ，符合 sub pattern 的字串內容會被存放在匹配陣列中，並依序指派數字代表此 sub pattern 。可以此數字在 pattern 的其他地方引用內容，例如 /The h([0-9]) means Title (\1)/ 表示第 1 個 sub pattern 是 0 到 9 的任一字元，而 \1 表示匹配的內容。故 'The h1 means Title 1', 'The h2 means Title 2' 到 'The h9 means Title 9' 符合規則。
10.\
表示轉義 (escaping) ，將其後的字元視為一般字元。例如要表示字串中含有 '/' 字元時，就必須寫作 /\// 。
11.|
「或」意，字串中含有 '|' 之前一符號或後一符號的內容。例如 /image\.(jpg|png)/ 表示 'image.jpg' 或 'image.png' 。通常會用 () 括住 '|' 的前後符號。
12.\d
表示任何一個數字，意同 [0-9] 。
13.\D
表示任何一個非數字，意同 [^0-9] 。
14.\w
表示任何一個字元與數字以及 '_' ，意同 [a-zA-Z0-9_] 。
15.\W
表示任何一個 \w 以外的字元。
16.\s
表示任何一個空白符號，包括 \t, \v 等。
17.\S
表示任何一個非空白符號。