public static List <List <string> > Read(string fileName, int columnCount) { List <List <string> > textList = new List <List <string> >(); excelApp = new ApplicationClass(); Workbooks myWorkBooks = excelApp.Workbooks; myWorkBooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Sheets sheets = excelApp.Sheets; mySheet1 = (Worksheet)sheets[1]; int rowCount = mySheet1.UsedRange.Rows.Count; if (rowCount != 0) { for (int i = 3; i <= rowCount; i++) { string name = ((Range)mySheet1.Cells[i, 2]).Text.ToString(); if (name != "") { List <string> list = new List <string>(); for (int j = 0; j < columnCount; j++) { list.Add(((Range)mySheet1.Cells[i, j + 1]).Text.ToString()); } textList.Add(list); } } } myWorkBooks.Close(); excelApp.Quit(); excelApp = null; return(textList); }
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 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]; } }
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; }
/// <summary> /// Open an existing Excel workbook. /// </summary> /// <param name="filename">path of Excel file to open</param> /// <param name="worksheet">optional worksheet to set as the active sheet</param> public void Open(string filename, string worksheet = "") { // Open the specified workbook. _workbooks = _excelApp.Workbooks; _workbook = _workbooks.Open(filename); // If the object was not created... if (_workbook == null) { // The file was not able to be opened. throw new Exception("Unable to open Excel workbook."); } // If a worksheet was not specified... if (string.IsNullOrEmpty(worksheet)) { // Act upon the active worksheet. _worksheet = _workbook.ActiveSheet; } // If a worksheet was specified... else { // Select an existing worksheet. _worksheet = _workbook.Worksheets[worksheet]; } }
private static HttpGetHelper httpGetHelper; //定义HttpGetHelper获取经纬度对象 public static void Process(string originalFileName, int originalColumn, int targetColumn, int rows) { try { string workTmp = originalFileName; objExcelApp = new Application(); objExcelWorkBooks = objExcelApp.Workbooks; objExcelWorkbook = objExcelWorkBooks.Open(workTmp, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets[1]; httpGetHelper = new HttpGetHelper(); for (int i = 2; /*objExcelWorkSheet.Cells[i, originalColumn].Text.ToString() != ""*/ i < rows + 2; i++) { string address = objExcelWorkSheet.Cells[i, originalColumn].Text.ToString(); string str = httpGetHelper.GaoDeAnalysis("key=3e0ded4b2852e194c63565d151c2e606&address=" + address); objExcelWorkSheet.Cells[i, targetColumn] = str; } string targetFileName = originalFileName.Insert(originalFileName.LastIndexOf('.'), "new"); objExcelWorkbook.SaveAs(targetFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } finally { objExcelApp.Quit(); } }
/// <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); } }
public void Init() { _excelApp = new Excel.Application(); _excelApp.DisplayAlerts = false; _excelApp.Visible = false; _workbooks = _excelApp.Workbooks; _wb = _workbooks.Open($@"{AppDomain.CurrentDomain.BaseDirectory}\blank2.xls"); SaveFileDialog dialog = new SaveFileDialog(); dialog.FileName = $"Акт списания {Extensions.RemoveIllegalCharsFromFilename(ActNumber)}. Сформировано {DateTime.Now.ToShortDateString()}.xls"; dialog.Filter = "Файлы Excel(*.xls; *.xlsx) | *.xls; *.xlsx"; dialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);//@"e:\";// if (dialog.ShowDialog() != DialogResult.OK) { throw new Exception("Файл не сохранён!"); } _wb.SaveAs(dialog.FileName); this._fileName = dialog.FileName; _wb.Close(); _wb = _excelApp.Workbooks.Open($@"{dialog.FileName}"); _excelApp.Visible = true; _ws1 = _wb.Worksheets["стр.1"]; _ws2 = _wb.Worksheets["стр.2"]; _ws3 = _wb.Worksheets["стр.3"]; _ws2.Select(); _currentWS = _ws2; }
private void ImportButton_Click(object sender, EventArgs e) { OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.Title = "Выберите файл Excel"; openFileDialog.Filter = "Файлы Excel(*.xls;*.xlsx)|*.xls;*.xlsx"; openFileDialog.Multiselect = false; if (!(openFileDialog.ShowDialog() == DialogResult.Cancel)) { string fileExcel = openFileDialog.FileName; Excel.Application excel = new Excel.Application(); Workbooks workbooks = excel.Workbooks; Workbook workbook = workbooks.Open(fileExcel); Worksheet worksheet = workbook.Worksheets.get_Item(1); Range range = worksheet.UsedRange; for (int i = 2; i <= range.Rows.Count; i++) { if (range.Cells[i, 1].Value != null && range.Cells[i, 2].Value != null && range.Cells[i, 3].Value != null) { CardsDataGridView.Rows.Add(range.Cells[i, 1].Value.ToString(), range.Cells[i, 2].Value.ToString(), range.Cells[i, 3].Value.ToString(), 4, LocationComboBox.SelectedValue); } } workbooks.Close(); excel.Quit(); CardsPanel.Visible = true; } }
public Workbook OpenExcelByMSExcel(string filePath) { workbook = _objWorkbooks.Open(filePath, Type.Missing, false, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); return(workbook); }
private static Dictionary <string, int> SelectData(string filename) { Application excelApplication = new Application(); Workbooks workbooks = excelApplication.Workbooks; Workbook workbook = workbooks.Open(filename); Sheets sheets = workbook.Worksheets; Worksheet worksheet = (Worksheet)sheets.Item[1]; Range firstColumn = worksheet.UsedRange.Columns[1]; Range secondColumn = worksheet.UsedRange.Columns[2]; var keys = (Array)firstColumn.Cells.Value; var values = (Array)secondColumn.Cells.Value; var keysArray = keys.OfType <object>().Select(o => o.ToString()).ToArray(); var valuesArray = values.OfType <object>().Select(o => int.Parse(o.ToString())).ToArray(); var result = new Dictionary <string, int>(); for (int i = 0; i < keysArray.Count(); i++) { result.Add(keysArray[i], valuesArray[i]); } workbook.Close(filename); excelApplication.Quit(); Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(excelApplication); return(result); }
public void readExcelFile(int totalRows, int totalColumns) { try { string excelTemplate = System.AppDomain.CurrentDomain.BaseDirectory + "template.xlsx"; if (!File.Exists(excelTemplate)) { throw new Exception("Excel template not found in " + excelTemplate); } xlApp = new Microsoft.Office.Interop.Excel.Application(); xlWorkBooks = xlApp.Workbooks; try { xlWorkBook = xlWorkBooks.Open(excelTemplate, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false); //open the template file! } catch (Exception e) { MessageBox.Show("template file is not spelled correctly or is not in the same directory as EBOMCreationTool.exe"); mainframe.end = true; return; } xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1); //worksheet to write data to getCells(totalRows, totalColumns); } finally { mainframe.WriteToConsole("Finished reading Excel File"); } }
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); }
private void btnImport_Click(object sender, EventArgs e) { Workbooks workbooks = xlsApp.Workbooks; workbook = workbooks.Open("D:\\temp\\user.xls");//索引从1开始 Worksheet sheet = workbook.Worksheets.Item[1]; textBox1.Text += sheet.Name + "\r\n"; textBox1.Text += sheet.Range["A1"].Value2 + "\r\n"; Range rows = sheet.Rows; textBox1.Text += "一共 " + rows.Count + "行\r\n"; Range cells = sheet.Cells; textBox1.Text += "一共 " + cells.Count + "个单元格\r\n"; //读取任意一个单元格 string val = ((Range)sheet.Cells[1, 1]).Text.ToString(); textBox1.Text += val + "\r\n"; for (int i = 0; i < 3; i++) { string val1 = ((Range)sheet.Cells[2, i + 1]).Text.ToString(); textBox1.Text += val1 + "\r\n"; } }
public ActionResult ChildExcelOpen(string File) { try { string path = Server.MapPath("~/Content/UploadedFolder/File"); string openPath = ""; string[] filePaths = Directory.GetFiles(path, "*.xls"); foreach (var item in filePaths) { if (File == Path.GetFileName(item)) { openPath = Path.Combine(Server.MapPath("~/Content/UploadedFolder/File"), Path.GetFileName(item)); } } //string patha = Server.MapPath("~/Content/Loan Calculation.xlsx"); var excelApp = new Application(); excelApp.Visible = true; Workbooks books = excelApp.Workbooks; Workbook sheet = books.Open(openPath); // System.IO.File.Open(childpath, FileMode.Open); //Application excel = new Application(); //var lstEmp = scheModel.tblExcelMasters.Where(x => x.MasterExcel_Id == id).FirstOrDefault(); return(Json("Success", JsonRequestBehavior.AllowGet)); } catch (Exception ex) { throw; } }
static public int prtPageSize = 10;//19 public Workbook OpenExcel(string fileName) { Application app = new Application(); Workbooks wbks = app.Workbooks; return(wbks.Open(fileName)); }
public void OpenReportTempalte() { if (m_oExcelApp != null) { CloseReportTemplate(); } // Create an instance of Microsoft Excel, make it visible, // and open Book1.xls. //m_oExcelApp = new Excel.ApplicationClass(); m_oExcelApp = GetExcelApplication(); m_oBooks = m_oExcelApp.Workbooks; //m_oExcelApp.AskToUpdateLinks = false; // IMPORTANT: IF YOU ARE USING EXCEL Version >= 10.0 Use function // prototype in "EXCEL VERSION 10.0" section. // For Excel Version 9.0 use default "EXCEL VERSION = 9.0". // This application is not tested with versions lower than Excel 9.0 // Or greater than 10.0 // EXCEL VERSION 10.0 m_oBook = m_oBooks.Open(m_sReportTemplate, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing); // END EXCEL VERSION 10.0 // EXCEL VERSION 9.0 //m_oBook = m_oBooks.Open(m_sReportTemplate, m_oMissing, m_oMissing, // m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, // m_oMissing, m_oMissing, m_oMissing, m_oMissing,m_oMissing,m_oMissing); // END EXCEL VERSION 9.0 m_oSheet = (_Worksheet)m_oBook.Worksheets[m_nReportIndex]; }
// 打开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 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 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; } }
// 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 { } }
private void ShowStartDocument() { _app.Visible = true; _wbs = _app.Workbooks; _wb = _wbs.Open(Path.GetFullPath(PathResolver.GenerateExcelPracticePath(DocumentPath))); _wb.Activate(); _wb.BeforeClose += OnBeforeCloseExcel; }
/// <summary> /// 指定したファイルのエクセルを開く /// </summary> /// <param name="fileName"></param> private void OpenExcelFile(string fileName) { if (xlBook == null) { this.xlBooks = xlApp.Workbooks; this.xlBook = xlBooks.Open(fileName); } }
public ExcelFunction(string path, int Sheet) { excel.DisplayAlerts = false; this.path = path; wb = wbs.Open(this.path); ws = (Worksheet)wb.Worksheets[Sheet]; range = ws.UsedRange; }
/// <summary> /// Opens the book. /// </summary> /// <param name="fileName">Name of the file.</param> /// <param name="sheetNumber">The sheet number.</param> public void OpenBook(string fileName, int sheetNumber) { using (new ExcelUILanguageHelper()) { _application = new Application(); _books = _application.Workbooks; _book = _books.Open(fileName, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue); _sheet = (Worksheet)_book.Worksheets.Item[sheetNumber]; } }
public ExcelConfig() { app = new Application(); workbooks = app.Workbooks; string projectPath = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName; workbook = workbooks.Open(projectPath + "\\" + "ExcelMobileSite" + ".xlsx"); // workbook = workbooks.Open(@"F:\VS-SeleniumProjectDir\SeleniumTest1\SeleniumTest1\ExcelMobileSite.xlsx"); }
public static bool ConvertExcelToPdf(string inputFile, string outputfile) { string outputFileName = outputfile; Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); excelApp.Visible = false; Workbook workbook = null; Workbooks workbooks = null; try { //ExportAsFixedFormatXlFixedFormatType workbooks = excelApp.Workbooks; workbook = workbooks.Open(inputFile); workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, outputFileName, XlFixedFormatQuality.xlQualityStandard, true, true, Type.Missing, Type.Missing, false, Type.Missing); } catch (Exception) { MessageBox.Show("Co loi xay ra"); return(false); } finally { if (workbook != null) { workbook.Close(XlSaveAction.xlDoNotSaveChanges); while (Marshal.FinalReleaseComObject(workbook) != 0) { } ; workbook = null; } if (workbooks != null) { workbooks.Close(); while (Marshal.FinalReleaseComObject(workbooks) != 0) { } ; workbooks = null; } if (excelApp != null) { excelApp.Quit(); excelApp.Application.Quit(); while (Marshal.FinalReleaseComObject(excelApp) != 0) { } ; excelApp = null; } } return(true); }
public async Task LoadWorkbookAsync(string filePath, bool isReadOnly = true) { await Task.Run(new System.Action(() => { workbooks2 = xlApp.Workbooks; xlWorkbook = workbooks2.Open(Filename: filePath, ReadOnly: isReadOnly); } )); }
public string ConvertExcelToPdf(string inputFile, string pdfPath) { Application excelApp = new Application(); excelApp.Visible = false; Workbook workbook = null; Workbooks workbooks = null; try { workbooks = excelApp.Workbooks; workbook = workbooks.Open(inputFile); workbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, pdfPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } finally { if (workbook != null) { workbook.Close(XlSaveAction.xlDoNotSaveChanges); while (Marshal.FinalReleaseComObject(workbook) != 0) { } ; workbook = null; } if (workbooks != null) { workbooks.Close(); while (Marshal.FinalReleaseComObject(workbooks) != 0) { } ; workbooks = null; } if (excelApp != null) { excelApp.Quit(); excelApp.Application.Quit(); while (Marshal.FinalReleaseComObject(excelApp) != 0) { } ; excelApp = null; } GC.Collect(); GC.WaitForPendingFinalizers(); } return(pdfPath); }
/// <summary> /// Loads an embedded resource add-in. /// </summary> /// <param name="resourceName">Addin as 'embedded resource'</param> /// <returns>File name of the temporary file that the resource /// was written to.</returns> internal string LoadAddinFromEmbeddedResource(string resourceName) { Stream resourceStream = typeof(Instance).Assembly .GetManifestResourceStream(resourceName); if (resourceStream == null) { Logger.Error("LoadAddinFromEmbeddedResource: Unable to read embedded resource '{0}'", resourceName); throw new IOException("Unable to open resource stream " + resourceName); } string addinPath; Workbook loadedAddin = FindWorkbook(resourceName); if (loadedAddin == null) { string tempDir = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName()); Directory.CreateDirectory(tempDir); addinPath = Path.Combine(tempDir, resourceName); Stream tempStream = File.Create(addinPath); resourceStream.CopyTo(tempStream); tempStream.Close(); resourceStream.Close(); try { Logger.Info("LoadAddinFromEmbeddedResource: Loading..."); Workbooks.Open(addinPath); } catch (System.Runtime.InteropServices.COMException) { Logger.Warn("LoadAddinFromEmbeddedResource: COM exception caught, falling back to CorruptLoad"); DisableDisplayAlerts(); try { Workbooks.Open(addinPath, CorruptLoad: XlCorruptLoad.xlExtractData); } catch (System.Runtime.InteropServices.COMException e) { Logger.Fatal("LoadAddinFromEmbeddedResource: COM exception occurred after calling Workbooks.Open"); Logger.Fatal(e); throw new XLToolbox.Excel.ExcelException("Excel failed to load the legacy Toolbox add-in", e); } finally { EnableDisplayAlerts(); } } Logger.Info("LoadAddinFromEmbeddedResource: Loaded {0}", addinPath); } else { addinPath = loadedAddin.FullName; Logger.Info("LoadAddinFromEmbeddedResource: Already loaded, path is {0}", addinPath); } return(addinPath); }
/// <summary> /// Locates a workbook and returns it. If the workbook is loaded already, /// it will not be re-opened. If the workbook cannot be located, this method /// returns null. /// </summary> /// <param name="name">Name (either bare name or full name with path) of the /// workbook being sought. If this is null or whitespace, the active workbook /// is returned.</param> /// <returns>Workbook or null</returns> public Workbook LocateWorkbook(string name) { Predicate <Workbook> predicate; if (Path.GetFileName(name) == name) { // Find workbook by name only predicate = new Predicate <Workbook>(wb => wb.Name == name); } else { // Find workbook by full name (including path) predicate = new Predicate <Workbook>(wb => wb.FullName == name); } Workbook foundWorkbook = null; if (!String.IsNullOrWhiteSpace(name)) { Logger.Info("LocateWorkbook: Locating {0}", name); for (int i = 1; i <= Workbooks.Count; i++) { Workbook workbook = Workbooks[i]; if (predicate(workbook)) { Logger.Info("LocateWorkbook: Workbook already loaded"); foundWorkbook = workbook; break; } Bovender.ComHelpers.ReleaseComObject(workbook); } if (foundWorkbook == null) { Logger.Info("LocateWorkbook: Workbook not loaded, attempting to open it"); try { foundWorkbook = Workbooks.Open(name); } catch (Exception e) { Logger.Warn("LocateWorkbook: Failed to open workbook"); Logger.Warn(e); } } } else { Logger.Info("LocateWorkbook: Using active workbook"); foundWorkbook = Instance.Default.ActiveWorkbook; } if (foundWorkbook == null) { Logger.Warn("LocateWorkbook: Unable to locate workbook"); } return(foundWorkbook); }
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 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); } } }
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; }