Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
So I have an Program in which I load Data from an Excel File. The User can write the cell he want to edit, for example: "B3" is wanted to edit.

With this Information I want to take Data from another Column for example C2 and with the Data From C2 I want to give it into an SQL String, get all the Data and then I want to place the SQL Data in the B3 Cell the User Choose before.

So how to edit an Specific Field from User Input.

I get the Excel Data now with this Code(I use Interop.Excel):

What I have tried:

public async void editExcelFile()
   {
       if(!string.IsNullOrEmpty(emptyCell) && !string.IsNullOrEmpty(toFirstColumn) && !string.IsNullOrEmpty(fromFirstColumn))
       {
           Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
           DataSet ds = new DataSet();
           Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(path);
           foreach (Microsoft.Office.Interop.Excel.Worksheet ws in wb.Worksheets)
           {
               System.Data.DataTable td = new System.Data.DataTable();
               td = await Task.Run(() => formofDataTable(ws));
               ds.Tables.Add(td);//This will give the DataTable from Excel file in Dataset
           }




           wb.Close();


       }


   }

   public System.Data.DataTable formofDataTable(Microsoft.Office.Interop.Excel.Worksheet ws)
   {
       System.Data.DataTable dt = new System.Data.DataTable();
       string worksheetName = ws.Name;
       dt.TableName = worksheetName;
       Microsoft.Office.Interop.Excel.Range xlRange = ws.UsedRange;
       object[,] valueArray = (object[,])xlRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);
       for (int k = 1; k <= valueArray.GetLength(1); k++)
       {
           dt.Columns.Add((string)valueArray[1, k]);  //add columns to the data table.
       }
       object[] singleDValue = new object[valueArray.GetLength(1)]; //value array first row contains column names. so loop starts from 2 instead of 1
       for (int i = 2; i <= valueArray.GetLength(0); i++)
       {
           for (int j = 0; j < valueArray.GetLength(1); j++)
           {
               if (valueArray[i, j + 1] != null)
               {
                   singleDValue[j] = valueArray[i, j + 1].ToString();
               }
               else
               {
                   singleDValue[j] = valueArray[i, j + 1];
               }
           }
           dt.LoadDataRow(singleDValue, System.Data.LoadOption.PreserveChanges);
       }

       return dt;
   }
Posted
Updated 29-Sep-22 16:00pm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900