/// <summary> /// Teste si le fichier existe, si oui on l'ouvre sinon on le crée et l'enregistre /// </summary> /// <returns></returns> public bool CreerOuOuvrirFichier() { try { if (File.Exists(this.CheminExcel + "/Programmation_" + this.idProgrammation)) { _LesWorkBooks = _Application.Workbooks; _MonClasseur = _LesWorkBooks.Open(this.CheminExcel + "/Programmation_" + this.idProgrammation); _MaFeuille = (_Worksheet)_Application.ActiveSheet; return(true); } else { //Récupère le WorkBook _MonClasseur = _Application.Workbooks.Add(); //Récupère la feuille Active _MaFeuille = (_Worksheet)_MonClasseur.ActiveSheet; this.InscritEntete(); _MonClasseur.SaveAs(this.CheminExcel + "/Programmation_" + this.idProgrammation); return(true); } } catch (Exception e) { this.Logger.Error("Impossible d'ouvrir ou de créer le fichier Excel."); FermerExcel(); return(false); } }
// 打开Excel文件 public void OpenExcelFile() { OpenFileDialog opd = new OpenFileDialog(); if (opd.ShowDialog() == DialogResult.OK) excelOpenFileName = opd.FileName; textBox4.Text = System.IO.Path.GetFileNameWithoutExtension(excelOpenFileName); if (excelOpenFileName !="") { try { excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelWorkBooks = excelApplication.Workbooks; excelWorkBook = ((Workbook)excelWorkBooks.Open(excelOpenFileName, 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)); excelWorkSheet = (Worksheet)excelWorkBook.Worksheets[ActiveSheetIndex]; excelApplication.Visible = false; rangeOfExcel = excelWorkSheet.UsedRange.Cells.Rows.Count;//获取EXCEL行数 stateOfSave = false; } catch (Exception e) { closeApplication(); MessageBox.Show("(1)没有安装Excel;(2)或没有安装.NET 可编程性支持;\n详细信息:" + e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } else { MessageBox.Show("未选择文件!"); closeApplication(); } }
public void SaveAndClose(string path) { if (workbook != null) { workbook.SaveAs(path); workbook.Close(); Marshal.FinalReleaseComObject(workbook); workbook = null; workbooks.Close(); Marshal.FinalReleaseComObject(workbooks); workbooks = null; } if (excel != null) { excel.Application.Workbooks.Close(); excel.Application.Quit(); excel.Quit(); Marshal.FinalReleaseComObject(excel.Application); Marshal.FinalReleaseComObject(excel); excel = null; } GC.Collect(); GC.WaitForPendingFinalizers(); }
/// <summary> /// 关闭excel /// </summary> public void Close() { #region 释放Excel资源 //foreach (_Worksheet sheet in m_objBook.Sheets) //{ // System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); //} if (m_objBooks != null && m_objExcel != null) { foreach (_Workbook book in m_objBooks) { System.Runtime.InteropServices.Marshal.ReleaseComObject(book); } System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); } //System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets); // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange); // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objFont); //System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBorders); m_objExcel = null; m_objBooks = null; m_objBook = null; GC.Collect(); #endregion }
public static void RunVBATest() { //System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; //System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //object oMissing = System.Reflection.Missing.Value; Application oExcel = new Application(); oExcel.Visible = true; Workbooks oBooks = oExcel.Workbooks; _Workbook oBook = null; oBook = oBooks.Open("C:\\temp\\Book1.xlsm"); // Run the macro. RunMacro(oExcel, new Object[] { "TestMsg" }); // Quit Excel and clean up. oBook.Saved = true; oBook.Close(false); System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook); oBook = null; System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks); oBooks = null; oExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel); oExcel = null; //System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; }
//导出采购订单请款 public static void ExportDDQKDataItem(System.Data.DataTable objdt) { System.Data.DataTable dt = objdt; Application m_xlApp = new Application(); Workbooks workbooks = m_xlApp.Workbooks; Workbook workbook; Worksheet wksheet; workbook = m_xlApp.Workbooks.Open(System.Web.HttpContext.Current.Server.MapPath("采购订单请款模板") + ".xls", Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); m_xlApp.Visible = false; // Excel不显示 m_xlApp.DisplayAlerts = false; // 关闭提示,采用默认的方案执行(合并单元格的时候,如果两个单元格都有数据,会出现一个确认提示) wksheet = (Worksheet)workbook.Sheets.get_Item(1); //用数组填充表格 int rowCount = dt.Rows.Count; int colCount = dt.Columns.Count; object[,] dataArray = new object[rowCount, colCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray[i, j] = dt.Rows[i][j]; } } m_xlApp.get_Range("A2", m_xlApp.Cells[rowCount + 1, colCount]).Value2 = dataArray; m_xlApp.get_Range("A2", m_xlApp.Cells[rowCount + 1, colCount]).HorizontalAlignment = XlHAlign.xlHAlignLeft; wksheet.Columns.EntireColumn.AutoFit();//列宽自适应 string filename = System.Web.HttpContext.Current.Server.MapPath("/Contract_Data/ExportFile/" + "采购订单请款信息" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"); ExportExcel_Exit(filename, workbook, m_xlApp, wksheet); }
protected override void HandleWriteStarted() { // There are problems with exporting to excel on non-English machines // See: http://www.made4dotnet.com/Default.aspx?tabid=141&aid=15 iOriginalCulture = System.Threading.Thread.CurrentThread.CurrentCulture; ChangeToEnglishCulture(); iExcelApp = new Microsoft.Office.Interop.Excel.Application(); iExcelApp.DisplayAlerts = false; Workbooks workbooks = iExcelApp.Workbooks; workbooks.Add(XlWBATemplate.xlWBATWorksheet); iWorkbook = workbooks.get_Item(workbooks.Count); // Create summary sheet Sheets sheets = iWorkbook.Worksheets; iSummary = (Worksheet)sheets.get_Item(1); CreateSummary(iSummary); // Create pages iPages.Add(new Pages.PageUnchanged(iEngine.ResultsUnchanged, iWorkbook)); iPages.Add(new Pages.PageUnique(iEngine.Reconstructor2, iEngine.ResultsUniqueInHeap2, iWorkbook, "Unique in Heap 2", 2, 1)); iPages.Add(new Pages.PageUnique(iEngine.Reconstructor1, iEngine.ResultsUniqueInHeap1, iWorkbook, "Unique in Heap 1", 1, 2)); iPages.Add(new Pages.PageSimpleListing(iEngine.ResultsUnchangedLengthButDifferentContents, iWorkbook, "Same length, different contents")); iPages.Add(new Pages.PageSimpleListing(iEngine.ResultsChanged, iWorkbook, "Changed", false)); }
/// <summary> /// 创建Book、sheet /// </summary> public void CreateExceFile() { UserControl(false); m_objBooks = (Workbooks)m_objExcel.Workbooks; m_objBook = (Workbook)(m_objBooks.Add(miss)); sheet = (Worksheet)m_objBook.ActiveSheet; }
public Excel(string path, int Sheet) { if (String.IsNullOrWhiteSpace(path)) { excel = new _Excel.Application(); wbs = excel.Workbooks; wb = null; ws = null; wb = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); // ExcelWorkBook.Worksheets.Add(); //Adding New Sheet in Excel Workbook ws = wb.Worksheets[1]; // Compulsory Line in which sheet you want to write data wb.Worksheets[1].Name = "Sheet"; //Renaming the Sheet1 to Sheet if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + @"template.xlsx")) { File.Delete(AppDomain.CurrentDomain.BaseDirectory + @"template.xlsx"); wb.SaveAs(AppDomain.CurrentDomain.BaseDirectory + @"template.xlsx"); } else { wb.SaveAs(AppDomain.CurrentDomain.BaseDirectory + @"template.xlsx"); } } else { //this.path = path; wbs = excel.Workbooks; wb = wbs.Open(path); ws = wb.Worksheets[Sheet]; } }
/// <summary> /// 默认构造函数 /// </summary> public Excel() { books = app.Workbooks; book = books.Add(); sheets = book.Worksheets; sheet = (_Worksheet)sheets.get_Item(1); }
//当界面加载时,实例化两个对象 private void onFormLoaded(object sender, RoutedEventArgs e) { //打开Excel m_Excel = new Microsoft.Office.Interop.Excel.Application(); m_Workbooks = m_Excel.Application.Workbooks; //initDataBase(); }
public void Dispose() { // Cleanup excel.DisplayAlerts = false; wb.Close(SaveChanges: false); wbs.Close(); excel.Quit(); // Manual disposal because of COM while (Marshal.ReleaseComObject(excel) != 0) { } while (Marshal.ReleaseComObject(wbs) != 0) { } while (Marshal.ReleaseComObject(wb) != 0) { } while (Marshal.ReleaseComObject(ws) != 0) { } excel = null; wbs = null; wb = null; wbs = null; GC.Collect(); GC.WaitForPendingFinalizers(); }
/// <summary> /// /// </summary> public MSExcelUtil() { _excelApp = new Application(); _objWorkbooks = _excelApp.Workbooks; _excelApp.Visible = false; _excelApp.DisplayAlerts = false; }
/// <summary> /// 打开excel /// </summary> protected void Create() { m_objExcel = new Application(); m_objExcel.Application.DisplayAlerts = false; m_objBooks = (Workbooks)m_objExcel.Workbooks; m_objBook = (_Workbook)(m_objBooks.Add(m_objOpt)); }
public void InformeGeneralObras() { oExcel = new Application(); oBooks = oExcel.Workbooks; oBook = oBooks.Add(1); oSheets = (Sheets)oBook.Worksheets; oSheet = oSheets.get_Item(1); this.oSheet.Cells[1,1] = "Consecutivo"; this.oSheet.Cells[1,2] = "Título"; this.oSheet.Cells[1,3] = "Núm. de Material"; this.oSheet.Cells[1,4] = "Año"; this.oSheet.Cells[1,5] = "Tiraje"; int ind = 2; for (int j = 0; j < obrasImprimir.Count; j++) { oSheet.Cells[1][ind] = obrasImprimir[j].Consecutivo; oSheet.Cells[2][ind] = obrasImprimir[j].Titulo; oSheet.Cells[3][ind] = obrasImprimir[j].NumMaterial; oSheet.Cells[4][ind] = obrasImprimir[j].AnioPublicacion; oSheet.Cells[5][ind] = obrasImprimir[j].Tiraje; ind++; } this.oExcel.ActiveWorkbook.Save(); this.oExcel.Quit(); }
public static void Generate(string source, string destination) { Application xlApp = new Application(); xlApp.Visible = false; xlApp.UserControl = false; Workbooks xlWorkbooks = xlApp.Workbooks; Workbook xlWorkbook = xlWorkbooks.Open(source); xlWorkbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, destination); xlWorkbook.Close(false); xlWorkbooks.Close(); xlApp.Quit(); //MainWindowTitle will become empty afer being close Marshal.FinalReleaseComObject(xlWorkbook); Marshal.FinalReleaseComObject(xlWorkbooks); Marshal.FinalReleaseComObject(xlApp); GC.Collect(); GC.WaitForPendingFinalizers(); ExcelKiller.Kill(new Log.ServerLogger()); }
public static bool SaveExcel(string savePath, System.Data.DataTable tripTable, System.Data.DataTable testTable) { try { Application excelApp = new Application(); Workbooks excelBooks = excelApp.Workbooks; Workbook excelBook = excelBooks.Add(Missing.Value); Sheets sheets = excelBook.Sheets; Worksheet tripSheet = ((Worksheet)sheets.get_Item(1)); Worksheet testSheet = ((Worksheet)sheets.get_Item(2)); fillData(ref tripSheet, "Trip", tripTable); fillData(ref testSheet, "Test", testTable); excelBook.SaveAs(savePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); excelBook.Close(Missing.Value, Missing.Value, Missing.Value); excelBooks.Close(); excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); excelApp = null; return(true); } catch (Exception exp) { MsgBox.Show(exp.Message); return(false); } }
public static int CreateFromRealmsCSV(string source, string destination) { var app = CreateExcelApp(destination); app.ScreenUpdating = false; Workbooks workbooks = app.Workbooks; Workbook workbook = workbooks.Add(); Worksheet worksheet = (Worksheet)workbook.Worksheets.Item[1]; CreateWorksheetTemplate(worksheet); var csv = ConfigureCSV(source); // Reading records the old way var departmentRoot = ReadRealmsRecords(csv); // Writing records the old way PopulateTemplate(worksheet, departmentRoot); workbook.SaveAs(destination); // Cleanup app.ScreenUpdating = true; Cleanup(app, workbooks, workbook, worksheet); return(0); }
private void close() { m_app.DisplayAlerts = false; m_app.AlertBeforeOverwriting = false; m_app.Quit(); IntPtr t = new IntPtr(m_app.Hwnd); int k = 0; GetWindowThreadProcessId(t, out k); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); p.Kill(); m_app = null; m_book = null; m_books = null; ReleaseObj(m_book); ReleaseObj(m_books); ReleaseObj(m_sheet); ReleaseObj(m_sheets); ReleaseObj(m_app); System.GC.Collect(); System.GC.WaitForPendingFinalizers(); }
/// <summary> /// 格式化未供完表格 /// </summary> /// <param name="file"></param> public static void FormatWGWExcel(string file) { Application app = new Application(); app.DisplayAlerts = false; app.Visible = false; app.UserControl = true; Workbooks workbooks = app.Workbooks; _Workbook workbook = workbooks.Add(file); Sheets sheets = workbook.Sheets; Worksheet worksheet = (Worksheet)sheets.get_Item(1); int recordCount = 2;//从第二行开始判断 //查找数据行数 while (true) { if (Method.IsNumber(worksheet.Cells[recordCount, 1].TEXT)) { recordCount++; } else { break; } } MergeWGWCell(ref worksheet, 2, recordCount, 2); //MergePHCell(ref worksheet, 2, recordCount, 6); workbook.SaveAs(file); app.Quit(); }
/// <summary> /// 格式化表格 /// </summary> /// <param name="file"></param> public static void FormateExcel(string file) { Application app = new Application(); app.DisplayAlerts = false; app.Visible = false; app.UserControl = true; Workbooks workbooks = app.Workbooks; _Workbook workbook = workbooks.Add(file); Sheets sheets = workbook.Sheets; for (int i = 1; i <= 1; i++) //读取2009-2017表格,首张为汇总表 { Worksheet worksheet = (Worksheet)sheets.get_Item(i); int recordCount = 2;//从第二行开始判断 //查找数据行数 while (true) { if (Method.IsNumber(worksheet.Cells[recordCount, 1].TEXT)) { recordCount++; } else { break; } } MergeCell(ref worksheet, 2, recordCount, 3); MergeCell(ref worksheet, 2, recordCount, 7); } workbook.SaveAs(file); app.Quit(); }
private async void CalculateRowSize() { int row_size = 0; Application application = null; Workbooks wbooks = null; Workbook sourceWorkbook = null; try { application = new Application(); wbooks = application.Workbooks; listbox_excellPaths.Enabled = false; foreach (ExcellFile item in excellFilePaths) { if (item.RowSize == 0) { WriteConsole($"{item.FileName} Boyutu Okunuyor..."); listbox_excellPaths.SelectedItem = item; await Task.Run(() => { sourceWorkbook = wbooks.Open(item.Path); Worksheet wsheet = (Worksheet)sourceWorkbook.Worksheets.get_Item(1); row_size += wsheet.UsedRange.Rows.Count; item.RowSize = wsheet.UsedRange.Rows.Count; sourceWorkbook?.Close(); Marshal.ReleaseComObject(sourceWorkbook); }); int index = listbox_excellPaths.Items.IndexOf(item); listbox_excellPaths.Items.Remove(item); listbox_excellPaths.Items.Insert(index, item); } else { row_size += item.RowSize; } SetRowCount(row_size); } listbox_excellPaths.Enabled = true; wbooks?.Close(); Marshal.ReleaseComObject(wbooks); application?.Quit(); Marshal.ReleaseComObject(application); WriteConsole($"Toplam Satır Boyutu {row_size} olarak okundu."); MessageBox.Show($"{row_size}", "Toplam Satır Sayısı"); } catch { } finally { try { //sourceWorkbook?.Close(); //Marshal.ReleaseComObject(sourceWorkbook); } catch (Exception) { } application = null; } }
/// <summary> /// Excelリソース解放 /// </summary> /// <param name="ReleaseMode">リリース対象Enum</param> private void ReleaseExcelComObject(EnumReleaseMode ReleaseMode) { try { // xlSheet解放 if (xlSheet != null) { Marshal.ReleaseComObject(xlSheet); xlSheet = null; } if (ReleaseMode == EnumReleaseMode.Sheet) { return; } // xlSheets解放 if (xlSheets != null) { Marshal.ReleaseComObject(xlSheets); xlSheets = null; } if (ReleaseMode == EnumReleaseMode.Sheets) { return; } // xlBook解放 if (xlBook != null) { Marshal.ReleaseComObject(xlBook); xlBook = null; } if (ReleaseMode == EnumReleaseMode.Book) { return; } // xlBooks解放 if (xlBooks != null) { Marshal.ReleaseComObject(xlBooks); xlBooks = null; } if (ReleaseMode == EnumReleaseMode.Books) { return; } // xlApp解放 if (xlApp != null) { Marshal.ReleaseComObject(xlApp); } } catch (Exception) { throw; } }
// Read the first worksheet from a template document and extract and store // the page settings for later use protected static void SetPageOptionsFromTemplate(Application app, Workbooks workbooks, Hashtable options, ref Hashtable templatePageSetup) { if (!HasTemplateOption(options)) { return; } try { var template = workbooks.Open((string)options["template"]); AddCOMDelay(options); if (template != null) { // Run macros from template if the /excel_template_macros option is given if ((bool)options["excel_template_macros"]) { var eventsEnabled = app.EnableEvents; app.EnableEvents = true; template.RunAutoMacros(XlRunAutoMacro.xlAutoOpen); app.EnableEvents = eventsEnabled; } var templateSheets = template.Worksheets; if (templateSheets != null) { // Copy the page setup details from the first sheet or chart in the template if (templateSheets.Count > 0) { PageSetup tps = null; var firstItem = templateSheets[1]; if (firstItem is _Worksheet) { tps = ((_Worksheet)firstItem).PageSetup; } else if (firstItem is _Chart) { tps = ((_Chart)firstItem).PageSetup; } var tpsType = tps.GetType(); for (int i = 0; i < templateProperties.Length; i++) { var prop = tpsType.InvokeMember(templateProperties[i], System.Reflection.BindingFlags.GetProperty, null, tps, null); if (prop != null) { templatePageSetup[templateProperties[i]] = prop; } } Converter.ReleaseCOMObject(firstItem); } ReleaseCOMObject(templateSheets); } CloseExcelWorkbook(template); } ReleaseCOMObject(template); } finally { } }
public Task Send(SysData.DataTable table, string excelTemplatePath, bool directlyPrint, int startRowIndex = 2) { if (table == null) { throw new ArgumentNullException("table"); } if (string.IsNullOrEmpty(excelTemplatePath)) { throw new ArgumentNullException("excelTemplatePath", "You must specify path to the excel workbook"); } Task tsk = Task.Run(() => { _Application excelApp = null; Workbooks excelWorkbooks = null; _Workbook excelWorkbook = null; _Worksheet excelSheet = null; string fileNamePath = excelTemplatePath;; //Start Excel and create new workbook from the template excelApp = StartExcel(); try { excelWorkbooks = excelApp.Workbooks; excelWorkbook = excelWorkbooks.Open(fileNamePath); excelSheet = excelWorkbook.Sheets[1]; //Insert the DataGridView into the excel spreadsheet TableToExcelSheet(table, excelSheet, startRowIndex, 1); //if visible , then exit so user can see it, otherwise save and exit if (directlyPrint) { //bool okPrint = objExcel.Dialogs[XlBuiltInDialog.xlDialogPrint].Show(); // if (!okPrint) //{ // objWorkbook.Close(false); // return; //} excelSheet.PrintOut(); //excelWorkbook.Close(false); excelApp.DisplayAlerts = false; excelApp.Quit(); ReleaseResources(excelApp, excelWorkbooks, excelWorkbook, excelSheet); return; } excelApp.Visible = true; ReleaseResources(excelApp, excelWorkbooks, excelWorkbook, excelSheet); } catch (Exception) { ForceExcleToQuit(excelApp); ReleaseResources(excelApp, excelWorkbooks, excelWorkbook, excelSheet); throw; } } ); return(tsk); }
private static void CreateExcelComponents(out Application app, out Workbooks workBooks, out Workbook workbook, out Sheets worksheets, out Worksheet worksheet) { app = new Application(); workBooks = app.Workbooks; workbook = workBooks.Add(Type.Missing); worksheets = workbook.Sheets; worksheet = (Worksheet)worksheets[1]; }
public void Save() { excelapp.DisplayAlerts = false; excelappworkbooks = excelapp.Workbooks; excelappworkbook = excelappworkbooks[1]; //excelappworkbook.Saved = true; excelappworkbook.Save(); }
private void ShowStartDocument() { _app.Visible = true; _wbs = _app.Workbooks; _wb = _wbs.Open(Path.GetFullPath(PathResolver.GenerateExcelPracticePath(DocumentPath))); _wb.Activate(); _wb.BeforeClose += OnBeforeCloseExcel; }
/// <summary> /// Create Excel application parameters instances /// </summary> private void CreateExcelRef() { _ExcelApp = new Excel.Application(); _books = (Workbooks)_ExcelApp.Workbooks; _book = (_Workbook)(_books.Add(_optionalValue)); _sheets = (Sheets)_book.Worksheets; _sheet = (_Worksheet)(_sheets.get_Item(1)); }
/// <summary> /// Metodo que crea una aplicacion de Excel /// </summary> private void CrearExcel() { excelApp = new Application(); libros = excelApp.Workbooks; libro = libros.Add(valorOpcional); hojas = libro.Worksheets; hoja = (Worksheet)(hojas.Item[1]); }
/// <summary> /// This constructor determines the exel file being worked on and also prints the headers /// for the report. /// </summary> /// <param name="excel"> The current instance of excel. </param> /// <param name="wbs"> The workbooks instance. </param> /// <param name="wb"> The current workbook being used. </param> public Printer(_Application excel, Workbooks wbs, _Workbook wb) { this.excel = excel; this.wbs = wbs; this.wb = wb; this.ws = wb.Worksheets[sheetNumber]; printHeaders(); }
/// Create Excel application parameters instances private void CreateExcelRef() { excelApp = new Microsoft.Office.Interop.Excel.Application(); books = (Workbooks)excelApp.Workbooks; book = (Workbook)(books.Add(optionalValue)); sheets = (Sheets)book.Worksheets; sheet = (Worksheet)(sheets.get_Item(1)); }
public DataGridExcelTools() { CursorPosition = 1; excel = Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", string.Empty); //excel.ScreenUpdating = false; workbook = excel.workbooks; workbook.Add(); worksheet = excel.ActiveSheet; }
public XlsReportGenerator() { this.app = new Application { Visible = false }; this.workbooks = this.app.Workbooks; this.workbook = this.workbooks.Add(Missing.Value); this.worksheet = this.workbook.ActiveSheet; this.worksheet.Name = "Sheet1"; this.rnd = new Random(); }
public ExcelHelper(string workbookPath) { //Get reference to Excel.Application from the ROT. _xlApp = new Application(); _xlWorkBooks = _xlApp.Workbooks; //Display the name of the object. _xlWorkBook = _xlApp.Workbooks.Open( workbookPath, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0 ); }
/// <summary> /// 指定されたパラメタを元にインスタンスを生成します。 /// </summary> /// <param name="quitFlg">終了フラグ</param> public ExcelApplication(bool quitFlg = true) { // リソース解放フラグ設定 this.disposed = false; // Excelアプリケーションインスタンス生成 Type excelAppType = Type.GetTypeFromProgID(ExcelApplication.ExcelApplicationProgramId, true); interopExcelApplication = Activator.CreateInstance(excelAppType); // ワークブックコレクション生成 this.workbooks = new Workbooks(this.interopExcelApplication.Workbooks); // 終了フラグ設定 this.quitFlg = quitFlg; }
public void DisposeExcelObject() { try { if (workbook != null) workbook.Close(true); if (application != null) { if (application.Workbooks != null) application.Workbooks.Close(); application.Quit(); } if (range != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(range); if (workbook != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); if (workbooks != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); if (sheets != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets); if (sheets != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet); if (application != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(application); range = null; workbook = null; workbooks = null; sheets = null; worksheet = null; application = null; Process[] pProcess; pProcess = Process.GetProcessesByName("Excel"); foreach (var process in pProcess) { process.Kill(); } } catch (Exception ex) { } }
public void Open(string filePath) { excel = new Application {DisplayAlerts = false}; workbooks = excel.Workbooks; if (File.Exists(filePath)) { workbook = workbooks.Open(filePath); } else { workbook = workbooks.Add(); workbook.SaveAs(filePath); } worksheet = workbook.Worksheets[1] as Worksheet; if (worksheet == null) throw new NullReferenceException("worksheet"); }
public void Close() { if (worksheet != null) { Marshal.FinalReleaseComObject(worksheet); worksheet = null; } if (workbook != null) { workbook.Close(); Marshal.FinalReleaseComObject(workbook); workbook = null; } if (workbooks != null) { Marshal.FinalReleaseComObject(workbooks); workbooks = null; } if (excel != null) { excel.Quit(); Marshal.FinalReleaseComObject(excel); excel = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); }
public void GenerateExcel(DataSet receiveDS, string filePath, int rowcout) { ds = receiveDS; strFileName = filePath; try { application = new Application(); application.Visible = false;//true; application.DisplayAlerts = false; workbooks = application.Workbooks; workbook = (Workbook)application.Workbooks.Add(1); workbook.SaveAs(strFileName, XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, false); range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1"); range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //range = worksheet.Cells; int excelSheetRow = this.WriteDataToTheSpecifiedRange(ds.Tables[0], range, rowcout); range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1"); range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //range = worksheet.Cells; //this.OverWriteDataToTheSpecifiedRange(ds.Tables[skuDataTableIndex], range); //worksheet.get_Range("A1", "AZ1").EntireColumn.AutoFit(); //worksheet.SaveAs(strFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, // Type.Missing, Type.Missing, Type.Missing, Type.Missing); Worksheet XLsheet = ((Worksheet)workbook.ActiveSheet); XLsheet.Columns.AutoFit(); XLsheet.Name = ds.Tables[0].TableName; workbook.Save(); } catch (Exception exp) { //string path = System.Web.HttpContext.Current.Server.MapPath(@""); //path = path.Substring(0, path.Length - 3); //System.IO.File.AppendAllText("LeaveYearTemp" + "/errorLog.txt", exp.Message); throw exp; } finally { DisposeExcelObject(); } }
public void GenerateExcelWithHeaderForPdf(DataSet receiveDS, string filePath, int rowcout, string companyName, string companyAddress, int columnScape, bool istotal, int itemPerPage) { ds = receiveDS; strFileName = filePath; try { application = new Application(); application.Visible = false;//true; application.DisplayAlerts = false; workbooks = application.Workbooks; workbook = (Workbook)application.Workbooks.Add(1); workbook.SaveAs(strFileName, XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, false); range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1"); range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; Worksheet XLsheet = ((Worksheet)workbook.ActiveSheet); int excelSheetRow = this.WriteDataToTheSpecifiedRangeForPdf(ds.Tables[0], range, rowcout, itemPerPage, XLsheet, rowcout, companyName, columnScape); //if (istotal) //{ // var colCount = ds.Tables[0].Columns.Count; // var dataRowCount = ds.Tables[0].Rows.Count; // var headerRangeTotal = XLsheet.Range[XLsheet.Cells[excelSheetRow + 1, 1], XLsheet.Cells[excelSheetRow + 1, columnScape]]; // headerRangeTotal[1, 1] = "Total"; // headerRangeTotal.Font.Bold = true; // headerRangeTotal.Font.Size = 12; // headerRangeTotal.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF")); // headerRangeTotal.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // headerRangeTotal.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black); // headerRangeTotal.Merge(Type.Missing); // headerRangeTotal.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // //int i = columnScape - 1; // for (int i = 0; i < colCount - columnScape; i++) // { // //var rangeTotal = XLsheet.Range[XLsheet.Cells[rowcout + dataRowCount + 1, i + columnScape + 1], XLsheet.Cells[rowcout + dataRowCount + 1, i + columnScape + 1]]; // var rangeTotal = XLsheet.Range[XLsheet.Cells[excelSheetRow + 1, i + columnScape + 1], XLsheet.Cells[excelSheetRow + 1, i + columnScape + 1]]; // string startColumnLetter = ExcelColumnLetter(i + columnScape); // string endColumnLetter = ExcelColumnLetter(i + columnScape); // rangeTotal[1, 1] = "=SUM(" + startColumnLetter + "" + (rowcout + 1) + ":" + endColumnLetter + "" + (rowcout + dataRowCount) + ")"; // rangeTotal[1, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF")); // rangeTotal[1, 1].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // rangeTotal[1, 1].Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black); // } //} range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1"); range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; XLsheet.PageSetup.PrintTitleRows = "$1:$6"; XLsheet.Columns.AutoFit(); XLsheet.Name = ds.Tables[0].TableName; workbook.Save(); } catch (Exception exp) { throw exp; } finally { DisposeExcelObject(); } }
public void GenerateExcelWithHeader(DataSet receiveDS, string filePath, int rowcout, string companyName, string companyAddress, int columnScape, bool istotal, DateTime salaryMonth) { ds = receiveDS; strFileName = filePath; try { application = new Application(); application.Visible = false;//true; application.DisplayAlerts = false; workbooks = application.Workbooks; workbook = (Workbook)application.Workbooks.Add(1); workbook.SaveAs(strFileName, XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, false); range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1"); range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //range = worksheet.Cells; Worksheet XLsheet = ((Worksheet)workbook.ActiveSheet); #region Main Header // if (companyName != "") //{ var headerRange1 = XLsheet.Range[XLsheet.Cells[1, 1], XLsheet.Cells[rowcout - 2, 6]]; //headerRange1[1, 1] = companyName; headerRange1.Font.Bold = true; headerRange1.Font.Size = 16; //headerRange1.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF")); //headerRange1.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //headerRange1.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black); headerRange1.Merge(Type.Missing); headerRange1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //var picPath = "G:\\Project\\Asp.Net\\Azolution\\Empress_4_0_0\\Empress_3_0_0\\Images\\" + "report-logo.png"; string picPath = System.Web.HttpContext.Current.Server.MapPath("../Images/report-logo.png"); object missing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Range picPosition = headerRange1;//GetPicturePosition(); // retrieve the range for picture insert Microsoft.Office.Interop.Excel.Pictures p = XLsheet.Pictures(missing) as Microsoft.Office.Interop.Excel.Pictures; Microsoft.Office.Interop.Excel.Picture pic = null; pic = p.Insert(picPath, missing); pic.Left = Convert.ToDouble(picPosition.Left); pic.Top = picPosition.Top; // } if (companyName != "") { var headerRange2 = XLsheet.Range[XLsheet.Cells[rowcout - 3, 7], XLsheet.Cells[rowcout - 3, 14]]; headerRange2[1, 1] = companyName;//companyAddress; headerRange2.Font.Bold = true; headerRange2.Font.Size = 12; //headerRange2.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF")); //headerRange2.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //headerRange2.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black); headerRange2.Merge(Type.Missing); headerRange2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; } // if (companyName != "") // { var headerRange3 = XLsheet.Range[XLsheet.Cells[rowcout - 1, 1], XLsheet.Cells[rowcout - 1, 6]]; headerRange3[1, 1] = "Salary Control Register for the Month of " + salaryMonth.ToString("MMMM-yyyy");//companyAddress; headerRange3.Font.Bold = true; headerRange3.Font.Size = 12; headerRange3.Merge(Type.Missing); headerRange3.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //} //var headerRang = XLsheet.get_Range(XLsheet.Cells[1, 1], XLsheet.Cells[1, 43]); #endregion #region Print DateTime //var headerRangeSystem = XLsheet.Range[XLsheet.Cells[rowcout - 1, ds.Tables[0].Columns.Count - 4], XLsheet.Cells[rowcout - 1, ds.Tables[0].Columns.Count]]; //headerRangeSystem[1, 1] = "Print From Rapid 2.0"; //headerRangeSystem.Font.Bold = true; //headerRangeSystem.Font.Size = 10; //headerRangeSystem.Merge(Type.Missing); //headerRangeSystem.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //var headerRangeDateTimeVal = XLsheet.Range[XLsheet.Cells[rowcout - 2, ds.Tables[0].Columns.Count - 4], XLsheet.Cells[rowcout - 2, ds.Tables[0].Columns.Count]]; //headerRangeDateTimeVal[1, 1] = "Print Date & Time : " + DateTime.Now.ToString("MMMM dd, yyyy") + DateTime.Now.ToString("h:mm tt");//+ DateTime.Now.ToString("d-MMM-yyyy HH.mm.ss tt", CultureInfo.InvariantCulture);//DateTime.Now.ToString("dd MMM yyyy hh:mm:ss");//companyAddress; //headerRangeDateTimeVal.Font.Bold = true; //headerRangeDateTimeVal.Font.Size = 10; //headerRangeDateTimeVal.Merge(Type.Missing); //headerRangeDateTimeVal.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; #endregion int excelSheetRow = this.WriteDataToTheSpecifiedRange(ds.Tables[0], range, rowcout); if (istotal) { var colCount = ds.Tables[0].Columns.Count; var dataRowCount = ds.Tables[0].Rows.Count; var headerRangeTotal = XLsheet.Range[XLsheet.Cells[rowcout + dataRowCount + 1, 1], XLsheet.Cells[rowcout + dataRowCount + 1, columnScape]]; headerRangeTotal[1, 1] = "Total"; headerRangeTotal.Font.Bold = true; headerRangeTotal.Font.Size = 12; headerRangeTotal.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF")); headerRangeTotal.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; headerRangeTotal.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black); headerRangeTotal.Merge(Type.Missing); headerRangeTotal.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //int i = columnScape - 1; for (int i = 0; i < (colCount - columnScape)+1; i++) { var rangeTotal = XLsheet.Range[XLsheet.Cells[rowcout + dataRowCount + 1, i + columnScape + 1], XLsheet.Cells[rowcout + dataRowCount + 1, i + columnScape + 1]]; string startColumnLetter = ExcelColumnLetter(i + columnScape); string endColumnLetter = ExcelColumnLetter(i + columnScape); rangeTotal[1, 1] = "=SUM(" + startColumnLetter + "" + (rowcout + 1) + ":" + endColumnLetter + "" + (rowcout + dataRowCount) + ")"; rangeTotal[1, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF")); rangeTotal[1, 1].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; rangeTotal[1, 1].Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black); } } range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1"); range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //range = worksheet.Cells; //this.OverWriteDataToTheSpecifiedRange(ds.Tables[skuDataTableIndex], range); //worksheet.get_Range("A1", "AZ1").EntireColumn.AutoFit(); //worksheet.SaveAs(strFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, // Type.Missing, Type.Missing, Type.Missing, Type.Missing); XLsheet.PageSetup.PrintTitleRows = "$1:$5"; // XLsheet.PageSetup.RightHeader = "Page &P of &N"; XLsheet.PageSetup.LeftFooter = "-------------------------------------\n &BPrepared By&B \n " + "Rapid 2.0| Payroll | Print Date, Time && Page No : " + DateTime.Now.ToString("MMMM dd, yyyy") + " | " + DateTime.Now.ToString("h:mm tt") + " | " + "Page &P of &N"; XLsheet.PageSetup.BottomMargin = application.InchesToPoints(1.5); XLsheet.PageSetup.CenterFooter = "-------------------------------------\n &BChecked By&B \n"; XLsheet.PageSetup.RightFooter = "-------------------------\n &BApproved By&B \n"; XLsheet.Columns.AutoFit(); XLsheet.Name = ds.Tables[0].TableName; workbook.Save(); } catch (Exception exp) { //string path = System.Web.HttpContext.Current.Server.MapPath(@""); //path = path.Substring(0, path.Length - 3); //System.IO.File.AppendAllText("LeaveYearTemp" + "/errorLog.txt", exp.Message); throw exp; } finally { DisposeExcelObject(); } }
public void Clean() { // Show excel app excel.ScreenUpdating = true; excel.Visible = true; //Collect try { Marshal.ReleaseComObject(rg); Marshal.ReleaseComObject(rgHeader); Marshal.ReleaseComObject(worksheet); Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(excel); } catch (Exception e) { } rg = null; rgHeader = null; worksheet = null; workbook = null; excel = null; GC.Collect(); }
private void SaveDirectory(Workbooks workbooks, DirectoryInfo currentInputDirectory, DirectoryInfo currentOutputDirectory) { foreach(var subDir in currentInputDirectory.GetDirectories()) { SaveDirectory(workbooks, subDir, currentOutputDirectory.CreateSubdirectory(subDir.Name)); } foreach(var file in currentInputDirectory.GetFiles( "*.csv", SearchOption.TopDirectoryOnly)) { if(!currentOutputDirectory.Exists) { currentOutputDirectory.Create(); } SaveWorkbook(workbooks, file.FullName, Path.Combine(currentOutputDirectory.FullName, Path.GetFileNameWithoutExtension(file.Name) + ".xlsx")); } }
//创建一个Excel对象 public void Create() { app = new Application(); wbs = app.Workbooks; wb = wbs.Add(true); }
//打开一个Excel文件 public void Open(string FileName) { app = new Application(); wbs = app.Workbooks; wb = wbs.Add(FileName); //wb = wbs.Open(FileName, 0, true, 5,"", "", true, XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing); //wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing); mFilename = FileName; }
public void Dispose() { #if DEBUG Console.WriteLine("Cleaning up"); #endif _isDisposed = true; if (_worksheet != null) { for (int i = 0; i < _worksheet.Length; i++) { if (_worksheet[i] != null) { Marshal.ReleaseComObject(_worksheet[i]); } _worksheet[i] = null; } } _worksheet = null; if (_wTemplate != null) { _wTemplate.Close(false, Missing.Value, Missing.Value); Marshal.ReleaseComObject(_wTemplate); } _wTemplate = null; if (_workbooks != null) { _workbooks.Close(); Marshal.ReleaseComObject(_workbooks); } _workbooks = null; if (_excel != null) { _excel.Quit(); Marshal.ReleaseComObject(_excel); } _excel = null; }
public void OpenTemplate() { #if DEBUG Console.WriteLine("Opening up excel template file"); #endif if (_excel == null || _isDisposed) throw new ObjectDisposedException("excel", "Either the excel app has been closed, or it was never opened"); if (!File.Exists(_loadPathTemplate)) throw new FileNotFoundException(string.Format("Unable to find template file at path: {0}", _loadPathTemplate)); _workbooks = _excel.Workbooks; _wTemplate = _workbooks.Open(_loadPathTemplate, 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); _worksheet = new Worksheet[] { _wTemplate.Worksheets[1], _wTemplate.Worksheets[2], _wTemplate.Worksheets[3] }; }
private static void SaveWorkbook(Workbooks workbooks, string inPath, string outPath) { Workbook workbook = null; try { workbook = workbooks.Open(inPath); workbook.SaveAs(outPath, XlFileFormat.xlWorkbookDefault); } finally { if(workbook != null) { workbook.Close(); Marshal.FinalReleaseComObject(workbook); } } }
public ExcelCmd() { ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ExcelWorkbooks = ExcelApp.Workbooks; }
protected override bool OpenInternal(string fileName, bool write) { if (_obj != null) return false; _app = new Application(); _app.Visible = false; _app.DisplayAlerts = false; _app.ScreenUpdating = false; _workbooks = _app.Workbooks; if (!_write && File.Exists(fileName)) { _obj = _workbooks.Open(fileName); } else { _obj = _workbooks.Add(); } _worksheets = _obj.Worksheets; return true; }
//关闭一个Excel对象,销毁对象 public void Close() { //wb.Save(); wb.Close(Type.Missing, Type.Missing, Type.Missing); wbs.Close(); app.Quit(); wb = null; wbs = null; app = null; GC.Collect(); }
private void openExcel(String filename) { app.Visible = false; this.wbks = app.Workbooks; r_wbk= wbks.Add(filename); }
private void Form1_Load(object sender, EventArgs e) { 周次数.Text = GetWeekOfYear().ToString(); excelApplication = null;//Excel Application Object excelWorkBooks = null;//Workbooks excelWorkBook = null;//Excel Workbook Object excelWorkSheet = null;//Excel Worksheet Object ActiveSheetIndex = 1; System.Data.DataTable dt = new System.Data.DataTable(); dt.TableName = "table1"; dt.AcceptChanges(); }
private void initExcel() { try { excelApp = new ApplicationClass(); workbooks = excelApp.Workbooks; workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); worksheet =(Worksheet) workbook.Worksheets[1]; worksheet.Cells[1, 1] = "No"; worksheet.Cells[1, 2] = "Picture"; worksheet.Cells[1, 3] = "Time"; worksheet.Cells[1, 4] = "CorrectRate"; worksheet.Cells[1, 5] = "MaxGrasp"; worksheet.Cells[1, 6] = "MaxSpeed"; worksheet.Cells[1, 7] = "DetailData"; } catch(Exception exp){ throw(exp); } }
internal WorkbookList(Workbooks workbooks) { this.workbooks = workbooks; }