Phpspreadsheet Chinese document 2 (translation software version)

Due to phpoffice/phpexcel (this package has been discarded and is no longer maintained. The author suggests using the phpoffice/phpspreadsheet package instead.)

Recently, we started to use Phpspreadsheet and found that the Chinese document is incomplete. My English level is average, and I am anxious to develop. So put on the google translation, anxious friends can make do with it first, students with good English background can directly ignore this post.

This sticker is for reference only.
This sticker is for reference only.
This sticker is for reference only.

Original address:

Official documents of Phpspreadsheet

File » subject "read and write file

Read and write files

As you already know from the architecture, using the basic PhpSpreadsheet class cannot read or write to the persistence store. To do this, PhpSpreadsheet provides readers and writers. This is the implementation of \ PhpOffice\PhpSpreadsheet\Reader\IReader and \ PhpOffice\PhpSpreadsheet\Writer\IWriter.

\ PhpOffice \ PhpSpreadsheet \ IOFactory

The PhpSpreadsheet API provides several ways to create instances of \ PhpOffice\PhpSpreadsheet\Reader\IReader or \ PhpOffice\PhpSpreadsheet\Writer\IWriter:

By creating the \ PhpOffice\PhpSpreadsheet\IOFactory directly. All of the following examples demonstrate the direct creation method. Note that you can also use the class \ PhpOffice\PhpSpreadsheet\IOFactory to do this.

\PhpOffice\PhpSpreadsheet\Reader\IReader use to create \ PhpOffice\PhpSpreadsheet\IOFactory
There are two ways to read files into PhpSpreadsheet: using automatic file type resolution or explicit.

Automatic file type resolution can check different distributions with \ PhpOffice\PhpSpreadsheet\Reader\IReader using PhpSpreadsheet. If one of them can load the specified filename, use that filename to load the file \ PhpOffice\PhpSpreadsheet\Reader\IReader. Explicit mode requires you to specify the mode that \ PhpOffice\PhpSpreadsheet\Reader\IReader should use.

You can use the following code samples to create an \ PhpOffice\PhpSpreadsheet\Reader\IReader instance \ PhpOffice\PhpSpreadsheet\IOFactory in automatic file type resolution mode:

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load("05featuredemo.xlsx");

A typical use of this feature is when you need to read files uploaded by users, and you don't know whether they are uploading xls or xlsx files.

If you need to set some properties on the reader (for example, read data only, see more later), you can use the following variations instead:

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile("05featuredemo.xlsx");
$reader->setReadDataOnly(true);
$reader->load("05featuredemo.xlsx");

You can use the following code samples to create an instance of \ PhpOffice\PhpSpreadsheet\Reader\IReader \ PhpOffice\PhpSpreadsheet\IOFactory in explicit mode using:

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx");
$spreadsheet = $reader->load("05featuredemo.xlsx");

Note that automatic type resolution mode is slightly slower than explicit mode.

\PhpOffice\PhpSpreadsheet\Writer\IWriter create with \ PhpOffice\PhpSpreadsheet\IOFactory
You can use \ PhpOffice\PhpSpreadsheet\Writer\IWriter to create an instance \ PhpOffice\PhpSpreadsheet\IOFactory:

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, "Xlsx");
$writer->save("05featuredemo.xlsx");

Excel 2007 (SpreadsheetML) file format

The xlsx file format is the main file format of PhpSpreadsheet. It allows memory spreadsheets to be output to. Xlsx files.

\PhpOffice \ PhpSpreadsheet \ Reader \ Xlsx reading spreadsheets

You can read the. xlsx file using the following code:

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load("05featuredemo.xlsx");

Read data only

You can set the option setReadDataOnly on the reader to instruct the reader to ignore styles, data validation, and so on, and only read cell data:

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load("05featuredemo.xlsx");

Read only specific worksheets

