Ejemplo n.º 1
0
        //public void AddWorkSheet(string sheetName)
        //{
        //    xl.Worksheet newWorkSheet;
        //    newWorkSheet = (xl.Worksheet)Globals.ThisWorkbook.Worksheets.Add();
        //}


        public  void CreateExcelFileFromDataTableOriginal(DataTable tbl)
        {
            xl._Worksheet oSheet;
            
            xl.Range oRng;
            object misvalue = System.Reflection.Missing.Value;
            oSheet = (xl._Worksheet)oWB.ActiveSheet;
            int idxpp = 1;
            int columnCount = 0;
            List<string> listColumn = new List<string>();
            foreach (DataColumn column in tbl.Columns)
            {
                columnCount += 1;
                listColumn.Add(column.ToString());
            }
            string columnCountString;
            columnCountString = GetExcelColumnName(columnCount);

            foreach (String val in listColumn)
            {
                oSheet.Cells[1, idxpp] = val;
                idxpp += 1;
            }
            oSheet.get_Range("A1", String.Format("{0}1", columnCountString)).Font.Bold = true;
            oSheet.get_Range("A1", String.Format("{0}1", columnCountString)).VerticalAlignment =
               xl.XlVAlign.xlVAlignCenter;

            // Create an array to multiple values at once.
            var totalRow = tbl.Rows.Count;
            int rowno = 0;

            string[,] saNames = new string[totalRow, columnCount];
            Debug.Print("totalrow" + totalRow.ToString());
            if (tbl.Rows.Count > 0)
            {
                foreach (DataRow dr in tbl.Rows)
                {

                    for (int idx = 0; idx < columnCount; idx += 1)
                    {
                        saNames[rowno, idx] = dr[idx].ToString();
                    }
                    rowno += 1;
                }

                //Debug.Print("rowcount: "+tbl.Rows.Count+ " colcount: "+tbl.Columns.Count+" columncount: "+columnCountString +" total row: "+totalRow);
                //Debug.Print(string.Format("{0}{1}", columnCountString, totalRow));
                oSheet.get_Range("A2", string.Format("{0}{1}", columnCountString, totalRow+1)).Value2 = saNames;
            }
            oRng = oSheet.get_Range("A1", string.Format("{0}1", columnCountString));
            oRng.EntireColumn.AutoFit();
            

            oWB.SaveAs(FileName, xl.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
                false, false, xl.XlSaveAsAccessMode.xlNoChange);

            oWB.Close();
            oXL = null;
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Close the current workbook
        /// </summary>
        /// <param name="saveChanges">Should the changes be saved to the document.</param>
        protected void CloseWorkbook(bool saveChanges = false)
        {
            if (_workbook != null)
            {
                object refSaveChanges = saveChanges;
                // Close the document
                // Close the workbook
                _workbook.Close(
                    refSaveChanges,
                    _refMissing,
                    _refMissing);
            }

            if (_worksheet != null)
            {
                _worksheet = null;
            }

            if (_sheets != null)
            {
                _sheets = null;
            }

            if (_workbooks != null)
            {
                _workbooks.Close();
            }

            _workbook  = null;
            _workbooks = null;
        }
Ejemplo n.º 3
0
        /// <summary>
        /// 释放内存
        /// </summary>
        public void Dispose(Excel._Worksheet CurSheet, Excel._Workbook CurBook, Excel._Application CurExcel)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
                CurSheet = null;
                CurBook.Close(false, mValue, mValue);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
                CurBook = null;

                CurExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
                CurExcel = null;

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
                {
                    //if (pro.StartTime < DateTime.Now)
                    pro.Kill();
                }
            }
            System.GC.SuppressFinalize(this);
        }
        /// <summary>
        /// Inchide toate procesele ce tin de excel si cheama garbage collector-ul
        /// </summary>
        public void Terminate()
        {
            wbXls.Close(null, null, null);
            appXls.Workbooks.Close();
            appXls.Quit();

            System.Runtime.InteropServices.Marshal.ReleaseComObject(appXls);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(this.activeWsXls);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wbXls);
            this.activeWsXls = null;
            wbXls            = null;
            appXls           = null;
            GC.Collect();              // colectarea componentelor

            //omoara toate procesele excel
            System.Diagnostics.Process [] p1 = System.Diagnostics.Process.GetProcessesByName("excel");
            foreach (System.Diagnostics.Process proc in p1)
            {
                if (!proc.CloseMainWindow())
                {
                    try
                    {
                        proc.Kill();
                    }
                    catch {}
                }
            }
        }
