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