コード例 #1
2
ファイル: Export.cs プロジェクト: hprog/exchange
 public Export(string embedded)
 {
     string tmp = String.Empty;
     try
     {
         //получаем шаблон из прикладных ресурсов
         Stream template = GetResourceFileStream(embedded);
         //создаем временный файл
         tmp = System.IO.Path.GetTempFileName().Replace(".tmp", ".xlsx");
         //сохраняем шаблон во временный файл
         using (var fileStream = File.Create(tmp))
         {
             template.CopyTo(fileStream);
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
     // Создаем приложение и открываем в нём временный файл
     objApp = new Excel.Application();
     objBook = objApp.Workbooks.Open(tmp);
     objBook.Activate();
     objSheets = objBook.Worksheets;
     objSheet = (Excel._Worksheet)objSheets.get_Item(1);
 }
コード例 #2
1
ファイル: ExcelHelper.cs プロジェクト: khoattn/LimsVisualizer
        public void CreateWorkbook()
        {
            var templateFile = Path.Combine(System.Windows.Forms.Application.StartupPath,
                @"Templates\TemplateWorkBook.xlsx");
            MainForm.LogWriter.WriteDebugMessage("Creating new workbook.");
            //mWorkbook = mApplication.Workbooks.Add(Missing.Value);
            mWorkbook = mApplication.Workbooks.Add(templateFile);

            mMeasurementDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[1];
            mMiscellaneousDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[2];
            //var sheet3 = (Excel.Worksheet)mWorkbook.Sheets.Item[3];

            //Rename first and second worksheet, delete third worksheet
            //mMeasurementDataWorksheet.Name = "Measurement Data";
            //mMiscellaneousDataWorksheet.Name = "Miscellaneous Data";
            //sheet3.Delete();

            //Set measurement data worksheet as active worksheet
            mMeasurementDataWorksheet.Activate();

            //Display windows in splitview
            mWorkbook.Windows.Arrange(Excel.XlArrangeStyle.xlArrangeStyleHorizontal);

            MainForm.HeadersWritten = false;
            MainForm.LogWriter.WriteDebugMessage("Created new workbook.");
        }
コード例 #3
0
 public static void WriteFormula(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string wValue, out bool success)
 {
     if (_excelDoc != null)
     {
         try
         {
             MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, position);
             _excelRge.FormulaLocal = wValue;
             success = true;
             return;
         }
         catch (Exception ex)
         {
             success = false;
             Log.LogHelper.AddLog(@"异常36", ex.Message, true);
             Log.LogHelper.AddLog(@"异常37", "  " + ex.TargetSite.ToString(), true);
             return;
         }
     }
     else
     {
         success = false;
         Log.LogHelper.AddLog(@"异常38", @"文件没有正常打开,无法读取数据", true);
         return;
     }
 }
コード例 #4
0
        private void btnExportToExcel_Click(object sender, EventArgs e)
        {
            Excel._Application app       = new Excel.Application();
            Excel._Workbook    workbook  = app.Workbooks.Add(Type.Missing);
            Excel._Worksheet   worksheet = null;
            app.Visible = true;
            worksheet   = workbook.Sheets["Sheet1"];
            worksheet   = workbook.ActiveSheet;

            // changing the name of active sheet
            worksheet.Name = "Setups-QuickCode";

            for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
            {
                worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
            }
            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }

            workbook.SaveAs("c:\\output.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        }
コード例 #5
0
        /***********************功能函数****************************/
        private void initialize()
        {
            form_main_program.CheckForIllegalCrossThreadCalls = false;
            excel_app               = new MSExcel.ApplicationClass(); //初始化
            book                    = excel_app.Workbooks.Add(Missing.Value);
            excel_app.Visible       = false;
            excel_app.DisplayAlerts = false;

            if (!File.Exists("data.txt"))
            {
                TheFile = new FileStream("data.txt", FileMode.Create);
                TheFile.Close();
            }
            //EXCEL COM口
            try
            {
                sheet = (MSExcel._Worksheet)book.Worksheets.get_Item(1);
            }
            catch (System.Exception ex)
            {
                sheet = (MSExcel._Worksheet)book.Worksheets.Add(Missing.Value, book.Worksheets[0], 0, Missing.Value);
            }
            /*zed1曲线设置*/
            zeggraph_init();
        }
コード例 #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;
            }
        }
コード例 #7
0
        /**
         * if previous logs exist we open it and update it
         **/
        public bool OpenWorkBook()
        {
            if (!File.Exists(m_logName))
            {
                return false;
            }

            try
            {
                m_WorkBook = (Excel._Workbook)(m_XLApp.Workbooks.Open(m_logName));
                m_Sheet = (Excel._Worksheet)m_WorkBook.ActiveSheet;
            }
            catch (Exception e)
            {
                throw new LoggerException("Cannot open log file: " + e.Message);
            }

            if (m_Sheet == null)
            {
                return false;
            }

            m_columnsMap = new Dictionary<string, int>();

            string projectName;
            int lastRow = m_Sheet.UsedRange.Rows.Count;
            for (int column = 2; column < m_Sheet.UsedRange.Columns.Count + 1; column++)
            {
                projectName = GetStringFromCell(1, column);
                m_columnsMap.Add(projectName, column);
            }
            return true;
        }
コード例 #8
0
        private void izvozpodatkovbutton_Click(object sender, EventArgs e)
        {
            oXL    = new Excel.Application();
            oWB    = (Excel.Workbook)oXL.Workbooks.Add();
            oSheet = (Excel._Worksheet)oWB.ActiveSheet;

            oSheet.Cells[1, 1] = "Ime Lekarne";
            oSheet.Cells[1, 2] = "Telefon";
            oSheet.Cells[1, 3] = "Delovni Čas";
            oSheet.Cells[1, 4] = "Naslov";
            oSheet.Cells[1, 5] = "Kraj";
            oSheet.Cells[1, 6] = "Število Delavcev";

            oSheet.get_Range("A1", "F1").Font.Bold         = true;
            oSheet.get_Range("A1", "F1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            polnjenjeexcel();
            try
            {
                oWB.Application.ActiveWorkbook.SaveAs(@"C:\Users\Jon\Desktop\Excel\" + imeexceltext.Text + ".xlsx");
                MessageBox.Show("Uspešno ustvarjena excel datoteka");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
コード例 #9
0
        public void PrintWskazniki(List <Wskazniki> listaWskaznikow, string nazwaSpolki)
        {
            oWB = (Excel._Workbook)(oXL.Workbooks.Open("E:\\raporty\\wyniki.xlsx"));


            oSheet      = (Excel._Worksheet)oWB.Sheets.Add();
            oSheet.Name = nazwaSpolki;
            //oRng = oSheet.UsedRange;

            oSheet.Activate();

            //   worksheet.get_Range(topLeftLetter, bottomRightLetter).EntireColumn.AutoFit();


            for (int j = 0; j < 5; j++)
            {
                int startRok = 2010;
                oSheet.Cells[1, j + 4] = (startRok + j).ToString();
            }

            for (int i = 0; i < listaWskaznikow.Count(); i++)
            {
                oSheet.Cells[i + 2, 1] = listaWskaznikow[i].category;
                oSheet.Cells[i + 2, 2] = listaWskaznikow[i].name;
                oSheet.Cells[i + 2, 3] = listaWskaznikow[i].wzor;

                for (int j = 0; j < 5; j++)
                {
                    oSheet.Cells[i + 2, j + 4] = listaWskaznikow[i].value[j];
                }
            }

            oWB.Save();
        }
コード例 #10
0
 public static void WriteValue(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string wValue, string numberFormat, out bool success)
 {
     if (_excelDoc != null)
     {
         try
         {
             bool checkSta = true;
             MSExcel.Worksheet _excelSht = (MSExcel.Worksheet)_excelDoc.Worksheets[sheetIndex];
             _excelSht.Cells[position.RowIndex, position.ColumnIndex] = wValue;
             if (!string.IsNullOrWhiteSpace(numberFormat))
             {
                 MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, position);
                 _excelRge.NumberFormatLocal = numberFormat;
             }
             success = checkSta;
             return;
         }
         catch (Exception ex)
         {
             success = false;
             Log.LogHelper.AddLog(@"异常33", ex.Message, true);
             Log.LogHelper.AddLog(@"异常34", "  " + ex.TargetSite.ToString(), true);
             return;
         }
     }
     else
     {
         success = false;
         Log.LogHelper.AddLog(@"异常35", @"文件没有正常打开,无法读取数据", true);
         return;
     }
 }
コード例 #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();
        }
コード例 #12
0
 public void StartUp()
 {
     _excelApp = new Excel.Application();
     _books    = (Excel.Workbooks)_excelApp.Workbooks;
     _book     = (Excel._Workbook)(_books.Add(_optionalValue));
     _sheets   = (Excel.Sheets)_book.Worksheets;
 }
コード例 #13
0
ファイル: XLSCreator.cs プロジェクト: Morceau-x/yakachoisir
        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();
        }
コード例 #14
0
ファイル: ExcelHelper.cs プロジェクト: yuguansu/FileHelper
        /// <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();
            }
        }
