/* Экспорт данных из dataGridView1 в Excel */ private void btn_Export_Click(object sender, EventArgs e) { bool okData = false; this.Cursor = Cursors.WaitCursor; Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Workbook ExcelWorkBook; Worksheet ExcelWorkSheet; ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value); //Таблица. ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); if (dgv_Sales.RowCount > 0) { for (int i = 0; i < dgv_Sales.ColumnCount - 4; i++) { ExcelApp.Cells[1, i + 1] = dgv_Sales.Columns[i].HeaderText; } for (int i = 0; i < dgv_Sales.Rows.Count; i++) { for (int j = 0; j < dgv_Sales.ColumnCount - 4; j++) { ExcelApp.Cells[i + 2, j + 1] = dgv_Sales.Rows[i].Cells[j].Value; } } saveFileDialog1.FileName = lb_StartDate.Text + "-" + lb_EndDate.Text + " Business Report"; okData = true; } else { MessageBox.Show("Нет данных для экспорта!", "Ошибка"); } saveFileDialog1.Filter = "Excel(*.xlsx)|*.xlsx|All files(*.*)|*.*"; this.Cursor = Cursors.Default; if (okData) { if (saveFileDialog1.ShowDialog() == DialogResult.Cancel) { ExcelWorkBook.Close(false); } else { // получаем выбранный файл string filename = saveFileDialog1.FileName; ExcelWorkBook.SaveAs(filename); ExcelWorkBook.Close(false); MessageBox.Show("Успешно сохранено!", "Успех"); } } }
public void ConvertToExcel(string name) { string fullPath = Path.Combine(Server.MapPath("~/Images/"), name); var file = System.IO.File.OpenRead(fullPath); Image picture = Image.FromStream(file, true, true); Bitmap Bpicture = (Bitmap)picture; Excel.Application ExcelApp = new Excel.Application(); Excel.Workbook ExcelWorkBook; Excel.Worksheet ExcelWorkSheet; ExcelWorkBook = ExcelApp.Workbooks.Add(Missing.Value); ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.ActiveSheet; int[,] ExcelArray = new int[Bpicture.Width, Bpicture.Height]; for (int i = 0; i < Bpicture.Width; i++) { for (int j = 0; j < Bpicture.Height; j++) { ExcelArray[i, j] = Bpicture.GetPixel(i, j).R; } } Excel.Range rng = ExcelWorkSheet.Cells[1, "A"]; rng = rng.Resize[Bpicture.Width, Bpicture.Height]; rng.Value = ExcelArray; string filenameExcel = name.Split('.').First() + ".xlsx"; string fullfilename = Server.MapPath("~/ExcelFiles/" + filenameExcel); if (System.IO.File.Exists(fullfilename)) { System.IO.File.Delete(fullfilename); } ExcelWorkBook.SaveAs(fullfilename, Excel.XlFileFormat.xlWorkbookDefault); ExcelWorkBook.Close(false, Type.Missing, Type.Missing); ExcelApp.Workbooks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkBook); }
/* Экспорт в *.xlsx */ private void btn_Export_Click(object sender, EventArgs e) { this.Enabled = false; label3.Visible = true; Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Workbook ExcelWorkBook; Worksheet ExcelWorkSheet; ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value); //Таблица. ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); for (int i = 3; i < dgv_Keywords.ColumnCount; i++) { ExcelApp.Cells[i - 2] = dgv_Keywords.Columns[i].HeaderText; ExcelWorkSheet.Columns[i + 1].ColumnWidth = 12; ExcelWorkSheet.Columns[i + 1].Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; } for (int i = 0; i < dgv_Keywords.Rows.Count; i++) { for (int j = 3; j < dgv_Keywords.ColumnCount; j++) { ExcelApp.Cells[i + 2, j - 2] = dgv_Keywords.Rows[i].Cells[j].Value; } } ExcelWorkSheet.get_Range("A1", "A1").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; ExcelWorkSheet.get_Range("B1", "B1").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; ExcelWorkSheet.get_Range("C1", "C1").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; ExcelWorkSheet.Columns[1].ColumnWidth = 30; ExcelWorkSheet.Columns[2].ColumnWidth = 30; ExcelWorkSheet.Columns[3].ColumnWidth = 50; saveFileDialog1.Filter = "Excel(*.xlsx)|*.xlsx|All files(*.*)|*.*"; saveFileDialog1.FileName = "Keyword Book 1"; if (saveFileDialog1.ShowDialog() == DialogResult.Cancel) { ExcelWorkBook.Close(false); } else { // получаем выбранный файл string filename = saveFileDialog1.FileName; ExcelWorkBook.SaveAs(filename); ExcelWorkBook.Close(false); MessageBox.Show("Успешно сохранено!", "Успех"); } label3.Visible = false; this.Enabled = true; }
private void StartMatching() { for (int i = 0; i < ordersList.Count; i++) { for (int j = 0; j < ordersListstring.Count; j++) { if (ordersList[i].AmazonOrderId.Equals(ordersListstring[j])) { resultList.Add(new OrdersModel()); resultList[resultList.Count - 1] = ordersList[i]; j = ordersListstring.Count; } } } Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Workbook ExcelWorkBook; Worksheet ExcelWorkSheet; ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value); //Таблица. ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); int row = 1; for (int i = 0; i < resultList.Count; i++) { for (int j = 0; j < resultList[i].FieldCount; j++) { ExcelApp.Cells[row, j + 1] = resultList[i].GetOrders(j); } row++; } af.saveFileDialog1.Filter = "Excel(*.xlsx)|*.xlsx|All files(*.*)|*.*"; af.saveFileDialog1.FileName = "Orders and Refunds Book 1"; if (af.saveFileDialog1.ShowDialog() == DialogResult.Cancel) { ExcelWorkBook.Close(false); } else { // получаем выбранный файл string filename = af.saveFileDialog1.FileName; ExcelWorkBook.SaveAs(filename); ExcelWorkBook.Close(false); MessageBox.Show("Успешно сохранено!", "Успех"); } }
public ExcelWriter(string filePath) : base(filePath) { if (!System.IO.File.Exists(filePath)) { object misValue = System.Reflection.Missing.Value; ExcelWorkBook = ExcelApp.Workbooks.Add(misValue); ExcelWorkSheet = ExcelWorkBook.Worksheets[1]; ExcelWorkBook.SaveAs(filePath); } else { ExcelWorkBook = ExcelApp.Workbooks.Open(filePath); } // noop }
public bool SaveExcelFile(string saveFileDialogTitle) { SFD_Excel.Filter = "Excel Sheet (*.xlsx)|*.xlsx"; SFD_Excel.Title = saveFileDialogTitle; SFD_Excel.OverwritePrompt = true; if (SFD_Excel.ShowDialog() != DialogResult.OK) { return(false); } // Save workbook ExcelApp.DisplayAlerts = false; ExcelWorkBook.SaveAs(SFD_Excel.FileName); ExcelApp.Visible = true; return(true); }
/* Экспорт ключей в *.xls */ private void btn_Export_Click(object sender, EventArgs e) { progressBar1.Maximum = dgv_Keywords.RowCount; progressBar1.Value = 0; progressBar1.Visible = true; Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Workbook ExcelWorkBook; Worksheet ExcelWorkSheet; ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value); //Таблица. ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); ExcelApp.Cells[1, 1] = "KEYWORD"; ExcelApp.Cells[1, 2] = "VALUE"; for (int i = 0; i < dgv_Keywords.Rows.Count; i++) { for (int j = 0; j < dgv_Keywords.ColumnCount; j++) { ExcelApp.Cells[i + 2, j + 1] = dgv_Keywords.Rows[i].Cells[j].Value; } progressBar1.Value++; } //Вызываем нашу созданную эксельку. //ExcelApp.Visible = true; //ExcelApp.UserControl = true; saveFileDialog1.Filter = "Excel(*.xlsx)|*.xlsx|All files(*.*)|*.*"; if (saveFileDialog1.ShowDialog() == DialogResult.Cancel) { } else { // получаем выбранный файл string filename = saveFileDialog1.FileName; ExcelWorkBook.SaveAs(filename); ExcelWorkBook.Close(false); MessageBox.Show("Успешно сохранено!", "Успех"); } progressBar1.Value = 0; progressBar1.Visible = false; }
private Stream CreateReport(Attendee[] data) { var tmpPath = Path.GetTempFileName(); System.IO.File.Delete(tmpPath); string[] heads = new string[] { "ФИО", "Телефон", "Email", "Тип" }; 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.Worksheets.get_Item(1); for (int i = 0; i < heads.Length; i++) { ExcelApp.Cells[1, i + 1] = heads[i]; } for (int i = 0; i < data.Length; i++) { ExcelApp.Cells[i + 2, 1] = data[i].ContactInfo.FullName; ExcelApp.Cells[i + 2, 2] = data[i].ContactInfo.PhoneNumber; ExcelApp.Cells[i + 2, 3] = data[i].ContactInfo.Email; ExcelApp.Cells[i + 2, 4] = data[i].Type.GetDescription(); } ExcelWorkSheet.Columns.AutoFit(); object misValue = System.Reflection.Missing.Value; ExcelWorkBook.SaveAs(tmpPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); ExcelApp.Workbooks.Close(); ExcelApp.Quit(); Marshal.ReleaseComObject(ExcelWorkSheet); Marshal.ReleaseComObject(ExcelWorkBook); var bytes = System.IO.File.ReadAllBytes(tmpPath); System.IO.File.Delete(tmpPath); return(new MemoryStream(bytes)); }
//Сохранить в excel private void SaveFileXlsToolStripMenuItem_Click(object sender, EventArgs e) { //Выибраем диркторию и имя if (saveFileDialog1.ShowDialog() == DialogResult.Cancel) { return; } string filenameSave = saveFileDialog1.FileName; //Создаем объекты приложения, книги, листа 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.Worksheets.get_Item(1); //Заполняем лист for (int j = 0; j < dataGridView4.Columns.Count; j++) { //Заголовки таблиц ExcelApp.Cells[1, j + 1] = dataGridView4.Columns[j].HeaderCell.Value; for (int i = 0; i < dataGridView4.Rows.Count; i++) { //Данные из таблицы ExcelApp.Cells[i + 2, j + 1] = dataGridView4.Rows[i].Cells[j].Value; } } //Сохраняем или заменяем существующий файл ExcelWorkBook.SaveAs(filenameSave); //Закрываем ExcelWorkBook.Close(); ExcelApp.Quit(); WriteLogFile("Сохранение данных в файл " + filenameSave); }
/* Экспорт PnL в Excel */ private void exportToolStripMenuItem_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Workbook ExcelWorkBook; Worksheet ExcelWorkSheet; ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value); //Таблица. ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); ExcelApp.Cells[1, 1] = "Тип"; ExcelApp.Cells[1, 2] = "Описание"; for (int i = 2; i < dataGridView1.ColumnCount; i++) { ExcelApp.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText; } for (int i = 0; i < dataGridView1.Rows.Count; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { ExcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value; } } saveFileDialog1.Filter = "Excel(*.xlsx)|*.xlsx|All files(*.*)|*.*"; if (saveFileDialog1.ShowDialog() == DialogResult.Cancel) { } else { // получаем выбранный файл string filename = saveFileDialog1.FileName; ExcelWorkBook.SaveAs(filename); ExcelWorkBook.Close(false); MessageBox.Show("Успешно сохранено!", "Успех"); } }
static void Main(string[] args) { SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["UMS"].ConnectionString); SqlCommand sqlCommand = new SqlCommand("SELECT * FROM ExcelGenerate", sqlConnection); SqlCommand sqlCommand2 = new SqlCommand("SELECT * FROM UserInfo", sqlConnection); SqlCommand sqlCommandDel = new SqlCommand("DELETE FROM ExcelGenerate", sqlConnection); /*sqlCommand.CommandType = CommandType.StoredProcedure;*/ /*sqlCommand.Parameters.AddWithValue("@UserId", userId);*/ sqlConnection.Open(); var reader = sqlCommand.ExecuteReader(); if (reader.Read()) { string fileName = reader.GetString(0); string sendEmailTo = reader.GetString(1); sqlConnection.Close(); sqlConnection.Open(); var getAll = sqlCommand2.ExecuteReader(); string OpenAtDownload = ""; /* System.Diagnostics.Debug.WriteLine("Is it empty: " + fileName.IsNullOrWhiteSpace()); * if (fileName == "" || fileName.IsNullOrWhiteSpace() == true) { fileName = "UserList"; }*/ if (fileName == "" || fileName == null) { fileName = "UserList"; } Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook; Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet; object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application ExcelExport = new Microsoft.Office.Interop.Excel.Application(); ExcelWorkBook = ExcelExport.Workbooks.Add(misValue); ExcelWorkSheet = ExcelWorkBook.Worksheets.get_Item(1); ExcelWorkSheet.Cells.NumberFormat = "@"; var rowColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow); ExcelWorkSheet.get_Range("A1", "K1").Interior.Color = rowColor; ExcelWorkSheet.get_Range("A1", "K1").Font.Bold = true; ExcelWorkSheet.Cells[1, 1] = "UserName"; ExcelWorkSheet.Cells[1, 2] = "FirstName"; ExcelWorkSheet.Cells[1, 3] = "LastName"; ExcelWorkSheet.Cells[1, 4] = "Email"; ExcelWorkSheet.Cells[1, 5] = "Address"; ExcelWorkSheet.Cells[1, 6] = "City"; ExcelWorkSheet.Cells[1, 7] = "Country"; ExcelWorkSheet.Cells[1, 8] = "ZipCode"; ExcelWorkSheet.Cells[1, 9] = "PhoneNumber"; ExcelWorkSheet.Cells[1, 10] = "Role"; ExcelWorkSheet.Cells[1, 11] = "Status"; /* var userInfoes = db.UserInfoes.Include(u => u.Role).Include(u => u.Status).Include(u => u.UserCredential); * userInfoes.ToList();*/ int row = 2; while (getAll.Read()) { ExcelWorkSheet.Cells[row, 1] = getAll.GetString(1); ExcelWorkSheet.Cells[row, 2] = getAll.GetString(2); ExcelWorkSheet.Cells[row, 3] = getAll.GetString(3); ExcelWorkSheet.Cells[row, 4] = getAll.GetString(4); ExcelWorkSheet.Cells[row, 5] = getAll.GetString(5); ExcelWorkSheet.Cells[row, 6] = getAll.GetString(6); ExcelWorkSheet.Cells[row, 7] = getAll.GetString(7); ExcelWorkSheet.Cells[row, 8] = getAll.GetString(8); ExcelWorkSheet.Cells[row, 9] = getAll.GetString(9); ExcelWorkSheet.Cells[row, 10] = getAll.GetInt32(10); ExcelWorkSheet.Cells[row, 11] = getAll.GetInt32(11); row++; } ExcelWorkSheet.Columns.AutoFit(); /*string isEmailSend = "";*/ sqlConnection.Close(); sqlConnection.Open(); try { ExcelWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); if (sendEmailTo != null) { try { ExcelWorkBook.SendMail(sendEmailTo, "User List From the User Management System"); /*isEmailSend = "Email was sent.";*/ } catch { /*isEmailSend = "Email was not sent.";*/ } } if (OpenAtDownload == "true") { ExcelWorkBook.WebPagePreview(); /*System.Diagnostics.Process.Start($"E:/Documents/{fileName}.xls"); */ } /*ExcelWorkBook.WebPagePreview();*/ ExcelWorkBook.Close(true, misValue, misValue); ExcelExport.Quit(); Marshal.ReleaseComObject(ExcelWorkSheet); Marshal.ReleaseComObject(ExcelWorkBook); Marshal.ReleaseComObject(ExcelExport); sqlCommandDel.ExecuteReader(); sqlConnection.Close(); Environment.Exit(0); /*return RedirectToAction("Index", new { a = "Excel Document was created sucessfully and it should be available in your Documents folder.", color = "green", emailStatus = isEmailSend });*/ } catch { ExcelWorkBook.Close(true, misValue, misValue); ExcelExport.Quit(); Marshal.ReleaseComObject(ExcelWorkSheet); Marshal.ReleaseComObject(ExcelWorkBook); Marshal.ReleaseComObject(ExcelExport); /*System.Diagnostics.Debug.WriteLine("Excel Document was not created.");*/ sqlCommandDel.ExecuteReader(); sqlConnection.Close(); /*return RedirectToAction("Index", new { a = "Excel Document was not created.", color = "red", emailStatus = isEmailSend });*/ Environment.Exit(0); } } }
private int CreateExcelFile() { bool okData = false; string path = "D:\\"; string fileName = ""; try { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Workbook ExcelWorkBook; Worksheet ExcelWorkSheet1; Worksheet ExcelWorkSheet2; Worksheet ExcelWorkSheet3; Worksheet ExcelWorkSheet4; ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value); ExcelWorkBook.Worksheets.Add(); ExcelWorkBook.Worksheets.Add(); ExcelWorkBook.Worksheets.Add(); //Таблица. ExcelWorkSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); ExcelWorkSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(2); ExcelWorkSheet3 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(3); ExcelWorkSheet4 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(4); int broadcnt = 0; int phrasecnt = 0; int exactcnt = 0; int othercnt = 0; if (resultList.Count > 0) { for (int i = 0; i < resultList.Count; i++) { if (resultList[i].matchType.ToLower().Equals("broad")) { for (int j = 0; j < resultList[0].ColumnCount; j++) { if (j >= 0 && j <= 5) { ExcelWorkSheet1.Cells[broadcnt + 2, j + 1] = resultList[i].GetVal(j).ToString(); } else if (j >= 6 && j <= 8) { ExcelWorkSheet1.Cells[broadcnt + 2, j + 1] = (int)resultList[i].GetVal(j); } else if (j == 9) { ExcelWorkSheet1.Cells[broadcnt + 2, j + 1] = Math.Round((double)resultList[i].GetVal(j), 2); } } broadcnt++; } else if (resultList[i].matchType.ToLower().Equals("phrase")) { for (int j = 0; j < resultList[0].ColumnCount; j++) { if (j >= 0 && j <= 5) { ExcelWorkSheet2.Cells[phrasecnt + 2, j + 1] = resultList[i].GetVal(j).ToString(); } else if (j >= 6 && j <= 8) { ExcelWorkSheet2.Cells[phrasecnt + 2, j + 1] = (int)resultList[i].GetVal(j); } else if (j == 9) { ExcelWorkSheet2.Cells[phrasecnt + 2, j + 1] = Math.Round((double)resultList[i].GetVal(j), 2); } } phrasecnt++; } else if (resultList[i].matchType.ToLower().Equals("exact")) { for (int j = 0; j < resultList[0].ColumnCount; j++) { if (j >= 0 && j <= 5) { ExcelWorkSheet3.Cells[exactcnt + 2, j + 1] = resultList[i].GetVal(j).ToString(); } else if (j >= 6 && j <= 8) { ExcelWorkSheet3.Cells[exactcnt + 2, j + 1] = (int)resultList[i].GetVal(j); } else if (j == 9) { ExcelWorkSheet3.Cells[exactcnt + 2, j + 1] = Math.Round((double)resultList[i].GetVal(j), 2); } } exactcnt++; } else { for (int j = 0; j < resultList[0].ColumnCount; j++) { if (j >= 0 && j <= 5) { ExcelWorkSheet4.Cells[othercnt + 2, j + 1] = resultList[i].GetVal(j).ToString(); } else if (j >= 6 && j <= 8) { ExcelWorkSheet4.Cells[othercnt + 2, j + 1] = (int)resultList[i].GetVal(j); } else if (j == 9) { ExcelWorkSheet4.Cells[othercnt + 2, j + 1] = Math.Round((double)resultList[i].GetVal(j), 2); } } othercnt++; } } fileName = "Advertising Alarm Report - " + startNew.ToString("dd-MM-yyyy") + "-" + endNew.ToString("dd-MM-yyyy") + " (" + DateTime.Now.ToString("HH-mm-ss") + ")"; okData = true; } ExcelWorkSheet1.Cells[1, 1] = "Товар"; ExcelWorkSheet1.Cells[1, 2] = "Маркетплейс"; ExcelWorkSheet1.Cells[1, 3] = "Campaign"; ExcelWorkSheet1.Cells[1, 4] = "AdGroup"; ExcelWorkSheet1.Cells[1, 5] = "Targeting"; ExcelWorkSheet1.Cells[1, 6] = "Match Type"; ExcelWorkSheet1.Cells[1, 7] = "Позапрошлая неделя"; ExcelWorkSheet1.Cells[1, 8] = "Прошлая неделя"; ExcelWorkSheet1.Cells[1, 9] = "Разница"; ExcelWorkSheet1.Cells[1, 10] = "Разница %"; ExcelWorkSheet1.Name = "Broad"; ExcelWorkSheet1.get_Range("G2", "J5000").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; ExcelWorkSheet1.Columns[1].ColumnWidth = 32; ExcelWorkSheet1.Columns[2].ColumnWidth = 22; ExcelWorkSheet1.Columns[3].ColumnWidth = 31; ExcelWorkSheet1.Columns[4].ColumnWidth = 17; ExcelWorkSheet1.Columns[5].ColumnWidth = 23; ExcelWorkSheet1.Columns[6].ColumnWidth = 13; ExcelWorkSheet1.Columns[7].ColumnWidth = 13; ExcelWorkSheet1.Columns[8].ColumnWidth = 13; ExcelWorkSheet1.Columns[9].ColumnWidth = 13; ExcelWorkSheet1.Columns[10].ColumnWidth = 13; ExcelWorkSheet2.Cells[1, 1] = "Товар"; ExcelWorkSheet2.Cells[1, 2] = "Маркетплейс"; ExcelWorkSheet2.Cells[1, 3] = "Campaign"; ExcelWorkSheet2.Cells[1, 4] = "AdGroup"; ExcelWorkSheet2.Cells[1, 5] = "Targeting"; ExcelWorkSheet2.Cells[1, 6] = "Match Type"; ExcelWorkSheet2.Cells[1, 7] = "Позапрошлая неделя"; ExcelWorkSheet2.Cells[1, 8] = "Прошлая неделя"; ExcelWorkSheet2.Cells[1, 9] = "Разница"; ExcelWorkSheet2.Cells[1, 10] = "Разница %"; ExcelWorkSheet2.Name = "Phrase"; ExcelWorkSheet2.get_Range("G2", "J5000").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; ExcelWorkSheet2.Columns[1].ColumnWidth = 32; ExcelWorkSheet2.Columns[2].ColumnWidth = 22; ExcelWorkSheet2.Columns[3].ColumnWidth = 31; ExcelWorkSheet2.Columns[4].ColumnWidth = 17; ExcelWorkSheet2.Columns[5].ColumnWidth = 23; ExcelWorkSheet2.Columns[6].ColumnWidth = 13; ExcelWorkSheet2.Columns[7].ColumnWidth = 13; ExcelWorkSheet2.Columns[8].ColumnWidth = 13; ExcelWorkSheet2.Columns[9].ColumnWidth = 13; ExcelWorkSheet2.Columns[10].ColumnWidth = 13; ExcelWorkSheet3.Cells[1, 1] = "Товар"; ExcelWorkSheet3.Cells[1, 2] = "Маркетплейс"; ExcelWorkSheet3.Cells[1, 3] = "Campaign"; ExcelWorkSheet3.Cells[1, 4] = "AdGroup"; ExcelWorkSheet3.Cells[1, 5] = "Targeting"; ExcelWorkSheet3.Cells[1, 6] = "Match Type"; ExcelWorkSheet3.Cells[1, 7] = "Позапрошлая неделя"; ExcelWorkSheet3.Cells[1, 8] = "Прошлая неделя"; ExcelWorkSheet3.Cells[1, 9] = "Разница"; ExcelWorkSheet3.Cells[1, 10] = "Разница %"; ExcelWorkSheet3.Name = "Exact"; ExcelWorkSheet3.get_Range("G2", "J5000").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; ExcelWorkSheet3.Columns[1].ColumnWidth = 32; ExcelWorkSheet3.Columns[2].ColumnWidth = 22; ExcelWorkSheet3.Columns[3].ColumnWidth = 31; ExcelWorkSheet3.Columns[4].ColumnWidth = 17; ExcelWorkSheet3.Columns[5].ColumnWidth = 23; ExcelWorkSheet3.Columns[6].ColumnWidth = 13; ExcelWorkSheet3.Columns[7].ColumnWidth = 13; ExcelWorkSheet3.Columns[8].ColumnWidth = 13; ExcelWorkSheet3.Columns[9].ColumnWidth = 13; ExcelWorkSheet3.Columns[10].ColumnWidth = 13; ExcelWorkSheet4.Cells[1, 1] = "Товар"; ExcelWorkSheet4.Cells[1, 2] = "Маркетплейс"; ExcelWorkSheet4.Cells[1, 3] = "Campaign"; ExcelWorkSheet4.Cells[1, 4] = "AdGroup"; ExcelWorkSheet4.Cells[1, 5] = "Targeting"; ExcelWorkSheet4.Cells[1, 6] = "Match Type"; ExcelWorkSheet4.Cells[1, 7] = "Позапрошлая неделя"; ExcelWorkSheet4.Cells[1, 8] = "Прошлая неделя"; ExcelWorkSheet4.Cells[1, 9] = "Разница"; ExcelWorkSheet4.Cells[1, 10] = "Разница %"; ExcelWorkSheet4.Name = "Other"; ExcelWorkSheet4.get_Range("G2", "J5000").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; ExcelWorkSheet4.Columns[1].ColumnWidth = 32; ExcelWorkSheet4.Columns[2].ColumnWidth = 22; ExcelWorkSheet4.Columns[3].ColumnWidth = 31; ExcelWorkSheet4.Columns[4].ColumnWidth = 17; ExcelWorkSheet4.Columns[5].ColumnWidth = 23; ExcelWorkSheet4.Columns[6].ColumnWidth = 13; ExcelWorkSheet4.Columns[7].ColumnWidth = 13; ExcelWorkSheet4.Columns[8].ColumnWidth = 13; ExcelWorkSheet4.Columns[9].ColumnWidth = 13; ExcelWorkSheet4.Columns[10].ColumnWidth = 13; if (okData) { try { path = path + fileName; ExcelWorkBook.SaveAs(@path); ExcelWorkBook.Close(false); return(1); } catch (Exception ex) { return(0); } } else { return(0); } } catch (Exception ex) { return(0); } }
private void SaveIntervalExcel_Click(object sender, RoutedEventArgs e) { if (intervalControl.Open) { System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog(); sfd.Filter = "Документ Excel(*.xlsx)|*.xlsx"; if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } string filename = sfd.FileName; int rows = intervalControl.IntervalGrid.Count(); double[] lBorder = intervalControl.IntervalGrid.Select(p => p.leftBorder).ToArray(); double[] rBorder = intervalControl.IntervalGrid.Select(p => p.rightBorder).ToArray(); double[] frequency = intervalControl.IntervalGrid.Select(p => p.frequency).ToArray(); double[] accumulatedFrequency = intervalControl.IntervalGrid.Select(p => p.accumulatedFrequency).ToArray(); 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.Worksheets.get_Item(1); ExcelWorkSheet.Cells[1, 1] = "Левая граница"; ExcelWorkSheet.Cells[1, 2] = "Правая граница"; ExcelWorkSheet.Cells[1, 3] = "Частота"; ExcelWorkSheet.Cells[1, 4] = "Накопленная частота"; ExcelWorkSheet.Cells[1, 5] = "Расчеты"; ExcelWorkSheet.Cells[1, 1].Font.Bold = true; ExcelWorkSheet.Cells[1, 2].Font.Bold = true; ExcelWorkSheet.Cells[1, 3].Font.Bold = true; ExcelWorkSheet.Cells[1, 4].Font.Bold = true; ExcelWorkSheet.Cells[1, 5].Font.Bold = true; for (int i = 0; i < rows; i++) { ExcelWorkSheet.Cells[i + 2, 1] = lBorder[i]; ExcelWorkSheet.Cells[i + 2, 2] = rBorder[i]; ExcelWorkSheet.Cells[i + 2, 3] = frequency[i]; ExcelWorkSheet.Cells[i + 2, 4] = accumulatedFrequency[i]; } ExcelWorkSheet.Cells[2, 5] = intervalControl.tbAverageValue.Text; ExcelWorkSheet.Cells[3, 5] = intervalControl.tbMode.Text; ExcelWorkSheet.Cells[4, 5] = intervalControl.tbMedian.Text; ExcelWorkSheet.Cells[5, 5] = intervalControl.tbRangeOfVariation.Text; ExcelWorkSheet.Cells[6, 5] = intervalControl.tbMeanLinearDeviation.Text; ExcelWorkSheet.Cells[7, 5] = intervalControl.tbDispersion.Text; ExcelWorkSheet.Cells[8, 5] = intervalControl.tbStandardDeviation.Text; ExcelWorkSheet.Cells[9, 5] = intervalControl.tbCoefficientVariation.Text; ExcelWorkSheet.Cells[10, 5] = intervalControl.tbNormalCoefficientAsymmetry.Text; ExcelWorkSheet.Cells[11, 5] = intervalControl.tbEstimationCoefficientAsymmetry.Text; ExcelWorkSheet.Cells[12, 5] = intervalControl.tbDegreeAsymmetry.Text; ExcelWorkSheet.Cells[13, 5] = intervalControl.tbMaterialityAsymmetry.Text; ExcelWorkSheet.Cells[14, 5] = intervalControl.tbExcess.Text; ExcelWorkSheet.Cells[15, 5] = intervalControl.tbExcessError.Text; ExcelWorkSheet.Columns.AutoFit(); ExcelWorkBook.SaveAs(filename); ExcelWorkBook.Close(); ExcelApp.Quit(); } else { dialogError.IsOpen = true; } }
private void HideDeletePage(ExcelApp.Application ExcelApp, IEnumerable <cParameter> ResPar, string[] DeletePages, string[] HidePages, StringBuilder pSuccess, StringBuilder pError, ref bool Result, string pPathCopy = null, bool IsShort = false) { ExcelApp.Workbook ExcelWorkBook; foreach (var el in ResPar) { ExcelWorkBook = ExcelApp.Workbooks.Open(el.FileName); if (IsShort) { // var s = Path.GetDirectoryName(el.FileName); string FN = Path.Combine(Path.GetDirectoryName(el.FileName), Path.GetFileNameWithoutExtension(el.FileName) + "_short" + Path.GetExtension(el.FileName)); el.FileName = FN; } if (DeletePages != null) { foreach (var page in DeletePages) { try { ExcelApp.Worksheet worksheet = (ExcelApp.Worksheet)ExcelWorkBook.Worksheets[page]; worksheet.Delete(); System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet); worksheet = null; } catch (Exception ex) { Result = false; var err = $"{DateTime.Now} Delete Page={page} {ex.Message}{Environment.NewLine}{Environment.StackTrace}{Environment.NewLine}"; pError.Append(err); pSuccess.Append(err); } } } //Ховаєм сторінки if (HidePages != null) { foreach (var page in HidePages) { try { ExcelApp.Worksheet worksheet = (ExcelApp.Worksheet)ExcelWorkBook.Worksheets[page]; worksheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden; System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet); worksheet = null; } catch (Exception ex) { Result = false; var err = $"{DateTime.Now} Hide Page={page} {ex.Message}{Environment.NewLine}{Environment.StackTrace}{Environment.NewLine}"; pError.Append(err); pSuccess.Append(err); } } } try { ExcelWorkBook.SaveAs(el.FileName); } catch (Exception ex) { Result = false; var err = $"{DateTime.Now} Save={el.FileName} {ex.Message}{Environment.NewLine}{Environment.StackTrace}{Environment.NewLine}"; pError.Append(err); pSuccess.Append(err); } if (!string.IsNullOrEmpty(pPathCopy)) { string NewFile = Path.Combine(pPathCopy, Path.GetFileName(el.FileName)); try { if (File.Exists(NewFile)) { File.Delete(NewFile); } ExcelWorkBook.SaveAs(NewFile); el.CopyFileName = NewFile; } catch (Exception ex) { Result = false; var err = $"{DateTime.Now} SaveCopy={NewFile} {ex.Message}{Environment.NewLine}{Environment.StackTrace}{Environment.NewLine}"; pError.Append(err); pSuccess.Append(err); } } if (ExcelWorkBook != null) { ExcelWorkBook.Close(false); } if (ExcelWorkBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkBook); ExcelWorkBook = null; } } }
private void SaveData_Click(object sender, EventArgs e) { try { Excel.Application ExcelApp = new Excel.Application(); Excel.Workbook ExcelWorkBook; Excel.Worksheet ExcelWorkSheet; Image image = Image.FromFile("C:\\Users\\admin\\Desktop\\lab3\\chart.jpeg"); //Построение книги ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value); //Построение таблицы ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); for (int i = 0; i < dataGridViewTable.Rows.Count; i++) { for (int j = 0; j < dataGridViewTable.ColumnCount; j++) { ExcelApp.Cells[i + 1, j + 1] = dataGridViewTable.Rows[i].Cells[j].Value; } } ExcelWorkSheet.Range["A2"].Value = image; ExcelApp.Visible = true; ExcelApp.UserControl = true; ExcelWorkBook = ExcelApp.Workbooks.Add(Type.Missing); ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.ActiveSheet; ExcelWorkBook.SaveAs(Path.Combine(Environment.CurrentDirectory, "C:\\Users\\admin\\Desktop")); } catch { MessageBox.Show("На вашем устройстве нельзя запустить Excel", "Ошибка вывода данных", MessageBoxButtons.OK, MessageBoxIcon.Error); } /*private void button1_Click(object sender, EventArgs e) * { * Excel.Application xlApp; * Excel.Workbook xlWorkBook; * Excel.Worksheet xlWorkSheet; * object misValue = System.Reflection.Missing.Value; * * xlApp = new Excel.Application(); * xlWorkBook = xlApp.Workbooks.Add(misValue); * xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); * * for (int i = 0; i < chart1.Series.Count; i++) * { * xlWorkSheet.Cells[1, 1] = ""; * xlWorkSheet.Cells[1, 2] = "DateTime";//put your column heading here * xlWorkSheet.Cells[1, 3] = "Data";// put your column heading here * * for (int j = 0; j < chart1.Series[i].Points.Count; j++) * { * xlWorkSheet.Cells[j + 2, 2] = chart1.Series[i].Points[j].XValue; * xlWorkSheet.Cells[j + 2, 3] = chart1.Series[i].Points[j].YValues[0]; * } * } * * Excel.Range chartRange; * * Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); * Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); * Excel.Chart chartPage = myChart.Chart; * * chartRange = xlWorkSheet.get_Range("B2", "c5");//update the range here * chartPage.SetSourceData(chartRange, misValue); * chartPage.ChartType = Excel.XlChartType.xlColumnClustered; * * xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); * xlWorkBook.Close(true, misValue, misValue); * xlApp.Quit(); * * releaseObject(xlWorkSheet); * releaseObject(xlWorkBook); * releaseObject(xlApp); * * MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls"); * } * * private void releaseObject(object obj) * { * try * { * System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); * obj = null; * } * catch (Exception ex) * { * obj = null; * MessageBox.Show("Exception Occured while releasing object " + ex.ToString()); * } * finally * { * GC.Collect(); * } * }*/ }