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
});
}
}
}
}
