GrapeCity Forums

i have a problem here according to my subject.

Can i create columnname on my spread web, with  query on XML.

here is a sample of my XML :

<ColumnCollection>
    <Column>
        <Name>Service</Name>
        <FieldText>Service</FieldText>
        <FieldId></FieldId>
        <SourceTable>T_Service</SourceTable>
        <Type>String</Type>
        <Width>200</Width>
        <LOVTarget></LOVTarget>
        <TargetResults></TargetResults>
        <Dependencies></Dependencies>
    </Column>
    <Column>
        <Name>Add_service</Name>
        <FieldText>Divisi</FieldText>
        <FieldId></FieldId>
        <SourceTable>T_Service</SourceTable>
        <Type>String</Type>
        <Width>200</Width>
        <LOVTarget></LOVTarget>
        <TargetResults></TargetResults>
        <Dependencies></Dependencies>
    </Column>
    <Column>
        <Name>Update_by</Name>
        <FieldText>Update_by</FieldText>
        <FieldId>Update_by</FieldId>
        <SourceTable>T_Service</SourceTable>
        <Type>Lookup</Type>
        <Width>200</Width>
        <LOVTarget></LOVTarget>
        <TargetResults></TargetResults>
        <Dependencies></Dependencies>
    </Column>
    <Column>
        <Name>Description</Name>
        <FieldText>Desc</FieldText>
        <FieldId>Description</FieldId>
        <SourceTable>T_Service</SourceTable>
        <Type>Lookup</Type>
        <Width>200</Width>
        <LOVTarget></LOVTarget>
        <TargetResults></TargetResults>
        <Dependencies></Dependencies>
    </Column> 

