如何在不引入任何延迟的情况下访问excel文件？ [英] how to access excel files without introducing any delay?

问题描述

我正在构建一个应用程序来保存excel文件中来自usb端口的数据，但是我必须打印相同数据的图表。但是在同一个程序中执行这两项任务时，更新chart时会有延迟。请帮助我。我是c＃的新手。

这是我的代码

i am building an application to save data from usb port in excel file but I have to print the chart of same data .But while doing both task in the same program there is a delay while updating chart.please help me out .i am new to c# .
this is my code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO.Ports;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;
using System.Collections;
namespace prototype
{
    public partial class Form1 : Form
    {
        Hashtable myHashtable;
        public string key, add_data, data, filename, pathfile, rxd_no,date,ravi1;
        private DateTime datetime;
        public string time;
        public int i, k,ravi;
        private SerialPort myport;
        private string in_data;
        private string j,l;
        public string[] arr = new string[60];


        //Excel._Application xlApp; // TO access excel file
        //Excel.Workbook xlWorkBook;
        //Excel.Worksheet xlWorkSheet;
        //object misValue = System.Reflection.Missing.Value;

        public string[] arr1 = new string[1000];
        public string[] rxd_data = new string[5];



        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            date = DateTime.Today.ToShortDateString(); // get the current date
            pathfile = @"C:\Users\BADLIA\Documents\";

            filename = DateTime.Now.ToString("yyyyMMdd") + ".xls"; // file name = current date
            if (File.Exists(pathfile + filename) == true) 
            { }
            else
            {   
                File.CreateText(pathfile + filename).Close();
                
            }


            // System.IO.File.AppendAllText(pathfile + filename, data_tb.Text); // to add data in current file
            //  System.IO.File.Move(@"C:\Users\CMSR LABS\Desktop\data\SAM.txt", @"C:\Users\CMSR LABS\Desktop\data\SAMUEL.txt");
            Excel._Application xlApp; // TO access excel file
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            MessageBox.Show(" Data has been saved to " + pathfile, "Save File");
            xlApp = new Excel.Application();

            xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            key = xlWorkSheet.get_Range("A1", "A1").Value2.ToString();
            int.TryParse(key, out i);
             


            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
       
            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            //This function automatically performs tasks defined in when the application is initialized.   

            MessageBox.Show(key);
        }
        private void releaseObject(object obj)
        {

            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                 obj = null;
            }

