如何在CODEIGNITER中使用phpExcel将mysql表导出到csv或excel文件
如何在CODEIGNITER中使用phpExcel将mysql表导出到csv或excel文件
提问于 2013-12-09 01:31:07
我一直在搜索如何将mysql表导出到csv或excel文件。我看到了一些步骤，我跟着它们走。有没有一种方法可以使用代码点火器将mysql表导出到csv或excel文件？
我试过这个PHPExcel。但对我来说似乎行不通。
function index()
{
$query = $this->db->get('filter_result');
if(!$query)
return false;
// Starting the PHPExcel library
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
$objPHPExcel->setActiveSheetIndex(0);
// Field names in the first row
$fields = $query->list_fields();
$col = 0;
foreach ($fields as $field)
{
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
$col++;
}
// Fetching the table data
$row = 2;
foreach($query->result() as $data)
{
$col = 0;
foreach ($fields as $field)
{
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->$field);
$col++;
}
$row++;
}
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
// Sending headers to force the user to download the file
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Products_'.date('dMy').'.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
}回答 4
Stack Overflow用户
回答已采纳
发布于 2013-12-16 12:09:01
这是我使用的密码。
<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class excel{
function to_excel($array, $filename) {
header('Content-Disposition: attachment; filename='.$filename.'.xls');
header('Content-type: application/force-download');
header('Content-Transfer-Encoding: binary');
header('Pragma: public');
print "\xEF\xBB\xBF"; // UTF-8 BOM
$h = array();
foreach($array->result_array() as $row){
foreach($row as $key=>$val){
if(!in_array($key, $h)){
$h[] = $key;
}
}
}
echo '<table><tr>';
foreach($h as $key) {
$key = ucwords($key);
echo '<th>'.$key.'</th>';
}
echo '</tr>';
foreach($array->result_array() as $row){
echo '<tr>';
foreach($row as $val)
$this->writeRow($val);
}
echo '</tr>';
echo '</table>';
}
function writeRow($val) {
echo '<td>'.$val.'</td>';
}
}
?>使用此代码创建一个库，并将其称为：
public function brandExcel() {
$this->load->library('excel');
$result = $this->config_model->getBrandsForExcel();
$this->excel->to_excel($result, 'brands-excel');
}Stack Overflow用户
发布于 2014-07-10 07:40:26
efenacigiray的回答很棒，但我得到了一个奇怪的错误，因为xls的格式与指定的不同，所以我只做了另一个函数，它工作得很好。
class excel {
function create_excel($array) {
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
//'id,name,contact_name,email,email2,mobile,mobile2,website,country,city,address,postal_code,info'
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'Id')
->setCellValue('B1', 'name!')
->setCellValue('C1', 'contact_name')
->setCellValue('D1', 'email')
->setCellValue('K1', 'address')
->setCellValue('L1', 'postal_code');
$i = 2;
foreach($array as $row){
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$i, $row['id'])
->setCellValue('B'.$i, $row['name'])
->setCellValue('C'.$i, $row['contact_name'])
->setCellValue('D'.$i, $row['email'])
->setCellValue('K'.$i, $row['address'])
->setCellValue('L'.$i, $row['postal_code']);
$i++;
}
// Miscellaneous glyphs, UTF-8
// $objPHPExcel->setActiveSheetIndex(0)
// ->setCellValue('A4', 'Miscellaneous glyphs')
// ->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Probable Clients');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="probClients.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
}Stack Overflow用户
发布于 2014-01-28 11:03:59
我发现efenacigiray的代码也适用于我，但我不得不删除代码‘->result()’
使用代码点火器版本: 2.1.4
(我会把这写成评论，但我还没有足够的代表)
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/20461623
复制相关文章
相似问题
腾讯云开发者