コード例 #15
0
ファイル: Form1.cs プロジェクト: LeeSunHong/VirusTotalProject
 private void checkFiles(String folderPath)
 {
     this.Visible = false;
     if (System.IO.Directory.Exists(folderPath))
     {
         excelApp = new Excel.Application();
         if (EXCEL_PATH.Exists)
         {
             workBook = excelApp.Workbooks.Open(EXCEL_PATH.FullName, 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);
         }
         else
         {
             workBook = excelApp.Workbooks.Add("");
         }
         System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(folderPath);
         taskCount = di.GetFiles().Length;
         Console.Write("Started scanning {0}files. 0/{0}", taskCount);
         foreach (var file in di.GetFiles())
         {
             System.IO.FileInfo fi = new System.IO.FileInfo(file.FullName);
             getFileReport(fi);
         }
     }
 }
コード例 #16
0
        private void Tool_Click(object sender, RoutedEventArgs e)
        {
            MSWord.Application EXCL1 = new MSWord.Application(); //新建一个应用程序EXC1
            EXCL1.Visible = true;                                //设置EXC1打开后可见

            MSWord.Workbooks wbs = EXCL1.Workbooks;
            MSWord._Workbook wb  = wbs.Add(GlobalData.gFileName);  //打开EXCEL

            MSWord._Worksheet mySheet;

            mySheet = wb.Sheets[wantUseSheetIndex(GlobalData.gMySheetName, wb)]; //找到我要操作的sheet
            mySheet.Activate();
            //tiaoshi.Text = excelElementRead(mySheet, 1, 5).Interior.Color.ToString();
            System.Data.DataTable dt = readNonredunOneColumn(mySheet, GlobalData.gWantUseColumn);

            MSWord._Worksheet testPointSheet = wb.Sheets[wantUseSheetIndex(GlobalData.gTestPointSheetName, wb)];
            testPointSheet.Activate();
            excelElementWrite(testPointSheet, 1, 1, "测试点序号");
            excelElementWrite(testPointSheet, 1, 2, "测试点");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                excelElementWrite(testPointSheet, i + 2, 1, dt.Rows[i][0].ToString());
                excelElementWrite(testPointSheet, i + 2, 2, dt.Rows[i][1].ToString());
            }

            writeTestPointCount(mySheet, GlobalData.gTestPointRange, GlobalData.gWantUseColumn, dt);

            wb.Save();
        }
コード例 #17
0
        public ExcelHandlerModel2()
        {
            SummaryTableTest.Clear();
            SummaryTableTest.Columns.Add("Файл");
            SummaryTableTest.Columns.Add("Статус");
            SummaryTableTest.Columns.Add("Количество контрактов", System.Type.GetType("System.Int32"));

            app      = new Excel.Application();
            workbook = app.Workbooks.Add(Type.Missing);

            worksheet = workbook.ActiveSheet;

            table      = worksheet.ListObjects.Add();
            table.Name = "Результат";
            Excel.Range header = table.HeaderRowRange;

            header[1, 1].Value = "ОСВ";
            header[1, 2].Value = "КФО";
            header[1, 3].Value = "Контрагент";
            header[1, 4].Value = "Договор";
            header[1, 5].Value = "Дебет";
            header[1, 6].Value = "Кредит";

            table.ListColumns.Item[5].Range.NumberFormatLocal = @"# ##0,00";
            table.ListColumns.Item[6].Range.NumberFormatLocal = @"# ##0,00";

            //table.ListColumns.Item[5].Range.NumberFormat = @"@";
        }
コード例 #18
0
ファイル: 标签.cs プロジェクト: warmkid/mitcpro
        void printLable(bool flag)
        {
            string path = Directory.GetCurrentDirectory();

            Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
            //File.Copy(@"../../xls/Extrusion/吹膜标签.xlsx", path + @"/label.xlsx", true);
            Microsoft.Office.Interop.Excel._Workbook wb = null;
            if (flag)
            {
                wb = oXL.Workbooks.Open(path + @"/../../xls/CSBag/标签 CS 制袋 内标签.xlsx");
            }
            else
            {
                wb = oXL.Workbooks.Open(path + @"/../../xls/CSBag/标签 CS 制袋 外包标签.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[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("yyyy/MM/dd");
            my.Cells[6, 5].Value  = deExpiry.Value.ToString("yyyy/MM");
            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 = true;
            //try
            //{
            //    my.PrintOut();
            //}
            //catch
            //{
            //}

            //wb.Close(false);
            //oXL.Quit();
            //Marshal.ReleaseComObject(wb);
            //Marshal.ReleaseComObject(oXL);
            //wb = null;
            //oXL = null;
        }
コード例 #19
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);
        }
コード例 #20
0
        public void CreateWorkbook()
        {
            var templateFile = Path.Combine(System.Windows.Forms.Application.StartupPath,
                                            @"Templates\TemplateWorkBook.xlsx");

            MainForm.LogWriter.WriteDebugMessage("Creating new workbook.");
            //mWorkbook = mApplication.Workbooks.Add(Missing.Value);
            mWorkbook = mApplication.Workbooks.Add(templateFile);

            mMeasurementDataWorksheet   = (Excel.Worksheet)mWorkbook.Sheets.Item[1];
            mMiscellaneousDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[2];
            //var sheet3 = (Excel.Worksheet)mWorkbook.Sheets.Item[3];

            //Rename first and second worksheet, delete third worksheet
            //mMeasurementDataWorksheet.Name = "Measurement Data";
            //mMiscellaneousDataWorksheet.Name = "Miscellaneous Data";
            //sheet3.Delete();

            //Set measurement data worksheet as active worksheet
            mMeasurementDataWorksheet.Activate();

            //Display windows in splitview
            mWorkbook.Windows.Arrange(Excel.XlArrangeStyle.xlArrangeStyleHorizontal);

            MainForm.HeadersWritten = false;
            MainForm.LogWriter.WriteDebugMessage("Created new workbook.");
        }
コード例 #21
0
 private void btnExportar_Click(object sender, EventArgs e)
 {
     if (dgvMaterias.Rows.Count == 0)
     {
         MessageBox.Show("Sin datos por EXPORTAR", "Aviso", MessageBoxButtons.OK);
     }
     else
     {
         Excel._Application app       = new Excel.Application();
         Excel._Workbook    workbook  = app.Workbooks.Add(Type.Missing);
         Excel._Worksheet   worksheet = null;
         app.Visible    = true;
         worksheet      = workbook.Sheets[1];
         worksheet      = workbook.ActiveSheet;
         worksheet.Name = "Horarios-Asignados";
         for (int i = 1; i < dgvMaterias.Columns.Count + 1; i++)
         {
             worksheet.Cells[1, i] = dgvMaterias.Columns[i - 1].HeaderText;
         }
         for (int i = 0; i < dgvMaterias.Rows.Count; i++)
         {
             for (int j = 0; j < dgvMaterias.Columns.Count; j++)
             {
                 worksheet.Cells[i + 2, j + 1] = dgvMaterias.Rows[i].Cells[j].Value.ToString();
             }
         }
         workbook.SaveAs("C:\\Users\\AbelFH\\Desktop\\Horarios-Asignados.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
         app.Quit();
         MessageBox.Show("EXCEL generado exitosamente", "Aviso", MessageBoxButtons.OK);
     }
 }
コード例 #22
0
        /**//// <summary>
            /// 打开没有模板的操作。
            /// </summary>
        public void Open()
        {
            this.Open(String.Empty);
        }

        /**//// <summary>
            /// 功能:实现Excel应用程序的打开
            /// </summary>
            /// <param name="TemplateFilePath">模板文件物理路径</param>
        public void Open(string TemplateFilePath)
        {
            //打开对象
            m_objExcel                = new Excel.Application();
            m_objExcel.Visible        = false;
            m_objExcel.DisplayAlerts  = false;
            m_objExcel.ScreenUpdating = false; //停止更新屏幕,加快速度

            //if (m_objExcel.Version != "11.0")
            //{
            //   // MessageBox.Show("您的 Excel 版本不是 11.0 (Office 2003),操作可能会出现问题。");
            //    m_objExcel.Quit();
            //    return;
            //}

            m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
            if (TemplateFilePath.Equals(String.Empty))
            {
                m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
            }
            else
            {
                m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
                                            m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
            }
            m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
            m_objSheet  = (Excel._Worksheet)(m_objSheets.get_Item(1));
            m_objExcel.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(m_objExcel_WorkbookBeforeClose);
        }
コード例 #23
0
        protected void ClearObject()
        {
            if (this.xlWorkSheet != null)
            {
                ReleaseObj.Marshal.ReleaseComObject(xlWorkSheet);
                this.IsWorkSheetOpened = false;
                this.xlWorkSheet       = null;
            }

            if (this.xlWorkBook != null)
            {
                if (this.IsWorkBookOpened)
                {
                    xlWorkBook.Close(false, misValue, misValue);
                    this.IsWorkBookOpened = false;
                }

                ReleaseObj.Marshal.ReleaseComObject(xlWorkBook);
                this.xlWorkBook = null;
            }

            if (this.xlApp != null)
            {
                if (this.IsAppOpened)
                {
                    xlApp.Quit();
                    this.IsAppOpened = false;
                }

                ReleaseObj.Marshal.ReleaseComObject(xlApp);
                this.xlApp = null;
            }

            this.ClearnGarbage();
        }
コード例 #24
0
        /// <summary>
        /// Crea objeto Excel
        /// </summary>
        /// <param name="workSheetName">Nombre de hoja</param>
        /// <param name="isVisible">True = es visible para el usuario la creación del documento</param>
        public void CreateExcelObject(string workSheetName, bool isVisible)
        {
            try
            {
                oApp         = new oExcel.Application();
                oApp.Visible = isVisible;
                try
                {
                    oWorkbook = (oExcel._Workbook)(oApp.Workbooks.Add(Missing.Value));
                }
                catch
                {
                    System.Threading.Thread.CurrentThread.CurrentCulture =
                        new System.Globalization.CultureInfo("en-US");
                    oWorkbook = (oExcel._Workbook)(oApp.Workbooks.Add(Missing.Value));
                }

                oWorksheet         = (oExcel._Worksheet)oWorkbook.ActiveSheet;
                oApp.DisplayAlerts = false;
                oWorksheet.Name    = workSheetName;
                IsObjectCreated    = true;
            }
            catch
            {
                IsObjectCreated = false;
            }
        }
コード例 #25
0
        private string GetExcelSheetName(string pPath)
        {
            //打开一个Excel应用

            _excelApp = new Excel.Application();
            if (_excelApp == null)
            {
                throw new Exception("打开Excel应用时发生错误!");
            }
            _books = _excelApp.Workbooks;
            //打开一个现有的工作薄
            _book   = _books.Add(pPath);
            _sheets = _book.Sheets;
            //选择第一个Sheet页
            _sheet = (Excel._Worksheet)_sheets.get_Item(1);
            string sheetName = _sheet.Name;

            ReleaseCOM(_sheet);
            ReleaseCOM(_sheets);
            ReleaseCOM(_book);
            ReleaseCOM(_books);
            _excelApp.Quit();
            ReleaseCOM(_excelApp);
            return(sheetName);
        }
コード例 #26
0
ファイル: Main.cs プロジェクト: Rhys362/TicketManager
        private void btnExcel_Click(object sender, EventArgs e)
        {
            //open excel and transfer data from datagridview
            Microsoft.Office.Interop.Excel._Application app      = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook    workbook = app.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet
                worksheet = null;
            app.Visible    = true;
            worksheet      = workbook.Sheets["Sheet1"];
            worksheet      = workbook.ActiveSheet;
            worksheet.Name = "Exported from Ticket Manager";

            for (int i = 1; i < dgvClosedTickets.Columns.Count + 1; i++)
            {
                worksheet.Cells[1, i] = dgvClosedTickets.Columns[i - 1].HeaderText;
            }

            for (int i = 0; i < dgvClosedTickets.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dgvClosedTickets.Columns.Count; j++)
                {
                    worksheet.Cells[i + 2, j + 1] = dgvClosedTickets.Rows[i].Cells[j].Value.ToString();
                }
            }

            workbook.SaveAs("c:\\ClosedTickets.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing,
                            Type.Missing, Type.Missing);
        }
コード例 #27
0
        private void ExportToExcel()
        {
            Excel._Application excel     = new Excel.Application();
            Excel._Workbook    workbook  = excel.Workbooks.Add(Type.Missing);
            Excel._Worksheet   worksheet = null;

            barExcel.Value = 0;
            for (int b = 0; b < 100; b++)
            {
                barExcel.Value += 1;
            }

            try
            {
                worksheet = workbook.ActiveSheet;

                worksheet.Name = "Control de Horas";

                int cellRowIndex    = 1;
                int cellColumnIndex = 1;

                for (int i = -1; i < dgvHoras.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dgvHoras.Columns.Count; j++)
                    {
                        if (cellRowIndex == 1)
                        {
                            worksheet.Cells[cellRowIndex, cellColumnIndex] = dgvHoras.Columns[j].HeaderText;
                        }
                        else
                        {
                            worksheet.Cells[cellRowIndex, cellColumnIndex] = dgvHoras.Rows[i].Cells[j].Value.ToString();
                        }
                        cellColumnIndex++;
                    }
                    cellColumnIndex = 1;
                    cellRowIndex++;
                }

                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.Filter      = "Excel files (*.xlsx)|*.xlsx";
                saveDialog.FilterIndex = 1;

                if (saveDialog.ShowDialog() == DialogResult.OK)
                {
                    workbook.SaveAs(saveDialog.FileName);
                    MessageBox.Show("Descarga Completa.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel    = null;
            }
        }
コード例 #28
0
ファイル: Excel.cs プロジェクト: techcap/CSharp
        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();
        }
コード例 #29
0
        public static Hashtable GetMacros(Excel._Workbook wDestination)
        {
            Hashtable pak = new Hashtable();

            VBA.VBProject  prj;
            VBA.CodeModule code;
            string         composedFile;

            prj = wDestination.VBProject;

            foreach (VBA.VBComponent comp in prj.VBComponents)
            {
                if (comp.Type == VBA.vbext_ComponentType.vbext_ct_StdModule)
                {
                    code = comp.CodeModule;


                    composedFile = comp.Name + Environment.NewLine;


                    for (int i = 0; i < code.CountOfLines; i++)
                    {
                        composedFile +=
                            code.get_Lines(i + 1, 1) + Environment.NewLine;
                    }
                    pak.Add(comp.Name, composedFile);
                }
            }

            return(pak);
        }
コード例 #30
0
ファイル: ReadExcel.cs プロジェクト: anthonied/CRM
        public void readExcel()
        {
            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(@"C:\Temp\data\Test.xlsx", 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);

            //Select the range of data containing the addresses and get the outer boundaries.
            rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
            long lLastRow = rngLast.Row;
            long lLastCol = rngLast.Column;

            // Iterate through the data and concatenate the values into a comma-delimited string.
            for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++)
            {
                for (long colCounter = 1; colCounter <= lLastCol; colCounter++)
                {
                    //Write the next value into the string.
                    Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter];
                    string cellvalue = cell.Value.ToString();
                    //TODO: add your business logic for retrieve cell value
                }
            }
        }
コード例 #31
0
        //Excel.Range wRange;

        public DataExcel()
        {
            // 開啟一個新的應用程式
            excelApp = new Excel.Application
            {
                // 讓Excel文件可見
                //Visible = true,

                // 停用警告訊息
                DisplayAlerts = false
            };

            // 加入新的活頁簿
            excelApp.Workbooks.Add(Type.Missing);

            // 引用第一個活頁簿
            wBook = excelApp.Workbooks[1];

            // 設定活頁簿焦點
            wBook.Activate();

            // 引用第一個工作表
            wSheet = (Excel._Worksheet)wBook.Worksheets[1];

            // 命名工作表的名稱
            wSheet.Name = "result";

            // 設定工作表焦點
            wSheet.Activate();
        }
