public void Initialize() { mergeInstanceApplication = new Excel.Application(); // Create a new application for another excel to merge instance. excelApplication = new Excel.Application(); if (!File.Exists(this._fileName)) { excelWorkbook = excelApplication.Workbooks.Add(); } if (File.Exists(this._fileName)) { File.Copy(this._fileName, this._tmpFileName, true); excelWorkbook = excelApplication.Application.Workbooks.Open(_tmpFileName, ReadOnly: false); } excelApplication.DisplayAlerts = false; excelWorkbook = excelApplication.Workbooks[1]; excelWorkbook.Activate(); int worksheetNum = excelWorkbook.Worksheets.Count; excelWorksheet = excelWorkbook.Worksheets[worksheetNum]; excelWorksheet.Activate(); }
public void PreencherErro(string erro, string cpf, string convenio) { if (PlanilhaErros == null) { CriarSaidaErro(); Excel.Worksheet ws = PlanilhaErros.ActiveSheet; ws.Cells.NumberFormat = "@"; ws.Cells[1, 1] = "CPF"; ws.Cells[1, 2] = "Convênio"; ws.Cells[1, 3] = "Erro"; linhaErro = 2; } PlanilhaErros.Activate(); Excel.Worksheet ws2 = PlanilhaErros.ActiveSheet; ws2.Cells[linhaErro, 1] = cpf; ws2.Cells[linhaErro, 2] = convenio; ws2.Cells[linhaErro, 3] = erro; linhaErro++; SalvarPlanilhas(); }
internal static IReadOnlyCollection <Rate> ParsePZU(string excelFilePath) { var result = new HashSet <Rate>(); #region fix column count by re-save string fullExcelFilePath = Path.Combine(Directory.GetCurrentDirectory(), excelFilePath); ExcelInterop.Application xl = new ExcelInterop.Application(); ExcelInterop.Workbook wb = xl.Application.Workbooks.Open(fullExcelFilePath); wb.Activate(); wb.Save(); wb.Close(); xl.Quit(); #endregion FileStream stream = File.Open(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream); DataSet dataset = excelReader.AsDataSet(); bool firstRow = true; foreach (DataRow row in dataset.Tables[0].Rows) { if (firstRow) { firstRow = false; continue; } //-2 poniżej bo https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year DateTime courseDate = new DateTime(1900, 1, 1).AddDays(Convert.ToInt32(row[0]) - 2); result.Add(new Rate(courseDate, Convert.ToDecimal(row[1]))); } excelReader.Close(); return(result); }
private Microsoft.Office.Interop.Excel.Worksheet fGenerateExcel() { // insert new excel sheet to copy errors to it Microsoft.Office.Interop.Excel.Worksheet vWorkSheet = new Worksheet(); try { vWorkSheet = vWorkBook.Worksheets.Add(); vWorkSheet.Name = ("Errors" + DateTime.Now.Ticks.ToString()); vWorkBook.Activate(); if ((System.Threading.Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName == "ar")) { vWorkSheet.DisplayRightToLeft = true; } else if ((System.Threading.Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName == "en")) { vWorkSheet.DisplayRightToLeft = false; } vXLS.Visible = true; } catch (Exception ex) { // vProgress.sProgressOff() vWorkBook.Close(true); // sKillAllExcels() ExceptionHandler.HandleException(ex.Message, "cImportExcel", "fGenerateExcel"); } return(vWorkSheet); }
private bool checkValue(Excel.Workbook book, Excel.Worksheet sheet, String cell) { book.Activate(); if (book.ActiveSheet != sheet) { sheet.Select(); } Excel.Range test = sheet.get_Range(cell); try { sheet.Select(test); } catch (Exception e) { // apparently, an empty cell is not valid for selection. return(true); } try { if (test.Value2 == "" || test.Value2 == null) { return(false); } else { return(true); } } catch (Exception e) { return(false); } }
private void doOpen(CodeActivityContext context) { var readPassword = ReadPassword.Get(context); if (string.IsNullOrEmpty(readPassword)) { readPassword = null; } var writePassword = WritePassword.Get(context); if (string.IsNullOrEmpty(writePassword)) { writePassword = null; } if (!string.IsNullOrEmpty(filename)) { foreach (Microsoft.Office.Interop.Excel.Workbook w in officewrap.application.Workbooks) { if (w.FullName == filename) { try { workbook = w; worksheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; break; } catch (Exception) { workbook = null; } } } if (workbook == null) { officewrap.application.DisplayAlerts = false; //application.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow; if (System.IO.File.Exists(filename)) { workbook = officewrap.application.Workbooks.Open(filename, ReadOnly: false, Password: readPassword, WriteResPassword: writePassword); } else { if (!string.IsNullOrEmpty(filename)) { workbook = officewrap.application.Workbooks.Add(); workbook.Activate(); //workbook.SaveCopyAs(filename); workbook.SaveAs(Filename: filename); } } officewrap.application.DisplayAlerts = true; } } if (workbook == null) { workbook = officewrap.application.ActiveWorkbook; } }
private void btnInHD_Click(object sender, EventArgs e) { Excel.Application exApp = new Excel.Application(); Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1]; Excel.Range exRang = (Excel.Range)exSheet.Cells[1, 1]; exRang.Range["A1:A2"].Font.Bold = true; exRang.Range["A1:A2"].Font.Size = 14; exRang.Range["A1"].Value = "Địa chỉ: Ngõ 22 Cầu Giấy Hà Nội"; exRang.Range["A2"].Value = "Điện thoại: 098786765"; exRang.Range["C4"].Font.Bold = true; exRang.Range["C4"].Font.Size = 20; exRang.Range["C4"].Font.Color = Color.Red; exRang.Range["c4"].Value = "HÓA ĐƠN BÁN"; exRang.Range["A6"].Font.Size = 14; exRang.Range["A6"].Value = "Khách hàng:" + txtTenKhach.Text; exRang.Range["A8:G8"].Font.Bold = true; exRang.Range["A8"].Value = "STT"; exRang.Range["B8"].Value = "Mã hàng"; exRang.Range["C8"].Value = "Tên hàng"; exRang.Range["D8"].Value = "Số lượng"; exRang.Range["E8"].Value = "Đơn giá"; exRang.Range["F8"].Value = "Giảm giá"; exRang.Range["G8"].Value = "Thành tiền"; int row = 8; for (int i = 0; i < dgvChiTietHang.Rows.Count - 1; i++) { row = row + 1; exRang.Range["A" + row.ToString()].Value = (i + 1).ToString(); exRang.Range["B" + row.ToString()].Value = dgvChiTietHang.Rows[i].Cells[0].Value.ToString(); exRang.Range["C" + row.ToString()].Value = dgvChiTietHang.Rows[i].Cells[1].Value.ToString(); exRang.Range["D" + row.ToString()].Value = dgvChiTietHang.Rows[i].Cells[2].Value.ToString(); exRang.Range["E" + row.ToString()].Value = dgvChiTietHang.Rows[i].Cells[3].Value.ToString(); exRang.Range["F" + row.ToString()].Value = dgvChiTietHang.Rows[i].Cells[4].Value.ToString(); exRang.Range["G" + row.ToString()].Value = dgvChiTietHang.Rows[i].Cells[5].Value.ToString(); } exRang.Range["E" + (row + 2).ToString()].Value = "Tổng tiền: " + txtTongTien.Text; exSheet.Name = "HDBan"; exBook.Activate(); SaveFileDialog svFile = new SaveFileDialog(); if (svFile.ShowDialog() == DialogResult.OK) { exBook.SaveAs(svFile.FileName); } exApp.Quit(); }
private void BtnXuatFile_NCC_Click(object sender, EventArgs e) { Excel.Application exApp = new Excel.Application(); Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1]; Excel.Range exRang = (Excel.Range)exSheet.Cells[1, 1]; exRang.Range["A1:A2:A3:D1"].Font.Bold = true; exRang.Range["A1:A2:A3:D1"].Font.Size = 14; exRang.Range["A1"].Value = lb_TenCH.Text; exRang.Range["A2"].Value = lb_DC.Text; exRang.Range["A3"].Value = lb_SDT.Text; exRang.Range["D1"].Value = "Hà Nội Ngày: " + lbTime.Text; exRang.Range["C4"].Font.Bold = true; exRang.Range["C4"].Font.Size = 22; exRang.Range["C4"].Value = "Danh Sách Nhà Cung Cấp"; exRang.Range["C4"].Font.Color = Color.Red; exRang.Range["A6:F6"].Font.Bold = true; exRang.Range["A6:F6"].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; exRang.Range["B6:C6"].ColumnWidth = 20; exRang.Range["D6"].ColumnWidth = 10; exRang.Range["C6:E6"].ColumnWidth = 20; exRang.Range["A6"].Value = "STT"; exRang.Range["B6"].Value = "ID NCC"; exRang.Range["C6"].Value = "Tên"; exRang.Range["D6"].Value = "ĐịaChỉ"; exRang.Range["E6"].Value = "Số Điện Thoại"; int row = 6; for (int i = 0; i < dgvNhaCungCap.Rows.Count - 1; i++) { row++; exRang.Range["A" + row.ToString() + ":" + "E" + row.ToString()].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; exRang.Range["A" + row.ToString()].Value = (i + 1).ToString(); exRang.Range["B" + row.ToString()].Value = dgvNhaCungCap.Rows[i].Cells[0].Value.ToString(); exRang.Range["C" + row.ToString()].Value = dgvNhaCungCap.Rows[i].Cells[1].Value.ToString(); exRang.Range["D" + row.ToString()].Value = dgvNhaCungCap.Rows[i].Cells[2].Value.ToString(); exRang.Range["E" + row.ToString()].Value = "SDT: " + dgvNhaCungCap.Rows[i].Cells[3].Value.ToString(); } row = row + 2; exRang.Range["E" + row.ToString()].Font.Bold = true; exRang.Range["E" + row.ToString()].Value = "Tổng Số NCC: " + (dgvNhaCungCap.Rows.Count - 1); exSheet.Name = "Nhà Cung Cấp"; exBook.Activate(); SaveFileDialog svFile = new SaveFileDialog(); if (svFile.ShowDialog() == DialogResult.OK) { exBook.SaveAs(svFile.FileName); MessageBox.Show("Đã xuất file thành công.", "Chúc Mừng", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); } exApp.Quit(); }
private void button1_Click(object sender, EventArgs e) { Excel.Application exApp = new Excel.Application(); Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1]; Excel.Range exRang = (Excel.Range)exSheet.Cells[1, 1]; exRang.Range["A1:A2"].Font.Bold = true; exRang.Range["A1:A2"].Font.Size = 14; exRang.Range["A1"].Value = "Địa chỉ số 236 Hoàng Quốc Việt"; exRang.Range["A2"].Value = "Số điện thoại: 0123566789 "; exRang.Range["C4"].Font.Bold = true; exRang.Range["C4"].Font.Size = 22; exRang.Range["C4"].Value = "DANH SÁCH ĐỘC GIẢ "; exRang.Range["C4"].Font.Color = Color.Red; exRang.Range["A6:F6"].Font.Bold = true; exRang.Range["A6:F6"].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; exRang.Range["B6:C6"].ColumnWidth = 20; exRang.Range["D6"].ColumnWidth = 30; exRang.Range["E6:G6"].ColumnWidth = 20; exRang.Range["A6"].Value = "STT"; exRang.Range["B6"].Value = "Mã Độc Giả"; exRang.Range["C6"].Value = "Tên Độc Giả"; exRang.Range["D6"].Value = "Ngày Sinh"; exRang.Range["E6"].Value = "Giới Tính"; exRang.Range["F6"].Value = "Địa Chỉ"; exRang.Range["G6"].Value = "Lớp"; int row = 7; for (int i = 0; i < dgvDocGia.Rows.Count - 1; i++) { row++; exRang.Range["A" + row.ToString()].Value = (i + 1).ToString(); exRang.Range["B" + row.ToString()].Value = dgvDocGia.Rows[i].Cells[0].Value.ToString(); exRang.Range["C" + row.ToString()].Value = dgvDocGia.Rows[i].Cells[1].Value.ToString(); exRang.Range["D" + row.ToString()].Value = dgvDocGia.Rows[i].Cells[2].Value.ToString(); exRang.Range["E" + row.ToString()].Value = dgvDocGia.Rows[i].Cells[3].Value.ToString(); exRang.Range["F" + row.ToString()].Value = dgvDocGia.Rows[i].Cells[4].Value.ToString(); exRang.Range["G" + row.ToString()].Value = dgvDocGia.Rows[i].Cells[5].Value.ToString(); } exBook.Activate(); SaveFileDialog svFile = new SaveFileDialog(); if (svFile.ShowDialog() == DialogResult.OK) { exBook.SaveAs(svFile.FileName); MessageBox.Show("Đã xuất file thành công"); } exApp.Quit(); }
public void consolidarArchivos(string ruta1, string ruta2, string nombre1, string nombre2) { Excel.Application miExcel = new Excel.Application(); miExcel.Visible = true; miExcel.DisplayAlerts = false; Excel.Workbook libro = miExcel.Workbooks.Open(ruta1); ((Excel.Worksheet)miExcel.Sheets[1]).Select(); Excel.Worksheet SheetExcel = (Excel.Worksheet)libro.ActiveSheet; SheetExcel.Columns.EntireColumn.AutoFit(); Excel.Workbook librof = miExcel.Workbooks.Add(); ((Excel.Worksheet)miExcel.Sheets[1]).Select(); Excel.Worksheet PestañaF = (Excel.Worksheet)librof.ActiveSheet; Excel.Range rango = PestañaF.get_Range("A:Z"); rango.Select(); rango.NumberFormat = "@"; copiarArchivos(SheetExcel, PestañaF); PestañaF.Name = nombre1; PestañaF.Columns.EntireColumn.AutoFit(); rango = PestañaF.get_Range("1:1"); rango.Select(); rango.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent2; rango.Font.ThemeColor = Excel.XlThemeColor.xlThemeColorDark1; rango.Font.Bold = true; libro.Close(); libro = miExcel.Workbooks.Open(ruta2); ((Excel.Worksheet)miExcel.Sheets[1]).Select(); SheetExcel = (Excel.Worksheet)libro.ActiveSheet; SheetExcel.Columns.EntireColumn.AutoFit(); librof.Worksheets.Add(); librof.Activate(); ((Excel.Worksheet)miExcel.Sheets[1]).Select(); PestañaF = (Excel.Worksheet)librof.ActiveSheet; rango = PestañaF.get_Range("A:Z"); rango.Select(); rango.NumberFormat = "@"; copiarArchivos(SheetExcel, PestañaF); PestañaF.Name = nombre2; PestañaF.Columns.EntireColumn.AutoFit(); rango = PestañaF.get_Range("1:1"); rango.Select(); rango.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent2; rango.Font.ThemeColor = Excel.XlThemeColor.xlThemeColorDark1; rango.Font.Bold = true; libro.Close(); if (!Directory.Exists(rutaEscritorio + @"\Archivos Generados\")) { Directory.CreateDirectory(rutaEscritorio + @"\Archivos Generados\"); } string nombre = "Chedraui Estado de Cuenta " + nombreAleatorio() + ".xlsx"; librof.SaveAs(rutaEscritorio + @"\Archivos Generados\" + nombre); File.Delete(ruta1); File.Delete(ruta2); }
private void toCsv(string dPath, string xPath) { int i = 1; string src; //Launch Excel App xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlApp.Visible = false; //open Workbook Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(xPath); xlWorkbook.Activate(); sheetName = new string[xlWorkbook.Worksheets.Count]; //Convert to csv (1 sheet => 1 .csv) foreach (Excel.Worksheet sheet in xlWorkbook.Worksheets) { sheet.Select(); sheetName[i - 1] = sheet.Name; src = System.IO.Directory.GetCurrentDirectory() + "\\Sheet_" + i.ToString() + ".csv"; xlWorkbook.SaveAs(src, Excel.XlFileFormat.xlCSV); srcList.Add(src); i++; } //Clean all Interop shits foreach (Excel.Worksheet sheet in xlWorkbook.Sheets) { while (Marshal.ReleaseComObject(sheet) != 0) { } } xlWorkbook.Close(false); xlApp.Quit(); while (Marshal.ReleaseComObject(xlApp) != 0) { } while (Marshal.ReleaseComObject(xlWorkbook) != 0) { } xlWorkbook = null; xlApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.WaitForPendingFinalizers(); }
public bool LoadXLS(string strFileName) { fileName = strFileName; string nakedFileName = System.IO.Path.GetFileName(strFileName); Excel.Application app = new Excel.Application(); app.Workbooks.Open(strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); Excel.Workbook workBook = app.Workbooks[1]; workBook.Activate(); Excel.Worksheet sheet = (Excel.Worksheet)workBook.Sheets[1]; pgbCurrent.Minimum = 2; pgbCurrent.Maximum = sheet.UsedRange.Rows.Count; pgbCurrent.Value = 2; int rowCont = sheet.UsedRange.Rows.Count; MH4Global.lbXLS.Text = "Loading and Converting... : " + nakedFileName; MH4Global.lbXLS.Update(); // 일반 퀘스트 문서인지, 스폐셜 퀘스트 문서인지 파악한다. bool bIsSpecialXLS = MH4Global.IsSpecialQuestXLS(strFileName); Thread[] trdList = new Thread[MH4Global.THREAD_MAX]; for (int i = 0; i < MH4Global.THREAD_MAX; ++i) { ThreadTask task = new ThreadTask(dataBase, i + 2, rowCont + 1, MH4Global.THREAD_MAX, sheet.UsedRange); if (bIsSpecialXLS) { trdList[i] = new Thread(task.LoadXLSSpecial); } else { trdList[i] = new Thread(task.LoadXLS); } trdList[i].IsBackground = true; trdList[i].Start(); } for (int i = 0; i < MH4Global.THREAD_MAX; ++i) { trdList[i].Join(); } app.ActiveWorkbook.Close(false, Missing.Value, Missing.Value); return(true); }
/// <summary> /// Gets a list of all column headers /// </summary> /// <returns>A list of all row 1 column contents</returns> internal MSExcel.Range GetColumnHeaders() { try { _wkBook.Activate(); _wkSheet.Select(false); _colRange = (MSExcel.Range)_wkSheet.Rows[1]; return(_colRange); } catch (Exception) { Console.WriteLine("Cannot get requested colum headers."); return(null); } }
/// <summary> /// Abstract base method for selecting a worksheet in an open workbook /// </summary> /// <param name="_sheet">The name of the worksheet to select</param> /// <returns>A reference to the worksheet requested</returns> public virtual MSExcel.Worksheet SelectWorksheet(string _sheet) { try { _wkBook.Activate(); _wkSheet = (MSExcel.Worksheet)_app.Worksheets[_sheet]; _wkSheet.Select(false); return(_wkSheet); } catch (Exception) { Console.WriteLine("Cannot select requested worksheet."); return(null); } }
private void button1_Click(object sender, EventArgs e) { Client.Unsubscribe(new String[] { "dkesp32" }); Excel.Application app = new Excel.Application(); Excel.Workbook workbook = app.Workbooks.Open("D:/quanlynhansu-EXCEL.xlsx"); Excel.Worksheet xlWorksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1); workbook.Activate(); xlWorksheet.Activate(); app.Visible = true; app.DisplayAlerts = false; //Environment.Exit(1); }
public override object OpenFile(ISiteSetting siteSetting, string filePath) { // set the file name from the open file dialog string fileName = filePath; object readOnly = false; object isVisible = true; // Here is the way to handle parameters you don't care about in .NET object missing = Type.Missing; // System.Reflection.Missing.Value; // Make Excel visible, so you can see what's happening // Open the document that was chosen by the dialog Excel.Workbook doc = this.ExcelApplication.Workbooks.Open(fileName, missing, readOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); // Activate the document so it shows up in front doc.Activate(); return(doc); }
private void ExportButton_Click(object sender, RoutedEventArgs e) { Excel.Application excel = new Excel.Application(); excel.Visible = false; Excel.Workbook workbook = excel.Workbooks.Add(Missing.Value); workbook.Activate(); Excel.Worksheet sheet1 = (Excel.Worksheet)workbook.Sheets[1]; for (int j = 0; j < OrdersGrid.Columns.Count; j++) { Excel.Range myRange = (Excel.Range)sheet1.Cells[1, j + 1]; sheet1.Cells[1, j + 1].Font.Bold = true; sheet1.Columns[j + 1].ColumnWidth = 15; myRange.Value2 = OrdersGrid.Columns[j].Header; } for (int i = 0; i < OrdersGrid.Columns.Count - 1; i++) { for (int j = 0; j < OrdersGrid.Items.Count; j++) { TextBlock b = OrdersGrid.Columns[i].GetCellContent(OrdersGrid.Items[j]) as TextBlock; if (b.Text == null) { break; } Excel.Range myRange = (Excel.Range)sheet1.Cells[j + 2, i + 1]; myRange.Value2 = b.Text; } } SaveFileDialog savefile = new SaveFileDialog(); savefile.Filter = "Excel Documents (*.xlsx)|*.xlsx"; savefile.FileName = $"AllOrders_{DateTime.Now.Day.ToString()}.{DateTime.Now.Month}.{DateTime.Now.Year}_{DateTime.Now.Hour}.{DateTime.Now.Minute}.xlsx"; if (savefile.ShowDialog() == false) { return; } string filename = savefile.FileName; excel.DisplayAlerts = false; workbook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workbook.Close(); excel.Quit(); MessageBox.Show("File Created!"); }
public override void OpenNewFile(string templatePath) { // set the file name from the open file dialog string fileName = templatePath; object readOnly = false; object isVisible = true; // Here is the way to handle parameters you don't care about in .NET object missing = System.Reflection.Missing.Value; // Make Excel visible, so you can see what's happening // Open the document that was chosen by the dialog Excel.Workbook doc = this.ExcelApplication.Workbooks.Add(fileName); // SetProperty(doc, "Path", "http://demo.sobiens.com/Sobiens.Connectors/Shared Documents"); // doc.Path = ""; // , ref missing, ref readOnly, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref isVisible); // Activate the document so it shows up in front doc.Activate(); }
private void btnDesactivar_Click(object sender, RibbonControlEventArgs e) { // el nombre de una Key debe incluir un root valido. Cursor.Current = Cursors.WaitCursor; const string userRoot = "HKEY_CURRENT_USER"; const string subkey = "Software\\Microsoft\\Office\\Excel\\Addins\\SAT.Dictamenes.SIPRED.Client"; const string keyName = userRoot + "\\" + subkey; object addInName = "SAT.Dictamenes.SIPRED.Client"; Registry.SetValue(keyName, "LoadBehavior", 0); Office.COMAddIn addIn = Globals.ThisAddIn.Application.COMAddIns.Item(ref addInName); addIn.Connect = false; MessageBox.Show("El AddIn [SAT] quedó deshabilitado", "AddIn SAT", MessageBoxButtons.OK, MessageBoxIcon.Information); Excel.Workbook nwbook = Globals.ThisAddIn.Application.Workbooks.Add(System.Type.Missing); nwbook.Activate(); Cursor.Current = Cursors.Default; }
public static void ConvertExcelToPdf(string excelFileIn, string pdfFileOut) { msExcel.Application excel = new msExcel.Application(); try { excel.Visible = false; excel.ScreenUpdating = false; excel.DisplayAlerts = false; FileInfo excelFile = new FileInfo(excelFileIn); string filename = excelFile.FullName; msExcel.Workbook wbk = excel.Workbooks.Open(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); wbk.Activate(); object outputFileName = wbk.FullName.Replace(".xslx", ".pdf");; msExcel.XlFixedFormatType fileFormat = msExcel.XlFixedFormatType.xlTypePDF; // Save document into PDF Format wbk.ExportAsFixedFormat(fileFormat, outputFileName, missing, missing, missing, missing, missing, missing, missing); object saveChanges = msExcel.XlSaveAction.xlDoNotSaveChanges; ((msExcel._Workbook)wbk).Close(saveChanges, missing, missing); wbk = null; } catch (Exception e) { Console.WriteLine(e); } finally { ((msExcel._Application)excel).Quit(); excel = null; } }
public void ColarDados(List <string[]> Tabela, string mensagem) { if (PlanilhaContratos == null) { CriarSaida(); Excel.Worksheet ws = PlanilhaContratos.ActiveSheet; ws.Cells.NumberFormat = "@"; ws.Cells[1, 1] = "CPF"; ws.Cells[1, 2] = "Origem Contrato"; ws.Cells[1, 3] = "Contrato"; ws.Cells[1, 4] = "Matrícula"; ws.Cells[1, 5] = "Data de Nascimento"; ws.Cells[1, 6] = "Órgão/Filial"; ws.Cells[1, 7] = "Saldo p/ Refin.(*)"; ws.Cells[1, 8] = "Parc. Refin. (*)"; ws.Cells[1, 9] = "Parc. Abertas"; ws.Cells[1, 10] = "Prz. do Contrato"; ws.Cells[1, 11] = "Vlr Parcela (R$)"; ws.Cells[1, 12] = "% Parc. Pagas"; ws.Cells[1, 13] = "Ação"; ws.Cells[1, 14] = "Mensagem"; linhaColada = 2; } PlanilhaContratos.Activate(); Excel.Worksheet ws2 = PlanilhaContratos.ActiveSheet; foreach (var linha in Tabela) { for (int coluna = 0; coluna < linha.Length; coluna++) { ws2.Cells[linhaColada, coluna + 1] = linha[coluna]; } ws2.Cells[linhaColada, 14] = mensagem; linhaColada++; } SalvarPlanilhas(); }
/// <summary> /// Opens the original word file and initializes the Word.Document object. /// The original file may be HTML, DOC, or RTF /// </summary> private void OpenDocument() { object missing = System.Reflection.Missing.Value; // doesn't update any reference links object updateLinks = 0; object readOnly = true; // not display the read-only recommended message object ignoreReadOnlyRecommended = true; //We could pass in a real password for these two fields, but currently //there is no way to pass in a document specific password in System4 object password = missing; //for write-reserved workbook object writeResPassword = password; m_workbook = m_excelApp.Workbooks.Open( m_originalFileName, updateLinks, readOnly, missing, password, writeResPassword, ignoreReadOnlyRecommended, missing, missing, missing, missing, missing, missing, missing, missing); m_workbook.Activate(); m_workbook.WebOptions.Encoding = Office_Core.MsoEncoding.msoEncodingUTF8; }
private void BtnExcel_Click(object sender, EventArgs e) { if (dt != null) //TH co du lieu de ghi { Excel.Application exApp = new Excel.Application(); Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1]; //Dinh dang chung Excel.Range tenExcel = (Excel.Range)exSheet.Cells[1, 1]; tenExcel.Font.Size = 14; tenExcel.Font.Bold = true; tenExcel.Font.Color = Color.Blue; tenExcel.Value = "Form Chi Tiết Nhân Viên"; //Dinh dang tieu de excel exSheet.get_Range("A2:E2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; Excel.Range header1 = (Excel.Range)exSheet.Cells[2, 1]; exSheet.get_Range("A2:B2").Merge(true); header1.Font.Bold = true; header1.Font.Color = Color.Yellow; header1.Value = "Trong SQL "; Excel.Range header2 = (Excel.Range)exSheet.Cells[2, 4]; exSheet.get_Range("D2:E2").Merge(true); header2.Font.Bold = true; header2.Font.Color = Color.Red; header2.Value = "Trong Form "; //Nhap thong so trong datagridview for (int i = 0; i < dt.Columns.Count; i++) { char ascii = (char)65; //A=65 exSheet.get_Range(ascii + (i + 3).ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; exSheet.get_Range(ascii + (i + 3).ToString()).Font.Bold = true; exSheet.get_Range(ascii + (i + 3).ToString()).Value = dt.Columns[i].ColumnName.ToString(); exSheet.get_Range(ascii + (i + 3).ToString()).ColumnWidth = 15; ascii++; exSheet.get_Range(ascii + (i + 3).ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; exSheet.get_Range(ascii + (i + 3).ToString()).Value = dt.Rows[0][dt.Columns[i].ColumnName.ToString()].ToString(); exSheet.get_Range(ascii + (i + 3).ToString()).ColumnWidth = 15; } exSheet.get_Range("D3:D12").Font.Bold = true; exSheet.get_Range("D3:E12").HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; exSheet.get_Range("D3:E12").ColumnWidth = 15; exSheet.get_Range("D3").Value = "Mã Nhân Viên"; exSheet.get_Range("E3").Value = txbMaNV.Text; exSheet.get_Range("D4").Value = "Tên Nhân Viên"; exSheet.get_Range("E4").Value = txbTenNV.Text; exSheet.get_Range("D5").Value = "Giới Tính"; exSheet.get_Range("E5").Value = cbGioiTinh.Text; exSheet.get_Range("D6").Value = "Ngày Sinh"; exSheet.get_Range("E6").Value = dtpNgaySinh.Text; exSheet.get_Range("D7").Value = "Điện Thoại"; exSheet.get_Range("E7").Value = txbDienThoai.Text; exSheet.get_Range("D8").Value = "Địa Chỉ"; exSheet.get_Range("E8").Value = txbDiaChi.Text; exSheet.get_Range("D10").Value = "Mã Công Việc"; exSheet.get_Range("E10").Value = cbChonMaCV.Text; exSheet.get_Range("D11").Value = "Công Việc"; exSheet.get_Range("E11").Value = txbCongViec.Text; exSheet.get_Range("D12").Value = "Mức Lương"; exSheet.get_Range("E12").Value = txbMucLuong.Text; exSheet.get_Range("D3:E12").BorderAround2(); exSheet.Name = "Nhân Viên"; exBook.Activate(); //kich hoat file excel //Thiet lap thuoc tinh savefiledialog //saveExcel.Filter = "Excel Document(*.xls)"; //saveExcel.FilterIndex = 0; //saveExcel.AddExtension = true; saveExcel.DefaultExt = ".xls"; if (saveExcel.ShowDialog() == DialogResult.OK) { exBook.SaveAs(saveExcel.FileName.ToString()); //Luu file Excel exApp.Visible = true; } else { exApp.Quit(); } } else { MessageBox.Show("Không có danh sách để in"); } }
protected override void Execute(NativeActivityContext context) { var readPassword = ReadPassword.Get(context); if (string.IsNullOrEmpty(readPassword)) { readPassword = null; } var writePassword = WritePassword.Get(context); if (string.IsNullOrEmpty(writePassword)) { writePassword = null; } filename = Filename.Get(context); officewrap.application.Visible = true; // officewrap.application.Visible = Visible.Get(context); if (!string.IsNullOrEmpty(filename)) { filename = Environment.ExpandEnvironmentVariables(filename); } workbook = (Workbook != null ? Workbook.Get(context) : null); if (!string.IsNullOrEmpty(filename) && workbook != null) { if (workbook.FullName.ToLower() != filename.ToLower()) { try { workbook.Close(true); workbook = null; Task.Run(() => { GC.Collect(); }); } catch (Exception) { throw; } } } if (!string.IsNullOrEmpty(filename) && workbook == null) { foreach (Microsoft.Office.Interop.Excel.Workbook w in officewrap.application.Workbooks) { if (w.FullName == filename) { workbook = w; break; } } if (workbook == null) { officewrap.application.DisplayAlerts = false; //application.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow; if (System.IO.File.Exists(filename)) { //workbook = officewrap.application.Workbooks.Open(filename, ReadOnly: false); workbook = officewrap.application.Workbooks.Open(filename, ReadOnly: false, Password: readPassword, WriteResPassword: writePassword); } else { workbook = officewrap.application.Workbooks.Add(); workbook.Activate(); //workbook.SaveCopyAs(filename); workbook.SaveAs(Filename: filename); } officewrap.application.DisplayAlerts = true; } } if (workbook == null) { workbook = officewrap.application.ActiveWorkbook; } if (workbook == null) { } var _worksheet = (Worksheet != null ? Worksheet.Get(context) : null); worksheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; if (!string.IsNullOrEmpty(_worksheet)) { foreach (object obj in workbook.Sheets) { Worksheet s = obj as Worksheet; if (s != null && s.Name == _worksheet) { s.Activate(); worksheet = s; } } } var sheetPassword = SheetPassword.Get(context); if (string.IsNullOrEmpty(sheetPassword)) { sheetPassword = null; } if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null) { worksheet.Unprotect(sheetPassword); } //Application.Set(context, application); Workbook.Set(context, workbook); }
public void SaveCoordinates(int offset, string injectString) { String currentFileName = imageFiles[pCurrentImage]; string ExcelFile = Directory.GetCurrentDirectory() + "\\YourWorkbook.xlsx"; xlApp.Visible = false; xlApp.DisplayAlerts = false; if (System.IO.File.Exists(ExcelFile) == false) { xlApp.Workbooks.Add(Type.Missing); aBook1 = xlApp.Workbooks[1]; aBook1.Activate(); } else { aBook1 = xlApp.Workbooks.Open(ExcelFile); } aSheet1 = (excel.Worksheet)aBook1.Sheets[1]; aRange = (excel.Range)aSheet1.Cells[1, 1]; aRange.Value2 = "FileName"; aRange = (excel.Range)aSheet1.Cells[1, 1 + offset]; aRange.Value2 = "Point" + offset; excel.Range last = aSheet1.Cells.SpecialCells(excel.XlCellType.xlCellTypeLastCell, Type.Missing); excel.Range range = aSheet1.get_Range("A1", last); int lastUsedRow = last.Row; int lastUsedColumn = last.Column; if (offset == 1) { lastUsedRow++; } System.Console.WriteLine("last row {0} ,offset {1}", aSheet1.UsedRange.Rows.Count, offset); aRange = (excel.Range)aSheet1.Cells[lastUsedRow, 1]; aRange.Value2 = Path.GetFileName(currentFileName); aRange = (excel.Range)aSheet1.Cells[lastUsedRow, ++offset]; aRange.Value2 = injectString; aSheet1.Application.DisplayAlerts = false; aSheet1.Application.AlertBeforeOverwriting = false; if (System.IO.File.Exists(ExcelFile) == false) { aBook1.SaveAs(ExcelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } else { aBook1.Save(); } releaseObject(aSheet1); releaseObject(aBook1); releaseObject(xlApp); xlApp.Quit(); }
private void exportToExcel() { Excel.Application exApp = new Excel.Application(); Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1]; try { // dinh dang chung Excel.Range tenCuaHang = (Excel.Range)exSheet.Cells[1, 1]; tenCuaHang.Font.Size = 18; tenCuaHang.Font.Bold = true; tenCuaHang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; tenCuaHang.Value = "CỬA HÀNG LINH KIỆN ĐIỆN TỬ PTH"; exSheet.Range["A1", "F1"].Merge(true); Excel.Range dcCuahang = (Excel.Range)exSheet.Cells[3, 1]; //dcCuahang.WrapText = true; dcCuahang.Font.Size = 12; dcCuahang.Value = "Địa chỉ: Trường ĐH GTVT"; exSheet.Range["A3", "C3"].Merge(true); Excel.Range dcCuahang2 = (Excel.Range)exSheet.Cells[4, 1]; //dcCuahang.WrapText = true; dcCuahang2.Font.Size = 12; dcCuahang2.Value = " Khoa CNTT"; exSheet.Range["A4", "C4"].Merge(true); Excel.Range dtCuahang = (Excel.Range)exSheet.Cells[5, 1]; dtCuahang.Font.Size = 12; dtCuahang.Font.Color = Color.Black; dtCuahang.Value = "SĐT: 0123.456.789"; exSheet.Range["A5", "C5"].Merge(true); Excel.Range header = (Excel.Range)exSheet.Cells[7, 1]; exSheet.Range["A7", "F7"].Merge(true); header.Font.Size = 13; header.Font.Bold = true; header.Value = "HÓA ĐƠN THANH TOÁN"; header.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // dinh dang tieu de bang exSheet.Range["A10", "G10"].Font.Bold = true; exSheet.Range["A10", "G10"].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; exSheet.Range["A10"].Value = "STT"; exSheet.Range["B10"].Value = "Tên sản phẩm"; exSheet.Range["C10"].Value = "Đơn vị tính"; exSheet.Range["D10"].Value = "Số lượng"; exSheet.Range["E10"].Value = "Đơn giá"; exSheet.Range["F10"].Value = "Thành tiền"; exSheet.Range["A10"].ColumnWidth = 4; exSheet.Range["B10"].ColumnWidth = 28; exSheet.Range["C10"].ColumnWidth = 10; exSheet.Range["D10"].ColumnWidth = 15; exSheet.Range["E10"].ColumnWidth = 10; exSheet.Range["F10"].ColumnWidth = 10; // Dinh dang ngay in Excel.Range ngay = (Excel.Range)exSheet.Cells[8, 4]; ngay.Font.Size = 12; ngay.Font.Color = Color.Black; ngay.Font.Bold = true; ngay.Value = "Ngày xuất: " + dateTimePicker1.Text; exSheet.Range["D8", "F8"].Merge(true); // Dinh dang so hoa don Excel.Range sohd = (Excel.Range)exSheet.Cells[8, 1]; ngay.Font.Size = 12; ngay.Font.Color = Color.Black; ngay.Font.Bold = true; ngay.Value = "Số HĐ: " + txtBillID.Text; exSheet.Range["A8", "B8"].Merge(true); // In du lieu for (int i = 0; i < dgvData.Rows.Count - 1; i++) { exSheet.Range["A" + (i + 11).ToString(), "F" + (i + 11).ToString()].Font.Bold = false; exSheet.Range["A" + (i + 11).ToString()].Value = dgvData.Rows[i].Cells["order"].Value.ToString(); exSheet.Range["B" + (i + 11).ToString()].Value = dgvData.Rows[i].Cells["name"].Value.ToString(); exSheet.Range["C" + (i + 11).ToString()].Value = dgvData.Rows[i].Cells["unit"].Value.ToString(); exSheet.Range["D" + (i + 11).ToString()].Value = dgvData.Rows[i].Cells["outPrice"].Value.ToString(); exSheet.Range["E" + (i + 11).ToString()].Value = dgvData.Rows[i].Cells["num"].Value.ToString(); exSheet.Range["F" + (i + 11).ToString()].Value = dgvData.Rows[i].Cells["total"].Value.ToString(); } // In cot tong tien exSheet.Range["A" + (10 + dgvData.Rows.Count).ToString(), "F" + (10 + dgvData.Rows.Count).ToString()].Font.Bold = true; exSheet.Range["A" + (10 + dgvData.Rows.Count).ToString()].Value = "Tiền hàng"; exSheet.Range["E" + (10 + dgvData.Rows.Count).ToString()].Value = lbTotalPrice.Text; // Tổng ck exSheet.Range["A" + (11 + dgvData.Rows.Count).ToString(), "F" + (10 + dgvData.Rows.Count).ToString()].Font.Bold = true; exSheet.Range["A" + (11 + dgvData.Rows.Count).ToString()].Value = "Chiết khấu"; exSheet.Range["E" + (11 + dgvData.Rows.Count).ToString()].Value = lbDiscount.Text; // Tiền phải trả exSheet.Range["A" + (12 + dgvData.Rows.Count).ToString(), "F" + (10 + dgvData.Rows.Count).ToString()].Font.Bold = true; exSheet.Range["A" + (12 + dgvData.Rows.Count).ToString()].Value = "Thành tiền"; exSheet.Range["E" + (12 + dgvData.Rows.Count).ToString()].Value = lbTruePrice.Text; // Bằng chữ exSheet.Range["A" + (13 + dgvData.Rows.Count).ToString(), "F" + (10 + dgvData.Rows.Count).ToString()].Font.Bold = true; exSheet.Range["A" + (13 + dgvData.Rows.Count).ToString()].Value = "Thành tiền"; exSheet.Range["E" + (13 + dgvData.Rows.Count).ToString()].Value = lbMoneyByWord.Text; // Border // Excel.Border myBorder = new Excel.Border(); exSheet.Name = "Hang"; exBook.Activate(); SaveFileDialog svFile = new SaveFileDialog(); svFile.Filter = "Excel document(*.xlsx)|*.xlsx"; svFile.FilterIndex = 1; svFile.AddExtension = true; svFile.DefaultExt = ".xlsx"; if (svFile.ShowDialog() == DialogResult.OK) { exBook.SaveAs(svFile.FileName.ToString()); } MessageBox.Show("Đã xuất file excel!", "Thông tin", MessageBoxButtons.OK, MessageBoxIcon.Information); exApp.Quit(); } catch (Exception ex) { exApp.Quit(); MessageBox.Show("Lỗi xuất Excel ở frmPrint: " + ex.Message); } }
private void Button1_Click(object sender, EventArgs e) { if (dgv.Rows.Count > 0) //TH có dữ liệu để ghi { //Khai báo và khởi tạo các đối tượng Excel.Application exApp = new Excel.Application(); Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1]; //Định dạng chung Excel.Range tenCuaHang = (Excel.Range)exSheet.Cells[1, 1]; tenCuaHang.Font.Size = 14; tenCuaHang.Font.Bold = true; tenCuaHang.Font.Color = Color.Blue; tenCuaHang.Value = "BẢNG ĐIỂM "; Excel.Range dcCuaHang = (Excel.Range)exSheet.Cells[2, 1]; dcCuaHang.Font.Size = 14; dcCuaHang.Font.Bold = true; dcCuaHang.Font.Color = Color.Blue; dcCuaHang.Value = "Copyright-Cao Thi Luyên"; Excel.Range dtCuaHang = (Excel.Range)exSheet.Cells[3, 1]; dtCuaHang.Font.Size = 14; dtCuaHang.Font.Bold = true; dtCuaHang.Font.Color = Color.Blue; dtCuaHang.Value = "Điện thoại: 0912403345"; Excel.Range header = (Excel.Range)exSheet.Cells[5, 2]; exSheet.get_Range("B5:G5").Merge(true); header.Font.Size = 13; header.Font.Bold = true; header.Font.Color = Color.Red; header.Value = "DANH SÁCH SINH VIÊN ĐIỂM CAO"; //Định dạng tiêu đề bảng exSheet.get_Range("A7:E7").Font.Bold = true; exSheet.get_Range("A7:E7").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; exSheet.get_Range("A7").Value = "STT"; exSheet.get_Range("B7").Value = "Mã Học Sinh"; exSheet.get_Range("C7").Value = "Tên Học Sinh"; exSheet.get_Range("C7").ColumnWidth = 20; exSheet.get_Range("D7").Value = "Điện Thoại"; exSheet.get_Range("E7").Value = "Địa Chỉ "; //In dữ liệu for (int i = 0; i < dgv.Rows.Count - 1; i++) { exSheet.get_Range("A" + (i + 8).ToString() + ":G" + (i + 8).ToString()).Font.Bold = false; exSheet.get_Range("A" + (i + 8).ToString()).Value = (i + 1).ToString(); exSheet.get_Range("B" + (i + 8).ToString()).Value = dgv.Rows[i].Cells[0].Value; exSheet.get_Range("C" + (i + 8).ToString()).Value = dgv.Rows[i].Cells[1].Value; exSheet.get_Range("D" + (i + 8).ToString()).Value = dgv.Rows[i].Cells[2].Value; exSheet.get_Range("E" + (i + 8).ToString()).Value = dgv.Rows[i].Cells[3].Value; } exSheet.Name = "Điểm thi"; exBook.Activate(); //Kích hoạt file Excel //Thiết lập các thuộc tính của SaveFileDialog dlgSave.Filter = "Excel Document(*.xls)|*.xls |Word Document(*.doc) |*.doc|All files(*.*)|*.*"; dlgSave.FilterIndex = 1; dlgSave.AddExtension = true; dlgSave.DefaultExt = ".xls"; if (dlgSave.ShowDialog() == System.Windows.Forms.DialogResult.OK) { exBook.SaveAs(dlgSave.FileName.ToString()); //Lưu file Excel } exApp.Quit(); //Thoát khỏi ứng dụng } else { MessageBox.Show("Không có danh sách hàng để in"); } }
private void btn_in_Click(object sender, EventArgs e) { SaveFileDialog dlgSave = new SaveFileDialog(); int n = Gridview_HDN.Rows.Count; if (Gridview_HDN.Rows.Count > 0) //TH có dữ liệu để ghi { //Khai báo và khởi tạo các đối tượng Excel.Application exApp = new Excel.Application(); Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1]; //Định dạng chung Excel.Range tenCuaHang = (Excel.Range)exSheet.Cells[1, 1]; tenCuaHang.Font.Size = 12; tenCuaHang.Font.Bold = true; tenCuaHang.Font.Color = Color.Blue; tenCuaHang.Value = "CỬA HÀNG BÁN PHỤ KIÊN Ô TÔ"; // Excel.Range dcCuaHang = (Excel.Range)exSheet.Cells[2, 1]; dcCuaHang.Font.Size = 12; dcCuaHang.Font.Bold = true; dcCuaHang.Font.Color = Color.Blue; dcCuaHang.Value = "Địa chỉ: 37B - TT Đông Anh - Hà Nội"; // Excel.Range dtCuaHang = (Excel.Range)exSheet.Cells[3, 1]; dtCuaHang.Font.Size = 12; dtCuaHang.Font.Bold = true; dtCuaHang.Font.Color = Color.Blue; dtCuaHang.Value = "Điện thoại: 0976967619"; // Excel.Range header = (Excel.Range)exSheet.Cells[5, 2]; exSheet.get_Range("B5:G5").Merge(true); header.Font.Size = 13; header.Font.Bold = true; header.Font.Color = Color.Red; header.Value = "DANH SÁCH CÁC MẶT HÀNG"; //Định dạng tiêu đề bảng exSheet.get_Range("A7:G7").Font.Bold = true; exSheet.get_Range("A7:G7").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; exSheet.get_Range("A7").Value = "STT"; exSheet.get_Range("B7").Value = "Mã SP"; exSheet.get_Range("C7").Value = "Tên Tên Sản Phẩm"; exSheet.get_Range("C7").ColumnWidth = 20; exSheet.get_Range("D7").Value = "Số lượng"; //exSheet.get_Range("E7").Value = ""; exSheet.get_Range("F7").Value = "Gía nhập"; exSheet.get_Range("G7").Value = "Thanh tiền"; //In dữ liệu for (int i = 0; i < Gridview_HDN.Rows.Count - 1; i++) { try { exSheet.get_Range("A" + (i + 8).ToString() + ":G" + (i + 8).ToString()).Font.Bold = false; exSheet.get_Range("A" + (i + 8).ToString()).Value = (i + 1).ToString(); exSheet.get_Range("C" + (i + 8).ToString()).Value = Gridview_HDN.Rows[i].Cells[1].Value.ToString(); exSheet.get_Range("B" + (i + 8).ToString()).Value = Gridview_HDN.Rows[i].Cells[0].Value.ToString(); exSheet.get_Range("D" + (i + 8).ToString()).Value = Gridview_HDN.Rows[i].Cells[2].Value.ToString(); // exSheet.get_Range("E" + (i + 8).ToString()).Value = Gridview_HDN.Rows[i].Cells[3].Value.ToString(); exSheet.get_Range("F" + (i + 8).ToString()).Value = Gridview_HDN.Rows[i].Cells[3].Value.ToString(); exSheet.get_Range("G" + (i + 8).ToString()).Value = Gridview_HDN.Rows[i].Cells[4].Value.ToString(); } catch { } } exSheet.get_Range("E" + (n + 8).ToString()).Value = "Tổng tiền"; exSheet.get_Range("G" + (n + 8).ToString()).Value = txt_tongtien.Text; exSheet.Name = "Hang"; exBook.Activate(); //Kích hoạt file Excel //Thiết lập các thuộc tính của SaveFileDialog dlgSave.Filter = "Excel Document(*.xls)|*.xls |Word Document(*.doc)| *.doc | All files(*.*) | *.* "; dlgSave.FilterIndex = 1; dlgSave.AddExtension = true; dlgSave.DefaultExt = ".xls"; if (dlgSave.ShowDialog() == System.Windows.Forms.DialogResult.OK) { exBook.SaveAs(dlgSave.FileName.ToString()); //Lưu file Excel } exApp.Quit(); //Thoát khỏi ứng dụng MessageBox.Show("In thanh công hóa đơn"); } else { MessageBox.Show("Không có danh sách hàng để in"); } }
private void Client_MqttMsgPublishReceived(object sender, MqttMsgPublishEventArgs e) { DateTime realtime = DateTime.Now; String hour = (realtime.ToString("hh")); String minute = (realtime.ToString("mm")); String dayss = (realtime.ToString("dd")); String monthss = (realtime.ToString("MM")); String yearss = (realtime.ToString("yyyy")); String gettime = dayss + ':' + monthss + ':' + yearss + ' ' + '-' + ' ' + hour + ':' + minute; //************** EXCEL************* message = Encoding.Default.GetString(e.Message); Excel.Application app = new Excel.Application(); Excel.Workbook workbook = app.Workbooks.Open("D:/quanlynhansu-EXCEL.xlsx"); Excel.Worksheet xlWorksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1); workbook.Activate(); xlWorksheet.Activate(); app.Visible = false; app.DisplayAlerts = false; xlWorksheet.Cells[1, 1] = "TÊN"; xlWorksheet.Cells[1, 2] = "CHỨC VỤ"; xlWorksheet.Cells[1, 3] = "MÃ SỐ"; xlWorksheet.Cells[1, 4] = "GIỜ VÀO"; xlWorksheet.Cells[1, 6] = "GIỜ RA"; try { if (message != "ESP_reconnected") { mestmp = message.Split('@'); Console.WriteLine("WORD_IN: " + message); if (mestmp[1].Equals("IN")) { if (app == null) { MessageBox.Show("Excel is not properly installed!!"); return; } // ************* IN ***************** stt = Int32.Parse(mestmp[0]); msnvtmp[stt] = mestmp[0]; Console.WriteLine(msnvtmp[stt]); SetText_sttIN(mestmp[0].ToString()); SetText_tenIN(mestmp[2].ToString()); SetText_chucvuIN(mestmp[3].ToString()); SetText_msIN(mestmp[4].ToString()); SetText_gioIN(gettime); xlWorksheet.Cells[Int32.Parse(mestmp[0]), 1] = mestmp[2]; xlWorksheet.Cells[Int32.Parse(mestmp[0]), 2] = mestmp[3]; xlWorksheet.Cells[Int32.Parse(mestmp[0]), 3] = mestmp[4]; xlWorksheet.Cells[Int32.Parse(mestmp[0]), 4] = gettime; xlWorksheet.Columns.AutoFit(); //cleanup workbook.Save(); workbook.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); KillSpecificExcelFileProcess(); } if (mestmp[1].Equals("OUT")) { if (app == null) { MessageBox.Show("Excel is not properly installed!!"); return; } // ************* OUT ***************** if (mestmp[0] == msnvtmp[Int32.Parse(mestmp[0])]) { xlWorksheet.Cells[Int32.Parse(mestmp[0]), 6] = gettime; } xlWorksheet.Columns.AutoFit(); //cleanup //cleanup workbook.Save(); workbook.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); KillSpecificExcelFileProcess(); SetText_sttOUT(mestmp[0].ToString()); SetText_tenOUT(mestmp[2].ToString()); SetText_chucvuOUT(mestmp[3].ToString()); SetText_msOUT(mestmp[4].ToString()); SetText_gioOUT(gettime); } } if (dis == 1) { Client.Disconnect(); Console.WriteLine("DIS-----------"); dis = 0; Environment.Exit(1); } } catch (Exception) { workbook.Save(); workbook.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); KillSpecificExcelFileProcess(); } }
private void buttonXuatBaoCao_Click(object sender, EventArgs e) { //dữ liệu DataGridView datasource = DanhsachNCC; if (datasource.Rows.Count > 1 || datasource.Text != "") { //nơi in datagrid int rowPrint = 8; //hàng 8 int colprint = 3; //cột 2 //các tiêu đề string Ten = "Nguyễn Thảo zzz"; string DiaChi = "Địa Chỉ : H_ N__"; string TieudeBaoCao = $"Báo Cáo Danh Sách Sản Phẩm Của Nhà Cung Cấp " + comboBoxNCC.Text; char[] col = { ' ', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'K', 'L', 'M', 'N', 'O' }; //tạo đối tượng excel Excel.Application ex = new Excel.Application(); //tạo workbook trong đối tượng excel Excel.Workbook wbook = ex.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); //tạo worksheet trong workbook Excel.Worksheet wsheet = wbook.Worksheets[1]; ex.DisplayAlerts = false; wsheet.Name = "Báo Cáo"; Excel.Range TenCuaHang = (Excel.Range)wsheet.Cells[1, 1]; TenCuaHang.Font.Size = 15; TenCuaHang.Font.Color = Color.Blue; TenCuaHang.Font.Bold = true; TenCuaHang.Value = Ten; Excel.Range Diachi = (Excel.Range)wsheet.Cells[2, 1]; Diachi.Font.Size = 13; Diachi.Value = DiaChi; Excel.Range Title = (Excel.Range)wsheet.Cells[6, 1]; wsheet.get_Range("A6:I6").Merge(true); Title.Font.Size = 17; Title.Font.Color = Color.Red; Title.Font.Bold = true; Title.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; Title.Value = TieudeBaoCao; //IN Báo Cáo wsheet.get_Range("E8:I8").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //========================================================================== PrintTable(wsheet, datasource, rowPrint, colprint); //======================================================================== ex.Columns["B:C"].AutoFit(); //chỉnh sao cho cột tự động vừa với thông tin ở trong dùng thì bỏ // đi //thêm khung vào bảng string firstcell = col[colprint] + rowPrint.ToString(); string lastcell = col[colprint + datasource.ColumnCount - 1] + (rowPrint + datasource.Rows.Count).ToString(); wsheet.get_Range(firstcell + ":" + lastcell).Borders.Weight = Excel.XlBorderWeight.xlMedium; wbook.Activate(); SaveFileDialog save = new SaveFileDialog(); save.Filter = "Excel Document(*.xlsx)|*.xlsx |Word Document(*.doc) |*.doc|All files(*.*)|*.*"; save.AddExtension = true; save.DefaultExt = ".xlsx"; if (save.ShowDialog() == DialogResult.OK) { try { wbook.SaveAs(save.FileName.ToString()); MessageBox.Show("Xuất Báo Cáo Thành Công", "Thông Báo"); } catch (System.Runtime.InteropServices.COMException) { MessageBox.Show("Cửa sổ excel hiện đang mở không thể thực hiện thay đổi"); } catch (Exception t) { MessageBox.Show($"Có Lỗi không lưu được excel {t}"); } finally { ex.Quit(); } } } else { MessageBox.Show("Không có gì để Báo Cáo ", "Thông Báo"); } }
private void button1_Click(object sender, EventArgs e) { Excel.Application exApp = new Excel.Application(); Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1]; //Định dạng chung Excel.Range tenCuaHang = (Excel.Range)exSheet.Cells[1, 1]; tenCuaHang.Font.Size = 14; tenCuaHang.Font.Bold = true; tenCuaHang.Font.Color = Color.Blue; tenCuaHang.Value = "BẢNG DANH SÁCH DOANH THU PHIM"; Excel.Range dcCuaHang = (Excel.Range)exSheet.Cells[2, 1]; dcCuaHang.Font.Size = 13; dcCuaHang.Font.Bold = true; dcCuaHang.Font.Color = Color.Blue; dcCuaHang.Value = "Copyright: Nguyễn Văn Cung"; Excel.Range dtCuaHang = (Excel.Range)exSheet.Cells[3, 1]; dtCuaHang.Font.Size = 13; dtCuaHang.Font.Bold = true; dtCuaHang.Font.Color = Color.Blue; dtCuaHang.Value = "Điện thoại: 0399544543"; Excel.Range header = (Excel.Range)exSheet.Cells[5, 2]; exSheet.get_Range("B5:G5").Merge(true); header.Font.Size = 13; header.Font.Bold = true; header.Font.Color = Color.Red; header.Value = "DANH SÁCH DOANH THU PHIM"; //Định dạng tiêu đề bảng exSheet.get_Range("B7:N7").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //exSheet.get_Range("A7").Value = "STT"; exSheet.get_Range("B7").Value = "Mã Phim"; exSheet.get_Range("B7").ColumnWidth = 20; exSheet.get_Range("C7").Value = "Tên Phim"; exSheet.get_Range("C7").ColumnWidth = 20; exSheet.get_Range("D7").Value = "Mã nước sản xuất"; exSheet.get_Range("E7").Value = "Mã hãng sản xuất"; exSheet.get_Range("F7").Value = "Đạo diễn"; exSheet.get_Range("G7").Value = "Mã thể loại"; exSheet.get_Range("G7").ColumnWidth = 20; exSheet.get_Range("H7").Value = "Ngày khởi chiếu"; exSheet.get_Range("H7").ColumnWidth = 20; exSheet.get_Range("I7").Value = "Ngày kết thúc"; exSheet.get_Range("I7").ColumnWidth = 20; exSheet.get_Range("J7").Value = "Nữ chính"; exSheet.get_Range("J7").ColumnWidth = 20; exSheet.get_Range("K7").Value = "Nam chính"; exSheet.get_Range("K7").ColumnWidth = 20; exSheet.get_Range("L7").Value = "Tổng chi"; exSheet.get_Range("M7").Value = "Tổng thu"; DataTable dataTable = fimlsDAO.Instance.getListFimls(); DataColumnCollection dataColumnCollection = dataTable.Columns; //In dữ liệu for (int i = 1; i <= dataTable.Rows.Count + 1; i++) { for (int j = 1; j <= dataTable.Columns.Count; j++) { if (i == 1) { exApp.Cells[5 + i, j] = dataColumnCollection[j - 1].ToString(); } else { exApp.Cells[5 + i, j] = dataTable.Rows[i - 2][j - 1].ToString(); } } } exSheet.Name = "Danh sách doanh thu phim"; exBook.Activate(); sdlSave.Filter = "Excel Document(*.xls)|*.xls |Word Document(*.doc) |*.doc|All files(*.*)|*.*"; sdlSave.FilterIndex = 1; sdlSave.AddExtension = true; sdlSave.DefaultExt = ".xls"; if (sdlSave.ShowDialog() == System.Windows.Forms.DialogResult.OK) { exBook.SaveAs(sdlSave.FileName.ToString()); } exApp.Quit(); }