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);
}
