/* Экспорт данных из 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();
* }
* }*/
}
