PHP PHPExcel_Worksheet::setCellValueByColumnAndRow方法代码示例

 protected function writeCell($value, $column, $row, $config)
 {
     // auto type
     if (!isset($config['type']) || $config['type'] === null) {
         $this->sheet->setCellValueByColumnAndRow($column, $row, $value);
     } elseif ($config['type'] === 'date') {
         if (!is_int($value)) {
             $timestamp = strtotime($value);
         }
         $this->sheet->SetCellValueByColumnAndRow($column, $row, \PHPExcel_Shared_Date::PHPToExcel($timestamp));
         if (!isset($config['styles']['numberformat']['code'])) {
             $config['styles']['numberformat']['code'] = $this->defaultDateFormat;
         }
     } elseif ($config['type'] === 'url') {
         if (isset($config['label'])) {
             if ($config['label'] instanceof \Closure) {
                 // NOTE: calculate label on top level
                 $label = call_user_func($config['label']);
             } else {
                 $label = $config['label'];
             }
         } else {
             $label = $value;
         }
         $urlValid = filter_var($value, FILTER_VALIDATE_URL) !== false;
         if (!$urlValid) {
             $label = '';
         }
         $this->sheet->setCellValueByColumnAndRow($column, $row, $label);
         if ($urlValid) {
             $this->sheet->getCellByColumnAndRow($column, $row)->getHyperlink()->setUrl($value);
         }
     } else {
         $this->sheet->setCellValueExplicitByColumnAndRow($column, $row, $value, $config['type']);
     }
     if (isset($config['styles'])) {
         $this->sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($config['styles']);
     }
 }

 /**
  * Render the header row
  */
 protected function renderHeader()
 {
     $columnNumber = 0;
     // Headers
     if ($this->templateVariableContainer->exists('listCaptions')) {
         foreach ($this->templateVariableContainer['listCaptions'] as $columnIdentifier => $caption) {
             /* @var $caption Tx_PtExtlist_Domain_Model_List_Cell */
             $this->activeSheet->setCellValueByColumnAndRow($columnNumber, $this->rowNumber, strip_tags($caption->getValue()));
             $excelSettings = $this->getExcelSettingsByColumnIdentifier($columnIdentifier);
             /**
              * Width
              */
             if (is_array($excelSettings) && array_key_exists('width', $excelSettings)) {
                 $this->activeSheet->getColumnDimensionByColumn($columnNumber)->setWidth($excelSettings['width']);
             } else {
                 $this->activeSheet->getColumnDimensionByColumn($columnNumber)->setAutoSize(true);
             }
             $this->doCellStyling($columnNumber, $columnIdentifier, 'header');
             $columnNumber++;
         }
     }
     $this->rowNumber++;
 }

 /**
  * Populates the PHPExcel sheet with the headers from the result query
  *
  * @param SMWQueryResult $res The query result
  */
 protected function populateDocumentWithHeaders(SMWQueryResult $res)
 {
     $this->colNum = 0;
     foreach ($res->getPrintRequests() as $pr) {
         $header = $pr->getLabel();
         if ($this->showLabel($header)) {
             $this->sheet->setCellValueByColumnAndRow($this->colNum, self::HEADER_ROW_OFFSET, $header)->getStyleByColumnAndRow($this->colNum, self::HEADER_ROW_OFFSET)->getFont()->setBold(true);
             $this->colNum++;
         }
     }
 }

 /**
  * @author caochunhui@dachuwang.com
  * @description 用数组和地址直接生成excel文件
  * 每一个数组占一个sheet
  */
 private function _convert_array_to_excel($arr = array(), $sheet_titles = array(), $out_name = '', $barcode_arr = array())
 {
     //下面的代码是抄的。
     //set cache
     $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
     PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
     //open excel file
     $write_objPHPExcel = new PHPExcel();
     $write_objPHPExcel->getDefaultStyle()->getFont()->setName('simsun')->setSize(10);
     //下面要循环了
     $sheet_cnt = 0;
     foreach ($arr as $item) {
         //用订单id.csv来命名每一个sheet
         $out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $sheet_titles[$sheet_cnt]);
         //$out_sheet->setTitle($item);
         //row index start from 1
         $row_index = 0;
         foreach ($item as $row) {
             $row_index++;
             //$cellIterator = $row->getCellIterator();
             //$cellIterator->setIterateOnlyExistingCells(false);
             //column index start from 0
             $column_index = -1;
             foreach ($row as $cell) {
                 $column_index++;
                 //var_dump($cell);
                 $out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell, PHPExcel_Cell_DataType::TYPE_STRING);
             }
         }
         //如果条码数组不为空，那么说明需要在sheet里插入条码
         if (!empty($barcode_arr) && isset($barcode_arr[$sheet_cnt])) {
             $barcode_download_res = $this->_download_barcode($barcode_arr[$sheet_cnt]);
             if ($barcode_download_res['code'] == 200) {
                 //no pic you say a jb
                 $pic_path = $barcode_download_res['file'];
                 $objDrawing = new PHPExcel_Worksheet_Drawing();
                 $objDrawing->setName('barcode');
                 $objDrawing->setDescription('');
                 $objDrawing->setPath($pic_path);
                 $objDrawing->setHeight(50);
                 $objDrawing->setCoordinates('D26');
                 //$objDrawing->setOffsetX(10);
                 //$objDrawing->getShadow()->setVisible(true);
                 //$objDrawing->getShadow()->setDirection(36);
                 $objDrawing->setWorksheet($out_sheet);
                 //no pic you say a jb
             }
         }
         $write_objPHPExcel->addSheet($out_sheet);
         $sheet_cnt++;
     }
     $write_objPHPExcel->removeSheetByIndex(0);
     //删除第一个空sheet
     //上面要循环了
     //上面的代码是抄的
     //write excel file
     $objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel);
     $dir_name = dirname($out_name);
     if (!is_dir($dir_name)) {
         $res = mkdir($dir_name, 0777, TRUE);
     }
     $objWriter->save($out_name);
 }

 /**
  * Write one blank somewhere in the worksheet.
  *
  * @param integer $row    Zero indexed row
  * @param integer $col    Zero indexed column
  * @param mixed   $format The XF format for the cell
  */
 public function write_blank($row, $col, $format = null)
 {
     $this->worksheet->setCellValueByColumnAndRow($col, $row + 1, '');
     $this->apply_format($row, $col, $format);
 }

 /**
  * Set value in specific cell
  * @param \PHPExcel_Worksheet $sheet      The worksheet
  * @param integer             $col        The selected column
  * @param integer             $row        The selected row
  * @param array               $value      The values to insert
  * @param array               $format     Associative arrays with formats
  * @param array               $styleArray An array representing the style
  * @param $row
  */
 protected function applyValue(\PHPExcel_Worksheet $sheet, $col, $row, $value, $format, $styleArray = array())
 {
     if (0 == count($styleArray)) {
         $styleArray = $this->getDefaultStyleArrayForRow($row);
     }
     $style = new \PHPExcel_Style();
     $style->applyFromArray($styleArray);
     switch ($format) {
         case Format::DATE:
             if (!$value instanceof \DateTime) {
                 $date = new \DateTime($value);
             } else {
                 $date = $value;
             }
             $value = \PHPExcel_Shared_Date::PHPToExcel($date);
             $style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
             break;
         case Format::DATETIME:
             if (!$value instanceof \DateTime) {
                 $date = new \DateTime($value);
             } else {
                 $date = $value;
             }
             $value = \PHPExcel_Shared_Date::PHPToExcel($date);
             $style->getNumberFormat()->setFormatCode(self::FORMAT_DATETIME);
             break;
         case Format::FLOAT2:
             $style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
             break;
         case Format::INTEGER:
             $style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
             break;
         case Format::MONEY:
         case Format::MONEY2:
             $style->getNumberFormat()->setFormatCode(self::FORMAT_EUR);
             break;
         case Format::PCT:
             $style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
             break;
         case Format::PCT2:
             $style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);
             break;
         case Format::STRING:
             $style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
             break;
     }
     $sheet->setCellValueByColumnAndRow($col, $row, $value);
     $sheet->duplicateStyle($style, \PHPExcel_Cell::stringFromColumnIndex($col) . $row);
 }

 /**
  * Writes a row of values
  *
  * @param \PHPExcel_Worksheet $worksheet
  * @param array               $data      An array of values with column indexes as keys
  */
 protected function writeValues(\PHPExcel_Worksheet $worksheet, array $data)
 {
     $worksheetName = $worksheet->getTitle();
     $row = $this->rowIndexes[$worksheetName];
     foreach ($this->labels[$worksheet->getTitle()] as $column => $label) {
         if (isset($data[$label])) {
             $worksheet->setCellValueByColumnAndRow($column, $row, $data[$label]);
         }
     }
     $this->rowIndexes[$worksheetName]++;
 }

 /**
  * @author caochunhui@dachuwang.com
  * @description 用数组和地址直接生成excel文件
  * 每一个数组占一个sheet
  */
 private function _convert_array_to_excel($arr = array(), $sheet_titles = array(), $out_name = '')
 {
     //下面的代码是抄的。
     //set cache
     $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
     PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
     //open excel file
     $write_objPHPExcel = new PHPExcel();
     //下面要循环了
     $sheet_cnt = 0;
     foreach ($arr as $item) {
         //用订单id.csv来命名每一个sheet
         $out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $sheet_titles[$sheet_cnt]);
         //$out_sheet->setTitle($item);
         //row index start from 1
         $row_index = 0;
         foreach ($item as $row) {
             $row_index++;
             //$cellIterator = $row->getCellIterator();
             //$cellIterator->setIterateOnlyExistingCells(false);
             //column index start from 0
             $column_index = -1;
             foreach ($row as $cell) {
                 $column_index++;
                 //var_dump($cell);
                 $out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell);
             }
         }
         $write_objPHPExcel->addSheet($out_sheet);
         $sheet_cnt++;
     }
     $write_objPHPExcel->removeSheetByIndex(0);
     //删除第一个空sheet
     //上面要循环了
     //上面的代码是抄的
     //write excel file
     $objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel);
     $dir_name = dirname($out_name);
     if (!is_dir($dir_name)) {
         $res = mkdir($dir_name, 0777, TRUE);
     }
     $objWriter->save($out_name);
 }

 protected function set_sheet_data(array $data, PHPExcel_Worksheet $sheet)
 {
     foreach ($data as $row => $columns) {
         foreach ($columns as $column => $value) {
             $sheet->setCellValueByColumnAndRow($column, $row, $value);
         }
     }
 }

 /**
  * generating the worksheet
  *
  * @param UDate              $lastUpdatedInDB
  * @param PHPExcel_Worksheet $sheet
  * @param array              $data
  * @param string             $preFix
  * @param bool               $debug
  *
  * @return Array The array of images
  */
 private static function _genSheet(UDate $lastUpdatedInDB, PHPExcel_Worksheet &$sheet, array $data, $preFix = '', $debug = false)
 {
     self::_log('-- Generating the sheets: ', __CLASS__ . '::' . __FUNCTION__, $preFix);
     $rowNo = 1;
     $titles = array_keys(self::_getRowWithDefaultValues($lastUpdatedInDB, null, $preFix, $debug));
     foreach ($titles as $colNo => $colValue) {
         $sheet->setCellValueByColumnAndRow($colNo, $rowNo, $colValue);
     }
     $rowNo += 1;
     self::_log('Generated title row', '', $preFix . self::TAB);
     $imageFiles = array();
     foreach ($data as $index => $product) {
         self::_log('ROW: ' . $index . ', SKU: ' . $product->getSku(), '', $preFix . self::TAB);
         if (!$product instanceof Product) {
             self::_log('SKIPPED, invalid product.', '', $preFix . self::TAB . self::TAB);
             continue;
         }
         $rowValue = self::_getRowWithDefaultValues($lastUpdatedInDB, $product, $preFix, $debug);
         $rowValues = array($rowValue);
         $images = ProductImage::getAllByCriteria('productId = ? and updated > ?', array($product->getId(), trim($lastUpdatedInDB)));
         //images
         self::_log('Got ' . count($images) . ' ProductImage(s) after "' . trim($lastUpdatedInDB) . '" for productID: ' . $product->getId(), '', $preFix . self::TAB);
         if (count($images) > 0) {
             foreach ($images as $index => $image) {
                 if (!($asset = $image->getAsset()) instanceof Asset) {
                     self::_log('No Asset found for Image Index: ' . $index, '', $preFix . self::TAB . self::TAB);
                     continue;
                 }
                 if (!is_file($asset->getPath())) {
                     self::_log('No file found: ' . $asset->getPath(), '', $preFix . self::TAB . self::TAB);
                     continue;
                 }
                 $imageFiles[] = array('fileName' => $asset->getFilename(), 'filePath' => $asset->getPath());
                 self::_log('Added array(fileName=>' . $asset->getFilename() . ', filePath => ' . $asset->getPath() . ') to imageFiles', '', $preFix . self::TAB . self::TAB);
                 $imageFilePath = '{{IMAGE_IMPORT_DIR}}/' . self::$_imageDirName . '/' . $asset->getFilename();
                 self::_log('New Image Path into the CSV("image" column):' . $imageFilePath, '', $preFix . self::TAB . self::TAB);
                 if (intval($index) === 0) {
                     $rowValues[0]['image'] = $imageFilePath;
                     $rowValues[0]['small_image'] = $imageFilePath;
                     $rowValues[0]['thumbnail'] = $imageFilePath;
                 } else {
                     $rowValues[0]['media_gallery'] = $rowValues[0]['media_gallery'] . ';' . $imageFilePath;
                     self::_log('added onto media_gallery: ' . $rowValues[0]['media_gallery'], '', $preFix . self::TAB . self::TAB);
                 }
             }
         }
         //start looping in the outer loop
         self::_log('There are ' . count($rowValues) . ' row(s) in total.', '', $preFix . self::TAB);
         foreach ($rowValues as $row) {
             foreach (array_values($row) as $colNo => $colValue) {
                 $sheet->setCellValueByColumnAndRow($colNo, $rowNo, $colValue);
             }
             $rowNo += 1;
         }
         self::_log('ADDED.', '', $preFix . self::TAB . self::TAB);
     }
     self::_log('-- DONE', __CLASS__ . '::' . __FUNCTION__, $preFix);
     return $imageFiles;
 }

 /**
  * Método que gera a planilha de evolução anual por unidade e ano
  */
 public function evolucaoAnual()
 {
     //Inicializando os filtros usados na página
     $this->filtro->initGets(array("unidade", "ano"));
     //Inicializando os objetos básicos
     $sqlUnidade = new SqlUnidade();
     $sqlSerie = new SqlSerie();
     $sqlEnsino = new SqlEnsino();
     $sqlAfa = new SqlAfa();
     //Contadores em geral
     $linha = 0;
     //Define a linha inicial da impressão da planilha
     $coluna = 0;
     //Define a coluna inicial da planilha
     $colunaInicial = $coluna;
     $r = 1;
     //Contador "R" das planilhas
     //Objetos e variaveis básicas da planilha
     $conceitos = array("D" => "Deficiente", "I" => "Insuficiente", "R" => "Regular", "B" => "Bom", "L" => "Louvor");
     $bimestres = array("1" => "1º Bimestre", "2" => "2º Bimestre", "3" => "3º Bimestre", "4" => "4º Bimestre");
     $bimRomanos = array("1" => "I", "2" => "II", "3" => "III", "4" => "IV");
     //Fazendo as consultas iniciais
     $ensinos = $sqlEnsino->listarTodos($this->filtro);
     $avaliacoea = $sqlAfa;
     $avaliacoes = $afaObj->listarAvaliacoes($unidade, $ano);
     die("ok");
     //Iniciando a geração da planilha
     while ($ensino = $ensinos->fetchObject()) {
         $avaliacoes = $afaObj->buscarAvaliacoes($ensino->pk_ensino, $unidade, $ano);
         //Teste para somente exibir as plainlhas de ensinos que contenham registros
         if (count($avaliacoes)) {
             //Ajustando a primeira coluna
             $this->workSheet->getColumnDimensionByColumn($coluna)->setWidth(33);
             //Trecho referente a primeira planilha
             //PREENCHENDO A PRIMEIRA PLANILHA
             //Definindo o título da planiplha
             $tituloPlanilhas = "TABELA R" . $r++ . " – DISTRIBUIÇÃO PERCENTUAL DOS CONCEITOS " . "FORMATIVOS DOS ALUNOS DO " . strtoupper($ensino->nome_ensino) . " EM CADA BIMESTRE";
             $this->workSheet->setCellValueByColumnAndRow($coluna, ++$linha, $tituloPlanilhas)->getStyleByColumnAndRow($coluna, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FFF2CC")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
             $this->workSheet->getStyleByColumnAndRow($coluna, $linha)->getFont()->setBold(TRUE);
             //Tornando a font bolder
             //
             $linhaReferencia = $linha;
             $linha++;
             $coluna++;
             //Criando o cabeçalho da primeira planilha
             foreach ($bimestres as $bimestre) {
                 $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, $bimestre)->getStyleByColumnAndRow($coluna, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FCE4D6")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                 $this->workSheet->mergeCellsByColumnAndRow($coluna, $linha, $coluna + 4, $linha);
                 //Mesclando as colunas
                 $coluna += 5;
             }
             $this->workSheet->getStyleByColumnAndRow(--$coluna, $linha)->applyFromArray(array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))));
             $coluna = $colunaInicial;
             //Resetando o contador de colunas
             // Adicionando as colunas indicadoras de níveis
             $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, "Série")->getStyleByColumnAndRow($coluna, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FCE4D6")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
             $this->workSheet->mergeCellsByColumnAndRow($coluna, $linha, $coluna++, ++$linha);
             //Mesclando as colunas
             //
             foreach (range(0, 3) as $indice) {
                 foreach (array_keys($conceitos) as $letra) {
                     $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, $letra)->getStyleByColumnAndRow($coluna++, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FCE4D6")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                 }
             }
             //Mesclando o título da planilha
             $this->workSheet->mergeCellsByColumnAndRow($colunaInicial, $linhaReferencia, --$coluna, $linhaReferencia);
             $this->workSheet->getStyleByColumnAndRow($coluna, $linhaReferencia)->applyFromArray(array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))));
             //
             $linha++;
             //Definindo as variaveis para calculo de médias
             $mdD1B = $mdI1B = $mdR1B = $mdB1B = $mdL1B = array();
             $mdD2B = $mdI2B = $mdR2B = $mdB2B = $mdL2B = array();
             $mdD3B = $mdI3B = $mdR3B = $mdB3B = $mdL3B = array();
             $mdD4B = $mdI4B = $mdR4B = $mdB4B = $mdL4B = array();
             $this->workSheet->getStyleByColumnAndRow(--$coluna, $linha)->applyFromArray(array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))));
             $coluna = $colunaInicial;
             //Resetando o contador de colunas
             //adicionando as séries da primeira coluna
             foreach ($avaliacoes as $avaliacao) {
                 $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, $avaliacao["serie_nome"])->getStyleByColumnAndRow($coluna++, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FCE4D6")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                 //Separando os dados
                 foreach (array_keys($bimestres) as $bimestre) {
                     foreach (array_keys($conceitos) as $conceito) {
                         $comando = '$qt' . $conceito . $bimestre . 'B = (float) ' . '(isset($avaliacao["qt-' . strtolower($conceito) . '-' . $bimestre . 'bi"]) ' . '? $avaliacao["qt-' . strtolower($conceito) . '-' . $bimestre . 'bi"] : 0.0);';
                         eval($comando);
                     }
                     $comando = '$qt' . $bimestre . 'B = (float) (isset($avaliacao["qt-' . $bimestre . 'bi"]) ' . '? $avaliacao["qt-' . $bimestre . 'bi"] : 0.0);';
                     eval($comando);
                 }
                 //Definindo os percentudais
                 foreach (array_keys($bimestres) as $bimestre) {
                     foreach (array_keys($conceitos) as $conceito) {
                         $comando = '$per' . $conceito . $bimestre . 'B = ' . 'Matematica::percentualDe($qt' . $conceito . $bimestre . 'B, $qt' . $bimestre . 'B);';
                         eval($comando);
                         $comando = 'array_push($md' . $conceito . $bimestre . 'B, $per' . $conceito . $bimestre . 'B);';
                         eval($comando);
                     }
//.........这里部分代码省略.........

 /**
  * Add a new row to a {@link PHPExcel_Worksheet} based of a
  * {@link DataObjectInterface}
  * @param PHPExcel_Worksheet  $sheet
  * @param DataObjectInterface $item
  * @param array               $fields List of fields to include
  * @return PHPExcel_Worksheet
  */
 protected function addRow(PHPExcel_Worksheet &$sheet, DataObjectInterface $item, array $fields)
 {
     $row = $sheet->getHighestRow() + 1;
     $col = 0;
     foreach ($fields as $field => $type) {
         $sheet->setCellValueByColumnAndRow($col, $row, $item->{$field});
         $col++;
     }
     return $sheet;
 }