public void readExcel() { string valueString = string.Empty; objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objBooks = (Excel.Workbooks)objExcelApp.Workbooks; //Open the workbook containing the address data. objBook = objBooks.Open(@"C:\Temp\data\Test.xlsx", 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); //Get a reference to the first sheet of the workbook. objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); //Select the range of data containing the addresses and get the outer boundaries. rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell); long lLastRow = rngLast.Row; long lLastCol = rngLast.Column; // Iterate through the data and concatenate the values into a comma-delimited string. for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++) { for (long colCounter = 1; colCounter <= lLastCol; colCounter++) { //Write the next value into the string. Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter]; string cellvalue = cell.Value.ToString(); //TODO: add your business logic for retrieve cell value } } }
private Worksheet objExcelWorkSheet; //定义Workbook工作表对象 #endregion Fields #region Methods // Change the Mapping WorkFlow @2015-7-16 By CC public void EditWorkFlow(string StrTestData) { objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objExcelWorkBooks = objExcelApp.Workbooks; objExcelWorkbook = objExcelWorkBooks.Open(StrTestData, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["WorkFlow"]; //strSheetName is the Sheet Name of Exce,if there is no name then Defult is "1" objExcelWorkSheet.Select(Type.Missing); int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //get the Row Number int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//get the column number int rowNo = 0; int ParameterRowNo = 0; int columnNo = 0; int FlagColumnNo = 0; for (int j = 1; j <= columnsint; j++) { string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, j]).Text.ToString(); if (strColumnsName == "Flag") { FlagColumnNo = j; } if (strColumnsName == "BusinessFlow") { rowNo = j; } if (strColumnsName == "ParameterValue") { ParameterRowNo = j; } } for (int i = 2; i <= rowsint; i++) { string strFlagValue = ((Range)objExcelWorkSheet.Cells[i, FlagColumnNo]).Text.ToString(); if (strFlagValue =="Y") { int RowNO = i; objExcelWorkSheet.Cells[RowNO, FlagColumnNo] = "N"; } string strBusinessFlow = ((Range)objExcelWorkSheet.Cells[i, rowNo]).Text.ToString(); if (strBusinessFlow == "HeaderMapping" || strBusinessFlow == "TaxMapping" || strBusinessFlow == "LineItemsMapping") { columnNo = i; string strParameterValue = ((Range)objExcelWorkSheet.Cells[i, ParameterRowNo]).Text.ToString(); if (strParameterValue == "IR" || strParameterValue == "TradeBilling" || strParameterValue == "TradeCredit" || strParameterValue == "TradeDebit" || strParameterValue == "TradeReturn") { objExcelWorkSheet.Cells[i, 1] = "Y"; } } } objExcelWorkbook.Save(); objExcelWorkbook.Close(false, StrTestData, false); objExcelApp.Quit(); NAR(objExcelApp); NAR(objExcelWorkbook); NAR(objExcelWorkSheet); }
private void OpenExcel() { myExcelApp = new Excel.Application(); myExcelApp.Visible = false; myExcelWorkbooks = myExcelApp.Workbooks; String fileName = Application.StartupPath + "\\Data\\pttform.xls";; myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet; //HandleRef hwnd = new HandleRef(myExcelApp, (IntPtr)myExcelApp.Hwnd); //GetWindowThreadProcessId(hwnd, out pid); }
public override void openExcel(string file) { if (File.Exists(file)) { application = new excel.Application(); application.DisplayAlerts = false; workBooks = application.Workbooks; workBook = workBooks.Open(file, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); sheets = workBook.Sheets; sheet = sheets.get_Item(1); } }
private void button3_Click(object sender, EventArgs e) { txtLibro.Text = @"V:\Varios\Listas OFAC\PEPS-Subir\LISTA PEPS - CNBS--YA"; // @"V:\Varios\Listas OFAC\PEPS-Subir\Listas PEPS - Periodo 2010 al 2018--YA.xls"; txtHoja.Text = "Hoja1"; var excelApp = new Excel.Application(); excelApp.Visible = true; Excel.Workbooks books = excelApp.Workbooks; Excel.Workbook hoja = books.Open(txtLibro.Text); }
public void OpenExcel(string excelFileName, string sheetName) { mHasException = false; try { var annihilationList = new List <object>(); Excel.Application xlApp = null; Excel.Workbooks xlWorkBooks = null; Excel.Workbook xlWorkBook = null; Excel.Worksheet xlWorkSheet = null; Excel.Sheets xlWorkSheets = null; xlApp = new Excel.Application(); annihilationList.Add(xlApp); xlApp.DisplayAlerts = false; xlWorkBooks = xlApp.Workbooks; annihilationList.Add(xlWorkBooks); xlWorkBook = xlWorkBooks.Open(excelFileName); annihilationList.Add(xlWorkBook); xlApp.Visible = false; xlWorkSheets = xlWorkBook.Sheets; annihilationList.Add(xlWorkSheets); for (var index = 1; index <= xlWorkSheets.Count; index++) { xlWorkSheet = (Excel.Worksheet)xlWorkSheets[index]; annihilationList.Add(xlWorkSheet); if (xlWorkSheet.Name == sheetName) { break; } annihilationList.Add(xlWorkSheet); } xlWorkBook.Close(); xlApp.UserControl = true; xlApp.Quit(); ReleaseObjects(annihilationList); } catch (Exception ex) { mHasException = true; mLastException = ex; } }
private void openDocument() { Debug.WriteLine("openDocument", GetType().Name); var fname = System.IO.Path.Combine(workingDir, simFile); //Excel works better with an absolute path. string speadsheet = System.IO.Path.GetFullPath(fname); Excel.Workbooks workbooks = o_xlApp.Workbooks; Debug.WriteLine("workbooks.Open", GetType().Name); o_xlWorkbook = workbooks.Open(speadsheet, 0, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, false, false, 0, true, 1, 0); Debug.WriteLine("Marshal.ReleaseComObject: workbooks", GetType().Name); Marshal.ReleaseComObject(workbooks); }
public void mofileexcelvualuu() { if (duongdanfileexcel != null) { var app = new excel.Application(); excel.Workbooks book = app.Workbooks; excel.Workbook sh = book.Open(duongdanfileexcel); app.Visible = true; sh.PrintOutEx(); } }
private static void ExtractDataFromExcel() { Excel.Application excelApp = null; Excel.Workbooks workBooks = null; Excel.Workbook workBook = null; Excel.Sheets workSheets = null; Excel.Worksheet workSheet = null; try { OpenFile("Excel files(*.xls*)|*.xls*"); if (!string.IsNullOrWhiteSpace(FileName)) { excelApp = new Excel.Application { Visible = false, ScreenUpdating = false, EnableEvents = false }; workBooks = excelApp.Workbooks; workBook = workBooks.Open(FileName); workSheets = workBook.Worksheets; workSheet = (Excel.Worksheet)workSheets.get_Item(1); ReadFromRow(workSheet); MessageBox.Show("Extract successful!", "Congratulation!", MessageBoxButton.OK); } } catch (FileNotFoundException) { MessageBox.Show("File not found!", "Error!", MessageBoxButton.OK); } catch (Exception ex) { MessageBox.Show($"Work is failed.\n{ex.Message}\n", "Error!", MessageBoxButton.OK); } finally { if (FileName != null) { workBook.Close(); excelApp.Quit(); FileName = null; Marshal.ReleaseComObject(workSheet); Marshal.ReleaseComObject(workSheets); Marshal.ReleaseComObject(workBook); Marshal.ReleaseComObject(workBooks); Marshal.ReleaseComObject(excelApp); } } }
public bool MonthlyLog() { if (!File.Exists(file)) { MessageBox.Show("Database can not be found"); return(false); } if (File.Exists(file)) { Excel.Application xlApp = new Excel.Application(); var date = DateTime.Now; var datex = $"{date.Day}_{date.Month}_{ date.Year}_{date.Minute}_{date.Hour}"; var newbook = $@"C:\Users\kyle.marshall\Documents\Projects\Inventory\Monthly_Log_{datex}.xlsx"; Excel.Workbooks xlWorkBooks = xlApp.Workbooks; Excel.Workbook xlWorkBook = xlWorkBooks.Open(file); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(4); object misValue = System.Reflection.Missing.Value; xlWorkSheet.Copy(misValue, misValue); var x = Task.Run(async delegate { await Task.Delay(5000); }); x.Wait(); xlApp.Workbooks[1].SaveAs(Filename: newbook); var t = Task.Run(async delegate { await Task.Delay(5000); }); t.Wait(); foreach (Excel.Workbook book in xlWorkBooks) { foreach (Excel.Worksheet sheet in book.Sheets) { Marshal.ReleaseComObject(sheet); } book.Close(false); Marshal.ReleaseComObject(book); } xlWorkBooks.Close(); Marshal.ReleaseComObject(xlWorkBooks); xlApp.Quit(); Marshal.ReleaseComObject(xlApp); GC.Collect(); return(true); } return(false); }
private void openExcelTemplate() { createExcelResult(); if (File.Exists(excelResult)) { application = new excel.Application(); application.DisplayAlerts = false; workBooks = application.Workbooks; workBook = workBooks.Open(excelResult, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); sheets = workBook.Sheets; sheet = sheets.get_Item(1); excelUtils.setSheet(sheet); } }
/// <summary> /// Opens the MinMax Document. /// </summary> public void Open() { try { if (Process.GetProcessesByName("EXCEL").Count() > 0) { // Try to get an open instance of Excel. try { myApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); Log.WriteLine("Instance of Excel Found"); } catch (COMException e) { Console.WriteLine("No Instance of Excel Found:\n" + e.Message); } } else { try { // Try to Create new instance of Excel. myApp = new Excel.Application(); Log.WriteLine("New Instance of Excel Created."); } catch (Exception) { Log.WriteLine("Cannot Access File on network, try Again."); } } myApp.Visible = true; // True to see new instance, false to hide // Set the objects to corresponding excel objects myBooks = myApp.Workbooks; myBook = myBooks.Open(minMaxPath); mySheet = myBook.Sheets["Marlin Steel"]; setRange(); // All excel objects are referenced excelObjSet = true; Log.WriteLine("Min-Max Document Opened."); } catch { Log.WriteLine("Cannot Access Min-Max Document."); } }
// private Excel.Worksheet sheet; public override void Convert(String inputFile, String outputFile) { Object nothing = Type.Missing; try { if (!File.Exists(inputFile)) { throw new ConvertException("File not Exists"); } if (IsPasswordProtected(inputFile)) { throw new ConvertException("Password Exist"); } app = new Excel.Application(); books = app.Workbooks; book = books.Open(inputFile, false, true, nothing, nothing, nothing, true, nothing, nothing, false, false, nothing, false, nothing, false); bool hasContent = false; foreach (Excel.Worksheet sheet in book.Worksheets) { Excel.Range range = sheet.UsedRange; if (range != null) { Excel.Range found = range.Cells.Find("*", nothing, nothing, nothing, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, nothing, nothing, nothing); if (found != null) { hasContent = true; } releaseCOMObject(found); releaseCOMObject(range); } } if (!hasContent) { throw new ConvertException("No Content"); } book.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, outputFile, Excel.XlFixedFormatQuality.xlQualityMinimum, false, false, nothing, nothing, false, nothing); } catch (Exception e) { release(); throw new ConvertException(e.Message); } release(); }
public XL(string path, bool showUi) { mPath = path; //Start Excel and get Application object. mApplication = new Excel.Application(); mApplication.Visible = true; mWorkbooks = mApplication.Workbooks; mWorkbook = mWorkbooks.Open(path); mActiveSheet = mWorkbook.ActiveSheet; mWorksheets = mWorkbook.Worksheets; mApplication.Visible = showUi; mApplication.UserControl = showUi; }
public ExcelApplication(String argosFile) { someFile = argosFile; helloWorld = "Hello World"; mainApp = new Excel.Application(); mainAppWorkbooks = mainApp.Workbooks; // opening workbook causes leak argosWorkbook = mainAppWorkbooks.Open(argosFile, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); argosWorksheets = argosWorkbook.Worksheets; aSheet = argosWorksheets.get_Item(1); aRange = aSheet.UsedRange; argosRowCount = aRange.Rows.Count; argosColumnCount = aRange.Columns.Count; }
public void openexcel() { xlapp = new xl.Application(); workbooks = xlapp.Workbooks; workbook = workbooks.Open(xlFilepath); //storing workshetnames in hashtable int count = 1; foreach (xl.Worksheet sheet in workbook.Sheets) { sheets[count] = sheet.Name; count++; } }
void InitialExcelSetting() { if (app == null) { Console.WriteLine("Excel无法启动"); return; } app.Visible = true; wbs = app.Workbooks; //wb = wbs.Add(Missing.Value); string templatePath = Directory.GetCurrentDirectory() + "\\LoadComparisonTemplate.xlsx"; wb = wbs.Open(templatePath); }
public void Load(string file) { if (string.IsNullOrEmpty(file)) { throw new SystemException("Unexpected! {DFDE83F4-CB29-4F08-A311-DBA404C844D0}"); } if (m_app != null) { throw new SystemException("Unexpected! {5E4F6BD1-B5B6-418F-BBE7-88E119417E88}"); } m_app = new Excel.Application(); m_workbooks = m_app.Workbooks; m_workbook = m_workbooks.Open(file); }
public void taovainfileexcel(DataTable dt, string tongma, string ngaybatdau, string ngayketthuc) { ExcelPackage ExcelPkg = new ExcelPackage(); ExcelWorksheet worksheet = ExcelPkg.Workbook.Worksheets.Add("hts"); worksheet.Cells["A1:C1"].Merge = true; worksheet.Cells["A2:C2"].Merge = true; worksheet.Cells["A3:C3"].Merge = true; worksheet.Cells["A1"].Value = "Danh mục VM _ Mã chưa trưng"; worksheet.Cells["A2"].Value = "Từ ngày: " + ngaybatdau + " - " + ngayketthuc; worksheet.Cells["A3"].Value = "Tổng mã chưa trưng: " + tongma; worksheet.Cells["A5"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light1); worksheet.Column(1).Width = 10; worksheet.Column(2).Width = 13; worksheet.Column(3).Width = 10; //worksheet.Cells[worksheet.Dimension.End.Row + 1, 1].Value = "Tổng sản phẩm:"; //worksheet.Cells[worksheet.Dimension.End.Row, 2].Value = tongsp; var allCells = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column]; var cellFont = allCells.Style.Font; cellFont.SetFromFont(new Font("Calibri", 10)); worksheet.PrinterSettings.LeftMargin = 0.2M / 2.54M; worksheet.PrinterSettings.RightMargin = 0.2M / 2.54M; worksheet.PrinterSettings.TopMargin = 0.2M / 2.54M; worksheet.Protection.IsProtected = false; worksheet.Protection.AllowSelectLockedCells = false; if (File.Exists("hts.xlsx")) { File.Delete("hts.xlsx"); } ExcelPkg.SaveAs(new FileInfo("hts.xlsx")); ExcelPkg.Dispose(); var app = new excel.Application(); excel.Workbooks book = app.Workbooks; excel.Workbook sh = book.Open(Path.GetFullPath("hts.xlsx")); //app.Visible = true; sh.PrintOutEx(); app.Quit(); Marshal.FinalReleaseComObject(app); Marshal.FinalReleaseComObject(book); }
public void OpenExcel(string FileName, string SheetName) { bool Proceed = false; Excel.Application xlApp = null; Excel.Workbooks xlWorkBooks = null; Excel.Workbook xlWorkBook = null; Excel.Worksheet xlWorkSheet = null; Excel.Sheets xlWorkSheets = null; xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlWorkBooks = xlApp.Workbooks; xlWorkBook = xlWorkBooks.Open(FileName); xlApp.Visible = false; xlWorkSheets = xlWorkBook.Sheets; for (int x = 1; x <= xlWorkSheets.Count; x++) { xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x]; if (xlWorkSheet.Name == SheetName) { Proceed = true; break; } System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet); xlWorkSheet = null; } xlWorkBook.Close(); xlApp.Quit(); ReleaseComObject(xlWorkSheets); ReleaseComObject(xlWorkSheet); ReleaseComObject(xlWorkBook); ReleaseComObject(xlWorkBooks); ReleaseComObject(xlApp); if (Proceed) { MessageBox.Show("Found sheet, do your work here."); } else { MessageBox.Show("Sheet not located"); } }
private string GetModifyDateExcel(string fullPath) { // Require both wkbs and wkb // If these vars are not declared, Excel instances do not close properly Excel.Application xlApp; Excel.Workbooks xlWkbs = null; Excel.Workbook xlWkb = null; try { xlApp = new Excel.Application() { DisplayAlerts = false, Visible = false }; } catch { return(appInstanceFail); } try { xlWkbs = xlApp.Workbooks; xlWkb = xlWkbs.Open(fullPath, false, true); return(xlWkb.BuiltinDocumentProperties("Last Save Time").Value.ToString() != "" ? xlWkb.BuiltinDocumentProperties("Last Save Time").Value.ToString() : blank); } catch { return(cannotOpen); } finally { if (xlWkb != null) { xlWkb.Close(false); Marshal.ReleaseComObject(xlWkb); } if (xlWkbs != null) { Marshal.ReleaseComObject(xlWkbs); } if (xlApp != null) { xlApp.Quit(); Marshal.ReleaseComObject(xlApp); } } }
private void prikazDijagramaClick() { var parent = System.IO.Directory.GetParent(Environment.CurrentDirectory); var path = parent.Parent.FullName + "\\XLSX\\" + "\\" + "PodaciKrive.xls"; string mySheet = path; var excelApp = new Excel.Application(); excelApp.Visible = true; Excel.Workbooks books = excelApp.Workbooks; Excel.Workbook sheet = books.Open(mySheet); }
public void OpenExcel() { xlApp = new xl.Application(); workbooks = xlApp.Workbooks; workbook = workbooks.Open(xlFilePath); sheets = new Hashtable(); int count = 1; // Storing worksheet names in Hashtable. foreach (xl.Worksheet sheet in workbook.Sheets) { sheets[count] = sheet.Name; count++; } }
public static void ShowImportTemplete(string TempleteName) { try { string filename = clsLxms.GetParameterValue("EXCEL_TEMPLETE_FILE"); if (!File.Exists(filename)) { MessageBox.Show(clsTranslate.TranslateString("Can not find the templete file") + ": \r\n" + filename, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } string destfile = @Path.GetTempPath() + TempleteName + ".xls"; Util.retValue1 = destfile; File.Copy(filename, destfile, true); Excel.Application m_objExcel = new Excel.Application(); m_objExcel.DisplayAlerts = false; Excel.Workbooks m_objBooks = m_objExcel.Workbooks; m_objBooks.Open(destfile, 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); Excel.Workbook m_objBook = (Excel.Workbook)m_objBooks.get_Item(1); Excel.Sheets sm_objSheets = (Excel.Sheets)m_objBook.Worksheets; Excel.Worksheet m_objSheet = (Excel.Worksheet)sm_objSheets.get_Item(TempleteName); for (int i = sm_objSheets.Count; i >= 1; i--) { Excel.Worksheet m_Sheet = sm_objSheets.get_Item(i); if (m_Sheet.Name.ToLower().CompareTo(TempleteName.ToLower()) != 0) { m_Sheet.Delete(); } } m_objSheet.Name = "Sheet1"; //m_objSheet.Activate(); System.Windows.Forms.Application.DoEvents(); m_objExcel.DisplayAlerts = false; m_objExcel.AlertBeforeOverwriting = false; //保存工作簿 m_objExcel.Visible = true; return; } catch (System.Exception ex) { MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } }
public void Open(string FileName)//打开一个Excel文件 { if (app == null) { app = new Excel.Application(); } //app.WindowState = Excel.XlWindowState.xlMinimized; //if (wb!=null) wb.Close(); wb = null; wbs = null; wbs = app.Workbooks; wb = wbs.Open(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlPlatform.xlWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); ws = GetSheet("单原料求解"); ws.Unprotect(); mFilename = FileName; }
public void openExcel() { myExcelApplication = null; myExcelApplication = new Excel.Application(); // create Excell App myExcelApplication.DisplayAlerts = false; // turn off alerts workbooks = myExcelApplication.Workbooks; myExcelWorkbook = workbooks.Open(excelFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // open the existing excel file myExcelWorkSheet = myExcelWorkbook.Worksheets[1]; // define in which worksheet, do you want to add data myExcelWorkSheet.Name = "WorkSheet 1"; // define a name for the worksheet (optinal) }
private void openExcelFile() { if (File.Exists(headersFile)) { application = new excel.Application(); application.DisplayAlerts = false; workBooks = application.Workbooks; workBook = workBooks.Open(headersFile, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); sheets = workBook.Sheets; } else { em.showErrorFileNotExist(headersFile); System.Environment.Exit(0); } }
static void ForLoopReleaseCOM() { Excel.Application app = null; Excel.Workbooks books = null; Excel.Workbook book = null; Excel.Sheets sheets = null; try { app = new Excel.Application(); books = app.Workbooks; book = books.Open(@"C:\Temp\ExcelBook1Sheets.xlsx"); sheets = book.Sheets; for (int i = 1; i <= sheets.Count; i++) { Excel.Worksheet sheet = sheets.Item[i]; Console.WriteLine(sheet.Name); if (sheet != null) { Marshal.ReleaseComObject(sheet); } } Console.Read(); book.Close(); app.Quit(); } finally { if (sheets != null) { Marshal.ReleaseComObject(sheets); } if (book != null) { Marshal.ReleaseComObject(book); } if (books != null) { Marshal.ReleaseComObject(books); } if (app != null) { Marshal.ReleaseComObject(app); } } }
private void btnGenerarExcel_Click(object sender, EventArgs e) { ExportExcel exportExcel = new ExportExcel(); string excelFilename = "Informe"; this.spmostrar_ReporteTableAdapter.Rows.Clear(); var dtInforme = ToDataTable(Conexion.MostrarDatos(this.spmostrar_ReporteTableAdapter)); exportExcel.Export_Ctr_Excel(dtInforme, excelFilename); //Abrir el documento de excel string mySheet = @"C:\Users\ibrah\Documents\Materias\Octavo Cuatrimestre\Base de Datos\" + excelFilename + ".xlsx"; var excelApp = new Excel.Application(); excelApp.Visible = true; Excel.Workbooks books = excelApp.Workbooks; Excel.Workbook sheet = books.Open(mySheet); }
private void OpenWorkbook() { _application = new Excel.Application() { DisplayAlerts = false }; _application.Application.Interactive = true; _application.Application.UserControl = true; _application.Visible = false; _workbooks = _application.Workbooks; _workbook = _workbooks.Open(FilePath, Type.Missing, this._ReadOnly, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);; }
private void merge(Excel.Workbooks Workbooks, Excel.Worksheet desWorksheet, List <String> files) { for (int i = 0; i < files.Count; i++) { Excel.Workbook wb = Workbooks.Open(files[i]); if (i == 0) { wb.Sheets[1].Range["a1"].CurrentRegion.Copy(desWorksheet.Cells[1, 1]); } else { int row = desWorksheet.Range["a1048576"].End[Excel.XlDirection.xlUp].Row; wb.Sheets[1].Range["a1"].CurrentRegion.Offset(1).Copy(desWorksheet.Cells[row + 1, 1]); } wb.Close(); } }
//◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆ /// <summary> /// シート数取得 /// ※注意※ これを実行するとプロセスが残ってしまう。 /// </summary> /// <param name="F_P_N"></param> /// <returns></returns> //◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆ public int Sheet_figure(string F_P_N) { Excel.Application Ap = null; Excel.Workbooks Bk = null; //Excel.Worksheet St = null; //Excel起動 Ap = new Excel.Application(); //Excel表示 Ap.Visible = false; Ap.DisplayAlerts = false; //Excelブック取得 Bk = Ap.Workbooks; Bk.Open(F_P_N, // 指定ファイルPath Type.Missing, // (省略可能)UpdateLinks (0 / 1 / 2 / 3) false, // (省略可能)ReadOnly (True / False ) Type.Missing, // (省略可能)Format // 1:タブ / 2:カンマ (,) / 3:スペース / 4:セミコロン (;) // 5:なし / 6:引数 Delimiterで指定された文字 Type.Missing, // (省略可能)Password Type.Missing, // (省略可能)WriteResPassword Type.Missing, // (省略可能)IgnoreReadOnlyRecommended Type.Missing, // (省略可能)Origin Type.Missing, // (省略可能)Delimiter Type.Missing, // (省略可能)Editable Type.Missing, // (省略可能)Notify Type.Missing, // (省略可能)Converter Type.Missing, // (省略可能)AddToMru Type.Missing, // (省略可能)Local Type.Missing // (省略可能)CorruptLoad ); int figure = Ap.Worksheets.Count; //■Excelファイルを閉じる Bk.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(Bk); Ap.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(Ap); //プロセス強制終了(プロセスが残ってしまった時の為 ※但しなぜか2度目以降は効かない。) //System.Diagnostics.Process[] ps = System.Diagnostics.Process.GetProcessesByName("EXCEL.EXE"); //foreach (System.Diagnostics.Process p in ps) p.Kill(); return(figure); }
// private Excel.Worksheet sheet; public override void Convert(String inputFile, String outputFile) { Object nothing = Type.Missing; try { if (!File.Exists(inputFile)) { throw new ConvertException("File not Exists"); } if (IsPasswordProtected(inputFile)) { throw new ConvertException("Password Exist"); } app = new Excel.Application(); books = app.Workbooks; book = books.Open(inputFile, false, true, nothing, nothing, nothing, true, nothing, nothing, false, false, nothing, false, nothing, false); bool hasContent = false; foreach (Excel.Worksheet sheet in book.Worksheets) { Excel.Range range = sheet.UsedRange; if (range != null) { Excel.Range found = range.Cells.Find("*", nothing, nothing, nothing, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, nothing, nothing, nothing); if (found != null) hasContent = true; releaseCOMObject(found); releaseCOMObject(range); } } if (!hasContent) throw new ConvertException("No Content"); book.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, outputFile, Excel.XlFixedFormatQuality.xlQualityMinimum, false, false, nothing, nothing, false, nothing); } catch (Exception e) { release(); throw new ConvertException(e.Message); } release(); }
public void Open(string strFilePath) { _strFilePath = strFilePath; _application = new Microsoft.Office.Interop.Excel.Application(); _workbooks = _application.Workbooks; _workbook = _workbooks.Open(_strFilePath, 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 = (Microsoft.Office.Interop.Excel.Worksheet)_workbook.Worksheets[1]; }
// Get The RunTime For First Run @2015-7-16 by CC public List<String> GetRunTimeForRun(string StrTestData) { objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objExcelWorkBooks = objExcelApp.Workbooks; objExcelWorkbook = objExcelWorkBooks.Open(StrTestData, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); //Open the Sheet objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["Global"]; //Exce工作簿的Sheet objExcelWorkSheet.Select(Type.Missing); //取得总记录行数 (包括标题列) int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //得到行数 int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//得到列数 string temp1 = ((Range)objExcelWorkSheet.Cells[1, 1]).Text.ToString(); //遍历得到RunTime int rowcolum = 0; List<String> str = new List<String>(); for (int i = 1; i <= columnsint; i++) { string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, i]).Text.ToString(); if (strColumnsName == "RunTime") { rowcolum = i;//Get Column No for (int j = 2; j <= rowsint; j++) { string StrRunTimeValue = ((Range)objExcelWorkSheet.Cells[j, i]).Text.ToString(); str.Add(StrRunTimeValue); } } } objExcelWorkbook.Close(false, StrTestData, false); objExcelApp.Quit(); NAR(objExcelApp); NAR(objExcelWorkbook); NAR(objExcelWorkSheet); return str; }
private Excel.Workbook GetActiveWorkbook() { oWorkbooks = this.ExcelApp.Workbooks; localAppPath = Path.Combine(localAppPath, Path.GetFileNameWithoutExtension(@excelFileName)); if (Directory.Exists(path: localAppPath)) { Directory.Delete(path: localAppPath, recursive: true); } Directory.CreateDirectory(path: localAppPath); return oWorkbooks.Open(@excelFileName, 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); }
// Insert Row in the Driver.xls public void InsertRowsValues(string StrDriverPath, string StrCaseName, string StrTestDataPath, string StrComCode, string StrAsset, string StrRunTime) { objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objExcelWorkBooks = objExcelApp.Workbooks; objExcelWorkbook = objExcelWorkBooks.Open(StrDriverPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["Driver"]; //strSheetName是指的Exce工作簿的Sheet名,如果没有命名则为"1" objExcelWorkSheet.Select(Type.Missing); int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //得到行数 if (rowsint > 1) { for (int i = 2;i <= rowsint; i++) { string strFlagValue = ((Range)objExcelWorkSheet.Cells[i, 1]).Text.ToString(); if (strFlagValue == "Y") { int RowNO = i; objExcelWorkSheet.Cells[RowNO, 1] = "N"; } } } if (rowsint == 1) { rowsint = 2; } objExcelWorkSheet.Cells[rowsint, 1] = "Y"; objExcelWorkSheet.Cells[rowsint, 2] = "fa_30_saptao_mec"; objExcelWorkSheet.Cells[rowsint, 3] = StrRunTime; objExcelWorkSheet.Cells[rowsint, 4] = StrRunTime; objExcelWorkSheet.Cells[rowsint, 5] = StrCaseName; objExcelWorkSheet.Cells[rowsint, 6] = StrTestDataPath; objExcelWorkSheet.Cells[rowsint, 7] = "Run"; objExcelWorkSheet.Cells[rowsint, 8] = StrComCode; objExcelWorkSheet.Cells[rowsint, 9] = StrAsset; objExcelWorkbook.Save(); objExcelWorkbook.Close(false, StrDriverPath, false); objExcelApp.Quit(); NAR(objExcelApp); NAR(objExcelWorkbook); NAR(objExcelWorkSheet); }
// Get the RunTime Values for ReRun & Mapping public string GetRunTimeVlaue(string StrOutputPath) { objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objExcelWorkBooks = objExcelApp.Workbooks; objExcelWorkbook = objExcelWorkBooks.Open(StrOutputPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); //Open the Sheet objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["Output"]; //Exce工作簿的Sheet objExcelWorkSheet.Select(Type.Missing); //取得总记录行数 (包括标题列) int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //得到行数 int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//得到列数 string temp1 = ((Range)objExcelWorkSheet.Cells[1, 1]).Text.ToString(); //遍历得到CompanyCode和TestAsset、Runtime int rowcolum = 0; for (int i = 1; i <= columnsint; i++) { string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, i]).Text.ToString(); if (strColumnsName == "RunTime") { rowcolum= i;//得到Colum号 } } String RunTimeValues = ((Range)objExcelWorkSheet.Cells[rowsint,rowcolum]).Text.ToString(); objExcelWorkbook.Close(false, StrOutputPath, false); objExcelApp.Quit(); NAR(objExcelApp); NAR(objExcelWorkbook); NAR(objExcelWorkSheet); return RunTimeValues; }
public List<String> GetExcelValuesList(string StrTestData,string StrRuntime) { objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objExcelWorkBooks = objExcelApp.Workbooks; objExcelWorkbook = objExcelWorkBooks.Open(StrTestData, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); //Open the Sheet objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["Global"]; //Exce工作簿的Sheet objExcelWorkSheet.Select(Type.Missing); //取得总记录行数 (包括标题列) int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //得到行数 int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//得到列数 string temp1 = ((Range)objExcelWorkSheet.Cells[1, 1]).Text.ToString(); //遍历得到CompanyCode和TestAsset int rowcolum = 0; int RowID = 0; List<String> str = new List<String>(); List<String> strRowID = new List<String>(); for (int i = 1; i <= columnsint; i++) { string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, i]).Text.ToString(); if (strColumnsName == "RunTime") { rowcolum = i;//Get the columm no for (int j = 1; j <= rowsint;j++ ) { string strRowValue = ((Range)objExcelWorkSheet.Cells[j, i]).Text.ToString(); if(strRowValue == StrRuntime) { RowID = j; strRowID.Add(RowID.ToString()); } } } if (strColumnsName == "CompanyCode") { rowcolum = i;//得到Column号 string strRowIDNo = strRowID[0]; int RowNo = Convert.ToInt32(strRowIDNo); String temp = ((Range)objExcelWorkSheet.Cells[RowNo, i]).Text.ToString(); str.Add(((Range)objExcelWorkSheet.Cells[RowNo, i]).Text.ToString()); } if (strColumnsName == "TargetBoxName") { rowcolum = i;//得到行号 string strRowIDNo = strRowID[0]; int RowNo = Convert.ToInt32(strRowIDNo); String tempAssent = ((Range)objExcelWorkSheet.Cells[RowNo, i]).Text.ToString(); // string Asset = tempAssent.Substring(tempAssent.Length - 3); str.Add(tempAssent); } } //if (strColumnsName == "CompanyCode") //{ // rowcolum = i;//得到Column号 // String temp = ((Range)objExcelWorkSheet.Cells[2, i]).Text.ToString(); // str.Add(((Range)objExcelWorkSheet.Cells[2, i]).Text.ToString()); //} //if (strColumnsName == "TargetBoxName") //{ // rowcolum = i;//得到行号 // String tempAssent = ((Range)objExcelWorkSheet.Cells[2, i]).Text.ToString(); // string Asset = tempAssent.Substring(tempAssent.Length - 3); // str.Add(Asset); //} objExcelWorkbook.Close(false, StrTestData, false); objExcelApp.Quit(); NAR(objExcelApp); NAR(objExcelWorkbook); NAR(objExcelWorkSheet); return str; }
private static string OpenExcel(ExcelInfo excelInfo, bool isOpenSheet = true) { Console.WriteLine("Open File:【{0}】", excelInfo.FilePath); if (!File.Exists(excelInfo.FilePath)) { return $"文件【{excelInfo.FilePath}】不存在"; } _objExcel = new Excel.Application { Visible = false, DisplayAlerts = false, AlertBeforeOverwriting = false }; _objBooks = _objExcel.Workbooks; if (excelInfo.FilePath.Equals(String.Empty) || !File.Exists(excelInfo.FilePath)) { _objBook = _objBooks.Add(ObjOpt); } else { _objBook = _objBooks.Open(excelInfo.FilePath, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt); } if (isOpenSheet) { _objSheet = OpenSheet(excelInfo); if (_objSheet == null) { return "没有指定页签"; } } return ""; }
private void openWorkbook() { OpenFileDialog fileDialog = new OpenFileDialog(); string filepath; fileDialog.Filter = "Excel|*.xls;*.xlsx|All Files|*.*"; fileDialog.Multiselect = false; if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { filepath = fileDialog.FileName; m_xlWorkbooks = m_xlApp.Workbooks; m_xlWorkbook = m_xlWorkbooks.Open(filepath, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); m_xlSheets = m_xlWorkbook.Worksheets; m_xlWorksheets = new Dictionary<string, Excel.Worksheet>(); foreach (Excel.Worksheet sheet in m_xlSheets) { m_xlWorksheets.Add(sheet.Name, sheet); } comboBoxWorksheetSelector.DataSource = m_xlWorksheets.Keys.ToList<string>(); labelExcelWorkbookName.Text = "\"" + Path.GetFileName(filepath) + "\""; } }
public List<AccountExcel> readExcel(string sExcelPath) { var lReturn = new List<AccountExcel>(); string valueString = string.Empty; objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objBooks = (Excel.Workbooks)objExcelApp.Workbooks; //Open the workbook containing the address data. objBook = objBooks.Open(sExcelPath, 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); //Get a reference to the first sheet of the workbook. objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell); long lLastRow = rngLast.Row; long lLastCol = rngLast.Column; for (long rowCounter = 2; rowCounter <= lLastRow; rowCounter++) //FirstRow Has Headers - start at row 2 { if (ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value) != "") { var adAccount = new AccountExcel(); adAccount.sCustomerNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value); adAccount.sAccountName = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 40]).Value); adAccount.sAddressLine1 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 2]).Value); adAccount.sAddressLine2 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 5]).Value); adAccount.sAddressLine3 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 9]).Value); adAccount.sPostCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 15]).Value); adAccount.sTelephone = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 17]).Value); adAccount.sVatNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 18]).Value); adAccount.sCountryCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 21]).Value); adAccount.sEmail = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 37]).Value); adAccount.sWeb = "";// ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 38]).Value); adAccount.sKAM = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 31]).Value); adAccount.sRegion = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 24]).Value); lReturn.Add(adAccount); } } //Close the Excel Object objBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value); objBooks.Close(); objExcelApp.Quit(); Marshal.ReleaseComObject(objSheet); Marshal.ReleaseComObject(objSheets); Marshal.ReleaseComObject(objBooks); Marshal.ReleaseComObject(objBook); Marshal.ReleaseComObject(objExcelApp); objSheet = null; objSheets = null; objBooks = null; objBook = null; objExcelApp = null; GC.GetTotalMemory(false); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.GetTotalMemory(true); return (lReturn); }
public void Setup() { resourcePath = @"../../../../test.xlsx"; app = new Excel.Application(); var excelSheetPath = Path.GetFullPath(resourcePath); wbs = app.Workbooks; wb = wbs.Open(excelSheetPath); wss = wb.Worksheets; ws = (Excel.Worksheet)wss["Tasks"]; excelController = new ExcelController(resourcePath); }
private void PrepareRecord() { objRecordExcel = new Excel.Application(); objRecordBooks = objRecordExcel.Workbooks; if (File.Exists(txtStorage.Text)) objRecordBook = objRecordBooks.Open(txtStorage.Text); else objRecordBook = objRecordBooks.Add(true); objRecordSheets = objRecordBook.Sheets; objRecordSheet1 = objRecordSheets[1]; // 收件箱 objRecordSheet2 = objRecordSheets[1]; // 已发短信 bool bSheet1Exists = false, bSheet2Exists = false; foreach (Excel.Worksheet objSheet in objRecordSheets) { if (objSheet.Name == "收件箱") { objRecordSheet1 = objSheet; bSheet1Exists = true; } else if (objSheet.Name == "已发短信") { objRecordSheet2 = objSheet; bSheet2Exists = true; } } if (!bSheet1Exists) { objRecordSheet1 = objRecordSheets.Add(); objRecordSheet1.Name = "收件箱"; objRecordSheet1.Cells[1, 1] = "发件人"; objRecordSheet1.Cells[1, 2] = "短信内容"; objRecordSheet1.Cells[1, 3] = "发送时间"; } if (!bSheet2Exists) { objRecordSheet2 = objRecordSheets.Add(); objRecordSheet2.Name = "已发短信"; objRecordSheet2.Cells[1, 1] = "收件人"; objRecordSheet2.Cells[1, 2] = "短信内容"; objRecordSheet2.Cells[1, 3] = "发送时间"; objRecordSheet2.Cells[1, 4] = "结果"; objRecordSheet2.Cells[1, 5] = "余额"; } }
private void OpenExcel() { myExcelApp = new Excel.Application(); myExcelApp.Visible = false; myExcelWorkbooks = myExcelApp.Workbooks; String fileName = Application.StartupPath + "\\Data\\pttform.xls"; ; myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet; //HandleRef hwnd = new HandleRef(myExcelApp, (IntPtr)myExcelApp.Hwnd); //GetWindowThreadProcessId(hwnd, out pid); }