get data through serialport and place in excel sheet c#,windows application

[rajeswari02]

hi

am developing application on c#,

in this am getting data from handheld system through serialport.

data is like this:
$machine id,responce code,no of rows,current row,customer name,name,total balance,paid,paid date,remaining,checksum#

data like this getting from serialport ,and i need to split that and store in excel sheet,only machine id,customerid,name,total balance,paid,paid date,remaining.

here am using two applications for testing,one application is:

private void browse_Click(object sender, EventArgs e)
        {
            OpenFileDialog fdlg = new OpenFileDialog();
            fdlg.Filter = "All Files(*.*)|*.*";
            if (fdlg.ShowDialog() == DialogResult.OK)
            {
 
 
                textBox1.Text = fdlg.FileName;
                File.ReadAllText(textBox1.Text);
 
            }
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
             Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(textBox1.Text, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Excel._Worksheet xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;
 
            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;
 
 
 
            for (int i = 2; i <= rowCount; i++)
            {
 
 
                for (int j = 1; j <= colCount; j++)
                {
 
                    if (j == 1)
                        machId = xlRange.Cells[i, j].Value.ToString();
 
 
                    //upload1 = xlRange.Cells[i, j].Value.ToString();
                    if (j == 2)
                        custId = xlRange.Cells[i, j].Value.ToString();
 
 
                    if (j == 3)
                        name = xlRange.Cells[i, j].Value.ToString();
 
                    if (j == 4)
                        totalBal = xlRange.Cells[i, j].Value.ToString();
 
                    if (j == 5)
                        paid = xlRange.Cells[i, j].Value.ToString();
 
                    if (j == 6)
                        lastPaid = xlRange.Cells[i, j].Value.ToString("dd/MM/yyyy");
                    //MessageBox.Show("lastpaid:"+lastPaid);
 
                    if (j == 7)
                        due = xlRange.Cells[i, j].Value.ToString();
 
 
                    final = "$ ," + machId + ",0,1,50," + custId + "," + name + "," + totalBal + "," + paid + "," + lastPaid + "," + due + " # ";
 
 
                }
 
                if (serialPort1.IsOpen)
                {
                    if (machId.Length <= 5 && custId.Length <= 10 && name.Length <= 15 && totalBal.Length <= 7 && paid.Length <= 7 && lastPaid.Length <= 14 && due.Length <= 7)
                        serialPort1.Write(final);
 
 
                } serialPort1.DataReceived += new SerialDataReceivedEventHandler(serialPort1_DataReceived);
 
            }}
        }
    }
 

and another application is:

private void Download_Click(object sender, EventArgs e)
        {
 
            try
            {
                Excel.Application xlApp = default(Excel.Application);
                Excel.Workbook xlWorkBook = default(Excel.Workbook);
                Excel.Worksheet xlWorkSheet = default(Excel.Worksheet);
 
                object misValue = System.Reflection.Missing.Value;
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                xlWorkSheet.Cells[1, 1] = "Machine ID";
                xlWorkSheet.Cells[1, 2] = "Customer ID";
                xlWorkSheet.Cells[1, 3] = "Name";
                xlWorkSheet.Cells[1, 4] = "Total Balance";
                xlWorkSheet.Cells[1, 5] = "Paid Amount";
                xlWorkSheet.Cells[1, 6] = "Last Paid Date";
                xlWorkSheet.Cells[1, 7] = "Due Amount";
 
 
 
                xlWorkSheet.Cells[1, 1].Interior.ColorIndex = 39;
                xlWorkSheet.Cells[1, 2].Interior.ColorIndex = 39;
                xlWorkSheet.Cells[1, 3].Interior.ColorIndex = 39;
                xlWorkSheet.Cells[1, 4].Interior.ColorIndex = 39;
                xlWorkSheet.Cells[1, 5].Interior.ColorIndex = 39;
                xlWorkSheet.Cells[1, 6].Interior.ColorIndex = 39;
                xlWorkSheet.Cells[1, 7].Interior.ColorIndex = 39;
 
 
 
                serialPort1.DataReceived += new SerialDataReceivedEventHandler(serialPort1_DataReceived);
 
                 string[] lines = Regex.Split(s, "[$#,]");
             //string[] lines = s.Split(',');
 
 
                //MessageBox.Show("split Value:" + line);
                 String line;
 
 
                for (int p =2; p < lines.Length; p++)
                {
                   for (int q = 1; q <= 7; q++)
                    {
                         line=lines[q];
                     // xlWorkSheet.Cells[p, q] = lines;
                       if(line!="")
 
                           ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[p, q]).Value2 = lines[p];
 
 
 
 
 
                  }
 
                }
 
 
 
 
 
 
                xlWorkBook.SaveAs(fName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
            }
            catch (Exception p)
            {
                MessageBox.Show(p.StackTrace);
            }
 
            finally
            {
                if (xlApp != null)
                    releaseObject(xlApp);
                if (xlWorkBook != null)
                    releaseObject(xlWorkBook);
                if (xlWorkSheet != null)
                    releaseObject(xlWorkSheet);
            }
            if (System.IO.File.Exists(fName))
            {
                if (MessageBox.Show("Would you like to open the excel file?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    try
                    {
                        System.Diagnostics.Process.Start(fName);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error opening the excel file." + Environment.NewLine +
                          ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
 
        }   
 
 
        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();
            }
        }
 
        private void serialPort1_DataReceived(object sender, System.IO.Ports.SerialDataReceivedEventArgs e)
        {
            try
            {
                /*  for (int p = 0; p <2; p++)
                  {
                      inputdata = serialPort1.ReadExisting();
                      MessageBox.Show("Read:" + inputdata);
 
                  }*/
                if (inputdata == "$M121,0#")
 
                    MessageBox.Show("value is:" + inputdata);
 
                else
                {
                    if (!serialPort1.IsOpen)
                        return;
 
                    Thread.Sleep(50);
                    byte[] buffer = new byte[serialPort1.BytesToRead];
                    serialPort1.Read(buffer, 0, buffer.Length);
 
                    s = System.Text.ASCIIEncoding.ASCII.GetString(buffer);
                   // MessageBox.Show(s);
 
 
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
 
        }