internal void SetCellDataFormat(Cell cell, CellDataFormatFlag format,
ref object dataFormatArgs, List <Cell> formulaDirtyCells = null)
{
cell.DataFormat = format;
cell.DataFormatArgs = dataFormatArgs;
//string oldDisplay = cell.Display;
DataFormatterManager.Instance.FormatCell(cell);
unvell.ReoGrid.Utility.StyleUtility.UpdateCellRenderAlign(this, cell);
UpdateCellTextBounds(cell);
#if FORMULA
if (formulaDirtyCells != null)
{
// reference ranges (including cells)
foreach (var referencedRange in formulaRanges)
{
if (referencedRange.Value.Any(rr => rr.Contains(cell.InternalPos)))
{
if (!formulaDirtyCells.Contains(referencedRange.Key))
{
formulaDirtyCells.Add(referencedRange.Key);
}
}
}
}
#endif
}
/// <summary>
/// Set data format for specified range.
/// </summary>
/// <param name="range">Range to be set.</param>
/// <param name="format">Flag specifies that which format will be used.</param>
/// <param name="dataFormatArgs">Argument to specify the details for different format.</param>
public void SetRangeDataFormat(RangePosition range, CellDataFormatFlag format, object dataFormatArgs = null)
{
RangePosition fixedRange = FixRange(range);
int rend = fixedRange.EndRow;
int cend = fixedRange.EndCol;
#if FORMULA
List <Cell> formulaDirtyCells = new List <Cell>(10);
#else
List <Cell> formulaDirtyCells = null;
#endif
for (int r = fixedRange.Row; r <= rend; r++)
{
for (int c = fixedRange.Col; c <= cend;)
{
Cell cell = CreateAndGetCell(r, c);
SetCellDataFormat(cell, format, ref dataFormatArgs, formulaDirtyCells);
c += cell.Colspan > 1 ? cell.Colspan : 1;
}
}
#if FORMULA
foreach (var cell in formulaDirtyCells)
{
RecalcCell(cell);
}
#endif
RequestInvalidate();
}
/// <summary>
/// Do this operation.
/// </summary>
public override void Do()
{
Cell cell = Worksheet.CreateAndGetCell(Row, Col);
this.backupData = cell.HasFormula ? ("=" + cell.InnerFormula) : cell.InnerData;
this.backupDataFormat = cell.DataFormat;
this.backupDataFormatArgs = cell.DataFormatArgs;
try
{
Worksheet.SetSingleCellData(cell, Data);
var rowHeightSettings = WorksheetSettings.Edit_AutoExpandRowHeight
| WorksheetSettings.Edit_AllowAdjustRowHeight;
RowHeader rowHeader = this.Worksheet.GetRowHeader(cell.InternalRow);
this.backupRowHeight = rowHeader.InnerHeight;
if ((this.Worksheet.settings & rowHeightSettings) == rowHeightSettings &&
rowHeader.IsAutoHeight)
{
cell.ExpandRowHeight();
}
}
catch (Exception ex)
{
this.Worksheet.NotifyExceptionHappen(ex);
}
}
/// <summary>
/// Create instance for SetRangeDataFormatAction.
/// </summary>
/// <param name="range">Range to be appiled this action.</param>
/// <param name="format">Format type of cell to be set.</param>
/// <param name="dataFormatArgs">Argument belongs to format type to be set.</param>
public SetRangeDataFormatAction(RangePosition range, CellDataFormatFlag format,
object dataFormatArgs)
: base(range)
{
this.format = format;
this.formatArgs = dataFormatArgs;
}
/// <summary>
/// Set data format for specified range.
/// </summary>
/// <param name="addressOrName">Address or name to locate the cell and range on spreadsheet to be set.</param>
/// <param name="format">Flag specifies that which format will be used.</param>
/// <param name="dataFormatArgs">Argument to specify the details for different format.</param>
/// <exception cref="InvalidAddressException">Throw if specified address or name is illegal.</exception>
public void SetRangeDataFormat(string addressOrName, CellDataFormatFlag format, object dataFormatArgs = null)
{
if (RangePosition.IsValidAddress(addressOrName))
{
SetRangeDataFormat(new RangePosition(addressOrName), format, dataFormatArgs);
}
else if (this.registeredNamedRanges.TryGetValue(addressOrName, out var namedRange))
{
SetRangeDataFormat(namedRange, format, dataFormatArgs);
}
else
{
throw new InvalidAddressException(addressOrName);
}
}
internal static string EncodeCellDataFormat(CellDataFormatFlag format)
{
switch (format)
{
default:
case CellDataFormatFlag.General:
return(null);
case CellDataFormatFlag.Number:
return("number");
case CellDataFormatFlag.Text:
return("text");
case CellDataFormatFlag.DateTime:
return("datetime");
case CellDataFormatFlag.Percent:
return("percent");
case CellDataFormatFlag.Currency:
return("currency");
}
}
/// <summary>
/// Set data format for specified range.
/// </summary>
/// <param name="row">Number of row to locate the range.</param>
/// <param name="col">Number of column to locate the range.</param>
/// <param name="rows">Number of rows contained in the range.</param>
/// <param name="cols">Number of columns contained in the range.</param>
/// <param name="format">Flag specifies that which format will be used.</param>
/// <param name="dataFormatArgs">Argument to specify the details for different format.</param>
public void SetRangeDataFormat(int row, int col, int rows, int cols, CellDataFormatFlag format, object dataFormatArgs = null)
{
SetRangeDataFormat(new RangePosition(row, col, rows, cols), format, dataFormatArgs);
}
public DataFormatAttribute(CellDataFormatFlag formatFlag)
{
this.formatFlag = formatFlag;
}
internal void SetCellDataFormat(CellPosition pos, CellDataFormatFlag format, ref object dataFormatArgs)
{
SetCellDataFormat(CreateAndGetCell(pos), format, ref dataFormatArgs);
}
public FormatPage()
{
InitializeComponent();
#region Initialize Setting Panels
numberNegativeStyleList.Items.Add(new NegativeStyleListItem(
NumberDataFormatter.NumberNegativeStyle.Default, (-1234.10f).ToString("###,###.00"), SystemColors.WindowText));
numberNegativeStyleList.Items.Add(new NegativeStyleListItem(
NumberDataFormatter.NumberNegativeStyle.Red, (1234.10f).ToString("###,###.00"), Color.Red));
numberNegativeStyleList.Items.Add(new NegativeStyleListItem(
NumberDataFormatter.NumberNegativeStyle.Brackets, (1234.10f).ToString("(###,###.00)"), SystemColors.WindowText));
numberNegativeStyleList.Items.Add(new NegativeStyleListItem(
NumberDataFormatter.NumberNegativeStyle.RedBrackets, (1234.10f).ToString("(###,###.00)"), Color.Red));
numberNegativeStyleList.Items.Add(new NegativeStyleListItem(
NumberDataFormatter.NumberNegativeStyle.DollarSymbol |
NumberDataFormatter.NumberNegativeStyle.Default, (-1234.10f).ToString("$###,###.00"), SystemColors.WindowText));
numberNegativeStyleList.Items.Add(new NegativeStyleListItem(
NumberDataFormatter.NumberNegativeStyle.DollarSymbol |
NumberDataFormatter.NumberNegativeStyle.Red, (1234.10f).ToString("$###,###.00"), Color.Red));
numberNegativeStyleList.Items.Add(new NegativeStyleListItem(
NumberDataFormatter.NumberNegativeStyle.DollarSymbol |
NumberDataFormatter.NumberNegativeStyle.Brackets, (1234.10f).ToString("($###,###.00)"), SystemColors.WindowText));
numberNegativeStyleList.Items.Add(new NegativeStyleListItem(
NumberDataFormatter.NumberNegativeStyle.DollarSymbol |
NumberDataFormatter.NumberNegativeStyle.RedBrackets, (1234.10f).ToString("($###,###.00)"), Color.Red));
numberNegativeStyleList.Items.Add(new NegativeStyleListItem(
NumberDataFormatter.NumberNegativeStyle.Prefix_Sankaku, (1234.10f).ToString("▲ ###,###.00"), SystemColors.WindowText));
if (numberNegativeStyleList.Items.Count > 0)
{
numberNegativeStyleList.SelectedIndex = 0;
}
datetimeFormatList.SelectedIndexChanged += (s, e) =>
{
if (datetimeFormatList.SelectedItem != null)
{
DatetimeFormatListItem item = (DatetimeFormatListItem)datetimeFormatList.SelectedItem;
txtDatetimeFormat.Text = item.Pattern;
}
};
var currentCulture = Thread.CurrentThread.CurrentCulture;
datetimeFormatList.Items.AddRange(new object[] {
// culture patterns
new DatetimeFormatListItem(true, currentCulture.DateTimeFormat.ShortDatePattern),
new DatetimeFormatListItem(true, currentCulture.DateTimeFormat.LongDatePattern),
new DatetimeFormatListItem(true, currentCulture.DateTimeFormat.ShortDatePattern
+ " " + currentCulture.DateTimeFormat.ShortTimePattern),
new DatetimeFormatListItem(true, currentCulture.DateTimeFormat.LongDatePattern
+ " " + currentCulture.DateTimeFormat.LongTimePattern),
new DatetimeFormatListItem(true, currentCulture.DateTimeFormat.ShortTimePattern),
new DatetimeFormatListItem(true, currentCulture.DateTimeFormat.LongTimePattern),
// predefine patterns
new DatetimeFormatListItem(false, "yyyy/M/d"),
new DatetimeFormatListItem(false, "yyyy/M/d hh:mm"),
new DatetimeFormatListItem(false, "M/d"),
new DatetimeFormatListItem(false, "hh:mm"),
});
datetimeLocationList.SelectedIndexChanged += (s, e) =>
{
var ci = (CultureInfo)datetimeLocationList.SelectedItem;
((DatetimeFormatListItem)(datetimeFormatList.Items[0])).Pattern = ci.DateTimeFormat.ShortDatePattern;
((DatetimeFormatListItem)(datetimeFormatList.Items[1])).Pattern = ci.DateTimeFormat.LongDatePattern;
((DatetimeFormatListItem)(datetimeFormatList.Items[2])).Pattern = ci.DateTimeFormat.ShortDatePattern + " " + ci.DateTimeFormat.ShortTimePattern;
((DatetimeFormatListItem)(datetimeFormatList.Items[3])).Pattern = ci.DateTimeFormat.LongDatePattern + " " + ci.DateTimeFormat.LongTimePattern;
((DatetimeFormatListItem)(datetimeFormatList.Items[4])).Pattern = ci.DateTimeFormat.ShortTimePattern;
((DatetimeFormatListItem)(datetimeFormatList.Items[5])).Pattern = ci.DateTimeFormat.LongTimePattern;
DateTime dt = new DateTime(1980, 7, 13);
CultureInfo culture = (CultureInfo)datetimeLocationList.SelectedItem;
foreach (DatetimeFormatListItem item in datetimeFormatList.Items)
{
item.Sample = dt.ToString(item.Pattern, culture);
}
typeof(ListBox).InvokeMember("RefreshItems",
BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.InvokeMethod,
null, datetimeFormatList, new object[] { });
object backup = datetimeFormatList.SelectedItem;
if (backup != null)
{
datetimeFormatList.SelectedItem = null;
datetimeFormatList.SelectedItem = backup;
}
else
{
datetimeFormatList.SelectedIndex = 0;
}
};
currencySymbolList.SelectedIndexChanged += (s, e) =>
{
var listItem = (CurrencySymbolListItem)currencySymbolList.SelectedItem;
currencyNegativeStyleList.Items.Clear();
currencyNegativeStyleList.Items.Add(new NegativeStyleListItem(
NumberDataFormatter.NumberNegativeStyle.Default, /*"-$1,234.10 ###,###.00"*/ (1234.10f).ToString("c", listItem.Culture), SystemColors.WindowText));
currencyNegativeStyleList.Items.Add(new NegativeStyleListItem(
NumberDataFormatter.NumberNegativeStyle.Red, /*"$1,234.10"*/ (1234.10f).ToString("c", listItem.Culture), Color.Red));
currencyNegativeStyleList.Items.Add(new NegativeStyleListItem(
NumberDataFormatter.NumberNegativeStyle.Brackets, /*"($1,234.10)"*/ "(" + (1234.10f).ToString("c", listItem.Culture) + ")", SystemColors.WindowText));
currencyNegativeStyleList.Items.Add(new NegativeStyleListItem(
NumberDataFormatter.NumberNegativeStyle.RedBrackets, /*"($1,234.10)"*/ "(" + (1234.10f).ToString("c", listItem.Culture) + ")", Color.Red));
if (currentFormatArgs != null)
{
currencyFormatArgs.PrefixSymbol = null;
currencyFormatArgs.PostfixSymbol = null;
}
if (currencyNegativeStyleList.Items.Count > 0)
{
currencyNegativeStyleList.SelectedIndex = 0;
}
UpdateSample();
};
var cultures = CultureInfo.GetCultures(CultureTypes.InstalledWin32Cultures).Where(c => !c.IsNeutralCulture).OrderBy(c => c.EnglishName);
foreach (CultureInfo info in cultures)
{
datetimeLocationList.Items.Add(info);
if (info.Equals(currentCulture))
{
datetimeLocationList.SelectedItem = info;
}
try
{
var item = new CurrencySymbolListItem(info);
currencySymbolList.Items.Add(item);
if (info.Equals(currentCulture))
{
currencySymbolList.SelectedItem = item;
}
}
catch { }
}
// add valid data formatter
foreach (var key in Enum.GetValues(typeof(CellDataFormatFlag)))
{
IDataFormatter formatter;
if (DataFormatterManager.Instance.DataFormatters.TryGetValue((CellDataFormatFlag)key, out formatter))
{
formatList.Items.Add(key);
}
}
numberFormatArgs = new NumberDataFormatter.NumberFormatArgs
{
DecimalPlaces = 2,
NegativeStyle = NumberDataFormatter.NumberNegativeStyle.Default,
UseSeparator = true,
};
datetimeFormatArgs = new DateTimeDataFormatter.DateTimeFormatArgs
{
Format = currentCulture.DateTimeFormat.ShortDatePattern,
};
currencyFormatArgs = new CurrencyDataFormatter.CurrencyFormatArgs()
{
CultureEnglishName = currentCulture.EnglishName,
DecimalPlaces = (short)currentCulture.NumberFormat.CurrencyDecimalDigits,
NegativeStyle = NumberDataFormatter.NumberNegativeStyle.Default,
};
#endregion
formatList.SelectedIndexChanged += (sender, e) =>
{
try
{
currentFormat = (CellDataFormatFlag)Enum.Parse(typeof(CellDataFormatFlag), formatList.Text);
}
catch
{
currentFormat = CellDataFormatFlag.General;
}
Panel newSettingPanel = null;
switch (currentFormat)
{
case CellDataFormatFlag.Number:
newSettingPanel = numberPanel;
break;
case CellDataFormatFlag.DateTime:
newSettingPanel = datetimePanel;
break;
case CellDataFormatFlag.Currency:
newSettingPanel = currencyPanel;
break;
case CellDataFormatFlag.Percent:
newSettingPanel = percentPanel;
break;
}
if (newSettingPanel != currentSettingPanel)
{
if (newSettingPanel != null)
{
newSettingPanel.Location = new Point(grpSample.Left - 5, grpSample.Bottom + 10);
newSettingPanel.Show();
}
if (currentSettingPanel != null)
{
currentSettingPanel.Hide();
}
currentSettingPanel = newSettingPanel;
}
UpdateSample();
};
numberDecimalPlaces.ValueChanged += (s, e) => UpdateSample();
chkNumberUseSeparator.CheckedChanged += (s, e) => UpdateSample();
numberNegativeStyleList.SelectedIndexChanged += (s, e) => UpdateSample();
currencyDecimalPlaces.ValueChanged += (s, e) => UpdateSample();
currencyNegativeStyleList.SelectedIndexChanged += (s, e) => UpdateSample();
percentDecimalPlaces.ValueChanged += (s, e) => UpdateSample();
formatList.DoubleClick += (s, e) => RaiseDone();
numberNegativeStyleList.DoubleClick += (s, e) => RaiseDone();
datetimeFormatList.DoubleClick += (s, e) => RaiseDone();
currencyNegativeStyleList.DoubleClick += (s, e) => RaiseDone();
txtDatetimeFormat.TextChanged += (s, e) => UpdateSample();
}
public void LoadPage()
{
var sheet = this.grid.CurrentWorksheet;
sheet.IterateCells(sheet.SelectionRange, (r, c, cell) =>
{
if (backupFormat == CellDataFormatFlag.General)
{
sampleCell = cell.Clone();
unvell.ReoGrid.Utility.CellUtility.CopyCellContent(sampleCell, cell);
if (cell != null)
{
originalData = cell.Data;
}
backupFormat = cell.DataFormat;
return(true);
}
else if (backupFormat == cell.DataFormat)
{
return(true);
}
else
{
backupFormat = CellDataFormatFlag.General;
return(false);
}
});
currentFormat = backupFormat;
backupFormatArgs = null;
if (currentFormat != CellDataFormatFlag.General)
{
switch (currentFormat)
{
case CellDataFormatFlag.Number:
if ((sampleCell.DataFormatArgs
?? new NumberDataFormatter.NumberFormatArgs())
is NumberDataFormatter.NumberFormatArgs nargs)
{
numberDecimalPlaces.Value = nargs.DecimalPlaces;
chkNumberUseSeparator.Checked = nargs.UseSeparator;
foreach (NegativeStyleListItem item in numberNegativeStyleList.Items)
{
if (item.NegativeStyle == nargs.NegativeStyle)
{
numberNegativeStyleList.SelectedItem = item;
break;
}
}
}
backupFormatArgs = Clone(sampleCell.DataFormatArgs);
break;
case CellDataFormatFlag.DateTime:
DateTimeDataFormatter.DateTimeFormatArgs dargs = (DateTimeDataFormatter.DateTimeFormatArgs)sampleCell.DataFormatArgs;
foreach (var item in datetimeLocationList.Items)
{
if (string.Compare(((CultureInfo)item).IetfLanguageTag, dargs.CultureName, true) == 0)
{
datetimeLocationList.SelectedItem = item;
break;
}
}
txtDatetimeFormat.Text = dargs.Format;
object df = null;
foreach (var item in datetimeFormatList.Items)
{
if (((DatetimeFormatListItem)item).Pattern.Equals(dargs.Format, StringComparison.CurrentCultureIgnoreCase))
{
df = item;
break;
}
}
datetimeFormatList.SelectedItem = df;
backupFormatArgs = Clone(sampleCell.DataFormatArgs);
break;
case CellDataFormatFlag.Currency:
var cargs = (CurrencyDataFormatter.CurrencyFormatArgs)sampleCell.DataFormatArgs;
foreach (var item in currencySymbolList.Items)
{
if (string.Compare(((CurrencySymbolListItem)item).Culture.IetfLanguageTag, cargs.CultureEnglishName, true) == 0)
{
currencySymbolList.SelectedItem = item;
break;
}
}
currencyDecimalPlaces.Value = cargs.DecimalPlaces;
foreach (NegativeStyleListItem item in currencyNegativeStyleList.Items)
{
if (item.NegativeStyle == cargs.NegativeStyle)
{
currencyNegativeStyleList.SelectedItem = item;
break;
}
}
backupFormatArgs = Clone(sampleCell.DataFormatArgs);
break;
case CellDataFormatFlag.Percent:
if ((sampleCell.DataFormatArgs
?? new NumberDataFormatter.NumberFormatArgs())
is NumberDataFormatter.NumberFormatArgs pargs)
{
percentDecimalPlaces.Value = pargs.DecimalPlaces;
}
backupFormatArgs = Clone(sampleCell.DataFormatArgs);
break;
}
for (int i = 0; i < formatList.Items.Count; i++)
{
var item = formatList.Items[i].ToString();
if (string.Equals(item, currentFormat.ToString(), StringComparison.CurrentCultureIgnoreCase))
{
formatList.SelectedIndex = i;
break;
}
}
}
else
{
formatList.SelectedIndex = 0;
}
backupFormat = currentFormat;
}
public DataFormatAttribute(CellDataFormatFlag formatFlag)
{
this.formatFlag = formatFlag;
}
/// <summary>
/// Do this action
/// </summary>
public override void Do()
{
isCellNull = Grid.GetCell(row, col) == null;
ReoGridCell cell = Grid.CreateAndGetCell(row, col);
if (cell != null)
{
dataBackup = cell.Data;
displayBackup = cell.Display;
formatBackup = cell.DataFormat;
Grid.SetCellData(cell, data);
Grid.SelectRange(new ReoGridRange(cell.Row, cell.Col, cell.Rowspan, cell.Colspan));
isCellNull = false;
}
else
isCellNull = true;
}
/// <summary>
/// Create instance for SetRangeDataFormatAction
/// </summary>
/// <param name="range">Range to be appiled this action</param>
/// <param name="format">Format type of cell to be set</param>
/// <param name="dataFormatArgs">Argument belongs to format type to be set</param>
public RGSetRangeDataFormatAction(ReoGridRange range, CellDataFormatFlag format,
object dataFormatArgs)
: base(range)
{
this.format = format;
this.formatArgs = dataFormatArgs;
}
internal static string EncodeCellDataFormat(CellDataFormatFlag format)
{
switch (format)
{
default:
case CellDataFormatFlag.General:
return null;
case CellDataFormatFlag.Number:
return "number";
case CellDataFormatFlag.Text:
return "text";
case CellDataFormatFlag.DateTime:
return "datetime";
case CellDataFormatFlag.Percent:
return "percent";
case CellDataFormatFlag.Currency:
return "currency";
}
}
