How can i split excel sheet values and store it in new excel sheet?

Question

val1||val3|val4

I want to split the values separate and create new excelsheet store the splitted value.

In new Excel sheet i want to get values like.

Column1 Column2 Column3 Column4

value1 value2 value3 value4

val val3 val4

How can i get the Values like this.anyone give me solution for this.

i will get the data from excel and store the values in data table. For this i was using the following code.

public void LoadExcelData()
      {
         using (SPSite site = this.WebApplication.Sites[0])
         {
             using (SPWeb currWeb = site.OpenWeb())
             {
                 string fullpath = "";
                 SPQuery query = new SPQuery();
                 SPList currList = currWeb.Lists.TryGetList("JDFieldMapping");
                 SPListItemCollection itemColl = currList.GetItems(query);
                 query.ViewFields = string.Concat(
                "<FieldRef Name='FilePath' />");
                 SPListItemCollection oItemCol = currList.GetItems(query);
                 string directorypath = "";


                 foreach (SPListItem oItem in oItemCol)
                 {
                     directorypath = Convert.ToString(oItem["FilePath"]);
                     break;
                 }

                 var directory = new DirectoryInfo(directorypath);
                 var FilePath = (from f in directory.GetFiles()
                               orderby f.LastWriteTime descending
                               select f).First();

                 string Filename = FilePath.ToString();

                 fullpath = directorypath + Filename;
                 DataTable data = GetFileExtension(fullpath);


             }
         }
      }


      public DataTable GetFileExtension(string fullpath)
      {
          var FileFullpath = fullpath;
          string fileExtension = Path.GetExtension(fullpath).ToUpper();
          string connectionString = "";

          if (fileExtension == ".XLS")
          {
              connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fullpath + "'; Extended Properties='Excel 8.0;HDR=YES;'";
          }
          else if (fileExtension == ".XLSX")
          {
              connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fullpath + "';Extended Properties='Excel 12.0 Xml;HDR=YES;'";
          }

          if (!(string.IsNullOrEmpty(connectionString)))
          {
              string[] sheetNames = GetExcelSheetNames(connectionString);
              if ((sheetNames != null) && (sheetNames.Length > 0))
              {

                  OleDbConnection con = new OleDbConnection(connectionString);
              //    string status = "SELECT * FROM [" + sheetNames[0] + "]" + "WHERE [CAN Status] = 90;";
                  string status = "SELECT * FROM [" + sheetNames[0] + "]";
                  OleDbDataAdapter da = new OleDbDataAdapter(status, con);
                  dt = new DataTable();
                  da.Fill(dt);
                  con.Close();
              }
          }
          return dt;

      }



    private string[] GetExcelSheetNames(string strConnection)
      {
          var connectionString = strConnection;
          String[] excelSheets;
          using (var connection = new OleDbConnection(connectionString))
          {
              connection.Open();
              var dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
              if (dt == null)
              {
                  return null;
              }
              excelSheets = new String[dt.Rows.Count];
              int i = 0;

              // Add the sheet name to the string array.
              foreach (DataRow row in dt.Rows)
              {
                  excelSheets[i] = row["TABLE_NAME"].ToString();
                  i++;
              }
          }
          return excelSheets;
      }

What i was add a code that is working fine but in Excel sheet all values separated b y " | ". So i want to split those values and store it in either data table or new excel sheet how can i do this? — elina
Hi C4u i want split the data and store it in new excel sheet can you share with me any sample code. — elina
Look at String.Split(). Here's some examples: dotnetperls.com/split — sr28

Hardik Hardik · Accepted Answer · 2016-08-24T11:02:10

You can use below mentioned code to get data from excel with its columns in DATATABLE.

System.Data.OleDb.OleDbConnection MyConnection ;
System.Data.DataSet DtSet ;
System.Data.OleDb.OleDbDataAdapter MyCommand ;
MyConnection = new system.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
dataGridView1.DataSource = DtSet.Tables[0];
MyConnection.Close();

Note:- You need to change excel workbook path as well as column names according to your excel spreadsheet

Check below link

Read and Import Excel File into DataSet

How can i split excel sheet values and store it in new excel sheet?

1 Answers