我如何分割excel表值并将其存储在新的excel表中

我有一个Excel表，它在一个列中所有的值被分隔符(|)分开。Coumn1|column2|column3|column4(此列名位于excel表的第一行)value1 | value2 | value33 | value4

val1 | | val3 | val4

我想分割值分开，并创建新的excel表存储分割的值。

在新的Excel工作表中，我想获得的值如下。

Column1 Column2 Column3 Column4

value1 value2 value3 value4

我怎么能得到这样的值。谁能给我一个解决办法?

我将从excel中获取数据并将值存储在数据表中。为此，我使用了以下代码:

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;
      }

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();