public ЭкраннаяФорма ИмпортироватьЭкраннуюФорму(string Файл)
{
ЭкраннаяФорма форма = new ЭкраннаяФорма();
this.sheetFamilyID = GridFormulaEngine.CreateSheetFamilyID();
ExcelEngine engine = new ExcelEngine();
IApplication excel = engine.Excel;
IWorkbook workbook = engine.Excel.Workbooks.Open(Файл);
foreach (IWorksheet worksheet in workbook.Worksheets)
{
if (worksheet.Name.Trim().ToLower() == "code")
{
continue;
}
if (worksheet.Name.Trim().ToLower() == "шапка")
{
ШапкаЭкраннойФормы формы = new ШапкаЭкраннойФормы();
GridFormulaEngine.RegisterGridAsSheet("sys_шапка", формы.Представление, this.sheetFamilyID);
base.ExcelToGrid(worksheet, формы.Представление);
формы.УстановитьСтилевыеНастройкиТаблицы();
this.ОбработатьЯчейкиТаблицы(формы);
форма.Шапка = формы;
}
else
{
this.ПолучитьТаблицыФормы(worksheet, форма);
}
}
return(форма);
}
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
engine = ((GridFormulaCellModel)this.gridCalculations.CellModels["FormulaCell"]).Engine;
this.GridSettings();
}
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
if (DpiAware.GetCurrentDpi() > 96)
{
this.CaptionBarHeight = (int)DpiAware.LogicalToDeviceUnits(this.CaptionBarHeight);
}
//
// TODO: Add any constructor code after InitializeComponent call
//
engine = ((GridFormulaCellModel)this.gridCalculations.CellModels["FormulaCell"]).Engine;
this.GridSettings();
}
protected void ПроинициализироватьФункцииАвтоблоков(ОтчетнаяФормаДанных ОтчетнаяФорма)
{
GridFormulaCellModel model = (GridFormulaCellModel)this.Представление.CellModels["FormulaCell"];
GridFormulaEngine engine = model.Engine;
base.DrawCellDisplayText += new GridDrawCellDisplayTextEventHandler(this.ТаблицаExcel_DrawCellDisplayText);
this.функцииАвтоблоков = new ФункцииАвтоблоков(ОтчетнаяФорма);
if (engine.LibraryFunctions.ContainsKey("СуммаПоСтолбцу".ToUpper()))
{
engine.RemoveFunction("СуммаПоСтолбцу".ToUpper());
}
if (engine.LibraryFunctions.ContainsKey("КоличествоСтрок".ToUpper()))
{
engine.RemoveFunction("КоличествоСтрок".ToUpper());
}
engine.AddFunction("СуммаПоСтолбцу", new GridFormulaEngine.LibraryFunction(this.функцииАвтоблоков.СуммаПоСтолбцу));
engine.AddFunction("КоличествоСтрок", new GridFormulaEngine.LibraryFunction(this.функцииАвтоблоков.КоличествоСтрок));
}
private void PopulateNamesHashtable()
{
GridFormulaEngine engine = ((GridCellFormulaModel)grid.Model.CellModels["FormulaCell"]).Engine;
this.formulas = engine.LibraryFunctions.Clone() as Hashtable;
this.formulas.Add("+ OPERATOR", "");
this.formulas.Add("- OPERATOR", "");
this.formulas.Add("* OPERATOR", "");
this.formulas.Add("/ OPERATOR", "");
this.formulas.Add("> OPERATOR", "");
this.formulas.Add("< OPERATOR", "");
this.formulas.Add(" = OPERATOR", "");
this.formulas.Add(">= OPERATOR", "");
this.formulas.Add("<= OPERATOR", "");
this.formulas.Add("<> OPERATOR", "");
this.formulas.Add("^ EXPONENT", "");
this.formulas.Add("- UNARY MINUS", "");
}
private string ExcelLikeFormula(GridModel model, string text)
{
var dataContext = AssociatedObject.DataContext as MainViewModel;
if (dataContext != null)
{
GridSheetFamilyItem f = GridFormulaEngine.GetSheetFamilyItem(model);
if (text.Contains('!'))
{
for (int i = 0; i < dataContext.Workbook.Worksheets.Count; i++)
{
string s = "'" + dataContext.Workbook.Worksheets[i].Name + "'!";
string sheetname = dataContext.Workbook.Worksheets[i].Name + '!';
if (text.Contains(sheetname))
{
text = text.Replace(sheetname, s);
}
}
}
}
return(text);
}
void FormulaSupportWithRangeSelectionsHelper_PreviewMouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
if (grid.IsMouseOver)
{
currentCell = grid.PointToCellRowColumnIndex(new MouseEventArgs(e.MouseDevice, e.Timestamp));
if (currentCell.RowIndex > 0 && currentCell.ColumnIndex > 0)
{
if (grid.CurrentCell.CellRowColumnIndex != currentCell)
{
if (grid.CurrentCell.IsEditing)
{
if (OkToPlaceText())
{
startRow = currentCell.RowIndex;
startCol = currentCell.ColumnIndex;
inRangeSelection = true;
usingMouse = true;
GridRangeInfo range = selectedRange;
selectedRange = GridRangeInfo.Cell(startRow, startCol);
PlaceTextInCell(false, selectedRange);
grid.InvalidateCell(selectedRange.UnionRange(range));
e.Handled = true;
}
else
{
try
{
if (grid.CurrentCell.Renderer.CellModel is GridCellFormulaModel)
{
GridFormulaEngine engine = ((GridCellFormulaModel)grid.CurrentCell.Renderer.CellModel).Engine;
//check for missing right paren
string s = grid.CurrentCell.Renderer.ControlText;
int loc = s.LastIndexOf('(');
if (loc > -1)
{
int loc1 = s.LastIndexOf(')');
if (loc1 < loc || loc1 == -1)
{
grid.CurrentCell.Renderer.ControlText = s + ")";
}
}
string message = "", val = "", parseString = "";
if (!engine.IsFormulaValid(grid.CurrentCell.Renderer.ControlText, out parseString, out message, out val))
{
MessageBox.Show(message);
e.Handled = true;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
e.Handled = true;
}
}
}
}
else
{
currentCellModified = grid.CurrentCell.IsEditing;
}
}
}
}
private List <ТаблицаОтчетнойФормы> ПолучитьТаблицыФормы(IWorksheet worksheet, ЭкраннаяФорма Форма)
{
ТаблицаОтчетнойФормы формы = new ТаблицаОтчетнойФормы();
GridFormulaEngine.RegisterGridAsSheet("sys_общаяТаблица", формы.Представление, this.sheetFamilyID);
base.ExcelToGrid(worksheet, формы.Представление);
bool flag = false;
int top = -1;
string str = string.Empty;
int length = worksheet.Rows.Length;
int right = worksheet.Columns.Length;
bool flag2 = true;
List <ТаблицаОтчетнойФормы> list = new List <ТаблицаОтчетнойФормы>();
for (int i = 1; i <= length; i++)
{
string text = worksheet[i, 1].Text;
if (string.IsNullOrEmpty(text))
{
if ((i == length) && (top != -1))
{
text = "#Конец_Закладки";
}
else
{
continue;
}
}
if (text.ToLower().StartsWith("#закладка"))
{
flag = false;
top = i;
str = text;
}
else if (text.ToLower().StartsWith("#динамическаятаблица"))
{
flag = true;
top = i;
str = text;
}
else if (text.ToLower().StartsWith("#конец_закладки") && (top != -1))
{
GridRangeInfo range = GridRangeInfo.Cells(top, 1, i, right);
GridStyleInfoStoreTable cells = формы.GetCells(range);
int rowCount = cells.RowCount;
int colCount = cells.ColCount;
ТаблицаОтчетнойФормы формы2 = new ТаблицаОтчетнойФормы();
формы2.RowCount = rowCount;
формы2.ColCount = colCount;
this.ИдентифицироватьЗакладу(str, формы2);
string str3 = str.Trim().ToLower();
if (flag2)
{
формы2.ИмяЛиста = worksheet.Name;
}
else
{
формы2.ИмяЛиста = формы2.КодТаблицы;
}
flag2 = false;
GridFormulaEngine.RegisterGridAsSheet(формы2.ИмяЛиста, формы2.Представление, this.sheetFamilyID);
GridRangeInfo info2 = GridRangeInfo.Cells(1, 1, rowCount, colCount);
формы2.SetCells(info2, cells);
for (int j = 0; j <= colCount; j++)
{
формы2.ColWidths[j] = формы.ColWidths[j];
}
foreach (GridRangeInfo info3 in формы.CoveredRanges.Ranges)
{
формы2.CoveredRanges.Add(GridRangeInfo.Cells((info3.Top - top) + 1, info3.Left, (info3.Bottom - top) + 1, info3.Right));
}
формы2.RowHeights.ResizeToFit(GridRangeInfo.Rows(0, rowCount));
формы2.RowHeights[0] = 0;
формы2.RowHeights[1] = 0;
формы2.RowHeights[rowCount] = 0;
if (!flag)
{
this.ПроанализироватьТаблицуНаПеременные(формы2);
this.ОбработатьЯчейкиТаблицы(формы2);
Форма.ДобавитьТаблицу(формы2);
}
else
{
string[] strArray = формы2.КодТаблицы.Split(new char[] { ',' });
bool flag3 = str3.Contains("размещатьназакладке");
foreach (string str4 in strArray)
{
ДинамическаяТаблица таблица = new ДинамическаяТаблица();
таблица.ПостроитьТаблицуПоМодели(формы2);
таблица.КодТаблицы = str4;
таблица.азмещатьНаЗакладке = flag3;
Форма.ДобавитьДинамическуюТаблицу(таблица);
}
}
top = -1;
}
}
GridFormulaEngine.UnregisterGridAsSheet("sys_общаяТаблица", формы.Представление);
return(list);
}
private void Form1_Load(object sender, System.EventArgs e)
{
//helper class to mark headers/row on a the calculation sheet
GridMarkHeadersHelper helper = new GridMarkHeadersHelper(this.gridCalculations);
helper.RegisterGridForContextMenu(this.gridDisplay);
this.helperList = new Hashtable();
this.helperList.Add(this.gridCalculations, helper);
//register grids for multisheeted use
int sheetFamilyID = GridFormulaEngine.CreateSheetFamilyID();
GridFormulaEngine.RegisterGridAsSheet("Display", this.gridDisplay.Model, sheetFamilyID);
GridFormulaEngine.RegisterGridAsSheet("Calc", this.gridCalculations.Model, sheetFamilyID);
SetUpDisplaySheet();
SetUpCalcSheet();
SetUpNamedRanges();
//setup copying formulas
GridFormulaEngine engine = ((GridFormulaCellModel)this.gridCalculations.CellModels["FormulaCell"]).Engine;
engine.FormulaCopyFlags |= GridFormulaCopyFlags.ClipBoardFormula;
//make sure calc grid has current cell
this.tabBarPage2.ActiveControl = this.gridCalculations;
this.gridCalculations.CurrentCell.MoveTo(1, 1);
//bring up form on grid in first tab
this.tabBarSplitterControl1.ActivePage = this.tabBarPage1;
this.tabBarPage1.ActiveControl = this.gridDisplay;
this.gridDisplay.CurrentCell.MoveTo(5, 5);
this.gridDisplay.Refresh();
//subscribe to TabBar events to handle splitters generating new grids
this.tabBarSplitterControl1.PaneCreated += new Syncfusion.Windows.Forms.SplitterPaneEventHandler(tabBar_PaneCreated);
this.tabBarSplitterControl1.PaneClosing += new Syncfusion.Windows.Forms.SplitterPaneEventHandler(tabBar_PaneClosing);
this.gridCalculations.Properties.GridLineColor = System.Drawing.Color.Silver;
this.gridCalculations.DefaultGridBorderStyle = GridBorderStyle.Solid;
this.gridCalculations.ThemesEnabled = true;
this.Text = "Retirement Simulation Demo";
this.gridCalculations.GridVisualStyles = Syncfusion.Windows.Forms.GridVisualStyles.Metro;
this.gridDisplay.GridVisualStyles = Syncfusion.Windows.Forms.GridVisualStyles.Metro;
this.gridDisplay.DefaultRowHeight = (int)DpiAware.LogicalToDeviceUnits(25.0f);
this.gridCalculations.DefaultColWidth = (int)DpiAware.LogicalToDeviceUnits(70.0f);
this.gridCalculations.DefaultGridBorderStyle = GridBorderStyle.Solid;
this.gridCalculations.ThemesEnabled = true;
this.gridCalculations.MarkColHeader = true;
this.gridCalculations.MarkRowHeader = true;
this.gridCalculations.AlphaBlendSelectionColor = Color.Orange;
this.gridCalculations.Properties.GridLineColor = Color.FromArgb(208, 215, 229);
this.gridDisplay.GridVisualStyles = Syncfusion.Windows.Forms.GridVisualStyles.Metro;
this.StartPosition = FormStartPosition.CenterScreen;
this.tabBarSplitterControl1.ThemesEnabled = true;
this.gridDisplay.PrepareViewStyleInfo += new GridPrepareViewStyleInfoEventHandler(gridDisplay_PrepareViewStyleInfo);
this.tabBarSplitterControl1.ActivePageChanged += new ControlEventHandler(tabBarSplitterControl1_ActivePageChanged);
this.tabBarSplitterControl1.ShowVerticalScrollBar = false;
}
private void namedRangeEditor_Click(object sender, EventArgs e)
{
GridFormulaEngine engine = ((GridFormulaCellModel)grid.Model.CellModels["FormulaCell"]).Engine;
GridFormulaNamedRangesEditerHelper.ShowNamedRangesDialog(engine);
}
/// <summary>
/// Constructor which allows editing an existing GridFormulaEngine.NamedRanges list.
/// </summary>
/// <param name="engine">The GridFormulaEngine object that holds the NamedRanges.</param>
public GridFormulaNamedRangesEditerHelper(GridFormulaEngine engine)
{
this.engine = engine;
this.namedRanges = engine.NamedRanges;
SetArrayList();
}
/// <summary>
/// Displays a collection editor dialog for editing NamedRanges.
/// </summary>
/// <param name="engine">The GridFormulaEngine instance whose NamedRanges are being edited.</param>
public static void ShowNamedRangesDialog(GridFormulaEngine engine)
{
GridFormulaNamedRangesEditerHelper editHelper = new GridFormulaNamedRangesEditerHelper(engine);
CollectionEditor editor1 = new CollectionEditor(typeof(NamedRangeList));
WindowsFormsEditorServiceContainer esc = new WindowsFormsEditorServiceContainer(null);
////Subscribe to the event to change Dialog settings.
esc.ShowingDialog += new ControlEventHandler(ServiceContainerShowingDialog);
PropertyDescriptor pd = TypeDescriptor.GetProperties(editHelper)["List"];
TypeDescriptorContext tdc = new TypeDescriptorContext(editHelper, pd);
tdc.ServiceProvider = esc;
GridFormulaNamedRangesEditerHelper.NamedRangeList oldList = pd.GetValue(editHelper) as GridFormulaNamedRangesEditerHelper.NamedRangeList;
GridFormulaNamedRangesEditerHelper.NamedRangeList newList = editor1.EditValue(tdc, esc, oldList) as GridFormulaNamedRangesEditerHelper.NamedRangeList;
esc.ShowingDialog -= new ControlEventHandler(ServiceContainerShowingDialog);
if (newList != null && esc.DialogResult == DialogResult.OK)
{
ArrayList needUpdating = new ArrayList();
ArrayList needDeleting = new ArrayList();
Hashtable namedRanges = (Hashtable)engine.NamedRanges.Clone();
List <string> lst = new List <string>();
foreach (string key in namedRanges.Keys)
{
lst.Add(key);
}
lst.Sort();
engine.NamedRanges.Clear();
engine.NamedRangesOriginalNames.Clear();
int i = 0;
foreach (NamedRange range in newList)
{
string s = range.Key.ToUpper(CultureInfo.InvariantCulture);
if (namedRanges.Contains(range.Key) && !string.IsNullOrEmpty(range.Key) && namedRanges[lst[i]].Equals(range.Value))
{
engine.AddNamedRange(range.Key, range.Value);
}
else if (namedRanges.Contains(range.Key) && !string.IsNullOrEmpty(range.Key))
{
engine.AddNamedRange(range.Key, namedRanges[lst[i]].ToString());
}
else if (!namedRanges.Contains(range.Key) && !string.IsNullOrEmpty(range.Key) && !namedRanges.ContainsKey(range.Key))
{
engine.AddNamedRange(range.Key, range.Value);
}
if (namedRanges.ContainsKey(s))
{
if (namedRanges[s].Equals(range.Value))
{
needUpdating.Add(s);
}
}
i++;
}
foreach (string key in namedRanges.Keys)
{
if (!engine.NamedRanges.ContainsKey(key))
{
needDeleting.Add(key);
}
}
if (needDeleting.Count > 0 || needUpdating.Count > 0)
{
foreach (string s in needUpdating)
{
engine.UpdateDependentNamedRangeCell(s);
////Console.WriteLine("changed: " + s);
}
foreach (string s in needDeleting)
{
if (engine.NamedRanges.ContainsKey(s))
{
engine.UpdateDependentNamedRangeCell(s);
engine.RemoveNamedRangeDependency(s);
////Console.WriteLine("deleted: " + s);
}
}
}
engine.AdjustNameRangesForSize();
}
}
