将XLSX文件导入PHP数组

monwx1rj  于 2023-02-03  发布在  PHP
关注(0)|答案(6)|浏览(118)

是否可以将XLSX文件的每一行导入到PHP数组的一行中?

ne5o7dgx

ne5o7dgx1#

您可以使用PHPExel,它可以从这里获得:https://phpexcel.codeplex.com/releases/view/119187
下面是我用来将xlsxlsx读取到数组中的代码:

require_once('/path/to/PHPExcel.php');

$filename = "example.xlsx";
$type = PHPExcel_IOFactory::identify($filename);
$objReader = PHPExcel_IOFactory::createReader($type);
$objPHPExcel = $objReader->load($filename);

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $worksheets[$worksheet->getTitle()] = $worksheet->toArray();
}

print_r($worksheets);

更新日期/ 2022年2月13日:

PhpSpreadsheet已经推出几年了,并取代了PHPExel。下面的代码与上面的代码大致相同,只是有一些小的改进:
1.已将代码转换为函数或方法。
1.自动检测文件类型。
1.添加了指定如何处理空值、格式和公式的功能。
1.最重要的是,调用析构函数并清除内存。如果没有这最后一步,我在加载大文件后一直内存不足。

/**
 * Create a multidimensional array of worksheets from a filename.
 *
 * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
 * @param bool $calculateFormulas Should formulas be calculated?
 * @param bool $formatData Should formatting be applied to cell values?
 *
 * @return array
 */
function spreadsheet_to_array($nullValue = null, $calculateFormulas = true, $formatData = false) {
    $results = [];
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file);
    foreach ($spreadsheet->getWorksheetIterator() as $worksheet) {
        $results[$worksheet->getTitle()] = $worksheet->toArray($nullValue, $calculateFormulas, $formatData);
    }
    // save memory
    $spreadsheet->__destruct();
    $spreadsheet = NULL;
    unset($spreadsheet);
    return $results;
}
rm5edbpk

rm5edbpk2#

我用这个:

include 'simplexlsx.class.php';
$xlsx = @(new SimpleXLSX('myFile.xlsx'));
$data =  $xlsx->rows();

您可以从here中简化xslx。

    • 更新**

显然上面的链接已经失效了。你现在可以使用this了。(谢谢@Basti)

m1m5dgzv

m1m5dgzv3#

可以使用PHPExcel库解决问题:

$data = [];

$type = PHPExcel_IOFactory::identify($filepath);
$objReader = PHPExcel_IOFactory::createReader($type);

$objPHPExcel = $objReader->load($filepath);

$rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
foreach($rowIterator as $row){
    $cellIterator = $row->getCellIterator();
    foreach ($cellIterator as $cell) {
        $data[$row->getRowIndex()][$cell->getColumn()] = $cell->getCalculatedValue();
    }
}

其中$filepath -xls或xlsx文件的路径。

evrscar2

evrscar24#

是,使用phpspreadsheet

include 'vendor/autoload.php';
        if($_FILES["import_excel"]["name"] != '')
        {
            $allowed_extension = array('xls', 'csv', 'xlsx');
            $file_array = explode(".", $_FILES["import_excel"]["name"]);
            $file_extension = end($file_array);
            if(in_array($file_extension, $allowed_extension))
            {
                $file_name = time() . '.' . $file_extension;
                move_uploaded_file($_FILES['import_excel']['tmp_name'], $file_name);
                $file_type = \PhpOffice\PhpSpreadsheet\IOFactory::identify($file_name);
                $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($file_type);
                $spreadsheet = $reader->load($file_name);
                unlink($file_name);
                $data = $spreadsheet->getActiveSheet()->toArray();
                foreach($data as $row)
                {
                    $insert_data = array(
                        ':test1'          =>  $row[0],
                        ':test2'          =>  $row[1],
                        ':test3'          =>  $row[2],
                        ':test4'          =>  $row[3]
                    );
                 };
                $query = "
                    INSERT INTO post
                    (  test1, test2, test3, test4)
                    VALUES
                    ( :test1, :test2, :test3, :test4)
                ";
                $statement = $connect->prepare($query);
                $statement->execute($insert_data);
             }
             echo "succes";
        }else{
           echo "only xls,csv,xlsx are allowed";
        }
sulc1iza

sulc1iza5#

使用新版本的PHPSpreadSheet,您可以简单地做到这一点:

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

/*...*/

$reader = new Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load('upload/file.xls');
$sheet = $spreadsheet->getSheet($spreadsheet->getFirstSheetIndex());
$data = $sheet->toArray();

只是要小心,你有所有的单元格作为值。例如,日期转换为整型,所以你需要转换它
您可以使用NumberFormat查看所有转换器。
将整数单元格转换为日期的示例:

use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
/*....*/
//$value is an integer of a cell value
$value = 44823
$stringDate = NumberFormat::toFormattedString($value, 'YYYY-MM-DD');
// 2022-09-19 is displayed
echo $stringDate;

在此找到:https://blog.programster.org/phpspreadsheet-read-excel-file-to-array
文档中的详细信息:https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-files/https://phpspreadsheet.readthedocs.io/en/latest/
NumberFormat的源代码:https://phpoffice.github.io/PhpSpreadsheet/classes/PhpOffice-PhpSpreadsheet-Style-NumberFormat.html

shyt4zoc

shyt4zoc6#

<?php
require_once 'SimpleXLSX.php';

if ( $xlsx = SimpleXLSX::parse('pricelist.xlsx') ) {
  print_r( $xlsx->rows() );
} else {
  echo SimpleXLSX::parseError();
}
?>

SimpleXLSX

相关问题