all materi

Membuat Laporan(Report) dengan PHP Excel21 July 2012ramoreezLeave a commentGo to comments

Mungkin bagi newbi-newbi termasuk saya sering bingung jika berhadapan dengan reporting atau membuat laporan di PHP. Baik itu laporan dalam bentuk PDF atau EXCEL.

Saya sendiri pernah mencoba 3 (tiga) library untuk membuat laporan dalam bentuk PDF (bisa dilihat disni). Untuk laporan atau reporting dalam bentuk excel ada satu library PHP yang sangat mengagumkan, yaitu PHPExcel.Sebelumnya saya biasanya memakai cara biasa untuk membuat laporan dalam excel, cara ini termasuk cara yang paling mudah bagi saya:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

<?php

// isi $excel akan bernilai true apabila ditemukan parameter get dengan nama 'excel'

$excel = isset($_GET['excel']);

if($excel):

// buat nama file unique untuk di download

$filename = 'export-'.date('YmdHis');

// dengan perintah di bawah ini akan memunculkan dialog download di browser anda

header("Content-type: application/x-msdownload");

// perintah di bawah untuk menentukan nama file yang akan di download

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

else:

// tag header di html disembuyikan apabila sedang convert html to excel

?>

<html>

<head>

<style type="text/css">

/* setting format tabel */

table {

font-family: Verdana;

font-size: 8pt;

border-width: 1px;

border-style: solid;

border-color: #ccd2d2;

border-collapse: collapse;

background-color: #f9f9f9;

}

th {

color: #f00;

font-size: 8pt;

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

text-transform: uppercase;

text-align: center;

padding: 0.5em;

border-width: 1px;


border-color: #000;


background-color: #000;

}

td {

padding: 0.1em;

color: #272727;

vertical-align: top;

border-width: 1px;


border-color: #000;


font-size: 8pt;

}

</style>

<title>Membuat Report Excel Dari HTML Dengan PHP</title>

</head>

<body>

<?php endif; ?>

<?php if(!$excel): ?>



<div align="left" style="margin-top:15px">

<input type="button" onClick="document.location='?excel=1'" value="Export to Excel"

</div>

<br>

<?php endif; ?>

<?php

//setting koneksi anda

$server = "localhost";

$username = "root";

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

$password = "";

$db = "excel";

$tbl = "customer";

$koneksi = mysql_connect($server,$username,$password);

mysql_select_db($db, $koneksi) or die("Cannot connect to database..");

// create tabel dengan php

echo "<div align='left'><img src='http://smartcoderszone.com/wp-content/uploads/2010/09/generate-excel-report-with-php-and-mysql2.jpgheight='150'><div><br><br><br><br><br>";

echo "<p> </p>";

echo "<div align='left' style='color='#ff0000'><strong>DATA PEGAWAI</strong>";

echo "<table border='1'>";

echo " <tr bgcolor='#cccccc'>";

echo " <th>NO</th>";

echo " <th>company_name</th>";

echo " <th>email</th>";

echo " <th>city</th>";

echo " </tr>";

$str = " SELECT * FROM ".$db.".".$tbl.

" order by ID desc ";

$query = mysql_query($str);

if($query && mysql_num_rows($query) > 0){

$no = $posisi+1;

while($row = mysql_fetch_object($query)){

//if($bgcolor=='#f1f1f1'){$bgcolor='#ffffff';}

//else{$bgcolor='#f1f1f1';}

echo "<tr bgcolor=$bgcolor>";

echo " <td align='center' height='18'><strong>$no</strong></td>";

echo " <td>{$row->company_name} </td>";

echo " <td>{$row->email} </td>";

echo " <td>{$row->city} </td>";

$no++;

}

}

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

//echo $str;

echo " </tr>";

echo "</table>";

echo "</div>";

?>

</body>

</html>

Cara yang lebih rumit tapi lebih bisa di custom, sebelum saya “menemukan” phpexcel saya sering pakai cara ini

dan sepertinya cara ini sering digunakan karena kebanyakan tutorial di internet memakai cara ini:

1

2

3

4

5

6

7

8

9

10

11

<?php

// nama file

$namaFile = "report.xls";

// Function penanda awal file (Begin Of File) Excel

function xlsBOF() {

echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);

return;

}

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

// Function penanda akhir file (End Of File) Excel

function xlsEOF() {

echo pack("ss", 0x0A, 0x00);

return;

}

// Function untuk menulis data (angka) ke cell excel

function xlsWriteNumber($Row, $Col, $Value) {

echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);

echo pack("d", $Value);

return;

}

// Function untuk menulis data (text) ke cell excel

function xlsWriteLabel($Row, $Col, $Value ) {

$L = strlen($Value);

echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);

echo $Value;

return;

}

// header file excel

header("Pragma: public");

header("Expires: 0");

