Advertisement
Guest User

Untitled

a guest
Feb 12th, 2016
46
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.02 KB | None | 0 0
  1. // Do not confuse with { instead of [. It's okey.
  2. // $export_data and $data both are same (Input array).
  3. {
  4. "0": {
  5. "customer_id": 1,
  6. "name": "John Stevens",
  7. "Date of 1 Purchase": "2014-08-05T00:00:00+0000",
  8. "Date of 2 Purchase": "2014-09-05T00:00:00+0000",
  9. "Date of 3 Purchase": "2014-10-05T00:00:00+0000",
  10. ...
  11. ...
  12. ... 350 Cols ...
  13. }
  14. "1": {
  15. ...
  16. }
  17. ...
  18. ...
  19. "999 Rows"
  20. }
  21.  
  22. $r = 1;
  23. $filename = FILE_PATH . 'galliyan.xlsx';
  24.  
  25. $header = array_keys($export_data[0]);
  26.  
  27. $objPHPExcel = new PHPExcel();
  28. $col = 0;
  29. foreach ($header as $field) {
  30. $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
  31. $col++;
  32. }
  33. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  34. $objWriter->save($filename);
  35. chmod($filename, 0777);
  36. $r++;
  37.  
  38. $objPHPExcel = PHPExcel_IOFactory::load($filename);
  39. foreach($export_data as $row) {
  40. $col = 0;
  41. foreach ($row as $ro) {
  42. $objPHPExcel->getActiveSheet()->setCellvalueByColumnAndRow($col, $r, $ro);
  43. $col++;
  44. }
  45.  
  46. $r++;
  47. }
  48. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  49. $objWriter->save($filename);
  50. chmod($filename, 0777);
  51.  
  52. foreach ($data as $row) {
  53. if (!$flag) {
  54. $header = array();
  55. // display field/column names as first row
  56. $header = array_keys($row);
  57. $header = $this->setExcelHeaders($header);
  58. $this->createExcelFile($dir_name, $filename, $header, $export_type);
  59. $flag = TRUE;
  60. }
  61.  
  62. array_walk($row, array($this, 'cleanData'));
  63. array_push($values, $row);
  64.  
  65. }
  66.  
  67. $values = $this->setValues($values);
  68. $this->writeExcelFile($dir_name, $filename, $values);
  69.  
  70. function setExcelHeaders($hdrs) {
  71. $header = '';
  72. foreach ($hdrs as $title_val) {
  73. $header .= $title_val . "t";
  74. }
  75. return $header;
  76. }
  77.  
  78. function createExcelFile($dir_name, $filename, $header, $export_type = '') {
  79. $fp = fopen($dir_name . "/" . $filename, 'w');
  80. fwrite($fp, "$headern");
  81. fclose($fp);
  82.  
  83. $permission = Configure::read('Config.PERMISSION');
  84. if ($export_type == "") {
  85. chmod($dir_name, $permission);
  86. }
  87. chmod($dir_name . "/" . $filename, $permission);
  88. }
  89.  
  90. public function cleanData(&$str) {
  91. $str = preg_replace("/t/", "\t", $str);
  92. $str = preg_replace("/r?n/", "\n", $str);
  93. if (strstr($str, '"'))
  94. $str = '"' . str_replace('"', '""', $str) . '"';
  95. }
  96.  
  97. private function setValues($all_vals) {
  98. $data = '';
  99. for ($i = 0; $i < count($all_vals); $i++) {
  100. $line = '';
  101. foreach ($all_vals[$i] as $value) {
  102. if ((!isset($value)) || ( $value == "")) {
  103. $value = "t";
  104. }
  105. else {
  106. $value = str_replace('"', '""', $value);
  107. $value = '"' . $value . '"' . "t";
  108. }
  109. $line .= $value;
  110. }
  111. $data .= trim($line) . "n";
  112. }
  113. return $values = str_replace("r", "", $data);
  114. }
  115.  
  116. function writeExcelFile($dir_name, $filename, $data) {
  117. $fp = fopen($dir_name . "/" . $filename, 'a');
  118. fwrite($fp, "$data");
  119. fclose($fp);
  120. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement