/// <summary>
/// 根据格式返回 NumberFormatArgs
/// </summary>
/// <param name="dataFormat"></param>
/// <returns></returns>
private NumberFormatArgs getDataFormat(string dataFormat)
{
short lenth = 0;
if (dataFormat.Length <= 1)
{
lenth = 2;
}
else
{
lenth = (short)dataFormat.Split('.')[1].Length;
}
NumberFormatArgs arg = new NumberDataFormatter.NumberFormatArgs()
{
// 保留小数位数
DecimalPlaces = lenth,
// 默认显示负数
NegativeStyle = NumberDataFormatter.NumberNegativeStyle.Default,
// 不使用逗号分隔
UseSeparator = false,
};
return(arg);
}
private string DumpFormatArgs(object args)
{
StringBuilder sb = new StringBuilder();
if (args is NumberDataFormatter.NumberFormatArgs)
{
NumberDataFormatter.NumberFormatArgs nargs = (NumberDataFormatter.NumberFormatArgs)args;
sb.AppendLine(string.Format("{0,-20} = {1}", "Decimal Places", nargs.DecimalPlaces));
sb.AppendLine(string.Format("{0,-20} = {1}", "Negative Style", nargs.NegativeStyle.ToString()));
sb.AppendLine(string.Format("{0,-20} = {1}", "Use Separator", nargs.UseSeparator));
}
else if (args is DateTimeDataFormatter.DateTimeFormatArgs)
{
DateTimeDataFormatter.DateTimeFormatArgs dargs = (DateTimeDataFormatter.DateTimeFormatArgs)args;
sb.AppendLine(string.Format("{0,-20} = {1}", "Format", dargs.Format));
}
else if (args is CurrencyDataFormatter.CurrencyFormatArgs)
{
CurrencyDataFormatter.CurrencyFormatArgs cmargs = (CurrencyDataFormatter.CurrencyFormatArgs)args;
sb.AppendLine(string.Format("{0,-20} = {1}", "Culture EnglishName", cmargs.CultureEnglishName));
sb.AppendLine(string.Format("{0,-20} = {1}", "Decimal Places", cmargs.DecimalPlaces));
sb.AppendLine(string.Format("{0,-20} = {1}", "Negative Style", cmargs.NegativeStyle));
sb.AppendLine(string.Format("{0,-20} = {1}", "Prefix Symbol", cmargs.PrefixSymbol));
sb.AppendLine(string.Format("{0,-20} = {1}", "Postfix Symbol", cmargs.PostfixSymbol));
}
return(sb.ToString());
}
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));
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).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 == null)
{
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 = null;
return(false);
}
});
currentFormat = backupFormat;
backupFormatArgs = null;
if (currentFormat != null)
{
switch (currentFormat)
{
case CellDataFormatFlag.Number:
if (sampleCell.DataFormatArgs is NumberDataFormatter.NumberFormatArgs)
{
NumberDataFormatter.NumberFormatArgs nargs = (NumberDataFormatter.NumberFormatArgs)sampleCell.DataFormatArgs;
numberDecimalPlaces.Value = nargs.DecimalPlaces;
chkNumberUseSeparator.Checked = nargs.UseSeparator;
foreach (NegativeStyleListItem item in numberNegativeStyleList.Items)
{
if (item.NegativeStyle == nargs.NegativeStyle)
{
numberNegativeStyleList.SelectedItem = item;
break;
}
}
backupFormatArgs = nargs;
}
break;
case CellDataFormatFlag.DateTime:
DateTimeDataFormatter.DateTimeFormatArgs dargs = (DateTimeDataFormatter.DateTimeFormatArgs)sampleCell.DataFormatArgs;
txtDatetimeFormat.Text = dargs.Format;
int dfindex = -1;
for (int i = 0; i < datetimeFormatList.Items.Count; i++)
{
DatetimeFormatListItem item = (DatetimeFormatListItem)datetimeFormatList.Items[i];
if (item.Pattern.Equals(dargs.Format, StringComparison.CurrentCultureIgnoreCase))
{
dfindex = i;
break;
}
}
datetimeFormatList.SelectedIndex = dfindex;
backupFormatArgs = dargs;
break;
case CellDataFormatFlag.Currency:
var cargs = (CurrencyDataFormatter.CurrencyFormatArgs)sampleCell.DataFormatArgs;
var cultureName = cargs.CultureEnglishName;
foreach (var currencyCultureItem in currencySymbolList.Items)
{
if (string.Compare(((CurrencySymbolListItem)currencyCultureItem).Culture.EnglishName, cultureName, true) == 0)
{
currencySymbolList.SelectedItem = currencyCultureItem;
break;
}
}
currencyDecimalPlaces.Value = cargs.DecimalPlaces;
int cnindex = (int)cargs.NegativeStyle;
if (cnindex >= 0 && cnindex < currencyNegativeStyleList.Items.Count)
{
currencyNegativeStyleList.SelectedIndex = cnindex;
}
foreach (NegativeStyleListItem item in currencyNegativeStyleList.Items)
{
if (item.NegativeStyle == cargs.NegativeStyle)
{
currencyNegativeStyleList.SelectedItem = item;
break;
}
}
backupFormatArgs = cargs;
break;
case CellDataFormatFlag.Percent:
var pargs = (NumberDataFormatter.NumberFormatArgs)sampleCell.DataFormatArgs;
percentDecimalPlaces.Value = pargs.DecimalPlaces;
backupFormatArgs = pargs;
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;
}
/// <summary>
/// Init Worksheet
/// </summary>
private void InitWorksheet()
{
WorksheetAttribute classAttribue = _RecordModelType.GetCustomAttribute(typeof(WorksheetAttribute)) as WorksheetAttribute;
if (classAttribue != null)
{
_Worksheet.Name = classAttribue.Title;
}
else
{
_Worksheet.Name = _RecordModelType.Name;
}
PropertyInfo[] properties = _RecordModelType.GetProperties();
Dictionary <PropertyInfo, ColumnHeaderAttribute> ColHeaderAttributeDict = new Dictionary <PropertyInfo, ColumnHeaderAttribute>();
foreach (PropertyInfo property in properties)
{
ColumnHeaderAttribute headerAttribute = property.GetCustomAttribute(typeof(ColumnHeaderAttribute)) as ColumnHeaderAttribute;
if (headerAttribute != null && headerAttribute.IsVisible) //filter invisible item
{
ColHeaderAttributeDict.Add(property, headerAttribute);
}
}
if (ColHeaderAttributeDict.Count < 1)
{
#if DEBUG
Console.WriteLine("InitWorksheet Failed: HeaderAttributes.Count is 0.");
#endif
return;
}
ColHeaderAttributeDict = ColHeaderAttributeDict.OrderBy(one => one.Value.Index).ToDictionary(one => one.Key, one => one.Value); // order by index
// Re-Set Index
for (int i = 0; i < ColHeaderAttributeDict.Keys.Count; i++)
{
var key = ColHeaderAttributeDict.Keys.ElementAt(i);
ColHeaderAttributeDict[key].Index = i;
}
_Worksheet.Columns = ColHeaderAttributeDict.Count;
RangePosition rangePosition = new RangePosition();
rangePosition.Cols = 1;
rangePosition.Row = 0;
rangePosition.Rows = _Worksheet.RowCount;
for (int i = 0; i < properties.Count(); i++)
{
PropertyInfo property = properties[i];
var attribute = property.GetCustomAttribute(typeof(FormatAttributeBase));
if (attribute == null)
{
continue;
}
IFormatArgs formatArgs = attribute as IFormatArgs;
if (formatArgs != null)
{
ColumnHeaderAttribute headerAttribute = (from key in ColHeaderAttributeDict.Keys
where key.Equals(property)
select ColHeaderAttributeDict[property]).FirstOrDefault();
if (headerAttribute != null && headerAttribute.IsVisible)
{
rangePosition.Col = headerAttribute.Index;
//not work correctly
switch (formatArgs.CellDataFormatFlag)
{
case CellDataFormatFlag.General:
break;
case CellDataFormatFlag.Number:
{
NumberFormatAttribute numberFormatAttribute = formatArgs as NumberFormatAttribute;
NumberDataFormatter.NumberFormatArgs numberFormatter = new NumberDataFormatter.NumberFormatArgs();
if (numberFormatAttribute.DecimalPlaces != short.MaxValue)
{
numberFormatter.DecimalPlaces = numberFormatAttribute.DecimalPlaces;
}
numberFormatter.NegativeStyle = numberFormatAttribute.NegativeStyle;
numberFormatter.UseSeparator = numberFormatAttribute.UseSeparator;
numberFormatter.CustomNegativePrefix = numberFormatAttribute.CustomNegativePrefix;
numberFormatter.CustomNegativePostfix = numberFormatAttribute.CustomNegativePostfix;
_Worksheet.SetRangeDataFormat(rangePosition, CellDataFormatFlag.Number, numberFormatter);
//_ReoGridControl.DoAction(new SetRangeDataFormatAction(rangePosition, CellDataFormatFlag.Number, numberFormatter));
break;
}
case CellDataFormatFlag.DateTime:
{
DateTimeFormatAttribute dateTimeFormatAttribute = formatArgs as DateTimeFormatAttribute;
DateTimeDataFormatter.DateTimeFormatArgs dateTimeFormatArgs = new DateTimeDataFormatter.DateTimeFormatArgs();
dateTimeFormatArgs.Format = dateTimeFormatAttribute.Format;
dateTimeFormatArgs.CultureName = dateTimeFormatAttribute.CultureName;
_Worksheet.SetRangeDataFormat(rangePosition, CellDataFormatFlag.DateTime, dateTimeFormatArgs);
break;
}
case CellDataFormatFlag.Percent:
break;
case CellDataFormatFlag.Currency:
break;
case CellDataFormatFlag.Text:
break;
case CellDataFormatFlag.Custom:
break;
default:
break;
}
}
}
}
_ColumnWidthList = new List <int>();
_RowHeightList = new List <int>();
for (int i = 0; i < ColHeaderAttributeDict.Count; i++)
{
ColumnHeaderAttribute headerAttribute = ColHeaderAttributeDict.ElementAt(i).Value;
if (string.IsNullOrEmpty(headerAttribute.Text))
{
_Worksheet.ColumnHeaders[i].Text = ColHeaderAttributeDict.ElementAt(i).Key.Name;
}
else
{
_Worksheet.ColumnHeaders[i].Text = headerAttribute.Text;
}
if (headerAttribute.Width <= 0)
{
_Worksheet.ColumnHeaders[i].IsAutoWidth = true;
}
else
{
_Worksheet.ColumnHeaders[i].Width = (ushort)headerAttribute.Width;
}
_Worksheet.ColumnHeaders[i].IsVisible = true;
_Worksheet.ColumnHeaders[i].Tag = ColHeaderAttributeDict.ElementAt(i).Key; // Tag stores PropertyInfo of Model
_ColumnWidthList.Add(_Worksheet.ColumnHeaders[i].Width); // store column width
}
//stroe row height
for (int i = 0; i < _Worksheet.Rows; i++)
{
_RowHeightList.Add(_Worksheet.RowHeaders[i].Height);
}
// load data
LoadRecords();
_Worksheet.BeforeCellEdit += Worksheet_BeforeCellEdit;
_Worksheet.CellDataChanged += Worksheet_CellDataChanged;
_Worksheet.RangeDataChanged += Worksheet_RangeDataChanged;
_Worksheet.AfterPaste += Worksheet_AfterPaste;
_Worksheet.RowsHeightChanged += Worksheet_RowsHeightChanged;
_Worksheet.ColumnsWidthChanged += Worksheet_ColumnsWidthChanged;
}