Home > PHP > How to create a .csv which is Datev compatible

How to create a .csv which is Datev compatible

12. Juni 2009 PDF Version

require_once ‚Spreadsheet/Excel/Writer.php‘;

// Variable festlegen
$odbc = ’sy_xvol‘;
$user = ‚bla‘;
$pass =    ‚bla‘;
$umsatz = ‚Umsatz‘;
$datum = ‚Datum‘;
$date = date(„d.m.y“);

// mape generieren
$workbook = new Spreadsheet_Excel_Writer();

// header senden
$workbook->send(„Datev_AdHoc_($date).xls“);

// tabelle generieren
$worksheet =& $workbook->addWorksheet(„Datev_AdHoc_($date)“);

// individuelles format 1
$format_tableueberschrift =& $workbook->addFormat();
$format_tableueberschrift->setBold();
$format_tableueberschrift->setSize(14);

// individuelles format 1
$format_ueberschrift =& $workbook->addFormat();
$format_ueberschrift->setBold();
//$format_ueberschrift->setSize(8);

// Datumsformat-Formatieren
$date_format =& $workbook->addFormat();
$date_format->setNumFormat(‚ddmm‘);

// Tabellendaten (kopfzeile)
$worksheet->write(0, 0, ‚WKZ‘,$format_ueberschrift);
$worksheet->write(0, 1, ‚Soll/Haben‘,$format_ueberschrift);
$worksheet->write(0, 2, ‚Umsatz‘,$format_ueberschrift);
$worksheet->write(0, 3, ‚BU‘,$format_ueberschrift);
$worksheet->write(0, 4, ‚Gegenkonto‘,$format_ueberschrift);
$worksheet->write(0, 5, ‚Belegfeld1‘,$format_ueberschrift);
$worksheet->write(0, 6, ‚Belegfeld2‘,$format_ueberschrift);
$worksheet->write(0, 7, ‚Datum‘,$format_ueberschrift);
$worksheet->write(0, 8, ‚Konto‘,$format_ueberschrift);

// Datenbankverbindung
$conn = odbc_connect („$odbc“,“$user“,“$pass“);

// Tabelle einlesen und zeile für zeile in Tabelle

$qry = „select ‚Eur’as WKZ, ‚S‘ as ‚Soll/Haben‘, convert (char,co.invoice_net+co.vat,101)as ‚$umsatz‘,“ as BU, ‚4400‘ as Gegenkonto, co.order_ref as Belegfeld1,“ as Belegfeld2, convert (char, co.order_date,5)as ‚$datum‘, a.accounts_sys_ref as Konto from customer_order co, address a, organisation o

where co.order_date between convert(datetime,’$_POST[von_ah]‘)
and convert(datetime,’$_POST[bis_ah]‘)
and co.status_ref = 6
and co.customer_ref = o.organisation_ref
and co.invoice_status_ref = 9
and co.invoice_address_ref = a.address_ref
and co.order_type_ref in (1,3,7,11)
and co.vat not like ‚0.00‘
order by Belegfeld1″;

// Zurückgabe Query Result
$result = odbc_exec($conn,$qry);

$anz = 1;
while($row = odbc_fetch_array($result))
{
$worksheet->write($anz, 0, $row[‚WKZ‘]);
$worksheet->write($anz, 1, $row[‚Soll/Haben‘]);
$worksheet->write($anz, 2, number_format($row[$umsatz], 2, ‚,‘, “));
$worksheet->write($anz, 3, $row[‚BU‘]);
$worksheet->write($anz, 4, $row[‚Gegenkonto‘]);
$worksheet->write($anz, 5, $row[‚Belegfeld1‘]);
$worksheet->write($anz, 6, $row[‚Belegfeld2‘]);
$worksheet->write($anz, 7, preg_replace(‚/(d+)-(w+)-(d+)/i‘,’${1}${2}‘, $row[$datum]));
$worksheet->write($anz, 8, $row[‚Konto‘]);

$anz ++;
}

//Mappe senden
$workbook->close();

// Daten aus dem Speicher löschen
odbc_free_result($result);

// Close Connection
odbc_close($conn);

Categories: PHP Tags: , ,
Kommentare sind geschlossen