private void protectWS(Excel.Worksheet pWS, String pPwdWrite) { if((pWS != null) && (pPwdWrite != null)) { Object vWritePwd = Type.Missing; if(pPwdWrite != null) vWritePwd = pPwdWrite; pWS.Protect( vWritePwd, true, true, true, false, true, true, true, false, false, false, false, false, true, true, true ); } }
private Excel.Worksheet createList(Excel.Worksheet sheet, string name) { Excel.Worksheet origin = (Excel.Worksheet)application.ActiveSheet; sheet = (Excel.Worksheet)application.ActiveWorkbook.Worksheets.Add(Type.Missing, application.ActiveSheet, Type.Missing, Type.Missing); sheet.Name = name; Excel.Range column = ((Excel.Range)sheet.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; //Properties/Values column.Next.Next.Next.Value2 = "Properties/Values"; column.Next.Next.Next.Font.Bold = true; column.Next.Next.Next.Font.Background = Excel.XlBackground.xlBackgroundOpaque; column.Next.Next.Next.EntireColumn.ColumnWidth = 50; 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; sheet.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(); return sheet; }
/// <summary> /// Schreibt einen Wert in die Zelle des gegebenen Excel-Sheets /// </summary> /// <param name="sheet">Das Excel Sheet.</param> /// <param name="zelle">Die Zelle, z. B. A2.</param> /// <param name="value">Den Wert der Zelle als String.</param> public void WriteValueProtectedCell(Excel.Worksheet sheet, string zelle, string value) { sheet.Unprotect("1111"); Excel.Range r = sheet.get_Range(zelle, missing); r.Value2 = value; sheet.Protect("1111", false, true); }
/// <summary> ///设置保护工作表 /// </summary> private void LockExcel(Excel.Workbook wb, Excel.Worksheet xx) { xx.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing); xx.EnableSelection = Microsoft.Office.Interop.Excel.XlEnableSelection.xlNoSelection; wb.SheetBeforeDoubleClick += new Microsoft.Office.Interop.Excel.WorkbookEvents_SheetBeforeDoubleClickEventHandler(wb_SheetBeforeDoubleClick); //wb.SheetDeactivate += new Microsoft.Office.Interop.Excel.WorkbookEvents_SheetDeactivateEventHandler(Workbook_SheetDeactivate); //wb.SheetActivate += new Microsoft.Office.Interop.Excel.WorkbookEvents_SheetActivateEventHandler(Workbook_SheetActivate); //wb.SheetSelectionChange += new Microsoft.Office.Interop.Excel.WorkbookEvents_SheetSelectionChangeEventHandler(Workbook_SheetSelectionChange); }
private void FillXlsSheet(string screenshotBasePath, List<string> cultures, KeyValuePair<string, string> filesource, Excel.Worksheet sheet) { // add filesource metadata Excel.Range filesourcecell = sheet.Cells[ExcelMetadataRow, ExcelFilesourceColumn] as Excel.Range; filesourcecell.Value2 = filesource.Value; filesourcecell.Font.Italic = true; // add headers and culture metadata sheet.Cells[ExcelHeaderRow, ExcelKeyColumn] = "Key"; sheet.Cells[ExcelHeaderRow, ExcelCommentColumn] = "Comment"; sheet.Cells[ExcelHeaderRow, ExcelValueColumn] = "Value"; int index = ExcelCultureColumn; foreach (var culture in this.exportCultures) { sheet.Cells[ExcelHeaderRow, index] = culture.DisplayName; sheet.Cells[ExcelMetadataRow, index] = culture.Name; index++; } // make header bold and metadata italic var metadatarow = sheet.Rows[RowIndex: ExcelMetadataRow] as Excel.Range; metadatarow.Font.Italic = true; metadatarow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); metadatarow.Locked = true; metadatarow.Hidden = true; var headerrow = sheet.Rows[RowIndex: ExcelHeaderRow] as Excel.Range; headerrow.Font.Bold = true; headerrow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); headerrow.Locked = true; // set border var borders = headerrow.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom); borders.LineStyle = Excel.XlLineStyle.xlContinuous; borders.Weight = Excel.XlBorderWeight.xlMedium; // add actual data int row = ExcelDataRow; foreach (var r in this.PrimaryTranslation.Where(r => r.ResxRow.FileSource == filesource.Value).OrderBy(r => r.Key)) { sheet.Cells[row, ExcelKeyColumn] = r.Key; sheet.Cells[row, ExcelCommentColumn] = r.Comment; sheet.Cells[row, ExcelValueColumn] = r.Value.Replace(@"\r\n", Environment.NewLine); SecondaryTranslationRow[] rows = r.GetResxLocalizedRows(); // Set background and unlock culture cells for (int i = 0; i < cultures.Count; i++) { var cell = sheet.Cells[row, ExcelCultureColumn + i] as Excel.Range; var color = r.Implicit ? System.Drawing.Color.Bisque : System.Drawing.Color.Yellow; cell.Interior.Color = System.Drawing.ColorTranslator.ToOle(color); cell.Locked = false; Marshal.ReleaseComObject(cell); } foreach (SecondaryTranslationRow lr in rows) { int col = cultures.IndexOf(lr.Culture); if (col >= 0) { if (!string.IsNullOrEmpty(lr.Value)) { var cell = sheet.Cells[row, ExcelCultureColumn + col] as Excel.Range; var color = r.Implicit ? System.Drawing.Color.LightBlue : System.Drawing.Color.YellowGreen; cell.Interior.Color = System.Drawing.ColorTranslator.ToOle(color); sheet.Cells[row, col + ExcelCultureColumn] = lr.Value; Marshal.ReleaseComObject(cell); } } } row++; } sheet.Cells.get_Range("A1", "Z1").EntireColumn.AutoFit(); sheet.Cells.get_Range("A1", "Z1").EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; // Insert screenshots var sheetScreenshotDirectory = Path.Combine(screenshotBasePath, filesource.Key); if (Directory.Exists(sheetScreenshotDirectory)) { Excel.Range lastcell = sheet.Cells[row, 1] as Excel.Range; double lastCellTopPoints = (double)(lastcell.Top); float offset = (float)lastCellTopPoints + 20; Marshal.ReleaseComObject(lastcell); var screenshotFiles = Directory.GetFiles(sheetScreenshotDirectory) .Where(f => ScreenshotExtensions.Contains(Path.GetExtension(f).ToLower())); foreach (var screenshotFile in screenshotFiles) { int width, height; string resizedPath; bool isTempFile = ImageHelper.GetScaledImage(screenshotFile, ResxData.MaxScreenshotWidth, out width, out height, out resizedPath); sheet.Shapes.AddPicture(resizedPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 10, offset, width, height); offset += height + 10; if (isTempFile) { File.Delete(resizedPath); } } } // Set width of value column var valuecolumn = sheet.Columns.get_Item(ExcelValueColumn) as Excel.Range; valuecolumn.ColumnWidth = 80; // Set width of translated columns for (int i = 0; i < cultures.Count; i++) { var column = sheet.Columns.get_Item(i + ExcelCultureColumn) as Excel.Range; column.ColumnWidth = 60; Marshal.ReleaseComObject(column); } // hide key column if (AppSettings.Default.HideKeys) { var column = sheet.Columns.get_Item(ExcelKeyColumn) as Excel.Range; column.Hidden = true; Marshal.ReleaseComObject(column); } // hide comment column if (AppSettings.Default.HideComments) { var column = sheet.Columns.get_Item(ExcelCommentColumn) as Excel.Range; //column.Hidden = true; column.ColumnWidth = 0; Marshal.ReleaseComObject(column); } sheet.Protect(Password: "", Contents: true, AllowFormattingColumns: true); Marshal.ReleaseComObject(metadatarow); Marshal.ReleaseComObject(headerrow); Marshal.ReleaseComObject(borders); Marshal.ReleaseComObject(valuecolumn); Marshal.ReleaseComObject(sheet); }
private void CreateExplanationSheet(Excel.Worksheet sheet) { sheet.Name = ExplanationSheetName; int rowIndex = 2; int columnIndex = 2; var column = sheet.Columns.get_Item(ExcelValueColumn) as Excel.Range; column.ColumnWidth = 160; column.WrapText = true; sheet.Cells.Font.Size = 16; sheet.Cells[rowIndex++, columnIndex] = ResX.explanation_header; rowIndex++; sheet.Cells[rowIndex++, columnIndex] = ResX.explanation_yellow; sheet.Cells[rowIndex++, columnIndex] = ResX.explanation_green; sheet.Cells[rowIndex++, columnIndex] = ResX.explanation_screenshot1; sheet.Cells[rowIndex++, columnIndex] = ResX.explanation_screenshot2; sheet.Cells[rowIndex++, columnIndex] = ResX.explanation_string_inserts; sheet.Cells[rowIndex++, columnIndex] = ResX.explanation_excel_newline; sheet.Protect(Password: "", Contents: true, AllowFormattingColumns: true, AllowFormattingRows: true); Marshal.ReleaseComObject(column); }