Excel Uploading PHP

Import Excel File into MySQL Database using PHP

Database backup and restore is the most important part of maintaining software. Taking automatic periodical backup is a must for any project.

In case of unknown security flaws which may collapse your system. In such an unfortunate situation, it will be the holy grail to save you if you have a backup.

It is not only enough to take a backup by exporting your database. Also, we need to experiment with importing backups to ensure the restoration.

Backup files can be in any format. Many of the database clients support SQL, Excel or CSV format files to import external data.

We will create our own PHP database client only for importing Excel data into a database.

Restoring Excel backup into a database via programming will save us time. Let us study how to implement a quick restore by importing bulk data from Excel files. You may visit my earlier export data in CSV format if you want an export example.

This screenshot shows the output by displaying the list of imported rows from the database.

What is inside?

About this Excel import example

This example allows users to choose the import template via HTML form input. A sample Excel template is added with this downloadable resource.

With a valid file, the HTML form submitted will call PHP to process Excel parsing.

In PHP, the PHPSpreadsheet library is used to read the Excel file data and convert it into an array.

The code will execute the database insert row by row by iterating the array data.

File Structure

See the Excel import example’s file structure shown below. The vendor folder has the PHPSpreadsheet library and its dependencies.

I have given a sample import template with these files to experiment with the import operation.

The schema.sql has the script to create the target database. You can find the SQL script in the next section.

Database script

Import the following SQL script and create a database environment. Also, configure your database details in DataSource.php to make this example work.

