using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Reflection;
namespace Cat_s_Hand
{
public class FrmMeibo : System.Windows.Forms.Form
{
//CheckBoxの配列を作成
private System.Windows.Forms.CheckBox[] arrayCBox
= new System.Windows.Forms.CheckBox[10];
/*==========Grid2Excel表示用変数=======================*/
public string[] strExcelHeader =
{"番号", "年", "組", "No", "氏名", "フリガナ", "性"};
public int ExcelColNum = 7;
/*=====================================================*/
private void GridWidth()
{
int iCnt, PlusScroll = 17, PlusWidth = 40;
if(checkBox1.Checked) PlusWidth += 34;
if(checkBox2.Checked) PlusWidth += 18;
if(checkBox3.Checked) PlusWidth += 18;
if(checkBox4.Checked) PlusWidth += 22;
if(checkBox5.Checked) PlusWidth += 90;
if(checkBox6.Checked) PlusWidth += 90;
if(checkBox7.Checked) PlusWidth += 22;
iCnt = dataGrid1.BindingContext[dsMeibo1, "生徒名簿"].Count;
if(iCnt > 33)
{
PlusScroll = 17;
dataGrid1.Height = 636;
}
else
{
PlusScroll = 0;
dataGrid1.Height = 24 + 18 * (iCnt+1);
}
dataGrid1.Width = PlusWidth + PlusScroll;
}
private void DataRead()
{
string Nen, Kumi;
Nen = cmbNen.Text.Substring(0,1);
Kumi = cmbKumi.Text.Substring(0,1);
dsMeibo1.Clear();
odaMeibo.SelectCommand.Parameters["年"].Value = Nen;
odaMeibo.SelectCommand.Parameters["組"].Value = Kumi;
odaMeibo.Fill(dsMeibo1, "生徒名簿");
GridWidth();
}
private void arrayCBox_ChekedChanged(object sender, EventArgs e)
{
string str;
str = ((System.Windows.Forms.CheckBox) sender).Text;
switch(str)
{
case "番号" : str="生徒No"; break;
case "年" : str="年"; break;
case "組" : str="組"; break;
case "No" : str="番号"; break;
case "氏名" : str="氏名"; break;
case "フリガナ": str="フリガナ"; break;
case "性" : str="性別"; break;
}
CBoxText = str;
SQL_String();
}
private void FrmMeibo_Load(object sender, System.EventArgs e)
{
//チェックボックスコントロールの配列に代入
arrayCBox[1] = checkBox1;
arrayCBox[2] = checkBox2;
arrayCBox[3] = checkBox3;
arrayCBox[4] = checkBox4;
arrayCBox[5] = checkBox5;
arrayCBox[6] = checkBox6;
arrayCBox[7] = checkBox7;
for(int i=1; i<8; i++)
arrayCBox[i].CheckedChanged +=
new EventHandler(arrayCBox_ChekedChanged);
DataRead();
}
private void button1_Click(object sender, System.EventArgs e)
{
this.Close();
}
private void SQL_String()
{
string[] ColName;
string Select = "";
//forExcel
ExcelColNum = 0;
dataGrid1.TableStyles.Clear();
DataGridTableStyle tsMeibo = new DataGridTableStyle();
tsMeibo.MappingName = "生徒名簿";
ColName = new string[8];
if(checkBox1.Checked)
{
ColName[1] = ",生徒No ";
ExcelColNum++;
strExcelHeader[ExcelColNum-1] = "番号";
DataGridColumnStyle TextCol11 = new DataGridTextBoxColumn();
TextCol11.MappingName = "生徒No";
TextCol11.HeaderText = "番号";
TextCol11.Width = 34;
TextCol11.Alignment = HorizontalAlignment.Center;
TextCol11.NullText = "";
tsMeibo.GridColumnStyles.Add(TextCol11);
}
else
ColName[1] = "";
if(checkBox2.Checked)
{
ColName[2] = ",年 ";
ExcelColNum++;
strExcelHeader[ExcelColNum-1] = "年";
DataGridColumnStyle TextCol12 = new DataGridTextBoxColumn();
TextCol12.MappingName = "年";
TextCol12.HeaderText = "年";
TextCol12.Width = 18;
TextCol12.Alignment = HorizontalAlignment.Center;
TextCol12.NullText = "";
tsMeibo.GridColumnStyles.Add(TextCol12);
}
else
ColName[2] = "";
if(checkBox3.Checked)
{
ColName[3] = ",組 ";
ExcelColNum++;
strExcelHeader[ExcelColNum-1] = "組";
DataGridColumnStyle TextCol13 = new DataGridTextBoxColumn();
TextCol13.MappingName = "組";
TextCol13.HeaderText = "組";
TextCol13.Width = 18;
TextCol13.Alignment = HorizontalAlignment.Center;
TextCol13.NullText = "";
tsMeibo.GridColumnStyles.Add(TextCol13);
}
else
ColName[3] = "";
if(checkBox4.Checked)
{
ColName[4] = ",番号 ";
ExcelColNum++;
strExcelHeader[ExcelColNum-1] = "No";
DataGridColumnStyle TextCol14 = new DataGridTextBoxColumn();
TextCol14.MappingName = "番号";
TextCol14.HeaderText = "No";
TextCol14.Width = 22;
TextCol14.Alignment = HorizontalAlignment.Center;
TextCol14.NullText = "";
tsMeibo.GridColumnStyles.Add(TextCol14);
}
else
ColName[4] = "";
if(checkBox5.Checked)
{
ColName[5] = ",氏名 ";
ExcelColNum++;
strExcelHeader[ExcelColNum-1] = " 氏 名";
DataGridColumnStyle TextCol15 = new DataGridTextBoxColumn();
TextCol15.MappingName = "氏名";
TextCol15.HeaderText = " 氏 名";
TextCol15.Width = 90;
TextCol15.Alignment = HorizontalAlignment.Left;
TextCol15.NullText = "";
tsMeibo.GridColumnStyles.Add(TextCol15);
}
else
ColName[5] = "";
if(checkBox6.Checked)
{
ColName[6] = ",[フリガナ] ";
ExcelColNum++;
strExcelHeader[ExcelColNum-1] = "フリガナ";
DataGridColumnStyle TextCol16 = new DataGridTextBoxColumn();
TextCol16.MappingName = "フリガナ";
TextCol16.HeaderText = "フリガナ";
TextCol16.Width = 90;
TextCol16.Alignment = HorizontalAlignment.Left;
TextCol16.NullText = "";
tsMeibo.GridColumnStyles.Add(TextCol16);
}
else
ColName[6] = "";
if(checkBox7.Checked)
{
ColName[7] = ",性別";
ExcelColNum++;
strExcelHeader[ExcelColNum-1] = "性";
DataGridColumnStyle TextCol17 = new DataGridTextBoxColumn();
TextCol17.MappingName = "性別";
TextCol17.HeaderText = "性";
TextCol17.Width = 22;
TextCol17.Alignment = HorizontalAlignment.Center;
TextCol17.NullText = "";
tsMeibo.GridColumnStyles.Add(TextCol17);
}
else
ColName[7] = "";
dataGrid1.TableStyles.Add(tsMeibo);
for(int i=1; i<8; i++)
{
Select += ColName[i];
}
this.oleDbSelectCommand1.CommandText =
"SELECT 生徒ID" + Select + " FROM 生徒名簿 WHERE (年 = ?)
AND (組 = ?) ORDER BY 生徒No";
DataRead();
}
private void cmbNen_TextChanged(object sender, System.EventArgs e)
{
DataRead();
}
private void cmbKumi_TextChanged(object sender, System.EventArgs e)
{
DataRead();
}
/*========== Excelで印刷 ==================================*/
private string[] GetHeaderArray()
{
string[] ret = new string[ExcelColNum];
for(int i=0; i<ExcelColNum; i++)
{
ret[i] = strExcelHeader[i];
}
return ret;
}
private string[,] GetDataArray(int irow)
{
int iRow = irow;
int iCol = ExcelColNum;
string[,] ret = new string[iRow,iCol];
for(int i=0; i<irow; i++)
{
for(int j=0; j<iCol; j++)
{
ret[i,j] = dataGrid1[i,j].ToString();
}
}
return ret;
}
private void cmbPrint_TextChanged(object sender, System.EventArgs e)
{
if(cmbPrint.Text == "Excel")
{
Excel.Application exApp;
Excel._Workbook wkBook;
Excel.Workbooks wkBooks;
Excel.Sheets wkSheets;
Excel._Worksheet wkSheet;
Excel.Range range;
try
{
exApp = new Excel.Application();
wkBooks = exApp.Workbooks;
wkBook = wkBooks.Add(Missing.Value);
wkSheets = wkBook.Worksheets;
wkSheet = (Excel._Worksheet)wkSheets.get_Item(1);
string[] Headstr = GetHeaderArray();
for(int i=0; i<Headstr.Length; i++)
{
wkSheet.Cells[1,i+1] = Headstr[i];
}
range = wkSheet.get_Range("A2", Missing.Value);
int iCnt =
dataGrid1.BindingContext[dsMeibo1, "生徒名簿"].Count;
string[,] dataValue = GetDataArray(iCnt);
int r = dataValue.GetLength(0);
int c = dataValue.GetLength(1);
range = range.get_Resize(r,c);
//Excel2002
//range.set_Value(Missing.Value, dataValue);
//Excel2000
try
{
range.Value = dataValue;
}
catch(Exception ex)
{
MessageBox.Show("Excel2000で印刷します。"Using Excel",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
exApp.Visible = true;
exApp.UserControl = true;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
}
}
}
}
|