private void PopulateClientTemplate(KeyValuePair <string, string> templateNameAndfileSaveName)
{
toolStripStatusLabel1.Text = "Creating " + templateNameAndfileSaveName.Value;
object oMissing = Missing.Value;
string currentDirectory = Directory.GetCurrentDirectory();
object oPathToTemplate = currentDirectory + "\\" + templateNameAndfileSaveName.Key;
string absolutePathToSave = Path.GetFullPath(FullPathToClientDirectory);
if (!File.Exists(oPathToTemplate.ToString()))
{
MessageBox.Show($"Cannot find template: {templateNameAndfileSaveName.Key}.\nPlease fill out remaining templates manually.", "Error", MessageBoxButtons.OKCancel);
}
Word._Application wordApplication = new Word.Application();
Word._Document currentDocument = wordApplication.Documents.Add(ref oPathToTemplate, ref oMissing, ref oMissing, ref oMissing);
wordApplication.Visible = false;
ReplaceBookmarksWithFormTextIn(currentDocument);
currentDocument.Repaginate();
currentDocument.Fields.Update();
currentDocument.SaveAs2(Path.Combine(absolutePathToSave, templateNameAndfileSaveName.Value));
currentDocument.Close();
wordApplication.Quit();
}
private void createDOCX(String tempfilename, String newPath, Random random)
{
//MessageBox.Show("DOCX");
string newFileName = tempfilename + ".docx";
newPath = System.IO.Path.Combine(newPath, newFileName);
int sizeofwrite = random.Next(1, 10000);
if (!System.IO.File.Exists(newPath))
{
StringBuilder sb = new StringBuilder();
object fileName = newPath;
object missing = System.Reflection.Missing.Value;
object endOfDoc = "\\endofdoc"; /* \endofdoc is a predefined bookmark */
//Start Word and create a new document.
Word._Application word = new Word.Application();
Word._Document document = word.Documents.Add(ref missing, ref missing, ref missing, ref missing);
for (int x = 0; x < sizeofwrite; x++)
{
Byte[] b = new Byte[1];
random.NextBytes(b);
sb.AppendLine(b[0].ToString());
}
word.Selection.TypeText(sb.ToString());
/*
* //Insert a paragraph at the beginning of the document.
* Word.Paragraph para1;
* para1 = document.Content.Paragraphs.Add(ref missing);
* para1.Range.Text = "Adding paragraph thru C#";
* para1.Range.Font.Bold = 1;
* para1.Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;
* para1.Format.SpaceAfter = 24; //24 pt spacing after paragraph.
* para1.Range.InsertParagraphAfter();
*/
//word.Visible = true;
document.SaveAs2(ref fileName, ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing);
document.Close(ref missing, ref missing, ref missing);
word.Quit(ref missing, ref missing, ref missing);
textBox4.AppendText("Created file: " + newPath.ToString() + System.Environment.NewLine + "SIZE: " + sizeofwrite.ToString() + "\r\n\r\n");
}
}
public static void ConvertToPdf(string input, string filename, string output)
{
WdSaveFormat format = WdSaveFormat.wdFormatPDF;
Microsoft.Office.Interop.Word._Application oWord = new Microsoft.Office.Interop.Word.Application();
Microsoft.Office.Interop.Word._Document oDoc = null;
object oMissing = System.Reflection.Missing.Value;
try
{
oWord.Visible = false;
oWord.ScreenUpdating = false;
object isVisible = true;
object readOnly = true;
object confirmConversions = false;
object addToRecentFiles = false;
object passwordDocument = "12345";
object oInput = input;
object oOutput = output;
object oFormat = WdOpenFormat.wdOpenFormatAuto;
if (filename.EndsWith(".docx") || filename.EndsWith(".doc"))
{
oFormat = WdOpenFormat.wdOpenFormatAllWord;
}
oDoc = oWord.Documents.OpenNoRepairDialog(ref oInput, ref confirmConversions, ref readOnly, ref addToRecentFiles, ref passwordDocument, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oFormat, ref oMissing, ref isVisible, ref oMissing, ref oMissing, ref oMissing, ref oMissing);
oDoc.Activate();
object oSaveFormat = format;
oDoc.SaveAs2(ref oOutput, ref oSaveFormat, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);
}
finally
{
oWord.Quit(Microsoft.Office.Interop.Word.WdSaveOptions.wdDoNotSaveChanges, ref oMissing, ref oMissing);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oWord);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oDoc);
// Release all Interop objects.
if (oDoc != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oDoc);
}
if (oWord != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWord);
}
oDoc = null;
oWord = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public void Save(string filename)
{
log("Saving: " + filename);
oDoc.SaveAs(filename, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);
oDoc.SaveAs2(@"C:\Temp\TestDocumentWith1Paragraph.docx");
oDoc.Close(ref oMissing, ref oMissing, ref oMissing);
oWord.Quit();
}
private void createDOC(String tempfilename, String newPath, Random random)
{
//MessageBox.Show("DOC");
string newFileName = tempfilename + ".doc";
newPath = System.IO.Path.Combine(newPath, newFileName);
int sizeofwrite = random.Next(1, 10000);
if (!System.IO.File.Exists(newPath))
{
StringBuilder sb = new StringBuilder();
object fileName = newPath;
object missing = System.Reflection.Missing.Value;
object endOfDoc = "\\endofdoc"; /* \endofdoc is a predefined bookmark */
//Start Word and create a new document.
Word._Application word = new Word.Application();
Word._Document document = word.Documents.Add(ref missing, ref missing, ref missing, ref missing);
for (int x = 0; x < sizeofwrite; x++)
{
Byte[] b = new Byte[1];
random.NextBytes(b);
sb.AppendLine(b[0].ToString());
}
word.Selection.TypeText(sb.ToString());
document.SaveAs2(ref fileName, ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing);
document.Close(ref missing, ref missing, ref missing);
word.Quit(ref missing, ref missing, ref missing);
textBox4.AppendText("Created file: " + newPath.ToString() + System.Environment.NewLine + "SIZE: " + sizeofwrite.ToString() + "\r\n\r\n");
}
}
private void ConvertFile(string filepath)
{
var fileformat = Word.WdSaveFormat.wdFormatXMLDocument;
var newfilepath = Path.ChangeExtension(filepath, ".docx");
Console.WriteLine($"{Path.GetFileName(filepath)} -> {Path.GetFileName(newfilepath)}");
Word._Document document = _word.Documents.Open(
filepath,
ConfirmConversions: false,
ReadOnly: true,
AddToRecentFiles: false,
PasswordDocument: Type.Missing,
PasswordTemplate: Type.Missing,
Revert: false,
WritePasswordDocument: Type.Missing,
WritePasswordTemplate: Type.Missing,
Format: Type.Missing,
Encoding: Type.Missing,
Visible: false,
OpenAndRepair: false,
DocumentDirection: Type.Missing,
NoEncodingDialog: true,
XMLTransform: Type.Missing);
document.Convert();
string v = _word.Version;
switch (v)
{
case "14.0":
case "15.0":
document.SaveAs2(newfilepath, fileformat, CompatibilityMode: Word.WdCompatibilityMode.wdWord2007);
break;
default:
document.SaveAs(newfilepath, fileformat);
break;
}
document.Close();
document = null;
}
// TODO:
private void ButtonAutoFill_Click(object sender, EventArgs e)
{
// Populate the dictionary's Values with input from the textboxes
WordFunc.FillNewTextInput(this);
// Retrieve new text with replaced fields
bodyText = WordFunc.ReplaceText(this, bodyText);
//Console.Out.WriteLine(bodyText);
oWord = new Word.Application {
Visible = true
};
oDoc = oWord.Documents.Open(fileNamePath);
Console.Out.WriteLine(String.Format("{0}: {1}", fileNamePath, fileNamePath.Substring(0, fileNamePath.LastIndexOf('\\'))));
oDoc.SaveAs2(fileNamePath.Substring(0, fileNamePath.LastIndexOf('\\')) + @"\outDoc.doc");
oDoc.Content.Text = bodyText;
Console.Out.WriteLine(oDoc.Content.Text);
WordFunc.CloseWordFile(oWord, oDoc);
}
/// <summary>
/// 初始化 Common.Office.WordInfo
/// </summary>
private void Init()
{
try
{
this.wApp = new Word.Application()
{
Visible = true
};
if (this.strWordFilePath == null)
{
this.wDoc = this.wApp.Documents.Add();
}
else
{
if (this.strWordTemplatesFilePath == null)
{
this.wDoc = this.wApp.Documents.Open(this.strWordFilePath);
}
else
{
this.wDoc = this.wApp.Documents.Add(this.strWordTemplatesFilePath, ref missing, ref missing, ref missing);
}
}
if (this.wDoc == null)
{
throw new Exception("Failed to open the Word file");
}
wDoc.Activate();// 当前文档置前
wDoc.SaveAs2(this.strWordFilePath, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing);
}
catch (Exception ex)
{
Log.WriteLog(null, strBaseInfo, ex);
}
}
/// <summary>
/// 文档中添加图片
/// </summary>
/// <param name="filePath">word文件名</param>
/// <param name="picPath">picture文件名</param>
/// <returns></returns>
public static bool AddPicture(string filePath, string picPath)
{
try
{
Object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Word._Application WordApp = new Application();
WordApp.Visible = true;
object filename = filePath;
Microsoft.Office.Interop.Word._Document WordDoc = WordApp.Documents.Add();
//WordApp.Documents.Open(ref filename, ref oMissing,
//ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
//ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);
//移动光标文档末尾
object count = WordDoc.Paragraphs.Count;
object WdLine = Microsoft.Office.Interop.Word.WdUnits.wdParagraph;
WordApp.Selection.MoveDown(ref WdLine, ref count, ref oMissing); //移动焦点
WordApp.Selection.TypeParagraph(); //插入段落
object LinkToFile = false;
object SaveWithDocument = true;
object Anchor = WordDoc.Application.Selection.Range;
WordDoc.Application.ActiveDocument.InlineShapes.AddPicture(picPath, ref LinkToFile, ref SaveWithDocument, ref Anchor);
//保存
WordDoc.SaveAs2(ref filename);
WordDoc.Close(ref oMissing, ref oMissing, ref oMissing);
WordApp.Quit(ref oMissing, ref oMissing, ref oMissing);
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
return(false);
}
}
public void ConvertDocument()
{
Word._Application application = new Word.Application();
object fileformat = Word.WdSaveFormat.wdFormatXMLDocument;
DirectoryInfo directory = new DirectoryInfo(@_sourcePath);
foreach (FileInfo file in directory.GetFiles("*.doc", SearchOption.AllDirectories))
{
if (file.Extension.ToLower() == ".doc")
{
object filename = file.FullName;
string newfilename = file.Name.Replace(".doc", ".docx");
Word._Document document = application.Documents.Open(filename);
document.Convert();
document.SaveAs2(_targetpath + '/' + newfilename, fileformat);
document.Close();
document = null;
}
}
application.Quit();
application = null;
}
/// <summary>
/// 将字符串存储为word文档格式的文件的方法(多线程)。
/// </summary>
/// <param name="strText">要保存的字符串内容。</param>
/// 需要标签
public void print_Click(object p_str, EventArgs e)
{
object sid = dataGridView1.SelectedRows[0].Cells[0].Value;
string config = "Data Source=" + System.Windows.Forms.Application.StartupPath + @"\test.db";
SQLiteConnection conn = new SQLiteConnection(config);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from Datas where id = " + sid;
SQLiteDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Word._Application word = new Microsoft.Office.Interop.Word.Application();
word.Visible = false;
//Application.StartupPath -- 获取到 .exe 所在 debug 路径
object template = System.Windows.Forms.Application.StartupPath + @"\template\template.dotx";
object linkToFile = false; // 定义该插入图片是否为外部链接
object saveWithDocument = true; // 定义插入图片是否随 word 文档一起保存
string numName = "编号" + sid + ".png";
string replaceNumPic = System.Windows.Forms.Application.StartupPath + @"\images\" + numName;
string overallName = "整体" + sid + ".png";
string replaceOverallPic = System.Windows.Forms.Application.StartupPath + @"\images\" + overallName;
string partialName = "缺陷" + sid + ".png";
string replacePartialPic = System.Windows.Forms.Application.StartupPath + @"\images\" + partialName;
Microsoft.Office.Interop.Word._Document docx = word.Documents.Add(ref template, ref missing, ref missing, ref missing);
object[] bookmarks = new object[17];
bookmarks[0] = "time";
bookmarks[1] = "detectTime";
bookmarks[2] = "inspector";
bookmarks[3] = "lineName";
bookmarks[4] = "towerNum";
bookmarks[5] = "deviceType";
bookmarks[6] = "deviceState";
bookmarks[7] = "distance";
bookmarks[8] = "temperature";
bookmarks[9] = "humidity";
bookmarks[10] = "longtitude";
bookmarks[11] = "latitude";
bookmarks[12] = "maxDB";
bookmarks[13] = "avgDB";
bookmarks[14] = "numPic";
bookmarks[15] = "overallPic";
bookmarks[16] = "partialPic";
docx.Bookmarks.get_Item(ref bookmarks[0]).Range.Text = DateTime.Now.ToString("yyyyMMddHHmmss");
if (reader.Read())
{
docx.Bookmarks.get_Item(ref bookmarks[1]).Range.Text = reader["detectTime"].ToString();
docx.Bookmarks.get_Item(ref bookmarks[2]).Range.Text = reader["inspector"].ToString();
docx.Bookmarks.get_Item(ref bookmarks[3]).Range.Text = reader["lineName"].ToString();
docx.Bookmarks.get_Item(ref bookmarks[4]).Range.Text = reader["towerNum"].ToString();
docx.Bookmarks.get_Item(ref bookmarks[5]).Range.Text = reader["deviceType"].ToString();
docx.Bookmarks.get_Item(ref bookmarks[6]).Range.Text = reader["deviceState"].ToString();
docx.Bookmarks.get_Item(ref bookmarks[7]).Range.Text = reader["distance"].ToString();
docx.Bookmarks.get_Item(ref bookmarks[8]).Range.Text = reader["temperature"].ToString();
docx.Bookmarks.get_Item(ref bookmarks[9]).Range.Text = reader["humidity"].ToString();
docx.Bookmarks.get_Item(ref bookmarks[10]).Range.Text = reader["longtitude"].ToString();
docx.Bookmarks.get_Item(ref bookmarks[11]).Range.Text = reader["latitude"].ToString();
docx.Bookmarks.get_Item(ref bookmarks[12]).Range.Text = reader["maxDB"].ToString();
docx.Bookmarks.get_Item(ref bookmarks[13]).Range.Text = reader["avgDB"].ToString();
//docx.Bookmarks.get_Item(ref bookmarks[14]).Range.Text = reader["numPic"].ToString();
//docx.Bookmarks.get_Item(ref bookmarks[15]).Range.Text = reader["overallPic"].ToString();
//docx.Bookmarks.get_Item(ref bookmarks[16]).Range.Text = reader["partialPic"].ToString();
object numPicMark = bookmarks[14].ToString(); // 杆塔编号照片书签
docx.Bookmarks.get_Item(ref numPicMark).Select(); // 查找图片书签
word.Selection.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter; // 设置图片居中
InlineShape numShape = word.Selection.InlineShapes.AddPicture(replaceNumPic, ref linkToFile, ref saveWithDocument, ref missing); // 在书签位置添加图片
numShape.Height = 240;
numShape.Width = 240;
object overallPicMark = bookmarks[15].ToString(); // 整体照片书签
docx.Bookmarks.get_Item(ref numPicMark).Select(); // 查找图片书签
word.Selection.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter; // 设置图片居中
InlineShape overallShape = word.Selection.InlineShapes.AddPicture(replaceOverallPic, ref linkToFile, ref saveWithDocument, ref missing); // 在书签位置添加图片
overallShape.Height = 240;
overallShape.Width = 240;
object partialPicMark = bookmarks[16].ToString(); // 杆塔编号照片书签
docx.Bookmarks.get_Item(ref numPicMark).Select(); // 查找图片书签
word.Selection.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter; // 设置图片居中
InlineShape partialShape = word.Selection.InlineShapes.AddPicture(replacePartialPic, ref linkToFile, ref saveWithDocument, ref missing); // 在书签位置添加图片
partialShape.Height = 240;
partialShape.Width = 240;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.FileName = DateTime.Now.ToString("yyyyMMddHHmmss");
sfd.Filter = "Word 文档(*.docx)|*.docx|Word 97-2003 文档(*.doc)|(*.doc)";
sfd.DefaultExt = "Word 文档(*.docx)|*.docx|Word 97-2003 文档(*.doc)|(*.doc)";
if (sfd.ShowDialog() == DialogResult.OK)
{
object filename = sfd.FileName;
docx.SaveAs2(ref filename, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);
docx.Close(ref missing, ref missing, ref missing);
word.Quit(ref missing, ref missing, ref missing);
MessageBox.Show("已生成报告");
}
reader.Close();
conn.Close();
}
public static bool AddPictureAndTable(string docPath, string picPath, System.Data.DataTable dt)
{
try
{
Object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Word._Application WordApp = new Application();
WordApp.Visible = true;
object filename = docPath;
Microsoft.Office.Interop.Word._Document WordDoc = WordApp.Documents.Add();
#region 填充图片
//移动光标文档末尾
object count = WordDoc.Paragraphs.Count;
object WdLine = Microsoft.Office.Interop.Word.WdUnits.wdParagraph;
WordApp.Selection.MoveDown(ref WdLine, ref count, ref oMissing); //移动焦点
WordApp.Selection.TypeParagraph(); //插入段落
object LinkToFile = false;
object SaveWithDocument = true;
object Anchor = WordDoc.Application.Selection.Range;
WordDoc.Application.ActiveDocument.InlineShapes.AddPicture(picPath, ref LinkToFile, ref SaveWithDocument, ref Anchor);
#endregion
#region 填充表格
//插入表格
Microsoft.Office.Interop.Word.Table newTable = WordDoc.Tables.Add(WordApp.Selection.Range, dt.Rows.Count + 1, dt.Columns.Count, ref oMissing, ref oMissing);
newTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;//.wdLineStyleThickThinLargeGap;
newTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
//垂直居中
WordApp.Selection.Cells.VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//水平居中
WordApp.Selection.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;
for (int rowIndex = 0; rowIndex <= dt.Rows.Count; rowIndex++)
{
for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
if (rowIndex == 0)
{
newTable.Cell(rowIndex + 1, colIndex + 1).Range.Text = dt.Columns[colIndex].ColumnName;
}
else
{
newTable.Cell(rowIndex + 1, colIndex + 1).Range.Text = dt.Rows[rowIndex - 1][colIndex] + "";
}
}
}
#endregion
//保存
WordDoc.SaveAs2(ref filename);
WordDoc.Close(ref oMissing, ref oMissing, ref oMissing);
WordApp.Quit(ref oMissing, ref oMissing, ref oMissing);
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
return(false);
}
}
// обработчик нажатия кнопки SELECT1
private void selectButton1_Click(object sender, EventArgs e)
{
OleDbCommand command;
OleDbDataReader reader;
string Commercial_name = textBox1.Text;
string Category = comboBox1.Text;
string idCommercial;
string Store_query;
string Product_query;
idCommercial = String.Format("SELECT Id_commercial FROM Commercial WHERE Commercial_name = \"{0}\"", Commercial_name);
command = new OleDbCommand(idCommercial, myConnection);
reader = command.ExecuteReader();
if (!reader.HasRows)
{
richTextBox1.Text += "В базе не найдено такой торговой сети\n";
return;
}
richTextBox1.Text += "Подключаемся к базе данных\n";
List <string> Idshop = new List <string>();
List <string[]> Items = new List <string[]>();
List <string[]> Items_c = new List <string[]>();
if (Category.Equals("Все"))
{
richTextBox1.Text += "Выполяем запрос\n";
//Получаем по id торговой сети по её имени
idCommercial = String.Format("SELECT Id_commercial FROM Commercial WHERE Commercial_name = \"{0}\"", Commercial_name);
command = new OleDbCommand(idCommercial, myConnection);
reader = command.ExecuteReader();
while (reader.Read())
{
idCommercial = reader[0].ToString();
}
reader.Close();
//Получаем список магазинов(складов) этой торговой сети
Store_query = String.Format("SELECT Id_store FROM Shops WHERE Id_commercial = {0}", idCommercial);
command = new OleDbCommand(Store_query, myConnection);
reader = command.ExecuteReader();
while (reader.Read())
{
Idshop.Add(reader[0].ToString());
}
reader.Close();
//Получаем список: id товаров, их цены и количество для каждой торговой точки(склада)
int k = 0;
int t;
string categ;
Items.Add(new string[6]);
richTextBox1.Text += "Формируем отчёт на основе результатов выполнения запроса\n";
// Получить объект приложения Word.
Word._Application word_app = new Word.Application();
// Сделать Word видимым (необязательно).
//word_app.Visible = true;
// Создаем документ Word.
int ml = 0;
object missing = Type.Missing;
List <Word.Paragraph> para = new List <Word.Paragraph>();
Word._Document word_doc = word_app.Documents.Add(
ref missing, ref missing, ref missing, ref missing);
para.Add(word_doc.Paragraphs.Add(ref missing));
// Создаем абзац заголовка.
para[ml].Range.Text = Commercial_name;
object style_name = "Заголовок 1";
para[ml].Range.set_Style(ref style_name);
para[ml].Range.InsertParagraphAfter();
para.Add(word_doc.Paragraphs.Add(ref missing));
ml++;
para[ml].Range.Text = String.Format("Отчёт создан {0}", System.DateTime.Now.ToLocalTime());
para[ml].Range.InsertParagraphAfter();
Word.Range _currentRange = para[ml].Range;
foreach (string i in Idshop)
{
string[] info = new string[5];
Store_query = String.Format("SELECT Id_shop, Address, Holder_name, Holder_phone, Email FROM Shops WHERE Id_store = \"{0}\"", i);
command = new OleDbCommand(Store_query, myConnection);
reader = command.ExecuteReader();
while (reader.Read())
{
info[0] = reader[0].ToString();
info[1] = reader[1].ToString();
info[2] = reader[2].ToString();
info[3] = reader[3].ToString();
info[4] = reader[4].ToString();
}
reader.Close();
para[ml].Range.InsertParagraphAfter();
para[ml].Range.InsertBefore(String.Format("Торговая точка {0} \n Адрес: {1} \n Владелец: {2} \n Телефон: {3} \n Почта: {4}", info[0], info[1], info[2], info[3], info[4]));
_currentRange = para[ml].Range.Next();
k = 0;
Items = new List <string[]>();
Items.Add(new string[6]);
Product_query = String.Format("SELECT Id_item, Amount, Price FROM Store WHERE Id_store = {0}", i);
command = new OleDbCommand(Product_query, myConnection);
reader = command.ExecuteReader();
while (reader.Read())
{
Items[k][0] = reader[0].ToString();
Items[k][4] = reader[1].ToString();
Items[k][5] = reader[2].ToString();
k++;
Items.Add(new string[6]);
}
reader.Close();
for (int l = 0; l < k; l++)
{
Product_query = String.Format("SELECT Item_name, Id_category,Producer_name FROM Items WHERE Id_item = {0}", Items[l][0]);
//richTextBox1.Text += Product_query + " \n";
command = new OleDbCommand(Product_query, myConnection);
reader = command.ExecuteReader();
categ = "";
while (reader.Read())
{
Items[l][1] = reader[0].ToString();
Items[l][3] = reader[2].ToString();
categ = reader[1].ToString();
}
reader.Close();
Product_query = String.Format("SELECT Category_name FROM Item_category WHERE Id_category = {0}", categ);
command = new OleDbCommand(Product_query, myConnection);
reader = command.ExecuteReader();
while (reader.Read())
{
Items[l][2] = reader[0].ToString();
}
reader.Close();
}
Word.Table _table = word_doc.Tables.Add(para[ml].Range, Items.Count, 5, ref missing, ref missing);
_table.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDouble;
_table.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleDouble;
_currentRange = _table.Cell(1, 1).Range;
_table.Cell(1, 1).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalTop;
_table.Cell(1, 1).Shading.BackgroundPatternColor = Word.WdColor.wdColorOrange;
_currentRange.InsertAfter("Название товара");
_currentRange = _table.Cell(1, 2).Range;
_table.Cell(1, 2).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalTop;
_table.Cell(1, 2).Shading.BackgroundPatternColor = Word.WdColor.wdColorPaleBlue;
_currentRange.InsertAfter("Категория");
_currentRange = _table.Cell(1, 3).Range;
_table.Cell(1, 3).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalTop;
_table.Cell(1, 3).Shading.BackgroundPatternColor = Word.WdColor.wdColorSeaGreen;
_currentRange.InsertAfter("Изготовитель");
_currentRange = _table.Cell(1, 4).Range;
_table.Cell(1, 4).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalTop;
_table.Cell(1, 4).Shading.BackgroundPatternColor = Word.WdColor.wdColorViolet;
_currentRange.InsertAfter("Количество товара");
_currentRange = _table.Cell(1, 5).Range;
_table.Cell(1, 5).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalTop;
_table.Cell(1, 5).Shading.BackgroundPatternColor = Word.WdColor.wdColorYellow;
_currentRange.InsertAfter("Цена");
for (int index = 2; index <= Items.Count; index++)
{
for (int j = 1; j <= 5; j++)
{
_currentRange = _table.Cell(index, j).Range;
_table.Cell(index, j).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalTop;
_currentRange.InsertAfter(Items[index - 2][j]);
}
}
para[ml].Range.InsertParagraphAfter();
para.Add(word_doc.Paragraphs.Add(ref missing));
ml++;
para[ml].Range.InsertParagraphAfter();
para[ml].Range.GoToEditableRange();
_currentRange = para[ml].Range;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.DefaultExt = "doc";
sfd.Filter = "Word files (*.doc)|*.doc|All files (*.*)|*.*";
if (sfd.ShowDialog() == DialogResult.OK)
{
word_doc.SaveAs2(sfd.FileName, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing);
}
// Закрыть.
object save_changes = false;
word_doc.Close(ref save_changes, ref missing, ref missing);
word_app.Quit(ref save_changes, ref missing, ref missing);
richTextBox1.Text += "Готово\n";
}
else
{
richTextBox1.Text += "Выполяем запрос\n";
//Получаем по id торговой сети по её имени
idCommercial = String.Format("SELECT Id_commercial FROM Commercial WHERE Commercial_name = \"{0}\"", Commercial_name);
command = new OleDbCommand(idCommercial, myConnection);
reader = command.ExecuteReader();
while (reader.Read())
{
idCommercial = reader[0].ToString();
}
reader.Close();
//Получаем список магазинов(складов) этой торговой сети
Store_query = String.Format("SELECT Id_store FROM Shops WHERE Id_commercial = {0}", idCommercial);
command = new OleDbCommand(Store_query, myConnection);
reader = command.ExecuteReader();
while (reader.Read())
{
Idshop.Add(reader[0].ToString());
}
reader.Close();
//Получаем список: id товаров, их цены и количество для каждой торговой точки(склада)
int k = 0;
int t;
string categ;
Items.Add(new string[6]);
richTextBox1.Text += "Формируем отчёт на основе результатов выполнения запроса\n";
// Получить объект приложения Word.
Word._Application word_app = new Word.Application();
// Сделать Word видимым (необязательно).
//word_app.Visible = true;
// Создаем документ Word.
int ml = 0;
object missing = Type.Missing;
List <Word.Paragraph> para = new List <Word.Paragraph>();
Word._Document word_doc = word_app.Documents.Add(
ref missing, ref missing, ref missing, ref missing);
para.Add(word_doc.Paragraphs.Add(ref missing));
// Создаем абзац заголовка.
para[ml].Range.Text = Commercial_name;
object style_name = "Заголовок 1";
para[ml].Range.set_Style(ref style_name);
para[ml].Range.InsertParagraphAfter();
para.Add(word_doc.Paragraphs.Add(ref missing));
ml++;
para[ml].Range.Text = String.Format("Отчёт создан {0}", System.DateTime.Now.ToLocalTime());
para[ml].Range.InsertParagraphAfter();
Word.Range _currentRange = para[ml].Range;
foreach (string i in Idshop)
{
string[] info = new string[5];
Store_query = String.Format("SELECT Id_shop, Address, Holder_name, Holder_phone, Email FROM Shops WHERE Id_store = \"{0}\"", i);
command = new OleDbCommand(Store_query, myConnection);
reader = command.ExecuteReader();
while (reader.Read())
{
info[0] = reader[0].ToString();
info[1] = reader[1].ToString();
info[2] = reader[2].ToString();
info[3] = reader[3].ToString();
info[4] = reader[4].ToString();
}
reader.Close();
para[ml].Range.InsertParagraphAfter();
para[ml].Range.InsertBefore(String.Format("Торговая точка {0} \n Адрес: {1} \n Владелец: {2} \n Телефон: {3} \n Почта: {4}", info[0], info[1], info[2], info[3], info[4]));
_currentRange = para[ml].Range.Next();
k = 0;
Items = new List <string[]>();
Items.Add(new string[6]);
Product_query = String.Format("SELECT Id_item, Amount, Price FROM Store WHERE Id_store = {0}", i);
command = new OleDbCommand(Product_query, myConnection);
reader = command.ExecuteReader();
while (reader.Read())
{
Items[k][0] = reader[0].ToString();
Items[k][4] = reader[1].ToString();
Items[k][5] = reader[2].ToString();
k++;
Items.Add(new string[6]);
}
reader.Close();
for (int l = 0; l < k; l++)
{
Product_query = String.Format("SELECT Item_name, Id_category,Producer_name FROM Items WHERE Id_item = {0}", Items[l][0]);
command = new OleDbCommand(Product_query, myConnection);
reader = command.ExecuteReader();
categ = "";
while (reader.Read())
{
Items[l][1] = reader[0].ToString();
Items[l][3] = reader[2].ToString();
categ = reader[1].ToString();
}
reader.Close();
Product_query = String.Format("SELECT Category_name FROM Item_category WHERE Id_category = {0}", categ);
command = new OleDbCommand(Product_query, myConnection);
reader = command.ExecuteReader();
while (reader.Read())
{
Items[l][2] = reader[0].ToString();
}
reader.Close();
richTextBox1.Text += Items[l][1] + " " + Items[l][2] + " " + Items[l][3] + " " + Items[l][4] + " " + Items[l][5] + "\n";
}
Items_c = new List <string[]>();
int h = 0;
for (int index = 0; index < Items.Count; index++)
{
if (Category.Equals(Items[index][2]))
{
Items_c.Add(new string[6]);
for (int j = 0; j < 6; j++)
{
Items_c[h][j] = Items[index][j];
}
h++;
}
}
textBox1.Text += Items_c.Count;
Word.Table _table = word_doc.Tables.Add(para[ml].Range, Items_c.Count + 1, 5, ref missing, ref missing);
_table.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDouble;
_table.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleDouble;
_currentRange = _table.Cell(1, 1).Range;
_table.Cell(1, 1).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalTop;
_table.Cell(1, 1).Shading.BackgroundPatternColor = Word.WdColor.wdColorOrange;
_currentRange.InsertAfter("Название товара");
_currentRange = _table.Cell(1, 2).Range;
_table.Cell(1, 2).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalTop;
_table.Cell(1, 2).Shading.BackgroundPatternColor = Word.WdColor.wdColorPaleBlue;
_currentRange.InsertAfter("Категория");
_currentRange = _table.Cell(1, 3).Range;
_table.Cell(1, 3).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalTop;
_table.Cell(1, 3).Shading.BackgroundPatternColor = Word.WdColor.wdColorSeaGreen;
_currentRange.InsertAfter("Изготовитель");
_currentRange = _table.Cell(1, 4).Range;
_table.Cell(1, 4).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalTop;
_table.Cell(1, 4).Shading.BackgroundPatternColor = Word.WdColor.wdColorViolet;
_currentRange.InsertAfter("Количество товара");
_currentRange = _table.Cell(1, 5).Range;
_table.Cell(1, 5).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalTop;
_table.Cell(1, 5).Shading.BackgroundPatternColor = Word.WdColor.wdColorYellow;
_currentRange.InsertAfter("Цена");
for (int index = 0; index < Items_c.Count; index++)
{
for (int j = 1; j <= 5; j++)
{
_currentRange = _table.Cell(index + 2, j).Range;
_table.Cell(index + 2, j).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalTop;
_currentRange.InsertAfter(Items_c[index][j]);
}
}
para[ml].Range.InsertParagraphAfter();
para.Add(word_doc.Paragraphs.Add(ref missing));
ml++;
para[ml].Range.InsertParagraphAfter();
para[ml].Range.GoToEditableRange();
_currentRange = para[ml].Range;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.DefaultExt = "doc";
sfd.Filter = "Word files (*.doc)|*.doc|All files (*.*)|*.*";
if (sfd.ShowDialog() == DialogResult.OK)
{
word_doc.SaveAs2(sfd.FileName, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing);
}
// Закрыть.
object save_changes = false;
word_doc.Close(ref save_changes, ref missing, ref missing);
word_app.Quit(ref save_changes, ref missing, ref missing);
richTextBox1.Text += "Готово\n";
}
}