-- -- Database: `import_excel` -- -- -------------------------------------------------------- -- -- Table structure for table `tbl_info` -- CREATE TABLE `tbl_info` ( `id` int(11) NOT NULL, `name` varchar(50) NOT NULL, `description` varchar(50) NOT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- -- Indexes for table `tbl_info` -- ALTER TABLE `tbl_info` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for table `tbl_info` -- ALTER TABLE `tbl_info` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;; 

Creating UI to upload import template

This HTML form with the file upload option is used to choose the Excel source. On submitting this form, the Excel file will be sent to the PHP to parse the data source. This file upload option will only allow the Excel files to choose using the accept attribute.

This code also contains the response HTML for displaying the message returned from PHP. This message is shown based on the type of response sent from PHP after Excel import.

Import Excel File into MySQL Database using PHP
 
 
 
 
Choose your file. Download excel template 
 
Import Excel and Save Data 
 
  
 
 
 ?>
 select($sqlSelect); if (! empty($result)) < ?>   ?>
 
 
Name
 
Description
 
 
  
 
 
 ?> 

Parse Excel data using PHPSpreadsheet

In this PHP code, I specified the array of allowed file types for the file-type validation in PHP. I validated the uploaded file type along with the array allowed type.

If a match is found, the code will execute other logic. Otherwise, it will return the error message to the user.

With successful validation, excel file parsing read and convert the data into an array. Each array index has an array of row data. After reading the non-empty row data, I ran the database insert and showed the response.

getConnection(); require_once ('vendor/autoload.php'); if (isset($_POST["import"])) < $allowedFileType = [ 'application/vnd.ms-excel', 'text/xls', 'text/xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ]; if (in_array($_FILES["file"]["type"], $allowedFileType)) < $targetPath = 'uploads/' . $_FILES['file']['name']; move_uploaded_file($_FILES['file']['tmp_name'], $targetPath); $Reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $spreadSheet = $Reader->load($targetPath); $excelSheet = $spreadSheet->getActiveSheet(); $spreadSheetAry = $excelSheet->toArray(); $sheetCount = count($spreadSheetAry); for ($i = 1; $i $description = ""; if (isset($spreadSheetAry[$i][1])) < $description = mysqli_real_escape_string($conn, $spreadSheetAry[$i][1]); >if (! empty($name) || ! empty($description)) < $query = "insert into tbl_info(name,description) values(. )"; $paramType = "ss"; $paramArray = array( $name, $description ); $insertId = $db->insert($query, $paramType, $paramArray); // $query = "insert into tbl_info(name,description) values('" . $name . "','" . $description . "')"; // $result = mysqli_query($conn, $query); if (! empty($insertId)) < $type = "success"; $message = "Excel Data Imported into the Database"; >else < $type = "error"; $message = "Problem in Importing Excel Data"; >> > > else < $type = "error"; $message = "Invalid File Type. Upload Excel File."; >> ?> 

Note: Download the PHPSpreadsheet and include it in the vendor directory.

Источник

Загрузка данных из Excel в MySQL с помощью PHP

Не так давно появилась задача – загрузить данные из Ecxel файла в базу сайта, и реализовать механизм так, чтобы быстро можно было обновлять эти данные, т.е. загружать новый файл. Решил попробовать парсер Excel-файлов phpExcelReader, но сколько не бился с ним, получал ошибку типа “… is not readable”. Поэтому решил использовать библиотеку PHPExcel.

Загружать будем прайс-лист:

Артикул	Наименование	Количество	Цена	Валюта	Единица
123456	Апельсин	20	50000	руб	килограмм
123457	Мандарин	20	54000	руб	килограмм
123458	Яблоко	50	23500	руб	килограмм

id

article

name

quantity

price

currency

unit

Скачав архив с библиотекой и открыв его вы увидите несколько папок и файлов – это описание и примеры использования библиотеки. Нам понадобится папка “Classes” – распаковываем архив и загружаем её, например, в корень сайта.

Итак, предположим, что форму загрузки мы сделали, библиотеку подключили, начнем писать скрипт обработки файла и загрузки в базу данных.

include 'db_conn.php'; // подключаемся к базе данных $loadfile = $_POST['file_name']; // получаем имя загруженного файла require_once $_SERVER['DOCUMENT_ROOT']."/Classes/PHPExcel/IOFactory.php"; // подключаем класс для доступа к файлу $objPHPExcel = PHPExcel_IOFactory::load($_SERVER['DOCUMENT_ROOT']."/uploads/".$loadfile); foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) // цикл обходит страницы файла < $highestRow = $worksheet->getHighestRow(); // получаем количество строк $highestColumn = $worksheet->getHighestColumn(); // а так можно получить количество колонок for ($row = 1; $row getCellByColumnAndRow(0, $row); //артикул $cell2 = $worksheet->getCellByColumnAndRow(1, $row); //наименование $cell3 = $worksheet->getCellByColumnAndRow(2, $row); //количество $cell4 = $worksheet->getCellByColumnAndRow(3, $row); //цена $cell5 = $worksheet->getCellByColumnAndRow(4, $row); //валюта $cell6 = $worksheet->getCellByColumnAndRow(5, $row); //единица измерения $sql = "INSERT INTO `price` (`article`,`name`,`quantity`,`price`,`currency`,`unit`) VALUES ('$cell1','$cell2','$cell3','$cell4','$cell5','$cell6')"; $query = mysql_query($sql) or die('Ошибка чтения записи: '.mysql_error()); > >

В принципе, второй цикл можно усовершенствовать, используя количество колонок, но у меня файл всегда будет стандартный, поэтому не вижу в этом смысла.

На CyberForum.ru нашел перевод документации к PHPExcel, который сделал SrgKord скачать можно тут: Документация разработчика PHPExcel (RUS).7z

Похожие записи

Источник

Import Excel File into MySQL Database using PHP

In this tutorial,I will learn you how to import excel file into mysql database in php.you can easy and simply import excel file into mysql database in php.

We always require to add data from admin panel like products, items, users, emails etc. If we have few records and require to add manually then it is ok, But if we have more data on excel file or csv file then it take long time to store data, At that time we can use direct import xls file or csv file into mysql database.

So, In this tutorial i am using Spreadsheet_Excel_Reader class for import csv file into php database. So you can do it by following few step. In this tutorial you have to do just as bellow file:

Step 1: Download Package

In first step we have to download PHP Excel Reader library from GitHub, So first let’s download from here :

After download extract it to your root folder and rename it to «library».

Step 2: Create db_config.php file

In this step, we have to create db_config.php file for database configuration, In this file you have to set database host, database username, database password, database name. This file will use for store data into database.

So, let’s create db_config.php file and put bellow code:

db_config.php

$dbHost = «localhost»;

$dbDatabase = «h_php»;

$dbPasswrod = «root»;

$dbUser = «root»;

$mysqli = new mysqli($dbHost, $dbUser, $dbPasswrod, $dbDatabase);

?>

Step 3: Create index.php file

In this step i am going to create index.php file in your root directory, in this file i created simply form using bootstrap, this way you have to just feel this form and click to «Submit» button. You can import excel file from file input button.

So let’s copy bellow code and put it into index.php file.

Upload

Download Demo File from here : Demo.ods

Step 4: Create excelUpload.php file

You have to create excelUpload.php file that will manage for import data into database, In this step we have to create uploads folder for store excel file into this file and then read that file.

So, let’s create excelUpload.php file and put bellow code:

excelUpload.php

require(‘library/php-excel-reader/excel_reader2.php’);

require(‘library/SpreadsheetReader.php’);

require(‘db_config.php’);

if(isset($_POST[‘Submit’])) <

$mimes = [‘application/vnd.ms-excel’,’text/xls’,’text/xlsx’,’application/vnd.oasis.opendocument.spreadsheet’];

if(in_array($_FILES[«file»][«type»],$mimes)) <

$uploadFilePath = ‘uploads/’.basename($_FILES[‘file’][‘name’]);

move_uploaded_file($_FILES[‘file’][‘tmp_name’], $uploadFilePath);

$Reader = new SpreadsheetReader($uploadFilePath);

$totalSheet = count($Reader->sheets());

echo «You have total «.$totalSheet.» sheets».

$html=»

«;

$html.=»

«;

/* For Loop for all sheets */

for($i=0;$i <$totalSheet;$i++)<

$Reader->ChangeSheet($i);

foreach ($Reader as $Row)

<

$html.=»

«;

$title = isset($Row[0]) ? $Row[0] : »;

$description = isset($Row[1]) ? $Row[1] : »;

$html.=»

«;

$html.=»

«;

$html.=»

«;

$query = «insert into items(title,description) values(‘».$title.»‘,'».$description.»‘)»;

$mysqli->query($query);

>

>

$html.=»

Title	Description
«.$title.»	«.$description.»

«;

echo $html;

echo «
Data Inserted in dababase»;

>else <

die(«
Sorry, File type is not allowed. Only Excel file.»);

>

>

?>

✌️ Like this article? Follow me on Twitter and Facebook. You can also subscribe to RSS Feed.

Источник

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.

.xls, .ods parser to SQL with PHP

License

alfhh/XLStoSQL

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

Tired of writting long lines of SQL code to create tables for a data base? Now, imagine typing all that data from an Excel file.. Yeah wasted time. Well not any more using this PHP module.

1. To create the SLQ snippet run «index.php»
2. Select the file and then click on «Upload»
3. Your SQL snippet will be printed!

WARNING YOU .XLS FILE WILL BE DELETED AFTER THE PROCESS, INSIDE THE PROJECT FOLDER

To know more about read the notes section

To correctly use this tool is necessary to follow this format: Link to an Excel file

-The file that is in charge of creating the SQL snippet is named fileHandler.php

-If you want to avoid deleting the .xls file remove line 152 of fileHandler.php

For reading the spreadsheet we use the «spreadsheet-reader» by Nuovo. Our creation is the production of the SQL lines using the reader from Nuovo. Here is the link to the original repo.

All of the code in this library is licensed under the MIT license. The Spreadsheet Reader code is owned by Nuovo.

About

.xls, .ods parser to SQL with PHP

Источник