Importing data in excel through c#

See more:

ASP.NET

MS-Excel

C#5.0

Hi ,
I have designed a bulk export in my project and it exports in Excel file when user exports the data. Actually i dont have Microsoft excel and I have designed the data in HTML table view and binded to .xls and exporter the file.. So now when the user tries to open the file... it will be opened in excel sheet with customized table view.....


Now my big problem is that now the same file will be modified by the user and the file will uploaded for Bulk Impporting data... So as it is not pure excel file I am unable to read the data from it.... please help me out... generally the below code I will use for reading excel data... please review code and help me out

public void ReadExcelData(string FilePath)
  {
      try
      {
          if (!string.IsNullOrEmpty(FilePath))
          {
              string strExtension = Path.GetExtension(FilePath);
              string strFileName = string.Empty;
              string FileOrginalName = FilePath.Split('|')[0];
              string action = string.Empty;
              string connectionString = string.Empty;
              OleDbCommand ExcelCommand;
              OleDbDataAdapter ExcelAdapter;
              DataTable dtSuspendUsers = new DataTable();
              DataTable dtRawData = new DataTable();
              strFileName = Path.GetFileNameWithoutExtension(FilePath);
              string strSourcePath = Server.MapPath("../ShippingDataUploadFiles/" + strFileName);
              if (strExtension == ".xls" || strExtension == ".xlsx")
              {

                  lblErrorMessage.Text = "";
                  if (strExtension == ".xls")
                  {
                      connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strSourcePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                  }
                  else if (strExtension == ".xlsx")
                  {
                      connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strSourcePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; ;
                  }
                  OleDbConnection con = new OleDbConnection(connectionString);
                  OleDbCommand cmd = new OleDbCommand();
                  con.Open();
                  //Get the list of sheet available in excel sheet
                  DataTable dtExcelDetails = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                  //Get first sheet name
                  string getExcelSheetName = string.Empty;

                  //getExcelSheetName = foundRows["Table_Name"].ToString();

                  if (true)
                  {
                      //Select rows from second sheet in excel sheet and fill into dataset
                      ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + "AddressBook$" + @"]", con);
                      ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
                      ExcelAdapter.Fill(dtRawData);
                      con.Close();

                      if (dtRawData.Rows.Count > 0)
                      {
                          ViewState["dtRawData"] = dtRawData;
                          // FormatShippingAddress(dtRawData);
                      }


                      if (dtRawData.Rows.Count > 0)
                      {
                          string[] strAddTemplateColumns = { "ShippingAddressID", "LineUp_Address_Id", "LineUp" };
                          for (int i = 0; i < strAddTemplateColumns.Count(); i++)
                          {
                              if (dtRawData.Columns.Contains(strAddTemplateColumns[i]))
                              {
                                  hdnTemplateType.Value = "Add/DeleteExportTemplate";
                              }

                          }
                          if (string.IsNullOrEmpty(hdnTemplateType.Value))
                          {
                              hdnTemplateType.Value = "DataFeedTemplate";
                          }
                      }


                      string[] FileDetails = new string[10];
                      FileDetails[0] = strFileName;
                      if (Session["UserName"] != null)
                      {
                          FileDetails[1] = Session["UserName"].ToString();
                      }
                      else
                      {
                          FileDetails[1] = "Admin";
                      }
                      FileDetails[2] = DateTime.Now.ToString();
                      FileDetails[3] = dtRawData.Rows.Count.ToString();
                      ShowUploadedDetails(FileDetails);
                  }
              }
              else
              {
                  lblErrorMessage.Text = "Please upload only .xls or .xlsx file format.";
              }
          }
      }
      catch (Exception ex)
      {

          throw;
      }
  }