How can I export tables to Excel from a webpage [closed]
Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.
How can I export tables to Excel from a webpage. I want the export to contain all the formatting and colours.
@user where are you declaring column sizes and such? I’m not deeply familiar with exporting data to Excel but you may need to declare them inline, i.e.
@user — there are at least two different issues here: 1) formatting the data so that it appears correctly in Excel, and 2) exporting the data using Javascript so that it sets the mime-type correctly, prompting the user to save the file. Are you trying to solve both of these problems?
14 Answers 14
Far and away, the cleanest, easiest export from tables to Excel is Jquery DataTables Table Tools plugin. You get a grid that sorts, filters, orders, and pages your data, and with just a few extra lines of code and two small files included, you get export to Excel, PDF, CSV, to clipboard and to the printer.
This is all the code that’s required:
$(document).ready( function () < $('#example').dataTable( < "sDom": 'Tlfrtip', "oTableTools": < "sSwfPath": "/swf/copy_cvs_xls_pdf.swf" >> ); > ); So, quick to deploy, no browser limitations, no server-side language required, and most of all very EASY to understand. It’s a win-win. The one thing it does have limits on, though, is strict formatting of columns.
If formatting and colors are absolute dealbreakers, the only 100% reliable, cross browser method I’ve found is to use a server-side language to process proper Excel files from your code. My solution of choice is PHPExcel It is the only one I’ve found so far that positively handles export with formatting to a MODERN version of Excel from any browser when you give it nothing but HTML. Let me clarify though, it’s definitely not as easy as the first solution, and also is a bit of a resource hog. However, on the plus side it also can output direct to PDF as well. And, once you get it configured, it just works, every time.
UPDATE — September 15, 2016: TableTools has been discontinued in favor of a new plugin called «buttons» These tools perform the same functions as the old TableTools extension, but are FAR easier to install and they make use of HTML5 downloads for modern browsers, with the capability to fallback to the original Flash download for browsers that don’t support the HTML5 standard. As you can see from the many comments since I posted this response in 2011, the main weakness of TableTools has been addressed. I still can’t recommend DataTables enough for handling large amounts of data simply, both for the developer and the user.
How to export html table to excel using javascript
Javascript is browser side language, you can convert an HTML document to PDF using a server side language. Also note that ActiveXObject solution are not portable, it only works on IE.
13 Answers 13
Check https://github.com/linways/table-to-excel. Its a wrapper for exceljs/exceljs to export html tables to xlsx.
TableToExcel.convert(document.getElementById("simpleTable1")); Sample Excel Italic and horizontal center in Arial Col 1 (number) Col 2 Wrapped Text Col 4 (date) Col 5 1 ABC1 Striked Text 05-20-2018 2210.00 2 ABC 2 Merged cell 05-21-2018 230.00 05-22-2018 2493.00 Hyperlink 4933.00 مرحبا 2009.00 All borders true false 1 0 Value Error All borders separately Excluded row Something Included Cell Excluded Cell Included Cell
This creates valid xlsx on the client side. Also supports some basic styling. Check https://codepen.io/rohithb/pen/YdjVbb for a working example.
@AtulKumar I have re-written everything on version 1.x. Now the usage is changed a little bit, since I changed the underlying library to exceljs. You can find a working example at: codepen.io/rohithb/pen/YdjVbb.
Only works in Mozilla, Chrome and Safari..
A B C 1 2 3 1 2 3 1 2 3 1 2 3
function write_headers_to_excel() < str=""; var myTableHead = document.getElementById('headers'); var rowCount = myTableHead.rows.length; var colCount = myTableHead.getElementsByTagName("tr")[0].getElementsByTagName("th").length; var ExcelApp = new ActiveXObject("Excel.Application"); var ExcelSheet = new ActiveXObject("Excel.Sheet"); ExcelSheet.Application.Visible = true; for(var i=0; i> > and then do the same thing for the tag.
EDIT: I would also highly recommend using jQuery. It would shorten this up to:
Now, of course, this is going to give you some formatting issues but you can work out how you want it formatted in Excel.
EDIT: To answer your question about how to do this for n number of tables, the jQuery will do this already. To do it in raw Javascript, grab all the tables and then alter the function to be able to pass in the table as a parameter. For instance:
var tables = document.getElementsByTagName('table'); for(var i = 0; i Then change the function write_headers_to_excel() to function write_headers_to_excel(table) . Then change var myTableHead = document.getElementById(‘headers’); to var myTableHead = table.getElementsByTagName(‘thead’)[0]; . Same with your write_bodies_to_excel() or however you want to set that up.
Thank you very much. It worked . I have one more question. How do i use the function if there are n tables in my html page.
All though it is a shame that this only works in IE, the OP was not about how to make his code work across multiple browsers. It was about why his current code wasn’t working with the table he had. Delving into the issue of making a javascript work in multiple browsers (which is the purpose of JS to begin with) I felt was outside the scope of this question. Personally, I never use IE unless forced to by my company’s intranet.
@prashu132 ‘Tis very true that this only works in IE. (And a shame as my previous comment mentioned.) If you are looking for a more cross browser solution, I would suggest pushing the data to the server side and doing the generation there before pushing it back to the client. It also appears there is a solution using a jQuery plugin located in this SO question
@CharlesCaldwell : i found another answer which might be a better one. Please try it out and vote it up if good and useful. Thank you.
You never use this var ExcelApp = new ActiveXObject(«Excel.Application»); in both javascript or jquery . I could not get this to work in IE 10 either
Excel Export Script works on IE7+ , Firefox and Chrome =========================================================== function fnExcelReport() < var tab_text=""; var textRange; var j=0; tab = document.getElementById('headerTable'); // id of table for(j = 0 ; j < tab.rows.length ; j++) < tab_text=tab_text+tab.rows[j].innerHTML+" "; //tab_text=tab_text+""; > tab_text=tab_text+"
"; tab_text= tab_text.replace(/]*>|/g, "");//remove if u want links in your table tab_text= tab_text.replace(/]*>/gi,""); // remove if u want images in your table tab_text= tab_text.replace(/]*>|/gi, ""); // reomves input params var ua = window.navigator.userAgent; var msie = ua.indexOf("MSIE "); if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) // If Internet Explorer < txtArea1.document.open("txt/html","replace"); txtArea1.document.write(tab_text); txtArea1.document.close(); txtArea1.focus(); sa=txtArea1.document.execCommand("SaveAs",true,"Say Thanks to Sumit.xls"); >else //other browser not tested on IE 11 sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text)); return (sa); > Just Create a blank iframe enter code here Call this function on @sampopes Thanks for your useful solution. I have a question based on your answer and I would like to seek your advice please. I have a table, the pagesize is 10 because I have to show 10 rows/records for each page. The table contains 18 rows/records, so the table will have 2 pages, in the button if I click the button to export to excel, it can show row/records in page 1. However in page 2, if I click the button to export excel, it shows rows/records in page 1.
@sampopes (cont) At this moment, I guess maybe the browser (Internet Explorer) remembers the content in page 1, so I refresh the browser, click the page 2 of the table and click the button to export the excel, howerver it (the excel) still shows rows/records in page 1. What I have tried: I add another iframe txtArea2 and add code for txtArea2 inside the function. I run the program, the result is the same. Do you have idea about how to export table data based on table pages? Thank you very much.
This might be a better answer copied from this question.
function generate_excel(tableid) < var table= document.getElementById(tableid); var html = table.outerHTML; window.open('data:application/vnd.ms-excel;base64,' + base64_encode(html)); >function base64_encode (data) < // http://kevin.vanzonneveld.net // + original by: Tyler Akins (http://rumkin.com) // + improved by: Bayron Guevara // + improved by: Thunder.m // + improved by: Kevin van Zonneveld (http://kevin.vanzonneveld.net) // + bugfixed by: Pellentesque Malesuada // + improved by: Kevin van Zonneveld (http://kevin.vanzonneveld.net) // + improved by: Rafal Kukawski (http://kukawski.pl) // * example 1: base64_encode('Kevin van Zonneveld'); // * returns 1: 'S2V2aW4gdmFuIFpvbm5ldmVsZA==' // mozilla has this native // - but breaks in 2.0.0.12! //if (typeof this.window['btoa'] == 'function') < // return btoa(data); //>var b64 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/="; var o1, o2, o3, h1, h2, h3, h4, bits, i = 0, ac = 0, enc = "", tmp_arr = []; if (!data) < return data; >do < // pack three octets into four hexets o1 = data.charCodeAt(i++); o2 = data.charCodeAt(i++); o3 = data.charCodeAt(i++); bits = o1 > 18 & 0x3f; h2 = bits >> 12 & 0x3f; h3 = bits >> 6 & 0x3f; h4 = bits & 0x3f; // use hexets to index into b64, and append result to encoded string tmp_arr[ac++] = b64.charAt(h1) + b64.charAt(h2) + b64.charAt(h3) + b64.charAt(h4); > while (i function XLExport() < try < var i; var j; var mycell; var tableID = "tblInnerHTML"; var objXL = new ActiveXObject("Excel.Application"); var objWB = objXL.Workbooks.Add(); var objWS = objWB.ActiveSheet; for (i = 0; i < document.getElementById('').rows.length; i++) < for (j = 0; j < document.getElementById('').rows(i).cells.length; j++) < mycell = document.getElementById('').rows(i).cells(j); objWS.Cells(i + 1, j + 1).Value = mycell.innerText; > > //objWS.Range("A1", "L1").Font.Bold = true; objWS.Range("A1", "Z1").EntireColumn.AutoFit(); //objWS.Range("C1", "C1").ColumnWidth = 50; objXL.Visible = true; > catch (err) < >> Check this out. I just got this working and it seems exactly what you are trying to do as well.
2 functions. One to select the table and copy it to the clipboard, and the second writes it to excel en masse. Just call write_to_excel() and put in your table id (or modify it to take it as an argument).
function selectElementContents(el) < var body = document.body, range, sel; if (document.createRange && window.getSelection) < range = document.createRange(); sel = window.getSelection(); sel.removeAllRanges(); try < range.selectNodeContents(el); sel.addRange(range); >catch (e) < range.selectNode(el); sel.addRange(range); >> else if (body.createTextRange) < range = body.createTextRange(); range.moveToElementText(el); range.select(); >range.execCommand("Copy"); > function write_to_excel() < var tableID = "AllItems"; selectElementContents( document.getElementById(tableID) ); var excel = new ActiveXObject("Excel.Application"); // excel.Application.Visible = true; var wb=excel.WorkBooks.Add(); var ws=wb.Sheets("Sheet1"); ws.Cells(1,1).Select; ws.Paste; ws.DrawingObjects.Delete; ws.Range("A1").Select excel.Application.Visible = true; > I think you can also think of alternative architectures. Sometimes something can be done in another way much more easier. If the producer of HTML file is you, then you can write an HTTP handler to create an Excel document on the server (which is much more easier than in JavaScript) and send a file to the client. If you receive that HTML file from somewhere (like an HTML version of a report), then you still can use a server side language like C# or PHP to create the Excel file still very easily. I mean, you may have other ways too. 🙂
I would suggest using a different approach. Add a button on the webpage that will copy the content of the table to the clipboard, with TAB chars between columns and newlines between rows. This way the «paste» function in Excel should work correctly and your web application will also work with many browsers and on many operating systems (linux, mac, mobile) and users will be able to use the data also with other spreadsheets or word processing programs.
The only tricky part is to copy to the clipboard because many browsers are security obsessed on this. A solution is to prepare the data already selected in a textarea, and show it to the user in a modal dialog box where you tell the user to copy the text (some will need to type Ctrl-C, others Command-c, others will use a «long touch» or a popup menu).
It would be nicer to have a standard copy-to-clipboard function that possibly requests a user confirmation. but this is not the case, unfortunately.
I try this with jquery; use this and have fun 😀
jQuery.printInExcel = function (DivID) < var ExcelApp = new ActiveXObject("Excel.Application"); ExcelApp.Workbooks.Add; ExcelApp.visible = true; var str = ""; var tblcount = 0; var trcount = 0; $("#" + DivID + " table").each(function () < $(this).find("tr").each(function () < var tdcount = 0; $(this).find("td").each(function () < str = str + $(this).text(); ExcelApp.Cells(trcount + 1, tdcount + 1).Value = str; str = ""; tdcount++ >); trcount++ >); tblcount++ >); >; use this in your class and call it with $.printInExcel(your var);
export html form data to excel
I have googled around and have been unable to crack this nut. I have a form setup to let my site visitors subscribe to my newsletter. It just sends an email to my email, and I add them to my contacts and just then forward them the newsletter. What I need is to be able to have their contact info placed in an Excel spreadsheet for ease of access to my list of subscribers. Can this be done with just html ? ( I don’t know any js or whatever else could be required)
@Diodeus I prefer to learn how to hand code stuff, and not rely on templates and widgets and whatnot.
Ok, then you will have to learn a server-side language, such as PHP since this cannot be done on the client alone using HTML.
2 Answers 2
One method would be to create a php (or your fav scripting language) script that takes an input and appends it to a csv file. Then have the html form post to said script. You could even setup a cronjob that sends you an update copy of your contacts periodically.
Actually — there IS a way to get the data from your HTML form exported into an Excel spreadsheet.
In your HTML form you’ll have to create a separate table (it can be hidden).
And the BIG SECRET IS: In the put id tags and then use a JavaScript to replace the innerHTML with the value of the fields on your form which collect the data you want.
.divCenMid .allbdrCenMid .allbdrCenTop .allbdrLtMid .allbdrLtTop QMS Assessment No Criteria Question Score Q1 Quality Unit Independency Do you have the Quality Unit? Q2 Apply PICS GMP Which GMP regulation do you use? Q3 Deviation or Non-conformance Do you have a deviation or non-conformance procedure? Q4 Complaint Do you have a customer complaint procedure?
If you can't figure out the details of how to apply it contact me.