Using Microsoft.Office.Interop.Excel Reading Excel Data by Rows and Columns
#region getExcelDataUsing Interop
private DataTable getexceldata()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str;
int rCnt;
int cCnt;
int rw = 0;
int cl = 0;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open("EXCELPATHMENTIONHERE", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
rw = range.Rows.Count;
cl = range.Columns.Count;
DataTable dt = new DataTable();
dt.Columns.Add("Column1");
dt.Columns.Add("Column2");
dt.Columns.Add("Column3");
dt.Columns.Add("Column4");
dt.Columns.Add("Column5");
dt.Columns.Add("Column6");
dt.Columns.Add("Column7");
dt.Columns.Add("Column8");
dt.Columns.Add("Column9");
dt.Columns.Add("Column10");
for (int i = 0; i < cl; i++)
{
dt.Rows.Add("", "", "", "", "", "", "", "", "", "");
}
for (rCnt = 2; rCnt <= rw; rCnt++)
{
for (cCnt = 1; cCnt < 10; cCnt++)
{
str = Convert.ToString((range.Cells[rCnt, cCnt] as Excel.Range).Value2);
dt.Rows[rCnt - 2][cCnt - 1] = str;
}
}
DataTable dd = dt;
xlWorkBook.Close(true, null, null);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
return dt;
}
#endregion
#region testOLEDBConnImportDataFromExcel
private void ImportDataFromExcel()
{
try
{
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFilePath.Text.ToString() + ";Extended Properties=Excel 8.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
MyConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
#endregion
#region testExportToExcel(DataTable dt)
public void ExportToExcel(DataTable dt)
{
//open file
StreamWriter wr = new StreamWriter(@"D\TestManualUpload.xls");
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
}
wr.WriteLine();
//write rows to excel file
for (int i = 0; i < (dt.Rows.Count); i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Rows[i][j] != null)
{
wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
}
else
{
wr.Write("\t");
}
}
//go to next line
wr.WriteLine();
}
//close file
wr.Close();
MessageBox.Show("File Saved Successfully");
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
private void btnBrowse_Click(object sender, EventArgs e)
{
lblmsg.Visible = false;
lblmsg.ForeColor = System.Drawing.Color.Green;
OpenFileDialog file = new OpenFileDialog();
if (file.ShowDialog() == DialogResult.OK)
{
txtFilePath.Text = file.FileName;
}
}
public void ImportDataFromExcel(string excelFilePath)
{
lblmsg.Visible = true;
lblmsg.Text = "Importing Data from Excel.... Please Wait...";
DataTable dtExcelData = new DataTable();
//declare variables - edit these based on your particular situation
string ssqltable = "MedicalEligUploadPkg";
// make sure your sheet name is correct, here sheet name is sheet1,
// so you can change your sheet name if have different
string myexceldataquery = "select * from [Sheet1$]";
try
{
//create our connection strings Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 8.0;HDR=YES'
string sexcelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties = Excel 12.0 Xml;HDR=YES;";
string ssqlconnectionstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
//execute a query to erase any previous data from our destination table
string sclearsql = "delete from " + ssqltable;
SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
//series of commands to bulk copy data from the excel file into our sql table
string conString = string.Empty;
string extension = Path.GetExtension(txtFilePath.Text);
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.MedicalEligUploadPkg";
//[OPTIONAL]: Map the Excel columns with that of the database table
/* sqlBulkCopy.ColumnMappings.Add("Subscriber ID", "Subscriber ID");
sqlBulkCopy.ColumnMappings.Add("Birth Date", "Birth Date");
sqlBulkCopy.ColumnMappings.Add("Issue Date", "Issue Date");
sqlBulkCopy.ColumnMappings.Add("Service Date", "Service Date");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Primary Aid Code", "Primary Aid Code");
sqlBulkCopy.ColumnMappings.Add("First Special Aid Code", "First Special Aid Code");
sqlBulkCopy.ColumnMappings.Add("Subscriber County", "Subscriber County");
sqlBulkCopy.ColumnMappings.Add("Eligibility Message", "Eligibility Message");
sqlBulkCopy.ColumnMappings.Add("Eligibility", "Eligibility");
sqlBulkCopy.ColumnMappings.Add("Comments", "Comments");*/
DataTable dtvalues = getexceldata();
sqlBulkCopy.ColumnMappings.Add(0, 0);
sqlBulkCopy.ColumnMappings.Add(1, 1);
sqlBulkCopy.ColumnMappings.Add(2, 2);
sqlBulkCopy.ColumnMappings.Add(3, 3);
sqlBulkCopy.ColumnMappings.Add(4, 4);
sqlBulkCopy.ColumnMappings.Add(5, 5);
sqlBulkCopy.ColumnMappings.Add(6, 6);
sqlBulkCopy.ColumnMappings.Add(7, 7);
sqlBulkCopy.ColumnMappings.Add(8, 8);
sqlBulkCopy.ColumnMappings.Add(9, 9);
con.Open();
sqlBulkCopy.WriteToServer(dtvalues);
con.Close();
}
}
lblmsg.Text = "Eligibility Uploaded successfully.";
sclearsql = "delete from medicaleliguploadpkg where (([Subscriber ID] = '') or ([Subscriber ID] is null))";
sqlconn = new SqlConnection(ssqlconnectionstring);
sqlcmd = new SqlCommand(sclearsql, sqlconn);
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
sclearsql = "RunMedicalUploadPkg";
sqlconn = new SqlConnection(ssqlconnectionstring);
sqlcmd = new SqlCommand(sclearsql, sqlconn);
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
}
catch (Exception ex)
{
lblmsg.Text = "Upload Failed";
lblmsg.ForeColor = System.Drawing.Color.Red;
MessageBox.Show(ex.ToString());
}
}
