php simple excel table

<?php

//

class SimpleExcelCellsTypes {

const None =0;

const Numeric =1;

const Date =2;

const Text =3;

}

class SimpleExcelSheets {

public $named =null;

public $fullfile =null;

public $has_header =null;

public $has_data =null;

public $max_row =1;

}

class SimpleExcel {

//

private $ColumnText =array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ','CA','CB','CC','CD','CE','CF','CG','CH','CI','CJ','CK','CL','CM','CN','CO','CP','CQ','CR','CS','CT','CU','CV','CW','CX','CY','CZ','DA','DB','DC','DD','DE','DF','DG','DH','DI','DJ','DK','DL','DM','DN','DO','DP','DQ','DR','DS','DT','DU','DV','DW','DX','DY','DZ','EA','EB','EC','ED','EE','EF','EG','EH','EI','EJ','EK','EL','EM','EN','EO','EP','EQ','ER','ES','ET','EU','EV','EW','EX','EY','EZ','FA','FB','FC','FD','FE','FF','FG','FH','FI','FJ','FK','FL','FM','FN','FO','FP','FQ','FR','FS','FT','FU','FV','FW','FX','FY','FZ','GA','GB','GC','GD','GE','GF','GG','GH','GI','GJ','GK','GL','GM','GN','GO','GP','GQ','GR','GS','GT','GU','GV','GW','GX','GY','GZ','HA','HB','HC','HD','HE','HF','HG','HH','HI','HJ','HK','HL','HM','HN','HO','HP','HQ','HR','HS','HT','HU','HV','HW','HX','HY','HZ','IA','IB','IC','ID','IE','IF','IG','IH','II','IJ','IK','IL','IM','IN','IO','IP','IQ','IR','IS','IT','IU','IV');

//

private $Strings =array();

private $Header =array();

//

private $worksheets =array();

//

private $DataFile ="";

//

private $db =null;

//

public $CreatedBy ="";

public $Company ="";

//

private $workbook ="";

private $styles ="";

private $sharedStrings ="";

private $content_types ="";

private $rels ="";

private $app ="";

private $core ="";

private $xl_rels ="";

private $theme1 ="";

private $sheet1_rels ="";

//

private $MaxColumn =0;

private $MaxRow =0;

//

private $ActiveSheet =0;

//

function __construct() {

// Create the SQLite database in memory at creation.

if (is_null($this->db)) {

if ($this->db = new SQLite3(':memory:')) {

$this->db->exec('CREATE TABLE storage (sheet INT, row INT, column INT, data_type INT, original_value TEXT, date_value INT, text_index INT);');

} else {

}

}

//

$Sheet1 = new SimpleExcelSheets();

$Sheet1->named = "Sheet1";

$Sheet1->has_header = false;

$Sheet1->has_data = false;

$this->worksheets[] = $Sheet1;

//

$Sheet2 = new SimpleExcelSheets();

$Sheet2->named = "Sheet2";

$Sheet2->has_header = false;

$Sheet2->has_data = false;

$this->worksheets[] = $Sheet2;

//

$Sheet3 = new SimpleExcelSheets();

$Sheet3->named = "Sheet3";

$Sheet3->has_header = false;

$Sheet3->has_data = false;

$this->worksheets[] = $Sheet3;

//

}

//

function checkDatabase($clearData = false, $clearHeader = false) {

//

if (is_null($this->db)) {

$this->db = new SQLite3(':memory:');

$this->db->exec('CREATE TABLE storage (sheet INT, row INT, column INT, data_type INT, original_value TEXT, date_value INT, text_index INT);');

} else {

if ($clearData == true) {

$this->worksheets[$this->ActiveSheet]->has_data = false;

$this->db->exec("DELETE FROM storage WHERE sheet = {$this->ActiveSheet} AND row > 1;");

}

if ($clearHeader == true) {

$this->worksheets[$this->ActiveSheet]->has_header = false;

$this->db->exec("DELETE FROM storage WHERE sheet = {$this->ActiveSheet} AND row = 1;");

}

}

//

}

//

private function EscapeXML($text) {

return str_replace("%", "%", str_replace(">", ">", str_replace("<", "<", str_replace("&", "&", $text))));

}

//

private function BuildFiles() {

//

// ------> /xl/workbook.xml as $workbook, simple container listing worksheets with their IDs.

;{

$this->workbook = "

<?xml version="1.0" encoding="utf-8" standalone="yes"?>

";

$this->workbook .= "<workbook xmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main"xmlns:r="https://schemas.openxmlformats.org/officeDocument/2006/relationships">";

$this->workbook .= "    <fileVersion appName="xl"lastEdited="4"lowestEdited="4"rupBuild="4506"/>";

$this->workbook .= "    <workbookPr defaultThemeVersion="124226"/>";

$this->workbook .= "    <bookViews>";

$this->workbook .= "        <workbookView xWindow="240"yWindow="75"windowWidth="16140"windowHeight="10365"/>";

$this->workbook .= "    </bookViews>";

$this->workbook .= "    <sheets>";

foreach ($this->worksheets as $key=>$value) {

// Loop through all worksheets in the workbook.

$this->workbook .= "        <sheet name="".$value->named."" sheetId="".(string)($key+1)."" r:id="rId".(string)($key+1)."" />";

// Use the name of the workbook and then the index (1 based) as the Sheet ID and the internal rId.

}

$this->workbook .= "    </sheets>";

$this->workbook .= "    <calcPr calcId="114210"/>";

$this->workbook .= "</workbook>";

;}

// ------> /xl/styles.xml as $styles, container for all styles used in the workbook. Indexes (used as IDs) are 0 based.

;{

$this->styles = "

<?xml version="1.0" encoding="utf-8" standalone="yes"?>

";

$this->styles .= "<styleSheet xmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main">";

$this->styles .= "    <fonts count="2">";

$this->styles .= "        <font>";

$this->styles .= "            <sz val="10"/>";

$this->styles .= "            <name val="Arial"/>";

$this->styles .= "        </font>";

$this->styles .= "        <font>";

$this->styles .= "            <b />";

$this->styles .= "            <sz val="10"/>";

$this->styles .= "            <name val="Arial"/>";

$this->styles .= "            <family val="2"/>";

$this->styles .= "        </font>";

$this->styles .= "    </fonts>";

$this->styles .= "    <fills count="3">";

$this->styles .= "        <fill>";

$this->styles .= "            <patternFill patternType="none"/>";

$this->styles .= "        </fill>";

$this->styles .= "        <fill>";

$this->styles .= "            <patternFill patternType="gray125"/>";

$this->styles .= "        </fill>";

$this->styles .= "        <fill>";

$this->styles .= "            <patternFill patternType="solid">";

$this->styles .= "                <fgColor indexed="22"/>";

$this->styles .= "                <bgColor indexed="64"/>";

$this->styles .= "            </patternFill>";

$this->styles .= "        </fill>";

$this->styles .= "    </fills>";

$this->styles .= "    <borders count="2">";

$this->styles .= "        <border>";

$this->styles .= "            <left />";

$this->styles .= "            <right />";

$this->styles .= "            <top />";

$this->styles .= "            <bottom />";

$this->styles .= "            <diagonal />";

$this->styles .= "        </border>";

$this->styles .= "        <border>";

$this->styles .= "            <left style="thin">";

$this->styles .= "                <color indexed="64"/>";

$this->styles .= "            </left>";

$this->styles .= "            <right style="thin">";

$this->styles .= "                <color indexed="64"/>";

$this->styles .= "            </right>";

$this->styles .= "            <top style="thin">";

$this->styles .= "                <color indexed="64"/>";

$this->styles .= "            </top>";

$this->styles .= "            <bottom style="thin">";

$this->styles .= "                <color indexed="64"/>";

$this->styles .= "            </bottom>";

$this->styles .= "            <diagonal />";

$this->styles .= "        </border>";

$this->styles .= "    </borders>";

$this->styles .= "    <cellStyleXfs count="1">";

$this->styles .= "        <xf numFmtId="0"fontId="0"fillId="0"borderId="0"/>";

$this->styles .= "    </cellStyleXfs>";

$this->styles .= "    <cellXfs count="3">";

$this->styles .= "        <xf numFmtId="0"fontId="0"fillId="0"borderId="0"xfId="0"/>";

$this->styles .= "        <xf numFmtId="0"fontId="1"fillId="2"borderId="1"xfId="0"applyFont="1"applyFill="1"applyBorder="1"applyAlignment="1">";

$this->styles .= "            <alignment horizontal="center"/>";

$this->styles .= "        </xf>";

$this->styles .= "        <xf numFmtId="14"fontId="0"fillId="0"borderId="0"xfId="0"applyNumberFormat="1"/>";

$this->styles .= "    </cellXfs>";

$this->styles .= "    <cellStyles count="1">";

$this->styles .= "        <cellStyle name="Normal"xfId="0"builtinId="0"/>";

$this->styles .= "    </cellStyles>";

$this->styles .= "    <dxfs count="0"/>";

$this->styles .= "    <tableStyles count="0"defaultTableStyle="TableStyleMedium9"defaultPivotStyle="PivotStyleLight16"/>";

$this->styles .= "</styleSheet>";

;}

// ------> /xl/sharedStrings.xml as $sharedStrings

;{

$this->sharedStrings = "

<?xml version="1.0" encoding="utf-8" standalone="yes"?>

";

$this->sharedStrings .= "<sst xmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main"count="".(string)count($this->Strings)."" uniqueCount="".(string)count($this->Strings)."">";

foreach ($this->Strings as $value) {

$this->sharedStrings .= "    <si><t>".$this->EscapeXML($value)."</t></si>";

}

$this->sharedStrings .= "</sst>";

;}

// ------> /xl/worksheets/sheet1.xml as $Sheet1

;{

foreach ($this->worksheets as $key=>$value) {

while (true) {

$value->fullfile = sys_get_temp_dir().'/'.uniqid('SimpleExcel', true).'.xml';

if (!file_exists($value->fullfile)) break;

}

if (!$f = fopen($value->fullfile, 'w')) {exit;}

if ($value->has_header == false && $value->has_data == false) {

fwrite($f, "

<?xml version="1.0" encoding="utf-8" standalone="yes"?>

");

fwrite($f, "<worksheet xmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main"xmlns:r="https://schemas.openxmlformats.org/officeDocument/2006/relationships">");

fwrite($f, "    <dimension ref="A1"/>");

fwrite($f, "    <sheetViews>");

fwrite($f, "        <sheetView workbookViewId="0"/>");

fwrite($f, "    </sheetViews>");

fwrite($f, "    <sheetFormatPr defaultRowHeight="12.75"/>");

fwrite($f, "    <sheetData />");

fwrite($f, "    <phoneticPr fontId="0"type="noConversion"/>");

fwrite($f, "    <pageMargins left="0.75"right="0.75"top="1"bottom="1"header="0.5"footer="0.5"/>");

fwrite($f, "    <headerFooter alignWithMargins="0"/>");

fwrite($f, "</worksheet>");

} else {

fwrite($f, "

<?xml version="1.0" encoding="utf-8" standalone="yes"?>

");

fwrite($f, "<worksheet xmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main"xmlns:r="https://schemas.openxmlformats.org/officeDocument/2006/relationships">");

fwrite($f, "    <dimension ref="A1:".$this->ColumnText[$this->MaxColumn].(string)($value->max_row)."" />");

fwrite($f, "    <sheetViews>");

if ($key == 0) {

fwrite($f, "        <sheetView tabSelected="1"workbookViewId="0">");

} else {

fwrite($f, "        <sheetView tabSelected="0"workbookViewId="0">");

}

fwrite($f, "            <selection activeCell="A1"sqref="A1"/>");

fwrite($f, "        </sheetView>");

fwrite($f, "    </sheetViews>");

fwrite($f, "    <sheetFormatPr defaultRowHeight="12.75"/>");

fwrite($f, "    <cols>");

fwrite($f, "        <col min="1"max="".(string)$this->MaxColumn."" width="10.140625" bestFit="1" customWidth="1" />");

fwrite($f, "    </cols>");

fwrite($f, "    <sheetData>");

$column = 1;

$row_index = 0;

$results = $this->db->query("SELECT s1.row, (SELECT count(s2.column) FROM storage s2 WHERE s2.sheet = s1.sheet AND s2.row = s1.row) as column_count, s1.column, s1.data_type, s1.original_value, s1.date_value, s1.text_index FROM storage s1 WHERE s1.sheet = $key ORDER BY s1.row, s1.column;");

while ($e = $results->fetchArray(SQLITE3_NUM)) {

if ($e[0] != $row_index) {

if ($e[0] > 1) {fwrite($f, "        </row>");}

fwrite($f, "        <row r="{$e[0]}"spans="1:".$e[1]."">");

$row_index = $e[0];

}

if ($e[0] == 1) {

fwrite($f, "            <c r="".$this->ColumnText[$e[2]-1].(string)$e[0]."" s="1" t="s">");

fwrite($f, "                <v>".$e[6]."</v>");

} else {

switch ($e[3]) {

case SimpleExcelCellsTypes::None:

fwrite($f, "            <c r="".$this->ColumnText[$e[2]-1].(string)$e[0]."">");

fwrite($f, "                <v></v>");

break;

case SimpleExcelCellsTypes::Numeric:

fwrite($f, "            <c r="".$this->ColumnText[$e[2]-1].(string)$e[0]."">");

fwrite($f, "                <v>".$e[4]."</v>");

break;

case SimpleExcelCellsTypes::Text:

fwrite($f, "            <c r="".$this->ColumnText[$e[2]-1].(string)$e[0]."" t="s">");

fwrite($f, "                <v>".$e[6]."</v>");

break;

case SimpleExcelCellsTypes::Date:

fwrite($f, "            <c r="".$this->ColumnText[$e[2]-1].(string)$e[0]."" s="2">");

fwrite($f, "                <v>".(string)round(25569 + ($e[5] / 86400), 0, PHP_ROUND_HALF_DOWN)."</v>");

break;

default:

fwrite($f, "            <c r="".$this->ColumnText[$e[2]-1].(string)$e[0]."">");

fwrite($f, "                <v>".$e[4]."</v>");

break;

}

}

fwrite($f, "            </c>");

}

fwrite($f, "        </row>");

fwrite($f, "    </sheetData>");

fwrite($f, "    <phoneticPr fontId="0"type="noConversion"/>");

fwrite($f, "    <pageMargins left="0.75"right="0.75"top="1"bottom="1"header="0.5"footer="0.5"/>");

fwrite($f, "    <pageSetup orientation="portrait"verticalDpi="0"r:id="rId1"/>");

fwrite($f, "    <headerFooter alignWithMargins="0"/>");

fwrite($f, "</worksheet>");

}

fclose($f);

}

;}

// ------> /[Content_Types].xml as $content_types

;{

$this->content_types = "