コード例 #32
0
ファイル: Ribbon1.cs プロジェクト: R-ye/ExcelAddin
        private void button2_Click_3(object sender, RibbonControlEventArgs e)
        {
            Microsoft.Office.Interop.Excel._Workbook wbook = Globals.ThisAddIn.Application.ActiveWorkbook; //获取激活的工作簿
                                                                                                           //  group1.Label = "当前共有 "+wbook.Sheets.Count.ToString()+" 张表\r\n自动引导至第一张表,表名为:"+wbook.Sheets[1].Name;//获取第一个工作表;

            //  Microsoft.Office.Interop.Excel.Worksheet newWorksheet = Globals.ThisAddIn.Application.ActiveSheet;
            // newWorksheet.Name = "Sheet1";
            Worksheet worksheet = wbook.Worksheets[1];//获取名为sheet1的工作表
            //   Worksheet worksheet = wbook.Worksheets[1];//获取名为sheet1的工作表
            string tag = worksheet.get_Range("A1", Missing.Value).Value2 + "###";

            if (tag.Length <= 0 || tag.IndexOf("日计表") == -1)
            {
                MessageBox.Show("未识别到有效数据", "错误");
                return;
            }

            int    num   = worksheet.Range["A1:H" + worksheet.UsedRange.Rows.Count].Find("100103000000").Row; //第一次出现空单元格的行数
            string str1  = worksheet.get_Range("E" + num, Missing.Value).Value2;
            string str2  = worksheet.get_Range("F" + num, Missing.Value).Value2;
            string str3  = worksheet.get_Range("G" + num, Missing.Value).Value2;
            int    num1  = worksheet.Range["A1:H" + worksheet.UsedRange.Rows.Count].Find("100102000000").Row; //第一次出现空单元格的行数
            string str11 = worksheet.get_Range("E" + num1, Missing.Value).Value2;
            string str21 = worksheet.get_Range("F" + num1, Missing.Value).Value2;
            string str31 = worksheet.get_Range("G" + num1, Missing.Value).Value2;
            double sum   = double.Parse(str1) + double.Parse(str11);
            double sum1  = double.Parse(str2) + double.Parse(str21);
            double sum2  = double.Parse(str3) + double.Parse(str31);

            //  MessageBox.Show(sum.ToString());
            MessageBox.Show("     现金收付\r\n----------------------------\r\n     收入:" + (sum).ToString() + "\r\n     付出:" + (sum1).ToString() + "\r\n     余额:" + (sum2).ToString());
        }
コード例 #33
0
        public static void Test()
        {
            //  创建一个excel应用
            MSExcel._Application app = new MSExcel.Application();

            //  打开一个excel文档
            MSExcel.Workbooks wbks = app.Workbooks;
            MSExcel._Workbook _wbk = wbks.Add(DEFAULT_CONTEMP_PATH + "statistic.xls");

            //  获取到excel中的一个sheet
            MSExcel.Sheets     shs  = _wbk.Sheets;
            MSExcel._Worksheet _wsh = (MSExcel._Worksheet)shs.get_Item(1);

            //Object Missing = System.Reflection.Missing.Value;


            String strValue = ((MSExcel.Range)_wsh.Cells[2, 1]).Text;

            Console.WriteLine(strValue);

            //  屏蔽掉系统跳出的Alert
            app.AlertBeforeOverwriting = false;

            //保存到指定目录
//            _wbk.SaveAs("./1.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            _wbk.SaveAs("./1.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        }
コード例 #34
0
ファイル: Form1.cs プロジェクト: mouadFahsi/analyse-prototype
        private void bt_Exp_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application app      = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook   wrkbk    = app.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet  wrksheet = null;
            app.Visible = true;

            wrksheet = wrkbk.ActiveSheet;
            for (int i = 1; i < personneDataGridView.Columns.Count + 1; i++)
            {
                wrksheet.Cells[1, i + 1] = personneDataGridView.Columns[i - 1].HeaderText;
            }

            for (int i = 0; i < personneDataGridView.Rows.Count - 1; i++)
            {
                for (int j = 0; j < personneDataGridView.Columns.Count; j++)
                {
                    if (personneDataGridView.Rows[i].Cells[j].Value != null)
                    {
                        wrksheet.Cells[i + 2, j + 2] = personneDataGridView.Rows[i].Cells[j].Value.ToString();
                    }
                    else
                    {
                        wrksheet.Cells[i + 2, j + 21] = "";
                    }
                }
            }
            for (int i = 0; i < personneDataGridView.Rows.Count; i++)
            {
                wrksheet.Cells[i + 2, 1] = personneDataGridView.Rows[i].HeaderCell.Value;
            }
        }
コード例 #35
0
 public override void Dispose()
 {
     if (_workbook != null)
     {
         Marshal.ReleaseComObject(_workbook);
         _workbook = null;
     }
 }
 public ExcelManipulation()
 {
     _excelApp = new Excel.Application();
     _flagexcelapp = 1;
     _excelApp.Visible = true;
     //Создаём новую книгу
     _workBook = (Excel._Workbook)(_excelApp.Workbooks.Add(Missing.Value));
     _workSheet = (Excel._Worksheet)_workBook.ActiveSheet;
 }
コード例 #37
0
ファイル: Export.cs プロジェクト: hprog/exchange
 public Export()
 {
     // Instantiate Excel and start a new workbook.
     objApp = new Excel.Application();
     objBooks = objApp.Workbooks;
     objBook = objBooks.Add(Missing.Value);
     objSheets = objBook.Worksheets;
     objSheet = (Excel._Worksheet)objSheets.get_Item(1);
 }
