Hello. I looked for any postings regarding Header Freezing and saw one that was a few months old. I tried everything that was suggested, but still my table headers won't freeze. Please advise. Thank you.

This is an awesome client-side library. Much more simpler than js-xlsx. However, could you please give some sample code for creating multiple worksheet and also how to define formula on the cell?

Jose, in your example code, I have found the feature for adding more than one worksheet to the file. I am still looking for Cell formula. Thanks again.

Hi,

Excellent plug-in which has proven to be incredibly helpful for achieving a great deal of efficiency gain for a project I'm working on.

Generate works great and I can open the file locally but I can't seem to send the file to a SharePoint location; when I've tried using the excel object, it saves the file but returns a corrupted document with 0kb size.

I've tried playing with the code to force it to export as a blob but having no luck... Any ideas?

I'm using a POST REST operation which works fine with JSPDF and it's pdf.output('blob') function:

function addFileToDocumentLibrary(siteURL, folder, file, fileName, successCallback, failureCallback){
    var url = siteURL + '/_api/web/GetFolderByServerRelativeUrl(\'' + folder + '\')/files/add(url=\'' + fileName + '\', overwrite=true)';

    $.ajax({
        url: url,
        type: 'POST',
        data: file,
        processData: false,
        headers: {
            'accept': 'application/json; odata=verbose',
            'X-RequestDigest': $('#__REQUESTDIGEST').val()
            //'content-length': arrayBuffer.byteLength
        },
        success: function(data){
            successCallback(data)
        },
        error: function(xhr, ajaxOptions, thrownError){
            failureCallback(xhr, ajaxOptions, thrownError)
        }
    });
}

It would be great if you could also select text wrapping in the formatting for a cell.

A possiblity would be to extend the align option to three characters - Horizontal, Vertical, Wrap
excel.set(0,2,undefined, 80, excel.addStyle( {align:"L T W"}));
(since it is in the tag in the excel xml)

This can be achieved by updating the "align" array to add "wrapText"

var align = {
        L: "left", C: "center", R: "right", T: "top", B: "bottom", W: "wrapText"
}

Updating toStyleXML(style) to begin as

