Php array to xlsx

Save array into xls file

I wish to save my 2 dimensional array into an .xls file. If use this code, the data is saved without leading zeroes:

$arr = array( array("01", "02", "03"), array("001", "02", "03"), array("00001", "02", "03"), ); // include PHPExcel library $objPHPExcel = new PHPExcel(); $objPHPExcel->getActiveSheet()->fromArray($arr); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save("file.xls"); 

. $objPHPExcel->getActiveSheet()->fromArray($arr); $objPHPExcel->getActiveSheet()->getStyle('A1:C3')->getNumberFormat()->setFormatCode("00000"); . 

How can I solve this problem? How can I save the original data from my array into an .xls file correctly?

1 Answer 1

Try setting the cells to the text format instead:

$objPHPExcel->getActiveSheet()->getStyle('A1:C3')->getNumberFormat()->setFormatCode('@'); 

or loop through the array, setting the format according to the string’s length ( ->setFormatCode(str_repeat(‘0’, strlen($arrElem)); , but that would be as silly as looping over the array, and explicitly setting each value:

$objPHPExcel->->getCell('A1')->setValueExplicit($arr[0][0], PHPExcel_Cell_DataType::TYPE_STRING); 

There might be a case for the blunt approach here, too:

$objPHPExcel->getDefaultStyle() ->getNumberFormat() ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); 

This should set the default format to txt.

What smokey (and I suggested) was using a number format for each iteration.

$col = range('A','C'); $cell = ''; foreach($arr as $colIndex => $vals) < $cell = $col[$colIndex];//the first array will be $col[0] === A foreach($vals as $row =>$val) < $cell .= $row +1;//A.(0+1) -->A1 $objPHPExcel->getCell($cell) ->getNumberFormat() ->setFormatCode(str_repeat('0', strlen($val))); //for 001, this is str_repeat('0',3) or '000' > > 

This formats the cells accordingly, to add the correct amount of leading zeroes. If all else fails, this, is a fairly good way of setting the correct formats

Источник

Export an Array of Arrays to Excel in php

I have an Array of arrays at the beginning of each sub array is the header of the column followed by integers that I want to populate the column. It looks something like this:

Array ( [0] => Array ( [0] => How was the Food? [1] => 3 [2] => 4 ) [1] => Array ( [0] => How was the first party of the semester? [1] => 2 [2] => 4 [3] => 0 ) ) 

3 Answers 3

Excel can open csv file directly . try

$array = Array ( 0 => Array ( 0 => "How was the Food?", 1 => 3, 2 => 4 ), 1 => Array ( 0 => "How was the first party of the semester?", 1 => 2, 2 => 4, 3 => 0 ) ); header("Content-Disposition: attachment; filename=\"demo.xls\""); header("Content-Type: application/vnd.ms-excel;"); header("Pragma: no-cache"); header("Expires: 0"); $out = fopen("php://output", 'w'); foreach ($array as $data) < fputcsv($out, $data,"\t"); >fclose($out); 

If you really want to export array to excel, have a look at PHPReport. I have written that class to simplify exporting with phpexcel. It supports xls and xlsx.

The best way of exporting array to excel is here.

 $data = array( '0' => array('Name'=> 'Parvez', 'Status' =>'complete', 'Priority'=>'Low', 'Salary'=>'001'), '1' => array('Name'=> 'Alam', 'Status' =>'inprogress', 'Priority'=>'Low', 'Salary'=>'111'), '2' => array('Name'=> 'Sunnay', 'Status' =>'hold', 'Priority'=>'Low', 'Salary'=>'333'), '3' => array('Name'=> 'Amir', 'Status' =>'pending', 'Priority'=>'Low', 'Salary'=>'444'), '4' => array('Name'=> 'Amir1', 'Status' =>'pending', 'Priority'=>'Low', 'Salary'=>'777'), '5' => array('Name'=> 'Amir2', 'Status' =>'pending', 'Priority'=>'Low', 'Salary'=>'777') ); $filename = time().".xls"; header("Content-Type: application/vnd.ms-excel"); header("Content-Disposition: attachment; filename=\"$filename\""); ExportFile($data); function ExportFile($records) < $heading = false; if(!empty($records)) foreach($records as $row) < if(!$heading) < // display field/column names as a first row echo implode("\t", array_keys($row)) . "\n"; $heading = true; >echo implode("\t", array_values($row)) . "\n"; > exit; > 

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Export data to Excel. PHP XLSX generator

License

shuchkin/simplexlsxgen

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

Export data to Excel XLSX file. PHP XLSX generator. No external tools and libraries.

Sergey Shuchkin sergey.shuchkin@gmail.com 2020-2023

Hey, bro, please ★ the package for my motivation 🙂 and donate for more motivation!

$books = [ ['ISBN', 'title', 'author', 'publisher', 'ctry' ], [618260307, 'The Hobbit', 'J. R. R. Tolkien', 'Houghton Mifflin', 'USA'], [908606664, 'Slinky Malinki', 'Lynley Dodd', 'Mallinson Rendel', 'NZ'] ]; $xlsx = Shuchkin\SimpleXLSXGen::fromArray( $books ); $xlsx->saveAs('books.xlsx'); // or downloadAs('books.xlsx') or $xlsx_content = (string) $xlsx 

The recommended way to install this library is through Composer. New to Composer?

This will install the latest supported version:

$ composer require shuchkin/simplexlsxgen

$data = [ ['Integer', 123], ['Float', 12.35], ['Percent', '12%'], ['Currency $', '$500.67'], ['Currency €', '200 €'], ['Currency ₽', '1200.30 ₽'], ['Currency (other)', ''], ['Currency Float (other)', ''], ['Datetime', '2020-05-20 02:38:00'], ['Date', '2020-05-20'], ['Time', '02:38:00'], ['Datetime PHP', new DateTime('2021-02-06 21:07:00')], ['String', 'Very long UTF-8 string in autoresized column'], ['Formula', 'SUM(B1:B2)'], ['Hyperlink', 'https://github.com/shuchkin/simplexlsxgen'], ['Hyperlink + Anchor', 'SimpleXLSXGen'], ['Internal link', 'Go to second page'], ['RAW string', "\0" . '2020-10-04 16:02:00'] ]; SimpleXLSXGen::fromArray($data)->saveAs('datatypes.xlsx');

$data = [ ['Normal', '12345.67'], ['Bold', '12345.67'], ['Italic', '12345.67'], ['Underline', '12345.67'], ['Strike', '12345.67'], ['Bold + Italic', '12345.67'], ['Hyperlink', 'https://github.com/shuchkin/simplexlsxgen'], ['Italic + Hyperlink + Anchor', 'SimpleXLSXGen'], ['Green', ''], ['Bold Red Text', ''], ['Size 32 Font', ''], ['Blue Text and Yellow Fill', ''], ['Border color', ''], ['Border style',''], ['Border sides', ''], ['Left', '12345.67'], ['Center', '12345.67'], ['Right', 'Right Text'], ['Center + Bold', 'Name'], ['Row height', ''], ['Top', ''], ['Middle + Center', ''], ['Bottom + Right', ''], ['MERGE CELLS MERGE CELLS MERGE CELLS MERGE CELLS MERGE CELLS', null], ['Word wrap', "Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book"], ]; SimpleXLSXGen::fromArray($data) ->setDefaultFont('Courier New') ->setDefaultFontSize(14) ->setColWidth(1, 35) ->mergeCells('A20:B20') ->saveAs('styles_and_tags.xlsx');

Prefix #0 cell value (use double quotes).

$PushkinDOB = '1799-07-06'; $data = [ ['Datetime as raw string', "\0".'2023-01-09 11:16:34'], ['Date as raw string', "\0".$PushkinDOB], ['Disable type detection', "\0".'+12345'], ['Insert greater/less them simbols', "\0".'20- short term: ], ]; SimpleXLSXGen::fromArray($data) ->saveAs('test_rawstrings.xlsx');

// Fluid interface, output to browser for download Shuchkin\SimpleXLSXGen::fromArray( $books )->downloadAs('table.xlsx'); // Fluid interface, multiple sheets Shuchkin\SimpleXLSXGen::fromArray( $books )->addSheet( $books2 )->download(); // Alternative interface, sheet name, get xlsx content $xlsx_cache = (string) (new Shuchkin\SimpleXLSXGen)->addSheet( $books, 'Modern style'); // Classic interface use Shuchkin\SimpleXLSXGen $xlsx = new SimpleXLSXGen(); $xlsx->addSheet( $books, 'Catalog 2021' ); $xlsx->addSheet( $books2, 'Stephen King catalog'); $xlsx->downloadAs('books_2021.xlsx'); exit(); // Autofilter $xlsx->autoFilter('A1:B10'); // Freeze rows and columns from top-left corner up to, but not including, // the row and column of the indicated cell $xlsx->freezePanes('C3'); // RTL mode // Column A is on the far right, Column B is one column left of Column A, and so on. Also, information in cells is displayed in the Right to Left format. $xlsx->rightToLeft(); // Set Meta Data Files // this data in propertis Files and Info file in Office $xlsx->setAuthor('Sergey Shuchkin ') ->setCompany('Microsoft ') ->setManager('Bill Gates ') ->setLastModifiedBy("Sergey Shuchkin ") ->setTitle('This is Title') ->setSubject('This is Subject') ->setKeywords('Keywords1, Keywords2, Keywords3, KeywordsN') ->setDescription('This is Description') ->setCategory('This is Сategory') ->setApplication('Shuchkin\SimpleXLSXGen')

 // array2excel.php if (isset($_POST['array2excel'])) < require __DIR__.'/simplexlsxgen/src/SimpleXLSXGen.php'; $data = json_decode($_POST['array2excel'], false); \Shuchkin\SimpleXLSXGen::fromArray($data)->downloadAs('file.xlsx'); return; > ?> html lang pl-s">en"> head> title>JS array to Exceltitle> head> script> function array2excel()  var books = [ ["ISBN", "title", "author", "publisher", "ctry"], [618260307, "The Hobbit", "J. R. R. Tolkien", "Houghton Mifflin", "USA"], [908606664, "Slinky Malinki", "Lynley Dodd", "Mallinson Rendel", "NZ"] ]; var json = JSON.stringify(books); var request = new XMLHttpRequest(); request.onload = function ()  if (this.status === 200)  var file = new Blob([this.response], type: this.getResponseHeader('Content-Type')>); var fileURL = URL.createObjectURL(file); var filename = "", m; var disposition = this.getResponseHeader('Content-Disposition'); if (disposition && (m = /"([^"]+)"/.exec(disposition)) !== null)  filename = m[1]; > var a = document.createElement("a"); if (typeof a.download === 'undefined')  window.location = fileURL; > else  a.href = fileURL; a.download = filename; document.body.appendChild(a); a.click(); > > else  alert("Error: " + this.status + " " + this.statusText); > > request.open('POST', "array2excel.php"); request.responseType = "blob"; request.setRequestHeader("Content-Type", "application/x-www-form-urlencoded"); request.send("array2excel pl-c1">+ encodeURIComponent(json)); > script> body> input type pl-s">button" onclick pl-s">array2excel()" value pl-s">array2excel" /> body> html>

ini_set('error_reporting', E_ALL ); ini_set('display_errors', 1 ); $data = [ ['Debug', 123] ]; Shuchkin\SimpleXLSXGen::fromArray( $data )->saveAs('debug.xlsx');

Источник

PHP create xls from array

It looks like you’re missing a line of code: you’re creating an object $writer , but then not actually doing anything with it.

4 Answers 4

Please try : As per official documentation, you first need to save the file with the object writer

Please let me know if this is what you wanted

setActiveSheetIndex(0); $doc->getActiveSheet()->fromArray($sheet, null, 'A1'); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="your_name.xls"'); header('Cache-Control: max-age=0'); // Do your stuff here $writer = PHPExcel_IOFactory::createWriter($doc, 'Excel5'); $writer->save('php://output'); ?> 

Hi guys this example saved my ass but it do not work in firefox, and safari do you know what can I do about it?

mimetype is wrong for Excel5 it should be application/vnd.ms-excel . application/vnd.openxmlformats-officedocument.spreadsheetml.sheet is for Excel2007 files with xlsx extension.

Sometimes we just can’t use an external library/class like PHPExcel, in this case, I recommend you to use HTML table and change headers as xls, you can create it easily from array, very simple example:

A1B1C1D1'; foreach ($sheet as $row) < $table.= ''. implode('', $row) . ''; > $table.= ''; header('Content-Encoding: UTF-8'); header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT"); header ("Cache-Control: no-cache, must-revalidate"); header ("Pragma: no-cache"); header ("Content-type: application/x-msexcel;charset=UTF-8"); header ("Content-Disposition: attachment; filename=productsExport.xls" ); echo $table; ?> 

Источник