You can set the option setLoadSheetsOnly on the reader to instruct the reader to load only sheets with the given name:

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setLoadSheetsOnly(["Sheet 1", "My special sheet"]);
$spreadsheet = $reader->load("05featuredemo.xlsx");

Read only specific cells

You can set the option setReadFilter on the reader to indicate that the reader only loads cells that match the given rule. The read filter can be any implemented class \ PhpOffice\PhpSpreadsheet\Reader\IReadFilter. By default, use read all cells \ PhpOffice\PhpSpreadsheet\Reader\DefaultReadFilter.

The following code will read only lines 1 and 20 to 30 of any worksheet in the Excel file:

class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter {

    public function readCell($column, $row, $worksheetName = '') {
        // Read title row and rows 20 - 30
        if ($row == 1 || ($row >= 20 && $row <= 30)) {
            return true;
        }
        return false;
    }
}

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setReadFilter( new MyReadFilter() );
$spreadsheet = $reader->load("06largescale.xlsx");

\PhpOffice \ PhpSpreadsheet \ Writer \ Xlsx write spreadsheet

You can write an. xlsx file using the following code:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save("05featuredemo.xlsx");

Formula pre calculation

By default, the author precomputes all formulas in the spreadsheet. On large spreadsheets, this can be slow or even unnecessary. However, you can disable formula precomputation:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->setPreCalculateFormulas(false);
$writer->save("05featuredemo.xlsx");

Office 2003 compatibility pack

Due to errors in the Office 2003 compatibility package, there may be some minor problems (mainly related to formula calculation) when opening the Xlsx spreadsheet. You can use the following code to enable office 2003 compatibility:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->setOffice2003Compatibility(true);
$writer->save("05featuredemo.xlsx");

The Office 2003 compatibility option should only be used when required, because it disables multiple Office 2007 file format options, resulting in a less functional office 2007 spreadsheet.

Excel 5 (BIFF) file format

xls file format is an old excel file format, which has been implemented in PhpSpreadsheet to provide a unified way to create. xlsx and. xls files. It's basically PEAR spreadsheet_ Excel_ A modified version of writer, although it has been extended and has fewer limitations and more functionality than the old PEAR library. This can read all BIFF versions using OLE2 through BIFF8: BIFF5 (introduced by Office 95), but not earlier versions.

The Xls file format will no longer be developed, it just provides other file formats for PhpSpreadsheet.

Excel5 (BIFF) limitations note that the BIFF file format has some limitations in styling cells and processing large spreadsheets through PHP.

\PhpOffice \ PhpSpreadsheet \ Reader \ Xls reading spreadsheets

You can read the. xls file using the following code:

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
$spreadsheet = $reader->load("05featuredemo.xls");

Read data only

You can set the option setReadDataOnly on the reader to instruct the reader to ignore styles, data validation, and so on, and only read cell data:

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load("05featuredemo.xls");

Read only specific worksheets

You can set the option setLoadSheetsOnly on the reader to instruct the reader to load only sheets with the given name:

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
$reader->setLoadSheetsOnly(["Sheet 1", "My special sheet"]);
$spreadsheet = $reader->load("05featuredemo.xls");

Read only specific cells

You can set the option setReadFilter on the reader to indicate that the reader only loads cells that match the given rule. The read filter can be any implemented class \ PhpOffice\PhpSpreadsheet\Reader\IReadFilter. By default, use read all cells \ PhpOffice\PhpSpreadsheet\Reader\DefaultReadFilter.

The following code will read only lines 1 and 20 to 30 of any worksheet in the Excel file:

class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter {

    public function readCell($column, $row, $worksheetName = '') {
        // Read title row and rows 20 - 30
        if ($row == 1 || ($row >= 20 && $row <= 30)) {
            return true;
        }
        return false;
    }
}

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
$reader->setReadFilter( new MyReadFilter() );
$spreadsheet = $reader->load("06largescale.xls");

