엑셀로 데이터 입력시 wr_id | wr_num wr_| parent 입력 문의
본문
엑셀로 데이터를 아래처럼 입력하고 있습니다.
그런데
wr_id | wr_num | wr_parent |
이부분을 자동으로 처리하고 싶은데 쉽게 방법이 있을까요?
<?
include_once("./common.php");
include_once("./head.sub.php");
set_time_limit ( 0 );
ini_set('memory_limit', '50M');
function only_number($n)
{
return preg_replace('/[^0-9]/', '', $n);
}
if($_FILES['excelfile']['tmp_name']) {
if(strlen($_POST['board_nm']) == 0){
alert('테이블명 누락');
}
$file = $_FILES['excelfile']['tmp_name'];
include_once(G5_LIB_PATH.'/Excel/reader.php');
$data = new Spreadsheet_Excel_Reader();
// Set output Encoding.
$data->setOutputEncoding('UTF-8');
/***
* if you want you can change 'iconv' to mb_convert_encoding:
* $data->setUTFEncoder('mb');
*
**/
/***
* By default rows & cols indeces start with 1
* For change initial index use:
* $data->setRowColOffset(0);
*
**/
/***
* Some function for formatting output.
* $data->setDefaultFormat('%.2f');
* setDefaultFormat - set format for columns with unknown formatting
*
* $data->setColumnFormat(4, '%.3f');
* setColumnFormat - set format for column (apply only to number fields)
*
**/
$data->read($file);
/*
$data->sheets[0]['numRows'] - count rows
$data->sheets[0]['numCols'] - count columns
$data->sheets[0]['cells'][$i][$j] - data from $i-row $j-column
$data->sheets[0]['cellsInfo'][$i][$j] - extended info about cell
$data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
if 'type' == "unknown" - use 'raw' value, because cell contain value with format '0.00';
$data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format
$data->sheets[0]['cellsInfo'][$i][$j]['colspan']
$data->sheets[0]['cellsInfo'][$i][$j]['rowspan']
*/
error_reporting(E_ALL ^ E_NOTICE);
$succ_count = 0;
$board_nm = $_POST['board_nm'];
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
if($i == 1){ // 1첫번째 열에 항목명을 컬럼명으로 테이블 만들기
$sql = " CREATE TABLE {$board_nm} (";
for($kk = 1; $kk < 80; $kk++){
$val1 = @trim(addslashes($data->sheets[0]['cells'][1][$kk]));
$jjum = "";
if(strlen(@trim(addslashes($data->sheets[0]['cells'][1][$kk+1]))) > 0){
$jjum = ",";
}
if(strlen($val1) > 0){
if($val1 == "strContent"){ // 컬럼중 text 타입으로 값이 많이 들어있는거 설정
$sql .= " `{$val1}` text NOT NULL {$jjum} ";
}else{
$sql .= " `{$val1}` varchar(255) NOT NULL {$jjum} ";
}
}
}
$sql .= ") DEFAULT CHARSET=utf8; ";
sql_query($sql);
}else{
$arr_fields = sql_field_names($board_nm);
$total_count++;
$j = 1;
$sql = " insert into {$board_nm} set ";
$zz = 0;
foreach($arr_fields as $line){
if( ($zz+1) == count($arr_fields)){
$sql .= " {$line} = '".addslashes($data->sheets[0]['cells'][$i][$j++])."' ";
}else{
$sql .= " {$line} = '".addslashes($data->sheets[0]['cells'][$i][$j++])."', ";
}
$zz++;
}
sql_query($sql);
//echo $sql."<BR>";
$succ_count++;
}
}
alert(number_format($succ_count)."개 insert 성공",G5_URL."/excel_upload.php");
/* */
}
?>
<div style="border:3px solid #000;padding:20px;max-width:700px;margin:50px auto 0 auto;">
<h1 style="margin-bottom:40px;">엑셀데이터를 업로드하여, 첫번째 열을 컬럼명으로 테이블 자동 생성 및 데이터 INSERT!!</h1>
<form name="frm" id="frm" method="post" enctype="multipart/form-data">
<input type="text" id="board_nm" name="board_nm" value="" class="frm_input" style='width:120px;' placeholder="만들어질 테이블명">
<label for="file">엑셀파일:</label> <input type="file" name="excelfile" id="excelfile" />
<p style="float:right;"><input type="submit" name="submit" value="DB업로드실행" class="btn1"/> <input type="submit" name="submit" value="창닫기" onclick="self.close();" class="btn1"/></p>
</form>
</div>
<?
include_once("./tail.sub.php");
?>
답변을 작성하시기 전에 로그인 해주세요.