C# .NET - Display Excel,Csv file on Windows Form

Asked By sachin on 16-Nov-11 05:56 AM
Hi

my requirement is that i want to display Excel, CSV file on Windows form.. Plz see the image file for dummy purpose it is on attachment .....plz see the below image for same like

plz give me a sample code for this

its urgent
dipa ahuja replied to sachin on 16-Nov-11 06:01 AM
You can import the excel file and display it in the dataGridview

private void button2_Click(object sender, EventArgs e)
{
  string ExcelConstr = @"Provider=Microsoft.ACE.OLEDB.12.0";
  ExcelConstr += "Data Source=d:\book1.xls;Extended Properties=Excel 12.0";
 
  string SqlConstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;";
  SqlConstr += "Integrated Security=True;User Instance=True";
 
  using (OleDbConnection con = new OleDbConnection(ExcelConstr))
  {
    con.Open();
    OleDbCommand com = new OleDbCommand("Select * from [Sheet1$]", con);
    OleDbDataReader dr = com.ExecuteReader();
    using (SqlConnection sqlcon = new SqlConnection(SqlConstr))
    {
      sqlcon.Open();
      using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
      {
        bulkCopy.DestinationTableName = "Table1";
        bulkCopy.ColumnMappings.Add("srno", "srno");
        bulkCopy.ColumnMappings.Add("amount", "amount");
        bulkCopy.WriteToServer(dr);
      }
    }
    dr.Close();
    dr.Dispose();
  }
  MessageBox.Show("successfully imported!");
  //display the imported data in the datagrid
  SqlDataAdapter da = new SqlDataAdapter("select * from Table1", SqlConstr);
  DataTable dt = new DataTable();
 
  da.Fill(dt);
 
  dataGridViewX1.DataSource = dt;
}
 
Jitendra Faye replied to sachin on 16-Nov-11 06:03 AM
Use this code-

In Web Forms

http://www.gridview.net/wp-content/uploads/2011/02/ReadexcelfileinASP.NET_.png

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.OleDb;

 

public partial class _Default : System.Web.UI.Page 

{

    protected void Page_Load(object sender, EventArgs e)

    {

 

    }
Jitendra Faye replied to sachin on 16-Nov-11 06:06 AM
Use this complete code-

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.OleDb;

 

public partial class _Default : System.Web.UI.Page 

{

    protected void Page_Load(object sender, EventArgs e)

    {

 

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        string target = Server.MapPath("~/Upload");

        if (FileUpload1.HasFile)

        {

            FileUpload1.SaveAs(System.IO.Path.Combine(target, FileUpload1.FileName));

 

            string connectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", target + "\\" + FileUpload1.FileName);

            string query = String.Format("select * from [{0}$]", "Sheet1");

            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);

            DataSet dataSet = new DataSet();

            dataAdapter.Fill(dataSet);

            GridView1.DataSource = dataSet.Tables[0];

            GridView1.DataBind();

        }       

    }

}
Kirtan Patel replied to sachin on 17-Nov-11 09:22 AM
char[] splitArray1 = { ',''\n''\r' };
char[] splitArray2 = { ' ' };
Int64[] SearchList = new long[1];
string CSVDataSource;
string FileName;
string FileSize;
string LineText;
string TempFileName;
 
string[] DataResult1 = { """""""""" };// Use to populate the grid.        
string[] Titles = { "Date""Time""Thickness""Track Speed" };
 
//Create a dataset  
DataSet dataset = new DataSet("My Dataset");
//Create a table
DataTable datatable = new DataTable("Temp.CSV");
 
public Form1()
{
  InitializeComponent();      
  TempFileName = @"C:\Temp.csv";
       
  lblLoading.Visible = false;
            
  CreateTable();
  dataset.Tables.Add(datatable);
}
 
private void btnOpenFile_Click(object sender, EventArgs e)
{
  dgvData.SelectAll();
  dgvData.ClearSelection();
  FileName = txtFileName.Text;
  CSVDataSource = FileName;
  if (File.Exists(TempFileName))
  {
    File.Delete(TempFileName);
  }
  StreamReader sr = new StreamReader(FileName);
  StreamWriter sw = new StreamWriter(TempFileName);
 
  string junk = sr.ReadLine();
   
  string FileData = sr.ReadToEnd();
  FileSize = FileData.Length.ToString("N");
  FileSize = FileSize.Substring(0, FileSize.IndexOf("."));
  
 
  sw.WriteLine(" Stream No.,Die No,Date,Time,Thickness,Status,Track");
  sw.WriteLine(LineText);
  sw.Write(FileData);
  sr.Close();
  sw.Close(); 
  ReadData();
  lblLoading.Visible = false;
  dgvData.Update();
  dgvData.Columns[4].HeaderText = "Track speed";
  if (File.Exists(TempFileName))
  {
    File.Delete(TempFileName);
  }
}
 
private void CreateTable()
{
  for (int i = 0; i < 4; i++)
  {
    datatable.Columns.Add(Titles[i]);
  }
}
 
private void ReadData()
{
  string tempPath = "C:";
  string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + tempPath + @"\;Extensions=asc,csv,tab,txt";
  OdbcConnection conn = new OdbcConnection(strConn);
  OdbcDataAdapter da = new OdbcDataAdapter("Select Date,Time,Thickness,Status,Track from temp.csv", conn);
  DataTable dt = new DataTable();
  da.Fill(dt);
  dgvData.DataSource = dt;
  dgvData.Columns[1].DefaultCellStyle.Format = "T";
  foreach (DataGridViewColumn col in dgvData.Columns)
  {
    col.SortMode = DataGridViewColumnSortMode.NotSortable;
  }
}