Good day,
I am having trouble with the excel file that i was uploaded.. after i uploaded the excel file i want to validate each header and each datatype of it. just to make sure that my dbase has integrity.let say i have 10 columns.
Is there a way on how could i validate each header? and its data type?
here is my code for uploading
protected void btnUpload_Click(object sender, EventArgs e)
{
string conn = "";
if (FileUpload1.HasFile)
{
string fname = Path.GetFileName(FileUpload1.PostedFile.FileName);
string fExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string fLocation = Server.MapPath("/ExcelFileUpload/" + fname);
if (fExtension == ".xls")
{
conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fExtension == ".xlsx")
{
conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
else if (fExtension != ".xlsx" || fExtension != "xls" || fExtension == String.Empty)
{
return;
}
FileUpload1.SaveAs(fLocation);
OleDbConnection con = new OleDbConnection(conn);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtCOO9Report = new DataTable();
con.Open();
DataTable dtExcelsheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelsheetName.Rows[0]["TABLE_NAME"].ToString();
cmd.CommandText = "Select * from [" + getExcelSheetName + "]";
dAdapter.SelectCommand = cmd;
DataSet ds = new DataSet("dsExcelC009ReportRecords");
dAdapter.Fill(ds, "dtExcelC009ReportRecords");
foreach (DataColumn dc in ds.Tables["dtExcelC009ReportRecords"].Columns)
{
dc.ColumnMapping = MappingType.Attribute;
}
con.Close();
_c009ReportEntities.xmlC009Report = ds.GetXml();
try
{
if (_c009ReportBll.InsertXMLC009Report(_c009ReportEntities) != 0)
{
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
catch (Exception err)
{
lblMsg.Visible = true;
lblMsg.Text = "Error in " + err.ToString();
}
//else
//{
// return;
//}
}
}
my codes seems to work on my machine without error. all i want is to validate the uplaoded data of the user..
