locked
How to populate data in specific cell of excel sheet. RRS feed

  • Question

  • User-1431525522 posted

    Hello,

    I have a requirement to populate data in a specific cell of existing excel sheet template from a database. I have an excel sheet with all the calculation done, just fill data on certain cell and that template will do rest of the calculations. I want to store that excel template on a server, so that once the user populate data on that sheet, he can download that excel sheet.

    I need a concept, that how can I to achieve this thing, and additional to that I want to keep every formatting of excel template like color, fonts everything as it is.

    Friday, January 8, 2016 10:25 AM

All replies

  • User269602965 posted

    Use OLEDB to insert data into a cell.  In this example a string value into CELL B12:B12 of a spreadsheet sheet

        Try
          Dim strYourData = "MYDATA"
          Dim strOLEDBConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strTargetXLSM & ";" & "Extended Properties='Excel 12.0 Macro;HDR=NO;'"
          Using OLEDBConn As New OleDbConnection(strOLEDBConn)
            OLEDBConn.Open()
            Using cmdUpdate As OleDbCommand = OLEDBConn.CreateCommand()
                cmdUpdate.CommandText = "UPDATE [{TEMPLATESHEETNAME}$B12:B12"] SET F1='" & strYourData & "'"
                cmdUpdate.ExecuteNonQuery()
            End Using
          End Using
        Catch oledbex As OleDbException
          MsgBox("Error: OLEDB failed to update worksheet." & oledbex.ToString, MsgBoxStyle.OkOnly, "Error: OLEDB error logged")
        End Try
    

    Saturday, January 9, 2016 3:50 AM