</CollumnCollection>

 I have a class that can show this column without any query on it .

   public List<FPColumn> Columns { get; set; } //array list to get columnname
    public ColumnHelper() {
        var Doc = XDocument.Load(String.Format("{0}\\EntryAnggaranBiaya.xml", System.Web.HttpContext.Current.Server.MapPath("~")));
       
        var Cols = from x in Doc.Descendants("Column")
                   select new FPColumn() {
                       Name         = x.Element("Name") == null ? "" : x.Element("Name").Value,
                       FieldText    = String.IsNullOrEmpty(x.Element("FieldText").Value) ? "" : x.Element("FieldText").Value,
                       FieldId      = String.IsNullOrEmpty(x.Element("FieldId").Value) ? "" : x.Element("FieldId").Value,
                       SourceTable  = String.IsNullOrEmpty(x.Element("SourceTable").Value) ? "" : x.Element("SourceTable").Value,
                       Type         = GetAnggaranBiayaCellType(String.IsNullOrEmpty(x.Element("Type").Value) ? "" : x.Element("Type").Value),
                       SubType      = GetAnggaranBiayaCellType(x.Element("SubType") == null ? "" : x.Element("SubType").Value),
                       LOVTarget    = String.IsNullOrEmpty(x.Element("LOVTarget").Value) ? "" : x.Element("LOVTarget").Value,
                       Width        = x.Element("Width") == null ? 0 : int.Parse(x.Element("Width").Value),
                       TargetResults = String.IsNullOrEmpty(x.Element("TargetResults").Value) ? "" : x.Element("TargetResults").Value,
                       Dependencies = String.IsNullOrEmpty(x.Element("Dependencies").Value) ? "" : x.Element("Dependencies").Value,
                       SubField     = x.Element("SubField") == null ? "" : x.Element("SubField").Value,
                   };
    
        var ExpandedColumns = new List<FPColumn>();
        /* First Iteration, expand all number for all columns */
        foreach (var x in Cols) { ExpandedColumns.Add(x); }

     //.... next code

What i want is, <Name> is created by my query, such as : Select * from T_Service. So that, the columname from table T_service can be shown on my spread (Service, Add_service, Update_by, Description).

May be like this : <Name>Select * from T_SERVICE<Name>

Plis Help me.

Hello, 

If you want to dynamically add the columns to your spread from your XML, you may use the code below:

        FpSpread1.Sheets[0].ColumnCount = 0;

        var Doc = XDocument.Load(String.Format("{0}\\XMLFILE.xml", Server.MapPath("~")));

        var res = (from d in Doc.Descendants("Column")

                   select d.Element("Name").Value).ToList();

        FpSpread1.ActiveSheetView.Columns.Add(0, res.Count);

        for (int i = 0; i < res.Count; i++)

        {

            FpSpread1.ActiveSheetView.Columns.Label = res;

        }

You cannot specify different datasource for different column. You may specify the DataSource for the sheet or for the Spread control. You may then specify the DataField property of the Column using

FpSpread1.ActiveSheetView.Columns.DataField=XElement; //you may generate the XElement the same way as you've generated the ColumnName in above code;

Regards, 

 

---

Scott S.
Product Manager, Spread ASP.NET
GrapeCity FarPoint

i didn't specify different datasource for different collumn on spread. I only used XML datasource. What i need is how to create some query from database on XML. The query is to select columnname from a table. Others columnname of XML are static.

Hello,

Have you also tried setting the AutoGenerateColumns to false and then using the datafield proeprty you can align the required column to the column you want to display. This will be applicable assuming you are using ReadXML of dataset for the binding Spread with an xml file.

 

---

Suresh Singh Dasila
GrapeCity FarPoint

I still don't get it bro..

Can u give some thread links about XML parsing within OracleDatareader Query?

Or, is there any trick that i can use to add some Coloum Name element XML from OracleDataReader Query?

Like this :

#region Add Column from T_SERVICE table

using (var Conn = new OracleConnection(Util.ConnStr))

{

Conn.Open();

using (var cmd = Conn.CreateCommand())

{

cmd.CommandText

= "select NAME"

+ " from T_SERVICE ";

using (var reader = cmd.ExecuteReader())

{

while (reader.Read())

{

//ExpandedColumns.Add(FPColumn);

// FPColumn test = new FPColumn();

// string ColumnName = reader.GetString(0);

// I don't have any correct syntax here, to add columnname from Sql To XML Documents.

 

}

reader.Close();

} // End Reader

} // End Command

Conn.Close();

} // End Connection

#endregion

 

 

Thx

I am not able to comprehend your requirement. In your first response, I understand that you wanted to generate add the Columns in your spread control by reading the Column>>Name elements from the XML that you provided. The code that I provided to you earlier does exactly the same. 

I do not understand what do you mean by, "XML parsing within OracleDatareader Query" or " i can use to add some Coloum Name element XML from OracleDataReader Query"

Regards, 

---

Scott S.
Product Manager, Spread ASP.NET
GrapeCity FarPoint

Dear Harish n else.. Thx for your reply.

The problem is solve. I have created dynamic columns to spread with a sql query (oracledatareader) on my class, like code below. May be it's confusing for you all, cause i am a new comer on spread web . C# and XML.

On XML file, i just create

 <Column>

<Name>Service</Name>

<FieldText>Service</FieldText>

<FieldId>Service_ID</FieldId>

<SourceTable>T_SERVICE</SourceTable>

<Type>Expand</Type>

<Width>100</Width>

<LOVTarget></LOVTarget>

<TargetResults></TargetResults>

<Dependencies></Dependencies>

</Column>

 

 

private int MakeServiceColumn(int ColIdx, SheetView ActiveSheet, FPColumn Column)

//One of my methods to create Dynamic Columns contains record of my sql query.

{

int StartIdx= ColIdx;

var Result = new Dictionary<String, String>();

using (var Conn = new OracleConnection(Util.ConnStr))

{

Conn.Open();

using (var cmd = Conn.CreateCommand())

{

cmd.CommandText

= "select NAME "

+ " from SERVICE ";

using (var reader = cmd.ExecuteReader())

{

while (reader.Read())

{

ActiveSheet.ColumnCount = ActiveSheet.ColumnCount + 1;

ActiveSheet.ColumnHeader.Cells[0, ColIdx].Text = reader.GetString(0);

ActiveSheet.Columns[ColIdx].Width = Column.Width;

ActiveSheet.Columns[ColIdx].CellType = new PercentCellType();

ActiveSheet.Columns[ColIdx].VerticalAlign = VerticalAlign.Middle;

ActiveSheet.Columns[ColIdx].Border.BorderStyleRight = BorderStyle.None;

ActiveSheet.Columns[ColIdx].Resizable = false;

ActiveSheet.Columns[ColIdx].BackColor = System.Drawing.Color.LightBlue;

for (int i = 0; i < ActiveSheet.RowCount; i++)

{

ActiveSheet.SetValue(i, ColIdx, 0);

}

ColIdx++;

}

reader.Close();

} // End Reader

} // End Command

Conn.Close();

} // End Connection

 

ColIdx = MakeTotalPercentColumn(ColIdx, StartIdx, ActiveSheet, Column);

// ActiveSheet.Columns[ColIdx].

return ColIdx;

 

 

}

But now, i have another problem with this custom spread about save and load from database.

1. was there any clue of it? I must insert hundred rows at one time on one sheet.

2. The paging i created, also have a problem, next page doesn't only contain next rows, but also previous rows. Is it Spread Bug?

You may refer the spWebProductTour sample that is shipped along with the spread control. It shold be available under following directory in your computer: 

C:\Program Files\FarPoint Technologies\Spread.ASP.4.dotNet35\v4.0.3506\Samples\Vb

In this sample, you may refer the "DataBinding" and "Paging" samples.

Regards, 

---

Scott S.
Product Manager, Spread ASP.NET
GrapeCity FarPoint

I have no answer from this sample.

I already have spread with custom columns from my XML above PLUS autogenerate columns from my query.

here is my code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using FarPoint.Web.Spread;
using Oracle.DataAccess.Client;
using System.Configuration;
using System.Data;
public partial class EntryAnggaranBiaya : System.Web.UI.Page {
    protected void Page_Load(object sender, EventArgs e) {
       

    }
 

    protected void Page_Init(object sender, EventArgs e) {
        CreateColumns();
        if (!Page.IsPostBack)
        {
            sp.ClientAutoCalculation = true;           
        }
     
    }

    protected void CreateColumns() {
        AnggaranBiayaHelper Helper = new AnggaranBiayaHelper();
        var ActiveSheet = sp.Sheets[0];
        ActiveSheet.ColumnCount = 0;
        ActiveSheet.RowCount = 5;
        ActiveSheet.PageSize = 5;
      
        //start create columns to spread

        int idx = 0;
        foreach (FPColumn Col in Helper.Columns) {
            switch (Col.Type) {
                case FPColumnType.Integer:
                    idx = MakeIntegerColumn(idx, ActiveSheet, Col);
                    break;
                case FPColumnType.Lookup:
                    idx = MakeLookupColumn(idx, ActiveSheet, Col);
                    break;
                case FPColumnType.Percent:
                    idx = MakePercentColumn(idx, ActiveSheet, Col);
                    break;
                case FPColumnType.Combobox:
                    idx = MakeComboColumn(idx, ActiveSheet, Col);
                    break;
                case FPColumnType.Expand:
                    idx = MakeJasaColumn(idx, ActiveSheet, Col);
                    break;
                default:
                    idx = MakeStringColumn(idx, ActiveSheet, Col);
                    break;
            }
        }
       
       
        ActiveSheet.ColumnCount = ActiveSheet.ColumnCount + 1; //  A stupid hack to prevent last column from shrinking
        ActiveSheet.Columns[ActiveSheet.ColumnCount - 1].Width = 0;  // A stupid hack to prevent last column from shrinking
        ActiveSheet.Columns[ActiveSheet.ColumnCount - 1].Resizable = false;
       

    }

 ..... etc

 Can u give me code to parse all of my column into a "SESSION["DATA"]" or may be into a MODEL spread or may be any other clues, so that i can bind it to Database when user insert/update values with my spread?

 

Hello,

I am not sure what you are trying to do exactly. It sounds like you set up a template for number and type of columns in Spread and then as the user types into the Spread, you want to update this changed data to some database. If Spread is not bound to a DataSource, then there is nothing in Spread to update a database directly. However, you should be able to programatically loop the rows and columns in the Spread to get the data to build SQL statements to do what you want to a database (insert, delete and update).

---

Scott S.
Product Manager, Spread ASP.NET
GrapeCity FarPoint

You are right Scotts, sorry, my english not very well.

Is there any code or sample code to be able to programatically loop the rows and columns in the Spread to get the data with oracle SQL statement?

I've been searching every where in this forum, but i can't find it.

is there anyone can help me?

Can i set a template datasource from my custom spread so that i can bind it to my database?  or may be AJAX call can make it easy to programatically loop the rows and columns in the spread to get the data?

 

thx.

You may use the CallBack client side method of the Spread and pass any non previously defined parameter to make a partial post back to the page. This will call the ButtonCommand event of the spread on server side and depending on the parameter, you may implement the Insert, Update and Delete operations. For Example 

ClientSide code:

var Spread=document.getElementById("FpSpread1");

Spread.UpdatePostbackData(); 

Spread.CallBack("Insert");

Server Side code, FpSpread1_ButtonCommand event

if(e.CommandName=="Insert")

{

//code to insert the data in SQL Server goes here

//You may loop through all the cells to get the values using

for(int i=0;i<FpSpread1.ActiveSheetView.RowCount-1; i++)

{

for(int j=0;j<FpSpread1.ActiveSheetView.ColumnCount-1;j++)

{

string CellValue=FpSpread1.ActiveSheetView.Cell[i,j].Text; 

}

} 

Regards,

---

Scott S.
Product Manager, Spread ASP.NET
GrapeCity FarPoint

Thx Haris.

 

But i still cannot solve my problem with that code. I have about 132 columns in my spread, generated by reading my XML above, and user can input/update one row or may be 10 rows at one time ( eg: copy paste from excel ) .

So, after i created these columns, i need to know, how to relate these custom columns to my database.

Here is my class to collect columnname from my xml on my fist post above.

 

using System;
using System.Collections.Generic;
using System.Xml.Schema;
using System.Xml.Linq;
using System.Linq;


/// <summary>
/// Summary description for AnggaranBiayaHelper
/// </summary>
public class AnggaranBiayaHelper {
    public List<FPColumn> Columns { get; set; }
    public AnggaranBiayaHelper() {
        var Doc = XDocument.Load(String.Format("{0}\\EntryAnggaranBiaya.xml", System.Web.HttpContext.Current.Server.MapPath("~")));
       
        var Cols = from x in Doc.Descendants("Column")
                   select new FPColumn() {
                       Name         = x.Element("Name") == null ? "" : x.Element("Name").Value,
                       FieldText    = String.IsNullOrEmpty(x.Element("FieldText").Value) ? "" : x.Element("FieldText").Value,
                       FieldId      = String.IsNullOrEmpty(x.Element("FieldId").Value) ? "" : x.Element("FieldId").Value,
                       SourceTable  = String.IsNullOrEmpty(x.Element("SourceTable").Value) ? "" : x.Element("SourceTable").Value,
                       Type         = GetAnggaranBiayaCellType(String.IsNullOrEmpty(x.Element("Type").Value) ? "" : x.Element("Type").Value),
                       SubType      = GetAnggaranBiayaCellType(x.Element("SubType") == null ? "" : x.Element("SubType").Value),
                       LOVTarget    = String.IsNullOrEmpty(x.Element("LOVTarget").Value) ? "" : x.Element("LOVTarget").Value,
                       Width        = x.Element("Width") == null ? 0 : int.Parse(x.Element("Width").Value),
                       TargetResults = String.IsNullOrEmpty(x.Element("TargetResults").Value) ? "" : x.Element("TargetResults").Value,
                       Dependencies = String.IsNullOrEmpty(x.Element("Dependencies").Value) ? "" : x.Element("Dependencies").Value,
                       SubField     = x.Element("SubField") == null ? "" : x.Element("SubField").Value,
                   };
        //var OriginalColumns =
        var ExpandedColumns = new List<FPColumn>();
        /* First Iteration, expand all number for all columns */
        foreach (var x in Cols) { ExpandedColumns.Add(x); }     


        /* Second Iteration, generate number to all columns */
        int IdxColumn = 0;
        foreach (FPColumn Col in ExpandedColumns) {
            switch (Col.Type) {
                case FPColumnType.Lookup:
                    Col.TextIndex = IdxColumn; IdxColumn++;
                    Col.IdIndex = IdxColumn; IdxColumn++;
                    Col.ColumnIndex = IdxColumn; IdxColumn++;                   
                    break;
               
                default:
                    Col.TextIndex = IdxColumn;
                    Col.ColumnIndex = IdxColumn;
                    Col.IdIndex = IdxColumn; IdxColumn++;
                    break;
            }
        }
        var ColumnLibrary = ExpandedColumns.ToDictionary(x => x.Name);

          
        /* Third Iteration, fill number to all related columns (result and dependencies) */
        foreach (FPColumn Col in ExpandedColumns) {
            if (!String.IsNullOrEmpty(Col.TargetResults)) {
                var ResultColumns = Col.TargetResults.Split(',');
              
                foreach (String TargetColumnName in ResultColumns) {
                    var TargetColumn = ColumnLibrary[TargetColumnName.Trim()];
                    Col.ResultIndexes = Col.ResultIndexes + TargetColumn.TextIndex.ToString() + "|";
                   
                }
                if (Col.ResultIndexes.EndsWith("|"))
                    Col.ResultIndexes = Col.ResultIndexes.Remove(Col.ResultIndexes.Length - 1, 1);
            }
            else {
                Col.ResultIndexes = "-1";
            }

            if (!String.IsNullOrEmpty(Col.Dependencies)) {
                var TargetColumn = ColumnLibrary[Col.Dependencies.Trim()];
                Col.DependencyIndex = Col.DependencyIndex + TargetColumn.IdIndex.ToString();
            }
        }
        Columns = ExpandedColumns;
       
       
    }
    private FPColumnType GetAnggaranBiayaCellType(string TypeString) {
        switch (TypeString.ToUpper()) {
            case "LOOKUP": return FPColumnType.Lookup;
            case "INTEGER": return FPColumnType.Integer;
            case "MONEY": return FPColumnType.Money;
            case "GROUP": return FPColumnType.Group;
            case "DATE": return FPColumnType.Date;
            case "PERCENT": return FPColumnType.Percent;
            case "COMBOBOX": return FPColumnType.Combobox;
            case "EXPAND": return FPColumnType.Expand;
            default: return FPColumnType.String;
        }
    }

}

 

Thx

 

To bind the spread you have to set the Spread's DataSource property or the DataSourceId property along with the DataMember property. To bind the fields you've to set the Column's DataField property. 

Regards, 

---

Scott S.
Product Manager, Spread ASP.NET
GrapeCity FarPoint