日期:106年4月5日下午
地點:新北市教研中心
公文與計畫:
技術分享:
保太:校務系統匯出的學生XML檔案新增至資料庫 (使用 PHP)
學生XML檔案範例:
<?xml version="1.0" encoding="UTF-8"?>
<SCHOOL:report xmlns:SCHOOL="http://163.20.240.13">
<SCHOOL:table table_name="table0">
<SCHOOL:row roe_name="row0">
<SCHOOL:col col_name="col0">學號</SCHOOL:col>
<SCHOOL:col col_name="col1">姓名</SCHOOL:col>
<SCHOOL:col col_name="col2">年級</SCHOOL:col>
<SCHOOL:col col_name="col3">性別</SCHOOL:col>
<SCHOOL:col col_name="col4">生日</SCHOOL:col>
<SCHOOL:col col_name="col5">班級</SCHOOL:col>
<SCHOOL:col col_name="col6">座號</SCHOOL:col>
</SCHOOL:row>
<SCHOOL:row roe_name="row1">
<SCHOOL:col col_name="col0">102001</SCHOOL:col>
<SCHOOL:col col_name="col1">蔡承堯</SCHOOL:col>
<SCHOOL:col col_name="col2">1</SCHOOL:col>
<SCHOOL:col col_name="col3">男</SCHOOL:col>
<SCHOOL:col col_name="col4">20060910</SCHOOL:col>
<SCHOOL:col col_name="col5">01</SCHOOL:col>
<SCHOOL:col col_name="col6">01</SCHOOL:col>
</SCHOOL:row>
</SCHOOL:table>
</SCHOOL:report>
PHP 程式碼:
<?php
$dom = new DOMDocument('1.0', 'UTF-8');
$dom->load('students_esa.xml');
$students = $dom->getElementsByTagNameNS("http://163.20.240.13", "row");
foreach($students as $row){
$student = $row->getElementsByTagNameNS("http://163.20.240.13", "col");
if( $student->item(0)->nodeValue == '學號'){
continue;
}
$schoolno = $student->item(0)->nodeValue;
$studentName = $student->item(1)->nodeValue;
$classname = $student->item(2)->nodeValue . $student->item(5)->nodeValue;
$gender = $student->item(3)->nodeValue;
$birth = $student->item(4)->nodeValue;
if( strlen($birth) == 8 ){ // 生日為西元年
$birth = substr($birth,0,4) . "-" . substr($birth, 4,2) . "-" . substr($birth,6,2);
}else{ // 生日>非西元年
if( strlen($birth) == 6 ){
$birth = (substr($birth,0,2)+1911) . "-" . substr($birth, 2,2) . "-" . substr($birth,4,2);
}elseif( strlen($birth) == 7){
$birth = (substr($birth,0,3) +1911) . "-" . substr($birth, 3,2) . "-" . substr($birth,5,2);
}
}
$classno = $student->item(6)->nodeValue;
$sql_insert =<<<SQLSTRING
INSERT INTO user(
username,
cname,
classname,
classno
)
VALUES
(
'%s',
'%s',
'%s',
'%s'
)
SQLSTRING;
$conn = mysql_connect('192.168.11.100', 'bob', '!23456');
mysql_query('SET NAMES "UTF8" ');
mysql_selectdb('mydb', $conn);
if( mysql_query($sql_insert, $conn) ){
echo $studentName . ' 資料已新增!!<br />';
}else{
echo $studentName . ' 資料新增失敗!!<br />';
};
}
博文:匯入 XML 學生資料檔 (使用 Visual Studio)
伺服端程式:imageToBuffer
/// <summary>
/// 將 Byte 陣列轉換為 Image。
/// </summary>
/// <param name="Buffer">Byte 陣列。</param>
public static Image BufferToImage(byte[] Buffer)
{
if (Buffer == null || Buffer.Length == 0) { return null; }
byte[] data = null;
Image oImage = null;
Bitmap oBitmap = null;
//建立副本
data = (byte[])Buffer.Clone();
try
{
MemoryStream oMemoryStream = new MemoryStream(Buffer);
//設定資料流位置
oMemoryStream.Position = 0;
oImage = System.Drawing.Image.FromStream(oMemoryStream);
//建立副本
oBitmap = new Bitmap(oImage);
}
catch
{
throw;
}
//return oImage;
return oBitmap;
}
/// <summary>
/// 將 Image 轉換為 Byte 陣列。
/// </summary>
/// <param name="Image">Image 。</param>
/// <param name="imageFormat">指定影像格式。</param>
public static byte[] ImageToBuffer(Image Image, System.Drawing.Imaging.ImageFormat imageFormat)
{
if (Image == null) { return null; }
byte[] data = null;
using (MemoryStream oMemoryStream = new MemoryStream())
{
//建立副本
using (Bitmap oBitmap = new Bitmap(Image))
{
//儲存圖片到 MemoryStream 物件,並且指定儲存影像之格式
oBitmap.Save(oMemoryStream, imageFormat);
//設定資料流位置
oMemoryStream.Position = 0;
//設定 buffer 長度
data = new byte[oMemoryStream.Length];
//將資料寫入 buffer
oMemoryStream.Read(data, 0, Convert.ToInt32(oMemoryStream.Length));
//將所有緩衝區的資料寫入資料流
oMemoryStream.Flush();
}
}
return data;
}
客戶端程式: kendoUI_fileupload
//載入Picupload元件
$("#xml_file").kendoUpload({
template: kendo.template($('#fileTemplate').html()),
multiple: false,
async: {
saveUrl: "./ASHX/FileUpload.ashx",
removeUrl: "./ASHX/removeFile.ashx",
autoUpload: false
},
upload: PonUpload,
remove: PonRemove,
success: PonSuccess,
complete: PonComplete,
enabled: true,
validation: {
allowedExtensions: [".xml"]
}
});
function PonUpload(e) {
e.data = {
fcontrol_name: "uploadfile"
};
return e;
}
function PonRemove(e) {
e.data = {
fcontrol_name: "uploadfile"
};
return e;
}
function PonSuccess(e) {
}
function PonComplete(e) {
var grid = $("#st_grid").data("kendoGrid");
grid.dataSource.read();
}
//自動轉換儲存單位
function formatBytes(bytes, decimals) {
if (bytes == 0) return '0 Byte';
var k = 1000;
var dm = decimals + 1 || 3;
var sizes = ['Bytes', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB'];
var i = Math.floor(Math.log(bytes) / Math.log(k));
return parseFloat((bytes / Math.pow(k, i)).toFixed(dm)) + ' ' + sizes[i];
}
<script id="fileTemplate" type="text/x-kendo-template">
<div>
<table style="width:100%">
<tr>
<td>Name: #=name#</td>
<td>Size: #=formatBytes(size)#</td>
<%--<td>Extension: #=files[0].extension#</td>--%>
<td><button type='button' class='k-upload-action' style='position: absolute; top: 0; right: 0;'>上傳XML檔案</button></td>
</tr>
</table>
</div>
</script>
保太:Git 程式碼版控
學習資源: