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