곰팅이의 열정 ::

104613

181

'excel'에 해당되는 글 5건

2008/04/08 Linux에서 한글 excel 읽어 들이기
2007/07/06 [visual basic] Excel 객체를 사용한 ExportToExcel Module
2007/05/19 Spreadsheet_Excel_Writer 에 대한 메뉴얼
2007/04/04 html의 확장자를 xls로 만들어 내릴 경우
2007/03/25 Excel로 전환

Linux에서 한글 excel 읽어 들이기

개발관련/Perl 2008/04/08 16:23

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
}
}
}

TAG eck-kr, excel, perl

받은 트랙백이 없고, 댓글이 없습니다.

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

개발관련/MS 계열 2007/07/06 21:05

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

TAG excel, vb

받은 트랙백이 없고, 댓글이 없습니다.

Spreadsheet_Excel_Writer 에 대한 메뉴얼

개발관련/PHP 2007/05/19 10:21

출처: 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 ) );
으로 수정하면 긴 숫자도 깨지지않고 정상적으로 출력됨

TAG excel, php

받은 트랙백이 없고, 댓글이 없습니다.

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

개발관련 2007/04/04 15:37

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;

TAG excel

받은 트랙백이 없고, 댓글이 없습니다.

Excel로 전환

개발관련/Perl 2007/03/25 07:41

한글 테스트 해봐야 함









#!/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;

TAG excel, perl

받은 트랙백이 없고, 댓글이 없습니다.