Commit | Line | Data |
7909b81e |
1 | package SQL::Translator::Parser::Excel; |
19de1991 |
2 | |
3 | # ------------------------------------------------------------------- |
19de1991 |
4 | # Copyright (C) 2003 Ken Y. Clark <kclark@cpan.org>, |
5 | # darren chamberlain <darren@cpan.org>, |
7909b81e |
6 | # Chris Mungall <cjm@fruitfly.org>, |
7 | # Mike Mellilo <mmelillo@users.sourceforge.net> |
19de1991 |
8 | # |
9 | # This program is free software; you can redistribute it and/or |
10 | # modify it under the terms of the GNU General Public License as |
11 | # published by the Free Software Foundation; version 2. |
12 | # |
13 | # This program is distributed in the hope that it will be useful, but |
14 | # WITHOUT ANY WARRANTY; without even the implied warranty of |
15 | # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
16 | # General Public License for more details. |
17 | # |
18 | # You should have received a copy of the GNU General Public License |
19 | # along with this program; if not, write to the Free Software |
20 | # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA |
21 | # 02111-1307 USA |
22 | # ------------------------------------------------------------------- |
23 | |
24 | =head1 NAME |
25 | |
26 | SQL::Translator::Parser::Excel - parser for Excel |
27 | |
28 | =head1 SYNOPSIS |
29 | |
30 | use SQL::Translator; |
31 | use SQL::Translator::Parser::Excel; |
32 | |
33 | my $translator = SQL::Translator->new; |
34 | $translator->parser("SQL::Translator::Parser::Excel"); |
35 | |
36 | =head1 DESCRIPTION |
37 | |
7909b81e |
38 | Parses an Excel spreadsheet file for SQL::Translator. You can then |
39 | turn the data into a database tables or graphs. |
19de1991 |
40 | |
41 | =cut |
42 | |
43 | use strict; |
ab0aa010 |
44 | use vars qw($DEBUG $VERSION @EXPORT_OK); |
45 | $DEBUG = 0 unless defined $DEBUG; |
19de1991 |
46 | |
7909b81e |
47 | use Spreadsheet::ParseExcel; |
19de1991 |
48 | use Exporter; |
ab0aa010 |
49 | use SQL::Translator::Utils qw(debug normalize_name); |
50 | |
19de1991 |
51 | use base qw(Exporter); |
52 | |
53 | @EXPORT_OK = qw(parse); |
54 | |
8c7f5c7b |
55 | my %ET_to_ST = ( |
56 | 'Text' => 'VARCHAR', |
57 | 'Date' => 'DATETIME', |
58 | 'Numeric' => 'DOUBLE', |
59 | ); |
60 | |
19de1991 |
61 | # ------------------------------------------------------------------- |
ab0aa010 |
62 | # parse($tr, $data) |
63 | # |
64 | # Note that $data, in the case of this parser, is unuseful. |
65 | # Spreadsheet::ParseExcel works on files, not data streams. |
66 | # ------------------------------------------------------------------- |
19de1991 |
67 | sub parse { |
ab0aa010 |
68 | my ($tr, $data) = @_; |
8c7f5c7b |
69 | my $filename = $tr->filename || return; |
70 | my $wb = Spreadsheet::ParseExcel::Workbook->Parse( $filename ); |
71 | my $schema = $tr->schema; |
72 | my $table_no = 0; |
73 | |
8c7f5c7b |
74 | my $wb_count = $wb->{'SheetCount'} || 0; |
75 | for my $num ( 0 .. $wb_count - 1 ) { |
76 | $table_no++; |
77 | my $ws = $wb->Worksheet( $num ); |
78 | my $table_name = normalize_name( $ws->{'Name'} || "Table$table_no" ); |
ab0aa010 |
79 | |
80 | my @cols = $ws->ColRange; |
81 | next unless $cols[1] > 0; |
82 | |
8c7f5c7b |
83 | my $table = $schema->add_table( name => $table_name ); |
ab0aa010 |
84 | |
8c7f5c7b |
85 | for my $col ( $cols[0] .. $cols[1] ) { |
86 | my $cell = $ws->Cell(0, $col); |
87 | my $col_name = normalize_name( $cell->{'Val'} ); |
88 | my $data_type = ET_to_ST( $cell->{'Type'} ); |
89 | |
8c7f5c7b |
90 | my $field = $table->add_field( |
91 | name => $col_name, |
92 | data_type => $data_type, |
93 | default_value => '', |
94 | size => 255, |
95 | is_nullable => 1, |
96 | is_auto_increment => undef, |
97 | ) or die $table->error; |
98 | |
99 | if ( $col == 0 ) { |
100 | $table->primary_key( $field->name ); |
101 | $field->is_primary_key(1); |
ab0aa010 |
102 | } |
19de1991 |
103 | } |
104 | } |
105 | |
f62bd16c |
106 | return 1; |
ab0aa010 |
107 | } |
19de1991 |
108 | |
ab0aa010 |
109 | sub ET_to_ST { |
110 | my $et = shift; |
111 | $ET_to_ST{$et} || $ET_to_ST{'Text'}; |
19de1991 |
112 | } |
113 | |
114 | 1; |
115 | |
8c7f5c7b |
116 | # ------------------------------------------------------------------- |
117 | # Education is an admirable thing, |
118 | # but it is as well to remember that |
119 | # nothing that is worth knowing can be taught. |
120 | # Oscar Wilde |
121 | # ------------------------------------------------------------------- |
122 | |
19de1991 |
123 | =pod |
124 | |
7909b81e |
125 | =head1 AUTHORS |
19de1991 |
126 | |
7909b81e |
127 | Mike Mellilo <mmelillo@users.sourceforge.net>, |
ab0aa010 |
128 | darren chamberlain E<lt>dlc@users.sourceforge.netE<gt> |
7909b81e |
129 | Ken Y. Clark E<lt>kclark@cpan.orgE<gt> |
19de1991 |
130 | |
131 | =head1 SEE ALSO |
132 | |
133 | perl(1), Spreadsheet::ParseExcel. |
134 | |
135 | =cut |