#HOW TO INSERT UNDERLINE IN EXCEL CODE#

Note that in order to get the line break visible in the cell, you need to make sure that ‘Wrap Text’ is enabled. Or the old CONCATENATE formula in case you’re using older versions of Excel and don’t have CONCAT =CONCATENATE(A2,CHAR(10),B2,CHAR(10),C2)Īnd in case you are using Excel 2016 or prior versions, you can use the below TEXTJOIN formula (which is a better way to join cells/ranges) =TEXTJOIN(CHAR(10),TRUE,A2:C2) You can also use the CONCAT formula instead of using the ampersand (&) symbol: =CONCAT(A2,CHAR(10),B2,CHAR(10),C2) By placing the line feed where you want the line break, we are forcing the formula to break the line in the formula result.

#HOW TO INSERT UNDERLINE IN EXCEL CODE#

CHAR(10) uses the ASCII code which returns a line feed. The above formula uses CHAR(10) to add the line break as a part of the result. This can be useful when you have different cells that you want to combine and add a line break so that each part is in a different line.īelow is an example where I have used a formula to combined different parts of an address and have added a line break in each part.īelow is the formula that adds a line break within the formula result: =A2&CHAR(10)&B2&(CHAR(10)&C2) You can add a line break as a part of the formula result. But if you have to do this a lot of cells, you can use the other methods covered later in this tutorial. The keyboard shortcut is a quick way to add a line break if you only have to do this for a few cells. ‘Wrap Text’ option is available in the Home tab in the ribbon. In case it’s is not applied, you will see all the text in a single cell (even if you have the line break). Finish off the spreadsheet generation by using the code below and then open up your freshly generated spreadsheet.Note that you need the cell to be in the ‘Wrap text’ mode to see the content appear in the next line. Then we simply use the useful applyFromArray function to apply this style to our selected cell.Īnd that is pretty much it. So from above you can see that we first create a style array that holds our underline information. $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray) 'underline' => PHPExcel_Style_Font::UNDERLINE_SINGLE Okay, so let’s apply a normal underlining to that text we just inserted: $styleArray = array( $objPHPExcel->getActiveSheet()->setCellValue('A1','Underline me!') $objPHPExcel->getActiveSheet()->setTitle('Example') Then we populate a cell with some data so that we’ve got something to underline: $objPHPExcel->setActiveSheetIndex(0)

Then we set the active sheet to the default first sheet in the workbook and give it a title, in this case ‘Example’. Require_once 'Classes/PHPExcel/IOFactory.php' Header('Content-Disposition: attachment filename=example.xls')

Looking at this list, we can see that we pretty much have all the usual underline options that Excel offers us, so let’s go ahead and put this knowledge to action!įirst we initiate our PHPExcel object in the following fashion: header('Content-type: application/vnd.ms-excel') Opening this file we find: const UNDERLINE_NONE = 'none' Ĭonst UNDERLINE_DOUBLEACCOUNTING = 'doubleAccounting' Ĭonst UNDERLINE_SINGLEACCOUNTING = 'singleAccounting'

The easiest way to see this is by checking up which PHPExcel constants have been set up for the underline type and to do this we browse to the Font.php file located in the ClassesPHPExcelStyle folder of your PHPExcel installation/folder structure. Today’s little hint is on how one can go about underlining the value of a cell (for example, if you want to use that text as a link in your worksheet).Īt this point you might of course like to know which underline options you do have available to you. The CodePlex project PHPExcel is an absolutely brilliant implementation around Microsoft’s OpenXML standard, giving us a powerful PHP object driven engine that is capable of creating and reading from Excel 2007, PDF, HTML and even Excel 2003 spreadsheets and documents.

How to insert underline in excel