tools/NormalizeExcelXml.pl

   1 #!/usr/bin/perl -i
   2 use XML::Parser::PerlSAX;
   3
   4 die "usage: NormalizeExcelXml.pl <filename>\n" unless (@ARGV == 1);
   5
   6 $filename = $ARGV[0];
   7
   8 die "$filename not found\n" unless (-e $filename);
   9 die "$filename not writable\n" unless (-w $filename);
  10
  11 $backup = $filename . "~";
  12
  13 die "Could not rename $filename -> $backup\n" unless (rename ($filename, $backup));
  14
  15
  16 open (INFILE, "<$backup");
  17 open (OUTFILE, ">$filename");
  18
  19 select(OUTFILE);
  20
  21
  22 my $handler = MyHandler->new();
  23 my $parser = XML::Parser::PerlSAX->new( Handler => $handler );
  24
  25
  26 print "<?xml version=\"1.0\"?>";
  27 $parser->parse(Source => { ByteStream => \*INFILE });
  28
  29
  30
  31 package MyHandler;
  32
  33
  34 sub new {
  35                 my ($type) = @_;
  36                 my $self = {};
  37
  38                 $quiet_level = 0;
  39                 $opened = 0;
  40                 $indent_level = "";
  41                 $open_tag = "";
  42
  43                 $in_cell = 0;
  44
  45                 %bad_elements =
  46                 (
  47                                                 "NamedCell", 1,
  48                                                 "Styles", 1,
  49                 );
  50
  51                 %bad_attributes =
  52                 (
  53                                                 "ss:StyleID", 1,
  54                                                 "ss:AutoFitWidth", 1,
  55                 );
  56
  57                 return bless ($self, $type);
  58 }
  59
  60 sub start_element
  61 {
  62                 my ($self, $element) = @_;
  63                 my $name = $element->{Name};
  64
  65                 if (exists $bad_elements{$name})
  66                 {
  67                         $quiet_level += 1;
  68                 }
  69
  70                 if ($quiet_level == 0)
  71                 {
  72                         if (!$open_tag eq "")
  73                         {
  74                                 print ">";
  75                         }
  76
  77                         if (! $in_cell )
  78                         {
  79                                 print "\n$indent_level";
  80                         }
  81
  82                         print "<$name";
  83
  84                         foreach $z (keys %{$element->{Attributes}})
  85                         {
  86                                 if (!exists $bad_attributes{$z})
  87                                 {
  88
  89                                         print " " . $z . '="';
  90
  91                                         $_ = $element->{Attributes}->{$z};
  92
  93                                         s/\"/&quot;/g;
  94                                         s/</&lt;/g;
  95                                         s/>/&gt;/g;
  96
  97                                         print $_;
  98
  99                                         print '"';
 100
 101                                 }
 102                         }
 103
 104                         $open_tag = $name;
 105
 106                         #$indent_level = $indent_level . ' ';
 107                         $opened = 1;
 108
 109                         if ($name eq "Cell")
 110                         {
 111                                 $in_cell = 1;
 112                         }
 113                 }
 114 }
 115
 116
 117 sub end_element {
 118                 my ($self, $element) = @_;
 119                 my $name = $element->{Name};
 120
 121                 if ($quiet_level == 0)
 122                 {
 123                         #chop $indent_level;
 124
 125                         if ($open_tag eq $name)
 126                         {
 127                                 print "/>";
 128                         }
 129                         else
 130                         {
 131                                 if (!$opened && !$in_cell)
 132                                 {
 133                                         print "\n$indent_level";
 134                                 }
 135
 136                                 print "</$name>";
 137                         }
 138
 139                         $open_tag = "";
 140                         $opened = 0;
 141                 }
 142
 143                 if ($name eq "Cell")
 144                 {
 145                         $in_cell = 0;
 146                 }
 147
 148                 if (exists $bad_elements{$name})
 149                 {
 150                         $quiet_level -= 1;
 151                 }
 152 }
 153
 154 sub characters {
 155                 my ($self, $characters) = @_;
 156
 157                 $_ = $characters->{Data};
 158                 chomp;
 159
 160                 if (/\S/)
 161                 {
 162                         s/\"/&quot;/g;
 163                         s/</&lt;/g;
 164                         s/>/&gt;/g;
 165
 166                         if (!$open_tag eq "")
 167                         {
 168                                 print ">";
 169                                 $open_tag = "";
 170                         }
 171
 172                         print $_;
 173                 }
 174 }