Excel을 xml로 저장하기.

php의 엑셀 파서는 너무 후지다. 무슨 이유에선지 원본 파일의 내용과 파싱한 내용이 다르다.
다른 이름으로 저장을 해봤더니 정상적으로 읽어진다. 하지만 고객들에게 매번 저장을 새로 하라고 할 수는 없는 노릇이다.

perl로 테스트를 해 봤더니 원본도 이상없이 파싱이 된다. 흠 php엑셀 파서를 perl로 변경해야 할까?

#!/usr/bin/perl -w

    use strict;
    use Spreadsheet::ParseExcel;
    use Spreadsheet::ParseExcel::FmtUnicode;
    my $oExcel = new Spreadsheet::ParseExcel;
    my $oFmt = Spreadsheet::ParseExcel::FmtUnicode->new(Unicode_Map => "euc-kr");
    use XML::Excel;
    my $parser   = Spreadsheet::ParseExcel->new();
    my $excel_obj = XML::Excel->new({ParseExcel => $parser});

    my @arr_data;
    my $workbook = $parser->Parse('20090713101853_10002.XLS',$oFmt);

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

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

        for my $row ( $row_min .. $row_max ) {
            my @_row;
            for my $col ( $col_min .. $col_max ) {
                my $cell = $worksheet->get_cell( $row, $col );
                next unless $cell;
                push(@_row, $cell->value());

                #print "Row, Col    = ($row, $col)\n";
                #print "Value       = ", $cell->value(),       "\n";
                #print "Unformatted = ", $cell->unformatted(), "\n";
                print "#";
            }

            $arr_data[$row] = \@_row;
        }
    }
    $excel_obj->{column_data} = \@arr_data;
    $excel_obj->print_xml('aa.xml');

Linux에서 한글 excel 읽어 들이기

Linux에서 한글 excel을 읽으면 한글이 전부 깨진다...흠흠..

아래와 같은 방법으로 엑셀을 읽어 들이면 된다. 테스트 해보니 original과 변경된 값을 모두 볼 수 있음

훌륭함~~~

use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::FmtUnicode;               
my $oExcel = new Spreadsheet::ParseExcel;

my $oFmt = Spreadsheet::ParseExcel::FmtUnicode->new(Unicode_Map => "euc-kr");
my $oBook = $oExcel->Parse("kshsame.xls", $oFmt);

    my($iR, $iC, $oWkS, $oWkC);
    print "FILE  :", $oBook->{File} , "\n";
    print "COUNT :", $oBook->{SheetCount} , "\n";
    print "AUTHOR:", $oBook->{Author} , "\n";
    for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++) {
        $oWkS = $oBook->{Worksheet}[$iSheet];
        print "--------- SHEET:", $oWkS->{Name}, "\n";
        for(my $iR = $oWkS->{MinRow} ;
                defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) {
            for(my $iC = $oWkS->{MinCol} ;
                            defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++) {
                $oWkC = $oWkS->{Cells}[$iR][$iC];
                print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);  # Formatted Value
                print "( $iR , $iC ) =>", $oWkC->{Val}, "\n" if($oWkC);  # Original Value
            }
        }
    }

[visual basic] Excel 객체를 사용한 ExportToExcel Module

Public Function ExportToExcel(rs As Recordset, Optional FileName As String = "resultset.xls") As Boolean
    Dim ExcelSheet As Object
    Dim X As Excel.Worksheet
    'Dim cl As rdoColumn
    Dim col As Integer, row As Integer: col = 1: row = 1
    Dim I As Integer

    On Error GoTo Export_Err
    Screen.MousePointer = vbHourglass
    Set ExcelSheet = CreateObject("Excel.Sheet")
    Set X = ExcelSheet.Application.ActiveSheet
       
    X.cells(1, 1).Value = "just test 한글문제 해결되나?" ' cell의 내용 설정
    X.Name = "배송확인"  ' sheet의 이름 설정
   
    X.SaveAs App.Path & "\" & FileName
    X.Application.Quit
   
    Set ExcelSheet = Nothing
    Set X = Nothing
    Screen.MousePointer = vbDefault
    ExportToExcel = True
    Exit Function