\PhpOffice \ PhpSpreadsheet \ Writer \ Xls write spreadsheet

You can write an. xls file using the following code:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet);
$writer->save("05featuredemo.xls");

Excel 2003 XML file format

Excel 2003 XML file format is a file format that can be used in earlier versions of Microsoft Excel.

Excel 2003 XML restrictions note that the Excel 2003 XML format has some limitations in terms of styling cells and processing large spreadsheets through PHP.

\PhpOffice \ PhpSpreadsheet \ Reader \ Xml read spreadsheet

You can read the Excel 2003. XML file using the following code:

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xml();
$spreadsheet = $reader->load("05featuredemo.xml");

Read only specific cells

You can set the option setReadFilter on the reader to indicate that the reader only loads cells that match the given rule. The read filter can be any implemented class \ PhpOffice\PhpSpreadsheet\Reader\IReadFilter. By default, use read all cells \ PhpOffice\PhpSpreadsheet\Reader\DefaultReadFilter.

The following code will read only lines 1 and 20 to 30 of any worksheet in the Excel file:

class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter {

    public function readCell($column, $row, $worksheetName = '') {
        // Read title row and rows 20 - 30
        if ($row == 1 || ($row >= 20 && $row <= 30)) {
            return true;
        }
        return false;
    }

}

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xml();
$reader->setReadFilter( new MyReadFilter() );
$spreadsheet = $reader->load("06largescale.xml");

Symbolic LinK (SYLK)

Symlinks (SYLK) are Microsoft file formats that are commonly used to exchange data between applications, especially spreadsheets. SYLK files usually have a. slk suffix. It consists only of displayable ANSI characters and can be easily created and processed by other applications, such as databases.

SYLK restrictions note that the SYLK file format has some restrictions on styling cells and processing large spreadsheets through PHP.

\PhpOffice \ PhpSpreadsheet \ Reader \ Slk reading spreadsheets

You can read the. slk file using the following code:

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Slk();
$spreadsheet = $reader->load("05featuredemo.slk");

Read only specific cells

You can set the option setReadFilter on the reader to indicate that the reader only loads cells that match the given rule. The read filter can be any implemented class \ PhpOffice\PhpSpreadsheet\Reader\IReadFilter. By default, use read all cells \ PhpOffice\PhpSpreadsheet\Reader\DefaultReadFilter.

The following code will only read lines 1 and 20 to 30 of any worksheet in the SYLK file:

class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter {

    public function readCell($column, $row, $worksheetName = '') {
        // Read title row and rows 20 - 30
        if ($row == 1 || ($row >= 20 && $row <= 30)) {
            return true;
        }
        return false;
    }

}

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Slk();
$reader->setReadFilter( new MyReadFilter() );
$spreadsheet = $reader->load("06largescale.slk");

Open / free office (. ods)

Open Office or Libre office. ODS file is the standard file format of Open Office or Libre Office Calc file.

\PhpOffice \ PhpSpreadsheet \ Reader \ Ods reading spreadsheets

You can read the. ods file using the following code:

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Ods();
$spreadsheet = $reader->load("05featuredemo.ods");

Read only specific cells

You can set the option setReadFilter on the reader to indicate that the reader only loads cells that match the given rule. The read filter can be any implemented class \ PhpOffice\PhpSpreadsheet\Reader\IReadFilter. By default, use read all cells \ PhpOffice\PhpSpreadsheet\Reader\DefaultReadFilter.

The following code will only read lines 1 and 20 to 30 of any worksheet in the Calc file:

class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter {

    public function readCell($column, $row, $worksheetName = '') {
        // Read title row and rows 20 - 30
        if ($row == 1 || ($row >= 20 && $row <= 30)) {
            return true;
        }
        return false;
    }

}

$reader = new PhpOffice\PhpSpreadsheet\Reader\Ods();
$reader->setReadFilter( new MyReadFilter() );
$spreadsheet = $reader->load("06largescale.ods");

