Create Excel file using PHP

Question

I am using this code to generate an excel file. In my data there is one column named barcode, suppose the barcode is 012525551951200001.110100. When I write to an excel file it appears as 1.16222E+16, how can I make it appear as 012525551951200001.110100?

$DB_Server = "**********";
$DB_Username = "**********";
$DB_Password = "**********";
$DB_DBName = "**********";
$DB_TBLName = "patient";
$xls_filename = 'export_'.date('Y-m-d').'.xls'; 
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password);
$Db = @mysql_select_db($DB_DBName, $Connect);
$result = @mysql_query($sql,$Connect);
HEADER("Content-Type: application/xls");
HEADER("Content-Disposition: attachment; filename=Questionnaires Records.$xls_filename");
HEADER("Pragma: no-cache");
HEADER("Expires: 0");

$sep = "\t"; 

for ($i = 0; $i<mysql_num_fields($result); $i++) {
echo mysql_field_name($result, $i) . "\t";
}
print("\n");

while($row = mysql_fetch_row($result))
{
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result); $j++)
{
  if(!isset($row[$j])) {
    $schema_insert .= "NULL".$sep;
  }
  elseif ($row[$j] != "") {
    $schema_insert .= "$row[$j]".$sep;
  }
  else {
    $schema_insert .= "".$sep;
  }
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
}

You need to make sure you format the cell that will hold the barcode as Text — Hmax, Jul 31 '18 at 09:49
This will help: https://stackoverflow.com/questions/17143236/phpexcel-set-cell-type-before-writing-a-value-in-it — Lovepreet Singh, Jul 31 '18 at 09:50
@RamandeepSingh you need to format the Excel cell/column before writing. — Hmax, Jul 31 '18 at 09:58

score -1 · Answer 1 · edited Jul 31 '18 at 11:15

-1

You should increase the size of that column using the below code:

$objPHPExcel->getActiveSheet()->getColumnDimension(A1)->setWidth(7);

edited Jul 31 '18 at 11:15

Hmax

314
6
16

answered Jul 31 '18 at 10:06

Nashir

254
1
5
9

Create Excel file using PHP

1 Answers1