Creating extra row without data in excel using PHPEXCEL in codeigniter

+1 vote
82 views
asked Mar 8 in CodeIgniter by srinivasulu_yagnam (180 points)

I'm creating an excel file using PHPEXCEL library in codeigniter.

Actually i'm taking the data from one excel file and copying the data from loaded file to new file. Finally a new excel is creating with an extra row without data.

But When I open that file and saving without doing any modification (Simply opening the file and Ctrl+S), all the extra row commas are eliminating. This I have found the major error from the file comparision. I need to eliminate the commas at last programtically. http://codebeautify.org/file-diff

Please check the attachments image.

image description here

First Pane in the image is created file and second pane is after saving the file (Manually opened from location)

Please help how to resolve.

        $this->load->library('Excel');
        $objPHPExcel_final = new PHPExcel();
        $objPHPExcel_final->setActiveSheetIndex(0);

       // Loading new data file
        $objPHPExcel = PHPExcel_IOFactory::load($uploaded_file_path);
        $allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);

        // Loading excel template for header
        $objPHPExcel_exist = PHPExcel_IOFactory::load($existing_file_path);
        $allDataInSheet_exist = $objPHPExcel_exist->getActiveSheet()->toArray(null, true, true, true);

        // Taking headers from exising and Putting Some Data into new Excel file
 $objPHPExcel_final->getActiveSheet()->SetCellValue("G" . $i, date("d/m/Y")); // Application Date
                    $objPHPExcel_final->getActiveSheet()->SetCellValue("I" . $i, "Individual"); // Cover Type
                    $objPHPExcel_final->getActiveSheet()->SetCellValue("J" . $i, "1"); // No.of Lives
                    $objPHPExcel_final->getActiveSheet()->SetCellValue("K" . $i, "1"); // Tenure

           $objWriter_final->save('myfile.xls');![Image]

3 Answers

0 votes
answered Mar 8 by Suraj
99% chances are that you are using wrong encoding for the new file creation. Try to force it to UTF-8 which should solve the issue. Maybe give a try to BOM also.

To check if there is a file header issue, you can just open the new file on google docs, which would apply the missing headers itself and both the files should look exactly same.

Good luck!
commented Mar 8 by anonymous
Thanks for your response. Can please share sample php code if you have already.
0 votes
answered Mar 9 by Jac
If you want to use PHPExcel try to put the same charset on all your pages, File charset & content charset. (UTF-8)

Put this on your file header :

header('Content-Type: text/html; charset=utf-8');

And change the type of file in UTF-8 Without Bom

On Nodepadd++ go to :

Click on "Format" Select "Encode in UTF-8 without BOM"

I hope it will help you.
0 votes
answered Mar 9 by Jake
The best of options in my option is to go with the pretty class developed by Eliselab

https://github.com/bcit-ci/CodeIgniter/wiki/Export-to-Excel-2013

Place this on your controller and you are good to go

$this->load->library('export');
$this->load->model('YourModel');

$result = $this->YourModel->YourQueryFunction();
$this->export->to_excel($result, 'FileNameHere');

Related questions

+1 vote
1 answer 184 views
asked Dec 6, 2016 in CodeIgniter by Charlie
0 votes
1 answer 324 views
0 votes
1 answer 14 views
+1 vote
1 answer 380 views
+2 votes
0 answers 120 views
...