CSV (comma separated values)

CSV (comma separated values) is often used as the import / export file format with other systems. PhpSpreadsheet allows you to read and write CSV files.

CSV restrictions please note that there are some restrictions on the format of CSV file in terms of style cell, number format, etc.

\PhpOffice \ PhpSpreadsheet \ Reader \ Csv read CSV file

You can read the. csv file using the following code:

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
$spreadsheet = $reader->load("sample.csv");

Set CSV options

CSV files are usually not really "comma separated," or use semicolons (;) as delimiters. You can \ PhpOffice\PhpSpreadsheet\Reader\Csv indicate some options before reading the CSV file.

Delimiters are automatically detected, so in most cases, you do not need to specify a delimiter. However, if automatic detection is not appropriate for use cases, it can be set manually.

Note that \ PhpOffice\PhpSpreadsheet\Reader\Csv by default assumes that the loaded CSV file is UTF-8 encoded. If you are reading a CSV file created in Microsoft Office Excel, the correct input encoding may be Windows-1252 (CP1252). Always make sure that the input encoding is set correctly.

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
$reader->setInputEncoding('CP1252');
$reader->setDelimiter(';');
$reader->setEnclosure('');
$reader->setSheetIndex(0);

$spreadsheet = $reader->load("sample.csv");

Read a specific worksheet

A CSV file can contain only one worksheet. Therefore, you can specify the worksheet to read from CSV:

$reader->setSheetIndex(0);

Read in existing spreadsheet

When using a CSV file, there may be situations where you want to import CSV data into an existing spreadsheet object. The following code loads the CSV file into an existing file containing some worksheets in $spreadsheet and imports it into the sixth worksheet:

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
$reader->setDelimiter(';');
$reader->setEnclosure('');
$reader->setSheetIndex(5);

$reader->loadIntoExisting("05featuredemo.csv", $spreadsheet);

\PhpOffice \ PhpSpreadsheet \ Writer \ Csv write CSV file

You can write a. csv file using the following code:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
$writer->save("05featuredemo.csv");

Set CSV options

CSV files are usually not really "comma separated," or use semicolons (;) as delimiters. You can \ PhpOffice\PhpSpreadsheet\Writer\Csv indicate some options before writing a CSV file:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
$writer->setDelimiter(';');
$writer->setEnclosure('');
$writer->setLineEnding("\r\n");
$writer->setSheetIndex(0);

$writer->save("05featuredemo.csv");

Write a specific worksheet

A CSV file can contain only one worksheet. Therefore, you can specify the worksheet to write to CSV:

$writer->setSheetIndex(0);

Formula pre calculation

By default, the author precomputes all formulas in the spreadsheet. On large spreadsheets, this can be slow or even unnecessary. However, you can disable formula precomputation:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
$writer->setPreCalculateFormulas(false);
$writer->save("05featuredemo.csv");

Write to UTF-8 CSV file

The CSV file is written in UTF-8. If they do not contain characters outside the ASCII range, no other action is required. However, if the file contains such characters, you should explicitly include the BOM file header; if not, Excel will not interpret them correctly. You can enable this feature using the following code:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
$writer->setUseBOM(true);
$writer->save("05featuredemo.csv");

Decimal and thousands separator

If the worksheet you are exporting contains numbers with decimal or thousand separator characters, you should consider which characters to use before exporting.

By default, PhpSpreadsheet looks in the server's locale to determine which characters to use. However, to avoid problems, it is recommended to explicitly set the characters as follows.

English users will use it before exporting:

\PhpOffice\PhpSpreadsheet\Shared\StringHelper::setDecimalSeparator('.');
\PhpOffice\PhpSpreadsheet\Shared\StringHelper::setThousandsSeparator(',');

German users will use the opposite value.

\PhpOffice\PhpSpreadsheet\Shared\StringHelper::setDecimalSeparator(',');
\PhpOffice\PhpSpreadsheet\Shared\StringHelper::setThousandsSeparator('.');

