Import excel files with image in php/mysql

问题:

I want to make an import script which allows users to upload their excel file (extension not important) to my php application.

The application should reconize a list of items (so far so good).

The difficulty in this case is that the excel files contain images...

I've read information about phpexcel library but it does not say anything about images.

Anybody ideas?

回答1:

You can access images by PHPExcel library.

For importing images:

$objPHPExcel = PHPExcel_IOFactory::load("MyExcelFile.xls");

foreach ($objPHPExcel->getSheetByName("My Sheet")->getDrawingCollection() as $drawing) {
    if ($drawing instanceof PHPExcel_Worksheet_MemoryDrawing) {
        ob_start();
        call_user_func(
            $drawing->getRenderingFunction(),
            $drawing->getImageResource()
        );
        $imageContents = ob_get_contents();
        ob_end_clean();
    }
}

Then $imageContents will contain the image data that you can output or save as a file etc. You can see this question: PHPExcel: How to insert an image in the first page header and enlarge it to fit it's content?

For accessing images:

ArrayObject of all the image objects in the active worksheet will return by:

$objPHPExcel->getActiveSheet()->getDrawingCollection() ;

These objects will be either PHPExcel_Worksheet_Drawing or PHPExcel_Worksheet_MemoryDrawing objects: you can identify which using is_a(). You can then use the methods appropriate to that class (as described in the API) either to read the image data from file (for PHPExcel_Worksheet_Drawing objects) or directly from the PHPExcel_Worksheet_MemoryDrawing object itself. The getName() and getDescription() methods can be used to retrieve the relevant values fro the image object.

Now it's also possible to have image objects associated with print headers:

$objPHPExcel->getActiveSheet()->getHeaderFooter()->getImages() 

can be used to retrieve images from the header/footer. This is an array of PHPExcel_Worksheet_HeaderFooterDrawing objects. All the PHPExcel_Worksheet_Drawing methods can be used to extract the image file from these objects.

回答2:

To import data from Excel (or any other program that can produce a text file) is very simple using the LOAD DATA command from the MySQL Command prompt.

1) Save your Excel data as a csv file (In Excel 2007 using Save As) Check the saved file using a text editor such as Notepad to see what it actually looks like, i.e. what delimiter was used etc.

2) LOAD DATA LOCAL INFILE ‘C:\temp\yourfile.csv’ INTO TABLE database.table FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ (field1, field2);

3) To store image to have to use TO_BASE64

Done!

Reference

回答3:

PHPExcel does support images, although it doesn't yet support charts

EDIT

Chart reader support added for Excel 2007+ .xlsx files in version 1.7.7 of PHPExcel