菜鸟perl，教你写程序利用excel中表结构生成SQL语句。

如上图显示，在公司每当起一个新项目时，都需要分析一些常用字段，及字段类型、字段长度。那分析完后，要需要对数据库进行相应的建表操作了，当然现在有很多工具，比如：powerdesin、ERwin之类的建模工具能省任，那比如公司给提供了一个类似excel这类的数据，你不就SB了吗？你肯定心里面想,F**k。

但是，没事的，我来啦。走起~

使用Perl脚本，对excel进行读取操作，然后根据相应的格式，生成对应的SQL语句。（此perl只适用此excel）

注：以下程序写的很不智能，如果您有好想法，请留言。

#!/usr/bin/perl -w

#use strict;

use Encode;

use Spreadsheet::ParseExcel::FmtUnicode;

use Spreadsheet::ParseExcel;

use POSIX qw(strftime);

use Sys::Hostname;

use Socket; 

 

my $parser = Spreadsheet::ParseExcel->new();

my $formatter = Spreadsheet::ParseExcel::FmtUnicode->new(Unicode_Map=>"cp936");#设置字符编码



open (DATA,">D:/14-perl/excel/create_table.sql"); 

my $workbook = $parser->Parse('D:/14-perl/excel/table_column.xls',$formatter);

my %hash_col;

my %hash_cre;

my %hash_comm;

my $rn=0;



   

for my $worksheet ( $workbook->worksheets() ) {

	my ( $row_min, $row_max ) = $worksheet->row_range();

    #my ( $col_min, $col_max ) = $worksheet->col_range();

   

   my $rn=$row_min+1;

for ($row_min .. $row_max) {

	

my $cell0 = $worksheet->get_cell( $rn, 0 );next unless $cell0; $a0=$cell0->value();$a0=encode('utf8',decode('gbk',$a0));

my $cell1 = $worksheet->get_cell( $rn, 1 );next unless $cell1; $a1=$cell1->value();$a1=encode('utf8',decode('gbk',$a1));

my $cell2 = $worksheet->get_cell( $rn, 2 );next unless $cell2; $a2=$cell2->value();$a2=encode('utf8',decode('gbk',$a2));

my $cell3 = $worksheet->get_cell( $rn, 3 );next unless $cell3; $a3=$cell3->value();$a3=encode('utf8',decode('gbk',$a3));

my $cell4 = $worksheet->get_cell( $rn, 4 );next unless $cell4; $a4=$cell4->value();$a4=encode('utf8',decode('gbk',$a4));

my $cell5 = $worksheet->get_cell( $rn, 5 );next unless $cell5; $a5=$cell5->value();$a5=encode('utf8',decode('gbk',$a5));

my $cell6 = $worksheet->get_cell( $rn, 6 );next unless $cell6; $a6=$cell6->value();$a6=encode('utf8',decode('gbk',$a6));

my $cell7 = $worksheet->get_cell( $rn, 7 );next unless $cell7; $a7=$cell7->value();$a7=encode('utf8',decode('gbk',$a7));

my $cell8 = $worksheet->get_cell( $rn, 8 );next unless $cell8; $a8=$cell8->value();$a8=encode('utf8',decode('gbk',$a8));





 $hash_col{"\"$a0\".$a2"} .="COMMENT ON COLUMN \"$a0\".$a2.$a4 is '$a3';\n";

 $hash_comm{"\"$a0\".$a2"} ="$a1";

  

if ($a5 eq 'Y') {$a5='PRIMARY KEY'; $a5flag='Y'} else {$a5='';$a5flag='N'}

if ($a7 eq 'N') {if ($a5flag eq 'Y') {$a7='';} else{$a7='NOT NULL';}} else {$a7='';}

$hash_cre{"\"$a0\".$a2"} .="    $a4 $a6 $a5 $a7,";



	$rn +=1;

}



	}  

	





my $datetime=strftime("%Y-%m-%d %H:%M:%S\n", localtime(time));

my $name; 

my $aliases; 

my $type; 

my $len; 

my @thisaddr; 

