public string ToString(double value, ExpressionFormat info = null) { string text; if (value < MIN_NUMBER || value > MAX_NUMBER) { text = ExcelValue.VALUE.ToString(); } else { info ??= ExpressionFormat.General; var format = info.GetFormat(null); if (format.NeedsDate) { var date = ExcelValue.FromDateSerial(value); // Should start from beginning in order to use the overriden methods if (date.HasValue) { text = ToString(date.Value, info); } else { text = ExcelValue.NA.ToString(); } } else { text = string.Format(culture, format.Format, Convert.ToDecimal(value)); } } return(text); }
public void DATEVALUE() { AssertExpression("=DATEVALUE(\"\")", "#VALUE!"); AssertExpression("=DATEVALUE(\"22/8/2011\")", "40777"); AssertExpression("=DATEVALUE(\"22-Απρ-2011\")", "40655"); AssertExpression("=DATEVALUE(\"23/2/2011\")", "40597"); AssertExpression("=DATEVALUE(\"23-Ιουλ\")", ExcelValue.ToDateSerial(DateTime.Now.Year, 7, 23).ToString()); AssertExpression("=DATEVALUE(\"22-Απλ-2011\")", "#VALUE!"); }
public void DateValue_FromDateSerial() { Assert.Null(ExcelValue.FromDateSerial(-1)); Assert.Null(ExcelValue.FromDateSerial(0)); Assert.Equal(new DateTime(1900, 1, 1, 6, 0, 0), ExcelValue.FromDateSerial(0.25)); Assert.Equal(new DateTime(1900, 1, 1, 12, 0, 0), ExcelValue.FromDateSerial(0.5)); Assert.Equal(new DateTime(1900, 1, 1, 18, 0, 0), ExcelValue.FromDateSerial(0.75)); Assert.Equal(new DateTime(1900, 1, 1), ExcelValue.FromDateSerial(1)); Assert.Equal(new DateTime(1900, 1, 2), ExcelValue.FromDateSerial(2)); Assert.Equal(new DateTime(1900, 2, 28), ExcelValue.FromDateSerial(59)); Assert.Equal(new DateTime(1900, 3, 1), ExcelValue.FromDateSerial(61)); Assert.Equal(new DateTime(2008, 1, 1), ExcelValue.FromDateSerial(39448)); Assert.Equal(new DateTime(2020, 5, 16), ExcelValue.FromDateSerial(43967)); }
private static void AdjustColumnMinMaxDelete(ExcelWorksheet ws, int columnFrom, int columns) { ExcelColumn col = ws.GetValueInner(0, columnFrom) as ExcelColumn; if (col == null) { var r = 0; var c = columnFrom; if (ws._values.PrevCell(ref r, ref c)) { col = ws.GetValueInner(0, c) as ExcelColumn; if (col._columnMax >= columnFrom) { col.ColumnMax = columnFrom - 1; } } } var toCol = columnFrom + columns - 1; var moveValue = new ExcelValue { _styleId = int.MaxValue }; var cse = new CellStoreEnumerator <ExcelValue>(ws._values, 0, columnFrom, 0, ExcelPackage.MaxColumns); while (cse.MoveNext()) { var column = cse.Value._value as ExcelColumn; if (column != null && column._columnMax > toCol) { if (column.ColumnMin > toCol) { column._columnMin -= columns; column._columnMax -= columns; } else if (column.ColumnMax > toCol) { column._columnMax -= columns; if (column._columnMin > columnFrom) { column._columnMin = columnFrom; } moveValue = cse.Value; } } } if (moveValue._styleId != int.MaxValue) { ws._values.SetValue(0, toCol + 1, moveValue); } }
public void DateValue_ToDateSerial() { Assert.Null(ExcelValue.ToDateSerial(new DateTime(1821, 3, 25))); Assert.Null(ExcelValue.ToDateSerial(new DateTime(1899, 12, 31))); Assert.Equal(1, ExcelValue.ToDateSerial(new DateTime(1900, 1, 1))); Assert.Equal(2, ExcelValue.ToDateSerial(new DateTime(1900, 1, 2))); Assert.Equal(59, ExcelValue.ToDateSerial(new DateTime(1900, 2, 28))); Assert.Equal(61, ExcelValue.ToDateSerial(new DateTime(1900, 3, 1))); Assert.Equal(39448, ExcelValue.ToDateSerial(new DateTime(2008, 1, 1))); Assert.Equal(43967, ExcelValue.ToDateSerial(new DateTime(2020, 5, 16))); Assert.Null(ExcelValue.ToDateSerial(0, 0, 0, 0, 0, -1)); Assert.Equal(0.0, ExcelValue.ToDateSerial(0, 0, 0, 0, 0, 0)); Assert.Equal(0.0000115741, ExcelValue.ToDateSerial(0, 0, 0, 0, 0, 1)); Assert.Equal(0.4668171296, ExcelValue.ToDateSerial(0, 0, 0, 11, 12, 13)); Assert.Equal(0.9999884259, ExcelValue.ToDateSerial(0, 0, 0, 23, 59, 59)); }
public virtual string ToString(DateTime value, ExpressionFormat info) { var format = info.GetFormat(ExpressionFormat.ShortDatePattern); if (format.NeedsDate) { return(string.Format(culture, format.Format, value)); } // Should start from beginning in order to use the overriden methods var serial = ExcelValue.ToDateSerial(value); if (serial.HasValue) { return(ToString(serial.Value, info)); } return(ExcelValue.NA.ToString()); }
private void SetStyleCells(StyleBase sender, StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, Dictionary <int, int> styleCashe) { var rowCache = new Dictionary <int, int>(address.End.Row - address.Start.Row + 1); var colCache = new Dictionary <int, ExcelValue>(address.End.Column - address.Start.Column + 1); var cellEnum = new CellStoreEnumerator <ExcelValue>(ws._values, address.Start.Row, address.Start.Column, address.End.Row, address.End.Column); var hasEnumValue = cellEnum.Next(); for (int row = address._fromRow; row <= address._toRow; row++) { for (int col = address._fromCol; col <= address._toCol; col++) { ExcelValue value; if (hasEnumValue && row == cellEnum.Row && col == cellEnum.Column) { value = cellEnum.Value; hasEnumValue = cellEnum.Next(); } else { value = new ExcelValue { _styleId = 0 }; } var s = value._styleId; if (s == 0) { // get row styleId with cache if (rowCache.ContainsKey(row)) { s = rowCache[row]; } else { s = ws._values.GetValue(row, 0)._styleId; rowCache.Add(row, s); } if (s == 0) { // get column styleId with cache if (colCache.ContainsKey(col)) { s = colCache[col]._styleId; } else { var v = ws._values.GetValue(0, col); colCache.Add(col, v); s = v._styleId; } if (s == 0) { int r = 0, c = col; if (ws._values.PrevCell(ref r, ref c)) { if (!colCache.ContainsKey(c)) { colCache.Add(c, ws._values.GetValue(0, c)); } var val = colCache[c]; var colObj = (ExcelColumn)(val._value); if (colObj != null && colObj.ColumnMax >= col) //Fixes issue 15174 { s = val._styleId; } } } } } if (styleCashe.ContainsKey(s)) { ws._values.SetValue(row, col, new ExcelValue { _value = value._value, _styleId = styleCashe[s] }); } else { ExcelXfs st = CellXfs[s]; int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(s, newId); ws._values.SetValue(row, col, new ExcelValue { _value = value._value, _styleId = newId }); } } } }