How can I get cell values ?

[aykut]

Hi,

I cannot get any kind of information out of spreadsheet. sheet count is zero, I could not figured out how to access cells...

I load demo xlsx files but I cannot access any information. also there's not much in help section.

 

Does this control serves just lo load and show xls files ? what I'm missing ?

[Bozhidar]

Hello,

You have to call the DataBind() method in order to access the Sheets and Cells in the code behind.

Regards,
Bozhidar
Telerik

[aykut]

okay my friend but problem is I cannot even run simple commands like:

        Dim workbook As New Workbook()
        Dim worksheet As Worksheet = workbook.Worksheets.Add()
        worksheet.Name = "Expense Report 2014"

I tried page_load event, page_init event. I read blogpost "SpreadProcessing - Complete Solution.....". obviously I'm doing something wrong. can you please provide a simple sample to add a new worksheet and set a cell's value please ? so I can understand which namespaces should I use because I believe I have problem with importing correct ones. and all the demos are for winforms, I need aspnet solution. I really tried hard but I'm stuck.

best.

[Bozhidar]

Hello,

The Spreadsheet Control doesn't implement the same interface as the SpreadProcessing library, so there are some differences to be expected. Here's some sample code that populates the Spreadsheet with data from the Products table of the Northwind database, which should point you in the right track:

protected void Page_Load(object sender, EventArgs e)
{
    RadSpreadSheet1.Sheets.Clear();

    foreach (var sheet in GetSheets())
    {
        RadSpreadSheet1.Sheets.Add(sheet);
    }
}
public List<Worksheet> GetSheets()
{
    var result = new List<Worksheet>();

    DataTable data = GetData();

    var sheet = new Worksheet();

    int rowIndex = 0;
    foreach (DataRow dataRow in data.Rows)
    {
        var row = new Row() { Index = rowIndex++ };

        int columnIndex = 0;
        foreach (DataColumn dataColumn in data.Columns)
        {
            if (dataColumn.ColumnName == "ID") continue; // Skip the ID column

            string cellValue = dataRow[dataColumn.ColumnName].ToString();

            var cell = new Cell() { Index = columnIndex++, Value = cellValue };

            row.AddCell(cell);
        }

        sheet.AddRow(row);
    }

    result.Add(sheet);

    return result;
}

public DataTable GetData()
{
    using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
    {
        conn.Open();
        string query = "SELECT * FROM [Products]";
        SqlCommand cmd = new SqlCommand(query, conn);

        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());
        return dt;
    }
}

Regards,
Bozhidar
Telerik

[aykut]

okay, not I can add sheets, thats fine but then I cannot access them. sheetcount is zero so I cannot access its content although my sheets are visible on the page. I will try to get cell values that user will enter. that's my goal :)

 some more help please...

 

  Protected Sub Page_Init(sender As Object, e As EventArgs) Handles Me.Init
      If Not IsPostBack Then
          Dim w As New Telerik.Web.Spreadsheet.Worksheet
          w.Name = "test 1"
          RadSpreadsheet1.Sheets.Add(w)
          Dim w2 As New Telerik.Web.Spreadsheet.Worksheet
          w2.Name = "test 2"
          RadSpreadsheet1.Sheets.Add(w2)
      End If
  End Sub

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

      Dim s As Integer = RadSpreadsheet1.Sheets.Count

      Response.Write(s.ToString)

  End Sub

[aykut]

hmmm, looks like on every postback RadSpreadsheet looses its sheets I added before. I tried Page_load or page_init events but nothing changes.

Is there a possibility that this component designed just to show data ?

Because I want to create a blank sheet on page and user will fill it in then when he clicks a button I'll read the values he entered.

I wont work database or anything.

[Bozhidar]

Hi,

The Spreadsheet control doesn't use ViewState in order to make it more lightweight, and therefore cannot automatically preserve its state across postbacks. If you want to be able to read and write to a custom datasource, you will have to implement a custom provider, which the control will be bound to. Using the provider you can use the "Save" button of the Spreadsheet toolbar to save the state at any time.

I've attached a sample page where a custom provider is implemented. It should be able to point you in the right direction while implementing your requirements.

Regards,
Bozhidar
Telerik

[aykut]

aha... that's exactly what I need. thank you. I could never think custom data provider.

 you are the best.

[Prashanti]

Hi,

How can i bind the toolbar Save to a handler method?

 

thanks,

[Konstantin Dikov]

Hello Prashanti,

The control is still in beta version and it does not provide functionality for handling such events.

Nevertheless, you could try to get reference to the "Save" button in the toolbar and attach handler for the "onmousedown" event.


Best Regards,
Konstantin Dikov
Telerik

[Kalyanakumar]

Hi,

I have a webpage using RadSpreadsheet control. Now i have to write the event to set the range values to validate only numbers. and i need to get the cell edit event to change the cell forecolore based on the values. like 0-3 red others green and maximum 10 else error messge.

Can anybody give the details for me.

[Bozhidar]

Hello,

The issue that was the topic of this forum thread has been answered and resolved. Since your question is not related to the current topic, I would advise you to open a separate forum thread, where the specific case can be investigated.

Regards,
Bozhidar
Telerik by Progress

[Fay]

Hello Bozidar's ,

Can you please help me with sample code to save file on Shared location .Its an export functionality that I am working on.

 

 

Regards,

farheen