SPREAD for ASP.NETデモアプリケーション

FpSpreadクラスのDataSourceプロパティに対してデータセットを設定した場合、デフォルトでは全てのフィールドが自動的に連結されます。

ただし、ColumnsクラスのDataFieldプロパティを使用すると、表示フィールドの位置（順番）を変更したり、ある特定のフィールドのみを連結することができます。

このサンプルでSPREADと連結しているテーブルは、１テーブルに月単位に「計画」列と「実績」列を保持しています。
例）「4月計画」、「4月実績」、「5月計画」、「5月実績」...

「計画データのみを連結」ボタンを押下すると、テーブル上の「計画」列のみをSPREADと連結します。
「実績データのみを連結」ボタンを押下すると、テーブル上の「実績」列のみをSPREADと連結します。
「全データを連結」ボタンを押下すると、テーブル上の全ての列をSPREADと連結します。

	製品ID	製品分類	製品名	4月計画	4月実績	5月計画	5月実績	6月計画	6月実績	7月計画	7月実績	8月計画	8月実績	9月計画	9月実績	10月計画	10月実績	11月計画	11月実績	12月計画	12月実績	1月計画	1月実績	2月計画	2月実績	3月計画	3月実績
1	10001	乳製品	酪農ミルク	5,500	5,600	5,000	5,100	4,500	4,600	6,000	6,100	5,500	5,600	5,000	5,100	4,500	4,600	6,000	6,100	5,500	5,600	5,000	5,100	4,500	4,600	6,000	6,100
2	20001	清涼飲料水	いよかんドリンク	1,000	1,100	3,000	3,100	2,700	2,800	2,700	2,800	1,000	1,100	3,000	3,100	2,700	2,800	2,700	2,800	1,000	1,100	3,000	3,100	2,700	2,800	2,700	2,800
3	20002	清涼飲料水	ぶどうジュース	3,000	3,100	3,500	3,600	4,800	4,900	4,800	4,900	3,000	3,100	3,500	3,600	4,800	4,900	4,800	4,900	3,000	3,100	3,500	3,600	4,800	4,900	4,800	4,900
4	20003	清涼飲料水	マンゴードリンク	2,000	2,100	1,000	1,100	500	600	1,050	1,150	2,000	2,100	1,000	1,100	500	600	1,050	1,150	2,000	2,100	1,000	1,100	500	600	1,050	1,150
5	30001	ビール	激辛ビール	5,500	5,600	8,000	8,100	8,500	8,600	10,000	10,100	5,500	5,600	8,000	8,100	8,500	8,600	10,000	10,100	5,500	5,600	8,000	8,100	8,500	8,600	10,000	10,100
6	30002	ビール	モルトビール	3,000	3,100	3,500	3,600	2,780	2,880	4,000	4,100	3,000	3,100	3,500	3,600	2,780	2,880	4,000	4,100	3,000	3,100	3,500	3,600	2,780	2,880	4,000	4,100
7	20004	清涼飲料水	ぶどうの街	500	600	300	400	200	300	700	800	500	600	300	400	200	300	700	800	500	600	300	400	200	300	700	800
8	30003	ビール	オリエントの村	8,000	8,100	9,500	9,600	9,580	9,680	9,000	9,100	8,000	8,100	9,500	9,600	9,580	9,680	9,000	9,100	8,000	8,100	9,500	9,600	9,580	9,680	9,000	9,100
9	40002	焼酎	吟醸ほめごろし	6,000	6,100	7,000	7,100	9,000	9,100	9,500	9,600	6,000	6,100	7,000	7,100	9,000	9,100	9,500	9,600	6,000	6,100	7,000	7,100	9,000	9,100	9,500	9,600
10	40003	焼酎	大吟醸オリエント	1,000	1,100	5,000	5,100	6,000	6,100	5,000	5,100	1,000	1,100	5,000	5,100	6,000	6,100	5,000	5,100	1,000	1,100	5,000	5,100	6,000	6,100	5,000	5,100
11	40005	焼酎	麦焼酎ちこちこ	1,000	1,100	1,500	1,600	1,200	1,300	1,258	1,358	1,000	1,100	1,500	1,600	1,200	1,300	1,258	1,358	1,000	1,100	1,500	1,600	1,200	1,300	1,258	1,358
12	10002	乳製品	酪農ミルク（低脂肪）	501	601	202	302	380	480	456	556	501	601	202	302	380	480	456	556	501	601	202	302	380	480	456	556

