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