phpexcelreport

<?php
session_start();
error_reporting(E_ALL);
/** PHPExcel */
include 'PHPExcel.php';
include '../../conn.php';
function chappsdatetime($waktu)
{
  if (isset($waktu))
  {
    $waktu_bulan= substr($waktu,5,2);
    $bulan_aray= array('Januari','Februari','Maret','April','Mei','Juni','Juli','Agustus','September','Oktober','November','Desember');
    return substr($waktu,8,2)." ".$bulan_aray[$waktu_bulan]." ".substr($waktu,0,4)." ".substr($waktu,11,8);
  }
}

//variabel
$bulan = $_POST['bulan'];
$thn = $_POST['thn'];
$cab = "Semarang";
//query perulangan sheet menurut jenis barang
$sheet = 0;
$sql="SELECT
        nama
      FROM
        jenis_barang
      ORDER BY
        id_brg ASC"
$jenis_brg = mysql_query($sql)or die(mysql_error());
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("Rifaqu Emprit")
            ->setLastModifiedBy("Administrator")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Laporan Barang Semarang");
while ($nama = mysql_fetch_array($jenis_brg))
{
  if($sheet>0) $objPHPExcel->createSheet();
  $objPHPExcel->setActiveSheetIndex($sheet);
  //autosize
  $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
  //pembuatan sheet
  $objPHPExcel->getActiveSheet()
              ->SetCellValue('A7', 'No.')
              ->SetCellValue('B7', 'Tangal Transaksi')
              ->SetCellValue('C7', 'Pembelian')
              ->SetCellValue('D7', 'Penjualan')
              ->SetCellValue('E7', 'Stock Barang')
              ->SetCellValue('F7', 'Harga Beli')
              ->SetCellValue('G7', 'Harga Jual')
              ->SetCellValue('H7', 'Laba Jual')
              ->SetCellValue('I7', 'Total Laba Penjualan')
              ->SetCellValue('J7', 'Total Pembelian')
              ->SetCellValue('K7', 'Total Harga Jual')
              ->SetCellValue('L7', 'Konsumen')
              ->SetCellValue('M7', 'Alamat')
              ->SetCellValue('N7', 'Keterangan');
  //Membuat Garis atau tabel
  $sharedStyle1 = 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)
                    ),
                  ));
  //query transaksi
  $baris = 6;
  $no = 0;
  $sql="SELECT *
        FROM
          transaksi
        WHERE
          kategori='".$nama['nama']."'
          AND
          tgl_transaksi LIKE '$thn-$bulan-%'";
  $transaksi = mysql_query($sql) or die(mysql_error());
  while($m=mysql_fetch_array($transaksi)){
    ++$no;
    ++$baris;
    $tgl = chappsdatetime($m['tgl_transaksi']);
    //isi
    $objPHPExcel->getActiveSheet()
                ->setCellValue("A$baris", $no)
                ->setCellValue("B$baris", $tgl)
                ->setCellValue("C$baris", $m['masuk'])
                ->setCellValue("D$baris", $m['keluar'])
                ->setCellValue("E$baris", '')
                ->setCellValue("F$baris", $m['harga_beli'])
                ->setCellValue("G$baris", $m['harga_jual'])
                ->setCellValue("H$baris", '=SUM(G'.$baris.'-F'.$baris.')')
                ->setCellValue("I$baris", '=SUM(H'.$baris.'*D'.$baris.')')
                ->setCellValue("J$baris", '=SUM(C'.$baris.'*F'.$baris.')')
                ->setCellValue("K$baris", '=SUM(D'.$baris.'*G'.$baris.')')
                ->setCellValue("L$baris", $m['konsumen'])
                ->setCellValue("M$baris", $m['alamat'])
                ->setCellValue("N$baris", $m['ket']);

    $sharedStyle2 = new PHPExcel_Style();
    $sharedStyle2->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($sharedStyle2, "A$baris:N$baris");
    $objPHPExcel->getActiveSheet()->getStyle("A$baris:H$baris")->getFont()->setName('Times');
    $objPHPExcel->getActiveSheet()->getStyle("A$baris:H$baris")->getFont()->setSize(11);
  }
  // Mulai Merge cells Judul
  $objPHPExcel->getActiveSheet()
              ->mergeCells('A1:N1')
              ->setCellValue('A1', "LAPORAN TRANSAKSI BAJA RINGAN")
              ->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel->getActiveSheet()->mergeCells('A2:N2');
  $objPHPExcel->getActiveSheet()->setCellValue('A2', "CV. ANJASA");
  $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel->getActiveSheet()->mergeCells('A3:N3');
  $objPHPExcel->getActiveSheet()->setCellValue('A3', "Jln. Tentara Pelajar No. 79");
  $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel->getActiveSheet()->mergeCells('A5:N5');
  $objPHPExcel->getActiveSheet()->setCellValue('A5', "Bulan $bulan Cabang $cab");
  $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFont()->setName('Times');
  $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFont()->setSize(12);
  $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true);
  $objPHPExcel->getActiveSheet()->getStyle('A2:H2')->getFont()->setName('Times');
  $objPHPExcel->getActiveSheet()->getStyle('A2:H2')->getFont()->setSize(12);
  $objPHPExcel->getActiveSheet()->getStyle('A2:H2')->getFont()->setBold(true);
  $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getFont()->setName('Times');
  $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getFont()->setSize(12);
  $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getFont()->setBold(true);
  $objPHPExcel->getActiveSheet()->getStyle('A5:H5')->getFont()->setName('Times');
  $objPHPExcel->getActiveSheet()->getStyle('A5:H5')->getFont()->setSize(12);
  $objPHPExcel->getActiveSheet()->getStyle('A5:H5')->getFont()->setBold(true);
  // Rename sheet
  $objPHPExcel->getActiveSheet()->setTitle($nama['nama']);
  $sheet++;
}
// 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="Laporan Transaksi Barang-'.$bulan.$thn.'.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;