Code to read the data from excel :
string
columnHeader = "";
OleDbDataReader drexcel =
cmdExcel.ExecuteReader();
strInsQuery =
"Insert into " + drptable.SelectedValue +
" (" + "Data_Month,Data_Year,";
for
(intFldCnt = 0; intFldCnt <= (drexcel.FieldCount - 1); intFldCnt++)
{
columnHeader =
drexcel.GetName(intFldCnt);
strInsQuery =
strInsQuery + "[" +
Strings.Replace(columnHeader, "#",
"") + "]"
+ ",";
}
strInsQuery =
Strings.Left(strInsQuery, Strings.Len(strInsQuery) - 1);
strInsQuery = strInsQuery +
") values ('" + File_month +
"'" + ","
+ File_Year + ",";
while
((drexcel.Read)) {
//Assign values to access command
parameters
for (intFldCnt = 0; intFldCnt <= (drexcel.FieldCount
- 1); intFldCnt++)
{
if (Information.IsDBNull(drexcel(intFldCnt))) {
strFldValues = strFldValues + "null,";
}
else {
strFldValues = strFldValues + "'" +
Strings.Replace(Strings.Trim(drexcel(intFldCnt)),
"'", "''") +
"',";
}
}
strFldValues =
Strings.Left(strFldValues, Strings.Len(strFldValues) - 1);
strInsFinQuery =
strInsQuery + strFldValues + ")";
cmdAccess.CommandText
= strInsFinQuery;
flag =
cmdAccess.ExecuteNonQuery();
if (flag != 0) {
updatedRec =
updatedRec + 1;
UploadSucessFlag = "TRUE";
}
else {
UploadSucessFlag = "FALSE";
}
recCount = recCount +
1;
strInsFinQuery =
"";
strFldValues =
"";
}
Code to read a particular column from the excel sheet :
connExcel = strExcel +
strfullpath + ";";
//creating
connection objects
_connection =
new OleDbConnection(connExcel);
connAccess =
new OleDbConnection(strConn);
//creating
command object for excel
OleDbCommand cmdExcel =
new OleDbCommand();
cmdExcel = _connection.CreateCommand();
cmdExcel.CommandType =
CommandType.Text;
cmdExcel.CommandText =
"SELECT * from [Sheet1$]";
//Command
object for Access
OleDbCommand cmdAccess =
new OleDbCommand();
cmdAccess =
connAccess.CreateCommand();
cmdAccess.CommandType =
CommandType.Text;
cmdAccess.CommandText =
"Select * from UtilizationData";
//Dim drAccess
As OleDbDataReader = cmdAccess.ExecuteReader()
//Open
connections
_connection.Open();
connAccess.Open();
DataSet dsExcel =
new DataSet();
daExcel =
new OleDbDataAdapter(cmdExcel);
daExcel.Fill(dsExcel,
"ExcelTable");
//logic to get
file date form the excel file
int
colcount = dsExcel.Tables["ExcelTable"].Rows[0].ItemArray.Count;
int
rowCount = dsExcel.Tables["ExcelTable"].Rows.Count;
System.DateTime File_Date =
default(System.DateTime);
for
(iRowCount = 0; iRowCount <= rowCount - 1; iRowCount++)
{
string DateRow = dsExcel.Tables["ExcelTable"].Columns[iRowCount].ToString();
if (Strings.UCase(DateRow).Contains("START
DATE")) {
//DateRowindex = iRowCount
File_Date =
Strings.Trim(dsExcel.Tables["ExcelTable"].Rows[0][iRowCount].ToString());
break;
// TODO: might not be correct. Was : Exit For
}
}