using Microsoft.Office.Excel.Server.WebServices; ………… /// <summary> /// Excel /// </summary> public List<CalculateControl> GetControlsInfo(List<CalculateControl> controls) { List<CalculateControl> controlInfoList = new List<CalculateControl>(); SessionId = ExcelService.OpenWorkbook(ExcelFileUrl, "ru-RU", "ru-RU", out status); string sheetName = SettingsPath.Split('!')[0]; string settingRange = SettingsPath.Split('!')[1]; // - , if (controls != null) Recalc(controls); /* , 3:8 * : * - , "" * - , ( Excel 2 ) */ int rowStart = int.Parse(settingRange.Split(':')[0].ToString().Substring(1)); int rowEnd = int.Parse(settingRange.Split(':')[1].ToString().Substring(1)); while (rowStart <= rowEnd) { string curRange = "A" + rowStart.ToString(); string cell = ExcelService.GetCellA1(SessionId, sheetName, curRange, false, out status).ToString(); if (cell == string.Empty) break; switch (cell) { case "DropDown": controlInfoList.Add(new CalculateControl { Type = CalculatorControlType.Dropdown, Name = GetCellValue(sheetName, "B", rowStart), DefaultVal = GetSingleVal(GetCellValue(sheetName, "G", rowStart)), Title = GetSingleVal(GetCellValue(sheetName, "C", rowStart)), ValueList = GetDropDownVal(GetSingleVal(GetCellValue(sheetName, "D", rowStart))), PutCell = GetCellValue(sheetName, "H", rowStart) }); break; case "Range": controlInfoList.Add(new CalculateControl { Type = CalculatorControlType.Range, Name = GetCellValue(sheetName, "B", rowStart), Title = GetCellValue(sheetName, "C", rowStart), DefaultVal = GetSingleVal(GetCellValue(sheetName, "G", rowStart)), MinVal = GetSingleVal(GetCellValue(sheetName, "E", rowStart)), MaxVal = GetSingleVal(GetCellValue(sheetName, "F", rowStart)), PutCell = GetCellValue(sheetName, "H", rowStart) }); break; case "SingleVal": controlInfoList.Add(new CalculateControl { Type = CalculatorControlType.SingleVal, Name = GetCellValue(sheetName, "B", rowStart), Title = GetCellValue(sheetName, "C", rowStart), DefaultVal = GetSingleVal(GetCellValue(sheetName, "G", rowStart)), }); break; } rowStart++; } ExcelService.CloseWorkbook(SessionId, out status); return controlInfoList; } private string GetCellValue(string sheetName, string colName, int rowIndex) { return GetCellValue(sheetName, string.Concat(colName, rowIndex)); } private string GetCellValue(string sheetName, string cell) { return ExcelService.GetCellA1(SessionId, sheetName, cell, false, out status).ToString(); } private List<string> GetDropDownVal(string rangeInf) { // – «'!B2:B4» // …………….. } private string GetSingleVal(string rangeInf) { var sheetName = rangeInf.Split('!')[0].ToString().Trim(); var cell = rangeInf.Split('!')[1].ToString().Trim(); return GetCellValue(sheetName, cell); } /// <summary> /// /// </summary> /// <param name="controls"></param> public void Recalc(List<CalculateControl> controls) { foreach (CalculateControl control in controls.Where(w => w.Type != CalculatorControlType.SingleVal)) { ExcelService.SetCellA1(SessionId, control.PutCell.Split('!')[0], control.PutCell.Split('!')[1], control.DefaultVal, out status); } ExcelService.CalculateWorkbook(SessionId, CalculateType.Recalculate, out status); }
/// <summary> /// - /// </summary> private void DrawCalculator() { pnlInput.Controls.Clear(); pnlOut.Controls.Clear(); foreach (CalculateControl control in controlsInfoList) { Label lbl = new Label {Text = control.Title }; if (control.Type.Equals(CalculatorControlType.Dropdown)) { DropDownList dd = new DropDownList { ClientIDMode = ClientIDMode.Static; EnableViewState = true; ID = control.Name; DataSource = control.ValueList; SelectedValue = control.DefaultVal; } dd.DataBind(); pnlInput.Controls.Add(lbl); pnlInput.Controls.Add(dd); pnlInput.Controls.Add(new HtmlGenericControl("br")); } if (control.Type.Equals(CalculatorControlType.Range)) { TextBox tbx = new TextBox { ClientIDMode = ClientIDMode.Static; EnableViewState = true; ID = control.Name; Text = control.DefaultVal; } pnlInput.Controls.Add(lbl); pnlInput.Controls.Add(tbx); pnlInput.Controls.Add(new HtmlGenericControl("br")); } if (control.Type.Equals(CalculatorControlType.SingleVal)) { HtmlGenericControl tbx = new HtmlGenericControl("input"); tbx.Attributes["id"] = control.Name; tbx.Attributes["Value"] = control.DefaultVal; pnlOut.Controls.Add(lbl); pnlOut.Controls.Add(new HtmlGenericControl("br")); pnlOut.Controls.Add(tbx); pnlOut.Controls.Add(new HtmlGenericControl("br")); } } }
Source: https://habr.com/ru/post/188338/