#phpexcel

Leer un archivo de Excel .xlsx con PHPExcel

PHPExcel es una librería que nos permite leer y escribir archivos, en este ejemplo veremos como leer un archivo .xlsx. (more…)

Hướng dẫn đọc file và ghi file excel bằng PHP

Trong bất kì một ứng dụng PHP nào, chúng ta cũng cần phải có ít nhất một lần đụng tới việc trích xuất dữ liệu hoặc đọc dữ liệu từ file excel, chẳng hạn như báo cáo doanh thu bán hàng, ngân hàng, danh sách nhân viên, danh sách khách hàng,… Rất rất nhiều thứ phải cần đến đến việc đọc và ghi file excel bằng PHP.

Do đó, trong bài viết ngày hôm nay, mình sẽ hướng dẫn các bạn làm được điều này, bằng…

Programmatic

You can programmatically set the vertical alignment of a cell like this:

$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

This code will vertically center the text in a cell. You can replace VERTICAL_CENTER with VERTICAL_TOP (to top-align), VERTICAL_BOTTOM (to bottom-align), or VERTICAL_JUSTIFY.

Note that getStyleByColumnAndRow($col, $row) uses the numeric column/row equivalent. I use this because all of my PHPExcel code increments with numbers rather than alpha characters. If you are using the standard nomenclature, you can just use getStyle(’A2′) instead of getStyleByColumnAndRow.

Style arrays

If you are using style arrays, you can add this attribute:

'alignment' => array('vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER )

Style arrays are handy because they can be defined in one place and re-used throughout your PHPExcel document.

And of course, you apply your style array like this:

Yesterday, on a client's request, I made my first Excel file with PHP. I'm one of those guys who is reluctant to trust any library and having to use PHPExcel stank of defeat.

I'm happy to say that I was very positively surprised here, just a few lines into the quick start guide on their Github page I noticed how clean the example code looked and how perfectly readable it was.

But that's not all about PHPExcel, it integrated perfectly with Netbeans. It's perfectly commented with Docblocks and therefore allows most modern IDE's to generate tool-tips.

Just will leave a small piece of code here:

<?php $excel = new PHPExcel(); $excel->setActiveSheetIndex(0); $excel->getActiveSheet()->setCellValue('A1', 'PHP Excel'); $excel->getActiveSheet()->setCellValue('A2', 'I love it!');

Ok so if you haven't heard there is a great project called phpexcel. Now this has many functionality but I'll just cover this well because I had to do it and didn't know hehe. This is fairly easy(once you've figured it out)..

First we reference 2 files: PHPExcel.php and IOFactory.php. Important! Don't touch these files unless you're a complete expert.

//include the following 2 files require '../class/PHPExcel.php'; require_once '../class/PHPExcel/IOFactory.php';

Now we start we create an object from the file in this example $path is my filename that is in the server like this:

$path = "filefolder/myfile.xls";

So once we do that you can capture every detail of the file here I have Title, Highest Row, Highest Column, Highest ColumnIndex

$objPHPExcel = PHPExcel_IOFactory::load($path); foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { $worksheetTitle = $worksheet->getTitle(); $highestRow = $worksheet->getHighestRow(); // e.g. 10 $highestColumn = $worksheet->getHighestColumn(); // e.g 'F' $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

With that info you can get the total number of columns

$nrColumns = ord($highestColumn) - 64;

Now you can start displaying or saving to your db table. What I do here is show the user the worksheet title, how many columns and how many rows were imported.

echo "<br>File ".$worksheetTitle." has "; echo $nrColumns . ' columns'; echo ' y ' . $highestRow . ' rows.';

Now just so the user feels warm and fuzzy I go ahead and show them the data being imported by echoing line by line.

echo '<br>Data: <table width="100%" cellpadding="3" cellspacing="0"><tr>'; for ($row = 1; $row <= $highestRow; ++ $row) { echo '<tr>'; for ($col = 0; $col < $highestColumnIndex; ++ $col) { $cell = $worksheet->getCellByColumnAndRow($col, $row); $val = $cell->getValue(); if($row === 1) echo '<td style="background:#000; color:#fff;">' . $val . '</td>'; else echo '<td>' . $val . '</td>'; } echo '</tr>'; } echo '</table>';

Ok and finally(I know you've been waiting for this. Here is my insert into statement. I start at row 2 because obviously row 1 is your column headers. Note: if you don't have headers then just use 1.

for ($row = 2; $row <= $highestRow; ++ $row) {

Now I create an array to hold the value of each column.

$val=array(); for ($col = 0; $col < $highestColumnIndex; ++ $col) { $cell = $worksheet->getCellByColumnAndRow($col, $row); $val[] = $cell->getValue(); }

And last but not least we insert.

$sql="insert into tablename(column1, column2, column3, column4, column5, column6) values('".$val[1] . "','" . $val[2] . "','" . $val[3]. "','" . $val[4]. "','" . $val[5]. "','" . $val[6]. "')"; //Run your mysql_query } } }

And just like that you went from excel -> mysql and a nice display for the user :)

Important: you must make sure that your excel files has the same amount of columns as your insert statement, so I would recommend giving them a template to download first.

Now so you don't think I'm some sort of super genious I actually had some help from Kaosforge Great site if you're stuck.