ソースコード

C#
VB
ASPX

別ウィンドウで表示

using System;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;

public partial class fieldbinding : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack) return;

        // データ連結
        BindSpread(0);
        
        // SPREAD初期化
        InitSpread(FpSpread1.Sheets[0]);

        // クライアント側スクリプトの設定
        string clientScript = "<script language=\"JavaScript\">";
        clientScript += "function bindschedule()";
        clientScript += "{";
        clientScript += "var spread = document.getElementById(\"" + FpSpread1.ClientID + "\");";
        clientScript += "spread.CallBack(\"bindschedule\");";
        clientScript += "}";
        clientScript += "function bindresult()";
        clientScript += "{";
        clientScript += "var spread = document.getElementById(\"" + FpSpread1.ClientID + "\");";
        clientScript += "spread.CallBack(\"bindresult\");";
        clientScript += "}";
        clientScript += "function bindall()";
        clientScript += "{";
        clientScript += "var spread = document.getElementById(\"" + FpSpread1.ClientID + "\");";
        clientScript += "spread.CallBack(\"bindall\");";
        clientScript += "}";
        clientScript += "</script>";

        ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "fieldbindingScript", clientScript, false);
    }

    private void InitSpread(FarPoint.Web.Spread.SheetView sheet)
    {
        // SPREAD設定
        sheet.FpSpread.CommandBar.Visible = false;
        sheet.FpSpread.CssClass = "spreadStyle";
        sheet.FpSpread.UseClipboard = false;

        // フォントサイズの設定
        sheet.DefaultStyle.Font.Size = FontUnit.Parse("80%");
        sheet.ColumnHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%");
        sheet.RowHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%");
        sheet.SheetCorner.DefaultStyle.Font.Size = FontUnit.Parse("80%");

        // シート設定
        sheet.PageSize = sheet.RowCount;

        // 列幅の設定
        sheet.Columns[0].Width = 45;
        sheet.Columns[1].Width = 85;
        sheet.Columns[2].Width = 140;

        for (int i = 3; i < FpSpread1.ActiveSheetView.ColumnCount; i++)
        {
            sheet.Columns[i].Width = 65;    
        }

        // 縦方向の揃え位置を中央に設定
        sheet.DefaultStyle.VerticalAlign = VerticalAlign.Middle;

        // 各列のセル型を設定
        FarPoint.Web.Spread.GeneralCellType gnr = new FarPoint.Web.Spread.GeneralCellType();
        gnr.FormatString = "#,##0";
        sheet.Columns[3, FpSpread1.ActiveSheetView.ColumnCount -1].CellType = gnr;
        sheet.Columns[3, FpSpread1.ActiveSheetView.ColumnCount - 1].HorizontalAlign = HorizontalAlign.Right;
    }

    private void BindSpread(int dataflg)
    {
        // リセット
        FarPoint.Web.Spread.SheetView sht = new FarPoint.Web.Spread.SheetView();
        sht = FpSpread1.ActiveSheetView;
        FpSpread1.Reset();
        FpSpread1.Sheets.Add(sht);

        // セル型の自動設定を無効化
        FpSpread1.ActiveSheetView.DataAutoCellTypes = false;

        DataSet ds = new DataSet();
        ds.ReadXml(MapPath("../App_Data/databind.xml"));

        if (dataflg == 0)
        {
            FpSpread1.ActiveSheetView.AutoGenerateColumns = true;
            FpSpread1.ActiveSheetView.DataSource = ds;
        }
        else
        {
            // 連結時の列自動生成を無効
            FpSpread1.ActiveSheetView.AutoGenerateColumns = false;
            FpSpread1.ActiveSheetView.ColumnCount = 15;

            FpSpread1.ActiveSheetView.DataSource = ds;

            FpSpread1.ActiveSheetView.Columns[0].DataField = "製品ID";
            FpSpread1.ActiveSheetView.Columns[1].DataField = "製品分類";
            FpSpread1.ActiveSheetView.Columns[2].DataField = "製品名";

            if (dataflg == 1)
            {
                FpSpread1.ActiveSheetView.Columns[3].DataField = "4月計画";
                FpSpread1.ActiveSheetView.Columns[4].DataField = "5月計画";
                FpSpread1.ActiveSheetView.Columns[5].DataField = "6月計画";
                FpSpread1.ActiveSheetView.Columns[6].DataField = "7月計画";
                FpSpread1.ActiveSheetView.Columns[7].DataField = "8月計画";
                FpSpread1.ActiveSheetView.Columns[8].DataField = "9月計画";
                FpSpread1.ActiveSheetView.Columns[9].DataField = "10月計画";
                FpSpread1.ActiveSheetView.Columns[10].DataField = "11月計画";
                FpSpread1.ActiveSheetView.Columns[11].DataField = "12月計画";
                FpSpread1.ActiveSheetView.Columns[12].DataField = "1月計画";
                FpSpread1.ActiveSheetView.Columns[13].DataField = "2月計画";
                FpSpread1.ActiveSheetView.Columns[14].DataField = "3月計画";
            }
            else
            {
                FpSpread1.ActiveSheetView.Columns[3].DataField = "4月実績";
                FpSpread1.ActiveSheetView.Columns[4].DataField = "5月実績";
                FpSpread1.ActiveSheetView.Columns[5].DataField = "6月実績";
                FpSpread1.ActiveSheetView.Columns[6].DataField = "7月実績";
                FpSpread1.ActiveSheetView.Columns[7].DataField = "8月実績";
                FpSpread1.ActiveSheetView.Columns[8].DataField = "9月実績";
                FpSpread1.ActiveSheetView.Columns[9].DataField = "10月実績";
                FpSpread1.ActiveSheetView.Columns[10].DataField = "11月実績";
                FpSpread1.ActiveSheetView.Columns[11].DataField = "12月実績";
                FpSpread1.ActiveSheetView.Columns[12].DataField = "1月実績";
                FpSpread1.ActiveSheetView.Columns[13].DataField = "2月実績";
                FpSpread1.ActiveSheetView.Columns[14].DataField = "3月実績";
            }
            FpSpread1.DataBind();
        }
    }

    protected void FpSpread1_ButtonCommand(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)
    {
        if (e.CommandName == "bindschedule")
        {
            // 計画データ連結
            BindSpread(1);

            FarPoint.Web.Spread.SheetView sheet = FpSpread1.Sheets[0];
            
            // SPREAD初期化
            InitSpread(sheet);  
        }
        else if (e.CommandName == "bindresult")
        {
            // 実績データ連結
            BindSpread(2);

            FarPoint.Web.Spread.SheetView sheet = FpSpread1.Sheets[0];
            
            // SPREAD初期化
            InitSpread(sheet);
        }
        else if (e.CommandName == "bindall")
        {
            // 全データ連結
            BindSpread(0);

            FarPoint.Web.Spread.SheetView sheet = FpSpread1.Sheets[0];
            
            // SPREAD初期化
            InitSpread(sheet);
        }
    }
}


