private void отобразитьВExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
ExcelClass InformationAboutElements = new ExcelClass();
Font HeadFont = new Font(" Times New Roman ", 12.0f, FontStyle.Bold);
try
{
int CurrentCell=0;
InformationAboutElements.NewDocument();
InformationAboutElements.AddNewPageAtTheStart("Параметры элемента УСП");
InformationAboutElements.SelectCells("A1", Type.Missing);
InformationAboutElements.SetColumnWidth(20);
InformationAboutElements.SetFont(HeadFont, 0);
InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick);
InformationAboutElements.WriteDataToCell("Параметр");
InformationAboutElements.SelectCells("B1", Type.Missing);
InformationAboutElements.SetColumnWidth(30);
InformationAboutElements.SetFont(HeadFont, 0);
InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick);
InformationAboutElements.WriteDataToCell("Значение");
for (int i = 0; i < DataInformTable.RowCount; i++)
{
CurrentCell = i + 2;
InformationAboutElements.SelectCells(("A" + CurrentCell.ToString()), Type.Missing);
InformationAboutElements.WriteDataToCell(DataInformTable[0,i].Value.ToString());
InformationAboutElements.SetHorisontalAlignment(2);
InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick);
InformationAboutElements.SelectCells(("B" + CurrentCell.ToString()), Type.Missing);
InformationAboutElements.WriteDataToCell(DataInformTable[1, i].Value.ToString());
InformationAboutElements.SetHorisontalAlignment(2);
InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Error");
}
finally {
InformationAboutElements.Visible = true;
InformationAboutElements.Dispose();
HeadFont.Dispose();
}
}
private void показатьВExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
ExcelClass InformationAboutElements = new ExcelClass();
Font HeadFont = new Font(" Times New Roman ", 12.0f, FontStyle.Bold);
try
{
string currentName = "";
int countCell = 2;
InformationAboutElements.NewDocument();
InformationAboutElements.AddNewPageAtTheStart("Параметры элемента УСП");
InformationAboutElements.SelectCells("A1", Type.Missing);
InformationAboutElements.SetColumnWidth(20);
InformationAboutElements.SetFont(HeadFont, 0);
InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick);
InformationAboutElements.WriteDataToCell("Параметр");
InformationAboutElements.SelectCells("B1", Type.Missing);
InformationAboutElements.SetColumnWidth(30);
InformationAboutElements.SetFont(HeadFont, 0);
InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick);
InformationAboutElements.WriteDataToCell("Значение");
for (int i = 1; i < 28; i++)
{
for (int j = 0; j < this.panel1.Controls.Count; j++)
{
if ((String.Compare(this.panel1.Controls[j].Tag.ToString(), i.ToString()) == 0))
{
if ((String.Compare(this.panel1.Controls[j].Text, "") != 0)&&(String.Compare(this.panel1.Controls[j].Text, "0") != 0))
{
currentName = ((System.Windows.Forms.Label)(this.Controls.Find(("label" + i.ToString()), true)[0])).Text;
InformationAboutElements.SelectCells((string)(("B" + countCell.ToString())), Type.Missing);
InformationAboutElements.WriteDataToCell(this.panel1.Controls[j].Text);
InformationAboutElements.SetHorisontalAlignment(2);
InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick);
InformationAboutElements.SelectCells((string)(("A" + countCell.ToString())), Type.Missing);
InformationAboutElements.WriteDataToCell(currentName);
InformationAboutElements.SetHorisontalAlignment(2);
InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick);
countCell++;
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
finally
{
InformationAboutElements.Visible = true;
InformationAboutElements.Dispose();
HeadFont.Dispose();
}
}
private void AddToExcel(string[,] texts, string excelPath, string excelName)
{
ExcelClass xls = new ExcelClass();
try
{
xls.NewDocument();
string fullPath = Path.Combine(excelPath, excelName);
xls.SaveDocument(fullPath);
xls.OpenDocument(fullPath, false);
for (int i = 0; i < texts.GetLength(0); i++)
{
for (int j = 0; j < texts.GetLength(1); j++)
{
xls.SetCellValue(j + 1, i + 1, texts[i, j].Trim());
}
}
xls.SetAutoFit("B:B");
xls.SetAutoFit("C:C");
xls.SetAutoFit("D:D");
xls.SetAutoFit("E:E");
xls.SetAutoFit("F:F");
}
finally
{
xls.CloseDocumentSave();
xls.Dispose();
}
}
void ExportDGVToExcel(DataGridView dgv)
{
ExcelClass InformationAboutElements = new ExcelClass();
Font HeadFont = new Font(" Times New Roman ", 12.0f, FontStyle.Bold);
int iterator = 0;
try
{
char[] alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
int CurrentCell = 0;
InformationAboutElements.NewDocument();
InformationAboutElements.AddNewPageAtTheStart("Данные");
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true)
{
InformationAboutElements.SelectCells(alpha[iterator] + (1).ToString(), Type.Missing);
InformationAboutElements.SetFont(HeadFont, 0);
InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin);
InformationAboutElements.WriteDataToCell(dgv.Columns[i].HeaderText);
for (int j = 0; j < dgv.Rows.Count; j++)
{
InformationAboutElements.SelectCells(alpha[iterator] + (j + 2).ToString(), Type.Missing);
InformationAboutElements.SetFont(HeadFont, 0);
InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin);
InformationAboutElements.setAutoFit(alpha[iterator] + (j + 2).ToString());
InformationAboutElements.WriteDataToCell(dgv[i, j].Value.ToString());
}
if (dgv[i, 0].Value.ToString().Length > dgv.Columns[i].HeaderText.Length)
{
InformationAboutElements.setAutoFit(alpha[iterator] + (2).ToString());
}
else
{
InformationAboutElements.setAutoFit(alpha[iterator] + (1).ToString());
}
iterator++;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
finally
{
InformationAboutElements.Visible = true;
InformationAboutElements.Dispose();
HeadFont.Dispose();
}
}
private void bExcelExportTo_Click(object sender, EventArgs e)
{
SaveFileDialog xlsF = new SaveFileDialog();
xlsF.Title = "Выберите файлы Excel с позициями";
xlsF.DefaultExt = "xlsx";
xlsF.Filter = "Файлы Excel (*.xls;*.xlsx)|*.xls;*.xlsx|All files (*.*)|*.*";
if (xlsF.ShowDialog() != DialogResult.OK)
return;
ExcelClass xls = new ExcelClass();
try
{
xls.NewDocument();
xls.SaveDocument(xlsF.FileName);
xls.OpenDocument(xlsF.FileName, false);
try
{
string workshop = tbWorkshop.Text;
List<string> tps = new List<string>();
Dictionary<string, string> param = new Dictionary<string, string>();
param.Add("T5_CE", workshop);
SqlOracle.Sel(
"select distinct t5_tp from table_5 where t5_ce = :T5_CE order by t5_tp",
param, out tps);
label16.Text = tps.Count.ToString();
int itp = 1;
int rowN = 2;
foreach (string tp in tps)
{
label10.Text = itp.ToString();
xls.SetCellValue("A", rowN, tp);
List<int> opers = new List<int>();
param = new Dictionary<string, string>();
param.Add("T5_TP", tp + "%");
SqlOracle.Sel(
"select distinct t5_no from table_5 where t5_tp like :T5_TP order by t5_no",
param, out opers);
label17.Text = opers.Count.ToString();
int ioper = 1;
foreach (int oper in opers)
{
label11.Text = ioper.ToString();
xls.SetCellValue("B", rowN, oper.ToString());
List<int> pers = new List<int>();
param = new Dictionary<string, string>();
param.Add("T5_TP", tp + "%");
param.Add("T5_NO", oper.ToString());
SqlOracle.Sel(
"select distinct t5_np from table_5 where t5_tp like :T5_TP and t5_no = :T5_NO order by t5_np",
param, out pers);
label18.Text = pers.Count.ToString();
int iper = 1;
foreach (int per in pers)
{
label12.Text = iper.ToString();
xls.SetCellValue("C", rowN, per.ToString());
if (oper == 35 && per == 30)
{
}
param = new Dictionary<string, string>();
param.Add("T5_TP", tp + "%");
param.Add("T5_NO", oper.ToString());
param.Add("T5_NP", per.ToString());
int btm;
SqlOracle.Sel(
"select distinct t5_bt from table_5 where t5_tp like :T5_TP and t5_no = :T5_NO and t5_np = :T5_NP",
param, out btm);
xls.SetCellValue("D", rowN, btm.ToString());
byte[] vo =
SqlOracle.GetBytes(
"select distinct t5_vo from table_5 where t5_tp like :T5_TP and t5_no = :T5_NO and t5_np = :T5_NP",
param);
if (vo.Length > 1)
{
for (int i = 0; i < vo.Length/2; i++)
{
int i1 = vo[i*2];
int i2 = vo[i*2 + 1];
if (i1 == 0 && i2 == 0)
{
continue;
}
string h1 = Convert.ToString(i1, 16);
string h2 = Convert.ToString(i2, 16);
if (h1.Length < 2)
{
h1 = "0" + h1;
}
string h = h2 + h1;
int nn = int.Parse(h, NumberStyles.AllowHexSpecifier);
param = new Dictionary<string, string>();
param.Add("T2", nn.ToString());
string pr;
SqlOracle.Sel("select t2_r1 from table_2 where t2_nn = :T2", param, out pr);
string name;
SqlOracle.Sel("select t2_nm from table_2 where t2_nn = :T2", param, out name);
string title;
SqlOracle.Sel("select t2_oboz from table_2 where t2_nn = :T2", param, out title);
int gr;
SqlOracle.Sel("select t2_ng from table_2 where t2_nn = :T2", param, out gr);
if (pr != null)
{
xls.SetCellValue("E", rowN, pr);
xls.SetCellValue("F", rowN, name);
xls.SetCellValue("G", rowN, title);
xls.SetCellValue("H", rowN, gr.ToString());
xls.SetCellValue("I", rowN, nn.ToString());
}
else
{
xls.SetCellValue("E", rowN, "Инструмент не найден в базе");
xls.SetCellValue("I", rowN, nn.ToString());
}
rowN++;
}
}
iper++;
Application.DoEvents();
rowN++;
}
ioper++;
rowN++;
}
itp++;
rowN++;
}
}
finally
{
xls.CloseDocumentSave();
}
}
finally
{
xls.Dispose();
}
}