0

I am working on "export to excel" sheet functionality using "PhpOffice\PhpSpreadsheet" library, till export to excel sheet working fine but My Problem here is that I have a total amount column where I have to add Indian currency symbol. I searched for a solution but no luck yet.

My Code:

require_once('../vendor/autoload.php'); 
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$objPHPSpreadSheet = new Spreadsheet();
$sheet = $objPHPSpreadSheet->getActiveSheet(); 
$results=getsalesReport($data);
$sheet->setCellValue('A1', 'title1');
$sheet->setCellValue('B1', 'title2');
$sheet->setCellValue('C1', 'title3'); 

$rowCount = 2;
if(isset($results) && !empty($results)){
  foreach ($results as $key => $val) {
     $total_amount = '₹'.$val['TotalAmount'];  // I added '₹' for INR currency symbol, its working fine in HTML
     $sheet->setCellValue('A'.$rowCount, $val['my_val1']);
     $sheet->setCellValue('B'.$rowCount, $val['my_val2']);
     $sheet->setCellValue('C'.$rowCount, $total_amount);
     $rowCount++; 
  }
 }
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=sales_report.xlsx');
header('Cache-Control: max-age=0');
$writer = new Xlsx($objPHPSpreadSheet);
$writer->save("php://output");

How to add Indian currency symbol to total_amount cell in Excel sheet? Hope someone helps. Thanks.

Prasad Patel
  • 707
  • 3
  • 16
  • 53
  • HTML !== Excel. In MS Excel, you should store the number (or SUM formula) in the cell; and apply a number format mask for displaying it as a currency; so why do you think PHPSpreadsheet does it any differently? – Mark Baker Jan 04 '18 at 09:35
  • And use the currency symbol in the number format mask, not an HTML entity..... MS Excel !== HTML – Mark Baker Jan 04 '18 at 09:36
  • First, Thanks for the reply. I know that HTML!=Excel, why I mentioned is that you will understand better. I didn't understand your reply. could you plz tell how to achieve this. – Prasad Patel Jan 04 '18 at 09:51
  • Take a look at some of the code examples provided with PHPSpreadsheet; in particular `05_Feature_demo.php` (which uses the template code in [sampleSpreadsheet.php](https://github.com/PHPOffice/PhpSpreadsheet/blob/develop/samples/templates/sampleSpreadsheet.php#L121) to set a currency number format mask.... you'd have to set your own string format mask rather than use one of the built-in constants, but that's how MS Excel renders numbers for display – Mark Baker Jan 04 '18 at 10:12

0 Answers0