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