PHP导出数据到Excel

时间：2022-11-24 15:20:38
<?php

date_default_timezone_set('PRC');

$filename="info.xls";//先定义一个excel文件

header("Content-Type: application/vnd.ms-execl");

header("Content-Type: application/vnd.ms-excel; charset=utf-8");

header("Content-Disposition: attachment; filename=$filename");

header("Pragma: no-cache");

header("Expires: 0");

//我们先在excel输出表头，当然这不是必须的

echo iconv("utf-8", "gb2312", "编号")."\t";

echo iconv("utf-8", "gb2312", "姓名")."\t";

echo iconv("utf-8", "gb2312", "年龄")."\t";

echo iconv("utf-8", "gb2312", "性别")."\n";//注意这个要换行

//这里我们定义一个数据库为datebse 数据库用户名：root 密码为：123456

$conn = mysql_connect("localhost","root","");

mysql_select_db("thinkphp");

mysql_query("set names utf8");

//在这里我们定义一个名叫studen的表，她有id，name，age，sex四个字段

$sql="select id,name,age,sex from student";

$result=mysql_query($sql);

while($row =mysql_fetch_array($result)){

    echo iconv("utf-8", "gb2312", $row['id'])."\t";

    echo iconv("utf-8", "gb2312", $row['name'])."\t";

    echo iconv("utf-8", "gb2312", $row['age'])."\t";

    echo iconv("utf-8", "gb2312", $row['sex'])."\n";

}

2.用PHPExcel 库导出，需要下载PHPExcel库支持。

<?php

/** PHPExcel */

require_once '../lib/PHPExcel/PHPExcel.php';

// Create new PHPExcel object

$objPHPExcel = new PHPExcel();

// Set 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");

$objPHPExcel->getActiveSheet()->mergeCells('A1:N1');

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//$objPHPExcel->getActiveSheet()->getStyle('A2')->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth();

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(18);

$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(10);

$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);

$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);

$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);

$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(21);

$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);

$objPHPExcel->setActiveSheetIndex(0)

    ->setCellValue('A1', '中奖信息表')

    ->setCellValue('A2', '姓名')

    ->setCellValue('B2', '性别')

    ->setCellValue('C2', '奖项')

    ->setCellValue('D2', '活动名称')

    ->setCellValue('E2', '中奖时间')

    ->setCellValue('F2', '媒体人/非媒体人')

    ->setCellValue('G2', '所属媒体')

    ->setCellValue('H2', '职位')

    ->setCellValue('I2', '电话号码')

    ->setCellValue('J2', '电子邮箱')

    ->setCellValue('K2', '身份证号')

    ->setCellValue('L2', '生日')

    ->setCellValue('M2', '总积分')

    ->setCellValue('N2', '绑定时间');

//数据库连接

$db = mysql_connect("localhost", "root", "");

mysql_select_db("df_xtl",$db);  //选择数据库，这里为"ywcl"。

mysql_query("SET NAMES UTF8"); //设定编码方式为UTF8

$sDate = '20101010';

$lDate = '20151010';

$sql="SELECT a.inter_time,a.prize,b.birthday,b.email,b.identity_card,b.media_name,b.position,b.reg_time,b.reporter_name,b.sex,b.sum_score,b.telphone,b.group_info,c.inter_name FROM `interaction` a,media b,interact_menu c

WHERE a.openid = b.openid and a.inter_time>= '".$sDate."' and a.inter_time <= '".$lDate."' and a.inter_name=c.id";

//echo $sql;

//$sql ="select * from media";

$result=mysql_query($sql);

$num=mysql_num_rows($result);

if ($num>0)

{

    $count=2;

    while($data=mysql_fetch_array($result))

    {

        $count+=1;

        $l1="A"."$count";

        $l2="B"."$count";

        $l3="C"."$count";

        $l4="D"."$count";

        $l5="E"."$count";

        $l6="F"."$count";

        $l7="G"."$count";

        $l8="H"."$count";

        $l9="I"."$count";

        $l10="J"."$count";

        $l11="K"."$count";

        $l12="L"."$count";

        $l13="M"."$count";

        $l14="N"."$count";

        if($data['sex'] == 0){

            $data['sex'] = '女';

        }else{

            $data['sex'] = '男';

        }

        if($data['group_info'] == 'meiti'){

            $data['group_info'] ='媒体人';

        }else{

            $data['group_info'] ='非媒体人';

        }

        if($data['prize'] ==0){

            $data['prize'] ='未中奖';

        }else if($data['prize'] ==1){

            $data['prize'] ='一等奖';

        }else if($data['prize'] ==2){

            $data['prize'] ='二等奖';

        }else if($data['prize'] ==3){

            $data['prize'] ='三等奖';

        }

            $objPHPExcel->setActiveSheetIndex(0)

                ->setCellValue($l1, $data['reporter_name'])

                ->setCellValue($l2, $data['sex'])

                ->setCellValue($l3, $data['prize'])

                ->setCellValue($l4, $data['inter_name'])

                ->setCellValue($l5, $data['inter_time'])

                ->setCellValue($l6, $data['group_info'])

                ->setCellValue($l7, $data['media_name'])

                ->setCellValue($l8, $data['position'])

                ->setCellValue($l9, $data['telphone'])

                ->setCellValue($l10, $data['email'])

                ->setCellValue($l11, $data['identity_card'])

                ->setCellValue($l12, $data['birthday'])

                ->setCellValue($l13, $data['sum_score'])

                ->setCellValue($l14, $data['reg_time']);

    }

}

// Rename sheet

$objPHPExcel->getActiveSheet()->setTitle('通讯录');

// 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="通讯录.xls"');

header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save('php://output');

exit;

?>

　　

相关文章