[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
表示任何一個非空白符號。

[C#]-利用clipboard轉換office內容物為圖片

最近接了一個case，
在office上選取物件利用ctrl+c複製物件到clipboard內，
並在picturebox控制項上利用ctrl+v將物件從clipboard取出，
並以圖片形式顯示在picturebox上，
最後將picturebox上的image存成圖片檔。

首先先記錄一下如何判斷鍵盤的組合鍵，如下:

private void Form1_KeyDown(object sender, KeyEventArgs e)
{
    switch (e.KeyData)
    {
        case (Keys.Control | Keys.V):
            // do something 
            break;
    }
}

接下來的重點是從clipboard內取出物件時必須知道它支援那些格式，
我們可以利用下面的方式知道該物件所支援的格式有那些:

foreach (string format in Clipboard.GetDataObject().GetFormats())
{
    // do something
}

也有一些簡便的方式，例如Clipboard.ContainsText()或Clipboard.ContainsImage()等可以判斷。

再來我們準備一些method來從clipboard中取出office中大部份常見的格式。
Bitmap格式的物件，我們都能用下面的方式取得image:

public void CatchBitmap()
{
    this.pictureBox1.Image = (Bitmap)Clipboard.GetImage();
}

這裡遇到比較麻煩的困難點是EnhancedMetafile格式，
必須要用win32裡的API去取得系統的clipboard來使用，方式如下:

public const uint CF_METAFILEPICT = 3;
public const uint CF_ENHMETAFILE = 14;

[DllImport("user32.dll", CharSet = CharSet.Auto, ExactSpelling = true)]
public static extern bool OpenClipboard(IntPtr hWndNewOwner);

[DllImport("user32.dll", CharSet = CharSet.Auto, ExactSpelling = true)]
public static extern bool CloseClipboard();

[DllImport("user32.dll", CharSet = CharSet.Auto, ExactSpelling = true)]
public static extern IntPtr GetClipboardData(uint format);

[DllImport("user32.dll", CharSet = CharSet.Auto, ExactSpelling = true)]
public static extern bool IsClipboardFormatAvailable(uint format);

public void CatchEMF()
{
    if (OpenClipboard(this.Handle)) {
        if (IsClipboardFormatAvailable(CF_ENHMETAFILE))
        {
            IntPtr ptr = GetClipboardData(CF_ENHMETAFILE);
            if (!ptr.Equals(new IntPtr(0)))
            {
                Metafile metafile = new Metafile(ptr, true);
                //Set the Image Property of PictureBox 
                this.pictureBox1.Image = metafile;
            }
        }
        CloseClipboard();
    }
}

再來如果copy放進clipboard內的是純文字物件的話，
就必須要用Graphics來drawing string才能產生圖檔，如下:

public void DrawString()
{
    String sImageText = Clipboard.GetText();
    Bitmap objBmpImage = new Bitmap(1, 1);

    int intWidth = 0;
    int intHeight = 0;

    // Create the Font object for the image text drawing.
    Font objFont = 
        new Font("Arial", 20, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Pixel);

    // Create a graphics object to measure the text's width and height.
    Graphics objGraphics = Graphics.FromImage(objBmpImage);

    // This is where the bitmap size is determined.
    intWidth = (int)objGraphics.MeasureString(sImageText, objFont).Width;
    intHeight = (int)objGraphics.MeasureString(sImageText, objFont).Height;

    // Create the bmpImage again with the correct size for the text and font.
    objBmpImage = new Bitmap(objBmpImage, new Size(intWidth, intHeight));

    // Add the colors to the new bitmap.
    objGraphics = Graphics.FromImage(objBmpImage);

    // Set Background color
    objGraphics.Clear(Color.White);
    objGraphics.SmoothingMode = SmoothingMode.AntiAlias;
    objGraphics.TextRenderingHint = TextRenderingHint.AntiAlias;
    objGraphics.DrawString(sImageText, objFont, 
        new SolidBrush(Color.FromArgb(102, 102, 102)), 0, 0);
    objGraphics.Flush();
    this.pictureBox1.Image = objBmpImage;
}

接下來下面要講解的功能是如何把在excel內選取的cells自動轉換成對應的chart圖，
基本上複製的cells放進clipboard本身就支援Bitmap格式，
但直接取用會直接像剪貼那樣，如果要轉換成對應的chart圖則必須再行判斷，
這裡是利用Microsoft.Office.Interop.Excel來轉換，範例碼如下:

public void Convert()
{
    IDataObject iData = Clipboard.GetDataObject();
    String cellArea = (String)iData.GetData(DataFormats.Text);
    String[] rows = cellArea.Split(new Char[] { '\n' });

    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    xlApp = new Excel.ApplicationClass();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    int rowLength = rows.Length;
    int rowCount = 1;
    int cellCount = 1;
    int maxCellCount = 1;
    
    foreach (String line in rows)
    {
        if (rowCount == rowLength)
        {
            break;
        }
        String[] cells = line.Split(null);
        int cellLength = cells.Length;
        foreach (String cell in cells)
        {
            if (cellCount == cellLength)
            {
                break;
            }
            if (cellCount > maxCellCount)
            {
                maxCellCount++;
            }
            xlWorkSheet.Cells[rowCount, cellCount] = cell;
            cellCount++;
        }
        rowCount++;
        cellCount = 1;
    }

    Excel.Range chartRange;

    Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
    Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
    Excel.Chart chartPage = myChart.Chart;

    rowCount--;
    maxCellCount--;
    String endCorner = map.ElementAt(maxCellCount).Value;
    endCorner = endCorner + rowCount.ToString();
    
    // endCorner的格式例如:c7，就是cells右下角的範圍邊界點
    chartRange = xlWorkSheet.get_Range("A1", endCorner);
    chartPage.SetSourceData(chartRange, misValue);
    // 設定繪製的chart圖型式
    chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
    chartPage.CopyPicture(Excel.XlPictureAppearance.xlScreen,
                          Excel.XlCopyPictureFormat.xlBitmap,
                          Excel.XlPictureAppearance.xlScreen);
    xlWorkBook.Close(false, false, misValue);
}

產生的chart圖會利用copy放進clipboard內。

使用畫面如下，紅色框部份就是被選取的cells，當在picturebox上按下ctrl+v時，
就會利用office元件繪製chart圖:

最後就是整理一些條件判斷式，來處理當遇到什樣格式時要執行那一個method比較恰當:

// 一般的圖檔
if (this.formatList.Contains("Bitmap") && !this.formatList.Contains("Text"))
{
    CatchBitmap();
}
// excel的cells欄位集合
else if (this.formatList.Contains("Bitmap") && this.formatList.Contains("Text"))
{
    // if want to create chart
    Convert();
                
    // offic 2003
    CatchBitmap();
                   
    // offic 2007
    CatchEMF();
}
// 純粹選取文字
else if (!this.formatList.Contains("Bitmap") && this.formatList.Contains("Text"))
{
    DrawString();
}
// excel2003的chart，只有emf格式，最後再處理
else if (this.formatList.Contains("EnhancedMetafile"))
{
    CatchEMF();
}

[C#]-利用Microsoft.Office.Interop.Outlook讀取outlook msg檔

若要開發 Microsoft Office Outlook 的增益集，您可以與 Outlook 物件模型提供的物件進行互動。此 Outlook 物件模型會提供表示使用者介面中各種項目的類別。例如，Microsoft.Office.Interop.Outlook.Application 類別是表示整個應用程式、Microsoft.Office.Interop.Outlook.MAPIFolder 類別是表示含有電子郵件訊息或其他項目的資料夾，而 Microsoft.Office.Interop.Outlook.MailItem 類別則是表示電子郵件訊息。

Outlook 會提供許多您可以與之互動的類別。下列各節將簡述某些最上層類別，並描述它們彼此的互動關係。這些類別包括：

• Microsoft.Office.Interop.Outlook.Application
• Microsoft.Office.Interop.Outlook.Explorer
• Microsoft.Office.Interop.Outlook.Inspector
• Microsoft.Office.Interop.Outlook.MAPIFolder
• Microsoft.Office.Interop.Outlook.MailItem
• Microsoft.Office.Interop.Outlook.AppointmentItem
• Microsoft.Office.Interop.Outlook.TaskItem
• Microsoft.Office.Interop.Outlook.ContactItem

下面是範例程式:
首先在專案參考內先匯入Microsoft.Office.Interop.Outlook
接著是取出msg檔內各欄位的方式

Microsoft.Office.Interop.Outlook.Application app = null;
Microsoft.Office.Interop.Outlook.MailItem item = null;

DialogResult msgFileSelectResult = this.openFileDialog.ShowDialog();
if (msgFileSelectResult == DialogResult.OK)
{
     string msgfile = this.openFileDialog.FileName
     app = new Microsoft.Office.Interop.Outlook.Application();
     item = app.Session.OpenSharedItem(msgfile) as Microsoft.Office.Interop.Outlook.MailItem;
     this.label1.Text = item.Subject;
     this.label2.Text = item.SenderName;
     this.label3.Text = item.To;
     this.label4.Text = item.Body;           
}

[PHP]-使用PDO操作sqlite

PHP5以後除了使用mysql_connect這種舊的方法連接資料庫之外，
加入了PDO的新功能可以用來連接資料庫，好處在於在效能上改善很多，
而且未來在將系統移植道不同的資料庫時也比較方便。

因為在PHP 5以前的php4/php3都是一堆的數據庫擴展來跟各個數據庫的連接和處理，
什麼 php_mysql.dll、php_pgsql.dll、php_mssql.dll、php_sqlite.dll等等擴展
來連接MySQL、 PostgreSQL、MS SQL Server、SQLite，同樣的，
我們必須借助 ADOdb、PEAR::DB、PHPlib::DB之類的數據庫抽象類來幫助我們，
無比煩瑣和低效。

以下是各種不同資料庫中的dsn(數據源)設定方法：

MySQL：
mysql:host={IP HOST}; dbname={DB NAME}

MSSQL︰
mssql:host={IP HOST};dbname={DB NAME}

Orcale：
oci:host={IP HOST};dbname={DB NAME}

SQLite：
sqlite:{DB PATH + DB NAME}

若是要設定預設的編碼，例如要設為使用UTF8,可在dsn中設定charset=UTF8即可。

簡介SQLite
1. ACID事務
2. 零配置 – 無需安裝和管理配置
3. 儲存在單一磁盤文件中的一個完整的數據庫
4. 數據庫文件可以在不同字節順序的機器間自由的共享
5. 支持數據庫大小至2TB
6. 足夠小, 大致3萬行C代碼, 250K
7. 比一些流行的數據庫在大部分普通數據庫操作要快
8. 簡單, 輕鬆的API
9. 包含TCL綁定, 同時通過Wrapper支持其他語言的綁定
10. 良好註釋的源代碼, 並且有著90%以上的測試覆蓋率
11. 獨立: 沒有額外依賴
12. Source完全的Open, 你可以用於任何用途, 包括出售它
要注意的是，如果在linux上使用sqlite，資料檔和資料檔所在目錄的權限一定要開，
這裡特別要強調的是，本身所在目錄的權限也要。

雖然SQLite允許忽略數據類型，下面仍列出常見的數據類型:
CREATE TABLE ex(
a VARCHAR(10),
b NVARCHAR(15),
c TEXT,
d INTEGER,
e FLOAT,
f BOOLEAN,
g CLOB,
h BLOB,
i TIMESTAMP,
j NUMERIC(10,5),
k VARYING CHARACTER (24),
l NATIONAL VARYING CHARACTER(16)
);

下面列舉一些利用PDO操作SQLite的範例:
create table

$dbh = new PDO('sqlite:example.db');
if (!$dbh) die ($error);

$stm = "CREATE TABLE User(id INTEGER PRIMARY KEY, name, birthday1, birthday2, ".
            "birthday0, gender, marry, education, job, tel1, tel2, ext, pw, ".
            "city, district, address, email)";
$dbh->exec($stm);

可以在欄位後面直接加INTEGER PRIMARY KEY指定為primary key，這個欄位值
同時也具有auto increment特性。
也可以利用下列語法指定索引鍵:
CREATE INDEX id_index on table_name(id)

select data from table

$dbh = new PDO('sqlite:example.db');
$sth = $dbh->prepare("SELECT * FROM User");
$sth->execute();
$result_set = $sth->fetchAll();

result_set的格式會如下:
Array
([0] =＞ Array(
[id] =＞ 1
[0] =＞ 1
[name] =＞ heiyeluren
[1] =＞ heiyeluren
[gender] =＞ 男
[2] =＞ 男
[time] =＞ 2006-10-28 23:14:23
[3] =＞ 2006-10-28 23:14:23
)

prepare statement 型式

$dbh = new PDO('sqlite:example.db');
$sth = $dbh->prepare("SELECT * FROM User WHERE email = ?");
$sth->execute(array($_SESSION['user']));
$result = $sth->fetch();

攔截錯誤訊息

$dbh = new PDO('sqlite:example.db');
$rs = $dbh-＞query("SELECT * FROM User");
if ($dbh-＞errorCode() != 00000){
　echo $dbh-＞errorInfo();
　exit;
}

PDO和PDOStatement對象有errorCode() 和 errorInfo() 方法，
如果沒有任何錯誤, errorCode() 返回的是: 00000 ，
否則就會返回一些錯誤代碼。errorInfo() 返回的一個數組，
包括PHP定義的錯誤代碼和MySQL的錯誤代碼和錯誤信息，數組結構如下：
Array
(
[0] =＞ 42S22
[1] =＞ 1054
[2] =＞ Unknown column aaa in field list
)

PDO的一些method參數說明

$dbh = new PDO('sqlite:example.db');
$dbh-＞setAttribute(PDO::ATTR_CASE, PDO::CASE_UPPER);
$rs = $dbh-＞query("SELECT * FROM User");
$rs-＞setFetchMode(PDO::FETCH_ASSOC);
$result_set = $rs-＞fetchAll();

setAttribute() 方法是設置部分屬性
PDO::CASE_LOWER -- 強制列名是小寫
PDO::CASE_NATURAL -- 列名按照原始的方式
PDO::CASE_UPPER -- 強制列名為大寫

setFetchMode方法來設置獲取結果集的返回值的類型
PDO::FETCH_ASSOC -- 關聯數組形式
PDO::FETCH_NUM -- 數字索引數組形式
PDO::FETCH_BOTH -- 兩者數組形式都有，這是缺省的
PDO::FETCH_OBJ -- 按照對象的形式，類似於以前的 mysql_fetch_object()

[Struts2]-Hibernate Integration

在談論Hibernate前我們要先了解JPA(Java Persistence API)

Java的物件導向模型與關聯資料庫模型之間有相當程度的不匹配，而一些物件與資料庫資料同步、更新，也就是所謂的Java永續儲存（Persistence）問題，在過去，Object/Relational Mapping（ORM）有 JBoss Hibernate、Oracle TopLink等解決方案，而JPA為吸收這些方案的經驗，所製訂出的Java永續儲存標準。

使用JPA，底層可以使用不同廠商的ORM實作，而介面則是JPA的標準，若您使用NetBeans+Glassfish，則預設的底層實作為TopLink，JBoss的工具其底層實作則為Hibernate。

要在Struts2使用Hibernate Plugin，必須有下列libs:

antlr-2.7.6.jar
commons-collections-3.1.jar
commons-fileupload-1.2.1.jar
commons-io-1.3.2.jar
commons-lang-2.3.jar
commons-logging-1.1.jar
dom4j-1.6.1.jar
ejb3-persistence.jar
freemarker-2.3.13.jar
hibernate3.jar
hibernate-annotations.jar
hibernate-commons-annotations.jar
hibernate-validator.jar
hsqldb.jar
javassist-3.9.0.GA.jar
jta-1.1.jar
junit-3.8.1.jar
log4j-1.2.15.jar
ognl-2.6.11.jar
slf4j-api-1.5.8.jar
slf4j-log4j12-1.5.8.jar
struts2-convention-plugin-2.1.6.jar
struts2-core-2.1.6.jar
struts2-fullhibernatecore-plugin-1.4-GA.jar
xwork-2.1.2.jar

另外在struts2.xml的package中，要繼承hibernate-default如下:

<struts>
<package name="user" extends="hibernate-default">
<action name="saveOrUpdateUser" method="saveOrUpdate" class="web.UserAction">
<result name="success" type="redirect">listUser</result>
</action>
<action name="listUser" method="list" class="web.UserAction">
<result name="success">/register.jsp</result>
</action>
<action name="editUser" method="edit" class="web.UserAction">
<result name="success">/register.jsp</result>
</action>
<action name="deleteUser" method="delete" class="web.UserAction">
<result name="success" type="redirect">listUser</result>
</action>
</package>
</struts>

上面的struts.xml也透露出了struts2的另一個應用，DispatchAction functionality，
action可以指定到class內的method，而不一定是預設的execute method。

接下來我們設定hibernate.cfg.xml來做O/R Mapping，
hibernate.cfg.xml主要是設定資料庫連線資訊，如url、driver、帳密等，
並註明這個連線資訊mapping到那些object。

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test</property>
        <property name="hibernate.connection.username">root</property>
        <property name="connection.password">syscom</property>
        <property name="connection.pool_size">1</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="show_sql">true</property>
        <property name="hbm2ddl.auto">create</property>
        <mapping class="domain.User"/>
    </session-factory>
</hibernate-configuration>

這裡的mapping是直接mapping到一個User類別。

直接mapping到物件的話要使用annotation。使用Hibernate Annotations，
不需要使用HBM映射檔案，而是直接在POJO上使用Annotation設定對映關係，
如下的User.java:

package domain;

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity //說明這是一個映射物件
@Table(name = "USER") //mapping到那一個table
public class User implements Serializable {

  private Long id;
  private String name;
  private String gender;
  private String country;
  private String aboutYou;
  private Boolean mailingList;

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "USER_ID") //非必要，在欄位名稱與屬性名稱不同時使用
  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  @Column(name = "USER_NAME")
  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  @Column(name = "USER_GENDER")
  public String getGender() {
    return gender;
  }

  public void setGender(String gender) {
    this.gender = gender;
  }

  @Column(name = "USER_COUNTRY")
  public String getCountry() {
    return country;
  }

  public void setCountry(String country) {
    this.country = country;
  }

  @Column(name = "USER_ABOUT_YOU")
  public String getAboutYou() {
    return aboutYou;
  }

  public void setAboutYou(String aboutYou) {
    this.aboutYou = aboutYou;
  }

  @Column(name = "USER_MAILING_LIST")
  public Boolean getMailingList() {
    return mailingList;
  }

  public void setMailingList(Boolean mailingList) {
    this.mailingList = mailingList;
  }
}

所有的Annotation都是在javax.persistence這個package之下，這是標準的 JPA Annotations，
Hibernate實作了這些Annotations，其中id是個特殊的屬性，Hibernate會使用它來作為主鍵識別，你可以定義主鍵產生的方式，這邊設定為自動產生主鍵，依賴於資料庫自動增生主鍵的機制，也就是相當於在XML中設定native。

使用Hibernate另一個酷炫的好處是我們不用事先建立資料表，只要hibernate.cfg.xml設定檔
內的hbm2ddl.autocreate參數值設為create，基於JPA的概念，
Hibernate底層會幫我們完成這一切。自動建立的資料表如下:

這裡要特別注意的是，如果hbm2ddl.autocreate參數值設為create，則每次系統重啟時資料表
會被清空，如果要保留原本資料表內的資料，參數值則必須設為update。

接著我們回頭來看前面的struts.xml，
可以看出這是一個新增、刪除、修改使用者資料的範例，
接下來我們將這個範例檔補齊。
首先編寫前端的render頁面register.jsp:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@taglib uri="/struts-tags" prefix="s"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Registration Page</title>
<s:head />
<style type="text/css">
@import url(style.css);
</style>
</head>
<body>
<s:form action="saveOrUpdateUser">
<s:push value="user">
<s:hidden name="id" />
<s:textfield name="name" label="User Name" />
<s:radio name="gender" label="Gender" list="{'Male','Female'}" />
<s:select name="country" list="{'India','USA','UK'}" headerKey=""
headerValue="Select" label="Select a country" />
<s:textarea name="aboutYou" label="About You" />
<s:checkbox name="mailingList"
label="Would you like to join our mailing list?" />
<s:submit />
</s:push>
</s:form>

<s:if test="userList.size() > 0">
<div class="content">
<table class="userTable" cellpadding="5px">
<tr class="even">
<th>Name</th>
<th>Gender</th>
<th>Country</th>
<th>About You</th>
<th>Mailing List</th>
<th>Edit</th>
<th>Delete</th>
</tr>
<s:iterator value="userList" status="userStatus">
<tr
class="<s:if test="#userStatus.odd == true ">odd</s:if><s:else>even</s:else>">
<td><s:property value="name" /></td>
<td><s:property value="gender" /></td>
<td><s:property value="country" /></td>
<td><s:property value="aboutYou" /></td>
<td><s:property value="mailingList" /></td>
<td><s:url id="editURL" action="editUser">
<s:param name="id" value="%{id}"></s:param>
</s:url> <s:a href="%{editURL}">Edit</s:a></td>
<td><s:url id="deleteURL" action="deleteUser">
<s:param name="id" value="%{id}"></s:param>
</s:url> <s:a href="%{deleteURL}">Delete</s:a></td>
</tr>
</s:iterator>
</table>
</div>
</s:if>
</body>
</html>

接下來是後端的action物件，UserAction.java:

package web;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.struts2.ServletActionContext;

import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.opensymphony.xwork2.ModelDriven;
import dao.UserDAO;
import dao.UserDAOImpl;
import domain.User;

public class UserAction extends ActionSupport implements ModelDriven {

  private User user = new User();
  private List userList = new ArrayList();
  private UserDAO userDAO = new UserDAOImpl();

  @Override
  public User getModel() {
    return user;
  }

  public String saveOrUpdate() {
    userDAO.saveOrUpdateUser(user);
    return SUCCESS;
  }

  public String list() {
    userList = userDAO.listUser();
    return SUCCESS;
  }

  public String delete() {
    HttpServletRequest request =
      (HttpServletRequest) ActionContext.getContext().get(ServletActionContext.HTTP_REQUEST);
    userDAO.deleteUser(Long.parseLong(request.getParameter("id")));
    return SUCCESS;
  }

  public String edit() {
    HttpServletRequest request =
      (HttpServletRequest) ActionContext.getContext().get(ServletActionContext.HTTP_REQUEST);
    user = userDAO.listUserById(Long.parseLong(request.getParameter("id")));
    return SUCCESS;
  }

  public User getUser() {
    return user;
  }

  public void setUser(User user) {
    this.user = user;
  }

  public List getUserList() {
    return userList;
  }

  public void setUserList(List userList) {
    this.userList = userList;
  }
}

在action的method內是用一個userDao類別來封裝物件與db之間的transaction。
同時在這個action我們可以看到struts2的另一個特殊用法，ModelDriven Action，
繼承ModelDriven interface後，action invoke過程就會涉入modelDriven interceptor，
此時framework就可以自動傳送前端表單資料到所覆寫的getModel() method所回傳的物件。
UserDao.java

package dao;

import java.util.List;
import domain.User;

public interface UserDAO {
  public void saveOrUpdateUser(User user);
  public List listUser();
  public User listUserById(Long userId);
  public void deleteUser(Long userId);
}

UserDaoImpl.java

package dao;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.Transaction;

import com.googlecode.s2hibernate.struts2.plugin.annotations.SessionTarget;
import com.googlecode.s2hibernate.struts2.plugin.annotations.TransactionTarget;
import domain.User;

public class UserDAOImpl implements UserDAO {

  @SessionTarget
  Session session;
  @TransactionTarget
  Transaction transaction;

  @Override
  public void saveOrUpdateUser(User user) {
    try {
      session.saveOrUpdate(user);
    } catch (Exception e) {
      transaction.rollback();
      e.printStackTrace();
    }
  }

  @Override
  public void deleteUser(Long userId) {
    try {
      User user = (User) session.get(User.class, userId);
      session.delete(user);
    } catch (Exception e) {
      transaction.rollback();
      e.printStackTrace();
    }
  }

  @SuppressWarnings("unchecked")
  @Override
  public List listUser() {
    List courses = null;
    try {
      courses = session.createQuery("from User").list();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return courses;
  }

  @Override
  public User listUserById(Long userId) {
    User user = null;
    try {
      user = (User) session.get(User.class, userId);
    } catch (Exception e) {
      e.printStackTrace();
    }
    return user;
  }
}

UserDAOImpl是本篇另一個重點，它說明如何利用annotation來取得物件與db之間的
操控權session、transaction。
利用@SessionTarget、@TransactionTarget這兩個annotation可以從hibernate.cfg.xml設定檔中，取得相對應的session、transaction控制物件。

除了利用annotation來取得session外，我們也能用傳統的方式，編寫一個session factory，
這裡要注意的是，POJO類別(User.java)內如果是使用annotation來設定對映關係的話，
就必須要利用AnnotationConfiguration類別來取得SessionFactory。

下面我們編寫一個HibernateUtil class來專門取得SessionFactory。

package dao;

import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.SessionFactory;

public class HibernateUtil {
  private static final SessionFactory sessionFactory;

  static {
    try {
      sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
    } catch (Throwable ex) {
      System.err.println("Initial SessionFactory creation failed." + ex);
      throw new ExceptionInInitializerError(ex);
    }
  }

  public static SessionFactory getSessionFactory() {
    return sessionFactory;
  }
}

接著我們就能從SessionFactory內取得session及transaction操控物件。

下面就是整個範例的操作畫面: