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;
}
}