Partial Public Class fieldbinding
    Inherits System.Web.UI.Page
   
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsPostBack Then
            Return
        End If

        ' データ連結
        BindSpread(0)

        ' SPREAD初期化
        InitSpread(FpSpread1.Sheets(0))

        ' クライアント側スクリプトの設定
        Dim clientScript As String = "<script language=""JavaScript"">"
        clientScript += "function bindschedule()"
        clientScript += "{"
        clientScript += "var spread = document.getElementById(""" + FpSpread1.ClientID & """);"
        clientScript += "spread.CallBack(""bindschedule"");"
        clientScript += "}"
        clientScript += "function bindresult()"
        clientScript += "{"
        clientScript += "var spread = document.getElementById(""" + FpSpread1.ClientID & """);"
        clientScript += "spread.CallBack(""bindresult"");"
        clientScript += "}"
        clientScript += "function bindall()"
        clientScript += "{"
        clientScript += "var spread = document.getElementById(""" + FpSpread1.ClientID & """);"
        clientScript += "spread.CallBack(""bindall"");"
        clientScript += "}"
        clientScript += "</script>"

        ScriptManager.RegisterClientScriptBlock(Me, Me.GetType(), "fieldbindingScript", clientScript, False)
    End Sub

    Private Sub InitSpread(ByVal sheet As FarPoint.Web.Spread.SheetView)
        ' SPREAD設定
        sheet.FpSpread.CommandBar.Visible = False
        sheet.FpSpread.CssClass = "spreadStyle"
        sheet.FpSpread.UseClipboard = False

        ' フォントサイズの設定
        sheet.DefaultStyle.Font.Size = FontUnit.Parse("80%")
        sheet.ColumnHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%")
        sheet.RowHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%")
        sheet.SheetCorner.DefaultStyle.Font.Size = FontUnit.Parse("80%")

        ' シート設定
        sheet.PageSize = sheet.RowCount

        ' 列幅の設定
        sheet.Columns(0).Width = 45
        sheet.Columns(1).Width = 85
        sheet.Columns(2).Width = 140

        For i As Integer = 3 To FpSpread1.ActiveSheetView.ColumnCount - 1
            sheet.Columns(i).Width = 65
        Next

        ' 縦方向の揃え位置を中央に設定
        sheet.DefaultStyle.VerticalAlign = VerticalAlign.Middle

        ' 各列のセル型を設定
        Dim gnr As New FarPoint.Web.Spread.GeneralCellType()
        gnr.FormatString = "#,##0"
        sheet.Columns(3, FpSpread1.ActiveSheetView.ColumnCount - 1).CellType = gnr
        sheet.Columns(3, FpSpread1.ActiveSheetView.ColumnCount - 1).HorizontalAlign = HorizontalAlign.Right
    End Sub

    Private Sub BindSpread(ByVal dataflg As Integer)
        ' リセット
        Dim sht As New FarPoint.Web.Spread.SheetView()
        sht = FpSpread1.ActiveSheetView
        FpSpread1.Reset()
        FpSpread1.Sheets.Add(sht)

        ' セル型の自動設定を無効化
        FpSpread1.ActiveSheetView.DataAutoCellTypes = False

        Dim ds As New System.Data.DataSet()
        ds.ReadXml(MapPath("../App_Data/databind.xml"))

        If dataflg = 0 Then
            FpSpread1.ActiveSheetView.AutoGenerateColumns = True
            FpSpread1.ActiveSheetView.DataSource = ds
        Else
            ' 連結時の列自動生成を無効
            FpSpread1.ActiveSheetView.AutoGenerateColumns = False
            FpSpread1.ActiveSheetView.ColumnCount = 15

            FpSpread1.ActiveSheetView.DataSource = ds

            FpSpread1.ActiveSheetView.Columns(0).DataField = "製品ID"
            FpSpread1.ActiveSheetView.Columns(1).DataField = "製品分類"
            FpSpread1.ActiveSheetView.Columns(2).DataField = "製品名"

            If dataflg = 1 Then
                FpSpread1.ActiveSheetView.Columns(3).DataField = "4月計画"
                FpSpread1.ActiveSheetView.Columns(4).DataField = "5月計画"
                FpSpread1.ActiveSheetView.Columns(5).DataField = "6月計画"
                FpSpread1.ActiveSheetView.Columns(6).DataField = "7月計画"
                FpSpread1.ActiveSheetView.Columns(7).DataField = "8月計画"
                FpSpread1.ActiveSheetView.Columns(8).DataField = "9月計画"
                FpSpread1.ActiveSheetView.Columns(9).DataField = "10月計画"
                FpSpread1.ActiveSheetView.Columns(10).DataField = "11月計画"
                FpSpread1.ActiveSheetView.Columns(11).DataField = "12月計画"
                FpSpread1.ActiveSheetView.Columns(12).DataField = "1月計画"
                FpSpread1.ActiveSheetView.Columns(13).DataField = "2月計画"
                FpSpread1.ActiveSheetView.Columns(14).DataField = "3月計画"
            Else
                FpSpread1.ActiveSheetView.Columns(3).DataField = "4月実績"
                FpSpread1.ActiveSheetView.Columns(4).DataField = "5月実績"
                FpSpread1.ActiveSheetView.Columns(5).DataField = "6月実績"
                FpSpread1.ActiveSheetView.Columns(6).DataField = "7月実績"
                FpSpread1.ActiveSheetView.Columns(7).DataField = "8月実績"
                FpSpread1.ActiveSheetView.Columns(8).DataField = "9月実績"
                FpSpread1.ActiveSheetView.Columns(9).DataField = "10月実績"
                FpSpread1.ActiveSheetView.Columns(10).DataField = "11月実績"
                FpSpread1.ActiveSheetView.Columns(11).DataField = "12月実績"
                FpSpread1.ActiveSheetView.Columns(12).DataField = "1月実績"
                FpSpread1.ActiveSheetView.Columns(13).DataField = "2月実績"
                FpSpread1.ActiveSheetView.Columns(14).DataField = "3月実績"
            End If
            FpSpread1.DataBind()
        End If
    End Sub

    Protected Sub FpSpread1_ButtonCommand(ByVal sender As Object, ByVal e As FarPoint.Web.Spread.SpreadCommandEventArgs) Handles FpSpread1.ButtonCommand
        If e.CommandName = "bindschedule" Then
            ' 計画データ連結
            BindSpread(1)

            Dim sheet As FarPoint.Web.Spread.SheetView = FpSpread1.Sheets(0)

            ' SPREAD初期化
            InitSpread(sheet)
        ElseIf e.CommandName = "bindresult" Then
            ' 実績データ連結
            BindSpread(2)

            Dim sheet As FarPoint.Web.Spread.SheetView = FpSpread1.Sheets(0)

            ' SPREAD初期化
            InitSpread(sheet)
        ElseIf e.CommandName = "bindall" Then
            ' 全データ連結
            BindSpread(0)

            Dim sheet As FarPoint.Web.Spread.SheetView = FpSpread1.Sheets(0)

            ' SPREAD初期化
            InitSpread(sheet)
        End If
    End Sub
End Class

<%@ Page MasterPageFile="~/MasterPage.master" Language="c#" AutoEventWireup="true" 
         Inherits="fieldbinding" CodeFile="fieldbinding.aspx.cs" %>

<%@ Register Assembly="FarPoint.Web.SpreadJ" Namespace="FarPoint.Web.Spread" TagPrefix="FarPoint" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
    <input id="Button1" type="button" value="計画データのみを連結" onclick="bindschedule();" style="width: 160px;" />
    <input id="Button2" type="button" value="実績データのみを連結" onclick="bindresult();" style="width: 160px;" />
    <input id="Button3" type="button" value="全データを連結" onclick="bindall();" style="width: 160px;" />
    <farpoint:FpSpread ID="FpSpread1" runat="server" 
        onbuttoncommand="FpSpread1_ButtonCommand">
        <CommandBar BackColor="Control" ButtonFaceColor="Control" ButtonHighlightColor="ControlLightLight"
            ButtonShadowColor="ControlDark" />
        <Sheets>
            <farpoint:SheetView SheetName="Sheet1">
            </farpoint:SheetView>
        </Sheets>
    </farpoint:FpSpread>
</asp:Content>

このページの先頭へ戻る