function toStyleXml(style) {
        var alignXml = "";
        if (style.align) {
            var h = align[style.align.charAt(0)];
            var v = align[style.align.charAt(1)];
	    var w = align[style.align.charAt(2)];
            if (h || v || w) {
                alignXml = "<alignment ";
                if (h) alignXml = alignXml + ' horizontal="' + h + '" ';
                if (v) alignXml = alignXml + ' vertical="' + v + '" ';
				if (w) alignXml = alignXml + ' ' + w + '="1" ';
                alignXml = alignXml + " />";
            }
        }
.
.
.

And then update the normalizeAlign to

function normalizeAlign(a) {
        if (!a) return "---";
        var a = replaceAllMultiple(a.toString() + " - - -", "  ", " ").trim().toUpperCase().split(" ");
        return a[0].charAt(0) + a[1].charAt(0)  + a[2].charAt(0);
    }

Oh, it is an awesome library - really impressive.

Hello,

I am exporting an excel file as a blank template with only one header "Username" on column A.

The problem is that upon opening on Excel, it prompts a dialog box and I dont have idea what causing the issue:

Try to replicate this on this environment:
Windows 7 Enterprise
Excel version: 14

I am using the myexcel library on our Angular application.

Thanks for the help!

excel.set(0, j, a, e.name);
excel.set(0, j, a, e.bank_name);
excel.set(0, j, a, e.account_no);
excel.set(0, j, a, e.bank_name);

on excel.generate it removes leading 0 from account number starting with zero

can we add the Dropdown in the excel file

Hello Jose
I want to use your library but for I need license text in myexcel.js file to support that code is free to use.
Are you able to add license text at the top of the myexcel.js file?, that will comply the requirement in my company to use the library.
Thanks in advance.

lets say i have an array of 5 values, but when iterate each row i end up only with 4 rows leave alone the headers, meant the data rows are just 4 . am i doing anything wrong here? and also on pc i can save file as xlsl which opens well but on mobile its saved as .xlsx.xls which warning pops up due to extension. let me copy the code
<button class="btn btn-success mb-1" @click.prevent="ExportExcel(Projects, Headers)">Export <i class="fas fa-plus fw"></i> </button>
`<script/>
methods:(){
ToLocaleDateString(value) {
var date = new Date(value);
var customdate=date.toLocaleDateString('en-GB', {day: 'numeric', month: 'numeric', year: 'numeric'}).replace(/ /g, '/');
return customdate;
},
ToLocaleDateString2(value) {
var date = new Date(value);
var customdate=date.toLocaleDateString('en-GB', {day: 'numeric', month: 'numeric', year: 'numeric'});
return customdate;
},
sortedArray(projects){
return projects.sort((a, b) => a.id - b.id );
},
phoneformat(phone){
var prefix = phone.substring(0,4);
var suffix = phone.substring(4);
return prefix +' '+ suffix
},
// ExportExcel(projects, headers) {
ExportExcel(projects, headers) {
var excel = $JExcel.new("Calibri light 10 #333333"); // Default font
// excel.set is the main function to generate content:
// We can use parameter notation excel.set(sheetValue,columnValue,rowValue,cellValue,styleValue)
// Or object notation excel.set({sheet:sheetValue,column:columnValue,row:rowValue,value:cellValue,style:styleValue })
// null or 0 are used as default values for undefined entries
excel.set( {sheet:0,value:"This is Sheet 1" } );
excel.addSheet("Sheet 2");
excel.set(0,8,1,15);
excel.set(0,8,2,13);
excel.set(0,7,3,"15+13");
excel.set(0,8,3,"=I2+I3");
var evenRow=excel.addStyle (
{
border: "none,none,none,thin #333333"
}); // Borders are LEFT,RIGHT,TOP,BOTTOM. Check $JExcel.borderStyles for a list of valid border styles
var oddRow=excel.addStyle ( { // Style for odd ROWS
fill: "#ECECEC" , // Background color, plain #RRGGBB, there is a helper $JExcel.rgbToHex(r,g,b)
border: "none,none,none,thin #333333"
});

	          	for (var i=1;i<projects.length;i++) excel.set({row:i,style: i%2==0 ? evenRow: oddRow  });					// Set style for the first 50 rows
	          	// excel.set({row:3,value: 30  });																	// We want ROW 3 to be EXTRA TALL

	          	var headers= headers;							// This array holds the HEADERS text
	          	var formatHeader=excel.addStyle ( { 															// Format for headers
	          			border: "none,none,none,thin #333333", 													// 		Border for header
	          			font: "Calibri 12 #0000AA B"}); 														// 		Font for headers
	          	for (var i=0;i<headers.length;i++){																// Loop all the haders
	          		excel.set(0,i,0,headers[i],formatHeader);													// Set CELL with header text, using header format
	          		excel.set(0,i,undefined,"auto");															// Set COLUMN width to auto (according to the standard this is only valid for numeric columns)
	          	}
	          	// Now let's write some data
	          	var today = (new Date());
	          	var initDate = new Date(2000, 0, 1);
	          	var endDate = new Date(2016, 0, 1);
	          	var dateStyle = excel.addStyle ( { 																// Format for date cells
	          			align: "R",																				// 		aligned to the RIGHT
	          			format: "yyyy.mm.dd hh:mm:ss", 															// 		using DATE mask, Check $JExcel.formats for built-in formats or provide your own
                font: "#00AA00"});
                var sortedprojects = this.sortedArray(projects)
	          	for (var i = 1; i <projects.length; i++){
              var idx             = 	projects[i].id;
              var client          = 	projects[i].owner?            projects[i].owner.name:'Not Set';
              var email           = 	projects[i].owner?            projects[i].owner.email_address:'Not Set';
              var phone           = 	projects[i].owner?            this.phoneformat(projects[i].owner.phone_number):'Not Set';
              var name            = 	projects[i].name?             projects[i].name:'Not Set';
              var status          = 	projects[i].status?           projects[i].status:'Not Set';
              var amount          = 	projects[i].amount?           projects[i].amount: 0;
              var managementfee   = 	projects[i].management_fee?   projects[i].management_fee: 0;
              var completion      = 	projects[i].completion?       projects[i].completion: 0;
              var proposedstart   = 	projects[i].proposed_start?   this.ToLocaleDateString(projects[i].proposed_start) : 'Not Set';
              var proposedend     = 	projects[i].proposed_end?     this.ToLocaleDateString(projects[i].proposed_end) : 'Not Set';
              var actualstart     = 	projects[i].actual_start?     this.ToLocaleDateString(projects[i].actual_start) : 'Not Set';
              var actualend       = 	projects[i].actual_end?       this.ToLocaleDateString(projects[i].actual_end) : 'Not Set';
              var resumedate      = 	projects[i].resume_date?      this.ToLocaleDateString(projects[i].resume_date) : 'Not Set';
              var terminationdate = 	projects[i].termination_date? this.ToLocaleDateString(projects[i].termination_date) : 'Not Set';
              var onholddate      = 	projects[i].on_hold_date?     this.ToLocaleDateString(projects[i].on_hold_date) : 'Not Set';
              var landmark        = 	projects[i].nearest_landmark? projects[i].nearest_landmark:'Not Set';
              var county          = 	projects[i].ward?             projects[i].ward.constituency.county.name:'Not Set';
              var subcounty       =	  projects[i].ward?             projects[i].ward.constituency.name:'Not Set';
              var ward 	          =   projects[i].ward?             projects[i].ward.name:'Not Set';
              //data entry
	          		excel.set(0,0,i, i);
	          		excel.set(0,1,i, client);
	          		excel.set(0,2,i, email);
	          		excel.set(0,3,i, phone);
	          		excel.set(0,4,i, name);
	          		excel.set(0,5,i, status);
	          		excel.set(0,6,i, 'Kshs.' +amount);
	          		excel.set(0,7,i, managementfee +''+'%');
	          		excel.set(0,8,i, completion +''+'%');
	          		excel.set(0,9,i, proposedstart);
	          		excel.set(0,10,i, proposedend);
	          		excel.set(0,11,i, actualstart);
	          		excel.set(0,12,i, actualend);
	          		excel.set(0,13,i, resumedate);
	          		excel.set(0,14,i, terminationdate);
	          		excel.set(0,15,i, onholddate);
	          		excel.set(0,16,i, county);
	          		excel.set(0,17,i, subcounty);
	          		excel.set(0,18,i, ward);
              }
	          	// const mergedCellStyle = excel.addStyle( {align:"C", font: "Calibri 12 #0000AA B"});
	          	// excel.set(0, 0, 50, "This cells are merged!", mergedCellStyle, 5);								// This cells are merged using colspan
	          	excel.set(0,0,undefined,3, excel.addStyle( {align:"L"}));																	// Set COLUMN 1 to 30 chars width
	          	excel.set(0,1,undefined,20, excel.addStyle( {align:"L"}));																	// Set COLUMN 1 to 30 chars width
	          	excel.set(0,2,undefined,30, excel.addStyle( {align:"L"}));																	// Set COLUMN 1 to 30 chars width
	          	excel.set(0,3,undefined,20, excel.addStyle( {align:"L"}));																	// Set COLUMN 1 to 30 chars width
            excel.set(0,4,undefined,30, excel.addStyle( {align:"L"}));
            excel.set(0,5,undefined,20, excel.addStyle( {align:"L"}));
	            excel.set(0,6,undefined,20, excel.addStyle( {align:"L"}));
	            excel.set(0,7,undefined,20, excel.addStyle( {align:"L"}));
	            excel.set(0,8,undefined,30, excel.addStyle( {align:"L"}));
	            excel.set(0,9,undefined,30, excel.addStyle( {align:"L"}));
	            excel.set(0,10,undefined,30, excel.addStyle( {align:"L"}));
	            excel.set(0,11,undefined,30, excel.addStyle( {align:"L"}));
	            excel.set(0,12,undefined,30, excel.addStyle( {align:"L"}));
	            excel.set(0,13,undefined,30, excel.addStyle( {align:"L"}));
	            excel.set(0,14,undefined,30, excel.addStyle( {align:"L"}));
	            excel.set(0,15,undefined,30, excel.addStyle( {align:"L"}));
	            excel.set(0,16,undefined,30, excel.addStyle( {align:"L"}));
	            excel.set(0,17,undefined,30, excel.addStyle( {align:"L"}));																	// Set COLUMN 1 to 30 chars width
	          	// excel.set(0,3,undefined,30);																	// Set COLUMN 3 to 20 chars width
	          	// excel.set(0,4,undefined,20, excel.addStyle( {align:"R"}));										// Align column 4 to the right
	          	// excel.set(0,1,3,undefined,excel.addStyle( {align:"L T"}));										// Align cell 1-3  to LEFT-TOP
	          	// excel.set(0,2,3,undefined,excel.addStyle( {align:"C C"}));										// Align cell 2-3  to CENTER-CENTER
	          	// excel.set(0,3,3,undefined,excel.addStyle( {align:"R B"}));										// Align cell 3-3  to RIGHT-BOTTOM
	              excel.generate(today +'-'+"ProjectReport.xlsx");
	          },

}
<Script/>

`

the "&" symbol is not escaped correctly (should become "&amp;") for formulas like

="test string " & A1 & " with concatenated values"

for now the only way to solve the problem is to manually escape the value before setting the cell, like so:

excel.set(0, 0,0, '="test " & A1 & " concatenated"'.replace(/&/ig,"&"))

If there's an option to give into the set function let me know!

Hi @josesegarra,

var stringFormatCell = excel.addStyle({
font: "Calibri 11 #000000",
align: "L",
format: "@"
});

I am using excel.set(0, 4, 5, "2E169", stringFormatCell);

but when its export in the excel its showing me " 2E+169".

Can you please check and let me know the workaround

Thank you in advance.

Is there an option to merge cells in a sheet?

Jose - First of all, thank you very much for all your help in my last few questions. In one application I used to generate HTML table string and download to a file with .xls extension. The .xls file used to open in MS Excel and it need to save it as .xlsx format. In HTML string I am able to define the page set up tags like Orientation, page size, Page Header, Page number, footer, page Margin, page break etc, I am attaching an example .xls file here that you can open in notepad and see the HTML string.

My question is , could it be possible to define page set up in a worksheet using your code. It would be awesome, if it can. However, I have not seen any function in your jExcel.js or in previous Myexcel.js .

Once again, your help will be highly appreciated and it will definitely make my life much easier.

UCD

BuildersList_216.xls.txt

For example: function with parametr a="CCW" returns value "C--".

I think, the problem is in function excel.set(), because parametr "colspan" is not passed on to function setCell().
Line 694 - return setCell(s.getCell(column, row), value, style, isstring);

When doing the following...

excel.generate("MySpreadsheet.xlsx");

and then the dialog box pops up,

the filename appears to be correct ( MySpreadsheet.xlsx)

But, if user chooses to Open with Excel, the spreadsheet name has .zip appended to it.
(example: MySpreadsheet.xlsx.zip )

If I choose to save the file instead of opening in Excel the file name does not have .zip appended.

This behavior is also manifested in the MyExcel sample provided here...
http://jsegarra1971.github.io/MyExcel/sample.html

Is this a feature? How does one prevent the .zip file extension when opening in Excel instead of saving.

Hi,

I have checked the last commit and the generateMergeCells change that was previously added is missing after the last commit for supporting formulas. Was this a mistake or is there any incompatibility?

Thank you in advance
Best regards

Jose - Thank you for your reply. I have used the cell formula. It is
working fine but after opening the downloaded Excel file I must click on
the cell in order to formula get effected.

I am facing another problem with your version o

xlsx.js.txt

xlsx.js.txt

f jszip.js file. I already
have a code for file (.xlsx) drag & drop using jszip.js. Please see the
attached two files containing my code example. Could you please let me know
what change I need to make to"xlsx.js*" in order to it work. In that case
I can use single jszip.js file for both .XLSX file creation and .XLSX file
parsing. I am also attaching the version of jszip.js i was using.

Thank you. I will be highly appreciate your help.

UCD

Hi, Need a auto fit column function for string column.
Really appreciate if there's any advise how to accomplish.
Thanks a lot.

I need to strikethrough some text in a cell what is the best way to do ????
Thank you in advance

Is there any way to freeze a row or column in the Excel? Header row or first column.

Hello.

How can i break line inside one cell? i try \r\n, char(10), ... And nothing works. :(

Or, another way to help me, is... Can i merge 3 cells in 1? How?

thanks!

Not supporting string for MAC.

Hi @josesegarra ,
I want to hide the column when I export it.

Need to hide first 6 column . Can you please help me or possible to add new feature in the JS.

Waiting for your positive response
Thanks

Hi, May I ask how could I merge cell?
Thanks.

Tried excel.set(0,7,3,"15+13", '', 2); but doesn't work.

how can I change the generate function to just generate all to string without zip function?
I just need to get the xml string

thank you for your help

I want to add an image at the top of excel file.

How can I acheive that.?

Thanks in adavance

Cannot find name '$JExcel'. Did you mean 'excel'? . when we build in angular6, it thrown errors like this. Can you guide how to overcome this issue

Any chance that this lib gets an extension where one could read from excel files (with the same simplicity)

Hi.
If value = null then value.charAt(0) throw exception in excel.js "Uncaught TypeError: Cannot read property 'charAt' of undefined..."
It is advisable to add a check that like if (value == null) value = '';

var value=cell.v; if (cell.isstring || isNaN(value)) { if (cell.isstring || value.charAt(0)!='=') return s + ' t="inlineStr" ><is><t>' + escape(value) + '</t></is></c>'; return s+' ><f>'+value.substring(1)+'</f></c>'; }

Hi jsegarra1971,

I have a table created using Datatables (Datatables.net) with pagination option. So it have multiple pages when number of rows exceed to the defined value. When I tried to export this table with your library, it's exporting only the first page and not the whole table.

Could you please help me in getting this done ?

I must appreciate the work you have done. Really great library to export HTML tables to excel in a easy way. Thank you for your great work to the community.

Thanks and regards,
Varun Dutta

What about insert images in the document?