To upload Excel and store it in database?

问题:

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;
        }

回答1:

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:

1. Create a table for the entity (excel file): EntityID, ExcelFileName
2. Create a table for the attribute (column info): AttributeID, EntityID, AttributeName
3. 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.

回答2:

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?

回答3:

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.

回答4:

 <form action="<?= base_url();?>Asset_controller/asset" method="POST" enctype="multipart/form-data">
<input type="file" class="form-control" name="xlxx" id="file_asset" required  accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel">
</form>
    $file=$_FILES['xlxx'];
            $time=time();
            $dir_upload = 'public/images/'; //where you are uploading file 
            $new_name = $time . '-' . $file['name'];
            $path= $dir_upload.$new_name;
            $fileTypes = array('xls','xlsx'); // File extensions
            $fileParts = pathinfo(strtolower($file['name']));
            if (in_array($fileParts['extension'],$fileTypes)) 
            {
              $ok= move_uploaded_file($file['tmp_name'],$path);
                if ($ok) {}
                else
                {
                    $this->session->set_flashdata('err_msg','Check File extension');
                    redirect('Asset_controller/asset_check_inout');
                }
            }
            $fname=$path;

          $xlreader=PHPExcel_IOFactory::createReaderForFile($fname); //creating reader for reading purpose
          $xlobj=$xlreader->load($fname);// creating object by loading the given file

          $sheet=$xlobj->getSheet(0);
          $rows=$sheet->getHighestRow();

          $col=$sheet->getHighestColumn();
          $cols=PHPExcel_Cell::columnIndexFromString($col);
          $xlsheet=$sheet->toArray(null,true,true,false);
          $com=0;
          $trans=0;
          $this->db->trans_begin();
           for($k=1;$k<$rows;$k++)
          {
    $data=array(
                            'asset_name'=>$xlsheet[$k][0], //column name=>excel sheets column
                            'serial_no'=>$xlsheet[$k][1],
                            'invoice_number'=>$xlsheet[$k][2],
                            'invoice_dt'=>$xlsheet[$k][3],
                            'model'=>$xlsheet[$k][4],
                            'service_tag'=>$xlsheet[$k][5],
                            'amount'=>$xlsheet[$k][6],
                            'asset_desc'=>$xlsheet[$k][7],
                            'org_id'=>$xlsheet[$k][8],
                        );
                $this->db->insert('tablename',$data);
                 $trans=++$trans;
                 if ($this->db->affected_rows())
                    {   
                        $com=++$com;
                    }
          }         
            if ($com==$trans)
                    {
                        $this->db->trans_commit();
                        $this->session->set_flashdata('succ_msg',"Upload Complete,<b> $com </b>records updated");
                        redirect('Asset_controller/asset');//path
                    }
                    else
                    {  
                        $this->db->trans_rollback();
                        $this->session->set_flashdata('err_msg','DataBase Problem');
                        redirect('Asset_controller/asset');//path
                    }




        }