To upload Excel and store it in database?

Question

I want to upload an Excel file into our webpage, then corresponding data store it in database. And then I want to retrieve all data and display it in table format. I have one code but using that I can't upload all Excel files. Only a single format can be upload.

Below is the function. But there is some restriction.

 public function check_excel($filename)
        {   
            $path='./assets/uploads/excel/'.$filename;
            $this->load->library('excel');  
            $inputFileType = PHPExcel_IOFactory::identify($path);
            $objReader = PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = PHPExcel_IOFactory::load($path);
            $sheet = $objPHPExcel->getSheet(0); 
            $highestRow = $sheet->getHighestRow();
            $highestColumn = $sheet->getHighestColumn();
            $xf[]='';
            $result[]='';
            $first_check='';
            $var_check=0;

            for ($row = 13; $row <= $highestRow; $row++)
            {           
                $xf[$row]=$objPHPExcel->getActiveSheet()->getCell('A'.$row)->getXfIndex(); // Get sheet index value
                if($row>13 && $row<16) //This block check first kpi data expand or not
                { 
                    if($xf[$row-1]==$xf[$row]) //check parent and child sheet index value same 
                        $first_check='false';
                    if ($row==15) 
                    {
                        if($xf[$row]==$xf[$row-1] || $xf[$row]==$xf[$row-2]) // check the grand-child sheet index value same in parent and child
                            $first_check='false';
                        else
                        {   
                            $first_check='true';
                            $a=$row-2;
                            $b=$row-1;
                            $check_kpi=$objPHPExcel->getActiveSheet()->getCell('A'.$a)->getXfIndex(); 
                            $check_unit=$objPHPExcel->getActiveSheet()->getCell('A'.$b)->getXfIndex(); 
                            $check_sub_unit=$objPHPExcel->getActiveSheet()->getCell('A'.$row)->getXfIndex(); 
                        }
                    }       
                }
                if($first_check=='true') //This block check second kpi to upto last kpi data expand or not 
                {
                    if($row>15)
                    {
                        if($var_check==1) // This block check the child data expand or not
                        {
                            if($check_unit!=$objPHPExcel->getActiveSheet()->getCell('A'.$row)->getXfIndex())
                            {
                                $result[$row]='false';
                                break;
                            }
                        }
                        if($var_check==2) // this block check the grand - child data expand or not
                        {
                            if($check_sub_unit!=$objPHPExcel->getActiveSheet()->getCell('A'.$row)->getXfIndex())
                            {
                                $result[$row]='false';
                                break;                          
                            }
                        }
                        if($xf[$row]!=$check_sub_unit)
                        {
                            if($xf[$row]!=$check_unit)
                                $var_check=1; // var_check value is one, the kpi is present
                            else
                                $var_check=2; // var_check value is two, the unit is present
                        }
                        else
                            $var_check=0; // var_check value is zero, the sub_unit is present
                    }   
                }
                else if($first_check=='false')
                {
                    $result[$row]='false';
                    break;
                }           
            }
            $return='true';
            for ($row = 13; $row <= $highestRow; $row++)
            {
                if(!empty($result[$row]))
                {
                    if($result[$row]=='false'){
                        $return='false';
                        break;
                    }                   
                }
            }
            return $return;
        }

no error.I go through several code but in all the table structure is fixed.But i want something different.For eg one excel has 3 field like id,name,skills and another has 5 field etc — Adarsh M Pallickal, Jan 23 '14 at 13:00
If I am correct you need dynamic column count instead of getting the data from column A,B,C etc ? is it ? — Anish, Jan 23 '14 at 13:07
@aneesh yes exactly.And another pblm is in my code the data is strictly started from the cell 13 — Adarsh M Pallickal, Jan 24 '14 at 04:18
cool . In your current code it is reading the data from the 13th row of excel sheet. Also to get the data from the colum dynamically you have to use the variable $highestColumn. This will give the total no:of visible coulmns in that excel sheet. Let me know if you are clear. — Anish, Jan 24 '14 at 05:15
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/45971/discussion-between-aneesh-and-meenakshi) — Anish, Jan 24 '14 at 05:20

score 5 · Accepted Answer · answered Jan 30 '14 at 05:36

It sounds like you are using a relational DB (e.g. MySQL, Postgres, etc), which uses fixed column tables.

You should probably use a Document-based DB (e.g. CouchDB, Mongo, etc). This would be the best solution.

But, if you're stuck using a relational DB, you can use an EAV model.

Here is a basic example:

Create a table for the entity (excel file): EntityID, ExcelFileName
Create a table for the attribute (column info): AttributeID, EntityID, AttributeName
Create a table for the value (excel row/column): ValueID, RowNumber, AttributeID, AttributeValue

The downside is that the AttributeValue isn't specifically typed (it's just varchar/text). You can solve this by adding a "AttributeType" to the attribute table that is then used in your code to know what type of data that column should contain. BUT, unless you know the contents/format of the Excel file in advance, you'll probably have to GUESS what the type of a column is...which isn't hard as long as the excel file isn't messed up.

If you're just displaying the data that was imported, this probably isn't a big deal.

There are other (more complex) ways to implement EAV, including one with typed columns, if you have such a need.

score 4 · Answer 2 · edited May 23 '17 at 10:34

4

Have you tried PHPExcel?

They also have a codeigniter library.

And this post might interest you : how to use phpexcel to read data and insert into database?

edited May 23 '17 at 10:34

Community

1
1

answered Jan 28 '14 at 09:36

Dvir Levy

8,018
11
39
60

But using this we can upload static means single format excel files – Adarsh M Pallickal Jan 28 '14 at 11:12
I'm not sure I understand what you mean by "single format excel files". – Dvir Levy Jan 28 '14 at 13:00
That is i have 3 excel files. one containing employee details.One contain machine details and last one contain organ details.And these are in different structure that is different column heading, etc.But i want to upload these 3 and save it in database dynamically – Adarsh M Pallickal Jan 29 '14 at 13:06

George Sovetov · Answer 3 · 2014-01-31T16:15:17.297

0

You can use PHPExcel of course, but have a look at other data format. Using comma-separated or tab-separated values can help you to solve your problem easily. Excel can save datasheets in these simple formats. Anyway, you cannot save formulas or conditional formatting in you database Moreover, it is much faster and robust and you can import CSV files with LOAD DATA INFILE query.

edited Jan 31 '14 at 16:15

answered Jan 31 '14 at 16:09

George Sovetov

4,942
5
36
57

To upload Excel and store it in database?

3 Answers3

Linked

Related