使用CI在Excel中导出数据 [英] data export in excel using CI
本文介绍了使用CI在Excel中导出数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试在Excel工作表中导出数据。
i已成功导出数据，但问题是我的数据来自数据库以数组形式出现，我不希望显示所有数组，我只希望将选择性数据发送到excel表
帮助
I am trying to export data in excel sheet. i have successfully exported the data but problem is that my data comes in form of array from database and i dont want all of the array to be displayed i want only selective data to be sent to excel sheet help please.
{
//load our new PHPExcel library
$this->load->library('Excel');
//activate worksheet number 1
$this->excel->setActiveSheetIndex(0);
//name the worksheet
$this->excel->getActiveSheet()->setTitle('Attendance Report');
$this->excel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('P')->setAutoSize(true);
$this->excel->getActiveSheet()->getStyle("A1:P1")->applyFromArray(array("font" => array("bold" => true)));
$this->excel->setActiveSheetIndex(0)->setCellValue('A1', 'Date (Day)');
$this->excel->setActiveSheetIndex(0)->setCellValue('B1', 'Worked Hour');
$this->excel->setActiveSheetIndex(0)->setCellValue('C1', 'In Time');
$this->excel->setActiveSheetIndex(0)->setCellValue('D1', 'In Remarks');
$this->excel->setActiveSheetIndex(0)->setCellValue('E1', 'Out Date');
$this->excel->setActiveSheetIndex(0)->setCellValue('F1', 'Out Time');
$this->excel->setActiveSheetIndex(0)->setCellValue('G1', 'Remarks');
$this->excel->setActiveSheetIndex(0)->setCellValue('H1', 'Difference');
$this->excel->setActiveSheetIndex(0)->setCellValue('I1', 'In Time');
$this->excel->setActiveSheetIndex(0)->setCellValue('J1', 'In Remarks');
$this->excel->setActiveSheetIndex(0)->setCellValue('K1', 'Out Date');
$this->excel->setActiveSheetIndex(0)->setCellValue('L1', 'Out Time');
$this->excel->setActiveSheetIndex(0)->setCellValue('M1', 'Remarks');
$this->excel->setActiveSheetIndex(0)->setCellValue('N1', 'Difference');
$this->excel->setActiveSheetIndex(0)->setCellValue('O1', 'Total OT');
$this->excel->setActiveSheetIndex(0)->setCellValue('P1', 'Remark');
$data=$this->mainModel->exportAttendanceReport();
// get all users in array formate
$this->excel->getActiveSheet()->fromArray($data, null, 'A2');
$filename='Attendance Report.xls'; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
}
这是我到目前为止所做的。
here is what i have done upto now.
推荐答案
尝试以下代码。我对您的代码做了一些更改，
//在$ row ['']变量中传递表的字段名称字段名
Try Following code. I am done some changes in your code ,
// Pass name of your tables field name in $row[''] variable
{
//load our new PHPExcel library
$this->load->library('Excel');
//activate worksheet number 1
$this->excel->setActiveSheetIndex(0);
//name the worksheet
$this->excel->getActiveSheet()->setTitle('Attendance Report');
$this->excel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('P')->setAutoSize(true);
$this->excel->getActiveSheet()->getStyle("A1:P1")->applyFromArray(array("font" => array("bold" => true)));
$this->excel->setActiveSheetIndex(0)->setCellValue('A1', 'Date (Day)');
$this->excel->setActiveSheetIndex(0)->setCellValue('B1', 'Worked Hour');
$this->excel->setActiveSheetIndex(0)->setCellValue('C1', 'In Time');
$this->excel->setActiveSheetIndex(0)->setCellValue('D1', 'In Remarks');
$this->excel->setActiveSheetIndex(0)->setCellValue('E1', 'Out Date');
$this->excel->setActiveSheetIndex(0)->setCellValue('F1', 'Out Time');
$this->excel->setActiveSheetIndex(0)->setCellValue('G1', 'Remarks');
$this->excel->setActiveSheetIndex(0)->setCellValue('H1', 'Difference');
$this->excel->setActiveSheetIndex(0)->setCellValue('I1', 'In Time');
$this->excel->setActiveSheetIndex(0)->setCellValue('J1', 'In Remarks');
$this->excel->setActiveSheetIndex(0)->setCellValue('K1', 'Out Date');
$this->excel->setActiveSheetIndex(0)->setCellValue('L1', 'Out Time');
$this->excel->setActiveSheetIndex(0)->setCellValue('M1', 'Remarks');
$this->excel->setActiveSheetIndex(0)->setCellValue('N1', 'Difference');
$this->excel->setActiveSheetIndex(0)->setCellValue('O1', 'Total OT');
$this->excel->setActiveSheetIndex(0)->setCellValue('P1', 'Remark');
$data=$this->mainModel->exportAttendanceReport();
// get all users in array formate
$n = 2;
if(!empty($data))
{
foreach ($data as $row)
{
// Pass name of your tables field name in following $row[''] variable
$this->excel->getActiveSheet()->setCellValue('A'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('B'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('C'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('D'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('E'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('F'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('G'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('H'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('I'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('J'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('K'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('L'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('M'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('N'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('O'.$n, $row['field_name']);
$this->excel->getActiveSheet()->setCellValue('P'.$n, $row['field_name']);
$n++;
}
}
$filename='Attendance Report.xls'; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
}
这篇关于使用CI在Excel中导出数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
