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,pa id date,remaining, checksum#
data like this getting from serialport ,and i need to split that and store in excel sheet,only machine id,customerid,n ame,total balance,paid,pa id date,remaining.
here am using two applications for testing,one application is:
and another application is:
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,pa id date,remaining, checksum#
data like this getting from serialport ,and i need to split that and store in excel sheet,only machine id,customerid,n ame,total balance,paid,pa id date,remaining.
here am using two applications for testing,one application is:
Code:
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);
}}
}
}
Code:
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);
}
}