none
Update cells Excel RRS feed

  • Question

  • Hello,

    I try to update one or more Excel cells based on their position and not on the column header (I know how to do it).

      MyConnection = new System.Data.OleDb.OleDbConnection (@ "Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + File + "; Extended Properties = Excel 12.0;");
    
    // My query
      Sql = @ "UPDATE [Directory $ D13: D14] SET F1 = 'toto1', F2 = 'toto2'";


    Whatever the query I run, I always get an error message. The latest:
    "No value given for one or more of the required parameters".

    When that is not it, is an error in the query.

    Would you have a track for me?

    Thank you in advance.


    MCITP EA Windows Server 2008 R2 MCSA Windows Server 2012 Apprenti développeur C# EF WPF

    Wednesday, February 1, 2017 3:45 PM

Answers

  • Hi Hageshii,

    I again try to check the code and I find that I used HDR=Yes instead of NO.

    so I changed it to No and its working without any error.

    another thing I find in your code that you are using 2 $ sign after sheet name. only 1 $ sign needed. so correct it.

    below is the updated code.

     private void button1_Click(object sender, EventArgs e)
            {
                String sConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\Users\v-padee\Desktop\ole.xlsx; Extended Properties ='Excel 8.0;HDR=No'";
                OleDbConnection objConn = new OleDbConnection(sConnectionString);
                objConn.Open();
                OleDbCommand objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A2:A2] SET F1=123456", objConn);
                objCmdSelect.ExecuteNonQuery();
                objConn.Close();
            }

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 7, 2017 6:18 AM
    Moderator
  • Hi,

    Finaly I have found a different solution :

    http://fczaja.blogspot.fr/2013/05/how-to-read-and-write-excel-cells-with.html
     
                public void UpdateCell(string sheetName, string cellCoordinates, object cellValue)
                {
                    using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(Fichier, true))
                    {
                        excelDoc.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
                        excelDoc.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
    
                        WorksheetPart worksheetPart = GetWorksheetPart(excelDoc, sheetName);
                        Cell cell = GetCell(worksheetPart, cellCoordinates);
                        cell.CellValue = new CellValue(cellValue.ToString());
                        worksheetPart.Worksheet.Save();
    
                    
                }
                }
    
                private WorksheetPart GetWorksheetPart(SpreadsheetDocument excelDoc, string sheetName)
                {
                    Sheet sheet = excelDoc.WorkbookPart.Workbook.Descendants<Sheet>().SingleOrDefault(s => s.Name == sheetName);
                    if (sheet == null)
                    {
                        throw new ArgumentException(
                            String.Format("No sheet named {0} found in spreadsheet {1}", sheetName, Fichier), "sheetName");
                    }
                    return (WorksheetPart)excelDoc.WorkbookPart.GetPartById(sheet.Id);
                }
    
               
                private Cell GetCell(WorksheetPart worksheetPart, string cellCoordinates)
                {
                    int rowIndex = int.Parse(cellCoordinates.Substring(1));
                    Row row = GetRow(worksheetPart, rowIndex);
    
                    Cell cell = row.Elements<Cell>().FirstOrDefault(c => cellCoordinates.Equals(c.CellReference.Value));
                    if (cell == null)
                    {
                        throw new ArgumentException(String.Format("Cell {0} not found in spreadsheet", cellCoordinates));
                    }
                    return cell;
                }
    
                private Row GetRow(WorksheetPart worksheetPart, int rowIndex)
                {
                    Row row = worksheetPart.Worksheet.GetFirstChild<SheetData>().
                                            Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
                    if (row == null)
                    {
                        throw new ArgumentException(String.Format("No row with index {0} found in spreadsheet", rowIndex));
                    }
                    return row;
                }


    After that, I had another problem with the file.
    So I use Excel Interop for repair it : 
    Missing missing = Missing.Value;
    Application excel = new Application();
    Workbook workbook = excel.Workbooks.Open(sourceFilePath,
        missing, missing, missing, missing, missing,
        missing, missing, missing, missing, missing,
        missing, missing, missing, XlCorruptLoad.xlRepairFile);
    workbook.SaveAs(savedFile, XlFileFormat.xlWorkbookDefault,
        missing, missing, missing, missing,
        XlSaveAsAccessMode.xlExclusive, missing,
        missing, missing, missing, missing);
    workbook.Close(true, missing, missing);


    Now, my program run !

    Thank very much for your help !

    MCITP EA Windows Server 2008 R2 MCSA Windows Server 2012 Apprenti développeur C# EF WPF

    • Marked as answer by Hageshii Tuesday, February 7, 2017 3:42 PM
    Tuesday, February 7, 2017 3:42 PM

