public static string GetExcelDocumentSetPDF(SetViewModel obj, string filePath, int setType)
{
try
{
Document doc = CreatePDF(obj);
MigraDoc.DocumentObjectModel.IO.DdlWriter.WriteToFile(doc, "MigraDoc.mdddl");
PdfDocumentRenderer renderer = new PdfDocumentRenderer(true, PdfSharp.Pdf.PdfFontEmbedding.Always);
renderer.Document = doc;
renderer.RenderDocument();
if (String.IsNullOrEmpty(filePath))
{
filePath = Directory.GetParent(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)).FullName;
//if (Environment.OSVersion.Version.Major >= 6)
//{
// filePath = Directory.GetParent(filePath).FullName;
//}
}
filename = obj.Set.First().Project + " - Сет " + obj.Set.First().Set + " - " + obj.Set.First().TestMethod + ".pdf";
renderer.PdfDocument.Save(filePath + "\\" + filename);
// ...and start a viewer.
Process.Start(filePath + "\\" + filename);
return(filePath + "\\" + filename);
}
catch (Exception ex)
{
return(null);
}
}
private static void FormatSheetForSet2(Excel.Worksheet sheet, SetViewModel obj)
{
// formatting All sheet
sheet.PageSetup.PrintGridlines = false;
sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
sheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
sheet.PageSetup.RightFooter = "Дата: &DD Стр &PP из &NN";
sheet.PageSetup.RightHeader = "Исследование " + obj.Set.First().Project + ", сет № " + obj.Set.First().Set + " - " + obj.Set.First().TestMethod +
" - " + obj.Set.First().AB;
sheet.PageSetup.Zoom = false;
sheet.PageSetup.LeftHeader = "НИИ Антимикробной химиотерапии";
sheet.PageSetup.TopMargin = 5;
sheet.PageSetup.BottomMargin = 5;
sheet.PageSetup.HeaderMargin = 2;
sheet.PageSetup.FooterMargin = 2;
sheet.PageSetup.RightMargin = 1;
sheet.PageSetup.LeftMargin = 5;
sheet.PageSetup.Order = Excel.XlOrder.xlOverThenDown;
// Formatting Set Number
sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, 3]].Merge();
FormatHeaderText1(sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, 3]]);
// Formatting Footer cell
sheet.Range[sheet.Cells[3 + obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count, 1], sheet.Cells[3 + obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count, 3]].Merge();
FormatHeaderText1(sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, 3]]);
//Formatting table with MO
FormatTableCells2(sheet.Range[sheet.Cells[1, 1], sheet.Cells[3 + obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count, obj.Set.Count + 3]]);
//Formatting AB Header
sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, obj.Set.Count() + 3]].RowHeight = 25;
sheet.Range[sheet.Cells[2, 1], sheet.Cells[2, obj.Set.Count() + 3]].RowHeight = 80;
sheet.Range[sheet.Cells[2, 4], sheet.Cells[2, obj.Set.Count() + 3]].Orientation = 90;
sheet.Range[sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3
, 4], sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3, obj.Set.Count() + 3]].RowHeight = 80;
sheet.Range[sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3
, 4], sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3, obj.Set.Count() + 3]].Orientation = 90;
sheet.Range[sheet.Cells[2, 1], sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3, 1]].ColumnWidth = 5;
sheet.Range[sheet.Cells[2, 2], sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3, 2]].ColumnWidth = 9;
sheet.Range[sheet.Cells[2, 3], sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3, 3]].ColumnWidth = 15;
FormatHeaderControlMOText2(sheet.Range[sheet.Cells[obj.Set.First().MOList.Count + 3, 1], sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3, 3 + obj.Set.Count]]);
sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 7, 2] = "Проверил:";
//sheet.PageSetup.PrintArea = sheet.Range[
// sheet.Cells[1,1],
// sheet.Cells[obj.Set.First().ControlMOList.Count + obj.Set.First().MOList.Count + 5, obj.Set.First().MICList.Count() +5]
// ];
}
private static object[,] PrepareListForSet2(SetViewModel obj)
{
var rowsCount = obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3;
var columnsCount = obj.Set.Count + 3;
object[,] data = new object[rowsCount, columnsCount];
data[0, 0] = "Сет № " + obj.Set.First().Set;
data[1, 0] = "№";
data[1, 1] = "Муз. №";
data[1, 2] = "МО";
for (int i = 0; i < obj.Set.Count; i++)
{
data[0, 3 + i] = obj.Set[i].AB;
data[1, 3 + i] = obj.Set[i].MICList.First().ToString() + " - " + obj.Set[i].MICList.Last().ToString();
data[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 2, 3 + i] = obj.Set.First().ControlMICList.First().ToString() + " - " +
obj.Set[i].ControlMICList.Last().ToString();
}
for (int i = 0; i < obj.Set.First().MOList.Count; i++)
{
data[i + 2, 0] = obj.Set.First().MOList[i].Number;
data[i + 2, 1] = obj.Set.First().MOList[i].MuseumNumber;
data[i + 2, 2] = obj.Set.First().MOList[i].MO;
}
for (int i = 0; i < obj.Set.First().ControlMOList.Count; i++)
{
data[i + 2 + obj.Set.First().MOList.Count, 0] = obj.Set.First().ControlMOList[i].Number;
data[i + 2 + obj.Set.First().MOList.Count, 1] = obj.Set.First().ControlMOList[i].MuseumNumber;
data[i + 2 + obj.Set.First().MOList.Count, 2] = obj.Set.First().ControlMOList[i].MO;
}
return(data);
}
private static object[,] PrepareListForSet2(SetViewModel obj)
{
var rowsCount = obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3;
var columnsCount = obj.Set.Count + 3;
object[,] data = new object[rowsCount, columnsCount];
data[0, 0] = "Сет № " + obj.Set.First().Set;
data[1, 0] = "№";
data[1, 1] = "Муз. №";
data[1, 2] = "МО";
for (int i = 0; i < obj.Set.Count; i++)
{
data[0, 3 + i] = obj.Set[i].AB;
data[1, 3 + i] = obj.Set[i].MICList.First().ToString() + " - " + obj.Set[i].MICList.Last().ToString();
data[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 2, 3 + i] = obj.Set.First().ControlMICList.First().ToString() + " - " +
obj.Set[i].ControlMICList.Last().ToString();
}
for (int i = 0; i < obj.Set.First().MOList.Count; i++)
{
data[i + 2, 0] = obj.Set.First().MOList[i].Number;
data[i + 2, 1] = obj.Set.First().MOList[i].MuseumNumber;
data[i + 2, 2] = obj.Set.First().MOList[i].MO;
}
for (int i = 0; i < obj.Set.First().ControlMOList.Count; i++)
{
data[i + 2 + obj.Set.First().MOList.Count, 0] = obj.Set.First().ControlMOList[i].Number;
data[i + 2 + obj.Set.First().MOList.Count, 1] = obj.Set.First().ControlMOList[i].MuseumNumber;
data[i + 2 + obj.Set.First().MOList.Count, 2] = obj.Set.First().ControlMOList[i].MO;
}
return data;
}
private static void FormatSheetForSet2(Excel.Worksheet sheet, SetViewModel obj)
{
// formatting All sheet
sheet.PageSetup.PrintGridlines = false;
sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
sheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
sheet.PageSetup.RightFooter = "Дата: &DD Стр &PP из &NN";
sheet.PageSetup.RightHeader = "Исследование " + obj.Set.First().Project + ", сет № " + obj.Set.First().Set + " - " + obj.Set.First().TestMethod +
" - " + obj.Set.First().AB;
sheet.PageSetup.Zoom = false;
sheet.PageSetup.LeftHeader = "НИИ Антимикробной химиотерапии";
sheet.PageSetup.TopMargin = 5;
sheet.PageSetup.BottomMargin = 5;
sheet.PageSetup.HeaderMargin = 2;
sheet.PageSetup.FooterMargin = 2;
sheet.PageSetup.RightMargin = 1;
sheet.PageSetup.LeftMargin = 5;
sheet.PageSetup.Order = Excel.XlOrder.xlOverThenDown;
// Formatting Set Number
sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, 3]].Merge();
FormatHeaderText1(sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, 3]]);
// Formatting Footer cell
sheet.Range[sheet.Cells[3 + obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count, 1], sheet.Cells[3 + obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count, 3]].Merge();
FormatHeaderText1(sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, 3]]);
//Formatting table with MO
FormatTableCells2(sheet.Range[sheet.Cells[1, 1], sheet.Cells[3 + obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count, obj.Set.Count + 3]]);
//Formatting AB Header
sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, obj.Set.Count() + 3]].RowHeight = 25;
sheet.Range[sheet.Cells[2, 1], sheet.Cells[2, obj.Set.Count() + 3]].RowHeight = 80;
sheet.Range[sheet.Cells[2, 4], sheet.Cells[2, obj.Set.Count() + 3]].Orientation = 90;
sheet.Range[sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3
, 4], sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3, obj.Set.Count() + 3]].RowHeight = 80;
sheet.Range[sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3
, 4], sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3, obj.Set.Count() + 3]].Orientation = 90;
sheet.Range[sheet.Cells[2, 1], sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3, 1]].ColumnWidth = 5;
sheet.Range[sheet.Cells[2, 2], sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3, 2]].ColumnWidth = 9;
sheet.Range[sheet.Cells[2, 3], sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3, 3]].ColumnWidth = 15;
FormatHeaderControlMOText2(sheet.Range[sheet.Cells[obj.Set.First().MOList.Count + 3, 1], sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3, 3 + obj.Set.Count]]);
sheet.Cells[obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 7, 2] = "Проверил:";
//sheet.PageSetup.PrintArea = sheet.Range[
// sheet.Cells[1,1],
// sheet.Cells[obj.Set.First().ControlMOList.Count + obj.Set.First().MOList.Count + 5, obj.Set.First().MICList.Count() +5]
// ];
}
public static string GetExcelDocumentSet(SetViewModel obj, string filePath, int setType)
{
Excel.Application ExcelApp;
Excel.Worksheet ExcelSheet;
Excel.Workbook ExcelWorkbook;
Excel.Workbooks ExcelWorkbooks;
Excel.Range ExcelRange;
int rowsCount;
int columnsCount;
dynamic data;
try
{
if (String.IsNullOrEmpty(filePath))
{
filePath = Directory.GetParent(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)).FullName;
//if (Environment.OSVersion.Version.Major >= 6)
//{
// filePath = Directory.GetParent(filePath).FullName;
//}
}
filename = obj.Set.First().Project + " - Сет " + obj.Set.First().Set + " - " + obj.Set.First().TestMethod + ".xlsx";
ExcelApp = CreateExcelObj();
ExcelWorkbooks = ExcelApp.Workbooks;
ExcelApp.ScreenUpdating = false;
// ExcelApp.PrintCommunication = false;
ExcelApp.DisplayAlerts = false;
ExcelWorkbook = ExcelWorkbooks.Add();
switch (setType)
{
case 1:
foreach (var itemSet in obj.Set)
{
ExcelSheet = ExcelWorkbook.Sheets.Add();
rowsCount = itemSet.MOList.Count + 8 + itemSet.ControlMOList.Count + 1;
columnsCount = itemSet.MICList.Count + 5;
ExcelRange =
ExcelSheet.Range[ExcelSheet.Cells[1, 1], ExcelSheet.Cells[rowsCount, columnsCount]];
if (
itemSet.AB.Length > 30)
{
ExcelSheet.Name = itemSet.AB.Substring(0, 30).Replace("/", "|").Replace("\\", "|");
}
else
{
ExcelSheet.Name = itemSet.AB.Replace("/", "|").Replace("\\", "|");
}
data = PrepareListForSet1(itemSet);
ExcelRange.Value = data;
FormatSheetForSet1(ExcelSheet, itemSet);
Marshal.ReleaseComObject(ExcelRange);
Marshal.ReleaseComObject(ExcelSheet);
}
break;
case 2:
ExcelSheet = ExcelWorkbook.Sheets.Add();
rowsCount = obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3;
columnsCount = obj.Set.Count + 3;
ExcelRange = ExcelSheet.Range[ExcelSheet.Cells[1, 1], ExcelSheet.Cells[rowsCount, columnsCount]];
ExcelSheet.Name = obj.Set.First().Project + " - Сет № " + obj.Set.First().Set;
data = PrepareListForSet2(obj);
ExcelRange.Value = data;
FormatSheetForSet2(ExcelSheet, obj);
Marshal.ReleaseComObject(ExcelRange);
Marshal.ReleaseComObject(ExcelSheet);
break;
default:
break;
}
// ExcelApp.PrintCommunication = true;
//ExcelWorkbook.SaveAs();
ExcelWorkbook.SaveAs(filePath + "\\" + filename, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
while (Marshal.ReleaseComObject(ExcelWorkbook) > 0)
{ }
while (Marshal.ReleaseComObject(ExcelWorkbooks) > 0)
{ }
ExcelApp.Quit();
while (Marshal.ReleaseComObject(ExcelApp) > 0)
{ }
return filePath + "\\" + filename;
}
catch (Exception ex)
{
return ex.Data + "\r\n" + ex.Message + "\r\n" + ex.Source + "\r\n" + ex.InnerException + "\r\n" +
ex.StackTrace;
}
finally
{
GC.Collect();
}
}
private static Document CreatePDF(SetViewModel obj)
{
Document doc = new Document();
//Defining styles
Style style = doc.Styles["Normal"];
style.Font.Name = "Arial";
style.Font.Size = 6;
style = doc.Styles[StyleNames.Header];
style.ParagraphFormat.AddTabStop("16cm", TabAlignment.Right);
style = doc.Styles[StyleNames.Footer];
style.ParagraphFormat.AddTabStop("8cm", TabAlignment.Right);
foreach (var itemSet in obj.Set)
{
//Define content section
Section section = doc.AddSection();
section.PageSetup.StartingNumber = 1;
section.PageSetup.LeftMargin = "0.7cm";
section.PageSetup.RightMargin = "0.7cm";
section.PageSetup.BottomMargin = "0.5cm";
section.PageSetup.TopMargin = "1.4cm";
section.PageSetup.FooterDistance = "0.5cm";
section.PageSetup.HeaderDistance = "0.5cm";
// Create Header
Table hTable = section.Headers.Primary.AddTable();
hTable.Borders.Width = 0;
var hlColumn = hTable.AddColumn("6cm");
hlColumn.Format.Alignment = ParagraphAlignment.Left;
var hcColumn = hTable.AddColumn("8cm");
hlColumn.Format.Alignment = ParagraphAlignment.Center;
var hrColumn = hTable.AddColumn("6cm");
hrColumn.Format.Alignment = ParagraphAlignment.Right;
var hRow = hTable.AddRow();
var hCell = hRow.Cells[0];
hCell.AddParagraph("НИИ Антимикробной химиотерапии");
hCell.Format.Alignment = ParagraphAlignment.Left;
hCell = hRow.Cells[1];
hCell.AddParagraph(itemSet.AB);
hCell.Format.Alignment = ParagraphAlignment.Center;
hCell = hRow.Cells[2];
hCell.AddParagraph("Исследование " + itemSet.Project);
hCell.Format.Alignment = ParagraphAlignment.Right;
hRow = hTable.AddRow();
hCell = hRow.Cells[0];
hCell.AddParagraph("Метод тестирования: " + itemSet.TestMethod);
hCell.Format.Alignment = ParagraphAlignment.Left;
hCell = hRow.Cells[2];
hCell.AddParagraph("Сет: " + itemSet.Set + " - " + "Антибиотик: " + itemSet.AB);
hCell.Format.Alignment = ParagraphAlignment.Right;
hRow = hTable.AddRow();
hCell = hRow.Cells[0];
hCell.AddParagraph("Дата печати: " + DateTime.Now.ToShortDateString());
hCell.Format.Alignment = ParagraphAlignment.Left;
Paragraph fPar = new Paragraph();
fPar.AddText("Страница ");
fPar.AddPageField();
fPar.AddText(" из ");
fPar.AddSectionPagesField();
hCell = hRow.Cells[2];
hCell.Add(fPar);
hCell.Format.Alignment = ParagraphAlignment.Right;
hCell = hTable.Rows[0].Cells[1];
hCell.MergeDown = 2;
hCell.Format.Font.Bold = true;
hCell.Format.Font.Size = 9;
hCell.Format.Alignment = ParagraphAlignment.Center;
// Create table
Table table;
Row row;
Cell cell;
CreateMicTable(doc, itemSet, section, out table, out row, out cell);
foreach (var item in itemSet.MOList)
{
row = table.AddRow();
row.Height = "0.55cm";
cell = row.Cells[0];
cell.VerticalAlignment = VerticalAlignment.Center;
cell.AddParagraph(item.Cell);
cell = row.Cells[1];
cell.VerticalAlignment = VerticalAlignment.Center;
cell.AddParagraph(item.MuseumNumber);
cell = row.Cells[2];
cell.VerticalAlignment = VerticalAlignment.Center;
cell.AddParagraph(item.MO);
if (item.Number % 12 == 0)
{
row.Borders.Bottom.Width = "0.05cm";
}
if ((item.Number % 48 == 0) && (item.Number > 10))
{
doc.LastSection.Add(table);
doc.LastSection.AddPageBreak();
CreateMicTable(doc, itemSet, section, out table, out row, out cell);
}
}
foreach (var item in itemSet.ControlMOList)
{
row = table.AddRow();
row.Height = "0.5cm";
cell = row.Cells[0];
cell.VerticalAlignment = VerticalAlignment.Center;
cell.AddParagraph(item.Cell);
cell.Shading.Color = Colors.LightGray;
cell = row.Cells[1];
cell.VerticalAlignment = VerticalAlignment.Center;
cell.AddParagraph(item.MuseumNumber);
cell.Shading.Color = Colors.LightGray;
cell = row.Cells[2];
cell.VerticalAlignment = VerticalAlignment.Center;
cell.Shading.Color = Colors.LightGray;
cell.AddParagraph(item.MO);
}
doc.LastSection.Add(table);
}
return doc;
}
public static string GetExcelDocumentSetPDF(SetViewModel obj, string filePath, int setType)
{
try
{
Document doc = CreatePDF(obj);
MigraDoc.DocumentObjectModel.IO.DdlWriter.WriteToFile(doc, "MigraDoc.mdddl");
PdfDocumentRenderer renderer = new PdfDocumentRenderer(true, PdfSharp.Pdf.PdfFontEmbedding.Always);
renderer.Document = doc;
renderer.RenderDocument();
if (String.IsNullOrEmpty(filePath))
{
filePath = Directory.GetParent(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)).FullName;
//if (Environment.OSVersion.Version.Major >= 6)
//{
// filePath = Directory.GetParent(filePath).FullName;
//}
}
filename = obj.Set.First().Project + " - Сет " + obj.Set.First().Set + " - " + obj.Set.First().TestMethod + ".pdf";
renderer.PdfDocument.Save(filePath + "\\" + filename);
// ...and start a viewer.
Process.Start(filePath + "\\" + filename);
return filePath + "\\" + filename;
}
catch (Exception ex)
{
return null;
}
}
private void Button_Click(object sender, RoutedEventArgs e)
{
SetViewModel data = GenerateSet();
MessageBox.Show(ExportToExcel.GetExcelDocumentSetPDF(data, null, 1));
}
private SetViewModel GenerateSet()
{
double startMIC = 0.125;
var MIClist = new List <double>();
for (int i = 1; i <= 10; i++)
{
MIClist.Add(startMIC);
startMIC = startMIC * 2;
}
startMIC = 0.03125;
var ControlMIClist = new List <double>();
for (int i = 1; i <= 10; i++)
{
ControlMIClist.Add(startMIC);
startMIC = startMIC * 2;
}
var MOlist = new List <SetRow>();
for (int i = 2; i <= 93; i++)
{
MOlist.Add(new SetRow
{
Cell = "A" + i,
MO = "Oranism " + i,
MuseumNumber = "MARAFON-" + (120 + i).ToString(),
Number = i
});
}
var ControlMOlist = new List <SetRow>();
for (int i = 1; i <= 3; i++)
{
ControlMOlist.Add(new SetRow
{
Cell = "",
MO = "Control Oranism " + i,
MuseumNumber = "control" + i,
Number = i
});
}
var collection = new ObservableCollection <SetItem>();
for (int i = 0; i <= 10; i++)
{
collection.Add(new SetItem
{
AB = "Antibiotic " + i,
Set = "Set Number " + i,
Project = "Project " + i,
TestMethod = "Метод разведения в агаре",
MICList = MIClist,
ControlMICList = ControlMIClist,
MOList = MOlist,
ControlMOList = ControlMOlist
});
}
var obj = new SetViewModel();
obj.Set = collection;
return(obj);
}
private SetViewModel GenerateSet()
{
double startMIC = 0.125;
var MIClist = new List<double>();
for (int i = 1; i <= 10; i++)
{
MIClist.Add(startMIC);
startMIC = startMIC * 2;
}
startMIC = 0.03125;
var ControlMIClist = new List<double>();
for (int i = 1; i <= 10; i++)
{
ControlMIClist.Add(startMIC);
startMIC = startMIC * 2;
}
var MOlist = new List<SetRow>();
for (int i = 2; i <= 93; i++)
{
MOlist.Add(new SetRow
{
Cell = "A" + i,
MO = "Oranism " + i,
MuseumNumber = "MARAFON-"+(120 + i).ToString(),
Number = i
});
}
var ControlMOlist = new List<SetRow>();
for (int i = 1; i <= 3; i++)
{
ControlMOlist.Add(new SetRow
{
Cell = "",
MO = "Control Oranism " + i,
MuseumNumber = "control" + i,
Number = i
});
}
var collection = new ObservableCollection<SetItem>();
for (int i = 0; i <= 10; i++)
{
collection.Add(new SetItem
{
AB = "Antibiotic " + i,
Set = "Set Number " + i,
Project = "Project " + i,
TestMethod = "Метод разведения в агаре",
MICList = MIClist,
ControlMICList = ControlMIClist,
MOList = MOlist,
ControlMOList = ControlMOlist
});
}
var obj = new SetViewModel();
obj.Set = collection;
return obj;
}
public static string GetExcelDocumentSet(SetViewModel obj, string filePath, int setType)
{
Excel.Application ExcelApp;
Excel.Worksheet ExcelSheet;
Excel.Workbook ExcelWorkbook;
Excel.Workbooks ExcelWorkbooks;
Excel.Range ExcelRange;
int rowsCount;
int columnsCount;
dynamic data;
try
{
if (String.IsNullOrEmpty(filePath))
{
filePath = Directory.GetParent(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)).FullName;
//if (Environment.OSVersion.Version.Major >= 6)
//{
// filePath = Directory.GetParent(filePath).FullName;
//}
}
filename = obj.Set.First().Project + " - Сет " + obj.Set.First().Set + " - " + obj.Set.First().TestMethod + ".xlsx";
ExcelApp = CreateExcelObj();
ExcelWorkbooks = ExcelApp.Workbooks;
ExcelApp.ScreenUpdating = false;
// ExcelApp.PrintCommunication = false;
ExcelApp.DisplayAlerts = false;
ExcelWorkbook = ExcelWorkbooks.Add();
switch (setType)
{
case 1:
foreach (var itemSet in obj.Set)
{
ExcelSheet = ExcelWorkbook.Sheets.Add();
rowsCount = itemSet.MOList.Count + 8 + itemSet.ControlMOList.Count + 1;
columnsCount = itemSet.MICList.Count + 5;
ExcelRange =
ExcelSheet.Range[ExcelSheet.Cells[1, 1], ExcelSheet.Cells[rowsCount, columnsCount]];
if (
itemSet.AB.Length > 30)
{
ExcelSheet.Name = itemSet.AB.Substring(0, 30).Replace("/", "|").Replace("\\", "|");
}
else
{
ExcelSheet.Name = itemSet.AB.Replace("/", "|").Replace("\\", "|");
}
data = PrepareListForSet1(itemSet);
ExcelRange.Value = data;
FormatSheetForSet1(ExcelSheet, itemSet);
Marshal.ReleaseComObject(ExcelRange);
Marshal.ReleaseComObject(ExcelSheet);
}
break;
case 2:
ExcelSheet = ExcelWorkbook.Sheets.Add();
rowsCount = obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3;
columnsCount = obj.Set.Count + 3;
ExcelRange = ExcelSheet.Range[ExcelSheet.Cells[1, 1], ExcelSheet.Cells[rowsCount, columnsCount]];
ExcelSheet.Name = obj.Set.First().Project + " - Сет № " + obj.Set.First().Set;
data = PrepareListForSet2(obj);
ExcelRange.Value = data;
FormatSheetForSet2(ExcelSheet, obj);
Marshal.ReleaseComObject(ExcelRange);
Marshal.ReleaseComObject(ExcelSheet);
break;
default:
break;
}
// ExcelApp.PrintCommunication = true;
//ExcelWorkbook.SaveAs();
ExcelWorkbook.SaveAs(filePath + "\\" + filename, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
while (Marshal.ReleaseComObject(ExcelWorkbook) > 0)
{
}
while (Marshal.ReleaseComObject(ExcelWorkbooks) > 0)
{
}
ExcelApp.Quit();
while (Marshal.ReleaseComObject(ExcelApp) > 0)
{
}
return(filePath + "\\" + filename);
}
catch (Exception ex)
{
return(ex.Data + "\r\n" + ex.Message + "\r\n" + ex.Source + "\r\n" + ex.InnerException + "\r\n" +
ex.StackTrace);
}
finally
{
GC.Collect();
}
}
private static Document CreatePDF(SetViewModel obj)
{
Document doc = new Document();
//Defining styles
Style style = doc.Styles["Normal"];
style.Font.Name = "Arial";
style.Font.Size = 6;
style = doc.Styles[StyleNames.Header];
style.ParagraphFormat.AddTabStop("16cm", TabAlignment.Right);
style = doc.Styles[StyleNames.Footer];
style.ParagraphFormat.AddTabStop("8cm", TabAlignment.Right);
foreach (var itemSet in obj.Set)
{
//Define content section
Section section = doc.AddSection();
section.PageSetup.StartingNumber = 1;
section.PageSetup.LeftMargin = "0.7cm";
section.PageSetup.RightMargin = "0.7cm";
section.PageSetup.BottomMargin = "0.5cm";
section.PageSetup.TopMargin = "1.4cm";
section.PageSetup.FooterDistance = "0.5cm";
section.PageSetup.HeaderDistance = "0.5cm";
// Create Header
Table hTable = section.Headers.Primary.AddTable();
hTable.Borders.Width = 0;
var hlColumn = hTable.AddColumn("6cm");
hlColumn.Format.Alignment = ParagraphAlignment.Left;
var hcColumn = hTable.AddColumn("8cm");
hlColumn.Format.Alignment = ParagraphAlignment.Center;
var hrColumn = hTable.AddColumn("6cm");
hrColumn.Format.Alignment = ParagraphAlignment.Right;
var hRow = hTable.AddRow();
var hCell = hRow.Cells[0];
hCell.AddParagraph("НИИ Антимикробной химиотерапии");
hCell.Format.Alignment = ParagraphAlignment.Left;
hCell = hRow.Cells[1];
hCell.AddParagraph(itemSet.AB);
hCell.Format.Alignment = ParagraphAlignment.Center;
hCell = hRow.Cells[2];
hCell.AddParagraph("Исследование " + itemSet.Project);
hCell.Format.Alignment = ParagraphAlignment.Right;
hRow = hTable.AddRow();
hCell = hRow.Cells[0];
hCell.AddParagraph("Метод тестирования: " + itemSet.TestMethod);
hCell.Format.Alignment = ParagraphAlignment.Left;
hCell = hRow.Cells[2];
hCell.AddParagraph("Сет: " + itemSet.Set + " - " + "Антибиотик: " + itemSet.AB);
hCell.Format.Alignment = ParagraphAlignment.Right;
hRow = hTable.AddRow();
hCell = hRow.Cells[0];
hCell.AddParagraph("Дата печати: " + DateTime.Now.ToShortDateString());
hCell.Format.Alignment = ParagraphAlignment.Left;
Paragraph fPar = new Paragraph();
fPar.AddText("Страница ");
fPar.AddPageField();
fPar.AddText(" из ");
fPar.AddSectionPagesField();
hCell = hRow.Cells[2];
hCell.Add(fPar);
hCell.Format.Alignment = ParagraphAlignment.Right;
hCell = hTable.Rows[0].Cells[1];
hCell.MergeDown = 2;
hCell.Format.Font.Bold = true;
hCell.Format.Font.Size = 9;
hCell.Format.Alignment = ParagraphAlignment.Center;
// Create table
Table table;
Row row;
Cell cell;
CreateMicTable(doc, itemSet, section, out table, out row, out cell);
foreach (var item in itemSet.MOList)
{
row = table.AddRow();
row.Height = "0.55cm";
cell = row.Cells[0];
cell.VerticalAlignment = VerticalAlignment.Center;
cell.AddParagraph(item.Cell);
cell = row.Cells[1];
cell.VerticalAlignment = VerticalAlignment.Center;
cell.AddParagraph(item.MuseumNumber);
cell = row.Cells[2];
cell.VerticalAlignment = VerticalAlignment.Center;
cell.AddParagraph(item.MO);
if (item.Number % 12 == 0)
{
row.Borders.Bottom.Width = "0.05cm";
}
if ((item.Number % 48 == 0) && (item.Number > 10))
{
doc.LastSection.Add(table);
doc.LastSection.AddPageBreak();
CreateMicTable(doc, itemSet, section, out table, out row, out cell);
}
}
foreach (var item in itemSet.ControlMOList)
{
row = table.AddRow();
row.Height = "0.5cm";
cell = row.Cells[0];
cell.VerticalAlignment = VerticalAlignment.Center;
cell.AddParagraph(item.Cell);
cell.Shading.Color = Colors.LightGray;
cell = row.Cells[1];
cell.VerticalAlignment = VerticalAlignment.Center;
cell.AddParagraph(item.MuseumNumber);
cell.Shading.Color = Colors.LightGray;
cell = row.Cells[2];
cell.VerticalAlignment = VerticalAlignment.Center;
cell.Shading.Color = Colors.LightGray;
cell.AddParagraph(item.MO);
}
doc.LastSection.Add(table);
}
return(doc);
}