コード例 #38
0
ファイル: frmMainPo.cs プロジェクト: itktc/projectktc-v2
        public void generatExcelNoSubmit(string f)
        {
            string file_Name = f;
            m_oExcelApp = new Excel.Application();
            m_oExcelApp.Visible = false;

            m_oExcelApp.UserControl = false;

            m_oSheet = null;
            excelRange = null;

            try
            {
                m_oBook = m_oExcelApp.Workbooks.Add(missing);
                Excel.Sheets sheets = m_oBook.Worksheets;

                //Add new 4 Sheet
                //sheets.Add(System.Type.Missing, m_oBook.Sheets[3], 1, Excel.XlSheetType.xlWorksheet);
                //Product Sheet [Sheet1]
                m_oSheet = (Excel._Worksheet)m_oBook.Sheets[1];
                m_oSheet.Activate();
                m_oSheet.Name = "รายการเสนอซื้อ";
                SetData_to_SheetNoSubmit();

                string template = Application.StartupPath;
                string strRunReport = file_Name;
                //string strPass = "******"; password

                m_oBook.SaveAs(strRunReport, Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null);

                m_oExcelApp.Visible = true;
            }
            catch (interop.COMException ex)
            {
                MessageBox.Show("Error accessing Excel: " + ex.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            }
            finally
            {
                if (m_oExcelApp == null)
                {
                    m_oExcelApp.Quit();
                    m_oExcelApp = null;
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    GC.Collect();
                    GC.WaitForPendingFinalizers();

                }

            }
        }
コード例 #39
0
ファイル: Excel.cs プロジェクト: bcourter/SpaceClaim-AddIns
        public ExcelWorksheet()
        {
            excelApp.Visible = true;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            excelWorkBook = excelApp.ActiveWorkbook;
            if (excelWorkBook == null)
                excelWorkBook = (Excel._Workbook) excelApp.Workbooks.Add(Missing.Value);

            excelSheet = (Excel._Worksheet) excelWorkBook.ActiveSheet;
            excelSheet.Name = "SpaceClaim";
        }
コード例 #40
0
 public Workbook( Excel._Workbook oWB)
 {
     this.oWB = oWB;
     this.workbookSheets = oWB.Sheets;
     foreach (Excel._Worksheet openedWorkSheet in workbookSheets)
     {
         sheetCounter += 1;
         Sheet openedSheet = new Sheet(openedWorkSheet);
         setIndex(openedSheet);
         Sheets.Add(openedSheet);
         sheetDict[openedSheet] = openedWorkSheet;
     }
     this.ActiveSheet = Sheets[0];
 }
コード例 #41
0
ファイル: DatasheetParser.cs プロジェクト: rebbit/ScriptTools
 //Method to close excel connection
 private void ExcelClose()
 {
     if (xlApp != null) {
         try {
             xlWorkBook.Close();
             System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
             xlApp = null;
             xlWorkBook = null;
         } catch (Exception ex) {
             xlApp = null;
             MessageBox.Show("Unable to release the Object " + ex.ToString());
         } finally {
             GC.Collect();
         }
     }
 }
コード例 #42
0
        /**
         * if no log file exists this method creates new spreadsheet
        **/
        private bool CreateWorkBook()
        {
            m_WorkBook = (Excel._Workbook)(m_XLApp.Workbooks.Add(Missing.Value));
            m_Sheet = (Excel._Worksheet) m_WorkBook.ActiveSheet;
            if (m_WorkBook == null || m_Sheet == null)
            {
                return false;
            }

            m_Sheet.Cells[1, 1] = "Date";
            m_columnsMap = new Dictionary<string, int>();
            foreach (KeyValuePair<string, ProjectData> entry in m_currProjectWarnings)
            {
                AddNewProjectColumn(entry.Key);
            }
            //addColumn("Total");

            ApplyStyle();

            return true;
        }
コード例 #43
0
        private void tmrData_Tick(object sender, EventArgs e)
        {
            int i;
            uint recLength;

            recLength = 25;
            // Get the Live data, to show in gauge
            txBuf[0] = (byte)CMD.GET_LIVE_DATA;
            txBuf[1] = 2;
            //USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
            string port = cbxPorts.SelectedItem.ToString();
            if (port == "")
                return;
            System.IO.Ports.SerialPort myPort = new System.IO.Ports.SerialPort(port);
            if (myPort.IsOpen == false) //if not open, open the port
                myPort.Open();
            //do your work here
            if (myPort.IsOpen == false) //if not open, open the port
                return;
            String data;
            data = myPort.ReadLine();
            myPort.Close();
            string [] dht=data.Split(',');
            //s.dataPtr = rxBuf[18];
            //i = 19;
            //convertData(ref currData, ref i);
            //gTemp.Value = currData.temp;
            //gPressure.Value = currData.pressure;
            //gHumid.Value = currData.humidity;
            if (dht.Length != 3)
                return;
            gHumid.Value = float.Parse(dht[0]);
            gTemp.Value = float.Parse(dht[1]);
            gPressure.Value = int.Parse(dht[2]);

            if (stt == 0)
            {
                //Start Excel and get Application object.
                oXL = new Excel.Application();
                oXL.Visible = true;

                //Get a new workbook.
                oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;
            }
                //Add table headers going cell by cell.
                oSheet.Cells[stt + 1, 1] = gTemp.Value.ToString();
                oSheet.Cells[stt + 1, 2] = gPressure.Value.ToString();
                oSheet.Cells[stt + 1, 3] = gHumid.Value.ToString();
                //oSheet.Cells[1, 4] = "Salary";

            DateTime timeNow = DateTime.Now;
            w[stt].humidity = gHumid.Value;
            w[stt].temp = gTemp.Value;
            w[stt].pressure = gPressure.Value;
            w[stt++].time = timeNow;
            if (stt == 999)
                stt = 0;
            Redraw(0);

            goLive = false;
            if (goLive) // Only load data to w if graph shows live data from PIC
            {
                recLength = 64;
                txBuf[0] = (byte)CMD.GET_FIRST_DATA;
                txBuf[1] = 2;
                USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
                loadData(10);
                for (i = 0; i < 5; i++)
                {
                    txBuf[0] = (byte)CMD.GET_NEXT_DATA;
                    txBuf[1] = 2;
                    USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
                    loadData(10);
                }

                txBuf[0] = (byte)CMD.GET_CURR_DATA;
                txBuf[1] = 2;
                USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
                loadData((int)s.dataPtr);
            }
        }
コード例 #44
0
 public void CloseBook()
 {
     object strFilePath = strOutputPath + "\\Data_all_summary";
     object oMissing = System.Reflection.Missing.Value;
     object xfFormat = Excel.XlFileFormat.xlXMLSpreadsheet;
     oWB.SaveAs(strFilePath,
         Excel.XlFileFormat.xlWorkbookDefault, oMissing, oMissing,
         false, false, Excel.XlSaveAsAccessMode.xlNoChange,
         oMissing, oMissing, oMissing, oMissing, oMissing);
     oWB.Close(false, oMissing, oMissing);
     oWB = null;
     oXL.Quit();
     oXL = null;
 }
コード例 #45
0
ファイル: Form1.cs プロジェクト: Specter85/SpreadSheetDiffer
        // 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);
                }
            }
        }
コード例 #46
0
ファイル: ExcelHelper.cs プロジェクト: wybq68/DIH_LUMBARROBAT
        private string GetExcelSheetName(string pPath)
        {
            //打开一个Excel应用

            _excelApp = new Excel.Application();
            if (_excelApp == null)
            {
                throw new Exception("打开Excel应用时发生错误!");
            }
            _books = _excelApp.Workbooks;
            //打开一个现有的工作薄
            _book = _books.Add(pPath);
            _sheets = _book.Sheets;
            //选择第一个Sheet页
            _sheet = (Excel._Worksheet)_sheets.get_Item(1);
            string sheetName = _sheet.Name;

            ReleaseCOM(_sheet);
            ReleaseCOM(_sheets);
            ReleaseCOM(_book);
            ReleaseCOM(_books);
            _excelApp.Quit();
            ReleaseCOM(_excelApp);
            return sheetName;
        }
コード例 #47
0
ファイル: ExcelTools.cs プロジェクト: raulalf7/ALF_DLL
        private static string OpenExcel(ExcelInfo excelInfo, bool isOpenSheet = true)
        {
            Console.WriteLine("Open File:【{0}】", excelInfo.FilePath);
            if (!File.Exists(excelInfo.FilePath))
            {
                return $"文件【{excelInfo.FilePath}】不存在";
            }

            _objExcel = new Excel.Application { Visible = false, DisplayAlerts = false, AlertBeforeOverwriting = false };

            _objBooks = _objExcel.Workbooks;
            if (excelInfo.FilePath.Equals(String.Empty) || !File.Exists(excelInfo.FilePath))
            {
                _objBook = _objBooks.Add(ObjOpt);
            }
            else
            {
                _objBook = _objBooks.Open(excelInfo.FilePath, ObjOpt, ObjOpt, ObjOpt, ObjOpt,
                                          ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt);
            }
            if (isOpenSheet)
            {
                _objSheet = OpenSheet(excelInfo);
                if (_objSheet == null)
                {
                    return "没有指定页签";
                }
            }
            return "";
        }
コード例 #48
0
ファイル: PhoneScraper.cs プロジェクト: hsxu/scrapers
        // Program entry point.
        static void Main(string[] args)
        {
            oXL = new Excel.Application();
            oXL.Visible = true;

            oWB = (Excel._Workbook)(oXL.Workbooks.Add(Type.Missing));
            oSheet = (Excel._Worksheet)oWB.ActiveSheet;

            Console.WriteLine("Excel worksheet created.");

            List<string> phonePages = GetListOfPhonePages();
            List<string> specPages = FindPhoneSpecPages(phonePages);

            int i = 1;
            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();
            foreach (string specPage in specPages)
            {
                // Specs are extracted on multiple threads to bypass
                // the time it takes to make web requests and load the DOM on the page.
                new Thread(() =>
                {
                    ExtractSpecs(specPage, i);
                }).Start();
                Random random = new Random();
                Thread.Sleep(random.Next(1000,3000));
                i++;
            }

            for (int j = 0; j < 1000; j++)
            {
                Thread.Sleep(1000);
                TimeSpan ts = stopWatch.Elapsed;
                // Format and display the TimeSpan value.
                string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                    ts.Hours, ts.Minutes, ts.Seconds,
                    ts.Milliseconds / 10);
                Console.WriteLine("RunTime " + elapsedTime);
            }
        }
コード例 #49
0
 public ExcelActiveDocument(IOfficeApplication application, MsExcel._Workbook workbook)
     : base(application, workbook.FullName)
 {
     _workbook = workbook;
     InitializeWsDocument(FullPath);
 }
コード例 #50
0
ファイル: ExcelRead.cs プロジェクト: anthonied/CRM
        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);
        }
コード例 #51
0
        public Workbook OpenWorkbook(string path = null)
        {
            if (path != null)
            {
                operatorWB = oXL.Workbooks.Open(path);
                Workbook _workbook = new Workbook(operatorWB);
                return _workbook;
            }

            else
            {
                operatorWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
                Workbook _workbook = new Workbook(operatorWB);
                return _workbook;
            }
        }
