public ExcelWorkbook(string filename, string password = "") { ExcelApplication = new Application { DisplayAlerts = false }; ExcelWorkbooks = ExcelApplication.Workbooks; if (File.Exists(filename)) { try { Workbook = ExcelWorkbooks.Open(Filename: filename, UpdateLinks: false, ReadOnly: false, Password: password); } catch (Exception ex) { disposedValue = false; Dispose(true); throw new LocalSystemException("Could not open locked Excel file : " + filename, ex); } } else { try { Workbook = ExcelWorkbooks.Add(XlWBATemplate.xlWBATWorksheet); Save(filename, true, password); Workbook.Close(); Cleanup.ReleaseObject(Workbook); Workbook = ExcelWorkbooks.Open(Filename: xSaveName, UpdateLinks: false, ReadOnly: false, Password: password); } catch (Exception ex) { disposedValue = false; Dispose(true); throw new LocalSystemException("Could not create new Excel file : " + filename, ex); } } Worksheets = Workbook.Worksheets; }
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; ExcelApp = CreateExcelObj(); ExcelWorkbooks = ExcelApp.Workbooks; ExcelApp.ScreenUpdating = false; ExcelApp.DisplayAlerts = false; ExcelWorkbook = ExcelWorkbooks.Add(); 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"; 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; } 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) { throw ex; } finally { while (Marshal.ReleaseComObject(ExcelWorkbook) > 0) { } while (Marshal.ReleaseComObject(ExcelWorkbooks) > 0) { } ExcelApp.Quit(); while (Marshal.ReleaseComObject(ExcelApp) > 0) { } GC.Collect(); } }