i am trying to upload an excel and read it in server and insert the excel data to sql server using Libre office in .net But i am getting null reference error once upload the excel.
Can anyone please help me with this ?
Code :
protected void btnUpload_Click(object sender, EventArgs e)
{
lblError.Text = "";
lblSuccess.Text = "";
if (FileUpload1.HasFile)
{
lblError.Text = "file upload process";
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath = FolderPath + FileName;
lblError.Text = FilePath;
FileUpload1.SaveAs(Server.MapPath("~") + "/bulkupload/" + FileUpload1.FileName);
lblError.Text = "File saved";
bulkUpload(FilePath, Extension, rbHDR.SelectedItem.Text);
}
}
private void bulkUpload(string FilePath, string Extension, string isHDR)
{
Response.Write(" in bulk upload function ");
lblError.Text = "in bulk Upload function ";
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
Response.Write(SheetName);
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
Response.Write(" openinng excel file ");
cmdExcel.CommandText = "SELECT Model_SVC,VersionNumber ,Released From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
//get the countries for bulk upload
var countries = new List<String>();
countries = GetCountryData();
//create datatable similar to database table for bulk upload
DataTable tbl = new DataTable();
tbl.Columns.Add(new DataColumn("Model_SVC", typeof(string)));
tbl.Columns.Add(new DataColumn("Country", typeof(string)));
tbl.Columns.Add(new DataColumn("VersionNumber", typeof(string)));
tbl.Columns.Add(new DataColumn("Description", typeof(string)));
tbl.Columns.Add(new DataColumn("Released", typeof(DateTime)));
tbl.Columns.Add(new DataColumn("Created", typeof(DateTime)));
tbl.Columns.Add(new DataColumn("Updated", typeof(DateTime)));
tbl.Columns.Add(new DataColumn("W_Upd_User", typeof(string)));
try
{
for (int i = 0; i < dt.Rows.Count; i++)
{
var model = dt.Rows[i][0];
var VersionNumber = dt.Rows[i][1];
var released = dt.Rows[i][2];
var descriprion = VersionNumber;
var updated = DateTime.Today;
var created = DateTime.Today;
var user = Session["user"] as UserInfo;
if (string.IsNullOrEmpty(model.ToString()) || string.IsNullOrEmpty(VersionNumber.ToString()) || released == null)
{
lblError.Visible = true;
lblError.Text = "Please enter all the required fileds in the excel sheet";
}
else
{
foreach (var country in countries)
{
DataRow dr = tbl.NewRow();
dr["Model_SVC"] = model.ToString();
dr["Country"] = country.ToString();
dr["VersionNumber"] = VersionNumber.ToString();
dr["Description"] = VersionNumber.ToString();
dr["Released"] = released;
dr["Created"] = DateTime.Today.ToString("yyyy/MM/dd");
dr["Updated"] = DateTime.Today.ToString("yyyy/MM/dd");
dr["W_Upd_User"] = user.FirstName;
tbl.Rows.Add(dr);
}
}
}
var numberOfRows = tbl.Rows.Count;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Models"].ToString());
SqlBulkCopy objbulk = new SqlBulkCopy(con);
objbulk.DestinationTableName = "WModel";
objbulk.ColumnMappings.Add("Model_SVC", "Model_SVC");
objbulk.ColumnMappings.Add("Country", "Country");
objbulk.ColumnMappings.Add("VersionNumber", "VersionNumber");
objbulk.ColumnMappings.Add("Description", "Description");
objbulk.ColumnMappings.Add("Released", "Released");
objbulk.ColumnMappings.Add("Created", "Created");
objbulk.ColumnMappings.Add("Updated", "Updated");
objbulk.ColumnMappings.Add("W_Upd_User", "W_Upd_User");
con.Open();
try
{
objbulk.WriteToServer(tbl);
}
catch (SqlException exs)
{
lblError.Visible = true;
lblError.Text = exs.Message;
}
lblSuccess.Visible = true;
lblSuccess.Text = numberOfRows + " records inserted succesfully ";
con.Close();
}
catch (Exception ex)
{
lblError.Visible = true;
lblError.Text = ex.Message;
}
}