Format Number in Cell

[jhortalburq]

I have a problem when exporting numbers, with separator format of thousands (,) ejem: 1,234.05 --- DO NOT take it as number, in excel, another problem is when I have numbers like 04.10 ... it takes as date, and I do not like Number ... any recommendations?

[hguzmang]

Sometimes 1245.67% gets into a date and sometimes into a ###############. Everything else is really great. Thanks!

[LuigiSZ]

//-------------------------------------------------

var isNumber = function isNumber(value) { // valid for italy number // , as decimal separator // . as thousand separator

var gg = value;
if (gg.indexOf(".") > -1) {	
	if (gg.indexOf(".") < (gg.length - 2)) {
		gg = value.replace(".","");
	}
}
gg = gg.replace(",",".");

if (typeof gg != "string" && gg != 'number') return false // we only process strings!  

var bRet = !isNaN(gg) && // use type coercion to parse the _entirety_ of the string (`parseFloat` alone 	does not do this)...
     !isNaN(parseFloat(gg)) // ...and ensure strings of whitespace fail
	 || typeof gg === 'number' && isFinite(gg)
	 

return bRet

}

//-------------------------------------------------

var isBoolean = function isBoolean(value) { return typeof variable === "boolean"; }

//-------------------------------------------------

var isValidDate = function isValidDate(obj) {

 // Checks for the following valid date formats:
 // DD/MM/YYYY
 // Also separates date into month, day, and year variables
 var datePat = /^(\d{2,2})(\/)(\d{2,2})\2(\d{4}|\d{4})$/;
 
var dateStr = obj;
 
if (dateStr.length == 0) {
	return false;
}

if (dateStr.length == 8) {
	if (dateStr.indexOf("/") == -1) {
		dateStr = dateStr.substring(0,2) + "/" + dateStr.substring(2,4) + "/" + dateStr.substring(4);
	} else {
		dateStr = dateStr.substring(0,6) + "20" + dateStr.substring(6);
	}			
}
if (dateStr.length == 6) {
	if (dateStr.indexOf("/") == -1) {
		dateStr = dateStr.substring(0,2) + "/" + dateStr.substring(2,4) + "/20" + dateStr.substring(4);
	}		
}

 var matchArray = dateStr.match(datePat); // is the format ok?
 if (matchArray == null) {
  return false;
 }
 
  // parse date into variables
 day = matchArray[1];
 month = matchArray[3];
 year = matchArray[4];
 
 if (month < 1 || month > 12) { // check month range
  return false;
 }
 if (day < 1 || day > 31) {
  return false;
 }
 if ((month==4 || month==6 || month==9 || month==11) && day==31) {
  return false;
 }
 if (month == 2) { // check for february 29th
  var isleap = (year % 4 == 0 && (year % 100 != 0 || year % 400 == 0));
  if (day>29 || (day==29 && !isleap)) {
   return false;
	}
 }
 return true;  // date is valid

}

//-------------------------------------------------

var vv = cellText; var tt = ''; if (isNumber(cellText)) { tt = 'n'; if (vv.indexOf(".") > -1) { if (vv.indexOf(".") < (vv.length - 2)) { vv = vv.replace(".",""); } } vv = vv.replace(",","."); bFmt = true; ffmt = '#,##0.00'; } else if (isBoolean(cellText)) { tt = 'b'; } else if (isValidDate(cellText)) { tt = 'n'; vv = JSDateToExcelDate(cellText); bFmt = true; ffmt = 'dd/mm/yyyy'; } else { tt = 's'; }

			return {
			  t: tt,
			  v: vv,
			  r: '<t>' + cellText + '</t>',
			  h: cellText,
			  w: cellText,
			  ...

//-------------------------------------------------

[ronskie69]

same problem here...