A component of a project I am working on is to import text in cells from Excel using PHPExcel as a quicker way to populate the MySQL database. How can I achieve preserving the Rich Text formatting of the Cell Contents e.g. "The Quick Brown Fox".
I am guessing it would require parsing the content-format somehow into matching HTML tags? Is there a way to achieve this in PHPExcel?
Thanks!
As for your PHP you can use something like this:
$product_list = "";
$order = isset($_GET['sort'])?$_GET['sort']:'date_added';
$query = "SELECT * FROM products ORDER BY $order ";
$sql = mysql_query($query);
while($row = mysql_fetch_array($sql)){
$product_list .= "<tr>
<td>".$row['id']."</td>
<td>".$row['product_name']."</td>
<td>".$row['price']."</td>
<td>".$row['category']."</td>
<td>".$row['subcategory']."</td>
<td>".$row['date_added']."</td>
</tr>";
}
For your HTML make sure you include your the name of the page receiving the parameters:
<table class="product-list">
<tr>
<th><a href='page.php?sort=id'>ID</a></th>
<th><a href='page.php?sort=product_name'>Name</a></th>
<th><a href='page.php?sort=price'>Price</a></th>
<th><a href='page.php?sort=category'>Category</a></th>
<th><a href='page.php?sort=subcategory'>Subcategory</a></th>
<th><a href='page.php?sort=date_added'>Added</a></th>
</tr>
<?php echo stripslashes($product_list); ?>
</table>
Bonus:
You can do the sorting on the client side using this jquery plugin.
Alright so I have tweaked Magnus' code a bit and I do thank him a lot for helping me figure this out.
textarea.php This is where you will write your own content, format the text and throw it to your php file that in turn would insert it to the database. I added comments for those that wants to learn from this as well.
<style>
#fake_textarea {
width: 100%;
height: 200px;
border: 1px solid red;
}
<!-- Add css to modify the text -->
#jBold {
font-weigth: bold;
}
#jItalic{
font-style:italic;
}
#jUnderline{
text-decoration: underline;
}
#jLT{
text-decoration: line-through;
}
</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-3.4.1.min.js"></script>
<body>
<!-- Put buttons here to modify the format -->
<div>
<select id="select_font" onchange="changeFont(this);">
<option value="Arial">Arial</option>
<option value="Sans Serif" selected>Sans Serif</option>
<option value="Comic Sans MS">Comic Sans MS</option>
<option value="Times New Roman">Times New Roman</option>
<option value="Courier New">Courier New</option>
<option value="Verdana">Verdana</option>
<option value="Trebuchet MS">Trebuchet MS</option>
<option value="Arial Black">Arial Black</option>
<option value="Impact">Impact</option>
<option value="Bookman">Bookman</option>
<option value="Garamond">Garamond</option>
<option value="Palatino">Palatino</option>
<option value="Georgia">Georgia</option>
</select>
<select id="select-size" onchange="changeSize(this);">
<option value="4">4</option>
<option value="8">8</option>
<option value="12">12</option>
<option value="16">16</option>
<option value="20">20</option>
<option value="24">24</option>
<option value="28">28</option>
<option value="32">32</option>
<option value="36">36</option>
<option value="40">40</option>
<option value="44">44</option>
<option value="48">48</option>
<option value="52">52</option>
<option value="56">56</option>
<option value="58">58</option>
</select>
<button id="jBold"><b>B</b></button><button id="jItalic"><i>I</i></button><button id="jUnderline">U</button><button id="jSuperScript">A<sup>A</sup></button><button id="jSubScript">A<sub>A</sub></button>
<button id="jLT">A</button>
<div>
<!-- Add a form -->
<form method="post" action="postcontent.php" id="contentform">
<!-- Add some hidden input in order for the form to submit some sort of value -->
<input type="hidden" name="content" id="hiddeninput" />
<!-- Add a place to insert the content -->
<div id='fake_textarea' contenteditable>
Select some text and click the button to make it bold...
<br>Or write your own text
</div>
<!-- Add a submit button-->
<button id="submit">Submit</button>
</form>
<!-- Script to make a selected text bold-->
<script type="text/javascript">
$(document).ready(function() {
$('#jBold').click(function() {
document.execCommand('bold');
});
});
</script>
<!-- Script to make a selected text italic-->
<script type="text/javascript">
$(document).ready(function() {
$('#jItalic').click(function() {
document.execCommand('italic');
});
});
</script>
<!-- Script to make add an underline-->
<script type="text/javascript">
$(document).ready(function() {
$('#jUnderline').click(function() {
document.execCommand('underline');
});
});
</script>
<!-- Script to make make selected text a superscript-->
<script type="text/javascript">
$(document).ready(function() {
$('#jSuperScript').click(function() {
document.execCommand('superscript');
});
});
</script>
<!-- Script to make make selected text a subscript-->
<script type="text/javascript">
$(document).ready(function() {
$('#jSubScript').click(function() {
document.execCommand('subscript');
});
});
</script>
<!-- Script to add a line-through-->
<script type="text/javascript">
$(document).ready(function() {
$('#jLT').click(function() {
document.execCommand('strikeThrough');
});
});
</script>
<!-- Changes the font type -->
<script type="text/javascript">
function changeFont(font) {
var sel = window.getSelection(); // Gets selection
if (sel.rangeCount) {
// Creates a new element, and insert the selected text with the chosen font inside
var e = document.createElement('span');
e.style = 'font-family:' + font.value + ';';
e.innerHTML = sel.toString();
// https://developer.mozilla.org/en-US/docs/Web/API/Selection/getRangeAt
var range = sel.getRangeAt(0);
range.deleteContents(); // Deletes selected text…
range.insertNode(e); // … and inserts the new element at its place
}
}
</script>
<!-- Changes the font size -->
<script type="text/javascript">
function changeSize(size) {
var sel = window.getSelection(); // Gets selection
if (sel.rangeCount) {
// Creates a new element, and insert the selected text with the chosen font inside
var e = document.createElement('span');
e.style = 'font-size:' + size.value + 'px;';
e.innerHTML = sel.toString();
// https://developer.mozilla.org/en-US/docs/Web/API/Selection/getRangeAt
var range = sel.getRangeAt(0);
range.deleteContents(); // Deletes selected text…
range.insertNode(e); // … and inserts the new element at its place
}
}
</script>
<!-- Script to add value to the hidden input then submits it-->
<script type="text/javascript">
$( "#submit" ).click(function() {
var htmlString = $( "#fake_textarea" ).html();
$('#hiddeninput').val(htmlString);
// Submit the real form
$('#contentform').submit();
});
</script>
</body>
postcontent.php This file will submit the value thrown from the hidden input to the database.
<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
//grabs the name of the hidden input that was posted
$pcd= $_POST['content'];
$uid="";
$bid="";
$cnum="";
$cid="";
//connect to database
$mysqli = new mysqli("localhost","root","","nw");
//error checking the connection
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
//submits it
$stmt= $mysqli->prepare("INSERT INTO usercontent (userid, bookid, chapterid, chapternum,data) VALUES (?,?,?,?,?)");
$stmt->bind_param("sssss", $uid, $bid,$cid, $cnum,$pcd);
$stmt->execute();
$stmt -> close();
$mysqli -> close();
}
?>
Hopes this will help someone as much as this person helped me.
Your $result variable is empty, test it out.
The reason why is because you did not set up any database name where you can get information from.
Paste this:
mysqli_select_db($con, 'your_database_name');
after
$con=mysqli_connect("server.com","username","password");
and it should be okay (of course if you have any records there).
After more research I found the following. The functionality for building a rich-text-editor is already implemented at the browser. IE was the first to create such an API and Firefox replicated it.
The main point is that the javascript object "document" has a property called designMode which can be set to "on". This converts all the page to to a textarea-like component. Imagine that the browser opens the page the same way that MS-Word would: the user can see the formatting but he can also type in the page (normally the browser opens a page as readonly).
window.document.designMode = "On";
Because the above affects all the web page, most editors use iFrames so that the editable area is only the iFrame which has it's own document object.
On top of that, there is an API that allows easy javascript access to styling. This is exposed throw the execCommand() method. For example you can call from Javascript
document.execCommand('bold', false, '');
and the selected text will become bold.
I have found the following:
A brief step by step guide.
A mozilla guide. It has the most convenient API reference I have found and also some more links.
A guide by microsoft.
It's mentioned in the documentation.
$objRichText = new PHPExcel_RichText(); $objRichText->createText('This invoice is '); $objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month'); $objPayable->getFont()->setBold(true); $objPayable->getFont()->setItalic(true); $objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) ); $objRichText->createText(', unless specified otherwise on the invoice.'); $objPHPExcel->getActiveSheet()->getCell('A18')->setValue($objRichText);