Js выгрузка в excel
Всем доброго!
есть маленький пример, но как заставить чтоб в документе эксель появился и заголовок таблицы?
Экспорт Excel в HTML
Всем привет. Задача: Нужно файлы .xls находящиеся в папке сохранить как html (Книгу со всеми.
Экспорт таблицы из Excel в HTML
Здравствуйте. На просторах интернета нашёл файл-помощник для переноса прайса из таблицы Excel в.
Экспорт HTML table в Excel
Здравствуйте. подскажите пожалуйста примером как можно экспортировать таблицу в эксель? Много чего.
Внести этот заголовок в таблицу, он у вас сейчас от нее отдельно, соответственно и не экспортируется.
Внести этот заголовок в таблицу, он у вас сейчас от нее отдельно, соответственно и не экспортируется.
был сделан вот такой вариант, и появится другой вопрос а что если там две и более таблицы?
Добавлено через 13 часов 8 минут
никто не пробовал реализовать такую?
Внести этот заголовок в таблицу, он у вас сейчас от нее отдельно, соответственно и не экспортируется.
asus, *.xls это Excel только старый формат 93г, в новых версиях читается без проблем.Проблемы не вижу.
asus, *.xls это Excel только старый формат 93г, в новых версиях читается без проблем.Проблемы не вижу.
это да, но вы по ссылке пройдите и скачайте потом откройте с помощью блокнота, заодно создайте новый документ в экселе и тоже откройте в блокноте, и увидите о чем я говорю.
Вижу, ну да и фиг с ним. Excel его нормально читает, редактировать можно, пересохранить в любой другой формат так же можно. Что конкретно в это варианте вас не устраивает мне не ясно.
Если это так принципиально то юзайте библиотеки вроде этой.
Вижу, ну да и фиг с ним. Excel его нормально читает, редактировать можно, пересохранить в любой другой формат так же можно. Что конкретно в это варианте вас не устраивает мне не ясно.
Экспорт нескольких Excel файлов в один Excel файл
Здравствуйте! Помогите пожалуйста с реализацией макроса (осилил только загрузку с одного.
Экспорт данных из таблицы Excel в шаблон Excel
Добрый день! Проблемка заключается в следующем: В папке debug во вложении есть файл.
Хитроумный экспорт Excel->Excel через кнопку
В общем, встал вопрос автоматизации создания документа. Есть большая табличка с кучей полей в.
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.
12 Answers 12
@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..
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:
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
@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.
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).
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 :D
use this in your class and call it with $.printInExcel(your var);
clarketm / TableExport Public archive
The simple, easy-to-implement library to export HTML tables to xlsx, xls, csv, and txt files.
License
This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.
Latest commit
Git stats
Files
Failed to load latest commit information.
README.md
Install manually using tags
To use this library, include the FileSaver.js library, and TableExport library before the closing tag of your HTML document:
Install with Bower
Install with npm
uncompressed | compressed | |
---|---|---|
CSS | 🔗 | 🔗 |
JS | 🔗 | 🔗 |
Images | — | 🔗 xlsx 🔗 xls 🔗 csv 🔗 txt |
In order to provide Office Open XML SpreadsheetML Format ( .xlsx ) support, you must include the following third-party library in your project before both FileSaver.js and TableExport.
Including xlsx.core.js is NOT necessary if installing with Bower or npm
Including Blob.js is NOT necessary if installing with Bower or npm
To use this library, simple call the TableExport constructor:
Additional properties can be passed-in to customize the look and feel of your tables, buttons, and exported data.
Notice that by default, TableExport will create export buttons for three different filetypes xls , csv , txt . You can choose which buttons to generate by setting the formats property to the filetype(s) of your choice.
Note: to use the xlsx filetype, you must include js-xlsx; reference the Add-Ons section.
TableExport supports additional methods (getExportData, update, reset and remove) to control the TableExport instance after creation.
Table to Excel 2
Export HTML table to valid excel file effortlessly. This library uses exceljs/exceljs under the hood to create the excel.
(Initial version of this library was using protobi/js-xlsx, it can be found here)
Just add a script tag:
Check this pen for working example.
Cell types can be set using the following data attributes:
Attribute | Description | Possible Values |
---|---|---|
data-t | To specify the data type of a cell | s : String (Default) n : Number b : Boolean d : Date |
data-hyperlink | To add hyper link to cell | External URL or hyperlink to another sheet |
data-error | To add value of a cell as error |
All styles are set using data attributes on td tags. There are 5 types of attributes: data-f-* , data-a-* , data-b-* , data-fill-* and data-num-fmt which corresponds to five top-level attributes font , alignment , border , fill and numFmt .
Category | Attribute | Description | Values |
---|---|---|---|
font | data-f-name | Font name | "Calibri" ,"Arial" etc. |
data-f-sz | Font size | "11" // font size in points | |
data-f-color | Font color | A hex ARGB value. Eg: FFFFOOOO for opaque red. | |
data-f-bold | Bold | true or false | |
data-f-italic | Italic | true or false | |
data-underline | Underline | true or false | |
data-f-strike | Strike | true or false | |
Alignment | data-a-h | Horizontal alignment | left , center , right , fill , justify , centerContinuous , distributed |
data-a-v | Vertical alignment | bottom , middle , top , distributed , justify | |
data-a-wrap | Wrap text | true or false | |
data-a-indent | Indent | Integer | |
data-a-rtl | Text direction: Right to Left | true or false | |
data-a-text-rotation | Text rotation | 0 to 90 | |
-1 to -90 | |||
vertical | |||
Border | data-b-a-s | Border style (all borders) | Refer BORDER_STYLES |
data-b-t-s | Border top style | Refer BORDER_STYLES | |
data-b-b-s | Border bottom style | Refer BORDER_STYLES | |
data-b-l-s | Border left style | Refer BORDER_STYLES | |
data-b-r-s | Border right style | Refer BORDER_STYLES | |
data-b-a-c | Border color (all borders) | A hex ARGB value. Eg: FFFFOOOO for opaque red. | |
data-b-t-c | Border top color | A hex ARGB value. | |
data-b-b-c | Border bottom color | A hex ARGB value. | |
data-b-l-c | Border left color | A hex ARGB value. | |
data-b-r-c | Border right color | A hex ARGB value. | |
Fill | data-fill-color | Cell background color | A hex ARGB value. |
numFmt | data-num-fmt | Number Format | "0" |
"0.00%" | |||
"0.0%" // string specifying a custom format | |||
"0.00%;\(0.00%\);\-;@" // string specifying a custom format, escaping special characters |
BORDER_STYLES: thin , dotted , dashDot , hair , dashDotDot , slantDashDot , mediumDashed , mediumDashDotDot , mediumDashDot , medium , double , thick
Exclude Cells and rows
To exclude a cell or a row from the exported excel add data-exclude="true" to the corresponding td or tr .
Example:
JavaScript is a versatile platform that allows easy customization of client-side scripting tools. In some applications, it's useful to have some sort of spreadsheet interface that is easy to code and maintain. The SpreadJS client-side JavaScript spreadsheet component, part of the SpreadJS package, is perfect for this.
In this blog, we will cover how to import/export to Excel in JavaScript following these steps:
Set Up the JavaScript Spreadsheet Project
To start off we can use the SpreadJS files hosted on NPM. To do this, we can install using command line argument. Open up a command prompt and navigate to the location of your application. There, you can install the required files with one command.
In this case, we need the base Spread-Sheets library, Spread-ExcelIO, and jQuery:
npm i @grapecity/spread-sheets @grapecity/spread-excelio jquery
Once those are installed, we can add references to those script and css files in our code:
Then add a script to the page that initializes the Spread.Sheets component, and a div element to contain it (since the SpreadJS spreadsheet component utilizes a canvas, this is necessary to initialize the component):
Add Excel Import Code
We need to create an instance of the client-side ExcelIO component that we can use to actually open the file:
Then we need to add a function to import a file. In this example, we import a local file, but you can do the same thing with a file on a server. If you’re importing a file from a server, you need to reference the location. The following is an example of an input element where the user can enter the location of the file:
Once you have that, you can directly access that value in script code:
The following code for the import function just uses a local file for the "excelUrl" variable:
Regardless of whether you're referencing a file on a server or locally, you'll need to add the following to your script inside the $(document).ready function:
Add Data to the Imported Excel File
In this tutorial, we import a local file that uses the “Profit loss statement” Excel template.
Now we can use Spread.Sheets script to add another revenue line into this file. Let’s add a button to the page that will do just that:
We can write a function for the click event handler for that button to add a row and copy the style from the previous row in preparation for adding some data. To copy the style, we will need to use the copyTo function and pass in:
- the origin and destination row and column indices
- row and column count
- the CopyToOptions value for style
All of the following script code for adding data and a Sparkline will be contained within this button click event handler. For most of the data, we can use the setValue function. This allows us to set a value in a sheet in Spread by passing in a row index, column index, and value:
Set a SUM formula in column P to match the other rows and set a percentage for column Q:
Lastly, we can copy the formulas from the previous rows to the new row for columns R through AD using the copyTo function again, this time using CopyToOptions.formula:
Add a Sparkline
Now we can add a sparkline to match the other rows of data. To do this, we need to provide a range of cells to get the data from and some settings for the sparkline. In this case, we can specify:
- the range of cells we just added data to
- settings to make the sparkline look like the other sparklines in the same column
After that, we call the setSparkline method and specify:
- a location for the sparkline
- the location of the data
- the orientation of the sparkline
- the type of sparkline
- the settings we created
If you were to try running the code now, it might seem a little slow because the workbook is repainting every time data is changed and styles are added. To drastically speed it up and increase performance, Spread.Sheets provides the ability to suspend painting and the calculation service. Let’s add the code to suspend both before adding a row and its data, and then resume both after:
Once we add that code, we can open the page in a web browser and see the Excel file load into Spread.Sheets with an added revenue row. Important: Keep in mind that Chrome doesn’t allow you to open local files for security purposes, so you need to use a web browser like Firefox to successfully run this code. Alternatively, if you load a file from a website URL, it should open fine in any browser.
Add Excel Export Code
Finally, we can add a button to export the file with the added row. To do this, we can use the client-side ExcelIO code built into Spread.Sheets:
That code gets the export file name from an exportFileName input element. We can define it and let users name the file like so:
Then we can add a button that calls this function:
Once you add a revenue row, you can export the file using the Export File button. Make sure to add the FileSaver external library to allow users to save the file where they want:
When the file is successfully exported, you can open it in Excel and see that the file looks like it did when it was imported, except there is now an extra revenue line that we added.
This is just one example of how you can use SpreadJS JavaScript spreadsheets to add data to your Excel files and then export them back to Excel with simple JavaScript code.
Download the sample
In another article series, we demonstrate how to import/export Excel spreadsheets in other Javascript frameworks:
Читайте также: