0

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;
        } 
    }
MD. RAKIB HASAN
  • 2,587
  • 4
  • 17
  • 29
sahana i.s
  • 21
  • 4
  • Does this answer your question? [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) – Ken White Apr 19 '22 at 03:38

0 Answers0