119
command.ExecuteNonQuery();
}
}
private DataTable LoadExcelSheetToTable(string
filename, string sheet)//загрузка в DataTable
{
DataTable table = new DataTable();
using (OleDbConnection co = new
OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; " + "data
source='" + filename + "';" + "Extended Properties=\"Excel
12.0;HDR=YES;IMEX=1\" "))
using (OleDbDataAdapter import = new
OleDbDataAdapter("select * from [" + sheet + "$]", co))
import.Fill(table);
return table;
}
private void Form1_Load(object sender, EventArgs
e)
{
// TODO: данная строка кода позволяет
загрузить данные в таблицу "databaseDataSet.DZ". При
необходимости она может быть перемещена или удалена.
this.dZTableAdapter.Fill(this.databaseDataSet.DZ);
string sqlpath = @"Data
Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Юлия\D
esktop\Диплом c#\DZ\DZ\Database.mdf;Integrated Security=True";
sqlConnection = new SqlConnection(sqlpath);
sqlConnection.Open();
fillcombobox(comboBoxClient1, new
SqlCommand("SELECT Client FROM DZ GROUP BY Client ORDER BY
Client", sqlConnection), "Client", 500, "Выберите клиента");
fillcombobox(comboBoxBlok, new
SqlCommand("SELECT ResponsibleBlock FROM DZ GROUP BY
120
ResponsibleBlock ORDER BY ResponsibleBlock", sqlConnection),
"ResponsibleBlock", 200, "Выбирете блок");
fillcombobox(comboBoxDepartment1, new
SqlCommand("SELECT ResponsibleDepartment FROM DZ GROUP BY
ResponsibleDepartment ORDER BY ResponsibleDepartment",
sqlConnection), "ResponsibleDepartment", 200, "Выбирете
отдел");
fillcombobox(comboBoxUnit1, new
SqlCommand("SELECT Unit FROM DZ GROUP BY Unit ORDER BY Unit",
sqlConnection), "Unit", 200, "Выбирете почтамт");
fillcomboboxMonth(comboBoxMonthToChart, new
SqlCommand("SELECT right(convert(varchar, Month, 106), 8) FROM
DZ GROUP BY Month ORDER BY Month", sqlConnection), "Month",
200, "Выбирете месяц");
fillcomboboxMonth(comboBoxMonthTop, new
SqlCommand("SELECT right(convert(varchar, Month, 106), 8) FROM
DZ GROUP BY Month ORDER BY Month", sqlConnection), "Month",
200, "Выбирете месяц");
fillcomboboxMonth(comboBoxMonth2, new
SqlCommand("SELECT right(convert(varchar, Month, 106), 8) FROM
DZ GROUP BY Month ORDER BY Month", sqlConnection), "Month",
200, "Выбирете месяц");
fillcombobox(comboBoxClientY, new
SqlCommand("SELECT Client FROM DZ GROUP BY Client ORDER BY
Client", sqlConnection), "Client", 500, "Выберите клиента");
fillcombobox(comboBoxBlockY, new
SqlCommand("SELECT ResponsibleBlock FROM DZ GROUP BY
ResponsibleBlock ORDER BY ResponsibleBlock", sqlConnection),
"ResponsibleBlock", 200, "Выбирете блок");
fillcombobox(comboBoxDepartmentY, new
SqlCommand("SELECT ResponsibleDepartment FROM DZ GROUP BY
ResponsibleDepartment ORDER BY ResponsibleDepartment",
sqlConnection), "ResponsibleDepartment", 200, "Выбирете
отдел");
121
fillcombobox(comboBoxUnitY, new
SqlCommand("SELECT Unit FROM DZ GROUP BY Unit ORDER BY Unit",
sqlConnection), "Unit", 200, "Выбирете почтамт");
fillcomboboxMonth(comboBoxMonthY, new
SqlCommand("SELECT right(convert(varchar, Month, 106), 8) FROM
DZ GROUP BY Month ORDER BY Month", sqlConnection), "Month",
200, "Выбирете месяц");
radioButtonUnit.Checked = true;
makeTop();
for (int i = 0; i <
checkedListBox1.Items.Count; i++)//при запуске, выбрать все
колонки
{
checkedListBox1.SetItemChecked(i, true);
}
dataGridView1.Columns[0].Visible = false;
dataGridView2.Columns[0].Visible = false;
}
private void fillcombobox(ComboBox cb, SqlCommand
command, string name_of_column_in_table, int DropDownWidth,
string cb_text) //метод для заполнения элемента ComboBox
{//заполнение combobox из БД
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = command;
da.Fill(ds, "DZ");
cb.DataSource = ds.Tables[0];
cb.DisplayMember = name_of_column_in_table;
cb.ValueMember = name_of_column_in_table;
cb.DropDownWidth = DropDownWidth;
cb.DropDownStyle = ComboBoxStyle.DropDownList;
cb.Text = cb_text;
}
122
private void fillcomboboxMonth(ComboBox cb,
SqlCommand command, string name_of_column_in_table, int
DropDownWidth, string cb_text) //метод для заполнения элемента
ComboBox
{
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = command;
da.Fill(ds, "DZ");
cb.DisplayMember = name_of_column_in_table;
cb.ValueMember = name_of_column_in_table;
//cb.DataSource = ds.Tables[0];//создать
string List<>?
List<string> items = new List<string>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
string item = dr.ItemArray[0].ToString();
items.Add(item);
}
cb.DataSource = items;
cb.DropDownWidth = DropDownWidth;
cb.DropDownStyle = ComboBoxStyle.DropDownList;
cb.Text = cb_text;
}
private void button2_Click(object sender,
EventArgs e)
{
SqlCommand command = new SqlCommand("TRUNCATE
TABLE DZ", sqlConnection);
command.ExecuteNonQuery();
MessageBox.Show("ok");
}
123
private void deleteEmpty(object sender, EventArgs
e)
{
SqlCommand command = new SqlCommand("DELETE
FROM DZ WHERE Client=''", sqlConnection);
command.ExecuteNonQuery();
//MessageBox.Show("ok");
}
public static void
SetComboBoxDropDownWidth(ComboBox comboBox, DataView
comboBoxSourceView, string visibleStringName)
{//устанавливаем ширину
if (comboBoxSourceView.Count == 0) { return; }
System.Drawing.Graphics graphics =
comboBox.CreateGraphics();
comboBox.DropDownWidth =
(from width in
(from DataRowView item in
comboBoxSourceView
select
Convert.ToInt32(graphics.MeasureString(item[visibleStringName]
.ToString(), comboBox.Font).Width))
select width).Max();
comboBox.DropDownWidth += 10; //иногда не
влезает последняя буква
}
private void comboBox1_SelectedIndexChanged(object
sender, EventArgs e)
{
string ClientName =
comboBoxClient1.SelectedValue.ToString();//получаем имя
выбранного клиента
SqlCommand command = new SqlCommand("SELECT "
+ columnName + " FROM DZ WHERE Client=N'" + ClientName + "'",
sqlConnection);//запрос в БД
124
command.ExecuteNonQuery();//выполняем команду
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = command;//устанавливаем
команду
da.Fill(ds, "DZ");
dataGridView1.DataSource =
ds.Tables[0];//выводим в DataGridView
tabControl1.SelectTab("Table");
double balans = 0;//переменная для поля Сумма
foreach (DataGridViewRow row in
dataGridView1.Rows)//суммируем все отображаемые строки
{
double incom;
double.TryParse(row.Cells[7].Value.ToString().Replace(".",
","), out incom);
balans += incom;
}
NumberFormatInfo nfi = new
NumberFormatInfo();//формат числа с пробелами и двумя знаками
после запятой
nfi.NumberGroupSeparator = " ";//разделитель
textBox1.Text = balans.ToString("N", nfi);
}
private void comboBoxY_SelectedIndexChanged(object
sender, EventArgs e)
{
string ClientName =
comboBoxClientY.SelectedValue.ToString();
SqlCommand command = new SqlCommand("SELECT "
+ columnName + " FROM DZ WHERE Client=N'" + ClientName + "'",
sqlConnection);
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
125
DataSet ds = new DataSet();
da.SelectCommand = command;
da.Fill(ds, "DZ");
dataGridView2.DataSource = ds.Tables[0];
double balans = 0;
foreach (DataGridViewRow row in
dataGridView2.Rows)
{
double incom;
double.TryParse(row.Cells[7].Value.ToString().Replace(".",
","), out incom);
balans += incom;
}
NumberFormatInfo nfi = new NumberFormatInfo();
//new CultureInfo("en-US",
false).NumberFormat;
nfi.NumberGroupSeparator = " ";
textBox1.Text = balans.ToString("N", nfi);
}
private void
comboBoxBlokY_SelectedIndexChanged(object sender, EventArgs e)
{
string BlockName =
comboBoxBlockY.SelectedValue.ToString();
SqlCommand command = new SqlCommand("SELECT "
+ columnName + " FROM DZ WHERE ResponsibleBlock=N'" +
BlockName + "'", sqlConnection);
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = command;
da.Fill(ds, "DZ");
dataGridView2.DataSource = ds.Tables[0];
double balans = 0;
126
foreach (DataGridViewRow row in
dataGridView2.Rows)
{
double incom;
double.TryParse(row.Cells[7].Value.ToString().Replace(".",
","), out incom);
balans += incom;
}
NumberFormatInfo nfi = new NumberFormatInfo();
//new CultureInfo("en-US",
false).NumberFormat;
nfi.NumberGroupSeparator = " ";
textBox1.Text = balans.ToString("N", nfi);
}
private void
comboBoxDepartmentY_SelectedIndexChanged(object sender,
EventArgs e)
{
string DepartmentName =
comboBoxDepartmentY.SelectedValue.ToString();
SqlCommand command = new SqlCommand("SELECT "
+ columnName + " FROM DZ WHERE ResponsibleDepartment=N'" +
DepartmentName + "'", sqlConnection);
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = command;
da.Fill(ds, "DZ");
dataGridView2.DataSource = ds.Tables[0];
}
private void
comboBoxUnitY_SelectedIndexChanged(object sender, EventArgs e)
{
127
string UnitName =
comboBoxUnitY.SelectedValue.ToString();
SqlCommand command = new SqlCommand("SELECT "
+ columnName + " FROM DZ WHERE Unit=N'" + UnitName + "'",
sqlConnection);
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = command;
da.Fill(ds, "DZ");
dataGridView2.DataSource = ds.Tables[0];
}
private void
comboBoxMonth2Y_SelectedIndexChanged(object sender, EventArgs
e)
{
string MonthName =
comboBoxMonthY.SelectedValue.ToString();
//SqlCommand command = new SqlCommand("SELECT
" + "Unit AS Почтамт, KAY AS 'Код аналитического учета',
Client AS Клиент, Contract AS Договор," +
// "NumberOfContract AS 'Номер
договора',ContractDate AS 'Дата договора', Sum AS Сумма, INN
AS ИНН," +
// "ResponsibleBlock AS 'Ответственный
блок', ResponsibleDepartment AS 'Ответственный отдел',
right(convert(varchar, Month, 106), 8) AS Месяц" + " FROM DZ
WHERE Month=right(convert(varchar, '" + MonthName + "', 106),
8)", sqlConnection);
SqlCommand command = new SqlCommand("SELECT "
+ columnName + " FROM DZ WHERE Month=right(convert(varchar, '"
+ MonthName + "', 106), 8)", sqlConnection);
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
128
da.SelectCommand = command;
da.Fill(ds, "DZ");
dataGridView2.DataSource = ds.Tables[0];
double balans = 0;
foreach (DataGridViewRow row in
dataGridView2.Rows)
{
double incom;
double.TryParse(row.Cells[7].Value.ToString().Replace(".",
","), out incom);
balans += incom;
}
NumberFormatInfo nfi = new NumberFormatInfo();
//new CultureInfo("en-US",
false).NumberFormat;
nfi.NumberGroupSeparator = " ";
textBox1.Text = balans.ToString("N", nfi);
}
private void
comboBoxBlok_SelectedIndexChanged(object sender, EventArgs e)
{
string BlockName =
comboBoxBlok.SelectedValue.ToString();
SqlCommand command = new SqlCommand("SELECT "
+ columnName + " FROM DZ WHERE ResponsibleBlock=N'" +
BlockName + "'", sqlConnection);
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = command;
da.Fill(ds, "DZ");
dataGridView1.DataSource = ds.Tables[0];
tabControl1.SelectTab("Table");