|
スパークライン
スパークラインは、セル内でデータを視覚的に表現する小さなチャートです。
セル内に縦棒/折れ線/勝敗のチャートを表示させ、隣接データの傾向を視覚的に表現することができます。
| A | B | 1月 | 2月 | 3月 | 4月 | 5月 | 6月 | 7月 | 8月 | 9月 | 10月 | 11月 | 12月 |
1 | 札幌 | | | | | | | | | | | | | |
2 | 平均気温( °C) | | -3.6 | -3.1 | 0.6 | 7.1 | 12.4 | 16.7 | 20.5 | 22.3 | 18.1 | 11.8 | 4.9 | -0.9 |
3 | 降水量(mm) | | 113.6 | 94 | 77.8 | 56.8 | 53.1 | 46.8 | 81 | 123.8 | 135.2 | 108.7 | 104.1 | 111.7 |
4 | 仙台 | | | | | | | | | | | | | |
5 | 平均気温( °C) | | 1.6 | 2 | 4.9 | 10.3 | 15 | 18.5 | 22.2 | 24.2 | 20.7 | 15.2 | 9.4 | 4.5 |
6 | 降水量(mm) | | 37 | 38.4 | 68.2 | 97.6 | 109.9 | 145.6 | 179.4 | 166.9 | 187.5 | 122 | 65.1 | 36.6 |
7 | 東京 | | | | | | | | | | | | | |
8 | 平均気温( °C) | | 6.1 | 6.5 | 9.4 | 14.6 | 18.9 | 22.1 | 25.8 | 27.4 | 23.8 | 18.5 | 13.3 | 8.7 |
9 | 降水量(mm) | | 52.3 | 56.1 | 117.5 | 124.5 | 137.8 | 167.7 | 153.5 | 168.2 | 209.9 | 197.8 | 92.5 | 51 |
10 | 名古屋 | | | | | | | | | | | | | |
11 | 平均気温( °C) | | 4.5 | 5.2 | 8.7 | 14.4 | 18.9 | 22.7 | 26.4 | 27.8 | 24.1 | 18.1 | 12.2 | 7 |
12 | 降水量(mm) | | 48.4 | 65.6 | 121.8 | 124.8 | 156.5 | 201 | 203.6 | 126.3 | 234.4 | 128.3 | 79.7 | 45 |
13 | 大阪 | | | | | | | | | | | | | |
14 | 平均気温( °C) | | 6 | 6.3 | 9.4 | 15.1 | 19.7 | 23.5 | 27.4 | 28.8 | 25 | 19 | 13.6 | 8.6 |
15 | 降水量(mm) | | 45.4 | 61.7 | 104.2 | 103.8 | 145.5 | 184.5 | 157 | 90.9 | 160.7 | 112.3 | 69.3 | 43.8 |
16 | 広島 | | | | | | | | | | | | | |
17 | 平均気温( °C) | | 5.2 | 6 | 9.1 | 14.7 | 19.3 | 23 | 27.1 | 28.2 | 24.4 | 18.3 | 12.5 | 7.5 |
18 | 降水量(mm) | | 44.6 | 66.6 | 123.9 | 141.7 | 177.6 | 247 | 258.6 | 110.8 | 169.5 | 87.9 | 68.2 | 41.2 |
19 | 福岡 | | | | | | | | | | | | | |
20 | 平均気温( °C) | | 6.6 | 7.4 | 10.4 | 15.1 | 19.4 | 23 | 27.2 | 28.1 | 24.4 | 19.2 | 13.8 | 8.9 |
21 | 降水量(mm) | | 68 | 71.5 | 112.5 | 116.6 | 142.5 | 254.8 | 277.9 | 172 | 178.4 | 73.7 | 84.8 | 59.8 |
22 | 那覇 | | | | | | | | | | | | | |
23 | 平均気温( °C) | | 17 | 17.1 | 18.9 | 21.4 | 24 | 26.8 | 28.9 | 28.7 | 27.6 | 25.2 | 22.1 | 18.7 |
24 | 降水量(mm) | | 107 | 119.7 | 161.4 | 165.7 | 231.6 | 247.2 | 141.4 | 240.5 | 260.5 | 152.9 | 110.2 | 102.8 |
|
|
ソースコード
別ウィンドウで表示
using System;
using System.Web.UI.WebControls;
public partial class style_sparkline : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
{
return;
}
// SPREADの設定
InitSpread(FpSpread1);
// シート設定
InitSheet(FpSpread1.Sheets[0]);
}
private void InitSpread(FarPoint.Web.Spread.FpSpread spread)
{
spread.CssClass = "spreadStyle";
spread.UseClipboard = false;
}
private void InitSheet(FarPoint.Web.Spread.SheetView sheet)
{
int row;
// フォントサイズの設定
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.DefaultStyle.VerticalAlign = VerticalAlign.Middle;
// 行列数の設定
sheet.RowCount = 24;
sheet.PageSize = 24;
sheet.ColumnCount = 14;
sheet.Columns[0].Width = 120;
sheet.Columns[1].Width = 160;
sheet.Columns[2, 13].Width = 40;
sheet.Columns[0].Font.Bold = true;
sheet.Columns[0].HorizontalAlign = HorizontalAlign.Center;
for (int i = 2; i < sheet.ColumnCount; i++)
{
sheet.ColumnHeader.Cells[0, i].Value = (i - 1).ToString() + "月";
}
sheet.SetValue(0, 0, "札幌");
sheet.SetClipValue(1, 0, 1, 14, "平均気温( °C)\t\t-3.6\t-3.1\t0.6\t7.1\t12.4\t16.7\t20.5\t22.3\t18.1\t11.8\t4.9\t-0.9");
sheet.SetClipValue(2, 0, 1, 14, "降水量(mm)\t\t113.6\t94\t77.8\t56.8\t53.1\t46.8\t81\t123.8\t135.2\t108.7\t104.1\t111.7");
sheet.SetValue(3, 0, "仙台");
sheet.SetClipValue(4, 0, 1, 14, "平均気温( °C)\t\t1.6\t2\t4.9\t10.3\t15\t18.5\t22.2\t24.2\t20.7\t15.2\t9.4\t4.5");
sheet.SetClipValue(5, 0, 1, 14, "降水量(mm)\t\t37\t38.4\t68.2\t97.6\t109.9\t145.6\t179.4\t166.9\t187.5\t122\t65.1\t36.6");
sheet.SetValue(6, 0, "東京");
sheet.SetClipValue(7, 0, 1, 14, "平均気温( °C)\t\t6.1\t6.5\t9.4\t14.6\t18.9\t22.1\t25.8\t27.4\t23.8\t18.5\t13.3\t8.7");
sheet.SetClipValue(8, 0, 1, 14, "降水量(mm)\t\t52.3\t56.1\t117.5\t124.5\t137.8\t167.7\t153.5\t168.2\t209.9\t197.8\t92.5\t51");
sheet.SetValue(9, 0, "名古屋");
sheet.SetClipValue(10, 0, 1, 14, "平均気温( °C)\t\t4.5\t5.2\t8.7\t14.4\t18.9\t22.7\t26.4\t27.8\t24.1\t18.1\t12.2\t7");
sheet.SetClipValue(11, 0, 1, 14, "降水量(mm)\t\t48.4\t65.6\t121.8\t124.8\t156.5\t201\t203.6\t126.3\t234.4\t128.3\t79.7\t45");
sheet.SetValue(12, 0, "大阪");
sheet.SetClipValue(13, 0, 1, 14, "平均気温( °C)\t\t6\t6.3\t9.4\t15.1\t19.7\t23.5\t27.4\t28.8\t25\t19\t13.6\t8.6");
sheet.SetClipValue(14, 0, 1, 14, "降水量(mm)\t\t45.4\t61.7\t104.2\t103.8\t145.5\t184.5\t157\t90.9\t160.7\t112.3\t69.3\t43.8");
sheet.SetValue(15, 0, "広島");
sheet.SetClipValue(16, 0, 1, 14, "平均気温( °C)\t\t5.2\t6\t9.1\t14.7\t19.3\t23\t27.1\t28.2\t24.4\t18.3\t12.5\t7.5");
sheet.SetClipValue(17, 0, 1, 14, "降水量(mm)\t\t44.6\t66.6\t123.9\t141.7\t177.6\t247\t258.6\t110.8\t169.5\t87.9\t68.2\t41.2");
sheet.SetValue(18, 0, "福岡");
sheet.SetClipValue(19, 0, 1, 14, "平均気温( °C)\t\t6.6\t7.4\t10.4\t15.1\t19.4\t23\t27.2\t28.1\t24.4\t19.2\t13.8\t8.9");
sheet.SetClipValue(20, 0, 1, 14, "降水量(mm)\t\t68\t71.5\t112.5\t116.6\t142.5\t254.8\t277.9\t172\t178.4\t73.7\t84.8\t59.8");
sheet.SetValue(21, 0, "那覇");
sheet.SetClipValue(22, 0, 1, 14, "平均気温( °C)\t\t17\t17.1\t18.9\t21.4\t24\t26.8\t28.9\t28.7\t27.6\t25.2\t22.1\t18.7");
sheet.SetClipValue(23, 0, 1, 14, "降水量(mm)\t\t107\t119.7\t161.4\t165.7\t231.6\t247.2\t141.4\t240.5\t260.5\t152.9\t110.2\t102.8");
row = 0;
while (row < sheet.RowCount)
{
if (row % 2 != 0)
{
sheet.Rows[row, row + 2].BackColor = System.Drawing.Color.FromArgb(240, 240, 240);
}
row += 3;
}
// スパークライン(折れ線)の設定
FarPoint.Web.Spread.ExcelSparklineGroup esg1 = new FarPoint.Web.Spread.ExcelSparklineGroup(new FarPoint.Web.Spread.ExcelSparklineSetting(), FarPoint.Web.Spread.SparklineType.Line);
FarPoint.Web.Spread.ExcelSparklineSetting ex1 = new FarPoint.Web.Spread.ExcelSparklineSetting();
ex1.ManualMax = 30;
ex1.ManualMin = -5;
ex1.SeriesColor = System.Drawing.Color.Green;
esg1.Setting = ex1;
row = 1;
while (row < FpSpread1.ActiveSheetView.RowCount)
{
FarPoint.Web.Spread.ExcelSparkline es1 = new FarPoint.Web.Spread.ExcelSparkline(row, 1, FpSpread1.ActiveSheetView, new FarPoint.Web.Spread.Model.CellRange(row, 2, 1, 12));
esg1.Add(es1);
row += 3;
}
sheet.SparklineContainer.Add(esg1);
// スパークライン(縦棒)の設定
FarPoint.Web.Spread.ExcelSparklineGroup esg2 = new FarPoint.Web.Spread.ExcelSparklineGroup(new FarPoint.Web.Spread.ExcelSparklineSetting(), FarPoint.Web.Spread.SparklineType.Column);
FarPoint.Web.Spread.ExcelSparklineSetting ex2 = new FarPoint.Web.Spread.ExcelSparklineSetting();
ex2.ManualMax = 300;
ex2.ManualMin = 0;
ex2.SeriesColor = System.Drawing.Color.CornflowerBlue;
esg2.Setting = ex2;
row = 2;
while (row < FpSpread1.ActiveSheetView.RowCount)
{
FarPoint.Web.Spread.ExcelSparkline es2 = new FarPoint.Web.Spread.ExcelSparkline(row, 1, FpSpread1.ActiveSheetView, new FarPoint.Web.Spread.Model.CellRange(row, 2, 1, 12));
esg2.Add(es2);
row += 3;
}
sheet.SparklineContainer.Add(esg2);
}
}
|
Partial Class style_sparkline
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)
' シート設定
InitSheet(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 InitSheet(ByVal sheet As FarPoint.Web.Spread.SheetView)
Dim row As Integer
' フォントサイズの設定
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.DefaultStyle.VerticalAlign = VerticalAlign.Middle
' 行列数の設定
sheet.RowCount = 24
sheet.PageSize = 24
sheet.ColumnCount = 14
sheet.Columns(0).Width = 120
sheet.Columns(1).Width = 160
sheet.Columns(2, 13).Width = 40
sheet.Columns(0).Font.Bold = True
sheet.Columns(0).HorizontalAlign = HorizontalAlign.Center
For i As Integer = 2 To sheet.ColumnCount - 1
sheet.ColumnHeader.Cells(0, i).Value = (i - 1).ToString() & "月"
Next
sheet.SetValue(0, 0, "札幌")
sheet.SetClipValue(1, 0, 1, 14, "平均気温( °C)" & vbTab & vbTab & "-3.6" & vbTab & "-3.1" & vbTab & "0.6" & vbTab & "7.1" & vbTab & "12.4" & vbTab & "16.7" & vbTab & "20.5" & vbTab & "22.3" & vbTab & "18.1" & vbTab & "11.8" & vbTab & "4.9" & vbTab & "-0.9")
sheet.SetClipValue(2, 0, 1, 14, "降水量(mm)" & vbTab & vbTab & "113.6" & vbTab & "94" & vbTab & "77.8" & vbTab & "56.8" & vbTab & "53.1" & vbTab & "46.8" & vbTab & "81" & vbTab & "123.8" & vbTab & "135.2" & vbTab & "108.7" & vbTab & "104.1" & vbTab & "111.7")
sheet.SetValue(3, 0, "仙台")
sheet.SetClipValue(4, 0, 1, 14, "平均気温( °C)" & vbTab & vbTab & "1.6" & vbTab & "2" & vbTab & "4.9" & vbTab & "10.3" & vbTab & "15" & vbTab & "18.5" & vbTab & "22.2" & vbTab & "24.2" & vbTab & "20.7" & vbTab & "15.2" & vbTab & "9.4" & vbTab & "4.5")
sheet.SetClipValue(5, 0, 1, 14, "降水量(mm)" & vbTab & vbTab & "37" & vbTab & "38.4" & vbTab & "68.2" & vbTab & "97.6" & vbTab & "109.9" & vbTab & "145.6" & vbTab & "179.4" & vbTab & "166.9" & vbTab & "187.5" & vbTab & "122" & vbTab & "65.1" & vbTab & "36.6")
sheet.SetValue(6, 0, "東京")
sheet.SetClipValue(7, 0, 1, 14, "平均気温( °C)" & vbTab & vbTab & "6.1" & vbTab & "6.5" & vbTab & "9.4" & vbTab & "14.6" & vbTab & "18.9" & vbTab & "22.1" & vbTab & "25.8" & vbTab & "27.4" & vbTab & "23.8" & vbTab & "18.5" & vbTab & "13.3" & vbTab & "8.7")
sheet.SetClipValue(8, 0, 1, 14, "降水量(mm)" & vbTab & vbTab & "52.3" & vbTab & "56.1" & vbTab & "117.5" & vbTab & "124.5" & vbTab & "137.8" & vbTab & "167.7" & vbTab & "153.5" & vbTab & "168.2" & vbTab & "209.9" & vbTab & "197.8" & vbTab & "92.5" & vbTab & "51")
sheet.SetValue(9, 0, "名古屋")
sheet.SetClipValue(10, 0, 1, 14, "平均気温( °C)" & vbTab & vbTab & "4.5" & vbTab & "5.2" & vbTab & "8.7" & vbTab & "14.4" & vbTab & "18.9" & vbTab & "22.7" & vbTab & "26.4" & vbTab & "27.8" & vbTab & "24.1" & vbTab & "18.1" & vbTab & "12.2" & vbTab & "7")
sheet.SetClipValue(11, 0, 1, 14, "降水量(mm)" & vbTab & vbTab & "48.4" & vbTab & "65.6" & vbTab & "121.8" & vbTab & "124.8" & vbTab & "156.5" & vbTab & "201" & vbTab & "203.6" & vbTab & "126.3" & vbTab & "234.4" & vbTab & "128.3" & vbTab & "79.7" & vbTab & "45")
sheet.SetValue(12, 0, "大阪")
sheet.SetClipValue(13, 0, 1, 14, "平均気温( °C)" & vbTab & vbTab & "6" & vbTab & "6.3" & vbTab & "9.4" & vbTab & "15.1" & vbTab & "19.7" & vbTab & "23.5" & vbTab & "27.4" & vbTab & "28.8" & vbTab & "25" & vbTab & "19" & vbTab & "13.6" & vbTab & "8.6")
sheet.SetClipValue(14, 0, 1, 14, "降水量(mm)" & vbTab & vbTab & "45.4" & vbTab & "61.7" & vbTab & "104.2" & vbTab & "103.8" & vbTab & "145.5" & vbTab & "184.5" & vbTab & "157" & vbTab & "90.9" & vbTab & "160.7" & vbTab & "112.3" & vbTab & "69.3" & vbTab & "43.8")
sheet.SetValue(15, 0, "広島")
sheet.SetClipValue(16, 0, 1, 14, "平均気温( °C)" & vbTab & vbTab & "5.2" & vbTab & "6" & vbTab & "9.1" & vbTab & "14.7" & vbTab & "19.3" & vbTab & "23" & vbTab & "27.1" & vbTab & "28.2" & vbTab & "24.4" & vbTab & "18.3" & vbTab & "12.5" & vbTab & "7.5")
sheet.SetClipValue(17, 0, 1, 14, "降水量(mm)" & vbTab & vbTab & "44.6" & vbTab & "66.6" & vbTab & "123.9" & vbTab & "141.7" & vbTab & "177.6" & vbTab & "247" & vbTab & "258.6" & vbTab & "110.8" & vbTab & "169.5" & vbTab & "87.9" & vbTab & "68.2" & vbTab & "41.2")
sheet.SetValue(18, 0, "福岡")
sheet.SetClipValue(19, 0, 1, 14, "平均気温( °C)" & vbTab & vbTab & "6.6" & vbTab & "7.4" & vbTab & "10.4" & vbTab & "15.1" & vbTab & "19.4" & vbTab & "23" & vbTab & "27.2" & vbTab & "28.1" & vbTab & "24.4" & vbTab & "19.2" & vbTab & "13.8" & vbTab & "8.9")
sheet.SetClipValue(20, 0, 1, 14, "降水量(mm)" & vbTab & vbTab & "68" & vbTab & "71.5" & vbTab & "112.5" & vbTab & "116.6" & vbTab & "142.5" & vbTab & "254.8" & vbTab & "277.9" & vbTab & "172" & vbTab & "178.4" & vbTab & "73.7" & vbTab & "84.8" & vbTab & "59.8")
sheet.SetValue(21, 0, "那覇")
sheet.SetClipValue(22, 0, 1, 14, "平均気温( °C)" & vbTab & vbTab & "17" & vbTab & "17.1" & vbTab & "18.9" & vbTab & "21.4" & vbTab & "24" & vbTab & "26.8" & vbTab & "28.9" & vbTab & "28.7" & vbTab & "27.6" & vbTab & "25.2" & vbTab & "22.1" & vbTab & "18.7")
sheet.SetClipValue(23, 0, 1, 14, "降水量(mm)" & vbTab & vbTab & "107" & vbTab & "119.7" & vbTab & "161.4" & vbTab & "165.7" & vbTab & "231.6" & vbTab & "247.2" & vbTab & "141.4" & vbTab & "240.5" & vbTab & "260.5" & vbTab & "152.9" & vbTab & "110.2" & vbTab & "102.8")
row = 0
While row < sheet.RowCount
If row Mod 2 <> 0 Then
sheet.Rows(row, row + 2).BackColor = System.Drawing.Color.FromArgb(240, 240, 240)
End If
row += 3
End While
' スパークライン(折れ線)の設定
Dim esg1 As New FarPoint.Web.Spread.ExcelSparklineGroup(New FarPoint.Web.Spread.ExcelSparklineSetting(), FarPoint.Web.Spread.SparklineType.Line)
Dim ex1 As New FarPoint.Web.Spread.ExcelSparklineSetting()
ex1.ManualMax = 30
ex1.ManualMin = -5
ex1.SeriesColor = System.Drawing.Color.Green
esg1.Setting = ex1
row = 1
While row < FpSpread1.ActiveSheetView.RowCount
Dim es1 As New FarPoint.Web.Spread.ExcelSparkline(row, 1, FpSpread1.ActiveSheetView, New FarPoint.Web.Spread.Model.CellRange(row, 2, 1, 12))
esg1.Add(es1)
row += 3
End While
sheet.SparklineContainer.Add(esg1)
' スパークライン(縦棒)の設定
Dim esg2 As New FarPoint.Web.Spread.ExcelSparklineGroup(New FarPoint.Web.Spread.ExcelSparklineSetting(), FarPoint.Web.Spread.SparklineType.Column)
Dim ex2 As New FarPoint.Web.Spread.ExcelSparklineSetting()
ex2.ManualMax = 300
ex2.ManualMin = 0
ex2.SeriesColor = System.Drawing.Color.CornflowerBlue
esg2.Setting = ex2
row = 2
While row < FpSpread1.ActiveSheetView.RowCount
Dim es2 As New FarPoint.Web.Spread.ExcelSparkline(row, 1, FpSpread1.ActiveSheetView, New FarPoint.Web.Spread.Model.CellRange(row, 2, 1, 12))
esg2.Add(es2)
row += 3
End While
sheet.SparklineContainer.Add(esg2)
End Sub
End Class
|
<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true"
CodeFile="sparkline.aspx.cs" Inherits="style_sparkline" %>
<%@ 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>
|
|