Use phpexcel to import excel into the database and export the database to excel.

Copy codeThe Code is as follows:
<? Php
/*
* Author zhy
* Date 2012 06 12
* For excel
*/
Date_default_timezone_set ("PRC ");
Error_reporting (E_ALL );
Error_reporting (0 );
Ini_set ('display _ errors ', TRUE );
Ini_set ('display _ startup_errors ', TRUE );
Define ('eol ', (PHP_SAPI = 'cli ')? PHP_EOL: '<br/> ');

Require_once ('../Classes/PHPExcel. php ');
Require_once ("config. php ");
Require_once ("mysql. class. php ");

// Generate a purchase report based on time
$ Time = date ("");
$ Minute = date ("I ");
$ Apm = "";
If ($ time = 'pm '){
$ Apm = $ time;
$ Stime = mktime (12, 00, 00, date ('M'), date ('D')-1, date ('y '));
$ Etime = mktime (11,59, 59, date ('M'), date ('D'), date ('y '));
} Else {
$ Apm = $ time;
$ Stime = mktime (12, 00, 00, date ('M'), date ('D')-1, date ('y '));
$ Etime = mktime (11,59, 59, date ('M'), date ('D'), date ('y '));
}

// Instantiate the excel class
$ ObjPHPExcel = new PHPExcel ();

//// // Obtain the document information
/////// $ ObjProps = $ objPHPExcel-> getProperties ();
///// Print_r ($ objProps );
//// // Echo "<br/> ";
////// $ ObjProps-> setDescription ("test_123456 ");
///// Print_r ($ objProps );

$ ObjPHPExcel-> setActiveSheetIndex (0)
-> SetCellValue ('a5 ', 'item Code ')
-> SetCellValue ('b5 ', 'hangzhou ')
-> SetCellValue ('c5 ', 'item name ')
-> SetCellValue ('d5 ', 'purchase volume ');

// Set the selected sheet table name
$ ObjPHPExcel-> getActiveSheet ()-> setTitle ('ancestral name ');
// Set the Font Style
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a1')-> getFont ()-> setName ('arial')-> setSize (25 ); ////-> setUnderline (true); //-> getColor ()-> setARGB ('ffffff000000 '); ///-> setBold (true );
// Merge cells to assign values to cells (values, strings, and formulas)
$ ObjPHPExcel-> getActiveSheet ()-> mergeCells ('a1: D3 ')-> setCellValue ('a1', 'zhongyi list ');
///// $ ObjPHPExcel-> getActiveSheet ()-> mergeCells ('a4: D4 ')-> setCellValue ('a4', "= SUM (E4: F4) ");

$ Date_now = date ("Y-m-d ");
$ ObjPHPExcel-> getActiveSheet ()-> mergeCells ('a4: D4 ')-> setCellValue ('a4', "date of purchase :". $ date_now. "". $ apm. "");
// Set the width of a Single Column
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('A')-> setWidth (15 );
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('B')-> setWidth (20); // $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('G ') -> setRowHeight (50 );/
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('C')-> setWidth (44 );
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('D')-> setWidth (15 );

// Bold border of the large border Style
$ LineBORDER = array (
'Borders' => array (
'Outline' => array (
'Style' => PHPExcel_Style_Border: BORDER_THICK,
'Color' => array ('arg' => '123 '),
),
),
);
// Header style
$ Head = array (
'Font' => array (
'Bold '=> true
),
'Alignment '=> array (
'Horizontal '=> PHPExcel_Style_Alignment: HORIZONTAL_CENTER,
'Version' => PHPExcel_Style_Alignment: VERTICAL_CENTER
),

);
// Title Style
$ Title = array (
'Font' => array (
'Bold '=> true
),
);
// Align
$ CENTER = array (
'Alignment '=> array (
'Horizontal '=> PHPExcel_Style_Alignment: HORIZONTAL_CENTER,
'Version' => PHPExcel_Style_Alignment: VERTICAL_CENTER
),
);
// Align right
$ RIGHT = array (
'Alignment '=> array (
'Horizontal '=> PHPExcel_Style_Alignment: HORIZONTAL_RIGHT,
'Version' => PHPExcel_Style_Alignment: VERTICAL_CENTER
),
);
// Fine border Style
$ Linestyle = array (
'Borders' => array (
'Outline' => array (
'Style' => PHPExcel_Style_Border: BORDER_THIN,
'Color' => array ('arg' => 'ff000000 '),
),
),
);

