예제 #1
0
 public static int ObtenerIndiceHojaExcel(Excel.Sheets hojasExcel, string nombreBusqueda)
 {
     foreach (Excel.Worksheet hoja in hojasExcel)
     {
         if (hoja.Name.Equals(nombreBusqueda))
         {
             return(hoja.Index);
         }
     }
     return(-1);
 }
예제 #2
0
        private void createExcel()
        {
            Excel.Application excelApp = null;
            Excel.Workbook    workbook = null;
            Excel.Sheets      sheets   = null;
            Excel.Worksheet   newSheet = null;

            try
            {
                FileInfo file = new FileInfo(fileLoc);
                if (file.Exists)
                {
                    excelApp = new Excel.Application();
                    workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "",
                                                       false, XlPlatform.xlWindows, "",
                                                       true, false, 0, true, false, false);

                    sheets = workbook.Sheets;

                    //check columns exist
                    foreach (Excel.Worksheet sheet in sheets)
                    {
                        Console.WriteLine(sheet.Name);
                        sheet.Select(Type.Missing);

                        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    }

                    newSheet             = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    newSheet.Name        = "My New Sheet";
                    newSheet.Cells[1, 1] = "BOO!";

                    workbook.Save();
                    workbook.Close(null, null, null);
                    excelApp.Quit();
                }
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

                newSheet = null;
                sheets   = null;
                workbook = null;
                excelApp = null;

                GC.Collect();
            }
        }
예제 #3
0
 private void Dispose(bool disposing)
 {
     if (disposing)
     {
         // Dispose managed resources.
         Marshal.FinalReleaseComObject(m_objExcel);
         m_objRange  = null;
         m_objSheet  = null;
         m_objSheets = null;
         m_objBooks  = null;
         m_objBook   = null;
         m_objExcel  = null;
     }
 }
예제 #4
0
 /// <summary>
 /// 新建一个Excel实例
 /// </summary>
 /// <param name="strTitle">Excel表头上的文字</param>
 public void DeclareExcelApp(string[] strTitle, string strSql, string strTableName, string strMastTitle)
 {
     m_objExcel         = new Excel.ApplicationClass();
     m_objExcel.Visible = true;
     m_objBooks         = (Excel.Workbooks)m_objExcel.Workbooks;
     m_objBook          = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
     m_objSheets        = (Excel.Sheets)m_objBook.Worksheets;
     if (intSheetTotalSize <= 3)
     {
         if (this.dbTotalSize <= this.dbSheetSize)
         {
             this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
             return;
         }
         else if (this.dbTotalSize <= this.dbSheetSize * 2)
         {
             this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
             this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
             return;
         }
         else
         {
             this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
             this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
             this.ExportDataByQueryTable(3, true, strTitle, strSql, strTableName, strMastTitle);
             return;
         }
     }
     for (int i = 3; i < intSheetTotalSize; i++)
     {
         m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt);
     }
     ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
     for (int i = 2; i <= m_objSheets.Count; i++)
     {
         ExportDataByQueryTable(i, true, strTitle, strSql, strTableName, strMastTitle);
     }
 }
예제 #5
0
        private void OpenTemplate()
        {
            try
            {
                InvoiceTemplate         = new Excel.ApplicationClass();
                InvoiceTemplate.Visible = false;
                //string workbookPath = m_TemplatePath;
                excelWorkbook = InvoiceTemplate.Workbooks.Open(m_TemplatePath,
                                                               0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                                                               true, false, 0, true, false, false);

                InvoiceSheets = InvoiceTemplate.Worksheets;
                string currentSheet = "Page1";
                if (m_iInvoiceType == (int)InvoiceType.ShippingList)
                {
                    currentSheet = "Page2";
                }
                excelWorksheet = (Excel.Worksheet)InvoiceSheets.get_Item(currentSheet);
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }
예제 #6
0
        static void Main(string[] args)
        {
            Console.WriteLine("Interop Performance Test - 10.000 Cells.");

            /*
             * start excel and disable messageboxes and screen updating
             */
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts  = false;
            excelApplication.ScreenUpdating = false;
            //excelApplication.WorkbookActivate += new AppEvents_WorkbookActivateEventHandler(excelApplication_WorkbookActivate);

            /*
             *  create new empty worksheet
             */
            Excel.Workbooks books  = excelApplication.Workbooks;
            Excel.Workbook  book   = books.Add(Missing.Value);
            Excel.Sheets    sheets = book.Worksheets;
            Excel.Worksheet sheet  = (Excel.Worksheet)sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            /*
             *  do the test
             *  we collect all references and release after time recording
             *  the 2 latebind libs release the references at end automaticly and we want a fair test
             */
            List <object> comReferenesList = new List <object>();
            DateTime      timeStart        = DateTime.Now;

            for (int i = 1; i <= 10000; i++)
            {
                string rangeAdress = "$A" + i.ToString();
                Range  cellRange   = (Range)sheet.Range[rangeAdress];
                cellRange.Value        = "value";
                cellRange.Font.Name    = "Verdana";
                cellRange.NumberFormat = "@";
                cellRange.WrapText     = false;
                Comment sampleComment = cellRange.AddComment("Sample Comment");
                comReferenesList.Add(cellRange);
                comReferenesList.Add(sampleComment);
            }
            DateTime timeEnd     = DateTime.Now;
            TimeSpan timeElapsed = timeEnd - timeStart;

            foreach (var item in comReferenesList)
            {
                Marshal.ReleaseComObject(item);
            }

            /*
             * display for user
             */
            string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine);

            Console.WriteLine(outputConsole);
            Console.Read();

            /*
             * write result in logfile
             */
            string logFile       = Path.Combine(Environment.CurrentDirectory, "Interop.log");
            string logFileAppend = timeElapsed.ToString() + Environment.NewLine;

            File.AppendAllText(logFile, logFileAppend, Encoding.UTF8);

            /*
             * release & quit
             */
            Marshal.ReleaseComObject(sheet);
            Marshal.ReleaseComObject(sheets);
            Marshal.ReleaseComObject(book);
            Marshal.ReleaseComObject(books);

            excelApplication.Quit();
            Marshal.ReleaseComObject(excelApplication);
        }