            catch (Exception ex)
            {
               obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());

            }
            finally
            {
                GC.Collect();
         
           }
            this.Invoke(new EventHandler(btnExport_Click));
            


        }

        private void button2_Click(object sender, EventArgs e)
        {
            myport = new SerialPort(); // define new serial port
            myport.BaudRate = 9600; // set baudrate of new serial   port
            myport.PortName = port_name.Text; // gets the name of the port which is written in textbox named "port_name"
            myport.Parity = Parity.None;
            myport.DataBits = 8;
            myport.StopBits = StopBits.One;
            port_name.Visible = true;
            // Serial write  

            myport.DataReceived += myport_DataReceived;
          
            try
            {
                myport.Open(); // start serial port
                myport.WriteLine("start"); // Serial write  
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error"); // displays"error" when exception occurs
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                myport.Close(); // close communication
            }
            catch (Exception ex2)
            {
                MessageBox.Show(ex2.Message, "Error");
            }
        }

         void myport_DataReceived(object sender, SerialDataReceivedEventArgs e) // predefined funtion name " <portname>_Datareceived"
        {
            //this function is called automatically whenever data is received on serial port

           // rxd_no = myport.ReadLine(); // "readline" read upto new line    

            in_data = myport.ReadTo("\x02");
            data = myport.ReadTo("\x03");// read upto new line


            int time_sec = DateTime.Now.Second; // get the current second
            j = in_data;
            l = data;
            k = time_sec;
            arr[k] = j;
            arr1[k] = l;

            this.Invoke(new EventHandler(displaydata_event)); // call functiom "displaydata_event"
        }


         private void displaydata_event(object sender, EventArgs e)
         {
             datetime = DateTime.Now; // define variable
             string time = datetime.Hour + ":" + datetime.Minute + ":" + datetime.Second;
             try
            
             {
                 int time_sec = DateTime.Now.Second; // get the current minute
                 //int time_sec = DateTime.Now.Millisecond;
                 //  int time_sec = DateTime.Now.Microsecond; // not working
                 // int time_min = DateTime.Now.Minute; // get the current minute
                 //int time_hour = DateTime.Now.Hour; // get the current hour
                 k = time_sec;
                 // for (i = 0; i < 60; i++)
                 //   {
                 chart1.Series["volt"].Points.AddXY(time, j);
                 chart1.Series["amp"].Points.AddXY(time, l);
                 // chart1(chart name),"volt"(series name),Points.AddXY(time_sec, arr[i])(add time_sec = x axis, arr[] = y axis) 
                 //  }
                 //  i = 0;
             }
             catch (Exception ex4)
             {
                 MessageBox.Show(ex4.Message, "Error");

             }

             datetime = DateTime.Now;
             time = datetime.Hour + ":" + datetime.Minute + ":" + datetime.Second;
             textBox3.Text = rxd_no; // same line
             textBox4.Text = time;
             textBox5.Text = data;
             textBox6.Text = in_data;


             this.Invoke(new EventHandler(excelsavedata_event));
             // data_tb.AppendText(time + "\t\t\t\t\t" + in_data+ "\n"); //new line
         }
    private void excelsavedata_event(object sender, EventArgs e)        
           
    {Excel._Application xlApp; // TO access excel file
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            

            xlApp = new Excel.Application(); // start new excel application
            xlApp.DisplayAlerts = false; // off display alerts
            xlWorkBook = xlApp.Workbooks.Open(pathfile + filename, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
            // open file in write mode
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            //get_Item(1) == sheet1
            xlWorkSheet.Cells[i, 4] = time; // cells[row,column]
            xlWorkSheet.Cells[i, 2] = in_data;
            xlWorkSheet.Cells[i, 3] = data;
             i++;

            xlWorkBook.SaveAs(pathfile + filename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue); //true== to save changes
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
            
            
            this.Invoke(new EventHandler(savedata_event));
        
        }
private void savedata_event(object sender, EventArgs e)
            
{       Excel._Application xlApp; // TO access excel file
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;


            xlApp = new Excel.Application(); // start new excel application
            xlApp.DisplayAlerts = false; // off display alerts
            xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\BADLIA\\Documents\\csharp.net-informations.xls", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
            // open file in write mode
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            ravi1 = xlWorkSheet.get_Range("B1", "B1").Value2.ToString();
           // MessageBox.Show(ravi1);
            int.TryParse(ravi1, out ravi);
            
            if (ravi == DateTime.Now.Day)
            {
                xlWorkSheet.Cells[1, 1] = i;
            }
            else
            {
                i = 1;
                xlWorkSheet.Cells[1, 1] = i;
                xlWorkSheet.Cells[1, 2] = DateTime.Now.Day;
            }
            xlWorkBook.SaveAs("C:\\Users\\BADLIA\\Documents\\csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue); //true== to save changes
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);


               }

 
     
    }
}

推荐答案

根据你的评论

引用：

我不想在excel文件中创建图表。我想显示应用程序中的实时图形。我只是将数据转储到excel文件中。

i am not trying to create chart in excel file .i want to show a real time graph in the application . i am just dumping the data in excel file.

然后你需要将数据转储到excel以外的东西 - 例如DataTable或List。



替换行

then you need to dump the data into something other than excel - a DataTable for example, or a List.

Replace the line

this.Invoke(new EventHandler(excelsavedata_event));

在 displaydata_event 中，将该存储空间存储在内存中。



当你有完成后接收所有数据然后你可以坚持使用Excel。



或者缓存你的 xlApp 在 excelsavedata_event 之外，例如在班级或可能作为单身人士班级（后者并不总是最好的主意）

in your displaydata_event with that storage in memory.

When you have finished receiving all of the data then you can persist it to Excel if you still want to.

Alternatively cache your xlApp outside of the excelsavedata_event e.g. at the class level or possibly as a singleton class (the latter is not always the best idea though)

这篇关于如何在不引入任何延迟的情况下访问excel文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！