Asked  7 Months ago    Answers:  5   Viewed   28 times

I tried to search for some plugins to import Excel file into MySQL database, one of them is http://code.google.com/p/php-excel-reader/

The tool is so powerful that it displays the entire excel content into html.

However, I think I just need to read the Excel file and extract the contents, for example, into an array, and then write a SQL statement for entering into the database.

Would there be any good codes and packages? Thanks!

 Answers

59

This is best plugin with proper documentation and examples

https://github.com/PHPOffice/PHPExcel

Plus point: you can ask for help in its discussion forum and you will get response within a day from the author itself, really impressive.

Wednesday, March 31, 2021
 
uiroshan
answered 7 Months ago
18

As told by questioning user Tiny on his own comment:

Finally, it worked. I just added exit at the end of my PHP script (at the end of the first code snippet in the question). Thanks very much all of you for giving me useful hints.

To give some constructive additional tips on this kind of problems:

  • A good tip is that you can omit the closing tag ?> on all your PHP script files, that way you know that you're not sending any aditional invisible whitespace at the end of it.
  • Also enconding PHP script files on UTF-8 on wrongfully configured web server can send an undesirable couple bytes at the begining of the script. Open the PHP file on Notepad++, check if it's UTF-8, and in that case, convert it to ANSI. If that makes it work, check your webserver/php configuration.
  • Just before the header calls, you can check if headers has been wrongfully sent with:

    if ( headers_sent() ) die("**Error: headers sent");

  • If you can't prevent that some function call sends undesirable strings to the browser, you can "erase" all of it using at the very beginning of your script:

    ob_start();

    and then, just before the first headers call, use:

    ob_clean();

    Be careful that with doing so will prevent you for receiving error feedback.

  • And lastly, as already said, if nothing has to be executed afterwards some point on the script, call exit or die.

Wednesday, March 31, 2021
 
ajaybc
answered 7 Months ago
16

You should create an array and store it in the database like this for example:

for ($row = 2; $row <= $highestRow; ++ $row) {
$val=array()
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
    $cell = $worksheet->getCellByColumnAndRow($col, $row);
    $val[] = $cell->getValue();
    //here's my prob..
    //echo $val;
}

$sql="insert into tablename (col1, col2, col3) values(`".$val[0]."`, `".$val[1]."`, `".$val[2].")";
$result = mysql_query($sql);


}
Saturday, May 29, 2021
 
macha
answered 5 Months ago
98

You won't be able to export it as XLSX without going back to the server. A XLSX file is a collection of XML files, zipped together. This means you do need to create multiple files. This is impossible to do with JS, client-side.

Instead, you should create a function retrieving the data from your HTML table and send that to you server. The server can then create the XLSX file for you (there are a bunch of libs available for that!) and send it back to the client for download.

If you expect to have a huge dataset, the XLSX creation on the server should be done as an async process, where you notify the user when it's done (instead of having the user waiting for the file to be created).

Let us know which language you use on your server, and we'll be able to recommend you some good libraries.

Friday, June 25, 2021
 
Corsair
answered 4 Months ago
87

I think you need add parameters:

  • index_col for convert column to index
  • usecols - parse columns by positions
  • change header position to 15

energy=pd.read_excel('Energy Indicators.xls',
                     sheet_name='Energy',
                     skiprows=[17],
                     skipfooter=38,
                     header=15,
                     index_col=[0],
                     usecols=[2,3,4,5]
                    )
print (energy.head())

               Energy Supply Energy Supply per capita  
Afghanistan              321                       10   
Albania                  102                       35   
Algeria                 1959                       51   
American Samoa           ...                      ...   
Andorra                    9                      121   

                Renewable Electricity Production  
Afghanistan                            78.669280  
Albania                               100.000000  
Algeria                                 0.551010  
American Samoa                          0.641026  
Andorra                                88.695650  
Monday, August 30, 2021
 
kat_indo
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :