Excel操作（C#）

private void button1_Click(object sender, EventArgs e)


{

//１データごとの行数

int RowCount = 7;

int ColCount = 5;





int StartCol1 = 2;

int StartCol2 = ColCount + StartCol1;

int StartCol3 = ColCount + StartCol2;



Excel.Application oXls = new Excel.Application(); // Excelオブジェクト



string excelName = "C:\\Users\\x300\\sample.xls";



Excel.Workbook oWBook; // workbookオブジェクト

oXls.Visible = true;

// Excelファイルをオープンする

oWBook = (Excel.Workbook)(oXls.Workbooks.Open(

excelName, // オープンするExcelファイル名

Type.Missing, // （省略可能）UpdateLinks (0 / 1 / 2 / 3)

Type.Missing, // （省略可能）ReadOnly (True / False )

Type.Missing, // （省略可能）Format

// 1:タブ / 2:カンマ (,) / 3:スペース / 4:セミコロン (;)

// 5:なし / 6:引数 Delimiterで指定された文字

Type.Missing, // （省略可能）Password

Type.Missing, // （省略可能）WriteResPassword

Type.Missing, // （省略可能）IgnoreReadOnlyRecommended

Type.Missing, // （省略可能）Origin

Type.Missing, // （省略可能）Delimiter

Type.Missing, // （省略可能）Editable

Type.Missing, // （省略可能）Notify

Type.Missing, // （省略可能）Converter

Type.Missing, // （省略可能）AddToMru

Type.Missing, // （省略可能）Local

Type.Missing // （省略可能）CorruptLoad

));



// 与えられたワークシート名から、Worksheetオブジェクトを得る

string sheetName = "Sheet1";

Excel.Worksheet oSheet; // Worksheetオブジェクト

oSheet = (Excel.Worksheet)oWBook.Sheets[

getSheetIndex(sheetName, oWBook.Sheets)];



//データ取り出し（コピー）

for (int i = 0; i < 100; i++)

{

//何行目

int DataRowNum = (int)(i / 3);

//何列目

int DataColNum = (int)(i % 3);



Excel.Range range1;

Excel.Range range2;



//コピー元

range1 = (Excel.Range)oSheet.Cells[1, StartCol1];

range2 = (Excel.Range)oSheet.Cells[1 + RowCount - 1, StartCol1 + ColCount - 1];

range1 = oSheet.get_Range(range1, range2);

range1.Copy(Type.Missing);



int rowstart = 0;

int colstart = 0;

int rowend = 0;

int colend = 0;



//コピー先検索

switch (DataColNum)

{

case 0:

rowstart = DataRowNum * RowCount + 1;

colstart = StartCol1;

rowend = DataRowNum * RowCount + RowCount;

colend = StartCol1 + ColCount - 1;

break;

case 1:

rowstart = DataRowNum * RowCount + 1;

colstart = StartCol2;

rowend = DataRowNum * RowCount + RowCount;

colend = StartCol2 + ColCount - 1;

break;

case 2:

rowstart = DataRowNum * RowCount + 1;

colstart = StartCol3;

rowend = DataRowNum * RowCount + RowCount;

colend = StartCol3 + ColCount - 1;

break;

default:

break;

}



range1 = (Excel.Range)oSheet.Cells[rowstart, colstart];

range2 = (Excel.Range)oSheet.Cells[rowend, colend];

//ペースト

range1 = oSheet.get_Range(range1, range2);

range1.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);



//値を入れていく

int c1 = 0;

int r1 = 0;

//名前

((Excel.Range)oSheet.Cells[rowstart + r1, colstart + c1]).Value2 = "なまえ";

//年齢

c1 = 3;

r1 = 1;

((Excel.Range)oSheet.Cells[rowstart + r1, colstart + c1]).Value2 = rowstart + r1;

}



//oWBook.Close(Type.Missing, Type.Missing, Type.Missing);

//oXls.Quit();



}







// 指定されたワークシート名のインデックスを返すメソッド

private int getSheetIndex(string sheetName, Excel.Sheets shs)

{

int i = 0;

foreach (Excel.Worksheet sh in shs)

{

if (sheetName == sh.Name)

{

return i + 1;

}

i += 1;

}

return 0;

}

Smat-Tag-Panelを活用してコンポーネント開発

スマートタグパネルについて

How To Use SmartTagPanel In Component Designer when you use VS.NET

Imports System.ComponentModel




_

Public Class XTextBox

Inherits TextBox



'... customizations





End Class



Public Class mySmartTagActionList

Inherits SmartTagActionListBase



Private m_Control As XTextBox



Sub New(ByVal component As IComponent)

MyBase.New(component)

m_Control = CType(component, XTextBox)

End Sub



Public Property BackColor() As Color

Get

Return m_Control.BackColor

End Get

Set(ByVal value As Color)

Me.SetPropertyByName(m_Control, "BackColor", value)

End Set

End Property



Public Property ForeColor() As Color

Get

Return m_Control.ForeColor

End Get

Set(ByVal value As Color)

Me.SetPropertyByName(m_Control, "ForeColor", value)

End Set

End Property



Public Property IsMultiline() As Boolean

Get

Return m_Control.Multiline

End Get

Set(ByVal value As Boolean)

Me.SetPropertyByName(m_Control, "Multiline", value)

End Set

End Property



Public Sub SwapColors()

Dim c As Color = Me.ForeColor

Me.ForeColor = Me.BackColor

Me.BackColor = c

RefreshDesigner()

End Sub



Public Overrides Sub AddActionItems()

'These properties are already defined

'in base (SmartTagActionListBase) class:



' => Name, Text, Font, RightToLeft



'Other properties/Methods should be defined in current class



AddActionHeader("Main")

AddActionProperty("Name", "Name:", "Main", "")

AddActionProperty("Text", "Text:", "Main", "")

AddActionProperty("Font", "Font:", "Main", "")

AddActionProperty("IsMultiline", "Multiline:", "", "")

AddActionHeader("Colors")

AddActionProperty("ForeColor", "ForeColor:", _

"Colors", "Sets the ForeColor")

AddActionProperty("BackColor", "BackColor:", _

"Colors", "Sets the BackColor")

AddActionText("This is my info...", "Colors")

AddActionMethod("SwapColors", "Swap Colors", _

"Colors", "Swap ForeColor/BackColor", True)

End Sub



End Class