static void Openfile() { string mySheet = ConfigurationSettings.AppSettings["EXCEL_PATH"]; var excelApp = new Excel.Application(); excelApp.Visible = true; Excel.Workbooks books = excelApp.Workbooks; Excel.Workbook sheet = books.Open(mySheet); foreach (CommandBar bar in sheet.CommandBars) { if ("Team".Equals(bar.Name)) { foreach (CommandBarControl control in bar.Controls) { if ("IDC_REFRESH".Equals(control.Tag.ToUpper())) { control.Execute(); } } } } sheet.RefreshAll(); sheet.Save(); excelApp.Quit(); }
public static void RefreshExcel(string execelLocation) { object missingValue = System.Reflection.Missing.Value; Excel.Application excel = new Excel.Application(); excel.DisplayAlerts = false; Excel.Workbook theWorkbook = excel.Workbooks.Open( execelLocation, missingValue, false, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue); lock (theWorkbook) { theWorkbook.RefreshAll(); } System.Threading.Thread.Sleep(5 * 1000); // Make sure correct save theWorkbook.Save(); theWorkbook.Close(); excel.Quit(); GC.Collect(); GC.WaitForPendingFinalizers(); }
public void DataSetToExcel(DataSet dataSet) { Excel.Workbook activeWorkBook = Globals.ThisAddIn.Application.ActiveWorkbook; Excel.Sheets sheets = activeWorkBook.Sheets; sheets.Add(); activeWorkBook.XmlMaps.Add(dataSet.GetXmlSchema(), dataSet.DataSetName); activeWorkBook.XmlImportXml(dataSet.GetXml(), out _, true, "$A1"); activeWorkBook.RefreshAll(); }
public void Close() { Workbook.RefreshAll(); ExcelApp.Calculate(); Workbook.Save(); Workbook.Close(); Marshal.ReleaseComObject(Worksheet); Marshal.ReleaseComObject(Workbook); ExcelApp.Quit(); }
// function to initialize excel doc log private void setExcelApp() { xlApp = null; try { xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"); } catch (System.Runtime.InteropServices.COMException) { xlApp = new Excel.Application(); xlApp.Visible = true; } // if the claims file is already open then it will be pulled into wkbk, otherwise it will be opened wkbk = null; foreach (Excel.Workbook WB in xlApp.Workbooks) { // all this stuff is to isolate the file name of the currently running workbooks. string strWB = WB.FullName.ToString(); int slashPos = strWB.LastIndexOf("\\"); strWB = strWB.Substring(slashPos + 1); if (strWB == docLogNameOnly) { wkbk = WB; //MessageBox.Show("Test success"); } } if (wkbk == null) { // in case file is not already open wkbk = xlApp.Workbooks.Open(docLogXL); // attempting to refresh the excel sheet try { wkbk.RefreshAll(); wkbk.Save(); } catch (System.Runtime.InteropServices.COMException) { MessageBox.Show("Program was unable to refresh the excel sheet, please do so manually"); } } // activating the workbook for use // below code is extended from wkbk.activate due to ambiguity ((Excel._Workbook)wkbk).Activate(); // assigning the worksheet for use sheetOne = wkbk.Worksheets[1]; // counting number of rows for use in another part of program countEntries = sheetOne.UsedRange.Rows.Count; // for testing purpose: MessageBox.Show(countClaims.ToString()); }
private void RefreshSheets(Excel.Application ExcelApp, Excel.Workbook WB) { //foreach (Excel.Worksheet WS in WB.Worksheets) //{ // foreach (Excel.QueryTable query in WS.QueryTables) // { // query.BackgroundQuery = false; // } WB.RefreshAll(); //ExcelApp.Application.CalculateUntilAsyncQueriesDone(); //} //WB.Save(); }
protected void Page_Load(object sender, EventArgs e) { Excel.Application excelApp = new Excel.Application(); excelApp.Visible = true; string workbookPath = "C:/Users/parevi01/Documents/sample1.xlsx"; Excel.Workbook sampleWorkBook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, true, 0, true, false, false); sampleWorkBook.RefreshAll(); Excel.Sheets sheets = sampleWorkBook.Worksheets; Excel.Worksheet mySheet = (Excel.Worksheet)sheets.get_Item("sheet1"); Excel.Range myCell = (Excel.Range)mySheet.get_Range("A1", "A1"); myCell.Value2 = "edited value"; }
// This is the method to run when the timer is raised. private void TimerEventProcessor(Object myObject, EventArgs myEventArgs) { myTimer.Stop(); // Displays a message box asking whether to continue running the timer. if (alarmCounter < 100000000) { bool tryAgain = false; int counter = 0; // Restarts the timer and increments the counter. while (!tryAgain && counter < 15) { try { tryAgain = false; xlWorkbook.RefreshAll(); } catch (Exception e) { Thread.Sleep(1000); tryAgain = true; counter++; } } //We add the refreshed data loaded_data.Add(new Data(Double.Parse((String)(xlRange.Cells[17, 2] as Excel.Range).Value.ToString()), DateTime.Now)); int count = loaded_data.Count; double data = loaded_data[count - 1].close; System.Diagnostics.Debug.Write(data.ToString()); //We add the new data to the graph graph_live.Graphique_refresh(loaded_data, alarmCounter); //We increment the counter alarmCounter += 1; myTimer.Enabled = true; } else { // Stops the timer. exitFlag = true; } }
public string RefreshAll(string FilePath) { string result = ""; try { result = OpenExcel(FilePath, ""); if (result == "") { Wb.RefreshAll(); result = ""; } } catch (Exception e) { return("Exception caught - " + e.Message); } return(result); }
static void Main(string[] args) { //http://commandline.codeplex.com/ var options = new Options(); if (CommandLine.Parser.Default.ParseArguments(args, options)) { // Values are available here if (options.Verbose) { Console.WriteLine("Filename: {0}", options.InputFile); } //Items? if (options.Verbose) { Console.WriteLine("Items Count: {0}", options.Items.Count.ToString()); options.Items.ToList().ForEach(i => Console.Write("{0}\t", i)); } } else { Console.WriteLine("required options not specified ... quiting."); return; } string txtLocation = Path.GetFullPath(options.InputFile); if (options.Verbose) { Console.WriteLine("Input File Full Path: {0}", txtLocation); } if (!File.Exists(txtLocation)) { Console.WriteLine("Input File does not exist: {0}", txtLocation); return; } object _missingValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook theWorkbook = excel.Workbooks.Open(txtLocation, _missingValue, false, _missingValue, _missingValue, _missingValue, true, _missingValue, _missingValue, true, _missingValue, _missingValue, _missingValue); if (options.Visible) { excel.Visible = true; } if (options.All) { theWorkbook.RefreshAll(); } else { if (options.Querytables) { refreshQueryTables(theWorkbook); } if (options.Connections) { refreshConnection(theWorkbook); } if (options.Pivottables) { refreshPivots(theWorkbook); } } // // To test: // XLRefreshC.exe -d -f ..\..\..\Book1.xlsm -m sheet1.showMessage // if (options.MacrosToRun != null) { if (options.Verbose) { Console.WriteLine("Macro Count:{0}", options.MacrosToRun.Count().ToString()); } foreach (string macro in options.MacrosToRun) { if (options.Verbose) { Console.WriteLine("Macro :{0}", macro); } excel.Run(macro); } } else { if (options.Verbose) { Console.WriteLine("no macro."); } } if (options.Verbose) { Console.WriteLine("shut it down!"); } excel.Calculate(); if (options.Verbose) { Console.WriteLine("Excel calculated"); } theWorkbook.Save(); if (options.Verbose) { Console.WriteLine("Workbook saved"); } theWorkbook.Close(true); if (options.Verbose) { Console.WriteLine("Workbook closed"); } excel.Quit(); if (options.Verbose) { Console.WriteLine("Excel Quit"); } //Console.WriteLine("Press any key to close..."); //Console.ReadLine(); return; }
public static DataTable ReadExcelUsingInterop(string exlDataSource, string sheetname) { ExlInterop.Application exlApp = new ExlInterop.Application(); ExlInterop.Workbook exlWb = null; ExlInterop.Worksheet exlSheet; int rCnt = 0; int cCnt = 0; object cellValue = null; string colValue = string.Empty; //dt holds both the empty and data rows of excel sheet DataTable dt = new DataTable(); try { exlWb = exlApp.Workbooks.Open(exlDataSource); int numSheets = exlWb.Sheets.Count; for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++) { exlSheet = (ExlInterop.Worksheet)exlWb.Sheets[sheetNum]; string strWorksheetName = exlSheet.Name; exlWb.RefreshAll(); if (strWorksheetName.Equals(sheetname)) { ExlInterop.Range range = exlSheet.UsedRange; // ExlInterop.Range range = exlSheet.UsedRange.SpecialCells( // ExlInterop.XlCellType.xlCellTypeVisible, // Type.Missing); for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++) { DataRow drow = dt.NewRow(); for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++) { cellValue = (range.Cells[rCnt, cCnt] as Excel.Range).Value2; colValue = cellValue == null ? string.Empty : Convert.ToString(cellValue); //Adding Header Row to DataTable if (rCnt == 1) { dt.Columns.Add(colValue); } else { drow[cCnt - 1] = colValue; } } if (rCnt > 1) { dt.Rows.Add(drow); dt.AcceptChanges(); } } } } } catch (Exception ex) { throw ex; } finally { exlWb.Close(); exlApp.Quit(); releaseProcessObject(exlWb); releaseProcessObject(exlApp); } return(dt); }
protected void btnSubmit_Click(object sender, EventArgs e) { String typeValue; String modelValue; String previousDate; String currentDate; //Assigning default values for the variables previousDate = (hifPrevDate.Value == "") ? "20130210" : hifPrevDate.Value; currentDate = (hifCurrentDate.Value == "") ? "20130522" : hifCurrentDate.Value; modelValue = (hifModel.Value == "") ? "Risk View" : hifModel.Value; //Appending type and model values to match the stored procedure requirements String tableType = ""; tableType = "rvscores"; ConnectionStringSettings cs; cs = ConfigurationManager.ConnectionStrings["DQConnectionString"]; String connString = cs.ConnectionString; SqlConnection dbConnection = new SqlConnection(connString); String sqlScoresCompare; sqlScoresCompare = "SCORES_COMPARE_SCRIPT"; SqlCommand dbCommand = new SqlCommand(sqlScoresCompare, dbConnection); dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.Parameters.Add(new SqlParameter("@previous", previousDate)); dbCommand.Parameters.Add(new SqlParameter("@current", currentDate)); dbCommand.Parameters.Add(new SqlParameter("@model", tableType)); dbConnection.Open(); try { dbCommand.ExecuteNonQuery(); } catch (SqlException) { Console.WriteLine("SQL Exception occured in submit button click event"); } finally { dbConnection.Close(); //updating the excel document Excel.Application excelApp = new Excel.Application(); excelApp.Visible = false; string workbookPath = "C:\\Users\\parevi01\\Documents\\LexisNexis\\Compare_Reports_v03.xlsx"; string physicalPath = System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath; //workbookPath = physicalPath + "Compare_Reports_v03.xlsx"; Excel.Workbook sampleWorkBook = excelApp.Workbooks.Open(workbookPath); sampleWorkBook.RefreshAll(); System.Threading.Thread.Sleep(2000); sampleWorkBook.Save(); System.Threading.Thread.Sleep(2000); excelApp.Workbooks.Close(); excelApp.Quit(); excelApp = new Excel.Application(); excelApp.Visible = true; sampleWorkBook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, true, 0, true, false, false); } }
// Обновить все данные, связанные с котировками private void ribbon_btnProfitClicked() { ribbon_btnRefreshAllClicked(); if ((this.Application != null) && (this.Application.ActiveWorkbook != null)) { Excel.Worksheet ProfitSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelProfitName, sh => { }); Excel.Worksheet TinkoffSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelTinkoffName, sh => { }); Excel.Worksheet HistorySheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelHistoryName, sh => { }); Excel.Worksheet BrokerSheet = null; if (HistorySheet == null) { // лист не найден - добавляем HistorySheet = (Excel.Worksheet) this.Application.ActiveWorkbook.Sheets.Add(); HistorySheet.Name = ExcelHistoryName; HistorySheet.Activate(); // заголовок int _count = 1; HistorySheet.Cells[_count, 1] = "Ticker"; HistorySheet.Cells[_count, 2] = "Date"; HistorySheet.Cells[_count, 3] = "IsCurrency"; HistorySheet.Cells[_count, 4] = "Count"; HistorySheet.Cells[_count, 5] = "Valute"; HistorySheet.Cells[_count, 6] = "Price"; HistorySheet.Cells[_count, 7] = "Summa"; HistorySheet.Cells[_count, 8] = "Dividend"; HistorySheet.Cells[_count, 9] = "MarketPrice"; HistorySheet.Cells[_count, 10] = "MarketSumma"; HistorySheet.Cells[_count, 11] = "Curs"; } if (ProfitSheet != null) { Dictionary <string, ProfitTicker> Profits = new Dictionary <string, ProfitTicker>(); int max_rows; int max_columns; string ticker; this.Application.StatusBar = $"Собираем портфели с листов данного excel-файла"; // портфель Тинькофф if (TinkoffSheet != null) { max_rows = TinkoffSheet.UsedRange.Rows.Count; for (int i = 2; i <= max_rows; i++) { ticker = TinkoffSheet.Cells[i, 2].Text; if ((ticker != null) && (ticker != "")) { double value; ProfitTicker item = null; if (Profits.ContainsKey(ticker)) { item = Profits[ticker]; } else { item = new ProfitTicker(); item.ticker = ticker; Profits.Add(ticker, item); } item.name = TinkoffSheet.Cells[i, 3].Text; item.InstrumentTypeName = TinkoffSheet.Cells[i, 4].Text; if (TinkoffSheet.Cells[i, 5].Value is double) { value = TinkoffSheet.Cells[i, 5].Value; } else { double.TryParse(TinkoffSheet.Cells[i, 5].Text, out value); } item.Count += value; item.currency = TinkoffSheet.Cells[i, 6].Text; if (TinkoffSheet.Cells[i, 8].Value is double) { value = TinkoffSheet.Cells[i, 8].Value; } else { double.TryParse(TinkoffSheet.Cells[i, 8].Text, out value); } item.Summa += value; if (TinkoffSheet.Cells[i, 14].Value is double) { value = TinkoffSheet.Cells[i, 14].Value; } else { double.TryParse(TinkoffSheet.Cells[i, 14].Text, out value); } item.MarketSumma += value; } } } foreach (var broker in BrokersName) { // находим лист BrokerSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, broker, sh => { }); // портфель брокера if (BrokerSheet != null) { max_rows = BrokerSheet.UsedRange.Rows.Count; for (int i = 2; i <= max_rows; i++) { ticker = BrokerSheet.Cells[i, 2].Text; if ((ticker != null) && (ticker != "")) { double value; ProfitTicker item = null; if (Profits.ContainsKey(ticker)) { item = Profits[ticker]; } else { item = new ProfitTicker(); item.ticker = ticker; Profits.Add(ticker, item); } item.name = BrokerSheet.Cells[i, 3].Text; item.InstrumentTypeName = BrokerSheet.Cells[i, 4].Text; if (BrokerSheet.Cells[i, 7].Value is double) { value = BrokerSheet.Cells[i, 7].Value; } else { double.TryParse(BrokerSheet.Cells[i, 7].Text, out value); } item.Count += value; if (BrokerSheet.Cells[i, 8].Value is double) { value = BrokerSheet.Cells[i, 8].Value; } else { double.TryParse(BrokerSheet.Cells[i, 8].Text, out value); } item.Nominal = value; item.currency = BrokerSheet.Cells[i, 9].Text; if (BrokerSheet.Cells[i, 11].Value is double) { value = BrokerSheet.Cells[i, 11].Value; } else { double.TryParse(BrokerSheet.Cells[i, 11].Text, out value); } item.Summa += value; if (BrokerSheet.Cells[i, 17].Value is double) { value = BrokerSheet.Cells[i, 17].Value; } else { double.TryParse(BrokerSheet.Cells[i, 17].Text, out value); } item.MarketSumma += value; } } } } ProfitSheet.Activate(); max_rows = ProfitSheet.UsedRange.Rows.Count; int count_rows = 1; this.Application.StatusBar = $"Заполняем лист {ExcelProfitName}"; // перебираем строки в excel: если есть в портфелях - заполняем, если нет - обнуляем for (int i = 2; i <= max_rows; i++) { ticker = ProfitSheet.Cells[i, 1].Text; if ((ticker == null) || (ticker == "")) { break; } count_rows++; if (Profits.ContainsKey(ticker)) { Profits[ticker].IsFound = true; FillProfit(ProfitSheet, count_rows, ticker, Profits[ticker], HistorySheet); } else { FillProfit(ProfitSheet, count_rows, ticker, null, HistorySheet); } this.Application.StatusBar = $"Заполняем лист {ExcelProfitName} - {ticker}"; } // все, что не найдено - добавляем в конец таблицы foreach (var item in Profits.Values) { if ((!item.IsFound) && (item.Count != 0)) //-V3024 { ticker = item.ticker; count_rows++; FillProfit(ProfitSheet, count_rows, ticker, item, HistorySheet); if (this.Application != null) { this.Application.StatusBar = $"Заполняем лист {ExcelProfitName} - {ticker}"; } } } // итоги count_rows++; ProfitSheet.Cells[count_rows, 5].FormulaR1C1 = $"=SUBTOTAL(9,R[{2- count_rows}]C:R[-1]C)"; ProfitSheet.Cells[count_rows, 17].FormulaR1C1 = $"=SUBTOTAL(9,R[{2 - count_rows}]C:R[-1]C)"; max_columns = ProfitSheet.UsedRange.Columns.Count; if (max_rows > count_rows) { ProfitSheet.Range[ProfitSheet.Cells[count_rows + 1, 1], ProfitSheet.Cells[max_rows, max_columns]].ClearContents(); } max_rows = count_rows; // оформляем MSExcel.RangeBold(ProfitSheet.Range[ProfitSheet.Cells[1, 1], ProfitSheet.Cells[max_rows, max_columns]], false); MSExcel.RangeBold(ProfitSheet.Range[ProfitSheet.Cells[1, 1], ProfitSheet.Cells[1, max_columns]], true); MSExcel.RangeBold(ProfitSheet.Range[ProfitSheet.Cells[max_rows, 1], ProfitSheet.Cells[max_rows, max_columns]], true); MSExcel.RangeWrapText(ProfitSheet.Range[ProfitSheet.Cells[1, 1], ProfitSheet.Cells[1, max_columns]]); MSExcel.RangeBorder(ProfitSheet.Range[ProfitSheet.Cells[1, 1], ProfitSheet.Cells[max_rows, max_columns]]); MSExcel.RangeAutoFilter(ProfitSheet.Cells); MSExcel.RangeVerticalAlignment(ProfitSheet.Cells, Excel.XlVAlign.xlVAlignCenter); // обновляем семейный бюджет string FamilyBudgetFile = MSExcel.GetNamedRangeValue <string>(this.Application.ActiveWorkbook, "FamilyBudgetFile"); string FamilyBudgetInvestField = MSExcel.GetNamedRangeValue <string>(this.Application.ActiveWorkbook, "FamilyBudgetInvest"); string FamilyBudgetToInvestField = MSExcel.GetNamedRangeValue <string>(this.Application.ActiveWorkbook, "FamilyBudgetToInvest"); if ((FamilyBudgetFile != "") && (FamilyBudgetInvestField != "") && (FamilyBudgetToInvestField != "") && File.Exists(FamilyBudgetFile)) { Excel.Workbook curWorkbook = this.Application.ActiveWorkbook; // открываем файл семейного бюджета Excel.Workbook familyWorkbook = this.Application.Workbooks.Open(FamilyBudgetFile); if (familyWorkbook != null) { double value; // считываем затраты на инвестиции value = MSExcel.GetNamedRangeValue <double>(familyWorkbook, FamilyBudgetToInvestField); MSExcel.SetNamedRangeValue <double>(curWorkbook, FamilyBudgetToInvestField, value); if (System.Windows.Forms.MessageBox.Show("Обновить результат инвестиций в семейном бюджете ?", "Внимание!", System.Windows.Forms.MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes) { // заполняем результат инвестиций value = MSExcel.GetNamedRangeValue <double>(curWorkbook, FamilyBudgetInvestField); MSExcel.SetNamedRangeValue <double>(familyWorkbook, FamilyBudgetInvestField, value); familyWorkbook.RefreshAll(); } familyWorkbook.Save(); familyWorkbook.Close(); } if (curWorkbook != null) { curWorkbook.Activate(); } } } this.Application.ActiveWorkbook.RefreshAll(); } if (this.Application != null) { this.Application.StatusBar = false; } }