0

Hellllo everyone.
i want to know that how to open a excel file in c# and how to copy the excel file data to the clipboard.
thanx

5
Contributors
6
Replies
9
Views
8 Years
Discussion Span
Last Post by serkan sendur
0

Please do a search on the forum. There are more than plenty examples of opening spreadsheets - and how to get/use data within them

0
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+filename+";Extended Properties=Excel 8.0");
            con.Open();
            try
            {
                //Create Dataset and fill with imformation from the Excel Spreadsheet for easier reference
                DataSet myDataSet = new DataSet();
                OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM ["+listname+"$]" , con);
                myCommand.Fill(myDataSet);
                con.Close();
                richTextBox1.AppendText("\nDataSet Filled");

                //Travers through each row in the dataset
                foreach (DataRow myDataRow in myDataSet.Tables[0].Rows)
                {
                    //Stores info in Datarow into an array
                    Object[] cells = myDataRow.ItemArray;
                    //Traverse through each array and put into object cellContent as type Object
                    //Using Object as for some reason the Dataset reads some blank value which
                    //causes a hissy fit when trying to read. By using object I can convert to
                    //String at a later point.
                    foreach (object cellContent in cells)
                    {
                        //Convert object cellContect into String to read whilst replacing Line Breaks with a defined character
                        string cellText = cellContent.ToString();
                        cellText = cellText.Replace("\n", "|");
                        //Read the string and put into Array of characters chars
                        richTextBox1.AppendText("\n"+cellText);
                    }
                }
                //Thread.Sleep(15000);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                //Thread.Sleep(15000);
            }
            finally
            {
                con.Close();
            }

credit to the original user.....

Hope it helps u:cool:

0

i want to pass csv file as an argument to excel application and want to see it display the results. there will be a button "export to excel" written on it, then it will open excel file with the values that is supplied to it as comma seperated strings.

0

i found a work around :
i read the values from the listview control and create an excel document with it as follows :

Excel.Application app = new Excel.Application();
			app.Visible = true;
			Excel.Workbook wb = app.Workbooks.Add(1);
			Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
			int i = 1;
			int i2 = 1;
			foreach (ListViewItem lvi in listLogs.Items)
			{
				i = 1;
				foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
				{
					ws.Cells[i2, i] = lvs.Text;
					i++;
				}
				i2++;
			}

i forgot to say you need to add reference to Excel object libraries 11 under COM section.

0

if the user does not have excel installed then you can save the listview as csv file :

saveLogsDialog.Filter = "csv files (*.csv)|*.csv";
			saveLogsDialog.FileName = "logs";
			saveLogsDialog.Title = "Export to Excel";
			StringBuilder sb = new StringBuilder();
			foreach (ColumnHeader ch in listLogs.Columns)
			{
				sb.Append(ch.Text + ",");
			}
			sb.AppendLine();
			foreach (ListViewItem lvi in listLogs.Items)
			{
				foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
				{
					if (lvs.Text.Trim() == string.Empty)
						sb.Append(" ,");
					else
						sb.Append(lvs.Text + ",");
				}
				sb.AppendLine();
			}
			DialogResult dr = saveLogsDialog.ShowDialog();
			if (dr == DialogResult.OK)
			{
				StreamWriter sw = new StreamWriter(saveLogsDialog.FileName);
				sw.Write(sb.ToString());
				sw.Close();
			}
This article has been dead for over six months. Start a new discussion instead.
Be sure to adhere to our posting rules.