Export_Err:

    ' code to display error or handle it
    Screen.MousePointer = vbDefault
    ExportToExcel = False
    Exit Function

End Function

Spreadsheet_Excel_Writer 에 대한 메뉴얼

출처: http://www.phpfreaks.com/pear_manual/page/package.fileformats.spreadsheet-excel-writer.html

그동안
$worksheet->write($i, $j, $this->get_data( $data, $key, $i ) );
를 사용했더니 오류가 발생함 예를 들어 번호가 길 경우 2E... 어쩌구 이런식으로 출력 됨

$worksheet->writeString($i, $j, $this->get_data( $data, $key, $i ) );
으로 수정하면 긴 숫자도 깨지지않고 정상적으로 출력됨

html의 확장자를 xls로 만들어 내릴 경우

mso format 에 대해서
date: 2007.4.4 15:16 - jk.

확장자를 xls로 만들어서 html로 data를 내릴 경우


<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
<xml>
<x:ExcelWorkbook>
  <x:ExcelWorksheets>
  <x:ExcelWorksheet>
   <x:Name>Sheet1</x:Name>
   <x:WorksheetOptions>
    <x:Selected/>
   </x:WorksheetOptions>
  </x:ExcelWorksheet>
  </x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>
</head>
<body>

## Misc
# Add a formula to a cell : <td x:num x:fmla="=B2+1"></td>
# Define a cell as Text   : <td style='mso-number-format:"\@"'>00123</td>
# Indent a row 2 columns  : <td colspan=2 style='mso-ignore:colspan'></td>

# perl sample
#!/usr/bin/perl
use strict;
use warnings;

sub add_xl_header    {
   return<<EOT;
<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
<xml>
<x:ExcelWorkbook>
  <x:ExcelWorksheets>
  <x:ExcelWorksheet>
   <x:Name>Sheet1</x:Name>
   <x:WorksheetOptions>
    <x:Selected/>
   </x:WorksheetOptions>
  </x:ExcelWorksheet>
  </x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>
</head>
<body>
EOT
}

sub add_xl_table    {
   defined(my $array_ref = shift) || return;
   my ($use_row_headers, $use_col_headers) = @_;
   my $table;
   my $max_width = 0;
   if ($use_row_headers)    {
       my $row = shift @$array_ref;
       $table .= "<tr>\n";
       $table .= "<th>$_</th>\n" for @$row;
       $table .= "</tr>\n";
   }
   for my $row (@$array_ref)    {
       $max_width = @$row if @$row > $max_width;
       $table .= "<tr>\n";
       $$row[0] = '<b>' . $$row[0] if $use_col_headers;
       $table .= "<td>$_</td>\n" for @$row;
       $table .= "</tr>\n";
   }
   $table = "<table>\n<col span=$max_width style='width:48pt'>\n$tabl
+e</table>\n";
   $table;
}

sub add_xl_separator    {
   my $sep_rows = shift || 1;
   return "<table><tr style='mso-xlrowspan:$sep_rows'></tr></table>\n
+";
}

sub add_xl_trailer    {
   return "</body>\n</html>\n";
}

## Misc
# Add a formula to a cell : <td x:num x:fmla="=B2+1"></td>
# Define a cell as Text   : <td style='mso-number-format:"\@"'>00123</
+td>
# Indent a row 2 columns  : <td colspan=2 style='mso-ignore:colspan'><
+/td>

