private static void FormatSheetForSet1(Excel.Worksheet sheet, SetItem obj) { // formatting All sheet sheet.PageSetup.PrintGridlines = false; ; sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait; sheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; sheet.PageSetup.RightFooter = "Дата: &DD Стр &PP из &NN"; sheet.PageSetup.RightHeader = "Исследование " + obj.Project + ", сет № " + obj.Set + " - " + obj.TestMethod + " - " + obj.AB; sheet.PageSetup.Zoom = false; sheet.PageSetup.LeftHeader = "НИИ Антимикробной химиотерапии"; sheet.PageSetup.TopMargin = 50; sheet.PageSetup.BottomMargin = 50; sheet.PageSetup.HeaderMargin = 20; sheet.PageSetup.FooterMargin = 20; sheet.PageSetup.RightMargin = 10; sheet.PageSetup.LeftMargin = 50; sheet.PageSetup.Order = Excel.XlOrder.xlOverThenDown; //// Foramatting test method //sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, obj.MICList.Count + 5]].Merge(); //FormatHeaderText1(sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, 3]]); //// Formatting Set Number //sheet.Range[sheet.Cells[3, 1], sheet.Cells[3, 4]].Merge(); //FormatHeaderText1(sheet.Range[sheet.Cells[3, 1], sheet.Cells[3, 1]]); //// Formatting Set Number //sheet.Range[sheet.Cells[3, 5], sheet.Cells[3, obj.MICList.Count + 5]].Merge(); //FormatHeaderText1(sheet.Range[sheet.Cells[3, 5], sheet.Cells[3, obj.MICList.Count + 5]]); //Formatting table with MO FormatTableCells1(sheet.Range[sheet.Cells[1, 1], sheet.Cells[1 + obj.MOList.Count, obj.MICList.Count + 5]]); sheet.PageSetup.Zoom = false; sheet.PageSetup.FitToPagesWide = 1; // sheet.PageSetup.FitToPagesTall = 0; //Formatting Control MO Header sheet.Range[sheet.Cells[2 + obj.MOList.Count, 1], sheet.Cells[2 + obj.MOList.Count, obj.MICList.Count + 5]].Merge(); sheet.Range[sheet.Cells[2 + obj.MOList.Count, 1], sheet.Cells[2 + obj.MOList.Count, obj.MICList.Count + 5]].RowHeight = 15; FormatHeaderControlMOText1( sheet.Range[ sheet.Cells[2 + obj.MOList.Count, 1], sheet.Cells[2 + obj.MOList.Count, obj.MICList.Count + 5]]); // Formatting table with control MO FormatTableCells1(sheet.Range[sheet.Cells[1 + obj.MOList.Count + 1, 1], sheet.Cells[1 + obj.MOList.Count + 1 + obj.ControlMOList.Count, obj.MICList.Count + 5]]); FormatHeaderControlMOText1(sheet.Range[ sheet.Cells[3 + obj.MOList.Count, 2], sheet.Cells[1 + obj.MOList.Count + obj.ControlMOList.Count, 4]]); //Formatting Top Row sheet.Range[sheet.Cells[1, 1], sheet.Cells[5, obj.MICList.Count + 5]].ColumnWidth = 6; //Formatting Left Columns sheet.Range[sheet.Cells[1, 1], sheet.Cells[5 + obj.MOList.Count, 1]].ColumnWidth = 6; sheet.Range[sheet.Cells[1, 2], sheet.Cells[5 + obj.MOList.Count, 2]].ColumnWidth = 8; sheet.Range[sheet.Cells[1, 3], sheet.Cells[5 + obj.MOList.Count, 3]].ColumnWidth = 8; sheet.Range[sheet.Cells[1, 4], sheet.Cells[5 + obj.MOList.Count, 4]].ColumnWidth = 14; //Formatting Right Columns sheet.Range[sheet.Cells[1, obj.MICList.Count + 5], sheet.Cells[1 + obj.MOList.Count, obj.MICList.Count + 5]].ColumnWidth = 8; sheet.Cells[obj.MOList.Count + obj.ControlMOList.Count + 3, 2] = "Проверил:"; // Разбиваем на две части if (obj.MOList.Count > 48) { sheet.ResetAllPageBreaks(); // sheet.DisplayPageBreaks = true; sheet.HPageBreaks.Add(sheet.Cells[50, 1]); } }
private void format(string filename) { double f; sttLabel1.Visible = true; Excel.Application ex = new Excel.Application(); Excel.Workbook wb = ex.Workbooks.Open(filename); Excel.Worksheet atws = wb.ActiveSheet; sttLabel1.Visible = false; progressbar1.Visible = true; for (int i = 1; i <= wb.Worksheets.Count; i++) { try { f = (float)(i * 100 / wb.Worksheets.Count); progressbar1.Value = (int)f; atws = wb.Worksheets[i]; if (atws.Visible == Excel.XlSheetVisibility.xlSheetHidden) { continue; } atws.Activate(); Excel.Range range = atws.Range[atws.PageSetup.PrintArea]; if (cb_border.Checked) { range.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium); } if (cb_break.Checked) { atws.ResetAllPageBreaks(); } if (cb_width.Checked) { atws.PageSetup.Zoom = false; atws.PageSetup.FitToPagesWide = 1; atws.PageSetup.FitToPagesTall = false; } if (cb_home.Checked) { atws.get_Range("a1", "a1").Select(); } if (cb_zoom.Checked) { wb.Windows[1].Zoom = nm_percent.Value; } if (cb_Font.Checked) { if (cb_coverpage.Checked && i == 1) { continue; } atws.Rows.Font.Name = cm_font.Text; atws.Rows.Font.Size = nr_size.Value; for (int j = 1; j <= atws.Shapes.Count; j++) { try { atws.Shapes.Item(j).TextFrame2.TextRange.Font.NameComplexScript = cm_font.Text; atws.Shapes.Item(j).TextFrame2.TextRange.Font.NameFarEast = cm_font.Text; atws.Shapes.Item(j).TextFrame2.TextRange.Font.NameAscii = cm_font.Text; atws.Shapes.Item(j).TextFrame2.TextRange.Font.NameOther = cm_font.Text; atws.Shapes.Item(j).TextFrame2.TextRange.Font.Name = cm_font.Text; atws.Shapes.Item(j).TextFrame2.TextRange.Font.Size = (int)nr_size.Value; } catch (Exception) { continue; } } } } catch (Exception) { continue; } } atws = wb.Worksheets[1]; atws.Activate(); wb.Close(SaveChanges: true); progressbar1.Visible = false; sttLabel1.Text = "Done"; sttLabel1.Visible = true; MessageBox.Show("Done", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); }