#!/usr/bin/perl
use strict;
use warnings;
use Win32::ODBC;
use Spreadsheet::WriteExcel::Big;

# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel::Big->new("mssql.xls");

# Add a worksheet
my $worksheet = $workbook->addworksheet();

#  Add and define a format
my $header = $workbook->addformat(); # Add a format
my $format = $workbook->addformat();
$header->set_bg_color('yellow');
$header->set_border();
$format->set_border();

# dsn needs to be configured - see Data Sources (ODBC) -> User dsn
my $dsn = "s-atlas";
my $db1;

if (!($db1 = new Win32::ODBC($dsn))){
	print "Error connecting to $dsn\nError: " . Win32::ODBC::Error() . "\n";
	exit;
}

my $sql;
while (<>) {$sql .= $_}

if ($db1->Sql($sql)){
	print "db1 SQL failed.\nError: " . $db1->Error() . "\n";
	$db1->Close();
	exit;
}

my @data;
my @fields;
my $col = my $row = 0;

while ($db1->FetchRow()){
	$col = 0;
	@data = $db1->Data();
	if (!@fields){
		@fields = $db1->FieldNames();
		#print "$_ " for @fields, "\n";
		$worksheet->write($row, $col++, $_, $header) for @fields;
	}else{
		#print "$_ " for @data, "\n";
		$worksheet->write($row, $col++, $_, $format) for @data;
	}
	# catch >= 65535 rows in new worksheet
	if ($row == 65535){
		$worksheet = $workbook->addworksheet();
		$row = $col = 0;
		$worksheet->write($row, $col++, $_, $header) for @fields;
	}
	$row++;
}

##</code><code>##

for (@data) {
	chop;
	if ($_ =~ m/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/){
		print;
		$worksheet->write_number($row, $col++, $_, $format);
	}else{
		$worksheet->write($row, $col++, $_, $format)
	}
}

##</code><code>##

s/\0//g for @data;

##</code><code>##

y/\0// for @data;