private static string FormatNumber(double d, string format, ExcelNumberFormatXml.ExcelFormatTranslator nf) { var split = format.Split(';'); if (split.Length == 3) { if (d > 0) { return(d.ToString(split[0], nf.Culture)); } else if (d < 0) { var s = d.ToString(split[1], nf.Culture); if (s.StartsWith("--") && split[1].StartsWith("-")) { return(s.Substring(1)); } return(s); } else { return(d.ToString(split[2], nf.Culture)); } } else { return(d.ToString(format, nf.Culture)); } }
internal static string GetFormattedText(object Value, ExcelWorkbook wb, int styleId, bool forWidthCalc, CultureInfo cultureInfo = null) { object v = Value; if (v == null) { return(""); } var styles = wb.Styles; var nfID = styles.CellXfs[styleId].NumberFormatId; ExcelNumberFormatXml.ExcelFormatTranslator nf = null; for (int i = 0; i < styles.NumberFormats.Count; i++) { if (nfID == styles.NumberFormats[i].NumFmtId) { nf = styles.NumberFormats[i].FormatTranslator; break; } } if (nf == null) { nf = styles.NumberFormats[0].FormatTranslator; //nf should never be null. If so set to General, Issue 173 } if (nf.Culture != null) { nf.Culture = cultureInfo; } string format, textFormat; if (forWidthCalc) { format = nf.NetFormatForWidth; textFormat = nf.NetTextFormatForWidth; } else { format = nf.NetFormat; textFormat = nf.NetTextFormat; } return(FormatValue(v, nf, format, textFormat)); }
public override string GetFormat(object value, string format) { var styles = _package.Workbook.Styles; ExcelNumberFormatXml.ExcelFormatTranslator ft = null; foreach (var f in styles.NumberFormats) { if (f.Format == format) { ft = f.FormatTranslator; break; } } if (ft == null) { ft = new ExcelNumberFormatXml.ExcelFormatTranslator(format, -1); } return(ExcelRangeBase.FormatValue(value, ft, format, ft.NetFormat)); }
private static string GetDateText(DateTime d, string format, ExcelNumberFormatXml.ExcelFormatTranslator nf) { if (nf.SpecialDateFormat == ExcelNumberFormatXml.ExcelFormatTranslator.eSystemDateFormat.SystemLongDate) { return(d.ToLongDateString()); } else if (nf.SpecialDateFormat == ExcelNumberFormatXml.ExcelFormatTranslator.eSystemDateFormat.SystemLongTime) { return(d.ToLongTimeString()); } else if (nf.SpecialDateFormat == ExcelNumberFormatXml.ExcelFormatTranslator.eSystemDateFormat.SystemShortDate) { return(d.ToShortDateString()); } if (format == "d" || format == "D") { return(d.Day.ToString()); } else if (format == "M") { return(d.Month.ToString()); } else if (format == "m") { return(d.Minute.ToString()); } else if (format.ToLower() == "y" || format.ToLower() == "yy") { return(d.ToString("yy", nf.Culture)); } else if (format.ToLower() == "yyy" || format.ToLower() == "yyyy") { return(d.ToString("yyy", nf.Culture)); } else { return(d.ToString(format, nf.Culture)); } }
internal static string FormatValue(object v, ExcelNumberFormatXml.ExcelFormatTranslator nf, string format, string textFormat) { if (v is decimal || TypeCompat.IsPrimitive(v)) { double d; try { d = Convert.ToDouble(v); } catch { return(""); } if (nf.DataType == ExcelNumberFormatXml.eFormatType.Number) { if (string.IsNullOrEmpty(nf.FractionFormat)) { return(d.ToString(format, nf.Culture)); } else { return(nf.FormatFraction(d)); } } else if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime) { var date = DateTime.FromOADate(d); return(GetDateText(date, format, nf)); } } else if (v is DateTime) { if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime) { return(GetDateText((DateTime)v, format, nf)); } else { double d = ((DateTime)v).ToOADate(); if (string.IsNullOrEmpty(nf.FractionFormat)) { return(d.ToString(format, nf.Culture)); } else { return(nf.FormatFraction(d)); } } } else if (v is TimeSpan) { if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime) { return(GetDateText(new DateTime(((TimeSpan)v).Ticks), format, nf)); } else { double d = new DateTime(0).Add((TimeSpan)v).ToOADate(); if (string.IsNullOrEmpty(nf.FractionFormat)) { return(d.ToString(format, nf.Culture)); } else { return(nf.FormatFraction(d)); } } } else { if (textFormat == "") { return(v.ToString()); } else { return(string.Format(textFormat, v)); } } return(v.ToString()); }