$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a1: D3 ')-> applyFromArray ($ head); //-> getAlignment () -> getHorizontal (''); //-> getBorders ()-> getTop ()-> setBorderStyle ('');
//-> SetWrapText (true); automatically wrap
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a4: D4 ')-> applyFromArray ($ RIGHT );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a5: D5 ')-> applyFromArray ($ title );

// Fill color
/// $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a1')-> getFill ()-> getStartColor ()-> setARGB ('ffffff00000000 ');/

// Insert data
$ Dsql-> Execute ('omebrand _ list', "select I. goods_id, sum ('nums') AS num, I. name, I. addon, I. price, g. bn as B, I. bn as h,
G. goods_id, I. goods_id, I. order_id
FROM 'sdb _ B2C _order_items 'as I, sdb_ B2C _goods as g
WHERE I. order_id in (select order_id from sdb_ B2C _orders where status = 'active' and createtime between $ stime and $ etime) and I. goods_id = g. goods_id and g. cat_id = 173 group by h ");
$ M = 0;
Unset ($ re );
While ($ row = $ dsql-> GetObject ('omebrand _ list '))
{$ Re [$ m] = get_object_vars ($ row );
$ M ++;
}
$ Row_count = 5;
$ ObjPHPExcel-> setActiveSheetIndex (0)
-> SetCellValue ('a6 ', 12325416541)
-> SetCellValue ('b6 ', 4962132165262)
-> SetCellValue ('c6 ', 121515212515241521)
-> SetCellValue ('d6, 96215465415 );
Foreach ($ re as $ r =>$ dataRow ){
$ BaseRow = 6;
$ Row = $ baseRow + $ r;
$ Bn = $ dataRow [h];
$ Goods_id = $ dataRow [goods_id];
$ Spec_value = "";
$ Aa = unserialize ($ dataRow [addon]);
If ($ aa ['product _ attr ']) {
Foreach ($ aa ['product _ attr'] as $ arr_special_info ){
$ Spec_value = $ arr_special_info ['value'];
}
}

Preg_match_all ('/\-? \ D + \.? \ D */I ', $ spec_value, $ row1 );
$ Num = $ row1 [0] [0];
$ All = $ num * $ dataRow [num];
If ($ spec_value = ''){
$ All = $ dataRow ['num'];
// $ Prce = $ dataRow [price];
}
$ ObjPHPExcel-> setActiveSheetIndex (0)
-> SetCellValue ('A'. $ row, $ dataRow ['B'])
-> SetCellValue ('B'. $ row, $ bn)
-> SetCellValue ('C'. $ row, $ dataRow ['name'])
-> SetCellValue ('D'. $ row, $ all );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('A'. $ row_count)-> applyFromArray ($ linestyle );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('B'. $ row_count)-> applyFromArray ($ linestyle );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('C'. $ row_count)-> applyFromArray ($ linestyle );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('D'. $ row_count)-> applyFromArray ($ linestyle );

$ BaseRow ++;
$ Row_count ++;
}
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('A'. $ row_count)-> applyFromArray ($ linestyle );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('B'. $ row_count)-> applyFromArray ($ linestyle );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('C'. $ row_count)-> applyFromArray ($ linestyle );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('D'. $ row_count)-> applyFromArray ($ linestyle );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a5: D'. $ row_count)-> applyFromArray ($ CENTER );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a1: D'. $ row_count)-> applyFromArray ($ lineBORDER );

// Set the print margin
$ ObjPHPExcel-> getActiveSheet ()-> getPageMargins ()-> setTop (0 );
$ ObjPHPExcel-> getActiveSheet ()-> getPageMargins ()-> setRight (0 );
$ ObjPHPExcel-> getActiveSheet ()-> getPageMargins ()-> setLeft (0 );
$ ObjPHPExcel-> getActiveSheet ()-> getPageMargins ()-> setBottom (0 );
// Set the paper type
$ ObjPHPExcel-> getActiveSheet ()-> getPageSetup ()-> setPaperSize (PHPExcel_Worksheet_PageSetup: PAPERSIZE_A4 );
// Set automatic filtering
$ ObjPHPExcel-> getActiveSheet ()-> setAutoFilter ('a5: D'. $ row_count );
// Set automatic line feed
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b6: B '. $ row_count)-> getAlignment ()-> setWrapText (true );
// Set the formatting number
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a6: A'. $ row_count)-> getNumberFormat ()-> setFormatCode ('123 ');

// Set the security level
$ Md = md5 (time ());
$ Md = substr ($ md, 0, 8 );
$ ObjPHPExcel-> getActiveSheet ()-> getProtection ()-> setPassword ("$ md ");
$ ObjPHPExcel-> getActiveSheet ()-> getProtection ()-> setSheet (true );//
$ ObjPHPExcel-> getActiveSheet ()-> getProtection ()-> setSort (true );
$ ObjPHPExcel-> getActiveSheet ()-> getProtection ()-> setInsertRows (true );
$ ObjPHPExcel-> getActiveSheet ()-> getProtection ()-> setFormatCells (true );

// Add an image
/*
$ Obj = $ objPHPExcel-> getActiveSheet ();
$ ObjDrawing = new PHPExcel_Worksheet_Drawing ();
$ ObjDrawing-> setName ('wsyimg ');
$ ObjDrawing-> setDescription ('image inserted by zhy ');
$ ObjDrawing-> setPath ('./wsy.jpg ');
$ ObjDrawing-> setHeight (50 );
$ ObjDrawing-> setCoordinates ('h23 ');
$ ObjDrawing-> setOffsetX (60 );
$ ObjDrawing-> setRotation (-10 );/
$ ObjDrawing-> getShadow ()-> setVisible (true );
$ ObjDrawing-> getShadow ()-> setDirection (-20 );/
$ ObjDrawing-> setWorksheet ($ obj );
*/

// Header and footer
// $ ObjPHPExcel-> getActiveSheet ()-> getHeaderFooter ()-> setOddHeader ('zhy ');
// $ ObjPHPExcel-> getActiveSheet ()-> getHeaderFooter ()-> setOddFooter ('end ');

$ ObjPHPExcel-> setActiveSheetIndex (0 );
$ Tname = date ('Y-m-dH ', time ());
$ Tnam = iconv ('utf-8', 'gbk', 'ancestral Order ');
$ Tname = $ tnam. $ tname;

// Save Excel 2007
// $ ObjWriter = new PHPExcel_Writer_Excel2007 ($ objPHPExcel );
// $ ObjWriter-> save (str_replace ('. php', '.xlsx', _ FILE __));

// Save in Excel 5
// $ ObjWriter = new PHPExcel_Writer_Excel5 ($ objPHPExcel );
// $ ObjWriter-> save (str_replace ('. php', '.xls', _ FILE __));

$ ObjWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5 ');
$ ObjWriter-> save (str_replace ('. php', '.xls', _ FILE __));

// $ Url = "/data/home/htdocs/ec/public/files /". date ("Y "). "/". date ("Ym "). "/";
CreateDir ($ url );
Function createDir ($ dir ){
If (! Is_dir ($ dir )){
Mkdir ($ dir, 0777, true );
Chmod ($ dir, 0777 );
Chown ($ dir, 'daemon ');
Chgrp ($ dir, 'daemon ');
}
}
$ Name = 'forexmple _ excel ';
Rename (str_replace ('. php', '.xls', _ FILE _), comment ');

?>