/**
* Genera un reporte en Excel
*
* @param array $result
* @param array $sumArray
* @param string $title
* @param array $weightArray
* @param array $headerArray
*/
function xls($result, $sumArray, $title, $weightArray, $headerArray)
{
error_reporting(0);
$file = md5(uniqid());
$config = Config::read('config');
$active_app = Router::get_application();
$workbook = new Spreadsheet_Excel_Writer("public/temp/{$file}.xls");
$worksheet =& $workbook->addWorksheet();
$titulo_verdana =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 20));
$titulo_verdana2 =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 18));
$workbook->setCustomColor(12, 0xf2, 0xf2, 0xf2);
$column_title =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 12, 'fgcolor' => 12, 'border' => 1, 'bordercolor' => 'black', "halign" => 'center'));
$column =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 11, 'border' => 1, 'bordercolor' => 'black'));
$column_centered =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 11, 'border' => 1, 'bordercolor' => 'black', "halign" => 'center'));
$worksheet->write(0, 0, strtoupper($config->{$active_app}->name), $titulo_verdana);
$worksheet->write(1, 0, "REPORTE DE " . strtoupper($title), $titulo_verdana2);
$worksheet->write(2, 0, "FECHA " . date("Y-m-d"), $titulo_verdana2);
for ($i = 0; $i <= count($headerArray) - 1; $i++) {
$worksheet->setColumn($i, $i, $weightArray[$i]);
$worksheet->write(4, $i, $headerArray[$i], $column_title);
}
$l = 5;
foreach ($result as $row) {
for ($i = 0; $i <= count($row) - 1; $i++) {
if (!is_numeric($row[$i])) {
$worksheet->writeString($l, $i, $row[$i], $column);
} else {
$worksheet->writeString($l, $i, $row[$i], $column_centered);
}
}
$l++;
}
$workbook->close();
error_reporting(E_ALL ^ E_STRICT);
if (isset($raw_output)) {
print "<script type='text/javascript'> window.open('" . KUMBIA_PATH . "temp/" . $file . ".xls', null); </script>";
} else {
Generator::forms_print("<script type='text/javascript'> window.open('" . KUMBIA_PATH . "temp/" . $file . ".xls', null); </script>");
}
}
function save_detail_excel($sql, $file_name, $file_type = "xls")
{
global $db_conn;
if ($sql != "") {
set_time_limit(0);
ini_set('memory_limit', '600M');
$rows = mysqli_query($db_conn, $sql);
$row_counts_list = mysqli_num_rows($rows);
$field_count = mysqli_num_fields($rows);
$fields = mysqli_fetch_fields($rows);
if ($row_counts_list != 0) {
//文档类型
if ($file_type == "xls") {
//记录集个数小于2000，使用phpexcel生成xls
/*if ($row_counts_list<2000 and $field_count<12) {
include("../plugin/excel.php");
//当前活动表
$objPHPExcel->setActiveSheetIndex(0);
//sheet表名
$objPHPExcel->getActiveSheet()->setTitle("".$file_name."");
$field_count2=$field_count-1;
$objPHPExcel->getActiveSheet()->setSharedStyle($excel_tit_left, "A1:$tit_arr[$field_count2]1");
$objPHPExcel->getActiveSheet()->setSharedStyle($excel_list_left, "A2:$tit_arr[$field_count2]".($row_counts_list+1));
for ($i=0;$i<$field_count;$i++){
$objPHPExcel->getActiveSheet()->setCellValue("$tit_arr[$i]1","".$fields[$i]->name."");
}
$i=1;
while($rs= mysqli_fetch_array($rows)){
$i+=1;
for ($k=0;$k<$field_count;$k++){
$objPHPExcel->getActiveSheet()->getCell($tit_arr[$k].$i)->setValueExplicit($rs[$k], PHPExcel_Cell_DataType::TYPE_STRING);
}
//if($i%100==0){
// ob_flush();
// flush();
//}
}
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$file_path=excel_file("".$file_name."_","xls");
$objWriter->save(str_replace('.php', '.xls',$file_path[2]));
}else{*/
//记录集个数大于2000，使用XML生成xls
include "Spreadsheet/Excel/Writer.php";
$file_path = excel_file("" . $file_name . "_", "xls");
$workbook = new Spreadsheet_Excel_Writer($file_path[2]);
$workbook->setCustomColor(20, 146, 208, 80);
$format_title_sty =& $workbook->addformat(array('Size' => 10, 'Bold' => 1, 'Border' => 1, 'FgColor' => 20, 'FontFamily' => utf82gb("宋体")));
$format_cont_sty =& $workbook->addformat(array('Size' => 10, 'Border' => 1, 'FontFamily' => utf82gb("宋体")));
$sheet_rows = 40000;
//单Sheet记录集个数
$sheet_id = 0;
$i = 0;
$sheet_i = 0;
while ($rs = mysqli_fetch_array($rows)) {
$i++;
$sheet_i++;
$row_ = $i % $sheet_rows;
if ($row_ == 1) {
if ($row_counts_list < $sheet_rows + 1) {
$sheet_id = "";
} else {
$sheet_id++;
}
$sheets =& $workbook->addWorksheet("" . utf82gb($file_name . $sheet_id) . "");
for ($f = 0; $f < $field_count; $f++) {
$sheets->writeString(0, $f, utf82gb($fields[$f]->name), $format_title_sty);
}
$sheet_i = 1;
}
for ($k = 0; $k < $field_count; $k++) {
$sheets->writeString($sheet_i, $k, utf82gb($rs[$k]), $format_cont_sty);
}
}
$workbook->close();
//}
} elseif ($file_type == "xml_xls") {
$file_type = "xls";
$file_path = excel_file("" . $file_name . "_", $file_type);
$fp = fopen($file_path[2], "w");
$sheet_rows = 40000;
//单Sheet记录集个数
$xml_head = "<?xml version=\"1.0\"?>\n<?mso-application progid=\"Excel.Sheet\"?>\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\"><DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\"><Title>详单数据导出</Title><Author>亚铭科技</Author><Category>详单、汇总</Category>\n<Company>亚铭科技</Company></DocumentProperties>\n<OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\"><AllowPNG/> <RemovePersonalInformation/></OfficeDocumentSettings><ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\"> <WindowHeight>8010</WindowHeight><WindowWidth>14805</WindowWidth><WindowTopX>240</WindowTopX><WindowTopY>105</WindowTopY>\n<ProtectStructure>False</ProtectStructure><ProtectWindows>False</ProtectWindows></ExcelWorkbook>\n<Styles>\n<Style ss:ID=\"Default\" ss:Name=\"Normal\"><Alignment ss:Vertical=\"Bottom\"/><Borders/><Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"10\" ss:Color=\"#000000\"/><Interior/><NumberFormat/><Protection/></Style><Style ss:ID=\"s71\"><Alignment ss:Vertical=\"Center\"/><Borders><Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/></Borders><Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Color=\"#000000\"/><NumberFormat ss:Format=\"@\"/></Style><Style ss:ID=\"s72\"><Alignment ss:Vertical=\"Center\"/><Borders>\n<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\n <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/></Borders><Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Color=\"#000000\" ss:Bold=\"1\"/> <Interior ss:Color=\"#92D050\" ss:Pattern=\"Solid\"/><NumberFormat ss:Format=\"@\"/></Style></Styles>";
fwrite($fp, $xml_head);
for ($i = 0; $i < $field_count; $i++) {
$filed_list .= "<Cell ss:StyleID=\"s72\"><Data ss:Type=\"String\">" . $fields[$i]->name . "</Data></Cell>";
}
$xml_sheet_head_row = "<Row>" . ($filed_list .= "</Row>");
$i = 0;
$sheet_id = 0;
while ($rs = mysqli_fetch_array($rows)) {
$i++;
$style = "";
$f_list = "";
$row_ = $i % $sheet_rows;
if ($row_ == 1) {
if ($row_counts_list < $sheet_rows + 1) {
$sheet_id = "";
} else {
$sheet_id++;
}
fwrite($fp, "\n<Worksheet ss:Name=\"" . $file_name . $sheet_id . "\">\n<Table x:FullRows=\"1\" ss:DefaultRowHeight=\"15\">" . $xml_sheet_head_row);
ob_flush();
flush();
}
for ($k = 0; $k < $field_count; $k++) {
$f_list .= "<Cell ss:StyleID=\"s71\"><Data ss:Type=\"String\">" . $rs[$k] . "</Data></Cell>";
}
fwrite($fp, "<Row>" . $f_list . "</Row>");
if ($row_ == 0) {
fwrite($fp, "</Table>\n<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\"><Unsynced/><Print><ValidPrinterInfo/>\n<PaperSizeIndex>9</PaperSizeIndex>\n<HorizontalResolution>-3</HorizontalResolution><VerticalResolution>0</VerticalResolution></Print><Selected/><Panes><Pane><Number>3</Number><ActiveCol>1</ActiveCol></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions></Worksheet>\n\n");
}
}
if ($row_ < $sheet_rows) {
fwrite($fp, "</Table>\n<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\"><Unsynced/><Print><ValidPrinterInfo/>\n<PaperSizeIndex>9</PaperSizeIndex>\n<HorizontalResolution>-3</HorizontalResolution><VerticalResolution>0</VerticalResolution></Print><Selected/><Panes><Pane><Number>3</Number><ActiveCol>1</ActiveCol></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions></Worksheet>\n\n");
}
fwrite($fp, "</Workbook>");
fclose($fp);
unset($xml_head);
unset($filed_list);
unset($f_list);
} elseif ($file_type == "csv") {
//生成Csv文档
$file_path = excel_file("" . $file_name . "_", $file_type);
$fp = fopen($file_path[2], "w");
for ($i = 0; $i < $field_count; $i++) {
$filed_list .= $fields[$i]->name . ",";
}
fwrite($fp, utf82gb($filed_list) . "\n");
$i = 1;
while ($rs = mysqli_fetch_array($rows)) {
$f_list = "";
$i++;
for ($k = 0; $k < $field_count; $k++) {
$f_list .= $rs[$k] . ",";
}
$row_list = $f_list . "\n";
fwrite($fp, utf82gb($row_list));
if ($i % 100 == 0) {
ob_flush();
flush();
}
}
fclose($fp);
unset($f_list);
unset($row_list);
} elseif ($file_type == "txt_n") {
$file_path = excel_file("" . $file_name . "_", "txt");
$fp = fopen($file_path[2], "w");
$filed_list = "C011|130002|861300005101|dxhbfc|客户姓名|客户姓别|客户生日|客户手机号|销售日期|0|职业";
fwrite($fp, utf82gb($filed_list) . "\r\n");
$i = 1;
while ($rs = mysqli_fetch_array($rows)) {
$f_list = "";
$i++;
for ($k = 0; $k < $field_count; $k++) {
$f_list .= $rs[$k] . "|";
}
$row_list = substr($f_list, 0, -1) . "\r\n";
fwrite($fp, utf82gb($row_list));
if ($i % 100 == 0) {
ob_flush();
flush();
}
}
fclose($fp);
unset($f_list);
unset($row_list);
} else {
//生成TXT文档
$file_path = excel_file("" . $file_name . "_", $file_type);
$fp = fopen($file_path[2], "w");
for ($i = 0; $i < $field_count; $i++) {
$filed_list .= $fields[$i]->name . "\t";
}
fwrite($fp, utf82gb($filed_list) . "\r\n");
$i = 1;
while ($rs = mysqli_fetch_array($rows)) {
$f_list = "";
$i++;
for ($k = 0; $k < $field_count; $k++) {
$f_list .= $rs[$k] . "\t";
}
$row_list = $f_list . "\r\n";
fwrite($fp, utf82gb($row_list));
if ($i % 100 == 0) {
ob_flush();
flush();
}
}
fclose($fp);
unset($f_list);
unset($row_list);
}
$do_res = array("counts" => "1", "file_path" => gb2utf8(str_replace("./data/", "/data/", $file_path[1])), "file_name" => gb2utf8($file_path[0]), "des" => "文件导出完成，请点击下载！");
} else {
$do_res = array("counts" => "0", "file_path" => "", "file_name" => "", "des" => "未找到符合条件的数据...");
}
} else {
$do_res = array("counts" => "-1", "file_path" => "", "file_name" => "", "des" => "数据查询条件有误，请检查后重试...");
}
return $do_res;
mysqli_free_result($rows);
}
function ExportToExcel($fileName, $sheetName, $rst)
{
$columnHeaders = array();
/* Creating a workbook */
$workbook = new Spreadsheet_Excel_Writer($fileName);
/* Creating a worksheet */
$worksheet =& $workbook->addWorksheet($sheetName);
$worksheet1 =& $workbook->addWorksheet("REG");
$worksheet2 =& $workbook->addWorksheet("MGT");
$worksheet3 =& $workbook->addWorksheet("RET");
$worksheet4 =& $workbook->addWorksheet("CNS");
$monthArray = array("01" => "Jan", "02" => "Feb", "03" => "Mar", "04" => "Apr", "05" => "May", "06" => "Jun", "07" => "Jul", "08" => "Aug", "09" => "Sep", "10" => "Oct", "11" => "Nov", "12" => "Dec");
$row = 0;
$col = 0;
$rowREG = 0;
$rowMGT = 0;
$rowRET = 0;
$rowCNS = 0;
$colREG = 0;
$colMGT = 0;
$colRET = 0;
$colCNS = 0;
//Set color
//setBgColor
$workbook->setCustomColor(12, 255, 151, 95);
$format =& $workbook->addFormat(array('Align' => 'center', 'Bold' => 1, 'Border' => 1));
$format->setFontFamily('Trebuchet MS');
$format->setFgColor(12);
$formatNormal =& $workbook->addFormat(array('Border' => 1, 'Align' => 'left'));
$formatNormal->setFontFamily('Trebuchet MS');
/* Freeze Panes */
$array = array(1, 0, 1, 6);
$worksheet->freezePanes($array);
$row = 0;
$col = 0;
if ($rst && !$rst->EOF) {
$columnHeaders = $rst->FieldTypesArray();
/* The actual data */
for ($i = 0; $i < count($columnHeaders); $i++) {
$worksheet->write($row, $col, $columnHeaders[$i]->name, $format);
$worksheet1->write($row, $col, $columnHeaders[$i]->name, $format);
$worksheet2->write($row, $col, $columnHeaders[$i]->name, $format);
$worksheet3->write($row, $col, $columnHeaders[$i]->name, $format);
$worksheet4->write($row, $col, $columnHeaders[$i]->name, $format);
$worksheet->setColumn($i, $i, 20);
$worksheet1->setColumn($i, $i, 20);
$worksheet2->setColumn($i, $i, 20);
$worksheet3->setColumn($i, $i, 20);
$worksheet4->setColumn($i, $i, 20);
$col++;
}
$rowREG++;
$rowMGT++;
$rowRET++;
$rowCNS++;
while (!$rst->EOF) {
$row++;
$col = 0;
$colREG = 0;
$colMGT = 0;
$colRET = 0;
$colCNS = 0;
$flag1 = 0;
$flag2 = 0;
$flag3 = 0;
$flag4 = 0;
$empNum = array();
for ($i = 0; $i < count($columnHeaders); $i++) {
$value = $rst->fields[$i];
$value2 = $value;
if (!isset($value) or $value == "") {
$worksheet->write($row, $col++, "", $formatNormal);
$value2 = "";
} else {
if (eregi("(<a href.*>)(.*)(</a>)", $value, $regs)) {
$worksheet->write($row, $col++, $regs[2], $formatNormal);
$value2 = $regs[2];
} else {
if (eregi("(<font.*>)(.*)(</font>)", $value, $regs)) {
$worksheet->write($row, $col++, $regs[2], $formatNormal);
$value2 = $regs[2];
} else {
if (ereg("([0-9]{2})(/)([0-9]{2})(/)([0-9]{4})", $value, $regs2)) {
$dateValue = $regs2[1] . "-" . $monthArray[$regs2[3]] . "-" . $regs2[5];
$worksheet->write($row, $col++, $dateValue, $formatNormal);
} else {
$worksheet->write($row, $col++, $value, $formatNormal);
}
}
}
if ($i == 0 && eregi("([a-zA-Z]{3})(.*)", $value2, $empNum)) {
$value2 = $empNum[2];
}
}
if ($empNum[1] == "REG") {
$worksheet1->write($rowREG, $colREG++, $value2, $formatNormal);
$flag1 = 1;
}
if ($empNum[1] == "MGT") {
$worksheet2->write($rowMGT, $colMGT++, $value2, $formatNormal);
$flag2 = 1;
}
if ($empNum[1] == "RET") {
$worksheet3->write($rowRET, $colRET++, $value2, $formatNormal);
$flag3 = 1;
}
if ($empNum[1] == "CNS") {
$worksheet4->write($rowCNS, $colCNS++, $value2, $formatNormal);
$flag4 = 1;
}
}
$rst->MoveNext();
if ($flag1) {
$rowREG++;
}
if ($flag2) {
$rowMGT++;
}
if ($flag3) {
$rowRET++;
}
if ($flag4) {
$rowCNS++;
}
}
$rst->close();
}
// Let's send the file
$workbook->close();
}
$column_width[0] = "30%";
$column_width = array_fill(1, 3, "20%");
//Querys
// kolommen en waarden $criterium[veldnaam]=waarde
$criterium["a037"] = "a037>0";
//organisatie cultuurbehoud
if ($action == 'download') {
include '../scripts/log_in.php';
require_once '../scripts/rizscripts.php';
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();
$workbook->send('ris' . $nr . '.xls');
$worksheet =& $workbook->addWorksheet('data');
//FORMAT OPTIONS
//achtergondkleur header/footer
$workbook->setCustomColor(12, 201, 201, 201);
$grey =& $workbook->addFormat();
$grey->setFgColor(12);
//achtergrondkleur rijen
$workbook->setCustomColor(13, 219, 217, 214);
$light_grey =& $workbook->addFormat();
$light_grey->setFgColor(13);
//decimalen
$dec_format =& $workbook->addFormat(array('NumFormat' => '0.0', 'BorderColor' => 'white', 'Border' => 1, 'FgColor' => 13));
//headers
$header_format =& $workbook->addFormat(array('BorderColor' => 'white', 'Align' => 'top', 'Border' => 1, 'FgColor' => 12));
//rijen (niet-decimaal)
$row_format =& $workbook->addFormat(array('BorderColor' => 'white', 'Border' => 1, 'FgColor' => 13));
//footer (niet-decimaal)
$footer_format =& $workbook->addFormat(array('BorderColor' => 'white', 'Border' => 1, 'FgColor' => 12));
//footer (decimaal)
function xls($params = array())
{
/*
// Generación de XLS //
$tabla = xls_select($sqlData);
$titulos = array('ID','Nombre Completo','No. Empleado','Fecha','Horas','Estatus','Capturado por','Capturado el');
$xlsData = array(
descarga => false
,datos => $tabla
,colsTitulos => $titulos
,archivo => 'HE_Horas-Extra'
,tituloTabla => 'HE - Horas Extra'
,hoja => 'Hoja1'
,directorio => $cfg[path_docs].'autorizacion/'
,id_empresa => $usuario[id_empresa]
);
$xls = xls($xlsData);
*/
global $Path, $Raiz;
require_once $Path[php] . 'class.spreadsheetExcelWriter.php';
// Parametros recibidos
$descarga = !isset($params[descarga]) ? false : $params[descarga];
$datos = !isset($params[datos]) ? array() : $params[datos];
$colsTitulos = !isset($params[colsTitulos]) ? array() : $params[colsTitulos];
$archivo = !isset($params[archivo]) ? 'tmp' : $params[archivo];
$tituloTabla = !isset($params[tituloTabla]) ? '' : $params[tituloTabla];
$hoja = !isset($params[hoja]) ? '' : $params[hoja];
$directorio = !isset($params[directorio]) ? false : $params[directorio];
// Parametros inicales
$x = 0;
#Filas
$y = 0;
#Columnas
if (file_exists($Raiz[local] . $directorio)) {
$dirLocal = $Raiz[local] . $directorio;
$dirUrl = $Raiz[url] . $directorio;
} else {
$dirLocal = $Path[tmp];
$dirUrl = $Path[tmpurl];
}
$id_empresa = str_pad($params[id_empresa], 2, '0', STR_PAD_LEFT);
$id_empresa .= '_';
$extensiones = array('/.xlsx/', '/.xls/', '/.csv/');
$archivo = preg_replace($extensiones, '', $archivo);
#limpia extensiones
$archivo = $archivo . '_' . $id_empresa . date('Ymd-His');
$hoja = empty($hoja) ? date('Ymd-His') : $hoja;
$filename[filename] = $archivo . '.xls';
if (!$descarga) {
/*Para crear en directorio de servidor*/
$filename[opc] = 'onserver';
$filename[local] = $dirLocal . $filename[filename];
$filename[url] = $dirUrl . $filename[filename];
$xls = new Spreadsheet_Excel_Writer($filename[local]);
} else {
/*Para descarga*/
$filename[opc] = 'download';
$filename[url] = $archivo;
$xls = new Spreadsheet_Excel_Writer();
$xls->send($filename[url]);
#Envio de headers HTML
}
// Cracion de hoja
$hoja1 = $xls->addWorksheet($hoja);
// Formatos
#Colores: setCustomColor(Indice, R,G,B)
/*
Indices Predefinidos:
0 -> Error
1 -> Blanco
2 -> Rojo
3 -> Verde
4 -> Azul
5 -> Amarillo
6 -> Magenta
7 -> Cyan
8 -> Negro
*/
$xls->setCustomColor(9, 255, 255, 255);
#Blanco
$xls->setCustomColor(10, 0, 0, 0);
#Negro
$xls->setCustomColor(11, 51, 51, 51);
#333333
$xls->setCustomColor(12, 221, 221, 221);
#DDDDDD
$xls->setCustomColor(13, 204, 204, 204);
#CCCCCC
$xls->setCustomColor(14, 45, 186, 113);
#2DBA71 : Verde PAE
$xls->setCustomColor(15, 5, 110, 247);
#056EF7 : Azul
/*
$f0=$xls->addFormat(array(
'Size' => 10 # Tamaños de letra
,'Align' => 'center' # Alineacion
,'Color' => 'green' # Color de letra
,'Bold' => 1 # Negrita
,'Underline' => 1 # Subrrayado
,'Pattern' => 1 # Patron de relleno
,'FgColor' => 'magenta' # Color de fondo
,'Border' => 1 # Borde de celda
,'BorderColor' => 'blue' # Color de borde
,'TextRotation' => '' # Grados de Rotación de texto: -1, 0, 90, 180
,'Locked' => 1 # Bloquear celda
));
*/
#Titulos de tabla
$fTitulos = $xls->addFormat();
$fTitulos->setFgColor(14);
# Fondo
$fTitulos->setBgColor('magenta');
# Color de Patron
$fTitulos->setPattern(1);
# Patron: 0-18 | 0 = no background
$fTitulos->setColor(9);
# Color de fuente
$fTitulos->setSize(11);
# Tamaño de fuente
$fTitulos->setAlign(vcenter);
# Alineacion V: top, vcenter, bottom, vjustify, vequal_space
$fTitulos->setAlign(center);
# Alineacion H: left, center, right, fill, justify, merge, equal_space
$fTitulos->setBold(1);
# Negrita: 1=bold, 400=normal, 700=Bold, 1000=extraBold
$fTitulos->setUnderline(false);
# Subrrayado
$fTitulos->setBorder(1);
# Borde de celda: 1 => thin, 2 => thick
$fTitulos->setBorderColor(9);
# Color de borde
$fTitulos->setTextRotation(0);
# Grados de Rotación de texto: -1, 0, 90, 180
// #Contenido
$fTxtOdd = $xls->addFormat();
$fTxtOdd->setFgColor(9);
# Fondo
$fTxtOdd->setColor(10);
# Color de fuente
$fTxtOdd->setSize(10);
# Tamaño de fuente
$fTxtOdd->setAlign(vcenter);
# Alineacion V: top, vcenter, bottom, vjustify, vequal_space
$fTxtOdd->setAlign(center);
# Alineacion H: left, center, right, fill, justify, merge, equal_space
$fTxtOdd->setBorder(1);
# Borde de celda: 1 => thin, 2 => thick
$fTxtOdd->setBorderColor(13);
# Color de borde
// --
$fTxtEven = $xls->addFormat();
$fTxtEven->setFgColor(12);
# Fondo
$fTxtEven->setColor(10);
# Color de fuente
$fTxtEven->setSize(10);
# Tamaño de fuente
$fTxtEven->setAlign(vcenter);
# Alineacion V: top, vcenter, bottom, vjustify, vequal_space
$fTxtEven->setAlign(center);
# Alineacion H: left, center, right, fill, justify, merge, equal_space
$fTxtEven->setBorder(1);
# Borde de celda: 1 => thin, 2 => thick
$fTxtEven->setBorderColor(13);
# Color de borde
// TODO: Funcion para auto-ajustar las columnas al ancho del contenido
// function autofit_columns($obj) {
// $col = 0;
// // var_dump($obj->_worksheets);
// while ($width=$obj->col_sizes){
// if($width){
// $obj->set_column($col, $col, $width);
// }
// $col++;
// }
// }
// autofit_columns($xls);
// Construccion de contenido
// write($x,$y,$valor,$formato) ==> $x=>Fila; $y=>Columna
#Titulos
if (!empty($tituloTabla)) {
$hoja1->write($x, $y, $tituloTabla, $fTitulos);
$hoja1->mergeCells($x, $y, $x, count($colsTitulos) - 1);
# Combinar celdas
$x++;
}
foreach ($colsTitulos as $tCol) {
$hoja1->write($x, $y, $tCol, $fTitulos);
$y++;
}
$x++;
#Contenido
foreach ($datos as $registro) {
#Formatos
$fRegistro = ++$r % 2 == 1 ? $fTxtOdd : $fTxtEven;
#Datos
$soloUno = !is_array($registro) ? true : false;
#Deteccion de total de registros
$data = !$soloUno ? $registro : $datos;
#Seleccion de arreglo
$y = 0;
for ($i = 0; $i < count($data) / 2; $i++) {
$hoja1->write($x, $y, $data[$i], $fRegistro);
$y++;
}
$x++;
if ($soloUno) {
break;
}
}
// Cerrar y crear archivo
$resultado = $xls->close();
/*Fin de XLS*/
return $filename;
}
function excel2($data)
{
require_once "Spreadsheet/Excel/Writer.php";
$workbook = new Spreadsheet_Excel_Writer();
$workbook->setVersion(8);
//$this->format_sheet();
$workbook->setCustomColor(31, 195,195,195);
$workbook->setCustomColor(32, 250,250,250);
$header =& $workbook->addFormat();
$header->setBold();
$header->setSize(10);
$header->setFgColor(31);
$header->setAlign('center');
$even =& $workbook->addFormat();
$even->setFgColor(32);
$even->setTextWrap();
$even->setAlign('vcenter');
$even->setAlign('center');
$even_red =& $workbook->addFormat();
$even_red->setFgColor(32);
$even_red->setTextWrap();
$even_red->setAlign('vcenter');
$even_red->setAlign('center');
$even_red->setColor('red');
$odd =& $workbook->addFormat();
$odd->setTextWrap();
$odd->setAlign('vcenter');
$odd->setAlign('center');
$odd_red =& $workbook->addFormat();
$odd_red->setTextWrap();
$odd_red->setAlign('vcenter');
$odd_red->setAlign('center');
$odd_red->setColor('red');
$worksheet =& $workbook->addWorksheet('SPEEDEX Report');
$worksheet->setColumn(0,0, 1);
$worksheet->setLandscape();
$y = 1;
$this->field_name = $this->selected_field;
$this->field_width = array( 'GMF/DR' => 15,
'SO/RO' => 15,
'ID' => 15,
'PO' => 15,
'SR' => 15,
'RNSS' => 15,
'MATCODE' => 15,
'MAT_DESC' => 30,
'QTY' => 5,
'CONSIGNEE' => 22,
'DATE OUT' => 12,
'DATE RCVD' => 12,
'RCVD BY' => 22,
'REMARKS' => 12,
'STATUS' => 12,
'WAYBILL' => 12,
'ETA' => 12,
'ETD' => 12,
'SLA' => 5,
'# DAYS DELIVERED' => 12,
'SLA' => 12,
'AREAS' => 12,
'RECVD BY' => 22);
for($i=0; $i<count($this->selected_field); $i++)
{
$worksheet->setColumn($i+1, $i+1, $this->field_width[$this->fields[$this->selected_field[$i]]]);
$worksheet->write($y, $i+1, strtoupper($this->field_name[$i]), $header);
}
$y++;
$n = 1;
foreach($data as $row)
{
$isOdd = true;
$sla = false;
if (($n++ % 2) == 0) $isOdd = false;
if ($row['sla'] > $row['asla'] && $row['DATE_RCVD'] == '---') $sla = true;
$xx = 1;
if ($isOdd)
{
if ($sla) $format = $odd_red; else $format = $odd;
}
else
{
if ($sla) $format = $even_red; else $format = $even;
}
foreach($this->selected_field as $val) $worksheet->write($y, $xx++, $row[$val], $format);
$y++;
}
$workbook->send('speedex_'.mktime().'.xls');
$workbook->close();
}
function render()
{
$filePath = $filename = $this->_fileName;
$workbook = new Spreadsheet_Excel_Writer($filename);
$workbook->setVersion(8);
// สำคัญมาก
$worksheet = $workbook->addWorksheet('sheet-1');
$worksheet->setInputEncoding('utf-8');
// สำคัญมาก
$worksheet->setPaper('A4');
$workbook->setCustomColor(20, 255, 255, 204);
$workbook->setCustomColor(21, 242, 242, 242);
$workbook->setCustomColor(23, 220, 230, 241);
if ($this->_pageType == 'P') {
$worksheet->setPortrait();
$worksheet->fitToPages('1748', '2480');
} else {
$worksheet->setLandscape();
$worksheet->fitToPages('2480', '1748');
}
$worksheet->setPrintScale(100);
foreach ($this->_column as $column) {
$worksheet->setColumn($column[0], $column[1], $column[2]);
}
$header =& $workbook->addFormat();
$header->setFontFamily('Helvetica');
$header->setBold(1);
$header->setBorder(1);
$header->setTop(2);
$header->setRight(2);
$header->setBottom(2);
$header->setLeft(2);
$header->setSize(9);
$header->setAlign('merge');
$header->setVAlign('vcenter');
$header->setHAlign('center');
$header->setFgColor(21);
# Add the title
$titleText = $this->_title;
$titleFormat =& $workbook->addFormat();
$titleFormat->setFontFamily('Helvetica');
$titleFormat->setBold();
$titleFormat->setSize('10');
$titleFormat->setColor('navy');
$titleFormat->setAlign('merge');
$titles = explode("\n", $titleText);
$i = 0;
$cellM = count($this->_column) - 1;
if ($cellM > 10) {
$cellM = 10;
}
foreach ($titles as $title) {
$worksheet->write($i, 0, $title, $titleFormat);
if (trim($title) != '') {
$worksheet->mergeCells($i, 0, $i, $cellM);
}
++$i;
}
$this->_currentRow = $this->_firstRow = $i + 1;
// set column width
//print_r($this->_column);
// Use this to keep track of the current row number
$firstRow = $this->_firstRow;
// set table header
//echo "count=",count($this->_column );
foreach ($this->_headers as $headerRow) {
// ปรับให้จำนวนสมาชิกใน array เท่ากัน
for ($i = 0; $i < count($this->_column); $i++) {
if (!isset($headerRow[$i])) {
$headerRow[$i] = '';
}
}
$j = 0;
foreach ($headerRow as $headerTitle) {
$worksheet->write($this->_currentRow, $j++, $headerTitle, $header);
}
++$this->_currentRow;
}
$worksheet->setRow($this->_currentRow - 1, 25);
// set data
$datas = $this->_data;
$i = 0;
--$this->_currentRow;
//echo $this->_currentRow;
$strat_data_row = $this->_currentRow;
foreach ($datas as $row) {
++$this->_currentRow;
$j = 0;
$rowData = array();
$formatList = array();
foreach ($row as $cols) {
$format = $this->getColumnFormat($j, $workbook);
$inline_format = $this->getFormat($this->_currentRow, $j);
if ($inline_format != null) {
foreach ($inline_format as $key => $value) {
$format->{"set{$key}"}($value);
}
}
$worksheet->write($this->_currentRow, $j, $cols, $format);
++$j;
}
//++$i;
}
//set formula
$firstRow = $firstRow + count($this->_headers);
++$this->_currentRow;
$lastRow = $this->_currentRow;
$colformula = array();
if (count($this->_formula) > 0) {
foreach ($this->_formula as $formula) {
$col = $formula[1];
$format = $this->getColumnFormat($col, $workbook);
$format->setBold();
$format->setTop(2);
// Top border
$format->setBottom(5);
$colformula[$row][$col] = $formula;
if (trim($formula[0]) == 'lastrow') {
$row = $lastRow;
} else {
$row = $formula[0];
}
$inline_format = $this->getFormat($row, $col);
if ($inline_format != null) {
foreach ($inline_format as $key => $value) {
$format->{"set{$key}"}($value);
}
}
if (substr($formula[2], 0, 1) == '=') {
$colLabel = $this->getColumnLabel($col);
$function = str_replace('[col]', $colLabel, $formula[2]);
$function = str_replace('[row]', $row + 1, $function);
$worksheet->writeFormula($row, $col, $function, $format);
} elseif (trim($formula[2]) == 'SUMCOLUMN') {
$collabel = $this->getColumnLabel($col);
$function = "=SUM({$collabel}{$firstRow}:{$collabel}{$lastRow})";
$worksheet->writeFormula($row, $col, $function, $format);
} elseif (trim($formula[2]) == 'COPY_FIRST_ROW') {
$collabel = $this->getColumnLabel($col);
$thisfirstRow = $firstRow + 1;
$function = "={$collabel}{$thisfirstRow}";
$worksheet->writeFormula($row, $col, $function, $format);
} else {
$worksheet->write($row, $col, $formula[2], $format);
}
}
}
//mergeCells
foreach ($this->_mergeCells as $cell) {
$worksheet->mergeCells($cell[0], $cell[1], $cell[2], $cell[3]);
}
// เพิ่มเติม text ต่างๆ
// ++$this->_currentRow ;
// ++$this->_currentRow ;
foreach ($this->_writetexts as $writetexts) {
if (!isset($writetexts[3])) {
$format =& $workbook->addFormat();
$format->setFontFamily('Helvetica');
$format->setBold();
$format->setSize('10');
$format->setColor('navy');
$format->setAlign('merge');
} else {
//$format = $writetexts[3];
$format = $workbook->addFormat($writetexts[3]);
}
if ($writetexts[0] == 'currentRow') {
$row = $this->_currentRow;
} elseif ($writetexts[0] == 'nextRow') {
$row = $this->_currentRow + 1;
} else {
$row = $writetexts[0];
}
if ($row >= $this->_currentRow) {
$this->_currentRow = $row;
}
if ($writetexts[1] == 'merge') {
$coll = 0;
$worksheet->write($row, $coll, $writetexts[2], $format);
$worksheet->mergeCells($row, 0, $row, $cellM);
for ($i = 1; $i <= $cellM; $i++) {
$worksheet->write($row, $i, ' ', $format);
}
} else {
$coll = $writetexts[1];
$worksheet->write($row, $coll, $writetexts[2], $format);
}
}
//$worksheet->freezePanes ( array ($firstRow + 1, 2 ) );
//echo "</table>";
++$this->_currentRow;
++$this->_currentRow;
$lastRow = $this->_currentRow;
$worksheet->write($lastRow, 0, 'created ' . date("d/m/Y H:i"));
$workbook->close();
$options['firstrow'] = $firstRow;
$options['dataset'] = $datas;
$options['formula'] = $colformula;
// echo $options['firstrow'];
//require_once 'Excel/excel_reader2.php';
//$data = new Spreadsheet_Excel_Reader ( $filePath,true,'',$options );
//$data->setOptions($options);
//$data->setOutputEncoding ( 'utf-8' );
//return $data->dump(true,true);
}
/**
* Export PDF document
*
* @param string $where WHERE clause for SQL statement
*/
function xlsexport($WHERE)
{
global $config, $lang;
$text_length = 256 - 3;
// videodb context dir
$context_dir = preg_replace('/^(.*)\\/.*?$/', '\\1', $_SERVER["SCRIPT_FILENAME"]);
// array of temp files wich have to be deleted if workbook is closed
$del_list = array();
// make shure we have list with extra fields, even if empty
$extra_fields = array_map('trim', explode(",", $config['xls_extra_fields']));
// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
$workbook->setCustomColor(12, 192, 192, 192);
// Headline
$workbook->setCustomColor(13, 255, 255, 200);
// Seen
$workbook->setCustomColor(14, 255, 220, 220);
// Lent
//$workbook->setCustomColor(15, 0,0,0); // Test
// sending HTTP headers
$outputFilename = $config['xls_output_filename'] ? $config['xls_output_filename'] : 'VideoDB';
$workbook->send($outputFilename . '.xls');
// Creating a worksheet
$sheetTitle = $config['xls_sheet_title'] ? $config['xls_sheet_title'] : 'VideoDB';
$worksheet =& $workbook->addWorksheet($sheetTitle);
// format templates
$alignLeftFormatNormal =& $workbook->addFormat();
$alignLeftFormatLent =& $workbook->addFormat(array('Pattern' => 1));
$alignLeftFormatLent->setFGColor(14);
$alignRightFormatNormal =& $workbook->addFormat(array('Align' => 'right'));
$alignRightFormatLent =& $workbook->addFormat(array('Align' => 'right', 'Pattern' => 1));
$alignRightFormatLent->setFgColor(14);
$alignCenterFormatNormal =& $workbook->addFormat(array('Align' => 'center'));
$alignCenterFormatLent =& $workbook->addFormat(array('Align' => 'center', 'Pattern' => 1));
$alignCenterFormatLent->setFgColor(14);
$titleFormatNormal =& $workbook->addFormat(array('Bold' => 1));
$titleFormatUnseen =& $workbook->addFormat(array('Bold' => 1, 'Pattern' => 1));
$titleFormatUnseen->setFgColor(13);
$titleFormatLent =& $workbook->addFormat(array('Bold' => 1, 'Pattern' => 1));
$titleFormatLent->setFgColor(14);
$plotFormatNormal =& $workbook->addFormat();
$plotFormatNormal->setTextWrap();
$plotFormatLent =& $workbook->addFormat(array('Pattern' => 1));
$plotFormatLent->setTextWrap();
$plotFormatLent->setFgColor(14);
$headlineFormat =& $workbook->addFormat(array('Bold' => 1, 'Align' => 'center', 'Pattern' => 1));
$headlineFormat->setFgColor(12);
$rowindex = 0;
$columnindex = 0;
if ($config['xls_show_headline']) {
$worksheet->setRow(0, 30);
$rowindex++;
}
// get data (see http://pear.php.net/bugs/bug.php?id=1572)
$result = iconv_array('utf-8', 'iso-8859-1', exportData($WHERE));
foreach ($result as $row) {
$columnindex = 0;
set_time_limit(300);
// rise per movie execution timeout limit if safe_mode is not set in php.ini
if (!empty($row['lentto']) && $config['xls_mark_lent']) {
$alignLeftFormat = $alignLeftFormatLent;
$alignCenterFormat = $alignLeftFormatLent;
$alignRightFormat = $alignLeftFormatLent;
} else {
$alignLeftFormat = $alignLeftFormatNormal;
$alignCenterFormat = $alignLeftFormatNormal;
$alignRightFormat = $alignLeftFormatNormal;
}
$worksheet->setRow($rowindex, 15, $alignLeftFormat);
foreach ($extra_fields as $field) {
$isNote = false;
$walks = 1;
if (preg_match('/(.+)\\((.+)\\)/', $field, $matches)) {
$field = trim($matches[1]);
$note = trim($matches[2]);
$walks = 2;
}
for ($walk = 0; $walk < $walks; $walk++) {
if ($walk == 1) {
$isNote = true;
$field = $note;
$columnindex--;
}
// title
if ($field == "title") {
// headline
if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
$worksheet->writeString(0, $columnindex, $lang['title'], $headlineFormat);
}
$title = $row['title'];
if ($row['subtitle']) {
$title .= ' - ' . $row['subtitle'];
}
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $lang['title'] . ":\n" . html_entity_decode($title));
} else {
if ($row['seen'] == '0' && $config['xls_mark_unseen']) {
$format = $titleFormatUnseen;
} elseif (!empty($row['lentto']) && $config['xls_mark_lent']) {
$format = $titleFormatLent;
} else {
$format = $titleFormatNormal;
}
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 50);
}
$imdb = $row['imdbID'];
$link = $imdb ? engineGetContentUrl($imdb, engineGetEngine($imdb)) : '';
$worksheet->writeUrl($rowindex, $columnindex, $link, html_entity_decode($title), $format);
}
$columnindex++;
} elseif ($field == "plot") {
// headline
if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
$worksheet->writeString(0, $columnindex, $lang['plot'], $headlineFormat);
}
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, leftString(html_entity_decode($row['plot']), $text_length));
} else {
if (!empty($row['lentto']) && $config['xls_mark_lent']) {
$format = $plotFormatLent;
} else {
$format = $plotFormatNormal;
}
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 50);
}
$worksheet->writeString($rowindex, $columnindex++, leftString(html_entity_decode($row['plot']), $text_length), $format);
}
} elseif ($field == "diskid") {
// headline
if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
$worksheet->writeString(0, $columnindex, $lang['diskid'], $headlineFormat);
}
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $lang['diskid'] . ":\n" . html_entity_decode($row['diskid']));
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 7);
}
$worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row['diskid']), $alignCenterFormat);
}
} elseif ($field == "language") {
// headline
if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
$worksheet->writeString(0, $columnindex, $lang['language'], $headlineFormat);
}
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $lang['language'] . ":\n" . html_entity_decode($row['language']));
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 30);
}
$worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row['language']), $alignLeftFormat);
}
} elseif ($field == "mediatype") {
// headline
if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
$worksheet->writeString(0, $columnindex, $lang['mediatype'], $headlineFormat);
}
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $lang['mediatype'] . ":\n" . html_entity_decode($row['mediatype']));
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 7);
}
$worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row['mediatype']), $alignLeftFormat);
}
} elseif ($field == "genres") {
// headline
if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
$worksheet->writeString(0, $columnindex, $lang['genres'], $headlineFormat);
}
if (count($row['genres'])) {
$output_genres = array();
foreach ($row['genres'] as $genre) {
$output_genres[] = html_entity_decode($genre['name']);
}
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $lang['genres'] . ":\n" . join(", ", $output_genres));
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 20);
}
$worksheet->writeString($rowindex, $columnindex, join(", ", $output_genres), $alignCenterFormat);
}
}
$columnindex++;
} elseif ($field == "runtime") {
// headline
if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
$worksheet->writeString(0, $columnindex, $lang['runtime'], $headlineFormat);
}
if ($row['runtime']) {
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $lang['runtime'] . ":\n" . html_entity_decode($row['runtime']) . ' min');
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 7);
}
$worksheet->writeString($rowindex, $columnindex, html_entity_decode($row['runtime']) . ' min', $alignRightFormat);
}
}
$columnindex++;
} elseif ($field == "year") {
// headline
if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
$worksheet->writeString(0, $columnindex, $lang['year'], $headlineFormat);
}
if ($row['year'] != '0000') {
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $lang['year'] . ":\n" . html_entity_decode($row['year']));
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 7);
}
$worksheet->writeNumber($rowindex, $columnindex, html_entity_decode($row['year']), $alignCenterFormat);
}
}
$columnindex++;
} elseif ($field == "owner") {
// headline
if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
$worksheet->writeString(0, $columnindex, $lang['owner'], $headlineFormat);
}
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $lang['owner'] . ":\n" . html_entity_decode($row['owner']));
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 15);
}
$worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row['owner']), $alignCenterFormat);
}
} elseif ($field == "lent") {
// headline
if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
$worksheet->writeString(0, $columnindex, $lang['lentto'], $headlineFormat);
}
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $lang['lentto'] . ":\n" . html_entity_decode($row['lentto']));
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 15);
}
$worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row['lentto']), $alignCenterFormat);
}
} elseif ($field == "seen") {
// headline
if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
$worksheet->writeString(0, $columnindex, $lang['seen'], $headlineFormat);
}
if ($isNote) {
if ($row['seen'] == 1) {
$worksheet->writeNote($rowindex, $columnindex++, html_entity_decode($lang['seen']));
} else {
$columnindex++;
}
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 2);
}
if ($row['seen'] == 1) {
$worksheet->writeString($rowindex, $columnindex++, "X", $alignCenterFormat);
} else {
$columnindex++;
}
}
} elseif ($field == "insertdate") {
// headline
if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
$worksheet->writeString(0, $columnindex, $lang['date'], $headlineFormat);
}
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $lang['date'] . ":\n" . html_entity_decode(preg_replace('/^([0-9]{4}\\-[0-9]{2}\\-[0-9]{2}).*/', '$1', $row['created'])));
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 10);
}
$worksheet->write($rowindex, $columnindex++, html_entity_decode(preg_replace('/^([0-9]{4}\\-[0-9]{2}\\-[0-9]{2}).*/', '$1', $row['created'])), $alignCenterFormat);
}
} elseif (preg_match("/^custom[0-4]\$/", $field)) {
// headline
if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
$worksheet->writeString(0, $columnindex, $config[$field], $headlineFormat);
}
//$row[$field] = html_entity_decode($row[$field]);
switch ($config[$field . 'type']) {
case 'ed2k':
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . html_entity_decode($row[$field]));
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 12);
}
$worksheet->writeUrl($rowindex, $columnindex++, html_entity_decode($row[$field]), 'ED2K-Link', $alignCenterFormat);
}
break;
case 'language':
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . html_entity_decode($row[$field]));
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 30);
}
$worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignLeftFormat);
}
break;
case 'rating':
if ($row[$field]) {
$rating = html_entity_decode($row[$field]) . '/10';
} else {
$rating = "";
}
if ($isNote) {
if ($row[$field]) {
$worksheet->writeNote($rowindex, $columnindex, $config[$field] . ":\n" . $rating);
}
$columnindex++;
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 7);
}
if ($row[$field]) {
$worksheet->writeString($rowindex, $columnindex, $rating, $alignCenterFormat);
}
$columnindex++;
}
break;
case 'fsk':
if (preg_match("/[0-9]+/", $row[$field]) && !preg_match("/[^0-9]+/", $row[$field])) {
$fskstr = 'FSK' . html_entity_decode($row[$field]);
} else {
$fskstr = html_entity_decode($row[$field]);
}
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . $fskstr);
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 7);
}
$worksheet->writeString($rowindex, $columnindex++, $fskstr, $alignCenterFormat);
}
break;
case 'barcode':
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . html_entity_decode($row[$field]));
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 15);
}
$worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignCenterFormat);
}
break;
case 'orgtitle':
if ($isNote) {
if (!empty($row[$field])) {
$worksheet->writeNote($rowindex, $columnindex, $config[$field] . ":\n" . html_entity_decode($row[$field]));
}
$columnindex++;
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 50);
}
$worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignLeftFormat);
}
break;
case 'movix':
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n " . html_entity_decode($row[$field]));
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 7);
}
$worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignCenterFormat);
}
break;
case 'mpaa':
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . html_entity_decode($row[$field]), $alignCenterFormat);
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 12);
}
$worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignCenterFormat);
}
break;
case 'bbfc':
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . html_entity_decode($row[$field]));
} else {
if ($rowindex == 1) {
$worksheet->setColumn($columnindex, $columnindex, 7);
}
$worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignCenterFormat);
}
break;
default:
// unknown
if ($isNote) {
$worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . html_entity_decode($row[$field]));
} else {
$worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignLeftFormat);
}
break;
}
}
}
//End of walk
}
$rowindex++;
}
// Let's send the file
$workbook->close();
}
}
}
//Studiengaenge laden
$stg_obj = new studiengang();
$stg_obj->getAll('typ, kurzbz', false);
$stg_arr = array();
foreach ($stg_obj->result as $row) {
$stg_arr[$row->studiengang_kz] = $row->kuerzel;
}
if (isset($_REQUEST['format']) && $_REQUEST['format'] == 'xls') {
// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
// sending HTTP headers
$workbook->send("Auswertung " . (isset($_REQUEST['reihungstest']) && $_REQUEST['reihungstest'] != '' ? $stg_arr[$rtest[$reihungstest]->studiengang_kz] . " " . $datum_obj->formatDatum($rtest[$reihungstest]->datum, 'd.m.Y') : 'aller Reihungstests') . ".xls");
$workbook->setVersion(8);
$workbook->setCustomColor(15, 192, 192, 192);
//Setzen der HG-Farbe Hellgrau
$workbook->setCustomColor(22, 193, 0, 0);
//Setzen der HG-Farbe Dunkelrot
// Creating a worksheet
$titel_studiengang = isset($_REQUEST['studiengang']) && $_REQUEST['studiengang'] != '';
$titel_semester = isset($_REQUEST['semester']) && $_REQUEST['semester'] != '';
$worksheet =& $workbook->addWorksheet("Technischer Teil " . ($titel_studiengang ? $stg_arr[$_REQUEST['studiengang']] : '') . ($titel_semester ? ' ' . $semester . '.Semester' : ''));
$worksheet->setInputEncoding('utf-8');
$worksheet->setZoom(85);
//Formate Definieren
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_bold->setAlign("center");
$format_bold->setFgColor(15);
$format_bold->setVAlign('vcenter');
echo "<font class='message'>" . t("Joista jätetään pois ne tuotteet joita ei ehdoteta ostettavaksi") . ".<br>";
}
flush();
//keksitään failille joku varmasti uniikki nimi:
list($usec, $sec) = explode(' ', microtime());
mt_srand((double) $sec + (double) $usec * 100000);
$excelnimi = md5(uniqid(mt_rand(), true)) . ".xls";
$workbook = new Spreadsheet_Excel_Writer('/tmp/' . $excelnimi);
$workbook->setVersion(8);
$worksheet =& $workbook->addWorksheet('Sheet 1');
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_center =& $workbook->addFormat();
$format_center->setBold();
$format_center->setHAlign('left');
$workbook->setCustomColor(12, 255, 255, 0);
$format_bg_yellow =& $workbook->addFormat();
$format_bg_yellow->setFgColor(12);
$format_bg_yellow->setPattern(1);
$format_bg_yellow_text_red =& $workbook->addFormat();
$format_bg_yellow_text_red->setFgColor(12);
$workbook->setCustomColor(17, 255, 0, 0);
$format_bg_yellow_text_red->setColor(17);
$format_bg_yellow_text_red->setPattern(1);
$workbook->setCustomColor(13, 200, 100, 180);
$format_bg_magenta =& $workbook->addFormat();
$format_bg_magenta->setFgColor(13);
$format_bg_magenta->setPattern(1);
$format_bg_magenta_text_red =& $workbook->addFormat();
$format_bg_magenta_text_red->setFgColor(13);
$format_bg_magenta_text_red->setColor(17);
function medlemordreliste($division = 0)
{
$time_start = microtime(true);
apache_setenv('KeepAliveTimeout', 60);
apache_setenv('Timeout', 60);
apache_setenv('no-gzip', 0);
ini_set('zlib.output_compression', 1);
error_reporting(E_ALL);
ini_set('display_errors', 'On');
ini_set('error_log', '/var/log/php.log');
#linux
set_time_limit(300);
ini_set('max_execution_time', 300);
/*
@apache_setenv('no-gzip', 1);
@ini_set('zlib.output_compression', 0);
@ini_set('implicit_flush', 1);
for ($i = 0; $i < ob_get_level(); $i++) { ob_end_flush(); }
ob_implicit_flush(1);
*/
require_once 'Spreadsheet/Excel/Writer.php';
if ($this->uri->segment(3) > 0) {
$division = $this->uri->segment(3);
} else {
$division = $this->input->post('division');
}
// Create a workbook
// $workbook = new Spreadsheet_Excel_Writer('/www/kbhff.skrig.dk/excel/test.xls');
$workbook = new Spreadsheet_Excel_Writer();
$this->load->helper('date');
$now = time();
$time = unix_to_human($now, FALSE, 'eu');
// Euro time with seconds
$divisionname = $this->_divisionname($division);
$excelfile = 'Medlemmer_ordrer_' . $time . '_' . utf8_decode($divisionname) . '.xls';
// sending HTTP headers
$workbook->send($excelfile);
$formatbold = $workbook->addFormat();
$formatbold->setBold(600);
$workbook->setCustomColor(12, 218, 254, 218);
$format_our_green =& $workbook->addFormat();
$format_our_green->setFgColor(12);
$rowformat1 =& $workbook->addFormat(array('Size' => 10, 'Color' => 'black'));
$rowformat2 =& $workbook->addFormat(array('Size' => 10, 'Color' => 'black', 'FgColor' => '12'));
// Creating a worksheet
$tabname = 'Oversigt, ' . $division . ' ' . unix_to_human(time(), FALSE, 'eu');
$worksheet =& $workbook->addWorksheet('Oversigt, ' . utf8_decode($divisionname));
$worksheet->setColumn(0, 0, 10);
// medlemsnummer
$worksheet->setColumn(1, 1, 30);
// navn
$worksheet->setColumn(2, 12, 11);
// orderdate
// Creating a title
$worksheet->write(0, 0, 'Medlem', $formatbold);
$worksheet->write(0, 1, 'Navn', $formatbold);
$query = $this->db->query('SELECT uid, firstname, middlename, lastname
FROM ff_persons, ff_division_members
WHERE ff_persons.uid = ff_division_members.member
AND ff_division_members.division = ' . (int) $division . ' ORDER BY ff_persons.firstname');
$currentrow = 1;
foreach ($query->result() as $row) {
$dynformat = alternator('rowformat1', 'rowformat2');
$format = ${$dynformat};
$worksheet->write($currentrow, 0, utf8_decode("{$row->uid}"), $format);
if ($row->middlename > '') {
$name = $row->firstname . ' ' . $row->middlename . ' ' . $row->lastname;
} else {
$name = $row->firstname . ' ' . $row->lastname;
}
// get member orderdetails
$datequery = $this->db->query('SELECT uid, pickupdate FROM ff_pickupdates WHERE division = ' . (int) $division . ' and datediff(now(),pickupdate) < 100 ORDER BY pickupdate ');
$ordercolstart = 2;
$count = 0;
foreach ($datequery->result() as $pickupdate) {
if ($currentrow == 1) {
$worksheet->write(0, $ordercolstart + $count, $pickupdate->pickupdate, $formatbold);
}
$order = $this->_get_member_dateorder($row->uid, $pickupdate->uid);
if ($order > 0) {
$worksheet->write($currentrow, $ordercolstart + $count, $order, $format);
} else {
$worksheet->write($currentrow, $ordercolstart + $count, '', $format);
}
$count++;
}
$worksheet->write($currentrow, 1, utf8_decode("{$name}"), $format);
$currentrow++;
}
$finalExcelRow = $currentrow;
// rowstart in Excel is 1, so is correct after ++
$worksheet->write($currentrow, 0, utf8_decode("SUM"), $formatbold);
$tempcol = 3;
$tempmaxcol = $ordercolstart + $count;
while ($tempcol <= $tempmaxcol) {
if ($tempcol > 26) {
$formula = '=SUM(A' . $this->_numtochars($tempcol) . '2:A' . $this->_numtochars($tempcol) . $finalExcelRow . ')';
} else {
$formula = '=SUM(' . $this->_numtochars($tempcol) . '2:' . $this->_numtochars($tempcol) . $finalExcelRow . ')';
}
$worksheet->writeFormula($currentrow, $tempcol - 1, $formula);
$tempcol++;
}
$time_end = microtime(true);
$time = $time_end - $time_start;
$worksheet->write($currentrow + 1, 1, $time);
$x = memory_get_peak_usage(true);
$x2 = memory_get_peak_usage();
$worksheet->write($currentrow + 2, 1, $x);
$worksheet->write($currentrow + 3, 1, $x2);
// Let's send the file
$workbook->close();
}
public function output($file = null)
{
ob_clean();
$spreadsheet = new Spreadsheet_Excel_Writer($file);
if ($file == null) {
$spreadsheet->send("report.xls");
}
$worksheet =& $spreadsheet->addWorkSheet("Report");
$worksheet->setLandscape();
$worksheet->hideGridlines();
$worksheet->setPaper(9);
$worksheet->setMargins(0.25);
$worksheet->setFooter("Generated on " . date("jS F, Y @ g:i:s A") . " by " . $_SESSION["user_lastname"] . " " . $_SESSION["user_firstname"]);
$row = 0;
foreach ($this->contents as $content) {
if (!is_object($content)) {
continue;
}
switch ($content->getType()) {
case "text":
$format =& $spreadsheet->addFormat();
if ($row != 0) {
$row++;
}
$style = "padding:0px;margin:0px;";
if (isset($content->style["font"])) {
$format->setFontFamily($content->style["font"]);
}
if (isset($content->style["size"])) {
$format->setSize($content->style["size"]);
}
if (isset($content->style["bold"])) {
$format->setBold(700);
}
$worksheet->write($row, 0, $content->getText(), $format);
break;
case "table":
if ($content->style["totalsBox"]) {
$format =& $spreadsheet->addFormat();
$format->setFontFamily("Helvetica");
$format->setSize(12);
$spreadsheet->setCustomColor(13, 180, 200, 180);
$format->setBorderColor(13);
$format->setBottom(2);
$format->setBold(700);
$totals = $content->getData();
for ($i = 0; $i < $this->numColumns; $i++) {
$worksheet->write($row, $i, $totals[$i], $format);
//,$format);
}
} else {
if (!$this->widthsSet && isset($content->data_params["widths"])) {
foreach ($content->data_params["widths"] as $i => $width) {
$worksheet->setColumn($i, $i, $width * 1.5);
}
$this->widthsSet = true;
}
$headers = $content->getHeaders();
$format =& $spreadsheet->addFormat();
$format->setFontFamily("Helvetica");
$format->setSize(12);
$spreadsheet->setCustomColor(12, 102, 128, 102);
$format->setFgColor(12);
$format->setColor("white");
$format->setBold(700);
$this->numColumns = count($headers);
foreach ($headers as $col => $header) {
$worksheet->write($row, $col, str_replace("\\n", "\n", $header), $format);
}
$format =& $spreadsheet->addFormat();
$format->setFontFamily("Helvetica");
$format->setSize(12);
$spreadsheet->setCustomColor(13, 180, 200, 180);
$format->setBorderColor(13);
$format->setBorder(1);
foreach ($content->getData() as $rowData) {
$row++;
$col = 0;
foreach ($rowData as $field) {
$worksheet->write($row, $col, trim($field), $format);
$col++;
}
}
}
break;
}
$row++;
}
$spreadsheet->close();
}