All replies

  • Hi Hageshii,

    try to use the code below.

     private void button1_Click(object sender, EventArgs e)
            {
                String sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\v-padee\Desktop\ole.xlsx;Extended Properties='Excel 8.0;HDR=NO'";
                OleDbConnection objConn = new OleDbConnection(sConnectionString);
                objConn.Open();
                OleDbCommand objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A2:A2] SET F1=123456", objConn);
    
    
                objCmdSelect.ExecuteNonQuery();
                objConn.Close();
            }

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 2, 2017 5:29 AM
    Moderator
  • Thank you Deepak,

    But when I try your code, I have a error message : 

     String sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='R:\\6 - Config clients\\_0 Service Technique\\Dévelopements\\Test_Excel\\Fichiers\\Annuaire_BSF - Copie.xlsx';Extended Properties='Excel 8.0;HDR=NO'";
                    OleDbConnection objConn = new OleDbConnection(sConnectionString);
                    objConn.Open();
                    OleDbCommand objCmdSelect = new OleDbCommand("UPDATE [Feuil1$$A2:A2] SET F1=123456", objConn);
                    objCmdSelect.ExecuteNonQuery();
                    objConn.Close();
    In my program, sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='R:\\6 - Config clients\\_0 Service Technique\\Dévelopements\\Test_Excel\\Fichiers\\Annuaire_BSF - Copie.xlsx';Extended Properties='Excel 8.0;HDR=NO'"

    Error message : "The external table is not in the expected format."

    When I try with a new empty Excel File, I have this message : "Can not update. The database or object is read-only"

    The error message appears after  

    objConn.Open();

    I dont undestand... :(


    MCITP EA Windows Server 2008 R2 MCSA Windows Server 2012 Apprenti développeur C# EF WPF

    Friday, February 3, 2017 1:57 PM
  • Hi Hageshii,

    if that not worked for you then try to refer suggestion below.

    I try to test with connection string below which is working fine on my side.

    String sConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\Users\v-padee\Desktop\ole.xlsx; Extended Properties = 'Excel 8.0;HDR=Yes'";

    if you want examples then you can refer link below.

    adapter.UpdateCommand = new OleDbCommand(“UPDATE [“ + xlsSheet + “] SET FirstName = ?, LastName = ?, Age = ?” +
    
    
                                                            ” WHERE FirstName = ? AND LastName = ? AND Age = ?”, oleDBConnection);
    

    Reference:

    How to update an Excel worksheet using DataSet and the OleDbDataAdapter

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 6, 2017 7:26 AM
    Moderator
  • Hi Deepak,

    Thank you a lot.

    For my connection, it's ok with : 

    String sConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = E:\test2.xlsx; Extended Properties = 'Excel 8.0;HDR=Yes'";

    But I have a problem with my command :

    OleDbCommand objCmdSelect = new OleDbCommand("UPDATE [Annuaire$B2:B2] SET F1=123456", objConn);

    Name of my Sheet is "Annuaire". When I try to update A2 or B2, I have message : "No value given for one or more of the required parameters." I don't understand why I have this message ?

    "123456" is the value for cell.

    Do you know this message ?


    MCITP EA Windows Server 2008 R2 MCSA Windows Server 2012 Apprenti développeur C# EF WPF

    Monday, February 6, 2017 1:30 PM
  • I tryed this : "UPDATE [Annuaire$A2:A2] SET A2=123456"

    But there are nothing update in the Excel File...


    MCITP EA Windows Server 2008 R2 MCSA Windows Server 2012 Apprenti développeur C# EF WPF

    Monday, February 6, 2017 2:04 PM
  • Hi Hageshii,

    I again try to check the code and I find that I used HDR=Yes instead of NO.

    so I changed it to No and its working without any error.

    another thing I find in your code that you are using 2 $ sign after sheet name. only 1 $ sign needed. so correct it.

    below is the updated code.

     private void button1_Click(object sender, EventArgs e)
            {
                String sConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\Users\v-padee\Desktop\ole.xlsx; Extended Properties ='Excel 8.0;HDR=No'";
                OleDbConnection objConn = new OleDbConnection(sConnectionString);
                objConn.Open();
                OleDbCommand objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A2:A2] SET F1=123456", objConn);
                objCmdSelect.ExecuteNonQuery();
                objConn.Close();
            }

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 7, 2017 6:18 AM
    Moderator
  • Great !

    Now, I can write on the Excel File and select my Sheet :)

    But I have a new problem...

    Program only write on the first line.

    I change the cell on my code :

    OleDbCommand objCmdSelect = new OleDbCommand("UPDATE [Feuil1$A2:A2] SET F1=" + "\"" + "123" + "\"" , objConn);
    OleDbCommand objCmdSelect = new OleDbCommand("UPDATE [Feuil1$B4:B4] SET F1=" + "\"" + "123" + "\"" , objConn);

    If I do A2:A2, A4:A4, B2:B2 or B4:B4, program only write on the first line.  Even I Do A10:A10, program write on the first line.

    I tried with 2 files and it's the same.

    Do you know why ?


    MCITP EA Windows Server 2008 R2 MCSA Windows Server 2012 Apprenti développeur C# EF WPF

    Tuesday, February 7, 2017 10:09 AM
  • Hi,

    Finaly I have found a different solution :

    http://fczaja.blogspot.fr/2013/05/how-to-read-and-write-excel-cells-with.html
     
                public void UpdateCell(string sheetName, string cellCoordinates, object cellValue)
                {
                    using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(Fichier, true))
                    {
                        excelDoc.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
                        excelDoc.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
    
                        WorksheetPart worksheetPart = GetWorksheetPart(excelDoc, sheetName);
                        Cell cell = GetCell(worksheetPart, cellCoordinates);
                        cell.CellValue = new CellValue(cellValue.ToString());
                        worksheetPart.Worksheet.Save();
    
                    
                }
                }
    
                private WorksheetPart GetWorksheetPart(SpreadsheetDocument excelDoc, string sheetName)
                {
                    Sheet sheet = excelDoc.WorkbookPart.Workbook.Descendants<Sheet>().SingleOrDefault(s => s.Name == sheetName);
                    if (sheet == null)
                    {
                        throw new ArgumentException(
                            String.Format("No sheet named {0} found in spreadsheet {1}", sheetName, Fichier), "sheetName");
                    }
                    return (WorksheetPart)excelDoc.WorkbookPart.GetPartById(sheet.Id);
                }
    
               
                private Cell GetCell(WorksheetPart worksheetPart, string cellCoordinates)
                {
                    int rowIndex = int.Parse(cellCoordinates.Substring(1));
                    Row row = GetRow(worksheetPart, rowIndex);
    
                    Cell cell = row.Elements<Cell>().FirstOrDefault(c => cellCoordinates.Equals(c.CellReference.Value));
                    if (cell == null)
                    {
                        throw new ArgumentException(String.Format("Cell {0} not found in spreadsheet", cellCoordinates));
                    }
                    return cell;
                }
    
                private Row GetRow(WorksheetPart worksheetPart, int rowIndex)
                {
                    Row row = worksheetPart.Worksheet.GetFirstChild<SheetData>().
                                            Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
                    if (row == null)
                    {
                        throw new ArgumentException(String.Format("No row with index {0} found in spreadsheet", rowIndex));
                    }
                    return row;
                }


    After that, I had another problem with the file.
    So I use Excel Interop for repair it : 
    Missing missing = Missing.Value;
    Application excel = new Application();
    Workbook workbook = excel.Workbooks.Open(sourceFilePath,
        missing, missing, missing, missing, missing,
        missing, missing, missing, missing, missing,
        missing, missing, missing, XlCorruptLoad.xlRepairFile);
    workbook.SaveAs(savedFile, XlFileFormat.xlWorkbookDefault,
        missing, missing, missing, missing,
        XlSaveAsAccessMode.xlExclusive, missing,
        missing, missing, missing, missing);
    workbook.Close(true, missing, missing);


    Now, my program run !

    Thank very much for your help !

    MCITP EA Windows Server 2008 R2 MCSA Windows Server 2012 Apprenti développeur C# EF WPF

    • Marked as answer by Hageshii Tuesday, February 7, 2017 3:42 PM
    Tuesday, February 7, 2017 3:42 PM