101
}
}
//получаем список меток с текущей датой и временем
foreach (BDDataSet.МеткиRow m in dictDatetime)
{
document.Bookmarks[m.Наименование].Select();
Microsoft.Office.Interop.Word.Selection currentSelection = ap.Selection;
if (m.КодВидаМетки == 3)
currentSelection.TypeText(DateTime.Now.ToShortDateString());
if (m.КодВидаМетки == 4)
currentSelection.TypeText(DateTime.Now.ToShortTimeString());
}
//колонтитулы
ap.ActiveDocument.Sections[1].Footers[Microsoft.Office.Interop.Word.WdHeaderFooterIndex.wd
HeaderFooterPrimary].Range.Text = textBox2.Text;
ap.ActiveDocument.Sections[1].Headers[Microsoft.Office.Interop.Word.WdHeaderFooterIndex.wd
HeaderFooterPrimary].Range.Text = textBox1.Text;
//ReleaseObject(ap);
ap.Visible = true;
return document;
}
catch (Exception ee)
{
MessageBox.Show(ee.Message, "Возникла ошибка", MessageBoxButtons.OK,
MessageBoxIcon.Error);
return null;
}
}
/// <summary>
/// Заполняем вордовский документ
/// </summary>
/// <param name="filename"></param>
/// <param name="sh"></param>
public string FillWordDocPacket(string filename, string filenameData)
{
Microsoft.Office.Interop.Excel.Application apData = new
Microsoft.Office.Interop.Excel.Application();
apData.Visible = false;
Microsoft.Office.Interop.Excel.Workbook bookData = null;
Microsoft.Office.Interop.Excel.Workbooks databook = apData.Workbooks;
apData.Workbooks.Open(filenameData,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
102
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
bookData = databook[1];
Microsoft.Office.Interop.Excel.Worksheet sheetData = bookData.Worksheets[1];
Microsoft.Office.Interop.Word.Application ap = new
Microsoft.Office.Interop.Word.Application();
ap.Visible = false;
string nameFold = "";
//создаем папку
nameFold = System.Windows.Forms.Application.StartupPath + @"\документы\" +
sh.Выражение1 + "_" + DateTime.Now.ToShortDateString().Replace(".", "-");
DirectoryInfo d;
if (!Directory.Exists(nameFold))
d = Directory.CreateDirectory(nameFold);
else
{
nameFold = System.Windows.Forms.Application.StartupPath + @"\документы\"
+ sh.Выражение1 + "_" + DateTime.Now.ToShortDateString().Replace(".", "-") + "-" +
DateTime.Now.ToShortTimeString().Replace(":", "-");
d = Directory.CreateDirectory(nameFold);
}
for (int j = 7; j <= sheetData.UsedRange.Rows.Count - 1; j++)//строки
{
if (sheetData.Cells[j, 1].value2 == null) continue; //нет порядковго номера
документа
string numberDoc = Convert.ToString(sheetData.Cells[j, 1].Value2);
//////////////новый документ
Document documentData = null;
documentData = ap.Documents.Open(filename);
//////////////
for (int i = 2; i <= sheetData.UsedRange.Columns.Count - 3; i++)//колонки
{
string nameCol = sheetData.Cells[5, i].Value2 == null ? "" :
Convert.ToString(sheetData.Cells[5, i].Value2);
if (nameCol == "") continue;
DataRow[] dr = bDDataSet1.Метки.Select("КодШаблона=" + sh.КодШаблона
+ " and Наименование='" + nameCol + "'");
if (dr == null || dr.Length == 0) continue;
BDDataSet.МеткиRow metka = ((BDDataSet.МеткиRow)dr[0]);
103
int vidMetka = metka.КодВидаМетки;
documentData.Bookmarks[metka.Наименование].Select();
Microsoft.Office.Interop.Word.Selection currentSelection = ap.Selection;
if (vidMetka == 1)
{
currentSelection.TypeText(sheetData.Cells[j, i].Value2 == null ? "" :
Convert.ToString(sheetData.Cells[j, i].Value2));
}
//текущая дата
if (vidMetka == 3)
currentSelection.TypeText(DateTime.Now.ToShortDateString());
//текущее время
if (vidMetka == 4)
currentSelection.TypeText(DateTime.Now.ToShortTimeString());
if (vidMetka == 2)
{
///вставляем таблицу
string[] s = metka.ЗаголовкиКолонок.Split('/');
//int row = b.RefersToRange.Row;
// int col = b.RefersToRange.Column;
int rowsCount = 0;
int colCount = s.Length;
for (int k = j + 1; k <= sheetData.UsedRange.Rows.Count - 1; k++)
{
if (sheetData.Cells[k, 1].Value2 != null)
{
rowsCount = (k - j) ;
break;
}
}
if (rowsCount == 0) rowsCount = sheetData.UsedRange.Rows.Count - j +1;
documentData.Bookmarks[metka.Наименование].Select();
Microsoft.Office.Interop.Word.Table tbl1 =
ap.Selection.Tables.Add(ap.Selection.Range, 1, colCount);
////заголовки колонок
for (int c = 0; c <= colCount - 1; c++)
{
tbl1.Cell(1, c + 1).Range.Text = s[c];
tbl1.Cell(1, c + 1).Range.Borders[WdBorderType.wdBorderLeft].LineStyle
= WdLineStyle.wdLineStyleSingle;
tbl1.Cell(1, c +
1).Range.Borders[WdBorderType.wdBorderRight].LineStyle = WdLineStyle.wdLineStyleSingle;
tbl1.Cell(1, c + 1).Range.Borders[WdBorderType.wdBorderTop].LineStyle
= WdLineStyle.wdLineStyleSingle;
104
tbl1.Cell(1, c +
1).Range.Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleSingle;
}
////строки таблицы
for (int r = 0; r <= rowsCount - 1; r++)
{
tbl1.Rows.Add();
for (int m = 0; m <= colCount - 1; m++)
{
tbl1.Cell(tbl1.Rows.Count, m + 1).Range.Text = sheetData.Cells[j + r, i
+ m].Value2 == null ? "" : Convert.ToString(sheetData.Cells[j + r, i + m].Value2);
tbl1.Cell(tbl1.Rows.Count, m +
1).Range.Borders[WdBorderType.wdBorderLeft].LineStyle = WdLineStyle.wdLineStyleSingle;
tbl1.Cell(tbl1.Rows.Count, m +
1).Range.Borders[WdBorderType.wdBorderRight].LineStyle = WdLineStyle.wdLineStyleSingle;
tbl1.Cell(tbl1.Rows.Count, m +
1).Range.Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleSingle;
tbl1.Cell(tbl1.Rows.Count, m +
1).Range.Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleSingle;
}
}
}
}
//сохраняем документ
//колонтитулы
ap.ActiveDocument.Sections[1].Footers[Microsoft.Office.Interop.Word.WdHeaderFooterIndex.wd
HeaderFooterPrimary].Range.Text = sheetData.Cells[2, 3].value2 == null ? "" : sheetData.Cells[2,
3].value2; ;
ap.ActiveDocument.Sections[1].Headers[Microsoft.Office.Interop.Word.WdHeaderFooterIndex.wd
HeaderFooterPrimary].Range.Text = sheetData.Cells[1, 3].value2 == null ? "" : sheetData.Cells[1,
3].value2; ;
//получаем список меток с текущей датой и временем
DataRow[] dr1 = bDDataSet1.Метки.Select("КодШаблона=" + sh.КодШаблона
+ " and (КодВидаМетки=3 or КодВидаМетки=4)");
foreach (BDDataSet.МеткиRow m in dr1)
{
documentData.Bookmarks[m.Наименование].Select();
Microsoft.Office.Interop.Word.Selection currentSelection = ap.Selection;
if (m.КодВидаМетки == 3)
currentSelection.TypeText(DateTime.Now.ToShortDateString());
105
if (m.КодВидаМетки == 4)
currentSelection.TypeText(DateTime.Now.ToShortTimeString());
}
documentData.SaveAs(d.FullName + @"\" + numberDoc + ".docx");
documentData.Close();
if(checkBox1.Checked && sheetData.Cells[j,
sheetData.UsedRange.Columns.Count - 2].Value2 != null )
{
string email = sheetData.Cells[j, sheetData.UsedRange.Columns.Count -
2].Value2 == null ? "" : Convert.ToString(sheetData.Cells[j, sheetData.UsedRange.Columns.Count -
2].Value2);
string tema = sheetData.Cells[j, sheetData.UsedRange.Columns.Count -
1].Value2 == null ? "" : Convert.ToString(sheetData.Cells[j, sheetData.UsedRange.Columns.Count -
1].Value2);
string text = sheetData.Cells[j, sheetData.UsedRange.Columns.Count].Value2
== null ? "" : Convert.ToString(sheetData.Cells[j, sheetData.UsedRange.Columns.Count ].Value2);
ClassEmail.SendMail("smtp.mail.ru", "test-diplom-2017@mail.ru", "test2017",
email,tema, text, d.FullName + @"\" + numberDoc + ".docx");
}
}
ap.Quit();
apData.Quit();
return d.FullName;
}
void ReleaseObject(object obj)
{
try
{
Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
MessageBox.Show(@"Unable to release the object " + ex);
obj = null;
}
finally
{
GC.Collect();
}
}
/// <summary>
106
/// Заполняем экселевский документ
/// </summary>
/// <param name="filename"></param>
/// <param name="sh"></param>
public Microsoft.Office.Interop.Excel.Worksheet FillExcelDoc(string filename,
BDDataSet.ШаблонСтипомRow sh)
{
Microsoft.Office.Interop.Excel.Application ap = new
Microsoft.Office.Interop.Excel.Application();
ap.Visible = false;
Microsoft.Office.Interop.Excel.Workbook book = null;
try
{
Microsoft.Office.Interop.Excel.Workbooks excelappworkbooks = ap.Workbooks;
ap.Workbooks.Open(filename,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
book = excelappworkbooks[1];
//получаем список меток документа
Microsoft.Office.Interop.Excel.Worksheet sheet = book.Worksheets[1];
this.меткиTableAdapter.Fill(this.bDDataSet1.Метки);
foreach (BDDataSet.МеткиRow m in dict.Keys)
{
//ищем нужную метку
foreach (Microsoft.Office.Interop.Excel.Name b in ap.Names)
{
if (b.Name.Equals(m.Наименование))
{
if (dict[m].GetType() == typeof(System.Windows.Forms.TextBox))
{
System.Windows.Forms.TextBox t =
(System.Windows.Forms.TextBox)dict[m];
b.RefersToRange.Value2 = t.Text;
}
else
{
////вставляем таблицу
DataGrid dgv = (DataGrid)dict[m];
System.Data.DataTable dt = (System.Data.DataTable)dgv.DataSource;
//Microsoft.Office.Interop.Word.Table tbl1 =
ap.Selection.Tables.Add(ap.Selection.Range, 1, dt.Columns.Count);
int row = b.RefersToRange.Row;
int col = b.RefersToRange.Column;
////заголовки колонок
107
for (int i = 0; i <= dt.Columns.Count - 1; i++)
{
Microsoft.Office.Interop.Excel.Range rng =
(Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, i + col];
rng.Value2 = dt.Columns[i].ColumnName;
rng.Borders[XlBordersIndex.xlInsideVertical].LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlDot;
rng.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlDot;
rng.Borders[XlBordersIndex.xlEdgeRight].LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlDot;
rng.Borders[XlBordersIndex.xlEdgeLeft].LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlDot;
}
////строки таблицы
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
//вставляем строку, так как под меткой таблицы ниже могут быть
данные и большая таблица может их перетересть
Microsoft.Office.Interop.Excel.Range Line =
(Microsoft.Office.Interop.Excel.Range)sheet.Rows[row + 2 + i];
Line.Insert();
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
Microsoft.Office.Interop.Excel.Range rng =
(Microsoft.Office.Interop.Excel.Range)sheet.Cells[row + i + 1, j + col];
rng.Value2 = dt.Rows[i][j] == null ? "" : dt.Rows[i][j].ToString(); ;
rng.Borders[XlBordersIndex.xlInsideVertical].LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlDot;
rng.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlDot;
rng.Borders[XlBordersIndex.xlEdgeRight].LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlDot;
rng.Borders[XlBordersIndex.xlEdgeLeft].LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlDot;
}
}
}
break;
}
}
}
ap.Visible = true;
//получаем список меток с текущей датой и временем
108
foreach (BDDataSet.МеткиRow m in dictDatetime)
{ //ищем нужную метку
foreach (Microsoft.Office.Interop.Excel.Name b in ap.Names)
{
if (b.Name.Equals(m.Наименование))
{
//текущая дата
if (m.КодВидаМетки == 3) b.RefersToRange.Value2 =
DateTime.Now.ToShortDateString();
//текущее время
if (m.КодВидаМетки == 4) b.RefersToRange.Value2 =
DateTime.Now.ToShortTimeString();
break;
}
}
}
//колонтитулы
sheet.PageSetup.CenterFooter = textBox2.Text;
sheet.PageSetup.CenterHeader = textBox1.Text;
ReleaseObject(ap);
return sheet;
}
catch (Exception ee)
{
MessageBox.Show(ee.Message, "Возникла ошибка", MessageBoxButtons.OK,
MessageBoxIcon.Error);
return null;
}
}
private void button2_Click(object sender, EventArgs e)
{
// получаем выбранный шаблон
BDDataSet.ШаблонСтипомRow sh =
(BDDataSet.ШаблонСтипомRow)((DataRowView)comboBox1.SelectedItem).Row;// ;
comboBox1.SelectedItem;
if (sh == null) return;
try
{
string filename = Path.GetTempFileName();
filename = Path.ChangeExtension(filename, (sh.КодТипа == 1 ? ".docx" :
".xlsx"));
109
using (Stream file = File.OpenWrite(filename))
{
file.Write(sh.Шаблон, 0, sh.Шаблон.Length);
file.Flush();
file.Close();
}
//вордовский шаблон
if (sh.КодТипа == 1)
{
FillWordDoc(filename, sh);
//Process.Start(filename);
}
//вордовский шаблон
if (sh.КодТипа == 2)
{
FillExcelDoc(filename, sh);
}
}
catch (Exception ee)
{
MessageBox.Show(ee.Message, "Возникла ошибка", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
private void button3_Click(object sender, EventArgs e)
{
// получаем выбранный шаблон
BDDataSet.ШаблонСтипомRow sh =
(BDDataSet.ШаблонСтипомRow)((DataRowView)comboBox1.SelectedItem).Row;// ;
comboBox1.SelectedItem;
if (sh == null) return;
try
{
string filename = Path.GetTempFileName();
filename = Path.ChangeExtension(filename, (sh.КодТипа == 1 ? ".docx" :
".xlsx"));
using (Stream file = File.OpenWrite(filename))
{
file.Write(sh.Шаблон, 0, sh.Шаблон.Length);
}
Process.Start(filename);
}
catch (Exception ee)
110
{
MessageBox.Show(ee.Message, "Возникла ошибка", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
private void button4_Click(object sender, EventArgs e)
{
// получаем выбранный шаблон
BDDataSet.ШаблонСтипомRow sh =
(BDDataSet.ШаблонСтипомRow)((DataRowView)comboBox1.SelectedItem).Row;// ;
comboBox1.SelectedItem;
if (sh == null) return;
FormMetki f = new FormMetki();
f.FillMetki(sh);
f.Show();
}
private void конвертацияToolStripMenuItem_Click(object sender, EventArgs e)
{
FormKonvert f = new FormKonvert();
f.Show();
}
private void выходToolStripMenuItem_Click(object sender, EventArgs e)
{
this.Close();
}
BDDataSet.ШаблонСтипомRow sh;
private void button5_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application ap = new
Microsoft.Office.Interop.Excel.Application();
ap.Visible = false;
Microsoft.Office.Interop.Excel.Workbook book = ap.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet sheet = book.Worksheets[1];
sheet.Cells[1, 2] = "Введите верхний колонтитул";
sheet.Cells[2, 2] = "Введите нижний колонтитул";
sheet.Columns[2].ColumnWidth = 31;
Microsoft.Office.Interop.Excel.Range rng = sheet.Range[sheet.Cells[1, 3],
sheet.Cells[1, 3]];
rng.Interior.ColorIndex = 33;
rng = sheet.Range[sheet.Cells[2,3], sheet.Cells[2, 3]];
rng.Interior.ColorIndex = 34;
rng = sheet.Range[sheet.Cells[1,1], sheet.Cells[1, 2]];
rng.Merge();
rng = sheet.Range[sheet.Cells[2, 1], sheet.Cells[2, 2]];
rng.Merge();