Hi!
I am trying to read a XLS spreadsheet and I want all values to be strings, i.e., I do not want them to cast to any type. The problem I have is that I have some large numbers that are EAN codes, and the readers converts them to float with scientific notation, therefore, not valid numbers. For example, I have the code 123456789012345000 that is read as 1.2345678901234E+17.
I have tried many options to achieve this, trying to use a custom ValueBinder, as it seems the most legit option. This is the code I have:
$binder = new class extends DefaultValueBinder {
public function bindValue(Cell $cell, $value)
{
$cell->setValueExplicit($value, DataType::TYPE_STRING);
return true;
}
};
Cell::setValueBinder(
$binder
);
$reader = IOFactory::createReaderForFile($path);
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($path);
$rows = $spreadsheet->getActiveSheet()->toArray(null, false, false);No matter what I try, I can not get it to work, it never goes through my ValueBinder, and I have not found anything in the documentation. Any help?
Thank you!
setValueExplicit but the casting of your value to string it probably the normal one in PHP. Check this https://stackoverflow.com/questions/1471674/why-is-php-printing-my-number-in-scientific-notation-when-i-specified-it-as-00 you might find a few useful hints.
Yes, I know that that happens. The thing is that I lose precision in the number. For example, 123456789012345000 becomes "123456789012344992.000000" when I do an sprintf('%f', $var). But it is not a number, it is a string, and I want it to be read as string in the beginning; if it is read as a string and not as a number, I think I will be able to solve the problem.
I will keep looking for a solution.
Thanks for your answer @frantzmiccoli !
Another thing is that the reader never calls my ValueBinder, so line
$cell->setValueExplicit($value, DataType::TYPE_STRING);is never called.
I am working on a problem and my google-fu is not cutting it. The gist is I need to make sure that text does not overflow a cell, with the problem being that I am using merged cells and Excel does not support auto-height with merged cells. SO, is there a simple way to check if the text in a given cell is overflowing? If so I could increment the row height until all text is visible.
Hello? Anyone in here?
I setup the page my excel file with Page Layout view and width.
getSheetView()->setView(SheetView::SHEETVIEW_PAGE_LAYOUT) and
getPageSetup()->setFitToWidth(1);
The problem is that I start the excel with Microsoft 2007, Pagesetup width is not work.
When I click anything in menu of excel.
Pagesetup width is work.
Is it problem of Excel or Is there any code to solve that problem?
Is there a way to apply a border style for a range of cells other than the default method :
$spreadsheet->getActiveSheet()->getStyle('A1:E10')->getBorders()->getTop()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);This method applies the border for the whole block and not each cell
@Med-Seif I think you may find this helpful: https://phpspreadsheet.readthedocs.io/en/develop/topics/recipes/#styles
You can use allBorders like
$stlyes = [
'borders' => [
'allBorders' => [
'borderStyle' => Style\Border::BORDER_THICK,
]
]This will apply border to each cell in the range you have selected, rather than to just the outer cells.
