When processing excel table data and collecting statistics of the same item, we often need to export some order excel tables from amazon. The table contains several fields: material encoding, item name, unit price, what I want to do now is to upload the data in this table to the database, then combine the first 13 digits of the material code, and add the numbers accordingly, total unit price * the amount of processed data needs to be stored in the database. it is best to generate an excel file. ------ Process excel table data and count the same item
We often need to export some order excel tables from amazon, which contain several fields: material code, item name, unit price, quantity
What I want to do now is to upload the data in this table to the database, then combine the first 13 digits of the material code, and add the numbers accordingly. The total amount = unit price * quantity
The processed data must be stored in the database. it is best to generate an excel file.
------ Solution --------------------
You can also develop a small program on your own.
1. develop an excel Upload box to directly upload your excel table
2. use phpExcelReader to read the excel table and store it in the database:
3. the database makes the corresponding judgment and then exports the excel table through PHPExcel.
Note: phpExcelReader and PHPExcel can be downloaded from the Internet.
------ Solution --------------------
Is to operate phpexcel/phpexcelreader. The core is to merge rows with the same prefix.
The method is to traverse all rows. records with the same prefix are stored in an array, that is, the structure:
Array (
Prefix1 => array (row1, row2 ),
Prefix2 => array (row3, row4)
)
Merge the values corresponding to each prefix and write them into a new excel file.
------ Solution --------------------
The key is your SQL statement.
------ Solution --------------------
At least paste the data imported into the excel file. Describes the expected results. Mysql is used.
------ Solution --------------------
Try:
Select Field 2, sum (field 3), sum (field 5) into outfile './a.xls' from tt group by field 2;
------ Solution --------------------
SELECT *, SUM (quantity) as a, SUM (amount) as B FROM tablename GROUP BY field 2