header("Cache-Control: must-revalidate, post-check=0,

pre-check=0");

header("Content-Type: application/force-download");

header("Content-Type: application/octet-stream");

header("Content-Type: application/download");

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

// header untuk nama file

header("Content-Disposition: attachment;

filename=".$namaFile."");

header("Content-Transfer-Encoding: binary ");

// memanggil function penanda awal file excel

xlsBOF();

// ------ membuat kolom pada excel --- //

// mengisi pada cell A1 (baris ke-0, kolom ke-0)

xlsWriteLabel(0,0,"NO");


xlsWriteLabel(0,1,"NIP");


xlsWriteLabel(0,2,"NAMA MAHASISWA");


xlsWriteLabel(0,3,"EMAIL");


xlsWriteLabel(0,4,"KELAMAMIN");

// -------- menampilkan data --------- //

// koneksi ke mysql

mysql_connect("localhost", "root", "");

mysql_select_db("schoolv2");

// query menampilkan semua data

$query = "SELECT * FROM datasiswa";

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

$hasil = mysql_query($query);

// nilai awal untuk baris cell

$noBarisCell = 1;

// nilai awal untuk nomor urut data

$noData = 1;

while ($data = mysql_fetch_array($hasil))

{

// menampilkan no. urut data

xlsWriteNumber($noBarisCell,0,$noData);

// menampilkan data nim

xlsWriteLabel($noBarisCell,1,$data['nip']);

// menampilkan data nama mahasiswa

xlsWriteLabel($noBarisCell,2,$data['nama']);

// menampilkan data nilai

xlsWriteNumber($noBarisCell,3,$data['email']);

// menentukan status kelulusan

if ($data['kelamin'] >= 60) $status = "LULUS";

else $status = "TIDAK LULUS";

// menampilkan status kelulusan

xlsWriteLabel($noBarisCell,4,$status);

// increment untuk no. baris cell dan no. urut data

$noBarisCell++;

$noData++;

}

// memanggil function penanda akhir file excel

xlsEOF();

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

exit();

?>

Setelah “dituntut” untuk membuat reporting / laporan yang lebih wonderfull saya merasa kedua cara diatas kurang cocok.Kendala saya saat itu adalah bagaimana menambahkan kop atau header dan logo/image atau gambar dalam excel.

Akhirnya saya searching di google.com untuk menemukan cara yang lebih bagus dan saya menemukan library phpexcel.berikut ini script phpexcel dan screenshoot nya yang pernah saya buat. ohy saya juga menambahkan fitur filter dan sorting di excel yang saya buat. kelebihan phpexcel yang lain adalah kita bisa menyimpannya sebagai file excel 2007.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

<?php

/**

* PHPExcel

*

* Copyright (C) 2006 - 2012 PHPExcel

*

* This library is free software; you can redistribute it and/or

* modify it under the terms of the GNU Lesser General Public

* License as published by the Free Software Foundation; either

* version 2.1 of the License, or (at your option) any later version.

*

* This library is distributed in the hope that it will be useful,

* but WITHOUT ANY WARRANTY; without even the implied warranty of

* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU

* Lesser General Public License for more details.

*

* You should have received a copy of the GNU Lesser General Public

* License along with this library; if not, write to the Free Software

* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

*

* @category PHPExcel

* @package PHPExcel

* @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)

* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL

* @version 1.7.7, 2012-05-19

*/

$tahun = $_POST['tahun'];

/** Error reporting */

error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

/** Include PHPExcel */

require_once '../Classes/PHPExcel.php';

// Create new PHPExcel object

$objPHPExcel = new PHPExcel();

// Set document properties

$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")

->setLastModifiedBy("Data Siswa SMPN 5 ********")

->setTitle("Data Siswa SMPN 5 ********")

->setSubject("Data Siswa SMPN 5 ********")

->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")

->setKeywords("office 2007 openxml php")

->setCategory("Test result file");

// Create the worksheet

$objPHPExcel->setActiveSheetIndex(0);

$objPHPExcel->getActiveSheet()->setCellValue('A7', "No")

->setCellValue('B7', "NIS")

->setCellValue('C7', "NISN")

->setCellValue('D7', "Nama")

->setCellValue('E7', "J.Kelamin")

->setCellValue('F7', "Tmp&Tgl.Lahir")

->setCellValue('G7', "Alamat")

->setCellValue('H7', "No.Telp/HP")

->setCellValue('I7', "Agama")

->setCellValue('J7', "No.Test")

->setCellValue('K7', "Kelas")

->setCellValue('L7', "Nama Ayah")

->setCellValue('M7', "Pendapatan Ayah");

$server = "localhost";

$username = "root";

$password = "";

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

$db = "schoolv2";

$koneksi = mysql_connect($server,$username,$password);

mysql_select_db($db, $koneksi) or die("Cannot connect to database..");

$SQL = mysql_query("SELECT datasiswa.nisn,nis,namalengkap,jeniskelamin, tempatlahirsiswa,tgllahirsiswa,agamasiswa,nomortest, alamattinggalsiswa,notelpsiswa,nohpsiswa,datanoabsen.tingkat,namaayah,pendapatanayah

FROM schoolv2.datasiswa,schoolv2.datanoabsen

WHERE datasiswa.idsiswa=datanoabsen.idsiswa AND datanoabsen.tahun='$tahun' ORDER BY nis");

$totJML = mysql_num_rows($SQL);

$dataArray= array();

$no=0;

while($row = mysql_fetch_array($SQL, MYSQL_ASSOC)){

$no++;

$row_array['no'] = $no;

$row_array['nis'] = $row['nis'];

$row_array['nisn'] = $row['nisn'];

$row_array['namalengkap'] = $row['namalengkap'];

$row_array['jeniskelamin'] = $row['jeniskelamin'];

$row_array['ttl'] = $row['tempatlahirsiswa'].", ".$row['tgllahirsiswa'];

$row_array['alamattinggalsiswa'] = $row['alamattinggalsiswa'];

$row_array['notelpsiswa'] = $row['nohpsiswa'];

$row_array['agamasiswa'] = $row['agamasiswa'];

$row_array['nomortest'] = $row['nomortest'];

$row_array['tingkat'] = $row['tingkat'];

$row_array['namaayah'] = $row['namaayah'];

$row_array['pendapatanayah'] = $row['pendapatanayah'];

array_push($dataArray,$row_array);

}

$nox=$no+7;

$objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A8');

// Set page orientation and size

$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LEGAL);

$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.75);

$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.75);

