Exemplo n.º 1
0
 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);
        }
Exemplo n.º 3
0
        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;
        }
Exemplo n.º 5
0
        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();
            }
        }