129
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();
//new CultureInfo("en-US",
false).NumberFormat;
nfi.NumberGroupSeparator = " ";
textBox1.Text = balans.ToString("N", nfi);
}
private void
comboBoxDepartment_SelectedIndexChanged(object sender,
EventArgs e)
{
string DepartmentName =
comboBoxDepartment1.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");
dataGridView1.DataSource = ds.Tables[0];
tabControl1.SelectTab("Table");
double balans = 0;
foreach (DataGridViewRow row in
dataGridView1.Rows)
130
{
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
выгрузитьВExcelToolStripMenuItem_Click(object sender,
EventArgs e)//метод выгрузки таблицы в Excel
{
Microsoft.Office.Interop.Excel.Application
ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook
ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet
ExcelWorkSheet;
ExcelWorkBook =
ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);//Книг
а
ExcelWorkSheet =
(Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksh
eets.get_Item(1);//Таблица
for (int i = 0; i < dataGridView1.Rows.Count;
i++)// цикл для каждой строки
{
for (int j = 0; j <
dataGridView1.ColumnCount; j++)//для каждого столбца
131
{
ExcelApp.Cells[1, j + 1] =
dataGridView1.Columns[j].HeaderText;//копирем заголовок
таблиццы с полями
ExcelApp.Cells[i + 2, j + 1] =
dataGridView1.Rows[i].Cells[j].Value;//копируем значения ячеек
}
}
//защита книги на дипозоне А:М
(ExcelApp.ActiveWorkbook.Sheets[1]).EnableSelection =
Microsoft.Office.Interop.Excel.XlEnableSelection.xlUnlockedCel
ls;
string protectionPassword = "1234";//задаем
пароль
(ExcelApp.ActiveWorkbook.Sheets[1]).Protect(Password:
protectionPassword, AllowFormattingCells:
false);//устанавливаем пароль
(ExcelApp.ActiveWorkbook.Sheets[1]).Protection.IsProtected =
true;
ч(ExcelApp.ActiveWorkbook.Sheets[1]).ProtectedRanges.Add("edit
able", new
Microsoft.Office.Interop.Excel.ExcelAddress("A:M"));//защищаем
диапозон ячеек
ExcelApp.Visible = true;//Отображаем созданный
Excel файл
ExcelApp.UserControl = true;
}
132
private void
comboBoxUnit_SelectedIndexChanged(object sender, EventArgs e)
{
string UnitName =
comboBoxUnit1.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");
dataGridView1.DataSource = ds.Tables[0];
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();
//new CultureInfo("en-US",
false).NumberFormat;
nfi.NumberGroupSeparator = " ";
textBox1.Text = balans.ToString("N", nfi);
}
private void
comboBoxMonth_SelectedIndexChanged(object sender, EventArgs e)
{
133
string MonthName =
comboBoxMonthToChart.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();
da.SelectCommand = command;
da.Fill(ds, "DZ");
dataGridView1.DataSource = ds.Tables[0];
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();
//new CultureInfo("en-US",
false).NumberFormat;
nfi.NumberGroupSeparator = " ";
134
textBox1.Text = balans.ToString("N", nfi);
}
private void
comboBoxMonth2_SelectedIndexChanged(object sender, EventArgs
e)
{
string MonthName =
comboBoxMonth2.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();
da.SelectCommand = command;
da.Fill(ds, "DZ");
dataGridView1.DataSource = ds.Tables[0];
double balans = 0;
foreach (DataGridViewRow row in
dataGridView1.Rows)
{
double incom;
double.TryParse(row.Cells[7].Value.ToString().Replace(".",
","), out incom);
135
balans += incom;
}
NumberFormatInfo nfi = new NumberFormatInfo();
//new CultureInfo("en-US",
false).NumberFormat;
nfi.NumberGroupSeparator = " ";
textBox1.Text = balans.ToString("N", nfi);
}
private void
comboBoxMonthTop_SelectedIndexChanged(object sender, EventArgs
e)
{
string MonthName =
comboBoxMonthTop.SelectedValue.ToString();
SqlCommand command = new SqlCommand("SELECT
TOP 10 " + columnName + " FROM DZ WHERE
Month=right(convert(varchar, '" + MonthName + "', 106), 8)
ORDER BY Sum DESC", sqlConnection);
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = command;
da.Fill(ds, "DZ");
dataGridViewTOP.DataSource = ds.Tables[0];
makeTopgraf();
}
private void makeTop()
{
string MonthName =
comboBoxMonthTop.SelectedValue.ToString();//получаем выбранный
месяц
SqlCommand command = new SqlCommand("SELECT
TOP 10 " + columnName + " FROM DZ WHERE
136
Month=right(convert(varchar, '" + MonthName + "', 106), 8)
ORDER BY Sum DESC", sqlConnection);//создаем запрос
command.ExecuteNonQuery();//выполняем запрос
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = command;//устанавливаем
команду для SqlDataAdapter
da.Fill(ds, "DZ");//заполняем DataSet
dataGridViewTOP.DataSource =
ds.Tables[0];//выводим в таблицу
makeTopgraf();//строим диаграмму
}
private void makeTopgraf()
{
chartTop.DataSource = null;//очистка графика
chartTop.Series.Clear();
chartTop.DataBind();
chartTop.Series.Add("Series1");
string MonthName =
comboBoxMonthTop.SelectedValue.ToString();//получаем месяц
SqlCommand command1 = new SqlCommand("SELECT
TOP 10 Client AS Клиент, Sum AS Сумма FROM DZ WHERE
Month=right(convert(varchar, '" + MonthName + "', 106), 8)
ORDER BY Sum DESC", sqlConnection);//создаем запрос
SqlDataAdapter da1 = new SqlDataAdapter();
DataSet ds1 = new DataSet();
da1.SelectCommand = command1;
da1.Fill(ds1, "DZ");
List<string> axisLabel = new List<string>();
for (int pointIndex = 0; pointIndex <
ds1.Tables[0].Rows.Count; pointIndex++)//для каждого столбца
на диаграмме
137
{
chartTop.Series["Series1"].Points.AddY(ds1.Tables[0].Rows[poin
tIndex][1]);//присваиваем зачение
chartTop.ChartAreas[0].AxisX.Title =
"Месяц";//добавляем название осей
chartTop.ChartAreas[0].AxisY.Title =
"Сумма";
chartTop.Series["Series1"].Points[pointIndex].AxisLabel =
ds1.Tables[0].Rows[pointIndex][0].ToString();//добавляем
названия месяцев
chartTop.ChartAreas["Default"].AxisX.IsMarginVisible =
false;//делаем, чтобы диаграмма не начиналаь с нуля
}
UpdateChartSettings();//обновляем диаграмму
}
private void buildChartBtn_Click(object sender,
EventArgs e)//построение круговой диаграммы
{
Chart1.DataSource = null;//очистка графика
Chart1.Series.Clear();
Chart1.DataBind();
SqlCommand command = new SqlCommand();
if (radioButtonUnit.Checked)//если выбрано по
почтамтам
{
command = new SqlCommand("SELECT
CONCAT(сумма,' ', Почтамт) AS Почтамт, Summa AS Сумма FROM
(SELECT Unit AS Почтамт, Replace(Format(SUM(Sum), 'N'),',','
') AS Сумма, SUM(Sum) AS Summa FROM DZ WHERE Month='" +
comboBoxMonthToChart.SelectedValue.ToString() + "' GROUP BY
Unit) AS th", sqlConnection);
138
SqlDataReader dr =
command.ExecuteReader();
Chart1.DataBindTable(dr, "Почтамт");
Chart1.Series[0].ChartType =
SeriesChartType.Pie;//выбираем тип диаграммы
Chart1.Series[0].IsVisibleInLegend =
false;//легенда отключена
Chart1.Series[0]["PieLabelStyle"] =
"Outside";//стиль подписи частей дигараммы
Chart1.ChartAreas[0].Area3DStyle.Enable3D
= true;//3D вид диаграммы
Chart1.Series[0]["PieDrawingStyle"] =
"SoftEdge";//стиль рисования диаграммы
dr.Close();
}
else if (radioButtonKind.Checked)//если
выбрано по видам
{
command = new SqlCommand("SELECT
CONCAT(сумма,' ', Вид) AS Вид, Summa AS Сумма FROM (SELECT
KindOfDZ AS Вид, Replace(Format(SUM(Sum), 'N'),',',' ') AS
Сумма, SUM(Sum) AS Summa FROM DZ WHERE Month='" +
comboBoxMonthToChart.SelectedValue.ToString() + "' GROUP BY
KindOfDZ) AS th", sqlConnection);
SqlDataReader dr =
command.ExecuteReader();
Chart1.DataBindTable(dr, "Вид");
Chart1.Series[0].ChartType =
SeriesChartType.Pie;
Chart1.Series[0].IsVisibleInLegend =
false;
Chart1.Series[0]["PieLabelStyle"] =
"Outside";
Chart1.ChartAreas[0].Area3DStyle.Enable3D
= true;