PHPExcel_Style_NumberFormat PhpSpreadsheet
Ngày đăng:
18/01/2023
Trả lời:
0
Lượt xem:
82
$objPHPExcel->getActiveSheet()->getStyle('B2') ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); $objPHPExcel->getActiveSheet()->getStyle('B2') ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle('B2') ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); $objPHPExcel->getActiveSheet()->getStyle('B2') ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); $objPHPExcel->getActiveSheet()->getStyle('B2') ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); $objPHPExcel->getActiveSheet()->getStyle('B2') ->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); $objPHPExcel->getActiveSheet()->getStyle('B2') ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('B2') ->getFill()->getStartColor()->setARGB('FFFF0000'); $objPHPExcel->getActiveSheet()->getStyle('A1') ->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); Để biết thêm. https. //github. com/PHPOffice/PHPExcel/blob/develop/Examples/05featuredemo. tập đoàn. php Show
// Create new PHPExcel object echo date('H:i:s') . " Create new PHPExcel object\n"; $objPHPExcel = new PHPExcel(); // Set properties echo date('H:i:s') . " Set properties\n"; $objPHPExcel->getProperties()->setCreator("Vikas Tharyani") ->setLastModifiedBy("Vikas Tharyani") ->setTitle($CompanyName) ->setSubject("General Leger Report") ->setDescription("Test") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); //Add Data $heading = sprintf("%s To %s",$FromDate,$ToDate); $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('D1', $CompanyName); $objPHPExcel->getActiveSheet()->setCellValue('D2', 'GeneralLedger'); $objPHPExcel->getActiveSheet()->setCellValue('D3', $heading); $objPHPExcel->getActiveSheet()->setCellValue('A4', 'AccountId'); $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Opening Balance'); $objPHPExcel->getActiveSheet()->setCellValue('B4', $FromAccid); $objPHPExcel->getActiveSheet()->setCellValue('b5', $OpeningBalance); $objPHPExcel->getActiveSheet()->setCellValue('A7', 'Date'); $objPHPExcel->getActiveSheet()->setCellValue('C7', 'Particulars'); $objPHPExcel->getActiveSheet()->setCellValue('E7', 'VoucherType'); $objPHPExcel->getActiveSheet()->setCellValue('G7', 'VoucherNo'); $objPHPExcel->getActiveSheet()->setCellValue('I7', 'Debit'); $objPHPExcel->getActiveSheet()->setCellValue('K7', 'Credit'); //Add data using database $i = 8; $j = 1; $querry7 = "select * from GLTRANSACTIONS where ACCOUNT_ID = 'G0003'"; $res7 = odbc_exec($conn,$querry7); while(odbc_fetch_row($res7)) { if($j%2 == 0) { $Refrenceno = sprintf('C%d',$i); $RefrencenoVal = sprintf('D%d',$i); $Refrencedate = sprintf('E%d',$i); $RefrenceDateVal = sprintf('F%d',$i); $chequeNo = sprintf('C%d',$i+1); $chequeNoval = sprintf('D%d',$i+1); $chequeDate = sprintf('E%d',$i+1); $chequeDateVal = sprintf('F%d',$i+1); $objPHPExcel->getActiveSheet()->setCellValue($Refrenceno, 'RefrenceNo'); $objPHPExcel->getActiveSheet()->setCellValue($RefrencenoVal,odbc_result($res7,'REF_NO')); $objPHPExcel->getActiveSheet()->setCellValue($Refrencedate, 'RefrenceDate'); $objPHPExcel->getActiveSheet()->setCellValue($RefrenceDateVal,odbc_result($res7,'REF_DATE')); $objPHPExcel->getActiveSheet()->setCellValue($Refrencedate, 'ChequeNo'); $objPHPExcel->getActiveSheet()->setCellValue($RefrenceDateVal,odbc_result($res7,'CHEQUE_NO')); $objPHPExcel->getActiveSheet()->setCellValue($Refrencedate, 'ChequeDate'); $objPHPExcel->getActiveSheet()->setCellValue($RefrenceDateVal,odbc_result($res7,'CHEQUE_DATE')); $objPHPExcel->getActiveSheet()->getStyle($Refrenceno)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $objPHPExcel->getActiveSheet()->getStyle($RefrencenoVal)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $objPHPExcel->getActiveSheet()->getStyle($Refrencedate)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $objPHPExcel->getActiveSheet()->getStyle($RefrenceDateVal)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $objPHPExcel->getActiveSheet()->getStyle($chequeNo)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $objPHPExcel->getActiveSheet()->getStyle($chequeNoval)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $objPHPExcel->getActiveSheet()->getStyle($chequeDate)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $objPHPExcel->getActiveSheet()->getStyle($chequeDateVal)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $i = $i+2; } else { $cellindexcol1 = sprintf('A%d',$i); $cellindexcol2 = sprintf('C%d',$i); $cellindexcol3 = sprintf('E%d',$i); $cellindexcol4 = sprintf('G%d',$i); $cellindexcol5 = sprintf('I%d',$i); $cellindexcol6 = sprintf('K%d',$i); $objPHPExcel->getActiveSheet()->setCellValue($cellindexcol1, odbc_result($res7,'TR_DATE')); $objPHPExcel->getActiveSheet()->setCellValue($cellindexcol2, odbc_result($res7,'TR_DESCRIPTION')); $objPHPExcel->getActiveSheet()->setCellValue($cellindexcol3, odbc_result($res7,'VOUCHER_TYPE')); $objPHPExcel->getActiveSheet()->setCellValue($cellindexcol4, odbc_result($res7,'TRANSACTION_ID')); $objPHPExcel->getActiveSheet()->setCellValue($cellindexcol5, odbc_result($res7,'WITHDRAW_AMOUNT')); $objPHPExcel->getActiveSheet()->setCellValue($cellindexcol6, odbc_result($res7,'DEPOSIT_AMOUNT')); //for alignment $objPHPExcel->getActiveSheet()->getStyle($cellindexcol1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $objPHPExcel->getActiveSheet()->getStyle($cellindexcol2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY); $objPHPExcel->getActiveSheet()->getStyle($cellindexcol3)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle($cellindexcol4)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle($cellindexcol5)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle($cellindexcol6)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); } $j++; $i++; } // Set column widths echo date('H:i:s') . " Set column widths\n"; $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(true); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(true); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(true); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(true); // Merge cells echo date('H:i:s') . " Merge cells\n"; $objPHPExcel->getActiveSheet()->mergeCells('D1:E1'); // Set fonts echo date('H:i:s') . " Set fonts\n"; $objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setSize(20); $objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setSize(12); $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $objPHPExcel->getActiveSheet()->getStyle('D3')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('D3')->getFont()->setSize(10); $objPHPExcel->getActiveSheet()->getStyle('D3')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('D3')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $objPHPExcel->getActiveSheet()->getStyle('D3')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $objPHPExcel->getActiveSheet()->getStyle('B4')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('B4')->getFont()->setSize(10); $objPHPExcel->getActiveSheet()->getStyle('B4')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('B4')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $objPHPExcel->getActiveSheet()->getStyle('B4')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setSize(12); $objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $objPHPExcel->getActiveSheet()->getStyle('C7')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('C7')->getFont()->setSize(12); $objPHPExcel->getActiveSheet()->getStyle('C7')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('C7')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $objPHPExcel->getActiveSheet()->getStyle('C7')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $objPHPExcel->getActiveSheet()->getStyle('E7')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('E7')->getFont()->setSize(12); $objPHPExcel->getActiveSheet()->getStyle('E7')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('E7')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $objPHPExcel->getActiveSheet()->getStyle('E7')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $objPHPExcel->getActiveSheet()->getStyle('G7')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('G7')->getFont()->setSize(12); $objPHPExcel->getActiveSheet()->getStyle('G7')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('G7')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $objPHPExcel->getActiveSheet()->getStyle('G7')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $objPHPExcel->getActiveSheet()->getStyle('I7')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('I7')->getFont()->setSize(12); $objPHPExcel->getActiveSheet()->getStyle('I7')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('I7')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $objPHPExcel->getActiveSheet()->getStyle('I7')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $objPHPExcel->getActiveSheet()->getStyle('K7')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('K7')->getFont()->setSize(12); $objPHPExcel->getActiveSheet()->getStyle('K7')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('K7')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $objPHPExcel->getActiveSheet()->getStyle('K7')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); // Set thin black border outline around column $borderindex = sprintf('A1:K%d',$i); echo date('H:i:s') . " Set thin black border outline around column\n"; $styleThinBlackBorderOutline = array( 'borders' => array( 'outline' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000'), ), ), ); $objPHPExcel->getActiveSheet()->getStyle($borderindex)->applyFromArray($styleThinBlackBorderOutline); // Set alignments echo date('H:i:s') . " Set alignments\n"; $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML'); $objWriter->setSheetIndex(0); $objWriter->save(str_replace('.php', '.htm', __FILE__)); // Echo memory peak usage echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n"; // Echo done echo date('H:i:s') . " Done writing files.\r\n";Cập nhật lần cuối. 16/12/2017 Đăng bởi. müslüm ÇEN Làm cách nào để đặt định dạng số trong phpspreadsheet?Tạo tệp Excel có cài đặt định dạng số trong PHP bằng PHPSpreadSheet . thiết lập phụ thuộc. { "yêu cầu". { "phpoffice/phpspreadsheet". "^1. 3" } } nhà soạn nhạc. json Cài đặt phpspreadsheet Tạo một tệp PHP mới và bắt đầu mã hóa Làm cách nào để đặt định dạng tiền trong PhpSpreadsheet?Trong PHPExcel, bạn có thể thực hiện việc này bằng cách sử dụng các dòng sau. $this->phpExcelObject->getActiveSheet() ->getStyle('D4') ->getNumberFormat() ->setFormatCode(\PHPExcel_Style_NumberFormat. FORMAT_CURRENCY_USD_SIMPLE);
Làm cách nào để đọc giá trị ô Excel trong PHP?Có thể đọc giá trị của một ô bằng cách sử dụng phương thức getValue() . // Lấy giá trị từ ô A1 $cellValue = $spreadsheet->getActiveSheet()->getCell('A1')->getValue(); .
PHPExcel có bị phản đối không?Phiên bản PHPExcel mới nhất, 1. 8. 1, được phát hành vào năm 2015. Dự án đã chính thức ngừng hoạt động vào năm 2017 và được lưu trữ vĩnh viễn vào năm 2019 . |