/* Экспорт данных из 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("Успешно сохранено!", "Успех");
}
}
}
static public void ShowNewTour(Tour newTour)
{
dataTours.Rows.Add(newTour._code.Value, newTour._name.Name, newTour._type.Name,
newTour._country.Name, newTour._visa.Value, newTour._hotel.Name,
newTour._transport.Name, newTour._food.Name, newTour._cost.Value, newTour._valuta.Name);
string filename = "C:\\Users\\Angela\\Documents\\visual studio 2015\\Projects\\turfirm\\turfirm\\bin\\Debug\\Tours.xlsx";
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.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 1] = newTour._code.Value;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 2] = newTour._name.Name;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 3] = newTour._type.Name;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 4] = newTour._country.Name;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 5] = newTour._visa.Value;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 6] = newTour._hotel.Name;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 7] = newTour._transport.Name;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 8] = newTour._food.Name;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 9] = newTour._cost.Value;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 10] = newTour._valuta.Name;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
public String ObtienePestañaEmpresa(ExcelWorkBook wb, string nombreEmpresa, int numeroHojas, string cuc)
{
string nombreSheet;
int rowIndex;
object[] row;
rowIndex = 13;//13
for (int i = 1; i <= numeroHojas; i++)
{
//ot6891 inicio
ExcelWorkSheet sheet = wb.GetSheet(i + 1);
//ot6891 fin
nombreSheet = sheet.getName();
if (nombreSheet.Equals(nombreEmpresa))
{
row = sheet.GetRow(rowIndex++, 'B', 'O');
while (row[0] != null)
{
if (row[0].ToString().Trim().Equals(cuc))
{
return(row[7].ToString().Trim());
}
row = sheet.GetRow(rowIndex++, 'B', 'O');
}
}
}
return("");
}
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);
}
public static void ReadData(ClientContext context)
{
Excel.Application ExcelApp;
Excel.Workbook ExcelWorkBook;
Excel.Worksheet ExcelWorkSheet;
Excel.Range ExcelRange;
ExcelApp = new Excel.Application();
ExcelWorkBook = ExcelApp.Workbooks.Open(@"D:\harsha853\SharePointAssessment.xlsx");
ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
int MaximumRows = ExcelWorkSheet.UsedRange.Rows.Count;
int MaximumColumns = ExcelWorkSheet.UsedRange.Columns.Count;
ExcelRange = ExcelWorkSheet.UsedRange;
string Reason;
string UploadStatus;
for (int Row = 2; Row < MaximumRows; Row++)
{
string FilePath = (ExcelRange.Cells[Row, 1] as Excel.Range).Value2;
string Status = (ExcelRange.Cells[Row, 2] as Excel.Range).Value2;
string CreatedBy = (ExcelRange.Cells[Row, 3] as Excel.Range).Value2;
string Department = (ExcelRange.Cells[Row, 6] as Excel.Range).Value2;
AddFilesFromExcel(context, FilePath, CreatedBy, Status, Department, out Reason);
UploadStatus = String.IsNullOrEmpty(Reason) ? "File Uploaded Successfully" : "Failed to Upload File";
ExcelRange.Cells[Row, 4] = UploadStatus;
ExcelRange.Cells[Row, 5] = Reason;
}
ExcelWorkBook.Save();
ExcelWorkBook.Close();
ExcelApp.Quit();
}
static public void DeleteManager(int code)
{
int j = 0;
for (int i = 0; i < dataManagers.Rows.Count; i++)
{
if (Convert.ToInt32(dataManagers[0, i].Value) == code)
{
j = i;
dataManagers.Rows.RemoveAt(i);
break;
}
}
string filename = "C:\\Users\\Angela\\Documents\\visual studio 2015\\Projects\\turfirm\\turfirm\\bin\\Debug\\Managers.xlsx";
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.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)ExcelWorkSheet.Rows[j + 1];
rg.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
ExcelWorkBook.Save();
ExcelApp.Quit();
}
static public void DeleteManager(int code)
{
int j = 0;
for (int i = 0; i < dataManagers.Rows.Count; i++)
{
if (Convert.ToInt32(dataManagers[0, i].Value) == code)
{
j = i;
dataManagers.Rows.RemoveAt(i);
break;
}
}
string filename = "E:\\Shop\\Данные\\Managers.xlsx";
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.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)ExcelWorkSheet.Rows[j + 1];
rg.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
ExcelWorkBook.Save();
ExcelApp.Quit();
}
/* Экспорт в *.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 void Close()
{
if (ExcelWorkBook != null)
{
ExcelWorkBook.Close();
}
if (ExcelApp != null)
{
ExcelApp.Quit();
}
}
public List<Data> ReadExcelWorkBook()
{
string filename = @"C:\Users\toth\Desktop\Kópia.xlsx";
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelBook = xlApp.Workbooks.Open(filename);
string[] excelSheets = new string[excelBook.Worksheets.Count];
int i = 0;
foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in excelBook.Worksheets)
{
excelSheets[i] = wSheet.Name;
i++;
}
string sheet = excelSheets[0] + "$";
excelBook.Close();
ExcelWorkBook workBook = new ExcelWorkBook();
List<Data> list = new List<Data>();
string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=YES;'";
using (OleDbConnection connection = new OleDbConnection(con))
{
connection.Open();
OleDbCommand command = new OleDbCommand("select * from [" + sheet+ "]", connection);
using (OleDbDataReader dr = command.ExecuteReader())
while (dr.Read())
{
if (dr[0].ToString() != "")
{
list.Add(new Data
{
oldCode = dr[0].ToString(),
newCode = Convert.ToInt32(dr[1]),
good = dr[2].ToString(),
mj = dr[3].ToString(),
quantity = Convert.ToInt32(dr[4]),
oldPrice = Convert.ToDouble(dr[5]),
newPrice = Convert.ToDouble(dr[7]),
EAN = Convert.ToInt64(dr[8])
}
);
}
}
connection.Close();
}
return list;
}
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);
}
/// <summary>
/// Metodo para leer archivo excel
/// </summary>
/// <param name="rutaArchivo"></param>
/// <returns></returns>
public ExcelData ProcesarArchivo(string rutaArchivo)
{
var excelData = new ExcelData();
var data = new ExcelWorkBook();
var workBook = (new ExcelReader()).ReadExcel(rutaArchivo);
if (!workBook.Equals(null))
{
var sheet = workBook.Sheets.FirstOrDefault();
if (!sheet.DataRows.Equals(null))
{
excelData = sheet;
}
}
return(excelData);
}
/* Экспорт ключей в *.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));
}
public static void Insert()
{
AlohaFly.DataExtension.DataCatalogsSingleton.Instance.DataCatalogsFill();
var ewb = new ExcelWorkBook();
var ws = ewb.GetWB(@"e:\t\1.xlsx");
if (ws == null)
{
return;
}
for (int row = 4; row < 73; row++)
{
long k = Convert.ToInt64(ws.Cells[row, 2].Value2.ToString());
long a = Convert.ToInt64(ws.Cells[row, 3].Value2.ToString());
var d = AlohaFly.DataExtension.DataCatalogsSingleton.Instance.Dishes.Where(b => b.Barcode == a).FirstOrDefault();
d.SHGastroId = k;
AlohaFly.DBProvider.Client.UpdateDish(d);
}
}
//Сохранить в 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);
}
static public void ShowNewManager(Manager newManager)
{
dataManagers.Rows.Add(newManager.Code, newManager.Name, newManager.Tel, newManager._tpy.Type, newManager.NameUser, newManager.hireDate);
string filename = "E:\\Shop\\Данные\\Managers.xlsx";
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.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 1] = newManager.Code;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 2] = newManager.Name;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 3] = newManager.Tel;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 4] = newManager._tpy.Type;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 5] = newManager.NameUser;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 6] = newManager.hireDate;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
static public void ShowNewClient(Client newClient)
{
dataClients.Rows.Add(newClient.Code, newClient.Name, newClient.Email, newClient.Tel, newClient.DateBd, newClient._tpy.Type);
string filename = "E:\\Shop\\Данные\\Clients.xlsx";
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.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 1] = newClient.Code;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 2] = newClient.Name;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 3] = newClient.Email;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 4] = newClient.Tel;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 5] = newClient.DateBd;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 6] = newClient._tpy.Type;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
static public void ShowNewManager(Manager newManager)
{
dataManagers.Rows.Add(newManager.Code, newManager.Name, newManager.Tel, newManager._tpy.Type, newManager.NameUser);
string filename = "C:\\Users\\Angela\\Documents\\visual studio 2015\\Projects\\turfirm\\turfirm\\bin\\Debug\\Managers.xlsx";
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.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 1] = newManager.Code;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 2] = newManager.Name;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 3] = newManager.Tel;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 4] = newManager._tpy.Type;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 5] = newManager.NameUser;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
/* Экспорт 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 public void ShowNewOrder(Order ord)
{
dataOrders.Rows.Add(ord.client, ord.manager, ord.DateOrder, ord.Code, ord._goods._name.Name);
string filename = "E:\\Shop\\Данные\\Orders.xlsx";
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.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 1] = ord.client;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 2] = ord.manager;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 3] = ord.DateOrder;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 4] = ord.Code;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 5] = ord._goods._name.Name;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
/// <summary>
///
/// </summary>
/// <param name="DGW"></param>
public void ExcelToDataGridView(DataGridView DGW)
{
int rCnt;
int cCnt;
try
{
OpenFileDialog opf = new OpenFileDialog();
opf.Title = "Open excel file";
opf.Filter = "Файл Excel|*.XLSX;*.XLS";
opf.ShowDialog();
System.Data.DataTable tb = new System.Data.DataTable();
string filename = opf.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;
Microsoft.Office.Interop.Excel.Range ExcelRange;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false,
false, 0, true, 1, 0);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelRange = ExcelWorkSheet.UsedRange;
for (rCnt = 2; rCnt <= ExcelRange.Rows.Count; rCnt++)
{
DGW.Rows.Add(1);
for (cCnt = 1; cCnt <= 2; cCnt++)
{
DGW.Rows[rCnt - 2].Cells[cCnt - 1].Value = ExcelApp.Cells[rCnt, cCnt].Value;
}
}
ExcelWorkBook.Close(true, null, null);
ExcelApp.Quit();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
throw;
}
}
static public void ShowNewOrder(Order ord)
{
dataOrders.Rows.Add(ord.client.Name, ord.manager.Name, ord.DateOrder, ord.Code, ord._tour._name.Name);
string filename = "C:\\Users\\Angela\\Documents\\visual studio 2015\\Projects\\turfirm\\turfirm\\bin\\Debug\\Orders.xlsx";
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.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 1] = ord.client.Name;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 2] = ord.manager.Name;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 3] = ord.DateOrder;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 4] = ord.Code;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 5] = ord._tour._name.Name;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
private void button5_Click(object sender, EventArgs e)
{
string str;
int rCnt;
int cCnt;
OpenFileDialog opf = new OpenFileDialog();
opf.Filter = "Excel (*.XLSX)|*.XLSX";
opf.ShowDialog();
System.Data.DataTable tb = new System.Data.DataTable();
string filename = opf.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;
Microsoft.Office.Interop.Excel.Range ExcelRange;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false,
false, 0, true, 1, 0);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelRange = ExcelWorkSheet.UsedRange;
for (rCnt = 1; rCnt <= ExcelRange.Rows.Count; rCnt++)
{
dataGridView4.Rows.Add(1);
for (cCnt = 1; cCnt <= 5; cCnt++)
{
str = (string)(ExcelRange.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Text;
dataGridView4.Rows[rCnt - 1].Cells[cCnt - 1].Value = str;
}
}
ExcelWorkBook.Close(true, null, null);
ExcelApp.Quit();
releaseObject(ExcelWorkSheet);
releaseObject(ExcelWorkBook);
releaseObject(ExcelApp);
}
static public void ShowNewClient(Client newClient)
{
dataClients.Rows.Add(newClient.Code, newClient.Name, newClient.Email, newClient.Tel, newClient.DateBd, newClient.Passport, newClient._tpy.Type);
string filename = "C:\\Users\\Angela\\Documents\\visual studio 2015\\Projects\\turfirm\\turfirm\\bin\\Debug\\Clients.xlsx";
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.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 1] = newClient.Code;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 2] = newClient.Name;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 3] = newClient.Email;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 4] = newClient.Tel;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 5] = newClient.DateBd;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 6] = newClient.Passport;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 7] = newClient._tpy.Type;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
/// <summary>
/// �w�肳�ꂽExcel�t�@�C����ǂݍ���
/// </summary>
private void ReadExcel()
{
//Excel�t�@�C���I����A�I�����Ȃ���ΏI���B
if (this.openFileExcel.ShowDialog() != DialogResult.OK)
{
return;
}
Address_DS ads = new Address_DS();
Address_DS errords = new Address_DS();
// ExcelWorkBook�I�[�v��
ExcelWorkBook wBook = new ExcelWorkBook();
wBook.Open(this.openFileExcel.FileName);
try
{
//�P�s�ڂ�ǂݍ���
ExcelWorkSheet sheet = wBook.WorkSheets[0];
ArrayList columnList = new ArrayList();
for (int col = sheet.FirstColumn; col <= sheet.LastColumn; col++)
{
if (sheet.Cells[0, col].Value == null)
{
continue;
}
columnList.Add(sheet.Cells[0, col].Value);
}
//�ǂݍ��ݍs���Ȃ��ꍇ�͋�̃f�[�^�Z�b�g��Ԃ��B
if (columnList.Count == 0)
{
MessageBox.Show(this, "�ǂݍ��݉\�ȍs������܂���B");
return;
}
using (ColumnSelectForm f = new ColumnSelectForm(columnList))
{
//�s�I���ʂ�\�����A���[�U�ɓǂݍ��ލs��w�肵�Ă�炤
if (f.ShowDialog() != DialogResult.OK)
{
//�I�����Ȃ���A��̃f�[�^�Z�b�g��Ԃ��B
return ;
}
int readStartRowIndex = 0;
if (f.ReadFirstRow == true) readStartRowIndex = 0;
else readStartRowIndex = 1;
//���[�U���w�肵�����ǂݍ���
for (int row = readStartRowIndex; row <= sheet.LastRow; row++)
{
//NULL�₷�łɎ�荞�܂ꂽ���[���A�h���X�͂͂����B
if (!((sheet.Cells[row, f.SelectColumnIndex].Value == null) ||
ads.Address.FindByMailAddress(sheet.Cells[row, f.SelectColumnIndex].Value.ToString()) != null))
{
string mailAddr = sheet.Cells[row, f.SelectColumnIndex].Value.ToString();
//���[���A�h���X�`���̃A�h���X�̂ݓo�^
if (TKMP.Writer.MailAddressCollection.IsAddressPattern(mailAddr) == true)
{
Address_DS.AddressRow aRow = ads.Address.NewAddressRow();
aRow.MailAddress = mailAddr;
ads.Address.AddAddressRow(aRow);
}
else
{
//���[���A�h���X�`���łȂ���̂́A�G���[���X�g�ɕۑ�
Address_DS.AddressRow aeRow = errords.Address.NewAddressRow();
aeRow.MailAddress = mailAddr;
errords.Address.AddAddressRow(aeRow);
}
}
}
}
}
finally
{
// ExcelWorkBook�N���[�Y
wBook.Close();
}
//Excel�t�@�C����ǂݎ��B
this._addressds.Merge(ads);
//�G���[����Z�b�g����B
this._errorForm.ErrorList = errords;
}
public void Procesar()
{
StringBuilder sbLinea;
sbLinea = new StringBuilder("");
CargaSegurosSuraDA da = new CargaSegurosSuraDA();
SqlConnection cn = GetConnection();
cn.Open();
SqlTransaction trans = cn.BeginTransaction(IsolationLevel.ReadUncommitted);
string path = sourcePath;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApplication excelApplication = new ExcelApplication();
ExcelWorkBook wb = excelApplication.OpenWorkBook(path, ExcelMode.Full);
try
{
// traer la cantidad de empresas atraves del campo codigo
ConveniosPlanEmpleadorDA conveniosPlanEmpleadorDA = new ConveniosPlanEmpleadorDA();
string codigosEmpresas = "COD_EMP_CON";
DataTable dtCodigosEmpresas = conveniosPlanEmpleadorDA.ObtenerTablaGeneral(codigosEmpresas);
int numeroEmpresas = dtCodigosEmpresas.Rows.Count;
object[] row;
int rowIndex;
string codigoParticipacion;
string nombreFondo;
DataRow[] drFondo;
DataTable dtFondosDepositos;
DataRow[] drFondoDeposito;
string idFondo;
string codigoTablatablaGeneralDepositos = "FONDO_EQUIV";
string mes = "";
string anio = "";
string estadoFondos = "ACT";
DataTable dtFondos = conveniosPlanEmpleadorDA.ListarFondos(estadoFondos);
CargaSegurosSuraDA cargaSegurosSuraDA = new CargaSegurosSuraDA();
if (numeroEmpresas == 0)
{
//no hay empresas
}
else
{
string periodo;
string[] periodoElementos;
string tipoMovimiento = "I";
string tipoAporte = ""; //"P"
double montoAporte;
string montoAporteCadena;
string nombreEmpresa;
bool existenRegistrosHoja = true;
string codigoEmpresa = "";
int grabo_registro = 0;
int numeroHojas = 0;
string parametro = "";
string constanteCP = "CP_";
int cantidadHojas = wb.GetSheetCount();
if (cantidadHojas == 3)
{
numeroHojas = wb.GetSheetCount();
}
else
{
numeroHojas = numeroEmpresas * 2 + 1;
}
//for (int i = 1; i <= numeroEmpresas; i++)
//{
for (int i = 1; i <= numeroHojas; i++)
{
ExcelWorkSheet sheet = wb.GetSheet(i);
// ExcelWorkSheet sheet = null;
nombreEmpresa = sheet.getName();
parametro = nombreEmpresa.Substring(0, 3);
StreamWriter sw = null;
//if (i == 1)
//{
// periodo = sheet.GetString("B8").Substring(4, 10);
// periodoElementos = periodo.Split('/');
// anio = periodoElementos[2].Trim();
// mes = periodoElementos[1].Trim();
//}
//codigoEmpresa = sheet.GetString("B1").Trim();
//DataTable dtDetalleConvenio = conveniosPlanEmpleadorDA.ObtenerDetalleConvenios(codigoEmpresa, Convert.ToInt32(mes), Convert.ToInt32(anio));
rowIndex = 13;//13
row = sheet.GetRow(rowIndex++, 'B', 'O');
int cont = 0;
while (row[0] != null & parametro != constanteCP)
{
try
{
ConvenioTD.ConvenioRow drConvenio = new ConvenioTD().Convenio.NewConvenioRow();
drConvenio.CUC = row[0].ToString();
drConvenio.MATRICULA = row[1] == null ? "" : row[1].ToString();
drConvenio.NOMBRE = row[2] == null ? "" : row[2].ToString();
drConvenio.NOMBRES = row[3].ToString();
drConvenio.APELLIDOS = row[4].ToString();
drConvenio.MONTO = Convert.ToDouble(row[5]);
drConvenio.FONDO = row[6].ToString();
drConvenio.ESTADO = row[7].ToString();
drConvenio.PARTICIPANTE = Convert.ToDouble(row[8]);
drConvenio.EMPLEADOR = Convert.ToDouble(row[9]);
drConvenio.ANTIGUEDAD = Convert.ToDouble(row[10]);
drConvenio.SALARIO_PROM = Convert.ToDouble(row[11]);
drConvenio.EDAD = Convert.ToDouble(row[12]);
drConvenio.BONO_ANUAL = Convert.ToDouble(row[13]);
cargaSegurosSuraDA.CargaRegistroConvenio(drConvenio, cn, trans);
row = sheet.GetRow(rowIndex++, 'B', 'O');
cont = cont + 1;
grabo_registro = grabo_registro + 1;
}
catch (Exception ex)
{
throw new Exception(string.Format("Error en la línea {0}. Detalle " + ex.Message, rowIndex - 1));
}
// trans.Commit();
}
if (cont > 0)
{
if (sw != null)
{
sw.Close();
}
}
existenRegistrosHoja = true;
}
if (grabo_registro > 0)
{
trans.Commit();
}
}
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
wb.Close();
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
excelApplication.Close();
}
}
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 void B_browse_Click(object sender, EventArgs e)
{
string str;
int rCnt;
int cCnt;
OpenFileDialog opf = new OpenFileDialog();
opf.Filter = "Файл Excel|*.XLSX;*.XLS";
if (opf.ShowDialog() == DialogResult.OK)
{
DataTable tb = new System.Data.DataTable();
string filename = opf.FileName;
Excel.Application ExcelApp = new Excel.Application();
Excel._Workbook ExcelWorkBook;
Excel.Worksheet ExcelWorkSheet;
Excel.Range ExcelRange;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false,
false, 0, true, 1, 0);
ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelRange = ExcelWorkSheet.UsedRange;
progressBar1.Maximum = ExcelRange.Rows.Count;
progressBar1.Value = 1;
dgv2.Visible = false;
dgv2.Rows.Clear();
for (rCnt = 2; rCnt <= ExcelRange.Rows.Count; rCnt++)
{
dgv2.Rows.Add(1);
for (cCnt = 1; cCnt <= 21; cCnt++)
{
str = (string)(ExcelRange.Cells[rCnt, cCnt] as Excel.Range).Text;
dgv2.Rows[rCnt - 2].Cells[0].Value = rCnt - 1;
if (cCnt == 11)
{
if (str.Contains("+") || str.ToLower().Contains("да") || str.ToLower().Contains("t"))
{
dgv2.Rows[rCnt - 2].Cells[cCnt].Value = true;
}
else
{
dgv2.Rows[rCnt - 2].Cells[cCnt].Value = false;
}
}
else
{
dgv2.Rows[rCnt - 2].Cells[cCnt].Value = str;
}
}
progressBar1.Value++;
}
dgv2.Visible = true;
MessageBox.Show($"Загружено {ExcelRange.Rows.Count - 1} записей!", "Внимание!");
ExcelWorkBook.Close(true, null, null);
ExcelApp.Quit();
releaseObject(ExcelWorkSheet);
releaseObject(ExcelWorkBook);
releaseObject(ExcelApp);
b_import.Enabled = true;
}
}
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);
}
}
