23820

Explicitly set a cell’s datatype as text for number values

I want to explicitly set a cell’s datatype as text for number values.

I am fetching data from my MySQL database and exporting to excel file using PHPExcel Library. I have large about 20 digit long numbers which i want to export to excel cell(each cell of entire column will have different numbers).

I have found that you can do this by code metioned PHPExcel Documentation 4.6.7 .

$i=0; foreach($dd as $d) { $objPHPExcel ->setActiveSheetIndex(0) ->getcell('A'.$i) ->setValueExplicit($d['serial_no'], PHPExcel_Cell_DataType::TYPE_STRING); $i++; }

$d['serial_no']-different serials coming from database e.g."9876543211234567890".

I have implemented this code its working fine. But when i open that excel sheet and try to modify that cell then click on anywhere else it again gets converted to number format(client's requirement: it should remain text).

I want stay that cell text only even if i edit in it.

Answer1:

I've not tested it, but possibly setting the cell as "quotePrefix" may prevent MS Excel from trying to convert the datatype when editing it with MS Excel

$objPHPExcel->getActiveSheet() ->getStyle('A2:O128') ->setQuotePrefix(true);

Excel2007 only

Answer2:

$objPHPExcel->getActiveSheet() ->getStyle('A'.$i) ->getNumberFormat() ->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );

Recommend

  • npm install error though Python already included [duplicate]
  • Code works extremely slowly and does not print output
  • Gradle complains about JUnit version on TestNG task
  • Is there a better way to run CLI commands with Node.js?
  • g++: error: CreateProcess no such file or directory
  • How to add plugin dependencies to pom.xml
  • Use query params of parent route to refresh the model in subroute
  • In Angular 2 how to get @Input value updated inside component?
  • LINQ to Entities does not recognize the method 'System.String ToString()' method, and this
  • Delphi. Analog of Memo/RichEdit
  • Angular5 Service Worker update(SWUpdate) not detecting on firefox. Working on chrome
  • Deploying pre-encrypted configuration files to a production environment
  • Instanciate service on startup in Angular2
  • get passwords from chrome
  • Double-click autofill - dynamic based on adjacent cell
  • quiver not drawing arrows just lots of blue, matlab
  • Specify the x-axis values in ggplot in R
  • Suppressing passwd when calling sqlplus from shell script
  • Groovy: Unexpected token “:”
  • print() is showing quotation marks in results
  • Content-Length header not returned from Pylons response
  • Play WS (2.2.1): post/put large request
  • Replace value with Factor in r data.table
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Splitting given String into two variables - php
  • NetLogo BehaviorSpace - Measure runs using reporters
  • C# - Is there a limit to the size of an httpWebRequest stream?
  • Is my CUDA kernel really runs on device or is being mistekenly executed by host in emulation?
  • Java applet as stand-alone Windows application?
  • vba code to select only visible cells in specific column except heading
  • Build own AppleScript numerical error handling
  • Jquery - Jquery Wysiwyg return html as a string
  • SQL merge duplicate rows and join values that are different
  • Acquiring multiple attributes from .xml file in c#
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • How can I remove ASP.NET Designer.cs files?
  • Reading document lines to the user (python)
  • Observable and ngFor in Angular 2
  • java string with new operator and a literal