private void btnAccept_Click(object sender, EventArgs e) { int cantRows = 0; if (txtCantIndices.Text.Trim() != string.Empty) { cantRows = Convert.ToInt32(txtCantIndices.Text); Excel.Worksheet NewActiveWorksheet = Globals.ThisAddIn.Application.ActiveSheet; if ((cantRows > 0) && (cantRows <= NewActiveWorksheet.Rows.Count)) { Excel.Range currentCell = (Excel.Range)Globals.ThisAddIn.Application.ActiveCell.Cells; NewActiveWorksheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel); Generales.InsertIndice(NewActiveWorksheet, cantRows, currentCell, ConFormula, NroPrincipal); NewActiveWorksheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false); this.Close(); } else { MessageBox.Show("Especifique por favor un dato válido.", "Agregar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } else { MessageBox.Show("Especifique por favor la cantidad de índices a insertar.", "Agregar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
private void ExelOpenButton_Click(object sender, EventArgs e) { excelapp = new Microsoft.Office.Interop.Excel.Application(); excelapp.SheetsInNewWorkbook = 1; excelworkbook = excelapp.Workbooks.Add(Type.Missing); excelworksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkbook.Sheets[1]; excelworksheet.Cells.Font.Size = 10; excelworksheet.Range[excelworksheet.Cells[1, 1], excelworksheet.Cells[9, 8]].Font.Size = 12; excelworksheet.Cells.ColumnWidth = 10; excelworksheet.Range[excelworksheet.Cells[1, 1], excelworksheet.Cells[1, 2]].Merge(); excelworksheet.Range[excelworksheet.Cells[1, 3], excelworksheet.Cells[1, 6]].Merge(); excelworksheet.Range[excelworksheet.Cells[1, 7], excelworksheet.Cells[1, 8]].Merge(); for (int i = 2; i <= 9; i++) { excelworksheet.Range[excelworksheet.Cells[i, 1], excelworksheet.Cells[i, 2]].Merge(); excelworksheet.Range[excelworksheet.Cells[i, 3], excelworksheet.Cells[i, 8]].Merge(); } excelworksheet.Cells[1, 1] = "Клиент:"; excelworksheet.Cells[2, 1] = "Пол:"; excelworksheet.Cells[3, 1] = "Возраст:"; excelworksheet.Cells[4, 1] = "Зарегестрирован:"; excelworksheet.Cells[5, 1] = "Паспорт:"; excelworksheet.Cells[6, 1] = "Права до:"; excelworksheet.Cells[7, 1] = "Задолженность:"; excelworksheet.Cells[9, 1] = "Список заказов:"; excelworksheet.Range[excelworksheet.Cells[10, 1], excelworksheet.Cells[10, 2]].Merge(); excelworksheet.Cells[10, 1] = "Автомобиль"; excelworksheet.Range[excelworksheet.Cells[10, 3], excelworksheet.Cells[10, 4]].Merge(); excelworksheet.Range[excelworksheet.Cells[10, 5], excelworksheet.Cells[11, 5]].Merge(); excelworksheet.Cells[10, 5] = "Кол-во дней опоздания возврата"; excelworksheet.Cells[10, 3] = "Даты"; excelworksheet.Range[excelworksheet.Cells[10, 6], excelworksheet.Cells[10, 8]].Merge(); excelworksheet.Cells[10, 6] = "Сумма"; excelworksheet.Cells[11, 1] = "Инв. №"; excelworksheet.Cells[11, 2] = "Модель"; excelworksheet.Cells[11, 3] = "выдачи"; excelworksheet.Cells[11, 4] = "возврата"; excelworksheet.Cells[11, 6] = "проката"; excelworksheet.Cells[11, 7] = "пени"; excelworksheet.Cells[11, 8] = "ИТОГО"; excelworksheet.Cells.VerticalAlignment = true; excelworksheet.Cells.WrapText = true; excelworksheet.Range[excelworksheet.Cells[11, 1], excelworksheet.Cells[11, 8]].RowHeight = excelworksheet.Cells[10, 1].RowHeight * 2; excelworksheet.Range[excelworksheet.Cells[1, 1], excelworksheet.Cells[9, 8]].Borders.Color = XlRgbColor.rgbWhite; excelworksheet.Range[excelworksheet.Cells[1, 1], excelworksheet.Cells[9, 8]].Borders.Item[XlBordersIndex.xlEdgeRight].Color = XlRgbColor.rgbBlack; excelworksheet.Range[excelworksheet.Cells[10, 1], excelworksheet.Cells[11, 8]].Font.Bold = true; excelworksheet.Range[excelworksheet.Cells[10, 1], excelworksheet.Cells[11, 8]].Borders.LineStyle = XlLineStyle.xlContinuous; excelworksheet.Cells[1, 3].Font.Bold = true; excelworksheet.Cells[1, 7].HorizontalAlignment = XlHAlign.xlHAlignCenter; excelworksheet.Cells[1, 7].Borders.Color = XlRgbColor.rgbBlack; excelworksheet.Range[excelworksheet.Cells[1, 7], excelworksheet.Cells[1, 8]].Borders.LineStyle = XlLineStyle.xlContinuous; setExcelTableCells(); excelworksheet.Protect(); excelapp.Visible = true; }
public static void Append(RequestPackage rqp) { try { Guid guid = Guid.NewGuid(); if (!String.IsNullOrWhiteSpace(rqp["f0"] as String)) { Guid.TryParse((rqp["f0"] as String), out guid); } String f0 = System.Convert.ToBase64String(guid.ToByteArray()).Substring(0, 22); XlConnection.Open(); Excel.Worksheet sheet = XlConnection.Sheet; // ищем первую пустую строку начиная с третьей int ri; for (ri = 3; ri < 10000; ri++) { if (sheet.Cells[ri, 1].Value == null) { break; } } if (ri < 10000) { // добавляем ячейки Excel.Range row = sheet.Range[sheet.Cells[ri, 1], sheet.Cells[ri, 19]]; sheet.Unprotect(); XlConnection.App.EnableEvents = false; row[1, 1] = f0; XlConnection.App.EnableEvents = true; sheet.Protect(); row[1, 4] = rqp["f3"] as String; //row[1, 5] = rqp["f4"] as String; // сама обновится через макрос Exel row[1, 6] = rqp["f5"] as String; row[1, 7] = rqp["f6"] as String; row[1, 8] = rqp["f7"] as String; row[1, 9] = XlConvert.ToXlDatetime(rqp["f8"] as String); row[1, 10] = XlConvert.ToXlDatetime(rqp["f9"] as String); row[1, 11] = XlConvert.ToXlDatetime(rqp["f10"] as String); row[1, 12] = XlConvert.ToXlDatetime(rqp["f11"] as String); row[1, 13] = rqp["f12"] as String; row[1, 14] = rqp["f13"] as String; row[1, 15] = rqp["f14"] as String; row[1, 16] = XlConvert.ToXlFloat(rqp["f15"] as String); row[1, 17] = XlConvert.ToXlInt(rqp["f16"] as String); row[1, 18] = rqp["f17"] as String; row[1, 19] = rqp["f18"] as String; XlConnection.App.DisplayAlerts = false; XlConnection.Book.Save(); } } catch (Exception ex) { Log.Write("XlStoredProcedure.Append(): " + ex.ToString()); } finally { XlConnection.Close(); } }
private void barBtnSave_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { WaitDialogForm wait = null; wait = new WaitDialogForm("", "正在保存数据, 请稍候..."); //判断文件夹xls是否存在,不存在则创建 if (!Directory.Exists(System.Windows.Forms.Application.StartupPath + "\\xls")) { Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + "\\xls"); } try { //保存excel文件 fpSpread1.SaveExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\PDWXZ" + year + ".xls"); //以下是打开文件设表格自动换行 // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); string filename = System.Windows.Forms.Application.StartupPath + "\\xls\\PDWXZ" + year + ".xls"; workBook = excelApp.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //取消保护工作表 ws.Unprotect(Missing.Value); //有数据的行数 int row = ws.UsedRange.Rows.Count; //有数据的列数 int col = ws.UsedRange.Columns.Count; //创建一个区域 range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); //设区域内的单元格自动换行 range.WrapText = true; //保护工作表 ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //保存工作簿 workBook.Save(); //关闭工作簿 excelApp.Workbooks.Close(); wait.Close(); MsgBox.Show("保存成功"); } catch (System.Exception ee) { wait.Close(); MsgBox.Show("保存错误!确定您安装有Office Excel,或者关闭所有Excel文件重试"); } }
public static void CleanPassword(this Excel.Worksheet sheet) { #region 一个小功能 sheet.Protect ( DrawingObjects: Office.MsoTriState.msoTrue, Contents: Office.MsoTriState.msoTrue, Scenarios: Office.MsoTriState.msoTrue, AllowFiltering: Office.MsoTriState.msoTrue, AllowUsingPivotTables: Office.MsoTriState.msoTrue ); sheet.Protect ( DrawingObjects: Office.MsoTriState.msoTrue, Contents: Office.MsoTriState.msoTrue, Scenarios: Office.MsoTriState.msoTrue, AllowFiltering: Office.MsoTriState.msoTrue, AllowUsingPivotTables: Office.MsoTriState.msoTrue ); sheet.Protect ( DrawingObjects: Office.MsoTriState.msoTrue, Contents: Office.MsoTriState.msoTrue, Scenarios: Office.MsoTriState.msoTrue, AllowFiltering: Office.MsoTriState.msoTrue, AllowUsingPivotTables: Office.MsoTriState.msoTrue ); sheet.Protect ( DrawingObjects: Office.MsoTriState.msoTrue, Contents: Office.MsoTriState.msoTrue, Scenarios: Office.MsoTriState.msoTrue, AllowFiltering: Office.MsoTriState.msoTrue, AllowUsingPivotTables: Office.MsoTriState.msoTrue ); sheet.Unprotect(); #endregion }
private void btnAccept_Click(object sender, EventArgs e) { string Mensaje = string.Empty; Excel.Range currentCell = (Excel.Range)Globals.ThisAddIn.Application.ActiveCell.Cells; Excel.Worksheet NewActiveWorksheet = Globals.ThisAddIn.Application.ActiveSheet; if (TxtExplicacion.Text.Trim() == string.Empty) { MessageBox.Show("Especifique por favor la explicación.", "Explicación índice", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { if (TxtExplicacion.Text.Length < 100) { Mensaje = "La explicación especificada tiene " + lblcontador.Text + " caracteres, debe contener al menos 100. ¿Desea continuar ? "; DialogResult dialogo = MessageBox.Show(Mensaje, "Explicación índice", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (dialogo == DialogResult.Yes) { NewActiveWorksheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel); Generales.InsertaExplicacion(NewActiveWorksheet, currentCell, TxtExplicacion.Text); NewActiveWorksheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false); this.Close(); } } else { if (TxtExplicacion.Text.Length >= 100) { NewActiveWorksheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel); Generales.InsertaExplicacion(NewActiveWorksheet, currentCell, TxtExplicacion.Text); NewActiveWorksheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false); this.Close(); } } } }
private void GenerateSheet(Excel.Worksheet sheet, string sheetName) { sheet.Name = sheetName; sheet.Cells.Locked = false; var columns = definition.Worksheets[EnumUtils.EnumValue <ConfigurationFile.WorkSheets>(sheetName)].Columns; foreach (var column in columns) { if (EnumUtils.IsOptional(column.TargetField)) { sheet.Cells[1, column.Position] = "(Optional)"; sheet.Columns[column.Position].Style = "Optional"; } else { sheet.Cells[1, column.Position] = "(Required)"; } sheet.Cells[1, column.Position].Style = "HeadingDetail"; sheet.Cells[2, column.Position] = column.Heading; sheet.Cells[2, column.Position].Style = "HeadingTitle"; var defaultValue = EnumUtils.GetDefault(column.TargetField); if (defaultValue != null) { sheet.Cells[3, column.Position] = $"(Default: {defaultValue.ToString()})"; } sheet.Cells[3, column.Position].Style = "HeadingDetail"; var options = EnumUtils.GetOptions(column.TargetField); if (options.Count() > 0) { var cell = (Excel.Range)sheet.Columns[column.Position]; cell.Validation.Add( Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, string.Join(", ", options)); } } sheet.Columns.AutoFit(); sheet.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); // Freeze the header rows. sheet.Activate(); sheet.Application.ActiveWindow.SplitRow = 3; sheet.Application.ActiveWindow.FreezePanes = true; }
public void ProtectSheet(ExcelInterop.Worksheet concernedSheet) { if (concernedSheet != null && !concernedSheet.ProtectContents) { concernedSheet.Cells.Locked = false; concernedSheet.Protect(Type.Missing, false, false, Type.Missing, false, true, true, true, false, false, false, false, false, false, true, true); } }
/// <summary> /// 保护工作表. /// </summary> /// <param name="sheetName"> 工作表名 </param> /// <param name="password"> 密码 </param> public void Protect( string sheetName, string password = "") { // 选择源工作表. Excel.Worksheet mySheet = (Excel.Worksheet)xlBook.Sheets.get_Item(sheetName); mySheet.Protect( Password: password, DrawingObjects: true, Contents: true, Scenarios: true); }
public static void RefreshLog() { Excel.Worksheet log = _wb.Sheets["Log"]; bool prot = log.ProtectContents; if (prot) { log.Unprotect(Password); } DataBase db = new DataBase(); db.RefreshLog(); if (prot) { log.Protect(Password); } }
public static void InsertLog(Core.DataBase.TipologiaLOG logType, string message) { Excel.Worksheet log = _wb.Sheets["Log"]; bool prot = log.ProtectContents; if (prot) { log.Unprotect(Password); } DataBase db = new DataBase(); db.InsertLog(logType, message); if (prot) { log.Protect(Password); } }
private void btnEliminaeExplicacion_Click(object sender, RibbonControlEventArgs e) { Excel.Range currentCell = (Excel.Range)Globals.ThisAddIn.Application.ActiveCell; int NroRow = currentCell.Row; Excel.Worksheet NewActiveWorksheet = Globals.ThisAddIn.Application.ActiveSheet; currentCell = (Excel.Range)NewActiveWorksheet.Cells[NroRow, 1]; string indice = currentCell.Value2; if (indice.ToUpper().Trim() == "EXPLICACION") { NewActiveWorksheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel); currentCell.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); //ref string NombreHoja = NewActiveWorksheet.Name.ToUpper().Replace(" ", ""); List <oSubtotal> ColumnasST = Generales.DameColumnasST(NombreHoja); int _Registro = 1; Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook; string _NameFile = wb.Name; int row = Generales.DameRangoPrincipal(NroRow, NewActiveWorksheet); foreach (oSubtotal ST in ColumnasST) { if (_Registro == 1) { _Registro += 1; Generales.ActualizarReferencia(_NameFile, NewActiveWorksheet.Name.ToUpper(), ST.Columna + row.ToString(), 0, ST.Columna, row.ToString(), 1, "E"); } } //ref NewActiveWorksheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false); } else { MessageBox.Show("La fila seleccionada no es una explicación ", "Eliminar Explicación", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
void activeSheet_SelectionChange(object sh, Excel.Range target) { _sheet1 = (Excel.Worksheet)sh; if (target.Row != 1 && (_objeto.FirstOrDefault(x => x.Nombre == _sheet1.Name) != null)) { try { _sheet1.Unprotect(); Globals.ThisAddIn.Application.Cells.Locked = false; //BloquearRango(_rowCount); _sheet1.Protect(AllowSorting: true, AllowFiltering: true); } catch (Exception e) { Console.WriteLine(e.Message); } } else { _sheet1.Unprotect(); } }
/// <summary>Función para Proteger y Desproteger las hojas de un archivo de Excel. /// <para>Desprotege y Protege un archivo de Excel. Referencia: <see cref="Proteccion(bool)"/> se agrega la referencia ExcelAddIn.Generales para invocarla.</para> /// <seealso cref="Proteccion(bool)"/> /// </summary> public static void Proteccion(bool accion) { int f; FileInfo _Excel = new FileInfo(Globals.ThisAddIn.Application.ActiveWorkbook.FullName); Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook; using (ExcelPackage _package = new ExcelPackage(_Excel)) { for (f = 1; f <= _package.Workbook.Worksheets.Count(); f++) { Excel.Worksheet xlSht = wb.Worksheets[f]; if (!accion) { xlSht.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel); } else { xlSht.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false); } } } }
private void createConceptList() { Excel.Worksheet origin = (Excel.Worksheet)application.ActiveSheet; conceptList = (Excel.Worksheet)application.ActiveWorkbook.Worksheets.Add(Type.Missing, application.ActiveSheet, Type.Missing, Type.Missing); conceptList.Name = "concept_list"; Excel.Range column = ((Excel.Range)conceptList.Cells[1, 1]); //ID column.Value2 = "ID"; column.Font.Bold = true; column.Font.Background = Excel.XlBackground.xlBackgroundOpaque; column.EntireColumn.ColumnWidth = 25; column.EntireColumn.WrapText = true; column.EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; //Name column.Next.Value2 = "Name"; column.Next.Font.Bold = true; column.Next.Font.Background = Excel.XlBackground.xlBackgroundOpaque; column.Next.EntireColumn.ColumnWidth = 30; column.Next.EntireColumn.WrapText = true; column.Next.EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; //Definition column.Next.Next.Value2 = "Definition"; column.Next.Next.Font.Bold = true; column.Next.Next.Font.Background = Excel.XlBackground.xlBackgroundOpaque; column.Next.Next.EntireColumn.ColumnWidth = 60; column.Next.Next.EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; column.Next.Next.EntireColumn.WrapText = true; column.Next.Next.EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignJustify; //Source column.Next.Next.Next.Value2 = "Source"; column.Next.Next.Next.Font.Bold = true; column.Next.Next.Next.Font.Background = Excel.XlBackground.xlBackgroundOpaque; column.Next.Next.Next.EntireColumn.ColumnWidth = 30; column.Next.Next.Next.EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; column.Next.Next.Next.EntireColumn.WrapText = true; column.Next.Next.Next.EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignJustify; //Definition column.Next.Next.Next.Next.Value2 = "Mapped Cells"; column.Next.Next.Next.Next.Font.Bold = true; column.Next.Next.Next.Next.Font.Background = Excel.XlBackground.xlBackgroundOpaque; column.Next.Next.Next.Next.EntireColumn.ColumnWidth = 60; column.Next.Next.Next.Next.EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; column.Next.Next.Next.Next.EntireColumn.WrapText = true; column.Next.Next.Next.Next.EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignJustify; conceptList.Protect(dummyPass, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //((Excel.Worksheet)application.Worksheets.get_Item(origin.Name)).Activate(); (origin as Microsoft.Office.Interop.Excel._Worksheet).Activate(); }
protected void handleProp(XElement selectedNode) { string id = selectedNode.Element(rs + "names").Element(rs + "id").Value; //Removing the institution identifying prefix from CADSR elements on client request if (id.Contains("-CADSR-")) { string[] idarr = id.Split('-'); id = idarr[idarr.Length - 2] + " v." + idarr[idarr.Length - 1]; } string preferredName = selectedNode.Element(rs + "names").Element(rs + "preferred").Value; string definition = selectedNode.Element(rs + "definition").Value; if (definition == null || definition.Length == 0) { definition = "(No definition supplied)"; } else { //Handle special caDSR/EVS format definition = definition.Trim().Replace(">", ">").Replace("<", "<").Replace("<![CDATA[", "").Replace("]]>", ""); if (definition.Contains("<def-source>")) { XElement e = XElement.Parse("<def>" + definition + "</def>"); definition = e.Element("def-definition").Value + "\n(Source: " + e.Element("def-source").Value + ")"; } } string label = preferredName; //Get selected range Excel.Range selected = (Excel.Range)application.Selection; if (selected.Value2 == null || selected.Value2.ToString().Length == 0) { selected.Value2 = label; } else { //Refuse to add? Do you have to remove first? } //Create concept list if not exists propList = useList(propList, "prop_list"); //Add new concept entry to concept_list propList.Unprotect(dummyPass); Excel.Range c = (Excel.Range)propList.Cells[2, 1]; Excel.Range found = propList.Cells.Find(id, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing); if (found == null) //if (existingIndex == 0) { for (int i = 3; c.Value2 != null; i++) { c = (Excel.Range)ocList.Cells[i, 1]; } c.Value2 = id; c.Next.Value2 = preferredName; c.Next.Next.Value2 = definition.Trim().Replace(">", ">").Replace("<", "<").Replace("&", "&"); //c.Next.Next.Next.Value2 = attr.Trim().Replace(",", "\n\n").Replace(",", ", ").Replace(">", ">").Replace("<", "<").Replace("&", "&"); //Cells mapped counter c.Next.Next.Next.Next.Value2 = 1; } else { found.Next.Next.Next.Next.Value2 = 1 + Convert.ToInt16(found.Next.Next.Next.Next.Value2.ToString()); } propList.Protect(dummyPass, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); /* Remove hyperlinks for now until a suitable two way link method is found */ selected.Hyperlinks.Add(selected, "", getSelectedRangeAddress(c), Type.Missing, label); selected.Font.Bold = true; selected.Font.Underline = false; selected.Font.ColorIndex = 1; selected.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent3; selected.Interior.TintAndShade = 0.2; }
/// <summary> /// Export Attendance /// </summary> private void ExportAttendance() { try { Excel1.Application myExcelApp; Excel1.Workbooks myExcelWorkbooks; Excel1.Workbook myExcelWorkbook; object misValue = System.Reflection.Missing.Value; myExcelApp = new Excel1.Application(); myExcelApp.Visible = false; myExcelWorkbooks = myExcelApp.Workbooks; string fileName = HttpContext.Current.Server.MapPath(".") + "\\Templates\\AttendanceExport.xlsx"; myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); Excel1.Worksheet xlSheet = (Excel1.Worksheet)myExcelWorkbook.Sheets[1]; EmpBL objEmp = new EmpBL(); objEmp.Date = DateTime.ParseExact(txtDate.Text.Trim().ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture); objEmp.WorkLocation = Session["Location"].ToString(); objEmp.ReportType = rblType.SelectedItem.Value; DataTable dtEmp = objEmp.GetAttendanceLineByDate(); if (dtEmp.Rows.Count > 0) { WriteToExcelAttendance(dtEmp, xlSheet); // ExportToPdf(dtEmp); string Password = "******"; xlSheet.Protect(Password, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); Random rnd = new Random(); string filePath = Server.MapPath(".") + "\\Reports\\Attendance_" + DateTime.Now.Day + "-" + DateTime.Now.Month + "-" + DateTime.Now.Year + "_" + rnd.Next() + ".xlsx"; ViewState["FileNameAtt"] = filePath; myExcelWorkbook.SaveAs(@filePath); myExcelWorkbook.Close(); myExcelWorkbooks.Close(); btnDownload.Visible = true; lblMessage.Text = "Successfully Exported"; lblMessage.ForeColor = System.Drawing.Color.Green; } else { lblMessage.Text = "No Data Found"; } } catch (Exception ex) { lblMessage.Text = ex.ToString(); } }
/// <summary>Función para obtener la contraseña de la hoja activa de Excel. /// <para>Obtiene la contraseña de la hoja activa de Excel para desbloquearla. Referencia: <see cref="_Macro(bool, Excel.Worksheet, string)"/> se agrega la referencia ExcelAddIn.Generales para invocarla.</para> /// <seealso cref="_Macro(bool, Excel.Worksheet, string)"/> /// </summary> public static void _Macro(bool accion, Excel.Worksheet xlSht, string PwsExcel) { int a, b, c, d, e, f, a1, a2, a3, a4, a5, a6; string _Contrasena = PwsExcel; try { if (!accion) { xlSht.Unprotect(_Contrasena); if (xlSht.ProtectContents == false) { //Byte[] _data = ASCIIEncoding.ASCII.GetBytes(_Contrasena); //ConfigurationManager.AppSettings["VAL7"] = Convert.ToBase64String(_data); return; } } else { xlSht.Protect(_Contrasena, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false); if (xlSht.ProtectContents == true) { return; } } } catch (Exception ex) { for (a = 65; a <= 66; a++) { for (b = 65; b <= 66; b++) { for (c = 65; c <= 66; c++) { for (d = 65; d <= 66; d++) { for (e = 65; e <= 66; e++) { for (a1 = 65; a1 <= 66; a1++) { for (a2 = 65; a2 <= 66; a2++) { for (a3 = 65; a3 <= 66; a3++) { for (a4 = 65; a4 <= 66; a4++) { for (a5 = 65; a5 <= 66; a5++) { for (a6 = 65; a6 <= 66; a6++) { for (f = 32; f <= 126; f++) { _Contrasena = ""; _Contrasena += Convert.ToChar(a); _Contrasena += Convert.ToChar(b); _Contrasena += Convert.ToChar(c); _Contrasena += Convert.ToChar(d); _Contrasena += Convert.ToChar(e); _Contrasena += Convert.ToChar(a1); _Contrasena += Convert.ToChar(a2); _Contrasena += Convert.ToChar(a3); _Contrasena += Convert.ToChar(a4); _Contrasena += Convert.ToChar(a5); _Contrasena += Convert.ToChar(a6); _Contrasena += Convert.ToChar(f); try { if (!accion) { xlSht.Unprotect(_Contrasena); if (xlSht.ProtectContents == false) { Byte[] _data = ASCIIEncoding.ASCII.GetBytes(_Contrasena); ConfigurationManager.AppSettings["VAL7"] = Convert.ToBase64String(_data); //Byte[] _data = ASCIIEncoding.ASCII.GetBytes(_Contrasena); //Configuration configServ = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); //configServ.AppSettings.Settings["VAL7"].Value = Convert.ToBase64String(_data); //configServ.Save(ConfigurationSaveMode.Modified); return; } } else { xlSht.Protect(_Contrasena, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false); if (xlSht.ProtectContents == true) { Byte[] _data = ASCIIEncoding.ASCII.GetBytes(_Contrasena); ConfigurationManager.AppSettings["VAL7"] = Convert.ToBase64String(_data); //Byte[] _data = ASCIIEncoding.ASCII.GetBytes(_Contrasena); //Configuration configServ = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); //configServ.AppSettings.Settings["VAL7"].Value = Convert.ToBase64String(_data); //configServ.Save(ConfigurationSaveMode.Modified); return; } } } catch (Exception exc) { } } } } } } } } } } } } } return; } }
protected void handleProp(QueryServiceControl.QueryServiceManager.property property) { string id = property.names.id; //Removing the institution identifying prefix from CADSR elements on client request if (id.Contains("-CADSR-")) { string[] idarr = id.Split('-'); id = idarr[idarr.Length - 2] + " v." + idarr[idarr.Length - 1]; } string preferredName = property.names.preferred; string definition = QueryServiceControl.QueryServiceControl.getDefinition(property.definition); string label = preferredName; //Get selected range Excel.Range selected = (Excel.Range)application.Selection; if (selected.Value2 == null || selected.Value2.ToString().Length == 0) { selected.Value2 = label; } else { //Refuse to add? Do you have to remove first? } //Create concept list if not exists propList = useList(propList, "prop_list"); //Add new concept entry to concept_list propList.Unprotect(dummyPass); Excel.Range c = (Excel.Range)propList.Cells[2, 1]; Excel.Range found = propList.Cells.Find(id, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing); if (found == null) //if (existingIndex == 0) { for (int i = 3; c.Value2 != null; i++) { c = (Excel.Range)ocList.Cells[i, 1]; } c.Value2 = id; c.Next.Value2 = preferredName; c.Next.Next.Value2 = definition.Trim().Replace(">", ">").Replace("<", "<").Replace("&", "&"); //c.Next.Next.Next.Value2 = attr.Trim().Replace(",", "\n\n").Replace(",", ", ").Replace(">", ">").Replace("<", "<").Replace("&", "&"); //Cells mapped counter c.Next.Next.Next.Next.Value2 = 1; } else { found.Next.Next.Next.Next.Value2 = 1 + Convert.ToInt16(found.Next.Next.Next.Next.Value2.ToString()); } propList.Protect(dummyPass, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); /* Remove hyperlinks for now until a suitable two way link method is found */ selected.Hyperlinks.Add(selected, "", getSelectedRangeAddress(c), Type.Missing, label); selected.Font.Bold = true; selected.Font.Underline = false; selected.Font.ColorIndex = 1; selected.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent3; selected.Interior.TintAndShade = 0.2; }
public void Update_Data() { if (!File.Exists(Report_File_Name)) { return; } try { Read_Data(Report_File_Name); int i = 0; string kStr = ""; int indx = 0; #region Update_ExcelData Excel.Application myExcelApp; Excel.Workbooks myExcelWorkbooks; Excel.Workbook myExcelWorkbook; object misValue = System.Reflection.Missing.Value; myExcelApp = new Excel.ApplicationClass(); myExcelApp.Visible = true; //myExcelApp.Visible = false; myExcelWorkbooks = myExcelApp.Workbooks; String fileName = Excel_File_Name; // set this to your file you want //myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); myExcelWorkbook = myExcelWorkbooks.Open(fileName, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet; Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["LL-OG1 & OG2"]; String cellFormulaAsString = myExcelWorksheet.get_Range("B12", misValue).Formula.ToString(); // this puts the formula in Cell A2 or text depending whats in it in the string. //int cel_index = 30; //int cel_index = 32; int cel_index = 26; int ci = (int)('B'); char c = (char)ci; //Excel_User_Input_Data ex_uip = new Excel_User_Input_Data(); double L = MyList.StringToDouble(PSC_Girder_User_Inputs[0].Input_Value, 0.0); double ang = MyList.StringToDouble(PSC_Girder_User_Inputs[5].Input_Value, 0.0); PSC_Girder_User_Inputs[0].Input_Value = (L / Math.Cos(ang * Math.PI / 180.0)).ToString("f2"); #region Write Live Load Forces List <int> lst_title_cell = new List <int>(); lst_title_cell.Add(8); lst_title_cell.Add(26); lst_title_cell.Add(46); lst_title_cell.Add(65); lst_title_cell.Add(84); lst_title_cell.Add(103); lst_title_cell.Add(122); lst_title_cell.Add(141); lst_title_cell.Add(160); lst_title_cell.Add(179); lst_title_cell.Add(198); lst_title_cell.Add(217); lst_title_cell.Add(236); int r = 0, cl = 0; //for (i = 12; i <= 274; i++) //for (i = 8; i <= 252; i++) for (i = 8; i <= 274; i++) { if (lst_title_cell.Contains(i) && this.Count > r) { myExcelWorksheet.get_Range(("A" + i.ToString()), misValue).Formula = this[r].Title; continue; } if ((i >= 8 && i <= 11) || (i >= 17 && i <= 19) || (i >= 25 && i <= 29) || (i >= 35 && i <= 37) || (i >= 43 && i <= 49) || (i >= 55 && i <= 57) || (i >= 63 && i <= 68) || (i >= 74 && i <= 76) || (i >= 82 && i <= 87) || (i >= 93 && i <= 95) || (i >= 101 && i <= 106) || (i >= 112 && i <= 114) || (i >= 120 && i <= 125) || (i >= 131 && i <= 133) || (i >= 139 && i <= 144) || (i >= 150 && i <= 152) || (i >= 158 && i <= 163) || (i >= 169 && i <= 171) || (i >= 177 && i <= 182) || (i >= 188 && i <= 190) || (i >= 196 && i <= 201) || (i >= 207 && i <= 209) || (i >= 215 && i <= 220) || (i >= 226 && i <= 228) || (i >= 234 && i <= 239) || (i >= 245 && i <= 247) || (i >= 253 && i <= 261) || (i >= 267 && i <= 269) ) { //myExcelWorksheet.get_Range(("A" + i.ToString()), misValue).EntireRow.Hidden = true; continue; } else { try { if (i > 269) { r = this.Count - 1; } else if (i > 261) { r = this.Count - 2; } if (i > 269) { r = this.Count - 1; } myExcelWorksheet.get_Range(("B" + i.ToString()), misValue).Formula = this[r].Bending_Moments[cl].ToString(); myExcelWorksheet.get_Range(("F" + i.ToString()), misValue).Formula = this[r].Shear_Forces[cl++].ToString(); if (cl == 5) { r++; cl = 0; } } catch (Exception ex) { //myExcelWorksheet.get_Range(("A" + i.ToString()), misValue).EntireRow.Hidden = false; //myExcelWorksheet.get_Range(("A" + i.ToString()), misValue).EntireRow.Hidden = true; } } } #endregion DL_Self_Weight #region User Inputs if (PSC_Girder_User_Inputs != null) { myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Input"]; List <int> lst_F = new List <int>(); int cnt = 0; for (i = 4; i <= 71; i++) { if ((i >= 5 && i <= 7) || i == 12 || i == 19 || i == 24 || (i >= 40 && i <= 41) || (i >= 47 && i <= 48) || (i >= 52 && i <= 53) || (i >= 61 && i <= 62) || i == 65 || i == 67 || i == 69) { continue; } lst_F.Add(i); PSC_Girder_User_Inputs[cnt++].Excel_Cell_Reference = "G" + i; } Excel.Range ran = myExcelWorksheet.get_Range("G4:G71", misValue); //myExcelWorksheet.Range["F4:F46"].Locked = false; //if ((bool)ran.Locked) //{ // MessageBox.Show(""); //} myExcelWorksheet.Unprotect("2011ap"); ran.Locked = false; for (i = 0; i < PSC_Girder_User_Inputs.Count; i++) { //myExcelWorksheet.get_Range("F" + lst_F[i].ToString(), misValue).Formula = Deckslab_User_Inputs[i].Input_Value; myExcelWorksheet.get_Range(PSC_Girder_User_Inputs[i].Excel_Cell_Reference, misValue).Formula = PSC_Girder_User_Inputs[i].Input_Value; } ran.Locked = true; myExcelWorksheet.Protect("2011ap"); } #endregion User Inputs #region Cable if ((false)) { if (PSC_Girder_User_Inputs != null) { myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Cable"]; //22 //23 //24 //26 //27 //28 //31 //36 myExcelWorksheet.get_Range("F22", misValue).Formula = PSC_Girder_User_Inputs.Type_of_Cable; myExcelWorksheet.get_Range("F31", misValue).Formula = PSC_Girder_User_Inputs.Type_of_Cable; myExcelWorksheet.get_Range("F36", misValue).Formula = PSC_Girder_User_Inputs.Type_of_Cable; myExcelWorksheet.get_Range("F23", misValue).Formula = PSC_Girder_User_Inputs.Strand_Area; myExcelWorksheet.get_Range("F24", misValue).Formula = PSC_Girder_User_Inputs.UTS; myExcelWorksheet.get_Range("F26", misValue).Formula = PSC_Girder_User_Inputs.Es; myExcelWorksheet.get_Range("F27", misValue).Formula = PSC_Girder_User_Inputs.Permissible_Slip; myExcelWorksheet.get_Range("F28", misValue).Formula = PSC_Girder_User_Inputs.Jacking_Distance; } } #region Update Cable 1 myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Cable 1"]; for (i = 0; i < cab1_ref.Count; i++) { if (cab1_ref[i] != "") { myExcelWorksheet.get_Range(cab1_ref[i], misValue).Formula = DGV_Cable[2, i].Value.ToString().Replace("K", ""); } //if (cab2_ref[i] != "") //{ // myExcelWorksheet.get_Range(cab2_ref[i], misValue).Formula = DGV_Cable[3, i].Value; //} //if (cab3_ref[i] != "") //{ // myExcelWorksheet.get_Range(cab3_ref[i], misValue).Formula = DGV_Cable[4, i].Value; //} //if (cab4_ref[i] != "") //{ // myExcelWorksheet.get_Range(cab4_ref[i], misValue).Formula = DGV_Cable[5, i].Value; //} } #endregion Update Cable 1 #region Update Cable 2 myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Cable 2"]; for (i = 0; i < cab2_ref.Count; i++) { if (cab2_ref[i] != "") { myExcelWorksheet.get_Range(cab2_ref[i], misValue).Formula = DGV_Cable[3, i].Value.ToString().Replace("K", "");; } } #endregion Update Cable 1 #region Update Cable 3 myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Cable 3"]; for (i = 0; i < cab3_ref.Count; i++) { if (cab3_ref[i] != "") { myExcelWorksheet.get_Range(cab3_ref[i], misValue).Formula = DGV_Cable[4, i].Value.ToString().Replace("K", "");; } } #endregion Update Cable 3 #region Update Cable 4 myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Cable 4"]; for (i = 0; i < cab4_ref.Count; i++) { if (cab4_ref[i] != "") { myExcelWorksheet.get_Range(cab4_ref[i], misValue).Formula = DGV_Cable[5, i].Value.ToString().Replace("K", "");; } } #endregion Update Cable 1 #endregion Cable //PSC_Girder_User_Inputs //Deckslab_User_Live_loads.Clear(); myExcelWorkbook.Save(); //myExcelWorkbook.Close(true, fileName, null); Marshal.ReleaseComObject(myExcelWorkbook); #endregion Update_ExcelData } catch (Exception ex) { } }
private void fpSpread_addsheet() { WaitDialogForm wait = null; wait = new WaitDialogForm("", "正在加载数据, 请稍候..."); try { //打开Excel表格 //清空工作表 fpSpread1.Sheets.Clear(); fpSpread1.OpenExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\PDWXZ" + year + ".xls"); fc.SpreadRemoveEmptyCells(fpSpread1); //保持格式 sh31.CellType(fpSpread1.Sheets[0]); } catch (System.Exception e) { //如果打开出错则重新生成并保存 fpSpread1.Sheets.Clear(); Firstadddata(); //判断文件夹是否存在,不存在则创建 if (!Directory.Exists(System.Windows.Forms.Application.StartupPath + "\\xls")) { Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + "\\xls"); } //保存excel文件 fpSpread1.SaveExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\PDWXZ" + year + ".xls"); //以下是打开文件设表格自动换行 // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); string filename = System.Windows.Forms.Application.StartupPath + "\\xls\\PDWXZ" + year + ".xls"; workBook = excelApp.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //取消保护工作表 ws.Unprotect(Missing.Value); //有数据的行数 int row = ws.UsedRange.Rows.Count; //有数据的列数 int col = ws.UsedRange.Columns.Count; //创建一个区域 range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); //设区域内的单元格自动换行 range.WrapText = true; //保护工作表 ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //保存工作簿 workBook.Save(); //关闭工作簿 excelApp.Workbooks.Close(); } wait.Close(); }
/// <summary> /// Funzione per il salvataggio delle modifiche apportate a ranges anche non contigui. /// </summary> /// <param name="Target">L'insieme dei ranges modificati</param> /// <param name="annotaModifica">Se la modifica va segnalata all'utente attraverso il commento sulla cella oppure no.</param> /// <param name="fromCalcolo">Flag per eseguire azioni particolari nel caso la provenienza del salvataggio sia da un calcolo.</param> /// <param name="tableName">La tabella in cui inserire le modifiche. Di default Tab.Modifica. Utile specificarme una diversa nel caso di esportazione XML.</param> public static void StoreEdit(Excel.Range Target, int annotaModifica = -1, bool fromCalcolo = false, string tableName = DataBase.TAB.MODIFICA) { if (Workbook.IdUtente != 0 && Workbook.CategorySheets.Contains(Target.Worksheet)) //non salva sulla tabella delle modifiche se l'utente non è configurato { Excel.Worksheet ws = Target.Worksheet; bool wasProtected = ws.ProtectContents; bool screenUpdating = ws.Application.ScreenUpdating; if (wasProtected) { ws.Unprotect(Workbook.Password); } if (screenUpdating) { Workbook.ScreenUpdating = false; } DefinedNames definedNames = new DefinedNames(Target.Worksheet.Name, DefinedNames.InitType.SaveDB); DataTable dt = Workbook.Repository[tableName]; if (ws.ChartObjects().Count > 0 && !fromCalcolo) { Sheet s = new Sheet(ws); s.AggiornaGrafici(); } string[] ranges = Target.Address.Split(','); foreach (string range in ranges) { Range rng = new Range(range); Range merged = null; object mergedVal = null; try { //controllo se c'è un merge nel range merged = new Range(ws.Range[rng.ToString()].MergeArea.Address); //salvo il valore mergedVal = ws.Range[rng.ToString()].Value; rng = merged; } catch { } foreach (Range row in rng.Rows) { if (definedNames.SaveDB(row.StartRow)) { bool annota = annotaModifica == -1 ? definedNames.ToNote(row.StartRow) : annotaModifica == 1; foreach (Range column in row.Columns) { string[] parts = definedNames.GetNameByAddress(column.StartRow, column.StartColumn).Split(Simboli.UNION[0]); string data; if (parts.Length == 4) { data = Date.GetDataFromSuffisso(parts[2], parts[3]); } else { data = Date.GetDataFromSuffisso(parts[2], ""); } if (!Workbook.Application.WorksheetFunction.IsErr(ws.Range[column.ToString()])) { DataRow r = dt.Rows.Find(new object[] { parts[0], parts[1], data }); if (r != null) { object val = ws.Range[column.ToString()].Value ?? ""; if (merged == null) { r["Valore"] = (val.Equals("-") ? "0" : val); } else { r["Valore"] = mergedVal ?? ""; } } else { DataRow newRow = dt.NewRow(); object val = ws.Range[column.ToString()].Value ?? ""; newRow["SiglaEntita"] = parts[0]; newRow["SiglaInformazione"] = parts[1]; newRow["Data"] = data; if (merged == null) { newRow["Valore"] = (val.Equals("-") ? "0" : val); } else { newRow["Valore"] = mergedVal ?? ""; } newRow["AnnotaModifica"] = annota ? "1" : "0"; newRow["IdApplicazione"] = Workbook.IdApplicazione; newRow["IdUtente"] = Workbook.IdUtente; dt.Rows.Add(newRow); } if (annota) { ws.Range[column.ToString()].ClearComments(); ws.Range[column.ToString()].AddComment("Valore inserito manualmente").Visible = false; } } } } } } if (wasProtected) { ws.Protect(Workbook.Password); } if (screenUpdating) { ws.Application.ScreenUpdating = true; } } }
public void Insert_Values_into_Excel_Long_Girder() { Excel.Application myExcelApp; Excel.Workbooks myExcelWorkbooks; Excel.Workbook myExcelWorkbook; object misValue = System.Reflection.Missing.Value; myExcelApp = new Excel.ApplicationClass(); myExcelApp.Visible = true; //myExcelApp.Visible = false; myExcelWorkbooks = myExcelApp.Workbooks; String fileName = Excel_File_Name; // set this to your file you want //myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); myExcelWorkbook = myExcelWorkbooks.Open(fileName, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet; Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["8. Summ Force"]; String cellFormulaAsString = myExcelWorksheet.get_Range("A1", misValue).Formula.ToString(); // this puts the formula in Cell A2 or text depending whats in it in the string. int cel_index = 7; VDataCollection vdc = dr.DL_Self_Weight; #region DL_Self_Weight for (int i = 0; i < vdc.Count; i++) { myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support; myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening; myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8; myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4; myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8; myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid; } #endregion DL_Self_Weight cel_index = 16; vdc = dr.DL_Deck_Wet_Conc; #region DL_Deck_Wet_Conc for (int i = 0; i < vdc.Count; i++) { myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support; myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening; myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8; myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4; myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8; myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid; } #endregion DL_Deck_Wet_Conc cel_index = 25; vdc = dr.DL_Deck_Dry_Conc; #region DL_Deck_Dry_Conc for (int i = 0; i < vdc.Count; i++) { myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support; myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening; myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8; myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4; myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8; myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid; } #endregion DL_Deck_Dry_Conc cel_index = 35; vdc = dr.DL_Self_Deck; #region DL_Self_Deck for (int i = 0; i < vdc.Count; i++) { myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support; myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening; myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8; myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4; myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8; myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid; } #endregion DL_Self_Deck cel_index = 48; vdc = dr.SIDL_Crash_Barrier; #region SIDL_Crash_Barrier for (int i = 0; i < vdc.Count; i++) { myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support; myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening; myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8; myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4; myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8; myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid; } #endregion SIDL_Crash_Barrier cel_index = 61; vdc = dr.SIDL_Wearing; #region SIDL_Wearing for (int i = 0; i < vdc.Count; i++) { myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support; myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening; myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8; myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4; myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8; myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid; } #endregion SIDL_Wearing cel_index = 75; vdc = dr.LL_Moving; #region LL_Moving for (int i = 0; i < vdc.Count; i++) { if (vdc[i].LoadText != "") { myExcelWorksheet.get_Range("L" + (cel_index + i), misValue).Formula = vdc[i].LoadText; } myExcelWorksheet.get_Range("M" + (cel_index + i), misValue).Formula = vdc[i].Support / 10; myExcelWorksheet.get_Range("N" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening / 10; myExcelWorksheet.get_Range("O" + (cel_index + i), misValue).Formula = vdc[i]._L_8 / 10; myExcelWorksheet.get_Range("P" + (cel_index + i), misValue).Formula = vdc[i]._L_4 / 10; myExcelWorksheet.get_Range("Q" + (cel_index + i), misValue).Formula = vdc[i]._3L_8 / 10; myExcelWorksheet.get_Range("R" + (cel_index + i), misValue).Formula = vdc[i].Mid / 10; } if ((cel_index + vdc.Count) <= 84) { //myExcelWorksheet.get_Range("A84" + (i), misValue).EntireRow.Hidden = true; cel_index = cel_index + vdc.Count; for (int i = cel_index; i <= 84; i++) { myExcelWorksheet.get_Range("A" + (i), misValue).EntireRow.Hidden = true; myExcelWorksheet.get_Range("L" + (i), misValue).Formula = "'"; myExcelWorksheet.get_Range("M" + (i), misValue).Formula = 0.0; myExcelWorksheet.get_Range("N" + (i), misValue).Formula = 0.0; myExcelWorksheet.get_Range("O" + (i), misValue).Formula = 0.0; myExcelWorksheet.get_Range("P" + (i), misValue).Formula = 0.0; myExcelWorksheet.get_Range("Q" + (i), misValue).Formula = 0.0; myExcelWorksheet.get_Range("R" + (i), misValue).Formula = 0.0; } } #endregion LL_Moving #region User Inputs if (Long_User_Inputs != null) { myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["1.Input"]; List <int> lst_F = new List <int>(); int cnt = 0; int i = 0; for (i = 5; i <= 71; i++) { if (i == 6 || i == 12 || i == 17 || i == 22 || i == 39 || i == 40 || i == 45 || i == 46 || i == 50 || i == 51 || i == 55 || i == 59 || i == 60 || i == 63 || i == 65) { continue; } lst_F.Add(i); Long_User_Inputs[cnt++].Excel_Cell_Reference = "F" + i; } Excel.Range ran = myExcelWorksheet.get_Range("F5:F71", misValue); //myExcelWorksheet.Range["F4:F46"].Locked = false; //if ((bool)ran.Locked) //{ // MessageBox.Show(""); //} myExcelWorksheet.Unprotect("2011ap"); ran.Locked = false; for (i = 0; i < Long_User_Inputs.Count; i++) { //myExcelWorksheet.get_Range("F" + lst_F[i].ToString(), misValue).Formula = Deckslab_User_Inputs[i].Input_Value; myExcelWorksheet.get_Range(Long_User_Inputs[i].Excel_Cell_Reference, misValue).Formula = Long_User_Inputs[i].Input_Value; } ran.Locked = true; myExcelWorksheet.Protect("2011ap"); } #endregion User Inputs myExcelWorkbook.Save(); //myExcelWorkbook.Close(true, fileName, null); Marshal.ReleaseComObject(myExcelWorkbook); }
private void xlsPut() { DialogResult ret; int sCnt = 0; int dCnt = 0; //ダイアログボックスの初期設定 string pathName = string.Empty; ret = folderBrowserDialog1.ShowDialog(); if (ret == System.Windows.Forms.DialogResult.OK) { pathName = folderBrowserDialog1.SelectedPath; if (MessageBox.Show("出力先は以下のフォルダでよろしいですか?" + Environment.NewLine + Environment.NewLine + pathName, "確認", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes) { ; } } else { return; } //マウスポインタを待機にする this.Cursor = Cursors.WaitCursor; // Excelテンプレートシート開く string sAppPath = System.AppDomain.CurrentDomain.BaseDirectory; Excel.Application oXls = new Excel.Application(); Excel.Workbook oXlsBook = (Excel.Workbook)(oXls.Workbooks.Open(sAppPath + Properties.Settings.Default.xlsTempPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)); Excel.Worksheet oxlsSheet = (Excel.Worksheet)oXlsBook.Sheets[1]; Excel.Range[] rng = new Microsoft.Office.Interop.Excel.Range[2]; try { // 会員情報を取得 JfgDataClassDataContext db = new JfgDataClassDataContext(); var s = db.会員情報.OrderBy(a => a.カード番号); //オーナーフォームを無効にする this.Enabled = false; //プログレスバーを表示する frmPrg frmP = new frmPrg(s.Count(), 0); frmP.Owner = this; frmP.Show(); // 会員情報を読み込む foreach (var item in s) { //プログレスバー表示 dCnt++; frmP.Text = "エクセル予定申告書作成中 " + dCnt.ToString() + "/" + s.Count().ToString(); frmP.progressValue = dCnt; frmP.ProgressStep(); // 選択条件 if (chkGen.CheckState == CheckState.Checked && item.JFG会員歴 == 1 || chkTai.CheckState == CheckState.Checked && item.JFG会員歴 == 2 || chkKyu.CheckState == CheckState.Checked && item.JFG会員歴 == 3 || chkFumei.CheckState == CheckState.Checked && item.JFG会員歴 == 4 || chkHi.CheckState == CheckState.Checked && item.JFG会員歴 == 5) { // シートの保護を解除する oxlsSheet.Unprotect(); // セルに値を渡す oxlsSheet.Cells[2, 7] = item.氏名 + "さん"; oxlsSheet.Cells[2, 11] = item.カード番号.ToString(); oxlsSheet.Cells[5, 2] = DateTime.Today.Year.ToString(); oxlsSheet.Cells[5, 3] = DateTime.Today.Month.ToString(); oxlsSheet.Cells[5, 6] = DateTime.Today.AddMonths(1).Year.ToString(); oxlsSheet.Cells[5, 7] = DateTime.Today.AddMonths(1).Month.ToString(); oxlsSheet.Cells[5, 10] = DateTime.Today.AddMonths(2).Year.ToString(); oxlsSheet.Cells[5, 11] = DateTime.Today.AddMonths(2).Month.ToString(); oxlsSheet.Cells[5, 14] = DateTime.Today.AddMonths(3).Year.ToString(); oxlsSheet.Cells[5, 15] = DateTime.Today.AddMonths(3).Month.ToString(); oxlsSheet.Cells[5, 18] = DateTime.Today.AddMonths(4).Year.ToString(); oxlsSheet.Cells[5, 19] = DateTime.Today.AddMonths(4).Month.ToString(); oxlsSheet.Cells[5, 22] = DateTime.Today.AddMonths(5).Year.ToString(); oxlsSheet.Cells[5, 23] = DateTime.Today.AddMonths(5).Month.ToString(); // 申告欄クリア for (int iRow = 8; iRow < 39; iRow++) { oxlsSheet.Cells[iRow, 3] = string.Empty; oxlsSheet.Cells[iRow, 7] = string.Empty; oxlsSheet.Cells[iRow, 11] = string.Empty; oxlsSheet.Cells[iRow, 15] = string.Empty; oxlsSheet.Cells[iRow, 19] = string.Empty; oxlsSheet.Cells[iRow, 23] = string.Empty; } // ウィンドウを非表示にする oXls.Visible = false; // 保存処理 oXls.DisplayAlerts = false; // シートの保護 oxlsSheet.Protect(Type.Missing, false, true, false, false, false, false, false, false, false, false, false, false, false, false, false); // シートの保存 string fileName = pathName + @"\" + item.カード番号.ToString() + " " + item.氏名 + ".xlsx"; oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // カウント sCnt++; } } // いったんオーナーをアクティブにする this.Activate(); // 進行状況ダイアログを閉じる frmP.Close(); // オーナーのフォームを有効に戻す this.Enabled = true; // 終了メッセージ MessageBox.Show(sCnt.ToString() + "人の予定申告シートを出力しました", "予定申告シート出力完了", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception e) { MessageBox.Show(e.Message, "エクセル予定申告シート出力", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } finally { // Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); // Excelを終了 oXls.Quit(); // COM オブジェクトの参照カウントを解放する System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(oXlsBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(oXls); //マウスポインタを元に戻す this.Cursor = Cursors.Default; } }
private void button2_Click(object sender, RibbonControlEventArgs e) { Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet); string AktifSayfa = activeWorksheet.Name; if (IslemSayfasi == AktifSayfa) //Doğru sayfada olup olmadığı kontrol edilir. { //veriler sıfırlanır. bool Hata_Yakalama = false; EKOCT_TOPLAM = 0; EKCT_TOPLAM = 0; KCT_TOPLAM = 0; KOCT_TOPLAM = 0; NKCT_TOPLAM = 0; ONCT_TOPLAM = 0; CVPSZCT_TOPLAM = 0; ARADCT_TOPLAM = 0; //bütün soruları işaretlemeyen cevaplayıcılar tespit edilir. Bos_Satirlar = new ArrayList(); int Bos_Satir_Sayisi = 0; for (int i = 1; i <= Katilimci_Sayisi; i++) {//Bütün boş satırların sayısını ve indis numaralarını bulur. int sayac = 0; for (int j = 1; j <= Madde_Sayisi; j++) { string siradaki = Hucrelerden_Okuma_Metodu((i + 1), (j + 1)); if (siradaki == "" || siradaki == " " || siradaki == "0") { sayac++; } } if (sayac == Madde_Sayisi) { Bos_Satir_Sayisi++; Bos_Satirlar.Add(i + 1); } } Excel.Worksheet wsSil = Globals.ThisAddIn.Application.ActiveSheet; Excel.Range row; int[] rows = new int[Bos_Satirlar.Count]; int SilinenSayac = 0; foreach (var item in Bos_Satirlar) {//Bütün soruları işaretlemeyen cevaplayıcılar silinir. row = (Excel.Range)wsSil.Rows[Convert.ToInt32(item.ToString()) - SilinenSayac]; row.Delete(); SilinenSayac++; } Katilimci_Sayisi = Katilimci_Sayisi - Bos_Satir_Sayisi; Sonuclar = new double[Katilimci_Sayisi, 9]; for (int i = 1; i <= Katilimci_Sayisi; i++) { Hata_Yakalama = false; ArrayList Katilimci_Cevaplari = new ArrayList();//Her katılımcı için liste oluşturulur. for (int j = 1; j <= Madde_Sayisi; j++) { string siradaki = Hucrelerden_Okuma_Metodu((i + 1), (j + 1)); if (siradaki == "" || siradaki == " ") { Katilimci_Cevaplari.Add(siradaki); } else { try {//Doğru aralıkta veri girişi yapılmış ise katılımcının cevapları bir listeye eklenir. //yanlış veri girişi yapılmış ise program hata mesajı verir. if (Convert.ToInt32(siradaki) > Anket_Tipi || Convert.ToInt32(siradaki) < 0) { i = Madde_Sayisi + 1; Hata_Yakalama = true; MessageBox.Show("Hatalı Veri Girişi Yapılmıştır.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); break; } else { Katilimci_Cevaplari.Add(siradaki); } } catch (Exception ex) { i = Madde_Sayisi + 1; Hata_Yakalama = true; MessageBox.Show("Hatalı Veri Girişi Yapılmıştır.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); break; } } } //formüllerin hesaplanması için ilgili metod çağırılır. EKOCT_Metod(Katilimci_Cevaplari, i); EKCT_Metod(Katilimci_Cevaplari, i); ECT = EKOCT + EKCT;//Ekstrem Cevaplama Tarzı. Sonuclar[(i - 1), 0] = ECT; if (Anket_Tipi % 2 == 1) {//Orta Noktası Bulunan Anket Türleri İçin. KCT_Metod(Katilimci_Cevaplari, i); KCT_TOPLAM += KCT; KOCT_Metod(Katilimci_Cevaplari, i); KOCT_TOPLAM += KOCT; ONCT_Metod(Katilimci_Cevaplari, i); ONCT_TOPLAM += ONCT; } NKCT = KCT - KOCT; NKCT_TOPLAM += NKCT; Sonuclar[(i - 1), 5] = NKCT; CVPSZCT_Metod(Katilimci_Cevaplari, i); CVPSZCT_TOPLAM += CVPSZCT; ARADCT = 1 - ECT; ARADCT_TOPLAM += ARADCT; Sonuclar[(i - 1), 8] = ARADCT; EKOCT_TOPLAM += EKOCT; EKCT_TOPLAM += EKCT; } if (Hata_Yakalama == false)//bir hata ile karşılaşılmadı ise devam eder. { Excel.Worksheet ws1 = Globals.ThisAddIn.Application.ActiveSheet; Excel.Range range = (Excel.Range)ws1.Range[ws1.Cells[2, Madde_Sayisi + 3], ws1.Cells[Katilimci_Sayisi + 1, Madde_Sayisi + 11]]; range.Value2 = Sonuclar; //her formül için ortalama değerleri bulunur. EKOCT_TOPLAM = EKOCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 5, EKOCT_TOPLAM.ToString("0.#####")); EKCT_TOPLAM = EKCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 4, EKCT_TOPLAM.ToString("0.#####")); Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 3, (EKCT_TOPLAM + EKOCT_TOPLAM).ToString("0.#####")); KCT_TOPLAM = KCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 6, KCT_TOPLAM.ToString("0.#####")); KOCT_TOPLAM = KOCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 7, KOCT_TOPLAM.ToString("0.#####")); NKCT_TOPLAM = NKCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 8, NKCT_TOPLAM.ToString("0.#####")); ONCT_TOPLAM = ONCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 9, ONCT_TOPLAM.ToString("0.#####")); CVPSZCT_TOPLAM = CVPSZCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 10, CVPSZCT_TOPLAM.ToString("0.#####")); ARADCT_TOPLAM = ARADCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 11, ARADCT_TOPLAM.ToString("0.#####")); //Hücreleri kilitleme ve renk ayarı. //kullanılmayan alanlar kullanıma kapatılır. string sutun = harfler[Madde_Sayisi + 10].ToString() + (Katilimci_Sayisi + 2).ToString(); Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet; Excel.Range fr; fr = ws.get_Range("A1", sutun); fr.Borders.Weight = 2; fr.Borders.Color = Color.Black; fr.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightBlue); Globals.ThisAddIn.Application.Cells.Locked = true; Globals.ThisAddIn.Application.get_Range("A1", sutun).Locked = false; Excel.Worksheet Sheet1 = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet; Sheet1.Protect(); } } else { MessageBox.Show("Hesaplama İşlemini '" + IslemSayfasi + "' İçinde Yapabilirsiniz.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
/// <summary> /// Handles insertion of common class elements into worksheet. /// /// TODO: Refactor code /// </summary> /// <param name="selectedNode">Common Class element element to use</param> protected void handleCommon(XElement selectedNode, string name, bool annotate) { string id = selectedNode.Element(rs + "names").Element(rs + "id").Value; string fullId = selectedNode.Element(rs + "names").Element(rs + "id").Value; //Removing the institution identifying prefix from CADSR elements on client request if (id.Contains("-CADSR-")) { string[] idarr = id.Split('-'); id = idarr[idarr.Length - 2] + "v" + idarr[idarr.Length - 1]; } string preferredName = selectedNode.Element(rs + "names").Element(rs + "preferred").Value; string definition = selectedNode.Element(rs + "definition").Value; if (definition == null || definition.Length == 0) { definition = "(No definition supplied)"; } else { //Handle special caDSR/EVS format definition = definition.Trim().Replace(">", ">").Replace("<", "<").Replace("<![CDATA[", "").Replace("]]>", ""); if (definition.Contains("<def-source>")) { XElement e = XElement.Parse("<def>" + definition + "</def>"); definition = e.Element("def-definition").Value + "\n(Source: " + e.Element("def-source").Value + ")"; } } string attr = createAttrs(selectedNode, name); string label = id + ":" + preferredName; string[] concepts = null; Excel.Range selected = (Excel.Range)application.Selection; string hLinkLabel = ""; if (annotate && (!name.Equals("vd") && !name.Equals("dec") && !name.Equals("cd"))) { concepts = extractConcepts(attr).Split(';'); Excel.Range leftCell = selected.get_Offset(0, -1); string prim = concepts[concepts.Length - 1]; string quals = ""; for (int i = 0; i < concepts.Length - 1; i++) quals += concepts[i] + ";"; if (quals.Length > 0) quals = quals.Substring(0, quals.Length - 1); if ((selected.Value2 == null || selected.Value2.ToString().Length == 0) && (((leftCell.Value2 == null || leftCell.Value2.ToString().Length == 0) || quals.Length == 0))) { selected.Value2 = prim; hLinkLabel = prim; if (quals.Length > 0) leftCell.Value2 = quals; } } else { //Get selected range if (selected.Value2 == null || selected.Value2.ToString().Length == 0) { selected.Value2 = label; hLinkLabel = label; } else { //Refuse to add? Do you have to remove first? } } // empty hlink indicates non-empty cell. don't overwrite if (hLinkLabel.Length > 0) { //Create concept list if not exists propList = useList(propList, name + "_list"); //Add new concept entry to concept_list propList.Unprotect(dummyPass); Excel.Range c = (Excel.Range)propList.Cells[2, 1]; Excel.Range found = propList.Cells.Find(id, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing); if (found == null) //if (existingIndex == 0) { for (int i = 3; c.Value2 != null; i++) { c = (Excel.Range)propList.Cells[i, 1]; } c.Value2 = fullId; c.Next.Value2 = preferredName; c.Next.Next.Value2 = definition.Trim().Replace(">", ">").Replace("<", "<").Replace("&", "&"); c.Next.Next.Next.Value2 = attr.Trim().Replace(",", "\n\n").Replace(",", ", ").Replace(">", ">").Replace("<", "<").Replace("&", "&"); //Cells mapped counter //c.Next.Next.Next.Next.Value2 = 1; } else { found.Next.Next.Next.Next.Value2 = 1 + Convert.ToInt16(found.Next.Next.Next.Next.Value2.ToString()); } propList.Protect(dummyPass, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); /* Remove hyperlinks for now until a suitable two way link method is found */ //Do not overwrite if something is in there already selected.Hyperlinks.Add(selected, "", getSelectedRangeAddress(c), Type.Missing, hLinkLabel); selected.Font.Bold = true; selected.Font.Underline = false; if (annotate) annotateComment(selected); else { selected.Font.ColorIndex = 1; selected.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent3; selected.Interior.TintAndShade = 0.2; } } }
private void btnEliminarIndice_Click(object sender, RibbonControlEventArgs e) { Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook; Worksheet sheetControl = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Excel.Worksheet sheet = Globals.ThisAddIn.Application.ActiveSheet; string IndiceActivo = ""; string IndiceSiguiente = ""; string _NameFile = wb.Name; bool Eliminar = false; List <string> NombreRangos = new List <string>(); List <string> NombreRangosDEL = new List <string>(); List <int> FilaPadre = new List <int>(); int FilapadreAux = 0; long dif = 0; string NamedRange = ""; bool tienedif = false; Excel.Range objRange = null; Excel.Range currentCell = (Excel.Range)Globals.ThisAddIn.Application.Selection; // filas seleccionadas try { foreach (Excel.Range cell in currentCell.Cells) { try { foreach (Excel.Name item1 in wb.Names) { // comparo la direccion de la celda con la del nombre del rango if (item1.Name.Substring(0, 3) == "IA_") { if (item1.RefersToRange.Cells.get_Address() == cell.Address) { NamedRange = item1.Name; break; } } } FilapadreAux = cell.Row; if (!FilaPadre.Contains(FilapadreAux)) { FilaPadre.Add(FilapadreAux); } objRange = (Excel.Range)sheet.Cells[cell.Row, 1]; IndiceActivo = objRange.Value2; if (IndiceActivo.ToUpper().Trim() == "EXPLICACION") { MessageBox.Show("No es posible eliminar el índice EXPLICACION.", "Eliminar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning); Eliminar = false; break; } if ((NamedRange != "IA_" + IndiceActivo) || (NamedRange == "")) { MessageBox.Show("No es posible eliminar un índice de formato guía", "Eliminar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning); Eliminar = false; break; } else { Eliminar = true; NombreRangosDEL.Add("IA_" + IndiceActivo); } } catch (Exception ex) { //MessageBox.Show(ex.Message); } } if (Eliminar) { sheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel); currentCell = (Excel.Range)Globals.ThisAddIn.Application.Selection; int CantRowDelete = currentCell.Cells.Rows.Count; objRange = (Excel.Range)sheet.Cells[currentCell.Cells.Row + 1, 1]; IndiceSiguiente = objRange.Value2; if (IndiceSiguiente != null) { if (IndiceSiguiente.ToUpper().Trim() == "EXPLICACION") { objRange.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); CantRowDelete += 1; } } currentCell.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); NombreRangosDEL.Sort(); string NM = NombreRangosDEL.FirstOrDefault(); sheetControl.Controls.Remove(NM); foreach (Excel.Name item2 in wb.Names) { if (item2.Name.Substring(0, 3) == "IA_") { NombreRangos.Add(item2.Name); } } string[] split = NM.Split('_'); NM = split[1]; // foreach (string Nm in NombreRangosDEL) long NamedRng = Convert.ToInt64(NM) + 100; string IndiceSig = "0" + Convert.ToString(NamedRng); string IndiceAnt = ""; while (NombreRangos.Contains("IA_" + IndiceSig)) { sheetControl.Controls.Remove("IA_" + IndiceSig); NamedRng = Convert.ToInt64(IndiceSig) + 100; IndiceSig = "0" + Convert.ToString(NamedRng); } FilaPadre.Sort(); int row = FilaPadre.FirstOrDefault(); objRange = (Excel.Range)sheet.Cells[row, 1]; if (objRange.get_Value(Type.Missing) != null) { IndiceActivo = objRange.get_Value(Type.Missing).ToString(); } objRange = (Excel.Range)sheet.Cells[row - 1, 1]; if (objRange.get_Value(Type.Missing) != null) { IndiceAnt = objRange.get_Value(Type.Missing).ToString(); } //me salto la explciacion if (IndiceAnt.Trim() == "EXPLICACION") { objRange = (Excel.Range)sheet.Cells[row - 2, 1]; if (objRange.get_Value(Type.Missing) != null) { IndiceAnt = objRange.get_Value(Type.Missing).ToString(); } } while (NombreRangos.Contains("IA_" + IndiceActivo)) { tienedif = false; dif = Convert.ToInt64(IndiceActivo) - Convert.ToInt64(IndiceAnt); while (dif != 100) { IndiceAnt = "0" + Convert.ToString(Convert.ToInt64(IndiceActivo) - 100); IndiceActivo = IndiceAnt; dif = dif - 100; tienedif = true; } objRange = (Excel.Range)sheet.Cells[row, 1]; objRange.Value2 = IndiceAnt; if (tienedif) { Generales.AddNamedRange(row, 1, "IA_" + Convert.ToString(IndiceAnt)); } //busco el siguiente activo row++; objRange = (Excel.Range)sheet.Cells[row, 1]; if (objRange.get_Value(Type.Missing) != null) { IndiceActivo = objRange.get_Value(Type.Missing).ToString(); } else { break; } } row = Generales.DameRangoPrincipal(FilaPadre.FirstOrDefault(), sheet);// busco el numero de fila OTRO para agregarle luego la sumatoria de los indices nuevos Excel.Range objRangeJ = ((Excel.Range)sheet.Cells[FilaPadre[0], 1]); objRangeJ.Select(); try { // limpio si hay error en la formula Excel.Range objRangeI = ((Excel.Range)sheet.Cells[row, 1]); //.SpecialCells(Excel.XlCellType.xlCellTypeFormulas, Excel.XlSpecialCellsValue.xlErrors);//obten las celdas con errores string NombreHoja = sheet.Name.ToUpper().Replace(" ", ""); List <oSubtotal> ColumnasST = Generales.DameColumnasST(NombreHoja); int _Registro = 1; foreach (oSubtotal ST in ColumnasST) { objRangeI = sheet.get_Range(ST.Columna + row.ToString(), ST.Columna + row.ToString()); //objRangeI.Clear(); if (_Registro == 1) { _Registro += 1; Generales.ActualizarReferencia(_NameFile, sheet.Name.ToUpper(), ST.Columna + row.ToString(), NombreRangos.Count, ST.Columna, row.ToString(), CantRowDelete, "E"); } } //wb.Save(); } catch (Exception ex) { } sheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false); } } catch (Exception ex) { // MessageBox.Show(ex.Message); } }
public void SyuYakuDL(DataGridView gr, DataTable ds) { string saveFileName = "集約_" + DateTime.Now.ToString("yyyyMMdd"); string templetFile = @"template\集約.xlsx"; SaveFileDialog saveDialog = new SaveFileDialog { Filter = "Excel文件 |*.xlsx", FileName = saveFileName, RestoreDirectory = true }; if (saveDialog.ShowDialog() == DialogResult.OK) { saveFileName = saveDialog.FileName; File.Copy(templetFile, saveFileName, true); Excel.Application excel = new Excel.Application(); Excel.Workbook workbook = excel.Workbooks.Open(saveFileName); Excel.Worksheet worksheet = workbook.Worksheets[1]; worksheet.Name = "集約"; excel.Visible = false; int excelRow = 0; int number = 1; int selectRow = 0; try { log.Info("EXEC BEGIN"); //一行目値取得 //datagridview行数判断 for (int a = 0; a < gr.RowCount; a++) { //選択行 if ((bool)gr.Rows[a].Cells[0].EditedFormattedValue == true) { //DataSet行数判断 for (int i = 0; i < ds.Rows.Count; i++) { if (gr.Rows[a].Cells["DENPYONO"].Value.Equals(ds.Rows[i][13]) && gr.Rows[a].Cells["SOKOCD"].Value.Equals(ds.Rows[i][19]) && gr.Rows[a].Cells["SYKFILENM"].Value.Equals(ds.Rows[i][17]) && gr.Rows[a].Cells["SEQNO"].Value.Equals(ds.Rows[i][18])) { //NO行取得 worksheet.Cells[2, 1] = 1; for (int r = 0; r < 19; r++) { worksheet.Cells[2, r + 2] = ds.Rows[i][r]; } selectRow = a; break; } else { continue; } } break; } } for (int b = selectRow + 1; b < gr.RowCount; b++) { if ((bool)gr.Rows[b].Cells[0].EditedFormattedValue == true) { for (int i = 0; i < ds.Rows.Count; i++) { if (gr.Rows[b].Cells["DENPYONO"].Value.Equals(ds.Rows[i][13]) && gr.Rows[b].Cells["SOKOCD"].Value.Equals(ds.Rows[i][19]) && gr.Rows[b].Cells["SYKFILENM"].Value.Equals(ds.Rows[i][17]) && gr.Rows[b].Cells["SEQNO"].Value.Equals(ds.Rows[i][18])) { Excel.Range RngToCopy = worksheet.get_Range("A2").EntireRow; Excel.Range RngToInsert = worksheet.get_Range("A" + (number + 2)).EntireRow; RngToInsert.Insert(Excel.XlInsertShiftDirection.xlShiftDown, RngToCopy.Copy()); //NO行取得 number++; worksheet.Cells[excelRow + 3, 1] = number; for (int j = 0; j < 19; j++) { worksheet.Cells[excelRow + 3, j + 2] = ds.Rows[i][j]; } excelRow++; } } } } } catch (Exception e) { log.Error("ERROR" + e.Message); } finally { Application.DoEvents(); var xml = XDocument.Load(@"..\Mitsui.xml"); var queryC = xml.Root.Descendants("rndflg") .Elements("col") .Select(a => a.Value); List <string> cols = new List <string>(); foreach (var element in queryC) { cols = element.Split(',').ToList(); } foreach (var b in cols) { worksheet.get_Range(b + ":" + b).Locked = false; } worksheet.Protect(); //workbook.Saved = true; workbook.Save(); excel.Quit(); Marshal.FinalReleaseComObject(excel); log.Info("EXEC END"); } } else { MessageBox.Show("処理を中止しました。"); } }
public override void Range(object Sh, Excel.Range Target) { if (Workbook.CategorySheets.Contains(Target.Worksheet)) { //Se la funzione scrive in altre celle, ricordarsi di disabilitare gli handler per la modifica delle celle //Workbook.WB.SheetChange -= Handler.StoreEdit; Workbook.RemoveStdStoreEdit(); Workbook.WB.SheetChange -= this.Range; Excel.Worksheet ws = Target.Worksheet; Excel.Worksheet wsMercato = Workbook.Sheets[Workbook.Mercato]; bool wasProtected = wsMercato.ProtectContents; if (wasProtected) { wsMercato.Unprotect(Workbook.Password); ws.Unprotect(Workbook.Password); } DefinedNames definedNames = new DefinedNames(ws.Name); DefinedNames definedNamesMercato = new DefinedNames(Workbook.Mercato); DataTable entita = Workbook.Repository[DataBase.TAB.CATEGORIA_ENTITA]; string[] ranges = Target.Address.Split(','); foreach (string range in ranges) { Range rng = new Range(range); foreach (Range cell in rng.Cells) { string[] parts = definedNames.GetNameByAddress(cell.StartRow, cell.StartColumn).Split(Simboli.UNION[0]); string siglaEntita = parts[0]; string siglaInformazione = parts[1]; string suffissoData = parts[2]; string suffissoOra = parts[3]; var rif = (from r in entita.AsEnumerable() where r["IdApplicazione"].Equals(Workbook.IdApplicazione) && r["SiglaEntita"].Equals(siglaEntita) select new { SiglaEntita = r["Gerarchia"] is DBNull ? r["SiglaEntita"] : r["Gerarchia"], Riferimento = r["Riferimento"] }).First(); string quarter = Regex.Match(siglaInformazione, @"Q\d").Value; quarter = quarter == "" ? "Q1" : quarter; Range rngMercato = new Range(definedNamesMercato.GetRowByName(rif.SiglaEntita, "UM", "T") + 2, definedNamesMercato.GetColFromName("RIF" + rif.Riferimento, "PROGRAMMA" + quarter)); rngMercato.StartRow += (Date.GetOraFromSuffissoOra(suffissoOra) - 1); wsMercato.Range[rngMercato.ToString()].Value = ws.Range[cell.ToString()].Value; ws.Range[cell.ToString()].Interior.ColorIndex = wsMercato.Range[rngMercato.ToString()].DisplayFormat.Interior.ColorIndex; } } if (wasProtected) { wsMercato.Protect(Workbook.Password); ws.Protect(Workbook.Password); } //Se la funzione scrive in altre celle, ricordarsi di riabilitare gli handler per la modifica delle celle //Workbook.WB.SheetChange += Handler.StoreEdit; Workbook.AddStdStoreEdit(); Workbook.WB.SheetChange += this.Range; } }
public void Read_Update_Data() { Clear(); if (!File.Exists(Report_File_Name)) { return; } List <string> list = new List <string>(File.ReadAllLines(Report_File_Name)); MyList mlist = null; int i = 0; string kStr = ""; Deck_Data dc = null; int indx = 0; for (i = 0; i < list.Count; i++) { kStr = MyList.RemoveAllSpaces(list[i]); if (kStr == "" || kStr.StartsWith("--------------")) { continue; } mlist = new MyList(kStr, ' '); dc = new Deck_Data(); try { indx = mlist.Count - 1; for (int j = 0; j < 15; j++) { dc.Add(mlist.GetDouble(indx--)); } dc.Reverse(); this.Add(dc); } catch (Exception ex) { } } kStr = ""; #region Update_ExcelData Excel.Application myExcelApp; Excel.Workbooks myExcelWorkbooks; Excel.Workbook myExcelWorkbook; object misValue = System.Reflection.Missing.Value; myExcelApp = new Excel.Application(); myExcelApp.Visible = true; //myExcelApp.Visible = false; myExcelWorkbooks = myExcelApp.Workbooks; String fileName = Excel_File_Name; // set this to your file you want //myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); myExcelWorkbook = myExcelWorkbooks.Open(fileName, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet; Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["5.Design"]; String cellFormulaAsString = myExcelWorksheet.get_Range("A1", misValue).Formula.ToString(); // this puts the formula in Cell A2 or text depending whats in it in the string. //int cel_index = 30; //int cel_index = 32; int cel_index = 26; int ci = (int)('B'); char c = (char)ci; #region DL_Self_Weight for (i = 0; i < Count; i++) { ci = (int)('B'); for (int j = 0; j < (this[i].Count); j++) { c = (char)ci++; myExcelWorksheet.get_Range(c.ToString() + (cel_index + i), misValue).Formula = this[i][j].ToString(); } //myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support; //myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening; //myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8; //myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4; //myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8; //myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid; } #endregion DL_Self_Weight #region User Inputs if (Deckslab_User_Inputs != null) { myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["1.Input"]; List <int> lst_F = new List <int>(); int cnt = 0; for (i = 4; i <= 49; i++) { if (i == 5 || i == 16 || i == 19 || i == 30 || i == 31 || i == 36 || i == 37 || (i > 43 && i < 49)) { continue; } lst_F.Add(i); Deckslab_User_Inputs[cnt++].Excel_Cell_Reference = "F" + i; } Excel.Range ran = myExcelWorksheet.get_Range("F4:F50", misValue); //myExcelWorksheet.Range["F4:F46"].Locked = false; //if ((bool)ran.Locked) //{ // MessageBox.Show(""); //} myExcelWorksheet.Unprotect("2011ap"); ran.Locked = false; for (i = 0; i < Deckslab_User_Inputs.Count; i++) { //myExcelWorksheet.get_Range("F" + lst_F[i].ToString(), misValue).Formula = Deckslab_User_Inputs[i].Input_Value; myExcelWorksheet.get_Range(Deckslab_User_Inputs[i].Excel_Cell_Reference, misValue).Formula = Deckslab_User_Inputs[i].Input_Value; } ran.Locked = true; myExcelWorksheet.Protect("2011ap"); } #endregion User Inputs //Deckslab_User_Live_loads.Clear(); #region Live Loads myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["2.LiveLoad"]; double max_val = llc[0].Get_Maximum_Load(); double val = (max_val * 10 / 2.0); List <string> lst_xls = new List <string>(); //lst_xls.Add(string.Format("57")); lst_xls.Add(string.Format("L28")); lst_xls.Add(string.Format("L29")); lst_xls.Add(string.Format("F105")); lst_xls.Add(string.Format("F106")); lst_xls.Add(string.Format("M105")); lst_xls.Add(string.Format("M106")); lst_xls.Add(string.Format("G369")); lst_xls.Add(string.Format("M369")); lst_xls.Add(string.Format("G374")); lst_xls.Add(string.Format("M374")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = val.ToString(); } lst_xls.Clear(); max_val = llc[3].Get_Maximum_Load(); val = (max_val * 10); //val = (max_val * 10 / 2.0); //lst_xls.Add(string.Format("100")); lst_xls.Add(string.Format("G161")); lst_xls.Add(string.Format("M161")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = val.ToString(); } //lst_xls.Add(string.Format("")); //lst_xls.Add(string.Format("")); //lst_xls.Add(string.Format("")); //lst_xls.Add(string.Format("")); lst_xls.Clear(); max_val = llc[4].Get_Maximum_Load(); val = (max_val * 10); //val = (max_val * 10 / 2.0); //lst_xls.Add(string.Format("50")); lst_xls.Add(string.Format("G229")); lst_xls.Add(string.Format("G230")); lst_xls.Add(string.Format("M229")); lst_xls.Add(string.Format("M230")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = val.ToString(); } //lst_xls.Add(string.Format("")); lst_xls.Clear(); max_val = llc[1].Total_Loads / 2.0;; val = (max_val * 10); //lst_xls.Add(string.Format("35")); lst_xls.Add(string.Format("G297")); lst_xls.Add(string.Format("M297")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = val.ToString(); } //lst_xls.Add(string.Format("")); //lst_xls.Add(string.Format("")); lst_xls.Clear(); max_val = llc[2].Get_Maximum_Load(); val = (max_val * 10 / 2.0); //lst_xls.Add(string.Format("85")); lst_xls.Add(string.Format("G363")); lst_xls.Add(string.Format("M363")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = val.ToString(); } lst_xls.Clear(); //lst_xls.Add(string.Format("2.600")); lst_xls.Add(string.Format("I12")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[0].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("0")); lst_xls.Add(string.Format("B24")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[1].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("250")); lst_xls.Add(string.Format("L30")); lst_xls.Add(string.Format("G370")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[5].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("500")); lst_xls.Add(string.Format("L31")); lst_xls.Add(string.Format("M370")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[6].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("1.200")); lst_xls.Add(string.Format("L32")); lst_xls.Add(string.Format("G371")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[7].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("1.800")); lst_xls.Add(string.Format("L33")); lst_xls.Add(string.Format("M108")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[8].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("0.400")); lst_xls.Add(string.Format("L34")); lst_xls.Add(string.Format("G372")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[9].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("5.273")); lst_xls.Add(string.Format("G166")); lst_xls.Add(string.Format("G232")); lst_xls.Add(string.Format("G364")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[2].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("810")); lst_xls.Add(string.Format("G164")); lst_xls.Add(string.Format("G365")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[10].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("1.22")); lst_xls.Add(string.Format("G165")); lst_xls.Add(string.Format("G234")); lst_xls.Add(string.Format("G366")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[3].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("1.93")); lst_xls.Add(string.Format("I166")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[11].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("1.25")); lst_xls.Add(string.Format("I167")); lst_xls.Add(string.Format("G235")); lst_xls.Add(string.Format("G300")); lst_xls.Add(string.Format("G367")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[4].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("360")); lst_xls.Add(string.Format("G233")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[12].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("840")); lst_xls.Add(string.Format("G298")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[13].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("30")); lst_xls.Add(string.Format("G299")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[14].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("4570")); lst_xls.Add(string.Format("M298")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[15].Input_Value; } #endregion Live Loads #region Design User Inputs if (Deckslab_Design_Inputs != null) { myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["5.Design"]; lst_xls.Clear(); //lst_xls.Add(string.Format("16")); lst_xls.Add(string.Format("G87")); lst_xls.Add(string.Format("E111")); lst_xls.Add(string.Format("E115")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[0].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("1000")); lst_xls.Add(string.Format("B92")); lst_xls.Add(string.Format("N205")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[1].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("10")); lst_xls.Add(string.Format("I111")); lst_xls.Add(string.Format("I115")); lst_xls.Add(string.Format("F134")); lst_xls.Add(string.Format("F141")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[2].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("200")); lst_xls.Add(string.Format("G111")); lst_xls.Add(string.Format("G115")); lst_xls.Add(string.Format("K111")); lst_xls.Add(string.Format("K115")); lst_xls.Add(string.Format("H134")); lst_xls.Add(string.Format("H141")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[3].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("0.8")); lst_xls.Add(string.Format("N188")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[4].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("0.5")); lst_xls.Add(string.Format("N191")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[5].Input_Value; } lst_xls.Clear(); //lst_xls.Add(string.Format("0.5")); lst_xls.Add(string.Format("N216")); for (i = 0; i < lst_xls.Count; i++) { myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[6].Input_Value; } } #endregion User Inputs try { myExcelWorkbook.Save(); //myExcelWorkbook.Close(true, fileName, null); Marshal.ReleaseComObject(myExcelWorkbook); } catch (Exception ex) { } #endregion Update_ExcelData }