protected _Worksheet GetExcelSheet(string path) { ExcelApp = StartExcel(); ExcelWorkbooks = ExcelApp.Workbooks; ExcelWorkbook = ExcelWorkbooks.Open(path); return(ExcelWorkbook.Sheets[1]); }
public List <ClienteBean> ReadFromExcel(string keyName) { string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, ExcelPath); List <ClienteBean> clientes = new List <ClienteBean>(); Excel.Application ExcelApp = new Excel.Application(); Excel.Workbooks ExcelWorkbooks; Excel.Workbook ExcelWorkbook; Excel.Worksheet ExcelWorksheet; Excel.Range ExcelRange; ExcelWorkbooks = ExcelApp.Workbooks; ExcelWorkbook = ExcelWorkbooks.Open(path); ExcelWorksheet = ExcelWorkbook.Sheets[1]; ExcelRange = ExcelWorksheet.UsedRange; int rowCount = ExcelRange.Rows.Count; int colCount = ExcelRange.Columns.Count; string[] headers = new string[colCount]; for (int i = 1; i <= colCount; i++) { headers[i - 1] = (Convert.ToString((ExcelRange.Cells[1, i] as Excel.Range).Value2)); } for (int j = 2; j <= rowCount; j++) { string Key = Convert.ToString((ExcelRange.Cells[j, 1] as Excel.Range).Value2); if (Key.Equals(keyName)) { ClienteBean clienteBean = new ClienteBean(); clienteBean.Apellido = Convert.ToString((ExcelRange.Cells[j, 2] as Excel.Range).Value2); clienteBean.Nombre = Convert.ToString((ExcelRange.Cells[j, 3] as Excel.Range).Value2); clienteBean.Dni = Convert.ToString((ExcelRange.Cells[j, 4] as Excel.Range).Value2); clienteBean.Sexo = Convert.ToString((ExcelRange.Cells[j, 5] as Excel.Range).Value2); clienteBean.Nivelestudios = Convert.ToString((ExcelRange.Cells[j, 6] as Excel.Range).Value2); clienteBean.Telefono = Convert.ToString((ExcelRange.Cells[j, 7] as Excel.Range).Value2); clienteBean.Edad = Convert.ToString((ExcelRange.Cells[j, 8] as Excel.Range).Value2); clienteBean.Resultado = Convert.ToString((ExcelRange.Cells[j, 9] as Excel.Range).Value2); clientes.Add(clienteBean); } continue; } ExcelWorkbook.Close(); ExcelWorkbooks.Close(); ExcelApp.Quit(); Marshal.ReleaseComObject(ExcelRange); Marshal.ReleaseComObject(ExcelWorksheet); Marshal.ReleaseComObject(ExcelWorkbooks); Marshal.ReleaseComObject(ExcelWorkbook); Marshal.ReleaseComObject(ExcelApp); return(clientes); }
private void StartExcel() { ExcelApp = new Application(); ExcelWorkbooks = ExcelApp.Workbooks; ExcelWorkbook = ExcelWorkbooks.Open(ExcelTemplatePath); ExcelSheet = ExcelWorkbook.Worksheets[1]; ExcelResources = new List <object>(); ExcelResources.AddRange(new object[] { ExcelApp, ExcelWorkbooks, ExcelWorkbook, ExcelSheet }); }
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(); } }