Notice that the code above sets the decimal and thousand separator as global options. This also affects how HTML and PDF are exported.

HTML

PhpSpreadsheet allows you to read or write spreadsheets in HTML format so that you can quickly represent the data to anyone who does not have a spreadsheet application on your PC, or load files created by other scripts that simply create HTML tags and provide them with. xls file extensions.

HTML restrictions please note that HTML file format has some restrictions on style cell, number format, etc.

\PhpOffice \ PhpSpreadsheet \ Reader \ HTML read spreadsheet

You can read. html or. htm files using the following code:

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Html();

$spreadsheet = $reader->load("05featuredemo.html");

HTML restrictions note that HTML readers are still in the experimental stage and do not support clean merged cells or nested tables

\ PhpOffice \ PhpSpreadsheet \ Writer \ Html

Note that \ PhpOffice\PhpSpreadsheet\Writer\Html only outputs the first sheet by default.

Writing spreadsheets

You can write an. htm file using the following code:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);

$writer->save("05featuredemo.htm");

Write all worksheets

An HTML file can contain one or more worksheets. If you want to write all worksheets to an HTML file, use the following code:

$writer->writeAllSheets();

Write a specific worksheet

An HTML file can contain one or more worksheets. Therefore, you can specify the worksheet to write to HTML:

$writer->setSheetIndex(0);

Set the picture root of HTML file
In some cases, you need to explicitly set the root of the included image. For example, instead of:

html
 <img src="./images/logo.jpg">

You may want to see:

<img src="http://www.domain.com/images/logo.jpg">

You can use the following code to achieve this result:

$writer->setImagesRoot('http://www.example.com');

Formula pre calculation

By default, the author precomputes all formulas in the spreadsheet. On large spreadsheets, this can be slow or even unnecessary. However, you can disable formula precomputation:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
$writer->setPreCalculateFormulas(false);

$writer->save("05featuredemo.htm");

Embed the generated HTML into the web page

In some cases, you may want to embed the generated HTML into an existing site. \PhpOffice \ PhpSpreadsheet \ Writer \ Html supports generating only specific parts of HTML code so that you can use them in your web site.

Supported methods:

generateHTMLHeader()
generateStyles()
generateSheetData()
generateHTMLFooter()
generateHTMLAll()

This is an example that retrieves all parts independently and merges them into the resulting HTML page:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
$hdr = $writer->generateHTMLHeader();
$sty = $writer->generateStyles(false); // do not write <style> and </style>
$newstyle = <<<EOF
<style type='text/css'>
$sty
html {
    background-color: yellow;
}
</style>
EOF;
echo preg_replace('@</head>@', "$newstyle\n</head>", $hdr);
echo $writer->generateSheetData();
echo $writer->generateHTMLFooter();

Write UTF-8 HTML file

By writing the BOM file header, you can mark the HTML file as UTF-8. You can enable this feature using the following code:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
$writer->setUseBOM(true);

$writer->save("05featuredemo.htm");

Decimal and thousands separator

See the section \ PhpOffice\PhpSpreadsheet\Writer\Csv how to control their appearance.

PDF format

PhpSpreadsheet allows you to write spreadsheets to PDF format to quickly distribute the data represented.

Pdf restrictions please note that there are some restrictions on PDF file format in terms of style cell, number format, etc.

\ PhpOffice \ PhpSpreadsheet \ Writer \ Pdf

PhpSpreadsheet's PDF Writer is a wrapper for third-party PDF rendering libraries such as TCPDF, mPDF, or Dompdf. You must now install the PDF rendering library yourself. But PhpSpreadsheet can be used with many different libraries.

Currently, the following libraries are supported:

Different libraries have different advantages and disadvantages. Some generate better format output than others, some faster or use less memory than others, and some generate smaller. pdf files. What they want to use according to their own situation is the choice of developers.

