要上传Excel并将其存储在数据库中？ [英] To upload Excel and store it in database?

问题描述

我要将Excel档案上传到我们的网页，然后将对应的资料储存在资料库中。然后我想检索所有数据并以表格格式显示。我有一个代码，但使用，我不能上传所有Excel文件。只有一种格式可以上传。

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

推荐答案

一个使用固定列表的关系数据库（例如MySQL，Postgres等）。

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

你应该使用基于文档的数据库（例如CouchDB，Mongo等） 。这将是最好的解决方案。

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

但是，如果你坚持使用关系数据库，你可以使用 EAV模型。

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

p>

Here is a basic example:

1. 为实体（excel文件）创建表：EntityID，ExcelFileName


2. attribute（column info）：AttributeID，EntityID，AttributeName


3. 为值（excel行/列）创建一个表格：ValueID，RowNumber，AttributeID，AttributeValue

缺点是AttributeValue不是特定类型的（它只是varchar /文本）。您可以通过向属性表添加AttributeType来解决此问题，然后在代码中使用该属性表来知道该列应包含哪种类型的数据。但是，除非你事先知道Excel文件的内容/格式，你可能需要GUESS什么类型的列是...这不难，只要excel文件不乱。

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.

如果你有这样的需要，还有其他（更复杂的）实现EAV的方法，包括一个带有类型列的方法。

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

这篇关于要上传Excel并将其存储在数据库中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！