my $host = hostname(); 

($name,$aliases,$type,$len,@thisaddr)=gethostbyname($host);

my $str=inet_ntoa($thisaddr[2]);

       

print DATA "spool SDATA_DDL.log\n";

print DATA "--***********************************************************************\n";

print DATA "-- XXX数据库建表语句\n";

print DATA "-- author: $str\n";

print DATA "-- date: $datetime";

print DATA "--***********************************************************************\n";



 foreach $key (sort keys  %hash_cre) {

	 	   $_= "drop table $key;\n create table $key (\n".$hash_cre{$key}.");\n\n"."COMMENT ON TABLE $key is '".$hash_comm{$key}."';\n".$hash_col{$key}."\n";

	 	    ##$hash_cre{$key};

	 	  s/\,\);/);/g;

	 	  s/\, /,\n/g;

	 	      print DATA  "$_\n";

	  }



 

print DATA "spool off\n";



=pod excel 模板

主题名称	表名称	表英文名	字段中文名	字段英文名	是否主键	字段类型	是否可空	是否视图

SDATA	预算权限项目机构关联表	BU_AUTHORITYPROJECTAGENCY	ID	ID	Y	NUMBER	N	N

SDATA	预算权限项目机构关联表	BU_AUTHORITYPROJECTAGENCY	预算权限ID	AUTHORITYID		NUMBER	N	N

SDATA	预算权限项目机构关联表	BU_AUTHORITYPROJECTAGENCY	预算项目ID	PROJECTID		NUMBER	N	N

=cut

如下是想要的结果：

spool SDATA_DDL.log

--***********************************************************************

-- XXX数据库建表语句

-- author: 192.168.1.215

-- date: 2017-01-04 17:10:02

--***********************************************************************

drop table "SDATA".BUD_BUDGET;

 create table "SDATA".BUD_BUDGET (

    ID NUMBER PRIMARY KEY ,

   BUDGETCODE VARCHAR2(30)  NOT NULL,

   UNITID NUMBER  NOT NULL,

   INFLOWAMOUNT NUMBER(21,2)  ,

   OUTFLOWAMOUNT NUMBER(21,2)  ,

   PLANDATE DATE  ,

   REPORTDATE DATE  ,

   CURRENCYCODE VARCHAR2(20)  ,

   MEMO VARCHAR2(1000)  ,

   PECUNIARYUNIT VARCHAR2(20)  ,

   OPERATIONSTATUS NUMBER  ,

   INPUTUSERID NUMBER  ,

   INPUTDATE DATE  ,

   MODIFYUSERID NUMBER  ,

   MODIFYDATE DATE  ,

   STATUSID NUMBER  ,

   DATAVERSION NUMBER  NOT NULL);



COMMENT ON TABLE "SDATA".BUD_BUDGET is '日预算表';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.ID is 'ID';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.BUDGETCODE is '预算编号';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.UNITID is '预算单位ID';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.INFLOWAMOUNT is '流入金额';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.OUTFLOWAMOUNT is '流出金额';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.PLANDATE is '计划日期';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.REPORTDATE is '计划上报日期';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.CURRENCYCODE is '币种';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.MEMO is '计划说明';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.PECUNIARYUNIT is '金额单位';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.OPERATIONSTATUS is '业务状态';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.INPUTUSERID is '录入人';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.INPUTDATE is '录入日期';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.MODIFYUSERID is '修改人';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.MODIFYDATE is '修改日期';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.STATUSID is '数据状态';

COMMENT ON COLUMN "SDATA".BUD_BUDGET.DATAVERSION is '数据版本';





drop table "SDATA".BUD_BUDGETDETAIL;

 create table "SDATA".BUD_BUDGETDETAIL (

    ID NUMBER PRIMARY KEY ,

   DAYBUDGETID NUMBER  NOT NULL,

   PLANAMOUNT NUMBER(21,2)  ,

   PROJECTCODE VARCHAR2(200)  ,

   PROJECTNAME VARCHAR2(300)  ,

   PROJECTATTRIBUTE NUMBER  ,

………………………………