Ejemplo n.º 5
0
        private void SaveClick(object sender, RoutedEventArgs e)
        {
            string pathFile = "D:\\Desktop\\Hello";

            try
            {
                //另存活頁簿
                wBook.SaveAs(pathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                Console.WriteLine("儲存文件於 " + Environment.NewLine + pathFile);
            }
            catch (Exception ex)
            {
                Console.WriteLine("儲存檔案出錯,檔案可能正在使用" + Environment.NewLine + ex.Message);
            }

            //關閉活頁簿
            wBook.Close(false, Type.Missing, Type.Missing);
            //關閉Excel
            excelApp.Quit();
            //釋放Excel資源
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            wBook    = null;
            excelApp = null;
            wSheet   = null;
            GC.Collect();
        }
Ejemplo n.º 6
0
        //获取投放周期、投放开始日期、结束日期
        public string[] Excel_cyc(string path)
        {
            excel.Application ex   = new excel.Application();
            excel._Workbook   tb   = ex.Workbooks.Open(path);
            excel.Worksheet   wkst = tb.Worksheets[1];
            try
            {
                string cyc = wkst.Cells[2, 1].value;

                List <string> ls = new List <string>();
                ls.Add(cyc.Substring(0, cyc.IndexOf(")") + 1));
                //ls.Add(cycformat(cyc));
                ls.Add(cyc.Substring(0, cyc.IndexOf("年") + 1));
                ls.Add(cyc.Substring(cyc.IndexOf("年") + 1, cyc.IndexOf("月") - cyc.IndexOf("年")));
                ls.Add(cyc.Substring(cyc.IndexOf("第"), cyc.IndexOf("周") - cyc.IndexOf("第") + 1));
                ls.Add(BeginDate(cyc).ToString());
                ls.Add(EndDate(cyc).ToString());
                ls.Add(cycformat(cyc));
                return(ls.ToArray());
            }
            catch (Exception dsex)
            {
                MessageBox.Show(dsex.Message);
                string[] err = { "err" };
                return(err);
            }
            finally
            {
                wkst = null;
                tb.Close();
                tb = null;
                ex.Quit();
                ex = null;
            }
        }
Ejemplo n.º 7
0
        public void Dispose()
        {
            if (Mws_Sheet != null)
            {
                Marshal.ReleaseComObject(Mws_Sheet);
                Mws_Sheet = null;
            }

            if (Mwb_Book != null)
            {
                Mwb_Book.Close(false, null, null);
                Marshal.ReleaseComObject(Mwb_Book);
                Mwb_Book = null;
            }

            if (Mxl_Excel != null)
            {
                Mxl_Excel.Workbooks.Close();
                Mxl_Excel.Quit();
                Marshal.ReleaseComObject(Mxl_Excel);
                Mxl_Excel = null;
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
Ejemplo n.º 8
0
        public static void exportAsXLS(string path, string filename)
        {
            object miss = Type.Missing;
            XLS    app  = new Excel.Application();

            app.DisplayAlerts = false;
            app.Visible       = false;
            Workbook  wb = app.Workbooks.Add(miss);
            Worksheet ws = (Worksheet)wb.Sheets[1];

            //ws = wb.Sheets[0];
            //ws = wb.Sheets["Sheet1"];
            ws      = wb.ActiveSheet;
            ws.Name = "Detail";

            /*
             * for (int i = 0; i < 5; i++)
             * {
             *  for (int j = 0; j < 3; j++)
             *  {
             *      ws.Cells[i, j] = "i = " + i.ToString() + "j = " + j.ToString();
             *  }
             * }
             */
            wb.SaveAs(path + filename);
            wb.Close();
            //wb.SaveAs(path + filename, "xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive);
            app.Quit();
        }
Ejemplo n.º 9
0
        private void Writetoexcel_Click(object sender, EventArgs e)
        {
            //Start Excel and get Application object.
            oXL         = new Microsoft.Office.Interop.Excel.Application();
            oXL.Visible = true;

            //Get a new workbook.
            oWB    = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
            oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

            //Add table headers going cell by cell
            oSheet.Cells[1, 1] = "voltage value is : ";
            oSheet.Cells[1, 2] = textBox2.Text;
            oXL.Visible        = false;
            oXL.UserControl    = false;
            string date = Convert.ToString(DateTime.UtcNow);

            string[] date2    = date.Split('/', ':');
            string   filename = "C:\\Users\\ayoubexo.CEBONGROUP\\source\\repos\\JCD\\JCD\\excel files\\" + string.Join("-", date2) + "_test result.xlsx";

            oWB.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
                       false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                       Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            oWB.Close();
        }
Ejemplo n.º 10
0
 private void UpdateExcel(int row, int col, string data)
 {
     // UpdateExcel() is the method used to replace cells inside the specified
     // Excel spreadsheet, using the Interop.Excel library.
     Excel.Application oXL    = null;
     Excel._Workbook   oWB    = null;
     Excel._Worksheet  oSheet = null;
     try
     {
         // This try block opens up your specified document in the File Chooser,
         // and attempts to write to cells with the specified data given (string data).
         oXL    = new Excel.Application();
         oWB    = oXL.Workbooks.Open(excelDocument);
         oSheet = String.IsNullOrEmpty(dropDown.Text) ? (Excel._Worksheet)oWB.ActiveSheet : (Excel._Worksheet)oWB.Worksheets[dropDown.Text];
         oSheet.Cells[row, col] = data;
         oWB.Save();
     }
     catch (Exception ex)
     {
         Debug.WriteLine(ex);
     }
     finally
     {
         if (oWB != null)
         {
             oWB.Close(); // Close down document, to avoid deadlock or R/W issues.
         }
     }
 }
Ejemplo n.º 11
0
        /// <summary>
        /// Closes Excel Application and clears Excel Objects used
        /// </summary>
        public static void Close()
        {
            UsingExcel = false;

            if (oXL != null)
            {
                Marshal.ReleaseComObject(oChartRSSIs);
                Marshal.ReleaseComObject(oChartTags);
                Marshal.ReleaseComObject(oSheetCharts);
                Marshal.ReleaseComObject(oSheetDataRSSIs);
                Marshal.ReleaseComObject(oSheetDataTags);
                Marshal.ReleaseComObject(oRng);

                if (oWB != null)
                {
                    oWB.Close(false, null, null); // closes workbook
                }

                Marshal.ReleaseComObject(oWB);
                oWB = null;

                oXL.Quit();  // exit excel application

                Marshal.ReleaseComObject(oXL);
                oXL = null;
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
Ejemplo n.º 12
0
        public string DataString(int row, int col)
        {
            // This method extracts data from cells using the .ToString() function.
            // However, as this simply hands the instructions off, Excel is extremely
            // slow reading and sending data and causes a bottleneck. Looking for a better
            // solution.
            Excel.Application oXL  = new Excel.Application();
            Excel._Workbook   oWB  = null;
            string            data = "";

            try
            {
                // Why in 0.0.12, I forgot to encase this in a try/catch, is beyond me.
                oWB = oXL.Workbooks.Open(excelDocument);
                Excel._Worksheet oSheet = String.IsNullOrEmpty(dropDown.Text) ? (Excel._Worksheet)oWB.ActiveSheet : (Excel._Worksheet)oWB.Worksheets[dropDown.Text];
                data = oSheet.Cells[row, col].Value.ToString();
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
            }
            finally
            {
                if (oWB != null)
                {
                    oWB.Close();
                }
            }
            return(data); // Return the data inside the cell.
        }
Ejemplo n.º 13
0
        public int RowMinMax()
        {
            // New method written so the end user doesn't go over the amount of cells already used.
            // However, this may be scratched as it could be that the end user wishes to fill new boxes.
            int rowCounter = 0;

            Excel.Application oXL    = null;
            Excel._Workbook   oWB    = null;
            Excel._Worksheet  oSheet = null;
            try
            {
                // Again, unlike 0.0.12, this should always be in a try/catch.
                oXL    = new Excel.Application();
                oWB    = oXL.Workbooks.Open(excelDocument);
                oSheet = String.IsNullOrEmpty(dropDown.Text) ? (Excel._Worksheet)oWB.ActiveSheet : (Excel._Worksheet)oWB.Worksheets[dropDown.Text];
                Excel.Range last  = oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); // Finds the last used cell.
                Excel.Range range = oSheet.get_Range("A1", last);
                rowCounter = last.Row;
            }
            catch (Exception ex)
            {
                Debug.Write(ex);
                MessageBox.Show("Please open a valid .xlsx document!");
            }
            finally
            {
                if (oWB != null)
                {
                    oWB.Close(); // Close down document, to avoid deadlock or R/W issues.
                }
            }
            return(rowCounter); // Return this max value.
        }
Ejemplo n.º 14
0
        public Sprawozdanie ReadRaport(string path)
        {
            string[,] dane = new string[30, 300];
            Sprawozdanie raportSpolkiNotoria = new Sprawozdanie();

            oWB = (Excel._Workbook)(oXL.Workbooks.Open(path));

            //oSheet = (Excel._Worksheet)oWB.ActiveSheet;
            oSheet = (Excel._Worksheet)oWB.Worksheets[6];
            oRng   = oSheet.UsedRange;


            for (int row = 1; row < 231; row++)
            {
                Pozycja node = new Pozycja();
                node.name = (string)(oRng.Cells[row, 2] as Excel.Range).Value2;
                node.pos  = row;
                for (int col = 17; col < 22; col++) // w 17 kolumnie mamy 2010 rok
                {
                    try
                    {
                        node.value[col - 17] = (int)(oRng.Cells[row, col] as Excel.Range).Value2;
                    }
                    catch { }
                }
                raportSpolkiNotoria.dane[row] = node;
            }

            oWB.Close();

            return(raportSpolkiNotoria);
        }
Ejemplo n.º 15
0
        void printLable()
        {
            string path = Directory.GetCurrentDirectory();

            Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook   wb  = null;
            wb = oXL.Workbooks.Open(path + @"/../../xls/cleancut/7 标签-清洁分切.xlsx");
            _Worksheet my = wb.Worksheets[wb.Worksheets.Count];

            my.Select();
            my.Cells[2, 2].Value = cb膜代码.Text;
            my.Cells[3, 2].Value = tb批号.Text;
            my.Cells[4, 2].Value = tb米.Text + "米;  " + tbKg.Text + "Kg";
            my.Cells[5, 2].Value = cb原膜代码.Text;
            my.Cells[6, 2].Value = String.Format("{0} {1}", dtp分切日期.Value.ToString("yyyy/MM/dd"),
                                                 cb白班.Checked ? "白班☑ 夜班□" : "白班□ 夜班☑");

            my = wb.Worksheets[1];
            my.Select();
            oXL.Visible = false;
            my.PrintOut();
            // 关闭文件,false表示不保存
            wb.Close(false);
            // 关闭Excel进程
            oXL.Quit();
            // 释放COM资源
            Marshal.ReleaseComObject(wb);
            Marshal.ReleaseComObject(oXL);
            wb  = null;
            oXL = null;
        }
Ejemplo n.º 16
0
        static void Main(string[] args)
        {
            string WorkDir = @"D:\气象\";

            string filename = WorkDir + "河北.xlsx";

            //  string[] RawDataStr_A = File.ReadAllLines(i+ ".xlsx" + ".csv", Encoding.UTF8);
            //   RawDataStr_A = File.ReadAllLines(i + ".xlsx" + ".csv", Encoding.Default);

            Microsoft.Office.Interop.Excel.Application appExcel   = new Application();
            Microsoft.Office.Interop.Excel._Workbook   ExcelBooks = null;


            ExcelBooks = Shashade(appExcel);
            if (File.Exists((string)filename)) //判断文件已经是否存在
            {
                File.Delete((string)filename); //若已存在,则删除
            }

            ExcelBooks.SaveAs(filename);

            //stopWatch.Stop();
            //TimeSpan ts3 = stopWatch.Elapsed;
            //string elapsedTime3 = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts3.Hours, ts3.Minutes, ts3.Seconds, ts3.Milliseconds / 10);
            //Console.WriteLine("RunTime " + elapsedTime3);
            ExcelBooks.Close();
            //appExcel.Quit();
            ExcelBooks = null;
            appExcel   = null;
        }
Ejemplo n.º 17
0
        /// <summary>
        /// 释放内存
        /// </summary>
        public void Dispose(Microsoft.Office.Interop.Excel._Worksheet CurSheet, Microsoft.Office.Interop.Excel._Workbook CurBook, Microsoft.Office.Interop.Excel._Application CurExcel)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
                CurSheet = null;
                CurBook.Close(false, mValue, mValue);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
                CurBook = null;

                CurExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
                CurExcel = null;

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            catch (System.Exception ex)
            {
                // HttpContext.Current.Response.Write("在释放Excel内存空间时发生了一个错误:" + ex);
            }
            finally
            {
                foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
                {
                    //if (pro.StartTime < DateTime.Now)
                    pro.Kill();
                }
            }
            System.GC.SuppressFinalize(this);
        }
Ejemplo n.º 18
0
        private void button1_Click(object sender, EventArgs e)
        {
            object oMissing = System.Reflection.Missing.Value;

            //Create an instance of Microsoft Excel, make it visible and open invoice.xls
            Excel.Application oExcel = new Excel.Application();
            oExcel.Visible = true;
            Excel.Workbooks oBooks = oExcel.Workbooks;
            Excel._Workbook oBook  = null;

            int index = comboBox1.SelectedIndex;

            oBook = oBooks.Open(files[index], oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

            //Run the macro sub CSV() in the PERSONAL.XLSB file
            oExcel.Run("'c:\\csv\\PERSONAL.XLSB'!CSV");

            //Quit Excel and clean up
            oBook.Close(false, oMissing, oMissing);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
            oBook = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
            oBooks = null;
            oExcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
            oExcel = null;

            GC.Collect();
        }
Ejemplo n.º 19
0
        /// <summary>
        /// 将DataSet写入excel中,dataset.table的表名作为sheet名,dataset.table的列标题作为每一个sheet的首行
        /// </summary>
        /// <param name="fileExcel"></param>
        /// <param name="dsInfo"></param>
        public static void WriteExcel(string fileExcel, DataSet dsInfo)
        {
            DataTable dt = new DataTable();

            excel         = new Excel.Application();
            excel.Visible = false;         //不显示excel前台界面
            Excel._Workbook  book  = null; //定义工作表
            Excel._Worksheet sheet = null; //定义worksheet
            //Excel.Range range = null;//定义range

            //添加内容
            try
            {
                book = excel.Workbooks.Open(fileExcel, Missing.Value, Missing.Value, Missing.Value
                                            , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                            , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                            , Missing.Value, Missing.Value, Missing.Value);
            }
            catch (Exception ex)
            {
                Logs.WriteLog("error", ex.Message);
                throw;
            }
            finally
            {
                book.Close();
                //excel.Quit();//好像无法结束进程
                IntPtr t = new IntPtr(excel.Hwnd);
                int    k = 0;
                GetWindowThreadProcessId(t, out k);
                Process p = Process.GetProcessById(k);
                p.Kill();
            }
        }
Ejemplo n.º 20
0
        //adds project to SEQR workbook
        private static void AddToWorkbook(string parkName, string projectName, bool newProject)
        {
            string region = albPath.Substring(0, albPath.Length - 1);

            region = region.Substring(region.LastIndexOf("\\") + 1);
            string smRegion = region.Replace(" ", "");
            string workbook = albPath + smRegion + "SEQRWorkbook.xlsx";

            //workbook = "D:\\CentralSEQRWorkbook.xlsx";

            Excel.Application excelApp = new Excel.Application();
            excelApp.Visible = false;

            Excel._Workbook wBk = excelApp.Workbooks.Open(workbook, Type.Missing, Type.Missing,
                                                          Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                          Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            Excel._Worksheet wSht = (Excel.Worksheet)wBk.Worksheets[1];

            int rowNum = 0;

            string searchStr = newProject ? "" : projectName;
            int    searchCol = newProject ? 1 : 3;

            foreach (Excel.Range row in wSht.Rows)
            {
                Excel.Range rng = (Excel.Range)wSht.Cells[row.Row, searchCol];

                if (rng.Text.ToString().Equals(searchStr))
                {
                    rowNum = row.Row;
                    break;
                }
            }

            if (newProject)
            {
                PrincipalContext ctx      = new PrincipalContext(ContextType.Domain);
                UserPrincipal    user     = UserPrincipal.Current;
                string           fullName = user.GivenName + " " + user.Surname;

                wSht.Cells[rowNum, 1] = region;
                wSht.Cells[rowNum, 2] = parkName;
                wSht.Cells[rowNum, 3] = projectName;
                wSht.Cells[rowNum, 5] = DateTime.Today.Year;
                wSht.Cells[rowNum, 7] = fullName;
            }
            else
            {
                wSht.Hyperlinks.Add(wSht.Cells[rowNum, 4], albPath + parkName + "\\" + projectName, Type.Missing, Type.Missing, parkName + "\\" + projectName);
                wSht.Cells[rowNum, 13] = DateTime.Today;
            }

            wBk.Save();
            wBk.Close();
            excelApp.Quit();
            excelApp = null;
            GC.Collect();
        }
Ejemplo n.º 21
0
        //public bool dB_Delete(string p_dB, string p_Col, string p_Cond)
        //{
        //    bool ret_val = false;

        //    string sql = "DELETE FROM [Sheet1$] WHERE [" + p_Col + "] LIKE '" + p_Cond + "%';";
        //    ret_val = dB_Update(p_dB, sql);
        //    return ret_val;
        //}


        public bool dB_Delete(string p_dB, string p_Col, string p_Cond)
        {
            bool   ret_val = false;
            int    col_idx = 0, row_idx = 0;
            string filename = DataDir + p_dB + dbFileExt;

            XL.Application xlApp       = new XL.Application();
            XL._Workbook   xlWorkbook  = xlApp.Workbooks.Open(filename);
            XL._Worksheet  xlWorksheet = xlWorkbook.Worksheets["Sheet1"];
            XL.Range       xlRange     = xlWorksheet.UsedRange;

            int row_cnt = xlRange.Rows.Count;
            int col_cnt = xlRange.Columns.Count;

            // First find the column number
            for (int i = 1; i < col_cnt; i++)
            {
                string val = xlRange.Cells[1, i].Value2.ToString();
                if (val == p_Col)
                {
                    col_idx = i;
                    break;
                }
            }

            if (col_idx > 0)
            {
                // Next find the row number to delete
                for (int i = 2; i <= row_cnt; i++)
                {
                    string val = xlRange.Cells[i, col_idx].Value2.ToString();
                    if (val == p_Cond)
                    {
                        row_idx = i;
                        break;
                    }
                }

                if (row_idx > 0)
                {
                    (xlWorksheet.Rows[row_idx, System.Reflection.Missing.Value] as XL.Range).Delete(XL.XlDeleteShiftDirection.xlShiftUp);
                    ret_val = true;
                }
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();
            xlApp.DisplayAlerts = false;
            xlWorkbook.Save();
            xlWorkbook.Close();
            xlApp.Quit();

            // release COM objects
            Marshal.ReleaseComObject(xlWorkbook);
            Marshal.ReleaseComObject(xlWorksheet);
            Marshal.ReleaseComObject(xlApp);

            return(ret_val);
        }
Ejemplo n.º 22
0
 /// <summary>
 /// save the excel sheet to the location with file name
 /// </summary>
 /// <param name="fileName"></param>
 protected virtual void SaveExcel(string fileName)
 {
     _workBook.SaveAs(fileName, _value, _value,
                      _value, _value, _value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                      _value, _value, _value, _value, null);
     _workBook.Close(false, _value, _value);
     _excelApplication.Quit();
 }
Ejemplo n.º 23
0
        // Event Handler for the middle browse button.
        private void mBook2Load_Click(object sender, EventArgs e)
        {
            // Create a new OpenFileDialog object that will be used to select the second
            // workbook.
            System.Windows.Forms.OpenFileDialog fWin = new OpenFileDialog();

            // Set the extensions and default folder path to be used by the dialog.
            fWin.DefaultExt       = "xlsx";
            fWin.Filter           = "Spreadsheet Files (*.xlsx;*.xls;*.csv)|*.xlsx;*.xls;*.csv";
            fWin.InitialDirectory =
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);

            // Open the dialog and do addional processing if a valid result is returned.
            if (fWin.ShowDialog() == DialogResult.OK)
            {
                // If a workbook is currently open close it.
                mBook2Sheets = null;
                if (mBook2 != null)
                {
                    mBook2.Close(false);
                    mBook2 = null;
                }

                if (fWin.FileName.Equals(mBook1File.Text) && fWin.FileName.EndsWith(".csv"))
                {
                    MessageBox.Show("Cannot compare a csv file to itself.");
                    return;
                }

                // Open the workbook selected in the dialog.
                mBook2File.Text = fWin.FileName;
                mBook2          = (Excel._Workbook)(mExcel.Workbooks.Add(fWin.FileName));
                mBook2Sheets    = (Excel.Sheets)(mBook2.Sheets);

                // Enable and clear the combobox for the second workbook's worksheets.
                mBook2SheetBox.Enabled = true;
                mBook2SheetBox.Items.Clear();

                // Populate the combobox for the second workbook.
                int numSheets = mBook2Sheets.Count;
                for (int i = 1; i <= numSheets; i++)
                {
                    mBook2SheetBox.Items.Add(((Excel._Worksheet)(mBook2Sheets.Item[i])).Name);
                }
            }
        }
Ejemplo n.º 24
0
        public void ExportCallQualityData(string formPath, string savePath, string fileFormat, DataTable dt, string contragent, string ocinyvach, string ocinyvanyi, string callType, string opDate, string criticalError, string filePath, string result)
        {
            try
            {
                Cursor.Current = Cursors.WaitCursor;
                app            = new Excel.Application();
                Excel._Workbook workbook = app.Workbooks.Open(formPath);
                //Excel._Worksheet worksheet = null;
                Excel._Worksheet worksheet = workbook.Sheets[1];
                worksheet      = workbook.ActiveSheet;
                worksheet.Name = "Оцінка якості";

                worksheet.Cells.Replace("{OpDateTime}", opDate);
                worksheet.Cells.Replace("{Ochinuvanyy}", ocinyvanyi);
                worksheet.Cells.Replace("{CallType}", callType);
                worksheet.Cells.Replace("{Contragent}", contragent);
                worksheet.Cells.Replace("{FilePath}", filePath);
                worksheet.Cells.Replace("{Ocinyvach}", ocinyvach);
                worksheet.Cells.Replace("{CriticalError}", criticalError);
                worksheet.Cells.Replace("{Result}", result);
                int startRow = 14;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i < dt.Rows.Count - 1)
                    {
                        worksheet.Rows[(startRow + i) + ":" + (startRow + i)].Copy();
                        worksheet.Rows[(startRow + i + 1) + ":" + (startRow + i + 1)].Insert();
                    }
                    worksheet.Range["B" + (startRow + i)].Value = dt.Rows[i][0].ToString();
                    worksheet.Range["D" + (startRow + i)].Value = dt.Rows[i][1].ToString();
                    worksheet.Range["E" + (startRow + i)].Value = dt.Rows[i][2].ToString();
                }
                worksheet.Rows[7 + ":" + (dt.Rows.Count - 1)].EntireRow().AutoFit();
                if (fileFormat.Contains("xlsx"))
                {
                    workbook.SaveAs(savePath);
                    app.Visible = true;
                }
                else if (fileFormat.Contains("pdf"))
                {
                    workbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, savePath);
                    workbook.Close(false);
                    app.Quit();
                    System.Diagnostics.Process.Start(savePath);
                }
                Cursor.Current = Cursors.Default;
                //MessageBox.Show("Дані вивантажено!" + Environment.NewLine + savePath, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                if (app.Workbooks.Count == 1)
                {
                    app.Quit();
                }
                app = null;
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Ejemplo n.º 25
0
 public void CloseExcel()
 {
     if (oXL == null)
     {
         return;
     }
     oWB.Close();
     oXL.Quit();
 }
Ejemplo n.º 26
0
        public void saveWorkbook(String fileName)
        {
            //save workbook
            oWB.SaveAs(fileName);

            //close workbook
            oWB.Close();
            oXL.Quit();
        }
Ejemplo n.º 27
0
        /// <summary>
        /// Metod koj vrsi zacuvuvanje na dokumentot pod opredeleno ime -
        /// momentalno vo direktoriumot C:\TEMP\NBOFILES
        /// </summary>
        /// <param name="fileName"></param>
        public void SaveExcel(string fileName)
        {
            object fileNameToSave = FILE_PATH + "\\" + fileName;

            _workBook.SaveAs(fileNameToSave, _value, _value,
                             _value, _value, _value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                             _value, _value, _value, _value, null);
            _workBook.Close(false, _value, _value);
            _excelApplication.Quit();
        }
        private static void ExcelClose(Excel._Workbook workbook, Excel._Application application)
        {
            var missingObject = System.Reflection.Missing.Value;

            workbook.Close(false, missingObject, missingObject);
            application.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(application);

            GC.Collect();
        }
Ejemplo n.º 29
0
        public static string externAsXLS(string path)
        {
            string dateNow  = DateTime.Now.ToShortDateString();
            string filename = dateNow + "_" + "extern_list";
            object miss     = Type.Missing;
            List <List <String> > externs = new List <List <string> >();

            try
            {
                List <object[]> list = Database.Database.database.RequestTable("f_get_extern_user", 6);
                foreach (var o in list)
                {
                    externs.Add(new List <string> {
                        (string)o[0], (string)o[1], (string)o[2], (string)o[3], (string)o[4], (string)o[5]
                    });
                }
            }
            catch (Exception) { }

            XLS app = new Excel.Application();

            app.DisplayAlerts = false;
            app.Visible       = false;
            Workbook  wb = app.Workbooks.Add(miss);
            Worksheet ws = (Worksheet)wb.Sheets[1];

            //ws = wb.Sheets[0];
            //ws = wb.Sheets["Sheet1"];
            ws      = wb.ActiveSheet;
            ws.Name = "Liste des externes";

            ws.Cells[1, 1] = "Login";
            ws.Cells[1, 2] = "Adresse mél";
            ws.Cells[1, 3] = "Prénom";
            ws.Cells[1, 4] = "Nom";
            ws.Cells[1, 5] = "Adresse";
            ws.Cells[1, 6] = "Numéro de téléphone";



            for (int y = 2; y <= externs.Count + 1; y++)
            {
                for (int x = 1; x <= externs[y - 2].Count; x++)
                {
                    ws.Cells[y, x] = externs[y - 2][x - 1];
                }
            }

            ws.Columns.AutoFit();
            wb.SaveAs(path + filename);
            wb.Close();
            //wb.SaveAs(path + filename, "xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive);
            app.Quit();
            return(filename + ".xlsx");
        }
Ejemplo n.º 30
0
        void printLable(bool flag)
        {
            string path = Directory.GetCurrentDirectory();

            Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook   wb  = null;
            if (flag)
            {
                wb = oXL.Workbooks.Open(path + @"/../../xls/LDPEBag/LDPE 制袋内包标签.xlsx");
            }
            else
            {
                wb = oXL.Workbooks.Open(path + @"/../../xls/LDPEBag/LDPE 制袋外包标签.xlsx");
            }
            _Worksheet my = wb.Worksheets[wb.Worksheets.Count];

            my.Select();
            my.Cells[1, 2].Value  = tc产品名称.Text;
            my.Cells[2, 2].Value  = tc产品编码.Text;
            my.Cells[3, 2].Value  = tc产品规格.Text;
            my.Cells[4, 2].Value  = tc产品批号.Text;
            my.Cells[5, 2].Value  = dc生产日期.Value.ToString("yyyy/MM/dd");
            my.Cells[6, 2].Value  = dc有效期至.Value.ToString("yyyy/MM");
            my.Cells[7, 2].Value  = tc数量.Text;
            my.Cells[8, 2].Value  = tc包装序号.Text;
            my.Cells[9, 2].Value  = tc毛重.Text;
            my.Cells[10, 2].Value = tc箱体规格.Text;
            my.Cells[11, 2].Value = tc注册证号.Text;


            my.Cells[1, 5].Value  = teName.Text;
            my.Cells[2, 5].Value  = teCode.Text;
            my.Cells[3, 5].Value  = teSize.Text;
            my.Cells[4, 5].Value  = teBatch.Text;
            my.Cells[5, 5].Value  = deMfg.Value.ToString("dd/MM/yyyy");
            my.Cells[6, 5].Value  = deExpiry.Value.ToString("MM/yyyy");
            my.Cells[7, 5].Value  = teQuantity.Text;
            my.Cells[8, 5].Value  = tePack.Text;
            my.Cells[9, 5].Value  = tegross.Text;
            my.Cells[10, 5].Value = teCarton.Text;

            my = wb.Worksheets[c标签模板.SelectedIndex + 1];
            my.Select();
            oXL.Visible = false;
            my.PrintOut();
            // 关闭文件,false表示不保存
            wb.Close(false);
            // 关闭Excel进程
            oXL.Quit();
            // 释放COM资源
            Marshal.ReleaseComObject(wb);
            Marshal.ReleaseComObject(oXL);
            wb  = null;
            oXL = null;
        }
Ejemplo n.º 31
0
        public List<AccountExcel> readExcel(string sExcelPath)
        {
            var lReturn = new List<AccountExcel>();
            string valueString = string.Empty;
            objExcelApp = new Microsoft.Office.Interop.Excel.Application();
            objBooks = (Excel.Workbooks)objExcelApp.Workbooks;
            //Open the workbook containing the address data.
            objBook = objBooks.Open(sExcelPath, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);
            //Get a reference to the first sheet of the workbook.
            objSheets = objBook.Worksheets;
            objSheet = (Excel._Worksheet)objSheets.get_Item(1);

            rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
            long lLastRow = rngLast.Row;
            long lLastCol = rngLast.Column;

            for (long rowCounter = 2; rowCounter <= lLastRow; rowCounter++) //FirstRow Has Headers - start at row 2
            {
                if (ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value) != "")
                {
                    var adAccount = new AccountExcel();

                    adAccount.sCustomerNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value);
                    adAccount.sAccountName = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 40]).Value);
                    adAccount.sAddressLine1 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 2]).Value);
                    adAccount.sAddressLine2 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 5]).Value);
                    adAccount.sAddressLine3 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 9]).Value);
                    adAccount.sPostCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 15]).Value);
                    adAccount.sTelephone = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 17]).Value);
                    adAccount.sVatNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 18]).Value);
                    adAccount.sCountryCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 21]).Value);
                    adAccount.sEmail = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 37]).Value);
                    adAccount.sWeb = "";// ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 38]).Value);
                    adAccount.sKAM = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 31]).Value);
                    adAccount.sRegion = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 24]).Value);
                    lReturn.Add(adAccount);
                }
            }
              //Close the Excel Object
            objBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);

            objBooks.Close();
            objExcelApp.Quit();

            Marshal.ReleaseComObject(objSheet);
            Marshal.ReleaseComObject(objSheets);
            Marshal.ReleaseComObject(objBooks);
            Marshal.ReleaseComObject(objBook);
            Marshal.ReleaseComObject(objExcelApp);

            objSheet = null;
            objSheets = null;
            objBooks = null;
            objBook = null;
            objExcelApp = null;

            GC.GetTotalMemory(false);
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.GetTotalMemory(true);
            return (lReturn);
        }