You can instantiate the writer with its specific name as follows:

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Mpdf');

Alternatively, you can register the writer you are using with a more generic name, so you don't have to remember which library you chose, just write a PDF file:

$class = \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf::class;
\PhpOffice\PhpSpreadsheet\IOFactory::registerWriter('Pdf', $class);
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Pdf');

Or, you can instantiate the author of your choice directly like this:

$writer = \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet);

Custom implementation or configuration

If you need a custom implementation or custom configuration of a supported PDF library. You can extend the PDF library and PDF writer as follows:

class My_Custom_TCPDF extends TCPDF
{
    // ...
}

class My_Custom_TCPDF_Writer extends \PhpOffice\PhpSpreadsheet\Writer\Pdf\Tcpdf
{
    protected function createExternalWriterInstance($orientation, $unit, $paperSize)
    {
        $instance = new My_Custom_TCPDF($orientation, $unit, $paperSize);

        // more configuration of $instance

        return $instance;
    }
}

\PhpOffice\PhpSpreadsheet\IOFactory::registerWriter('Pdf', MY_TCPDF_WRITER::class);

Writing spreadsheets

Once you have determined which renderer to use for pdf generation, you can write a. pdf file using the following code:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet);
$writer->save("05featuredemo.pdf");

Note that \ PhpOffice\PhpSpreadsheet\Writer\Pdf only outputs the first sheet by default.

Write all worksheets

A PDF file can contain one or more worksheets. If you want to write all worksheets to a PDF file, use the following code:

$writer->writeAllSheets();

Write a specific worksheet

A PDF file can contain one or more worksheets. Therefore, you can specify a worksheet to write to PDF:

$writer->setSheetIndex(0);

Formula pre calculation

By default, the author precomputes all formulas in the spreadsheet. On large spreadsheets, this can be slow or even unnecessary. However, you can disable formula precomputation:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet);
$writer->setPreCalculateFormulas(false);

$writer->save("05featuredemo.pdf");

Decimal and thousands separator

See the section \ PhpOffice\PhpSpreadsheet\Writer\Csv how to control their appearance.

Generate Excel file from template (read, modify, write)

The reader writer is a tool that enables you to generate Excel files from templates. This requires less coding than building Excel files from scratch, especially if your template has many styles, page setup properties, titles, and so on.

This is an example of how to open a template file, fill in several fields and save again:

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('template.xlsx');

$worksheet = $spreadsheet->getActiveSheet();

$worksheet->getCell('A1')->setValue('John');
$worksheet->getCell('A2')->setValue('Smith');

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('write.xls');

Note that you can load an xlsx file and generate an xls file.

Generate Excel files from HTML content

If you want to generate Excel files from pre rendered HTML content, you can use HTML Reader to do so automatically. This feature is useful when you generate Excel files from Web application content that will be downloaded / sent to users.

For example:

$htmlString = '<table>
                  <tr>
                      <td>Hello World</td>
                  </tr>
                  <tr>
                      <td>Hello<br />World</td>
                  </tr>
                  <tr>
                      <td>Hello<br>World</td>
                  </tr>
              </table>';

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Html();
$spreadsheet = $reader->loadFromString($htmlString);

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('write.xls'); 

Suppose you have multiple worksheets to create from html. This can be achieved as follows.

$firstHtmlString = '<table>
                  <tr>
                      <td>Hello World</td>
                  </tr>
              </table>';
$secondHtmlString = '<table>
                  <tr>
                      <td>Hello World</td>
                  </tr>
              </table>';

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Html();
$spreadsheet = $reader->loadFromString($firstHtmlString);
$reader->setSheetIndex(1);
$spreadhseet = $reader->loadFromString($secondHtmlString, $spreadsheet);

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('write.xls');

Tags: Excel xml less PHP

Posted on Fri, 05 Jun 2020 03:18:37 -0400 by Syn