This article is about converting Excel data to a table. In this example I am using Ms SQL Server 2005 as the database. You can use any database by changing the connection string.
1. First of all we have an Excel file. In the Excel file the first row should contain the field names. If the Excel file contains blank rows in the top, then the field names will be Filed1, Field2,Field3…and so on. For example the following shows the Excel data. Save this file and then use it.
| Client Id | Client Name | Client Address | Amount |
| 101 | ABC Infotech | New York | 100000 |
| 102 | XYZ Systems | Manhattan | 200000 |
2. Sometimes your column name may contain spaces. So in this example I am converting the blank space to underscore (_). Because in the database we cannot have a field name with a blank space.
3. First load the Excel file by giving a file dialog box.
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
public void Browse_Click(object sender, EventArgs e)
{
Excel.Application ExApp;
AllSheets.Items.Clear();
FileTextBox.Text = "";
try
{
openFileDialog1.Filter = "Excel Files (*.xls)|*.xls|All Files(*.*)|*.*";
openFileDialog1.InitialDirectory = @"c:\";
openFileDialog1.FileName = "Select File";
openFileDialog1.Title = "Select excel file..";
openFileDialog1.ShowDialog();
ExcelFile = openFileDialog1.FileName; ;
FileTextBox.Text = ExcelFile;
ExApp = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook MyWorkbook = ExApp.Workbooks.Open(ExcelFile, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
TotalSheets = MyWorkbook.Worksheets.Count;
foreach (Excel.Worksheet wksh in MyWorkbook.Worksheets)
{
AllSheets.Items.Add(wksh.Name);
}
MyWorkbook.Close(false, false, false);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Note: Because you are using the Excel properties, first add the reference for Excel.
4. When you will click on the Browse button it will ask for filename. Select the file and then whatever the sheets in that workbook will be displayed in ComboBox. Select any sheet and click on save.
Public void Save_Click()
{
OleDbConnection con = null;
OleDbCommand com = null;
OleDbDataAdapter da = null;
DataSet ds = null;
string SheetName;
SheetName = comboBox1.SelectedItem.ToString();
string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFile + ";Extended Properties=Excel 8.0";
con = new OleDbConnection(constr);
con.Open();
string strr = "select * from [" + SheetName + "$]";
com = new OleDbCommand(strr, con);
da = new OleDbDataAdapter();
da.SelectCommand = com;
ds = new DataSet();
da.Fill(ds);
TotalCols = ds.Tables[0].Columns.Count;
TotalRows = ds.Tables[0].Rows.Count;
string query=null;
try
{
SqlConnection con = new SqlConnection("Data Source=MyDataServer;Database=CustomerData;Integrated
Security=true");
con.Open();
SqlCommand com = new SqlCommand();
com.Connection = con;
query = "create table Customer(" + ds.Tables[0].Columns[0].ToString().Replace(" ","_") + " varchar(200)";
int i;
for (i = 1; i < TotalCols; i++)
{
string ActualField = ds.Tables[0].Columns[i].ToString();
string FinalField = ActualField.Replace(" ", "_");
query += "," + FinalField + " varchar(200)";
}
query += ")";
com.CommandText = query;
com.ExecuteNonQuery();
int ins, co;
query = "";
for (ins = 0; ins < TotalRows; ins++)
{
query = "insert into Customer values('" + ds.Tables[0].Rows[ins][0];
for (co = 1; co < TotalCols; co++)
{
query += "','"+ds.Tables[0].Rows[ins][co].ToString();
}
query += "')";
com.CommandText = query;
com.ExecuteNonQuery();
}
MessageBox.Show("Record saved");
}
catch (Exception ex)
{
MessageBox.Show("error: "+query+ "--"+ex.Message);
}
}