예제 #7
0
        public void OpenExcelAppWithFile(string filePath, int sheetIndex, string sheetName, bool visible)
        {
            _excelApplication = new Excel.Application();
            _excelApplication.Visible = visible;

            _workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
            _workBook = (Excel._Workbook)(_workBooks.Add(filePath));

            _excelSheets = (Excel.Sheets)_workBook.Worksheets;
            _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(sheetIndex));
            _excelSheet.Name = sheetName;
        }
예제 #8
0
        public void OpenExcelApp(bool visible)
        {
            _excelApplication = new Excel.Application();
            _excelApplication.Visible = visible;

            _workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
            _workBook = (Excel._Workbook)(_workBooks.Add(_value));

            _excelSheets = (Excel.Sheets)_workBook.Worksheets;
            _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1));
        }
예제 #9
0
    public static string ProcesarArchivo(string pathArchivo)
    {
        string            msgResul     = "Migración realizada correctamente.";
        ImportadorExcelBE infoImportar = new ImportadorExcelBE();

        UIUtility.COMObjectAplication ObjCom = null;
        Excel.Application             xlApp  = null;

        try
        {
            ObjCom = new UIUtility.COMObjectAplication("Excel.Application", "EXCEL");
            xlApp  = (Excel.Application)ObjCom.ObjetoAplication;

            Excel.Workbooks oBooks = xlApp.Workbooks;
            oBooks.Open(pathArchivo, ReadOnly: true, IgnoreReadOnlyRecommended: true);

            Excel.Workbook xlLibro = oBooks.Item[1];
            Excel.Sheets   oSheets = xlLibro.Worksheets;

            Excel.Worksheet hojaConfig = (Excel.Worksheet)oSheets.Item[1];

            DataTable dtConfig = ObtenerConfigPrincipal(infoImportar, hojaConfig);

            int             indiceHojaParaMigrar;
            Excel.Worksheet hojaParaMigrar;

            foreach (DataRow infoTabla in dtConfig.Rows)
            {
                indiceHojaParaMigrar = ObtenerIndiceHojaExcel(oSheets, infoTabla["Pestania"].ToString());
                if (indiceHojaParaMigrar > 0)
                {
                    hojaParaMigrar = (Excel.Worksheet)oSheets.Item[indiceHojaParaMigrar];

                    DataTable dtDefTablas = null;
                    dtDefTablas = ObtenerConfigColumnasSegunTabla(infoImportar, hojaParaMigrar, infoTabla["NombreTablaBD"].ToString());

                    DataTable dtDatos = null;
                    dtDatos = ObtenerRegistrosTablaSegunPestania(infoImportar, hojaParaMigrar, infoTabla["NombreTablaBD"].ToString());
                }
                else
                {
                    throw new ImportadorExcelExceptionBE("No se ha encontrado la Hoja Excel: " + infoTabla["Pestania"].ToString());
                }
            }

            ImportadorExcelBM objImportadorBM = new ImportadorExcelBM();
            objImportadorBM.GuardarImportacion(infoImportar);
        }
        catch (Exception ex)
        {
            if (xlApp != null)
            {
                xlApp.Quit();
                Marshal.ReleaseComObject(xlApp);
            }
            System.GC.Collect();
            System.GC.WaitForPendingFinalizers();

            msgResul = ex.Message;
        }
        finally
        {
            if (ObjCom != null)
            {
                ObjCom.terminarProceso();
            }
        }

        return(msgResul); // Si es "" (VACIO) entonces es PROCESO OK
    }