my @test_array1 = ( [2, 3, 5] , [7, 11, 13] , [17, 19, 23] );
my @test_array2 = ( ['', 'Height', 'Siblings'], ['Joe', 77, 1], ['Fran
+k', 70, 4] );

print add_xl_header;
print add_xl_table \@test_array1;
print add_xl_separator 3;
print add_xl_table (\@test_array2, 1, 1);
print add_xl_trailer;

Excel로 전환

한글 테스트 해봐야 함









#!/usr/bin/perl -w
#
# Parse ace file and extract read names of destined contig 
#
# Written by Suk Namgoong
#

use strict;
use Spreadsheet::WriteExcel;
 

my $usage = "aceprimer.pl <ace file name>";

my $acefilename = shift or die $usage; 
my $workbook = Spreadsheet::WriteExcel->new($acefilename.".xls");
my ($contigfound, $fofname, $readname, $ctstart);
my $worksheet1 = $workbook->add_worksheet("forward"); 
my $worksheet2 = $workbook->add_worksheet("reverse");
my $format = $workbook->add_format();
$format->set_bold();
$format->set_bg_color(18);
$format->set_pattern(1);

my $align = $workbook->add_format();
$align->set_align('center');

my $align2 = $workbook->add_format();
$align2->set_align('vjustify'); 

$contigfound = 0; 
my %primerhash = ();


#
# Parse Ace File and put start postion and length of each reads into hash (%read_start and %read length))
#
$worksheet1->write(0,0,"Experiment ID", $format);
$worksheet1->write(0,1,"Contig Name", $format);
$worksheet1->write(0,2,"Primer Name", $format);
$worksheet1->write(0,3 ,"Sequence", $format);
$worksheet1->write(0,4,"Tm", $format);
$worksheet1->write(0,5,"Comment", $align2);
 
$worksheet2->write(0,0,"Experiment ID", $format);
$worksheet2->write(0,1,"Contig Name", $format);
$worksheet2->write(0,2,"Primer Name", $format);
$worksheet2->write(0,3,"Sequence", $format);
$worksheet2->write(0,4,"Tm", $format);
$worksheet2->write(0,5,"Comment", $align2);


my $frow = 1;
my $rrow = 1;
my $col = 0;
my $exp_no = 1;
$ctstart = 0;
open (FILEHANDLE, "<$acefilename");
my ($contigname, $primername, $sequence, $comment, $oligostart, $oligoend, $meltingTemp);
 
while (my $readline = <FILEHANDLE>) { 
            
	chomp($readline);
	if ($readline =~ m/^CT{/) 
	{
		$ctstart = 1;
		$comment = "";
		next;	
	} 
	if ($ctstart eq 1 ) {
	 	if ($readline =~ m/^(\S+) oligo consed (\S+) (\S+)/) {
	 		$contigname = $1;
			$oligostart = $2;
			$oligoend = $3;
	 		$ctstart = 2;
			next;
		} 
		else {
			$ctstart = 0;
			next;
		}
	}

	if ($ctstart eq 2) {
		if ($readline =~ m/^(\S+) (\S+) (\S+)/) {
			$primername = $1;
			$sequence = $2;
			$meltingTemp = $3;
			$ctstart = 3;
			next;
		}		
	} 
       
	if ($ctstart eq 3) {
		if ($readline =~ m/^COMMENT{/) {
			$ctstart = 4;
			next;
		}   
	}

	 if ($ctstart eq 4) {
		if ($readline =~ m/}$/) {

 			$ctstart = 0;

			if (exists($primerhash{$comment})) {
				$worksheet2->write($frow,0,$primerhash{$comment});
				$worksheet2->write($frow,1,$contigname);
				$worksheet2->write($frow,2,$primername);
				$worksheet2->write($frow,3,$sequence);
				$worksheet2->write($frow,4,$meltingTemp,$align);
				$worksheet2->write($frow,5,$comment, $align2);
				$frow++;
			}
			else {
				$primerhash{$comment} = $exp_no;
				$worksheet1->write($rrow,0,$exp_no);
				$worksheet1->write($rrow,1,$contigname);
				$worksheet1->write($rrow,2,$primername);
				$worksheet1->write($rrow,3,$sequence);
				$worksheet1->write($rrow,4,$meltingTemp,$align);
				$worksheet1->write($rrow,5,$comment, $align2);
				$exp_no++;
				$rrow++;
			}			
			next;		
		}
		else {
			$comment = $comment.$readline;
			next;
		}
	}
}	
close FILEHANDLE;