POC on loading an Excel with multiple sheets to a DatagridView and saving the excel to a SQLserver DB Table
C# WinForm: POC on loading an Excel with multiple sheets to a DatagridView and saving the excel to a SQLserver DB Table
Requirement: To Load an Excel to a DataGridView and make the number of sheets available to a drop down list. Depending upon the selection of the item from the dropDown list, the excel sheet will be displayed in the DataGridView .Then Upload the excel to the database.
C# Code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace PracticeWindowsForm
{
public partial class editable_Grid_iew : Form
{
private string ExcelConnString_03 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties='Excel 8.0;HRD={1}'";
private string ExcelConnString_07 = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};Extended properties='Excel 8.0;HDR={1}'";
DataTable dta;
string constr;
public editable_Grid_iew()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
}
private void openFileDialog1_FileOk_1(object sender, CancelEventArgs e)
{
load_data("ALL");
}
public void load_data(String sheetname_)
{
List<string> sheetnames = new List<string>();
string filePath = openFileDialog1.FileName;
string extension = Path.GetExtension(filePath);
string header = radioButton1.Checked ? "YES" : "NO";
string sheetname = string.Empty; ;
if (sheetname_ == "ALL")
sheetname = string.Empty;
else
sheetname = sheetname_;
constr = string.Empty;
switch (extension)
{
case ".xls":
constr = string.Format(ExcelConnString_03, filePath, header);
break;
case ".xlsx":
constr = string.Format(ExcelConnString_07, filePath, header);
break;
}
using (OleDbConnection conn = new OleDbConnection(constr))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = conn;
conn.Open();
DataTable data_from_excel = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; i < data_from_excel.Rows.Count; i++)
{
sheetname = data_from_excel.Rows[i]["TABLE_NAME"].ToString();
sheetnames.Add(sheetname);
}
conn.Close();
}
}
using (OleDbConnection conn = new OleDbConnection(constr))
{
using (OleDbCommand cmd = new OleDbCommand())
{
using (OleDbDataAdapter adp = new OleDbDataAdapter())
{
DataTable dt = new DataTable();
dta = dt;
cmd.CommandText = "SELECT * FROM [" + sheetname + "]";
cmd.Connection = conn;
conn.Open();
adp.SelectCommand = cmd;
adp.Fill(dt);
conn.Close();
dataGridView1.DataSource = dt;
}
}
}
getdataSource(sheetnames);
}
public void getdataSource(List<string> lst_items)
{
foreach (string name in lst_items)
{
comboBox1.Items.Add(name);
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
string sheetname = this.comboBox1.GetItemText(this.comboBox1.SelectedItem.ToString());
using (OleDbConnection conn = new OleDbConnection(constr))
{
using (OleDbCommand cmd = new OleDbCommand())
{
using (OleDbDataAdapter adp = new OleDbDataAdapter())
{
DataTable dt = new DataTable();
cmd.CommandText = "SELECT * FROM [" + sheetname + "]";
adp.SelectCommand = cmd;
cmd.Connection = conn;
conn.Open();
adp.Fill(dt);
dataGridView1.DataSource = dt;
conn.Close();
}
}
}
}
private void button2_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
conn.Open();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (!row.IsNewRow)
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO STATE_CAR (Car_number,car_model,car_owner,Time,State) values (@c1,@c2,@c3,@c4,@c5)", conn))
{
cmd.Parameters.AddWithValue("@c1", row.Cells[0].Value);
cmd.Parameters.AddWithValue("@c2", row.Cells[1].Value);
cmd.Parameters.AddWithValue("@c3", row.Cells[2].Value);
cmd.Parameters.AddWithValue("@c4", row.Cells[3].Value);
cmd.Parameters.AddWithValue("@c5", row.Cells[4].Value);
cmd.ExecuteNonQuery();
}
}
}
}
}
}
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<connectionStrings>
<add name="DBConnection" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=H:\MvcApplication1\MvcApplication1\MvcApplication1\App_Data\Models_-20150625114416.mdf;Integrated Security=True;"/>
</connectionStrings>
</configuration>
Sql Server :
create table STATE_CAR
(Car_number varchar(20)
,car_model varchar(20)
,car_owner varchar(20)
,[Time] int
,[State] varchar(20))
Alter table STATE_CAR
Alter column [Time] varchar(20 )
select * from STATE_CAR
Winform Page:
namespace PracticeWindowsForm
{
partial class editable_Grid_iew
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// Clean up any resources being used.
/// </summary>
/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.openFileDialog1 = new System.Windows.Forms.OpenFileDialog();
this.button1 = new System.Windows.Forms.Button();
this.radioButton1 = new System.Windows.Forms.RadioButton();
this.radioButton2 = new System.Windows.Forms.RadioButton();
this.dataGridView1 = new System.Windows.Forms.DataGridView();
this.comboBox1 = new System.Windows.Forms.ComboBox();
this.button2 = new System.Windows.Forms.Button();
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
this.SuspendLayout();
//
// openFileDialog1
//
this.openFileDialog1.FileName = "openFileDialog1";
this.openFileDialog1.FileOk += new System.ComponentModel.CancelEventHandler(this.openFileDialog1_FileOk_1);
//
// button1
//
this.button1.Location = new System.Drawing.Point(57, 13);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(75, 23);
this.button1.TabIndex = 0;
this.button1.Text = "Select File";
this.button1.UseVisualStyleBackColor = true;
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// radioButton1
//
this.radioButton1.AutoSize = true;
this.radioButton1.Location = new System.Drawing.Point(250, 19);
this.radioButton1.Name = "radioButton1";
this.radioButton1.Size = new System.Drawing.Size(60, 17);
this.radioButton1.TabIndex = 1;
this.radioButton1.TabStop = true;
this.radioButton1.Text = "Header";
this.radioButton1.UseVisualStyleBackColor = true;
//
// radioButton2
//
this.radioButton2.AutoSize = true;
this.radioButton2.Location = new System.Drawing.Point(316, 19);
this.radioButton2.Name = "radioButton2";
this.radioButton2.Size = new System.Drawing.Size(77, 17);
this.radioButton2.TabIndex = 2;
this.radioButton2.TabStop = true;
this.radioButton2.Text = "No Header";
this.radioButton2.UseVisualStyleBackColor = true;
//
// dataGridView1
//
this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
this.dataGridView1.Location = new System.Drawing.Point(41, 42);
this.dataGridView1.Name = "dataGridView1";
this.dataGridView1.Size = new System.Drawing.Size(747, 381);
this.dataGridView1.TabIndex = 3;
//
// comboBox1
//
this.comboBox1.FormattingEnabled = true;
this.comboBox1.Location = new System.Drawing.Point(439, 14);
this.comboBox1.Name = "comboBox1";
this.comboBox1.Size = new System.Drawing.Size(121, 21);
this.comboBox1.TabIndex = 4;
this.comboBox1.SelectedIndexChanged += new System.EventHandler(this.comboBox1_SelectedIndexChanged);
//
// button2
//
this.button2.Location = new System.Drawing.Point(138, 12);
this.button2.Name = "button2";
this.button2.Size = new System.Drawing.Size(75, 23);
this.button2.TabIndex = 5;
this.button2.Text = "Save";
this.button2.UseVisualStyleBackColor = true;
this.button2.Click += new System.EventHandler(this.button2_Click);
//
// editable_Grid_iew
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(828, 498);
this.Controls.Add(this.button2);
this.Controls.Add(this.comboBox1);
this.Controls.Add(this.dataGridView1);
this.Controls.Add(this.radioButton2);
this.Controls.Add(this.radioButton1);
this.Controls.Add(this.button1);
this.Name = "editable_Grid_iew";
this.Text = "editable_Grid_iew";
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private System.Windows.Forms.OpenFileDialog openFileDialog1;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.RadioButton radioButton1;
private System.Windows.Forms.RadioButton radioButton2;
private System.Windows.Forms.DataGridView dataGridView1;
private System.Windows.Forms.ComboBox comboBox1;
private System.Windows.Forms.Button button2;
}
}
Comments
Post a Comment