By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,899 Members | 1,190 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,899 IT Pros & Developers. It's quick & easy.

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

P: 8
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:
Expand|Select|Wrap|Line Numbers
  1. private void browse_Click(object sender, EventArgs e)
  2.         {
  3.             OpenFileDialog fdlg = new OpenFileDialog();
  4.             fdlg.Filter = "All Files(*.*)|*.*";
  5.             if (fdlg.ShowDialog() == DialogResult.OK)
  6.             {
  7.  
  8.  
  9.                 textBox1.Text = fdlg.FileName;
  10.                 File.ReadAllText(textBox1.Text);
  11.  
  12.             }
  13.         }
  14.  
  15.         private void button1_Click(object sender, EventArgs e)
  16.         {
  17.              Excel.Application xlApp = new Excel.Application();
  18.             Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(textBox1.Text, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
  19.             Excel._Worksheet xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
  20.             Excel.Range xlRange = xlWorksheet.UsedRange;
  21.  
  22.             int rowCount = xlRange.Rows.Count;
  23.             int colCount = xlRange.Columns.Count;
  24.  
  25.  
  26.  
  27.             for (int i = 2; i <= rowCount; i++)
  28.             {
  29.  
  30.  
  31.                 for (int j = 1; j <= colCount; j++)
  32.                 {
  33.  
  34.                     if (j == 1)
  35.                         machId = xlRange.Cells[i, j].Value.ToString();
  36.  
  37.  
  38.                     //upload1 = xlRange.Cells[i, j].Value.ToString();
  39.                     if (j == 2)
  40.                         custId = xlRange.Cells[i, j].Value.ToString();
  41.  
  42.  
  43.                     if (j == 3)
  44.                         name = xlRange.Cells[i, j].Value.ToString();
  45.  
  46.                     if (j == 4)
  47.                         totalBal = xlRange.Cells[i, j].Value.ToString();
  48.  
  49.                     if (j == 5)
  50.                         paid = xlRange.Cells[i, j].Value.ToString();
  51.  
  52.                     if (j == 6)
  53.                         lastPaid = xlRange.Cells[i, j].Value.ToString("dd/MM/yyyy");
  54.                     //MessageBox.Show("lastpaid:"+lastPaid);
  55.  
  56.                     if (j == 7)
  57.                         due = xlRange.Cells[i, j].Value.ToString();
  58.  
  59.  
  60.                     final = "$ ," + machId + ",0,1,50," + custId + "," + name + "," + totalBal + "," + paid + "," + lastPaid + "," + due + " # ";
  61.  
  62.  
  63.                 }
  64.  
  65.                 if (serialPort1.IsOpen)
  66.                 {
  67.                     if (machId.Length <= 5 && custId.Length <= 10 && name.Length <= 15 && totalBal.Length <= 7 && paid.Length <= 7 && lastPaid.Length <= 14 && due.Length <= 7)
  68.                         serialPort1.Write(final);
  69.  
  70.  
  71.                 } serialPort1.DataReceived += new SerialDataReceivedEventHandler(serialPort1_DataReceived);
  72.  
  73.             }}
  74.         }
  75.     }
  76.  
and another application is:
Expand|Select|Wrap|Line Numbers
  1. private void Download_Click(object sender, EventArgs e)
  2.         {
  3.  
  4.             try
  5.             {
  6.                 Excel.Application xlApp = default(Excel.Application);
  7.                 Excel.Workbook xlWorkBook = default(Excel.Workbook);
  8.                 Excel.Worksheet xlWorkSheet = default(Excel.Worksheet);
  9.  
  10.                 object misValue = System.Reflection.Missing.Value;
  11.                 xlApp = new Excel.Application();
  12.                 xlWorkBook = xlApp.Workbooks.Add(misValue);
  13.                 xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
  14.                 xlWorkSheet.Cells[1, 1] = "Machine ID";
  15.                 xlWorkSheet.Cells[1, 2] = "Customer ID";
  16.                 xlWorkSheet.Cells[1, 3] = "Name";
  17.                 xlWorkSheet.Cells[1, 4] = "Total Balance";
  18.                 xlWorkSheet.Cells[1, 5] = "Paid Amount";
  19.                 xlWorkSheet.Cells[1, 6] = "Last Paid Date";
  20.                 xlWorkSheet.Cells[1, 7] = "Due Amount";
  21.  
  22.  
  23.  
  24.                 xlWorkSheet.Cells[1, 1].Interior.ColorIndex = 39;
  25.                 xlWorkSheet.Cells[1, 2].Interior.ColorIndex = 39;
  26.                 xlWorkSheet.Cells[1, 3].Interior.ColorIndex = 39;
  27.                 xlWorkSheet.Cells[1, 4].Interior.ColorIndex = 39;
  28.                 xlWorkSheet.Cells[1, 5].Interior.ColorIndex = 39;
  29.                 xlWorkSheet.Cells[1, 6].Interior.ColorIndex = 39;
  30.                 xlWorkSheet.Cells[1, 7].Interior.ColorIndex = 39;
  31.  
  32.  
  33.  
  34.                 serialPort1.DataReceived += new SerialDataReceivedEventHandler(serialPort1_DataReceived);
  35.  
  36.                  string[] lines = Regex.Split(s, "[$#,]");
  37.              //string[] lines = s.Split(',');
  38.  
  39.  
  40.                 //MessageBox.Show("split Value:" + line);
  41.                  String line;
  42.  
  43.  
  44.                 for (int p =2; p < lines.Length; p++)
  45.                 {
  46.                    for (int q = 1; q <= 7; q++)
  47.                     {
  48.                          line=lines[q];
  49.                      // xlWorkSheet.Cells[p, q] = lines;
  50.                        if(line!="")
  51.  
  52.                            ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[p, q]).Value2 = lines[p];
  53.  
  54.  
  55.  
  56.  
  57.  
  58.                   }
  59.  
  60.                 }
  61.  
  62.  
  63.  
  64.  
  65.  
  66.  
  67.                 xlWorkBook.SaveAs(fName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
  68.                 xlWorkBook.Close(true, misValue, misValue);
  69.                 xlApp.Quit();
  70.                 releaseObject(xlWorkSheet);
  71.                 releaseObject(xlWorkBook);
  72.                 releaseObject(xlApp);
  73.             }
  74.             catch (Exception p)
  75.             {
  76.                 MessageBox.Show(p.StackTrace);
  77.             }
  78.  
  79.             finally
  80.             {
  81.                 if (xlApp != null)
  82.                     releaseObject(xlApp);
  83.                 if (xlWorkBook != null)
  84.                     releaseObject(xlWorkBook);
  85.                 if (xlWorkSheet != null)
  86.                     releaseObject(xlWorkSheet);
  87.             }
  88.             if (System.IO.File.Exists(fName))
  89.             {
  90.                 if (MessageBox.Show("Would you like to open the excel file?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
  91.                 {
  92.                     try
  93.                     {
  94.                         System.Diagnostics.Process.Start(fName);
  95.                     }
  96.                     catch (Exception ex)
  97.                     {
  98.                         MessageBox.Show("Error opening the excel file." + Environment.NewLine +
  99.                           ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
  100.                     }
  101.                 }
  102.             }
  103.  
  104.         }   
  105.  
  106.  
  107.         private void releaseObject(object obj)
  108.         {
  109.             try
  110.             {
  111.                 System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
  112.                 obj = null;
  113.             }
  114.             catch (Exception ex)
  115.             {
  116.                 obj = null;
  117.                 MessageBox.Show("Unable to release the Object " + ex.ToString());
  118.             }
  119.             finally
  120.             {
  121.                 GC.Collect();
  122.             }
  123.         }
  124.  
  125.         private void serialPort1_DataReceived(object sender, System.IO.Ports.SerialDataReceivedEventArgs e)
  126.         {
  127.             try
  128.             {
  129.                 /*  for (int p = 0; p <2; p++)
  130.                   {
  131.                       inputdata = serialPort1.ReadExisting();
  132.                       MessageBox.Show("Read:" + inputdata);
  133.  
  134.                   }*/
  135.                 if (inputdata == "$M121,0#")
  136.  
  137.                     MessageBox.Show("value is:" + inputdata);
  138.  
  139.                 else
  140.                 {
  141.                     if (!serialPort1.IsOpen)
  142.                         return;
  143.  
  144.                     Thread.Sleep(50);
  145.                     byte[] buffer = new byte[serialPort1.BytesToRead];
  146.                     serialPort1.Read(buffer, 0, buffer.Length);
  147.  
  148.                     s = System.Text.ASCIIEncoding.ASCII.GetString(buffer);
  149.                    // MessageBox.Show(s);
  150.  
  151.  
  152.                 }
  153.             }
  154.             catch (Exception ex)
  155.             {
  156.                 MessageBox.Show(ex.Message);
  157.             }
  158.  
  159.         } 
Dec 17 '13 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.