書式の自動適用

NumberFormatInfoやDateTimeFormatInfoを使用して、編集中セルの書式を設定することができます。 編集時と表示時でそれぞれ別の書式を設定することができ、セルの編集が開始した際に編集中セルの書式が適用され、 編集が終了した際に表示時のセルの書式が適用されます。 例えば、数値のみの入力を行い、編集を終了したら自動的にカンマ区切りの書式を適用することができます。
 セル型表示時の書式編集時の書式入力列
1通貨型セル通貨記号:\通貨記号:なし¥12,345
2日付時刻型セル書式:yyyy/MM/dd書式:yyyyMMdd2021/02/27
3倍精度型セル3桁区切りあり3桁区切りなし12,345.6
4整数型セル負数記号:▲負数記号:-▲12
   

ソースコード

別ウィンドウで表示
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

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

        // SPREADの設定
        InitSpread(FpSpread1);

        // シート設定
        InitSpreadStyles(FpSpread1.Sheets[0]);
    }

    private void InitSpread(FarPoint.Web.Spread.FpSpread spread)
    {        
        spread.CssClass = "spreadStyle";
        spread.UseClipboard = false;
    }

    private void InitSpreadStyles(FarPoint.Web.Spread.SheetView sheet)
    {
        sheet.ColumnCount = 4;
        sheet.RowCount = 4;

        sheet.ColumnHeader.Cells[0, 0].Value = "セル型";
        sheet.ColumnHeader.Cells[0, 1].Value = "表示時の書式";
        sheet.ColumnHeader.Cells[0, 2].Value = "編集時の書式";
        sheet.ColumnHeader.Cells[0, 3].Value = "入力列";

        // 通貨型セルの設定
        FarPoint.Web.Spread.CurrencyCellType cct = new FarPoint.Web.Spread.CurrencyCellType();
        cct.NumberFormat = (System.Globalization.NumberFormatInfo)System.Globalization.CultureInfo.CurrentCulture.NumberFormat.Clone();

        cct.EditMode.NumberFormat = (System.Globalization.NumberFormatInfo)System.Globalization.CultureInfo.CurrentCulture.NumberFormat.Clone();
        cct.EditMode.NumberFormat.CurrencySymbol = "";

        sheet.Cells[0, 0].Value = "通貨型セル";
        sheet.Cells[0, 1].Value = "通貨記号:\\";
        sheet.Cells[0, 2].Value = "通貨記号:なし";
        sheet.Cells[0, 3].CellType = cct;
        sheet.Cells[0, 3].Value = 12345;

        // 日付時刻型セルの設定
        FarPoint.Web.Spread.DateTimeCellType dct = new FarPoint.Web.Spread.DateTimeCellType();
        dct.DateTimeFormat = new System.Globalization.DateTimeFormatInfo();
        dct.FormatString = "yyyy/MM/dd";

        dct.EditMode.DateTimeFormat = new System.Globalization.DateTimeFormatInfo();
        dct.EditMode.FormatString = "yyyyMMdd";

        sheet.Cells[1, 0].Value = "日付時刻型セル";
        sheet.Cells[1, 1].Value = "書式:yyyy/MM/dd";
        sheet.Cells[1, 2].Value = "書式:yyyyMMdd";
        sheet.Cells[1, 3].CellType = dct;
        sheet.Cells[1, 3].Value = DateTime.Now;

        // 倍精度型セルの設定
        FarPoint.Web.Spread.DoubleCellType dbct = new FarPoint.Web.Spread.DoubleCellType();
        dbct.NumberFormat = new System.Globalization.NumberFormatInfo();
        dbct.NumberFormat.NumberDecimalSeparator = ".";

        dbct.EditMode.NumberFormat = new System.Globalization.NumberFormatInfo();        
        dbct.EditMode.NumberFormat.NumberGroupSizes = new int[] { 0 };        

        sheet.Cells[2, 0].Value = "倍精度型セル";
        sheet.Cells[2, 1].Value = "3桁区切りあり";
        sheet.Cells[2, 2].Value = "3桁区切りなし";
        sheet.Cells[2, 3].CellType = dbct;
        sheet.Cells[2, 3].Value = 12345.6d;

        // 整数型セルの設定
        FarPoint.Web.Spread.IntegerCellType ict = new FarPoint.Web.Spread.IntegerCellType();
        ict.NumberFormat = new System.Globalization.NumberFormatInfo();
        ict.NumberFormat.NegativeSign = "";

        ict.EditMode.NumberFormat = new System.Globalization.NumberFormatInfo();
        ict.EditMode.NumberFormat.NegativeSign = "-";

        sheet.Cells[3, 0].Value = "整数型セル";
        sheet.Cells[3, 1].Value = "負数記号:▲";
        sheet.Cells[3, 2].Value = "負数記号:-";
        sheet.Cells[3, 3].CellType = ict;
        sheet.Cells[3, 3].Value = -12;

        // フォントサイズの設定
        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.Columns[0].Width = 140;
        sheet.Columns[1].Width = 140;
        sheet.Columns[2].Width = 140;

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

        // セルのロックの設定
        sheet.Cells[0, 0].Locked = true;
        sheet.Cells[0, 1].Locked = true;
        sheet.Cells[0, 2].Locked = true;
        sheet.Cells[1, 0].Locked = true;
        sheet.Cells[1, 1].Locked = true;
        sheet.Cells[1, 2].Locked = true;
        sheet.Cells[2, 0].Locked = true;
        sheet.Cells[2, 1].Locked = true;
        sheet.Cells[2, 2].Locked = true;
        sheet.Cells[3, 0].Locked = true;
        sheet.Cells[3, 1].Locked = true;
        sheet.Cells[3, 2].Locked = true;
        sheet.LockBackColor = System.Drawing.Color.FromArgb(184, 184, 184);
    }
}
Imports System.Globalization
Partial Class style_editmodeformat
    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

        ' SPREADの設定
        InitSpread(FpSpread1)

        ' シート設定
        InitSpreadStyles(FpSpread1.Sheets(0))
    End Sub

    Private Sub InitSpread(ByVal spread As FarPoint.Web.Spread.FpSpread)
        spread.CssClass = "spreadStyle"
        spread.UseClipboard = False
    End Sub

    Private Sub InitSpreadStyles(ByVal sheet As FarPoint.Web.Spread.SheetView)
        sheet.ColumnCount = 4
        sheet.RowCount = 4

        sheet.ColumnHeader.Cells(0, 0).Value = "セル型"
        sheet.ColumnHeader.Cells(0, 1).Value = "表示時の書式"
        sheet.ColumnHeader.Cells(0, 2).Value = "編集時の書式"
        sheet.ColumnHeader.Cells(0, 3).Value = "入力列"

        ' 通貨型セルの設定
        Dim cct As New FarPoint.Web.Spread.CurrencyCellType()
        cct.NumberFormat = DirectCast(CultureInfo.CurrentCulture.NumberFormat.Clone(), NumberFormatInfo)

        cct.EditMode.NumberFormat = DirectCast(CultureInfo.CurrentCulture.NumberFormat.Clone(), NumberFormatInfo)
        cct.EditMode.NumberFormat.CurrencySymbol = ""

        sheet.Cells(0, 0).Value = "通貨型セル"
        sheet.Cells(0, 1).Value = "通貨記号:\"
        sheet.Cells(0, 2).Value = "通貨記号:なし"
        sheet.Cells(0, 3).CellType = cct
        sheet.Cells(0, 3).Value = 12345

        ' 日付時刻型セルの設定
        Dim dct As New FarPoint.Web.Spread.DateTimeCellType()
        dct.DateTimeFormat = New DateTimeFormatInfo()
        dct.FormatString = "yyyy/MM/dd"

        dct.EditMode.DateTimeFormat = New DateTimeFormatInfo()
        dct.EditMode.FormatString = "yyyyMMdd"

        sheet.Cells(1, 0).Value = "日付時刻型セル"
        sheet.Cells(1, 1).Value = "書式:yyyy/MM/dd"
        sheet.Cells(1, 2).Value = "書式:yyyyMMdd"
        sheet.Cells(1, 3).CellType = dct
        sheet.Cells(1, 3).Value = DateTime.Now

        ' 倍精度型セルの設定
        Dim dbct As New FarPoint.Web.Spread.DoubleCellType()
        dbct.NumberFormat = New NumberFormatInfo()
        dbct.NumberFormat.NumberDecimalSeparator = "."

        dbct.EditMode.NumberFormat = New NumberFormatInfo()
        dbct.EditMode.NumberFormat.NumberGroupSizes = New Integer() {0}

        sheet.Cells(2, 0).Value = "倍精度型セル"
        sheet.Cells(2, 1).Value = "3桁区切りあり"
        sheet.Cells(2, 2).Value = "3桁区切りなし"
        sheet.Cells(2, 3).CellType = dbct
        sheet.Cells(2, 3).Value = 12345.6

        ' 整数型セルの設定
        Dim ict As New FarPoint.Web.Spread.IntegerCellType()
        ict.NumberFormat = New NumberFormatInfo()
        ict.NumberFormat.NegativeSign = ""

        ict.EditMode.NumberFormat = New NumberFormatInfo()
        ict.EditMode.NumberFormat.NegativeSign = "-"

        sheet.Cells(3, 0).Value = "整数型セル"
        sheet.Cells(3, 1).Value = "負数記号:▲"
        sheet.Cells(3, 2).Value = "負数記号:-"
        sheet.Cells(3, 3).CellType = ict
        sheet.Cells(3, 3).Value = -12

        ' フォントサイズの設定
        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.Columns(0).Width = 140
        sheet.Columns(1).Width = 140
        sheet.Columns(2).Width = 140

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

        ' セルのロックの設定
        sheet.Cells(0, 0).Locked = True
        sheet.Cells(0, 1).Locked = True
        sheet.Cells(0, 2).Locked = True
        sheet.Cells(1, 0).Locked = True
        sheet.Cells(1, 1).Locked = True
        sheet.Cells(1, 2).Locked = True
        sheet.Cells(2, 0).Locked = True
        sheet.Cells(2, 1).Locked = True
        sheet.Cells(2, 2).Locked = True
        sheet.Cells(3, 0).Locked = True
        sheet.Cells(3, 1).Locked = True
        sheet.Cells(3, 2).Locked = True
        sheet.LockBackColor = System.Drawing.Color.FromArgb(184, 184, 184)
    End Sub
End Class
<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true"
  CodeFile="editmodeformat.aspx.cs" Inherits="edit_editmodeformat" %>

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

<asp:Content ID="Content1" ContentPlaceHolderID="HeaderPlaceHolder1" runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
    <FarPoint:FpSpread ID="FpSpread1" runat="server" BorderColor="#A0A0A0" BorderStyle="Solid"
        BorderWidth="1px">
        <CommandBar BackColor="#F6F6F6" ButtonFaceColor="Control" ButtonHighlightColor="ControlLightLight"
            ButtonShadowColor="ControlDark">
        </CommandBar>
        <Sheets>
            <FarPoint:SheetView SheetName="Sheet1">
            </FarPoint:SheetView>
        </Sheets>
    </FarPoint:FpSpread>
</asp:Content>