Asked  7 Months ago    Answers:  5   Viewed   89 times

I have a excel file with two sheet:

  1. Worksheet;
  2. List- list of items which will display in Worksheet sheet as a list item.

Please see the below images:

List Worksheet

I want to generate this using PHPExcel library. I have tried but not getting the expected results. See my below code :

$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Soumya Biswas")
                             ->setLastModifiedBy("Soumya Biswas")
                             ->setTitle("Office 2007 XLSX Test Document")
                             ->setSubject("Office 2007 XLSX Test Document")
                             ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                             ->setKeywords("office 2007 openxml php")
                             ->setCategory("Test result file");


// Create a first sheet
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A5', "List");


// Set data validation
$objValidation = $objPHPExcel->getActiveSheet()->getCell('B5')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Value is not in list.');
$objValidation->setPromptTitle('Pick from list');
$objValidation->setPrompt('Please pick a value from the drop-down list.');
$objValidation->setFormula1('"$List.$A$1:$A$10"');  // Make sure to put the list items between " and "  !!!

$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle('List');

for ($i = 1; $i <= 10; $i++) {
    $objPHPExcel->getActiveSheet()->setCellValue("A{$i}", "List Item {$i}");
}
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);




header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="data-validation.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit; 

 Answers

70

I think the correct syntax for referencing ranges of cells in another sheet is:

List!$A$1:$A$10

So you should try:

$objValidation->setFormula1('List!$A$1:$A$10'); // tested it, worked for me

Got the idea from http://phpexcel.codeplex.com/discussions/320393:

->setFormula1("Worksheet!A1:{$endCell}1");// work....

Although this guy had another problem with using named ranges.

Background: I think with:

$objValidation->setFormula1('"$List.$A$1:$A$10"');

you're explicity using the given string between the quotation marks as the list value as explained here: here (where you probably got this snippet in the first place) or here. But since you don't want to use fixed list items but dynamically referred ones, you should omit the double quotation marks.

Saturday, May 29, 2021
 
Ula
answered 7 Months ago
Ula
92

You need DomPDF for rendering the PDF. Check if you have the DomPDF library, otherwise download and implement it as follow:

$rendererName = PHPExcel_Settings::PDF_RENDERER_DOMPDF;
$rendererLibrary = 'domPDF0.6.0beta3';
$rendererLibraryPath = dirname(__FILE__). 'libs/classes/dompdf' . $rendererLibrary;
Wednesday, March 31, 2021
 
Akdeniz
answered 9 Months ago
21

You're correct: row and column styles aren't supported by PHPExcel.

Cell styling is, but you can also set style by a range of cells:

$objPHPExcel->getActiveSheet()
    ->getStyle('E1:E256')
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
Wednesday, March 31, 2021
 
Jesse
answered 9 Months ago
33

EDIT I added a pull request with a fix: https://github.com/PHPOffice/PHPExcel/pull/251

This happens when you try to duplicate cell's style to the same cell; Take a look at this:

$phpe = new PHPExcel();
$sheet = $phpe->createSheet();

$sheet->setCellValue('A1', 'hi there') ;
$sheet->setCellValue('A2', 'hi again') ;

$sheet->duplicateStyle($sheet->getStyle('A1'), 'A2');

$writer = new PHPExcel_Writer_Excel2007($phpe);
$writer->save('./test.xlsx');

It will work just fine. BUT if I add another line like this:

$sheet->duplicateStyle($sheet->getStyle('A1'), 'A1');

then bang, infinite recursion starts after calling the save method

To fix your code, you should modify this part:

$sheet->duplicateStyle((($level == 0) ?  $this->styles['category'] : $this->styles['subcategory']), Cell::NUMBER . $row);

To something along the lines of:

$style = ($level == 0) ?  $this->styles['category'] : $this->styles['subcategory'];
$targetCoords = Cell::NUMBER . $row;
if($style->getActiveCell() != $targetCoords) {
    $sheet->duplicateStyle($style, $targetCoords);
}
Saturday, May 29, 2021
 
rblarsen
answered 7 Months ago
29

Short answer:

gem install "minitest-around"

Long answer:

before/after or setup/teardown in minitest are NOT hooks as in rspec, therefore you can't have multiple before/after or setup/teardown in minitest, since what they do is just redefining the method.

To solve this issue, you can use minitest-around, which adds support for multiple before/after or setup/teardown and around, simply add the gem to your test group:

# put in your Gemfile
gem 'minitest-around', group: :test

For setting up the database_cleaner, you can have it as you want, following is an example of the setup:

# tests/support/database_cleaner.rb
DatabaseCleaner.strategy = :transaction
DatabaseCleaner.clean_with(:truncation)

class Minitest::Rails::ActionController::TestCase
  def setup
    DatabaseCleaner.start
  end

  def teardown
    DatabaseCleaner.clean
  end
end

And in your test files:

# tests/your/test/file_test.rb
require 'support/database_cleaner'

# assertions here ...

That's it, see the Github for detailed info.

Thursday, August 12, 2021
 
ManojGeek
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share