$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75);

$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.75);

$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

// Set title row bold;

$objPHPExcel->getActiveSheet()->getStyle('A7:M7')->getFont()->setBold(true);

// Set fills

$objPHPExcel->getActiveSheet()->getStyle('A7:M7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

$objPHPExcel->getActiveSheet()->getStyle('A7:M7')->getFill()->getStartColor()->setARGB('FF808080');

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);

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

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(6.29);

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

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

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

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

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

$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(11);

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

$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(7.43);

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

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

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

// Set autofilter

// Always include the complete filter range!

// Excel does support setting only the caption

// row, but that's not a best practise...

$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

$objPHPExcel->setActiveSheetIndex(0);

$sharedStyle1 = new PHPExcel_Style();

$sharedStyle2 = new PHPExcel_Style();

$sharedStyle1->applyFromArray(

array('borders' => array(

'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),

'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),

'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM),

'left' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)

),

));

$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A7:M$nox");

// Set style for header row using alternative method

$objPHPExcel->getActiveSheet()->getStyle('A7:M7')->applyFromArray(

array(

'font' => array(

'bold' => true

),

'alignment' => array(

'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,

),

'borders' => array(

'top' => array(

'style' => PHPExcel_Style_Border::BORDER_THIN

)

),

'fill' => array(

'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,

'rotation' => 90,

'startcolor' => array(

'argb' => 'FFA0A0A0'

),

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

'endcolor' => array(

'argb' => 'FFFFFFFF'

)

)

)

);

// Add a drawing to the worksheet

$objDrawing = new PHPExcel_Worksheet_Drawing();

$objDrawing->setName('Logo');

$objDrawing->setDescription('Logo');

$objDrawing->setPath('../images/logo2.png');

$objDrawing->setCoordinates('B2');

$objDrawing->setHeight(120);

$objDrawing->setWidth(120);

$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objPHPExcel->getActiveSheet()->getStyle('A7:M1000')->getFont()->setName('Arial');

$objPHPExcel->getActiveSheet()->getStyle('A7:M1000')->getFont()->setSize(7);

// Merge cells

$objPHPExcel->getActiveSheet()->mergeCells('D2:M2');

$objPHPExcel->getActiveSheet()->setCellValue('D2', "PEMERINTAH KOTA ********");


$objPHPExcel->getActiveSheet()->setCellValue('D3', "DINAS PENDIDIKAN");


$objPHPExcel->getActiveSheet()->setCellValue('D4', "SMPN 5 ********");


$objPHPExcel->getActiveSheet()->setCellValue('D5', "Jl.WR.Supratman 12, Telp. 482713 ********");


$objPHPExcel->getActiveSheet()->setCellValue('D6', "REKAPITULASI DATA SISWA TAHUN $tahun");

$objPHPExcel->getActiveSheet()->getStyle('D2:M6')->getFont()->setName('Arial');

$objPHPExcel->getActiveSheet()->getStyle('D2:M5')->getFont()->setSize(18);

$objPHPExcel->getActiveSheet()->getStyle('D6')->getFont()->setSize(22);

$objPHPExcel->getActiveSheet()->getStyle('D2:M6')->getFont()->setBold(true);

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

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

// Redirect output to a client’s web browser (Excel2007)

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename="dataSiswa"'.date("d-F-Y").'".xlsx"');

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

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

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

exit;

// Save Excel 2007 file

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

$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

222

223

224

dan hasil dari script di atas seperti ini:

all materi

Documents