コード例 #52
0
ファイル: main.cs プロジェクト: martsve/excel
        static void Main(string[] args)
        {
            // set english culture (for english function names and . decimal)
            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");

            // list seperator is now , Trying to change it to ; doesnt work :(
            // System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator = ";";

            // show manual
            bool showMan = false;

            // check pipe
            String pipedText = "";
            bool isKeyAvailable;
            bool piped = false;
            try { isKeyAvailable = System.Console.KeyAvailable; }
            catch { pipedText = System.Console.In.ReadToEnd(); piped = true; }

            // if no args or pipe, show manual
            if (piped == false && args.Length == 0) showMan = true;

            // set default values
            string infile = "";
            string paste = "";
            List<string> macro = new List<string>();
            int[] cellA = new int[2] { 0, 1 };
            int[] cellB = new int[2] { 0, 1 };
            int[] outCellA = new int[2] { 0, 1 };
            int[] outCellB = new int[2] { 0, 0 };
            string sheet = "";
            string active = "";

            // warnings are off by default, since excel will warn about ANYTHING, which interupts the program and leads to errors.
            // f.eks. log charts will make the program fail, when they are given blank data in the step before new data is provided.
            bool warning = false;

            // save on exit
            bool save = true;

            // convert error codes to text in output
            bool outErr = true;

            // paste as text
            bool asText = false;

            // blehh..
            string errLine = "--------------------------------------------------------------------------------";

            // tab as default output space character
            string cellSpacer = "\t";

            // all charts that should be saved
            List<string> charts = new List<string>();

            // check input arguments
            int iarg = 0;
            for (int i = 0; i < args.Length;i++ )
            {
                if (args[i].StartsWith("-")) {

                    // show manual
                    if (args[i] == "-help" || args[i] == "--help" || args[i] == "-?") showMan = true;

                    // specify macro to run
                    if (args[i] == "-m") {
                      try {
                          macro.Add(args[i + 1]);
                        i++;
                      }
                      catch {
                        Error("No macro name given for -m.",1);
                      }
                    }
                    // paste input as text?
                    if (args[i] == "-t") asText = true;
                    // dont save
                    if (args[i] == "-n") save = false;
                    // hide warnings
                    if (args[i] == "-w") warning = true;
                    // set space character
                    if (args[i] == "-b")
                    {
                        cellSpacer = " ";
                        if (args.Length > i+1)
                            if (args[i + 1].Length == 1)
                            {
                                cellSpacer = args[i + 1];
                                i++;
                            }
                    }
                    // set paste sheet
                    if (args[i] == "-p")
                    {
                        try
                        {
                            active = args[i + 1];
                            i++;
                        }
                        catch
                        {
                            Error("No paste name given for -p.", 1);
                        }
                    }
                    // set output sheet
                    if (args[i] == "-s")
                    {
                        try
                        {
                            sheet = args[i + 1];
                            i++;
                        }
                        catch
                        {
                            Error("No sheet name given for -s.", 1);
                        }
                    }
                    // blank errors
                    if (args[i] == "-#") outErr = false;
                }

                else {
                    // excel file
                    if (iarg == 0) infile = args[i];
                    // paste file
                    else if (iarg == 1 && !piped)
                    {
                        paste = args[i];
                        if (paste == "~" || paste == "")
                        {
                            paste = "";
                            iarg++; iarg++;
                        }
                    }
                    // input cell ref
                    else if (iarg == 2)
                    {
                        string[] cellArr = args[i].Split(':');
                        if (cellArr.Length == 1) {
                             cellA = ExcelCellRef(cellArr[0]);
                        }
                        else {
                            cellA = ExcelCellRef(cellArr[0]);
                            cellB = ExcelCellRef(cellArr[1]);
                            iarg++;
                        }
                    }

                    else if (iarg == 3)
                    {
                        cellB = ExcelCellRef(args[i]);
                    }
                    // output cell ref
                    else if (iarg == 4)
                    {
                        string[] cellArr = args[i].Split(':');
                        if (cellArr.Length == 1)
                        {
                           outCellA = ExcelCellRef(cellArr[0]);
                        }
                        else
                        {
                            outCellA = ExcelCellRef(cellArr[0]);
                            outCellB = ExcelCellRef(cellArr[1]);
                            iarg++;
                        }
                    }
                    else if (iarg == 5)
                    {
                        outCellB = ExcelCellRef(args[i]);
                    }

                    // output charts
                    else if (iarg > 5)
                    {
                        charts.Add(args[i]);
                    }

                    iarg++;
                }
            }

            // Print header
            if (showMan)
            {
                Print(@"Usage: excel [OPTIONS] ExcelFile PasteFile Cell1 Cell2 OutCell1 OutCell2 [Chart1 [Chart2 ..]]
               or: excel =FORMULA

            Opens ExcelFile and places the contents of PasteFile from position given.
            Position is the range between Cell1 and Cell2. Unused cells are cleared.
            Echos all filled rows in the range between OutCell1 and OutCell2.
            Any charts named will be saved to <ExcelFile_ChartN>.png.
            'Sheet.ChartN' can be used if multiple charts has the same name.

            -p PasteSheet   Select the sheet that should be pasted to.
            -s OutSheet     Select the sheet that should be outputted.
            -m Macro        Run macro after paste. If -m is used multiples
                    times, more than 1 macro can be executed.
            -n              Do not save workbook
            -w              display Excel dialogs. Default is to surpress.
            -#              Replace errors with blanks in output
            -b [CHAR]       Set cell-spacing character in output to
                    'space' or 'CHAR' (default is 'tab')
            -t              Insert PasteFile as text instead of numbers

            If '~' is specified as PasteFile no file is loaded and Cell1 and Cell2
            should not be specified.

            Version 1.0. Report bugs to <*****@*****.**>");
                Environment.Exit(0);
            }

            if (infile.StartsWith("="))
            {
                string result = "";
                try
                {
                    result = ExcelMath.Calc(infile);
                }
                catch (Exception ex) {

                    Console.Error.WriteLine("Error: " + ex.Message);
            #if DEBUG
                    Console.ReadKey();
            #endif
                    Environment.Exit(1);
                }

                Print(result);
            #if DEBUG
                Console.ReadKey();
            #endif
                Environment.Exit(0);
            }

            // open file
            if (piped == false && infile == "") Error("No file given.", 1);
            System.IO.TextReader stream = new StringReader(pipedText);
            if (!piped && paste.Length > 0)
            {
                try { stream = new StreamReader(paste); }
                catch (Exception e) { Error("Unable to open file: " + paste + "\n\n"+errLine+"\n\n"+e.ToString(), 1); }
            }

            // number of lines and columns
            int N = 0;
            int C = 0;

            // input data, as numbers and text. only one will be used
            double[,] cells = null;
            string[,] sCells = null;

            // if pasted text
            if (paste.Length > 0)
            {
                String line;
                List<string[]> strings = new List<string[]>();

                // trim all lines and split between words
                while ((line = stream.ReadLine()) != null)
                {
                    line = line.Replace(",", " ");
                    line = line.Replace("\t", " ");
                    line = line.Trim();
                    line = System.Text.RegularExpressions.Regex.Replace(line, @"\s+", " ");
                    if (asText)
                    {
                        //if (line.Length > 0)
                            strings.Add(line.Split(' '));
                    }
                    else if (line.Length > 0 && !line.StartsWith("#"))
                        strings.Add(line.Split(' '));
                }

                // set number of rows and columns
                N = strings.Count();
                C = 0;
                foreach (string[] str in strings) if (str.Length > C) C = str.Length;

                // convert input data to a format the Excel-interop understands: var[,]
                if (asText) sCells = new string[N, C];
                else cells = new double[N, C];

                // parse all cells and add to array
                for (int i = 0; i < N; i++)
                {
                    for (int j = 0; j < strings[i].Length; j++)
                    {
                        try
                        {
                            if (asText) sCells[i, j] = strings[i][j];
                            else cells[i, j] = double.Parse(strings[i][j]);
                        }
                        catch (Exception e) { Error("Unable to parse number in paste file, line " + (i + 1) + ", column " + (j + 1) + ":\n" + strings[i][j] + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
                    }
                }
            }

            Excel.Range startCell;
            Excel.Range endCell;

            try
            {
                // open excel app
                oXL = new Excel.Application();

                if (!warning) oXL.DisplayAlerts = false;

                try
                {
                    // try to open the selected excel file
                    // we turn of errors, since excel prompts for macro-enabled files and other things
                    oXL.DisplayAlerts = false;
                    // we need the absolute file path, since excel defaults to the user home dir, not the current working dir :S
                    oWB = oXL.Workbooks.Open(Path.GetFullPath(infile));
                    // turn back on warnings if wanted
                    if (warning) oXL.DisplayAlerts = true;
                }
                catch (Exception e) { throw new System.Exception("Unable to open file: " + Path.GetFullPath(infile) + "\n\n" + errLine + "\n\n" + e.ToString()); }

                // set the active sheet
                if (active.Length > 0)
                {
                    try
                    {
                        oSheet = (Excel._Worksheet)oWB.Sheets[active];
                    }
                    catch (Exception e) { throw new System.Exception("Unable to select worksheet: " + active + "\n\n" + errLine + "\n\n" + e.ToString()); }
                }
                else
                    oSheet = (Excel._Worksheet)oWB.Worksheets[1];

                // insert data
                if (paste.Length > 0)
                {
                    // if only columns are specified, find the amount of rows used
                    if (cellA[0] == 0 && cellB[0] == 0)
                    {
                        string cell = GetExcelColumnName(cellA[1]) + ":" + GetExcelColumnName(cellB[1]);
                        Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
                        cellA[0] = 1;
                        cellB[0] = r.Rows.Count;
                    }

                    // select and paste values
                    try
                    {
                        startCell = (Excel.Range)oSheet.Cells[cellA[0], cellA[1]];
                        endCell = (Excel.Range)oSheet.Cells[cellB[0], cellB[1]];
                        oSheet.get_Range(startCell, endCell).Value = null;

                        endCell = (Excel.Range)oSheet.Cells[cellA[0] + N - 1, cellA[1] + C - 1];

                        if (asText) oSheet.get_Range(startCell, endCell).Value2 = sCells;
                        else oSheet.get_Range(startCell, endCell).Value2 = cells;
                    }
                    catch (Exception e)
                    {
                        string inputCell = GetExcelColumnName(cellA[1]) + cellA[0] + ":" + GetExcelColumnName(cellB[1]) + cellB[0];
                        throw new System.Exception("Unable to select input cells:\n\n         " + inputCell + "\n\n" + errLine + "\n\n" + e.ToString());
                    }
                }

                // run macro
                for (int i = 0; i < macro.Count; i++)
                {
                    try
                    {
                        oXL.Run(macro[i]);
                    }
                    catch (Exception e) { throw new System.Exception("Unable to run macro: " + macro[i] + "\n\n" + errLine + "\n\n" + e.ToString()); }
                }

                // force workbook refresh
                oXL.Calculate();

                // go to result sheet
                if (sheet.Length > 0)
                {
                    try
                    {
                        oSheet = (Excel._Worksheet)oWB.Sheets[sheet];
                    }
                    catch (Exception e) { throw new System.Exception("Unable to select output sheet:" + sheet + "\n\n" + errLine + "\n\n" + e.ToString()); }
                }

                // save charts

                foreach (Excel.Worksheet cSheet in oWB.Worksheets)
                {
                    // loop trough all charts

                    Excel.ChartObjects xlCharts = (Excel.ChartObjects)cSheet.ChartObjects(Type.Missing);
                    for (int i = 1; i <= xlCharts.Count; i++)
                    {

                        Excel.ChartObject oChart = (Excel.ChartObject)xlCharts.Item(i);
                        Excel.Chart chart = oChart.Chart;

                        string chartName = "";
                        if (charts.Contains(cSheet.Name + "." + oChart.Name)) chartName = cSheet.Name + "." + oChart.Name;
                        else if (charts.Contains(oChart.Name)) chartName = oChart.Name;

                        // if chart is specified for output, save it
                        if (chartName.Length > 0)
                        {
                            int id = charts.FindIndex(s => s == chartName);

                            charts.RemoveAt(id);

                            try
                            {
                                // we need full path name again.. excel defaults to user home dir...
                                string saveas = Path.GetFullPath(infile);
                                saveas = Path.GetDirectoryName(saveas) + "\\" + Path.GetFileNameWithoutExtension(saveas);
                                saveas = saveas + "_" + chartName + ".png";
                                chart.Export(saveas, "PNG");
                            }
                            catch (Exception e) { throw new System.Exception("Unable to save chart '" + chartName + "':\n\n" + errLine + "\n\n" + e.ToString()); }
                        }
                    }
                }

                // if any charts was not found; throw an error.
                if (charts.Count > 0)
                {
                    string list = "";
                    foreach (string s in charts) list += s + ", ";
                    throw new Exception("Unable to find chart(s): " + list);
                }

                // if only columns are specified, find amount of rows to use
                if (outCellA[0] == 0 && outCellB[0] == 0)
                {

                    if (outCellB[1] == 0) outCellB[1] = oSheet.UsedRange.Columns.Count;

                    string cell = GetExcelColumnName(outCellA[1]) + ":" + GetExcelColumnName(outCellB[1]);
                    Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
                    outCellA[0] = 1;
                    outCellB[0] = r.Rows.Count;
                }

                // select the output cell range
                try
                {
                    startCell = (Excel.Range)oSheet.Cells[outCellA[0], outCellA[1]];
                    endCell = (Excel.Range)oSheet.Cells[outCellB[0], outCellB[1]];
                }
                catch (Exception e)
                {
                    string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
                    throw new System.Exception("Unable to select output cells:\n            " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
                }

                // get output from selected cells
                object[,] arr = null;
                try
                {
                    Excel.Range r = (Excel.Range)oSheet.get_Range(startCell, endCell);
                    // if only 1 cell is selected, excel will return an object instead of object array!
                    if (r.Cells.Count == 1)
                    {
                        arr = new object[2, 2];
                        arr[1, 1] = r.Cells.Value2;
                    }
                    else arr = r.Cells.Value2 as object[,];
                }
                catch (Exception e)
                {
                    string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
                    throw new System.Exception("Invalid OutCells given. Unable to retrieve data:\n            " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
                }

                List<string> results = new List<string>();
                int last = 0;
                N = outCellB[0] - outCellA[0] + 1;
                C = outCellB[1] - outCellA[1] + 1;

                // loop trough output rows
                for (int i = 1; i <= N; i++)
                {
                    // loop trough output columns
                    string s = "";
                    for (int j = 1; j <= C; j++)
                    {
                        // check if cell contains an error
                        if (arr[i, j] is Int32)
                        {
                            if (outErr)
                            {
                                int eCode = (int)arr[i, j];
                                string e = "";

                                if (eCode == -2146826281) e = "#DIV/0!";
                                else if (eCode == -2146826246) e = "#N/A";
                                else if (eCode == -2146826259) e = "#NAME?";
                                else if (eCode == -2146826288) e = "#NULL!";
                                else if (eCode == -2146826252) e = "#NUM!";
                                else if (eCode == -2146826265) e = "#REF!";
                                else if (eCode == -2146826273) e = "#VALUE!";
                                // no more error codes exists (?) as of 2013.. But to be sure / support future ones:
                                else e = "#ERR" + eCode.ToString();

                                s = s + e + " " + cellSpacer;
                            }
                            else s = s + " " + cellSpacer;
                        }
                        else if (arr[i, j] != null) s = s + arr[i, j].ToString() + cellSpacer;
                        else s = s + " " + cellSpacer;
                    }
                    // remove cellspacer from last column
                    if (C > 0) results.Add(s.Remove(s.Length - 1).TrimEnd());
                    // record last row column with content
                    if (s.Replace(cellSpacer, " ").TrimEnd().Length > 0) last = results.Count();
                }

                // write output to console
                for (int i = 0; i < last; i++)
                    Console.WriteLine(results[i]);

                // save file
                if (save)
                {
                    // if macros are enabled, excel would prompt about saving
                    oXL.DisplayAlerts = false;
                    oWB.Save();
                }

            }

                // catch any exception
            catch (Exception theException)
            {
                Error(errLine + "\n  Error: " + theException.Message, 1);
            }

            finally
            {

                // clean up and exit
                CleanUp();
            }

            #if DEBUG
                Console.ReadKey();
            #endif
        }
コード例 #53
0
        //private bool createJobCostingSheet(ServiceSubmissionModel submission, string filename)
        private int createJobCostingSheet(ServiceSheetViewModel submission, string filename, bool firstSubmission, int lineNumber)
        {
            Excel.Range range;
            //First Open excel

            if (firstSubmission)
            {
                excelApplication = new Excel.Application();
                excelApplication.Visible = true;
                excelWorkbook = excelApplication.Workbooks.Open(filename);

                excelWorksheet = excelWorkbook.ActiveSheet;

                //Add the job title
                string customer = submission.Customer;
                string machineMake = submission.MachineMakeModel;
                string serialNumber = submission.MachineSerial;
                string jobDescription = submission.JobDescription;

                range = excelWorksheet.Cells[13, 1];
                range.Value2 = string.Concat(customer, " - ", machineMake, " - S/N: ", serialNumber, " - ", jobDescription);

                range = excelWorksheet.Cells[11, 9];
                range.Value2 = submission.MttJobNumber;
            }
            //Load all the days and loop through them. Output to the sheet
            AllServiceDayViewModels serviceDays = submission.AllServiceDays;

            //Need the engineers initials for each row
            string engFirstName = submission.UserFirstName;
            string engSurname = submission.UserSurname;
            string initials = string.Concat(engFirstName[0], engSurname[0]);

            int sheetNo = submission.SubmissionNumber;

            int currentSpreadsheetRow;

            if (lineNumber == -1)
            {
                currentSpreadsheetRow = 17;
            }
            else
            {
                currentSpreadsheetRow = lineNumber++;
            }


            foreach (ServiceDayViewModel currentDay in serviceDays.AllServiceDayVMs)
            {
                range = excelWorksheet.Cells[currentSpreadsheetRow, 1];
                range.Value2 = currentDay.DtReport;

                string day = currentDay.DtReport.DayOfWeek.ToString();
                range = excelWorksheet.Cells[currentSpreadsheetRow, 2];
                range.Value2 = day;

                double standardHours = calculateStandardHours(currentDay);
                range = excelWorksheet.Cells[currentSpreadsheetRow, 3];
                range.Value2 = standardHours;

                double overtimeHours = calculateOvertimeHours(currentDay);
                range = excelWorksheet.Cells[currentSpreadsheetRow, 4];
                range.Value2 = overtimeHours;

                range = excelWorksheet.Cells[currentSpreadsheetRow, 5];
                range.Value2 = currentDay.TotalTravelTime;

                bool dailyAllowance = currentDay.DailyAllowance;
                int dailyAllowanceValue = convertBoolToIntForAllowances(dailyAllowance);
                range = excelWorksheet.Cells[currentSpreadsheetRow, 6];
                range.Value2 = dailyAllowanceValue;

                bool overNight = currentDay.OvernightAllowance;
                int overnightAllowanceValue = convertBoolToIntForAllowances(overNight);
                range = excelWorksheet.Cells[currentSpreadsheetRow, 7];
                range.Value2 = overnightAllowanceValue;

                range = excelWorksheet.Cells[currentSpreadsheetRow, 8];
                range.Value2 = currentDay.Mileage;

                range = excelWorksheet.Cells[currentSpreadsheetRow, 9];
                range.Value2 = initials;

                range = excelWorksheet.Cells[currentSpreadsheetRow, 10];
                range.Value2 = sheetNo;

                currentSpreadsheetRow++;
            }

            MessageBox.Show("Need to handle bank holidays");

            return currentSpreadsheetRow;
        }
コード例 #54
0
ファイル: LaptopScraper.cs プロジェクト: hsxu/scrapers
        static void Main(string[] args)
        {
            oXL = new Excel.Application();
            oXL.Visible = true;

            oWB = (Excel._Workbook)(oXL.Workbooks.Add(Type.Missing));
            oSheet = (Excel._Worksheet)oWB.ActiveSheet;

            Console.WriteLine("Excel worksheet created.");

            List<string> reviewPages = GrabDataPages();
            Console.WriteLine("Data pages found.");

            List<string> specPages = NavigateToSpecs(reviewPages);
            Console.WriteLine("Spec pages found.");

            ExtractSpecs(specPages);
        }
コード例 #55
0
ファイル: ExcelHelper.cs プロジェクト: wybq68/DIH_LUMBARROBAT
 /// <summary>
 /// 创建一个Excel程序实例
 /// </summary>
 private void CreateExcelRef()
 {
     _excelApp = new Excel.Application();
     _books = (Excel.Workbooks)_excelApp.Workbooks;
     _book = (Excel._Workbook)(_books.Add(_optionalValue));
     _sheets = (Excel.Sheets)_book.Worksheets;
     _sheet = (Excel._Worksheet)(_sheets.get_Item(1));
 }
コード例 #56
0
        public void QuitBook()
        {
            object oMissing = System.Reflection.Missing.Value;
            if (oWB != null)
            {
                oWB.Close(false, oMissing, oMissing);
                oWB = null;
            }

            if (oXL != null)
            {
                oXL.Quit();
                oXL = null;
            }
        }
コード例 #57
0
        private void automateExcelSpreadsheet_Click(object sender, EventArgs e)
        {
            Excel.Range oRng;

            try
            {
                oXL = new Excel.Application();
                oXL.Visible = true;

                oWB = oXL.Workbooks.Add(Missing.Value);
                oWB.Title = "Test";
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;
                oSheet.Name = "Test";

                oSheet.Cells[1, 1] = "First Name";
                oSheet.Cells[1, 2] = "Last Name";
                oSheet.Cells[1, 3] = "E-mail";
                oSheet.Cells[1, 4] = "Salary";

                oSheet.get_Range("A1", "D1").Font.Bold = true;
                oSheet.get_Range("A1", "D1").RowHeight = 37;
                oSheet.get_Range("A1", "D1").VerticalAlignment =
                    Excel.XlVAlign.xlVAlignCenter;

                string[,] saNames = new string[5, 2];

                saNames[0, 0] = "John";
                saNames[0, 1] = "Smith";
                saNames[1, 0] = "Tom";
                saNames[1, 1] = "Brown";
                saNames[2, 0] = "Sue";
                saNames[2, 1] = "Thomas";
                saNames[3, 0] = "Jane";
                saNames[3, 1] = "Jones";
                saNames[4, 0] = "Adams";
                saNames[4, 1] = "Johnson";

                oSheet.get_Range("A2", "B6").Value2 = saNames;

                oRng = oSheet.get_Range("C2", "C6");
                oRng.Formula = "=A2 & \".\" & B2 & \"@\" & \"gmail.com\"";

                oRng = oSheet.get_Range("D2", "D6");
                oRng.Formula = "=RAND()*10000";
                oRng.NumberFormat = "$0.00";

                oRng = oSheet.get_Range("A1", "D1");
                oRng.EntireColumn.AutoFit();

                DisplayQuarterlySales(oSheet);

                oXL.Visible = true;
                oXL.UserControl = true;
            }
            catch (Exception exception)
            {
                string errorMessage = "Error :";

                errorMessage = string.Concat(errorMessage, exception.Message);
                errorMessage = string.Concat(errorMessage, " Line: ");
                errorMessage = string.Concat(errorMessage, exception.Source);

                MessageBox.Show(errorMessage, "Error");
            }
        }
コード例 #58
0
        public void OpenBook()
        {
            if (oXL != null || oWB != null)
            {
                return;
            }
            try
            {
                //Start Excel and get Application object.
                oXL = new Excel.Application();
                oXL.Visible = false;

                //Get a new workbook.
                object oTemplate = strExcelTemplate; //"c:\\Site Comparison Data (for auto poulation).xls";
                oWB = oXL.Workbooks.Add(oTemplate);
                //AppendSheet(oWB);
                //oWB.Save();
                //oXL.Quit();
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);
                throw theException;
            }
        }
コード例 #59
0
ファイル: DatasheetParser.cs プロジェクト: rebbit/ScriptTools
        //Method to initialize opening Excel
        private void ExcelInit(String path, out List<Product> idinfo)
        {
            xlApp = new Excel.Application();
            string dsSheet = "datasheet";
            string idinfoSheet = "idinfo";
            idinfo = null;
            if (System.IO.File.Exists(path)) {
                // then go and load this into excel
                xlWorkBook = xlApp.Workbooks.Open(path,
                0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t",
                false, false, 0, true, 1, 0);

                //read info datasheet first to generate product specs list
                info_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(idinfoSheet);
                idinfo = ReadIdInfoIntoDataTable(info_xlWorkSheet);
                //read datasheet and load the data into product specs list
                //ds_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(dsSheet);
                //ReadDatasheetIntoDataTable(ds_xlWorkSheet);
            } else {
                MessageBox.Show("Unable to open excel file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp = null;
                System.Windows.Forms.Application.Exit();
            }
        }
コード例 #60
0
ファイル: Form1.cs プロジェクト: Specter85/SpreadSheetDiffer
        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            // Refrences to the first workbook to be diffed.
            mBook1Sheets = null;
            if (mBook1 != null)
            {
                mBook1.Close();
                mBook1 = null;
            }

            // Refrences to the second workbook to be diffed.
            mBook2Sheets = null;
            if (mBook2 != null)
            {
                mBook2.Close();
                mBook2 = null;
            }

            // Refrence to the instance of Microsoft Excel being used.
            mExcel.Quit();
            mExcel = null;
        }