public virtual void Print()
{
try
{
//Insert the DataGridView into the excel spreadsheet
TableToExcelSheet(Data, ExcelSheet, ExcelProperties.StartRow, ExcelProperties.StartColumn);
//if visible , then exit so user can see it, otherwise save and exit
if (ExcelProperties.PrintDirectely)
{
//bool okPrint = objExcel.Dialogs[XlBuiltInDialog.xlDialogPrint].Show();
// if (!okPrint)
//{
// objWorkbook.Close(false);
// return;
//}
ExcelSheet.PrintOut();
//excelWorkbook.Close(false);
ExcelApp.DisplayAlerts = false;
ExcelApp.Quit();
Dispose();
return;
}
ExcelApp.Visible = true;
Dispose();
}
catch (Exception)
{
ForceExcleToQuit(ExcelApp);
Dispose();
throw;
}
}
/// <summary>
/// Close Excel file and release all Excel objects
/// </summary>
public void CloseFile()
{
try
{
if (Worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(Worksheet);
}
Sheet?.Close(false);
Books?.Close();
if (Sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(Sheet);
}
if (Books != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(Books);
Books = null;
}
}
finally
{
ExcelApp?.Quit();
if (ExcelApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
ExcelApp = null;
}
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
/// <summary>
/// Read FM file
/// </summary>
/// <param name="fmFilePath">The full path of the FM file.</param>
/// <returns>The main Ric list.</returns>
private List <string> GetMainRic(string fmFilePath)
{
List <string> ricList = new List <string>();
using (ExcelApp app = new ExcelApp(false, false))
{
if (!File.Exists(fmFilePath))
{
Logger.Log(string.Format("Can't find the FM file in the path {0} .", fmFilePath));
return(ricList);
}
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, fmFilePath);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
string ric = null;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 5, 3, ExcelLineWriter.Direction.Down))
{
ric = reader.ReadLineCellText();
while (!string.IsNullOrEmpty(ric))
{
ricList.Add(ric);
ric = reader.ReadLineCellText();
}
}
workbook.Close(false, workbook.FullName, false);
}
return(ricList);
}
/// <summary>
/// 释放资源
/// </summary>
public void Quit()
{
if (WorkBook != null)
{
WorkBook.Close(null, null, null);
}
if (ExcelApp != null)
{
ExcelApp.Workbooks.Close();
ExcelApp.Quit();
}
if (Range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(Range);
Range = null;
}
if (WorkSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkSheet);
WorkSheet = null;
}
if (WorkBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkBook);
WorkBook = null;
}
if (ExcelApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
ExcelApp = null;
}
GC.Collect();
}
static void Main(string[] args)
{
string fileFullePathName = getFileName(args);
string filename = Utility.GetFileNameFromPath(fileFullePathName);
// Excel.Application の新しいインスタンスを生成する
ExcelApp.GetInstance();
var xlApp = ExcelApp.GetInstance().sExcelApp;
var xlBooks = ExcelApp.GetInstance().sWorkBook;
try
{
Workbook wb = xlBooks.Open(fileFullePathName);
// マクロを実行する
// 標準モジュール内のTestメソッドに "Hello World" を引数で渡し実行
//xlApp.Run("work.xlsm!Test", "Hello World");
// Sheet1内のSheetTestメソッドを実行(引数なし)
xlApp.Run(filename + "test");
wb.Save();
}
finally
{
ExcelApp.Release();
}
}
/// <summary>
/// Implements the OnConnection method of the IDTExtensibility2 interface.
/// Receives notification that the Add-in is being loaded.
/// </summary>
/// <param term='application'>
/// Root object of the host application.
/// </param>
/// <param term='connectMode'>
/// Describes how the Add-in is being loaded.
/// </param>
/// <param term='addInInst'>
/// Object representing this Add-in.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnConnection(object application, Extensibility.ext_ConnectMode connectMode, object addInInst, ref System.Array custom)
{
string strAppName = (string)application.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, application, null);
if (strAppName == "Microsoft Word")
{
appThis = new WordApp(application);
}
else if (strAppName == "Microsoft Excel")
{
appThis = new ExcelApp(application);
}
else if (strAppName == "Microsoft Publisher")
{
appThis = new PubApp(application);
}
else if (strAppName == "Microsoft Access")
{
appThis = new AccessApp(application);
}
else
{
string strError = String.Format("The '{0}' application is not supported!", strAppName);
System.Windows.Forms.MessageBox.Show(strError, OfficeApp.cstrCaption);
throw new Exception(strError);
}
if (connectMode != ext_ConnectMode.ext_cm_Startup)
{
OnStartupComplete(ref custom);
}
}
public override void Print(IOfficeFilePropties properties)
{
if (properties == null)
{
throw new ArgumentNullException(nameof(properties));
}
var p = properties as ExcelFileProperties;
if (p == null)
{
throw new InvalidOperationException("You have to supply valid Excel File Properties");
}
try
{
CurrentExcelSheet = GetExcelSheet(p.Path);
TableToExcelSheet(p.Source, CurrentExcelSheet, p.StartRowIndex, p.StartcolumnIndex);
if (p.PrintDirectly)
{
CurrentExcelSheet.PrintOut();
ExcelApp.DisplayAlerts = false;
ExcelApp.Quit();
ReleaseResources(ExcelApp, ExcelWorkbooks, ExcelWorkbook, CurrentExcelSheet);
return;
}
ExcelApp.Visible = true;
//ReleaseResources(excelApp, excelWorkbooks, excelWorkbook, excelSheet);
}
catch
{
ForceExcleToQuit(ExcelApp);
ReleaseResources(ExcelApp, ExcelWorkbooks, ExcelWorkbook, CurrentExcelSheet);
throw;
}
}
private void generateFile(List <ChinaIndex> indexList, string fileName)
{
if (indexList.Count == 0)
{
Logger.Log("No item in the index list.");
return;
}
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, string.Format("{0}\\{1}", configObj.TargetFileDir, fileName));
int sheetNum = (indexList.Count + configObj.ChainNumPerSheet - 1) / configObj.ChainNumPerSheet;
int startPos = 0;
if (sheetNum > workbook.Worksheets.Count)
{
workbook.Worksheets.Add(Missing.Value, Missing.Value, sheetNum - workbook.Worksheets.Count, Missing.Value);
}
for (int i = 0; i < sheetNum; i++)
{
var worksheet = workbook.Worksheets[i + 1] as Worksheet;
startPos = i * configObj.ChainNumPerSheet;
int endPos = indexList.Count < (startPos + configObj.ChainNumPerSheet) ? indexList.Count : (startPos + configObj.ChainNumPerSheet);
WriterWorksheet(worksheet, indexList, startPos, endPos);
}
TaskResultList.Add(new TaskResultEntry(fileName, "", workbook.FullName));
workbook.Save();
workbook.Close(true, workbook.FullName, true);
}
}
public void Dispose()
{
ExcelDocument?.Close(false, "", false);
ExcelApp?.Quit();
if (ExcelApp != null)
{
while (Marshal.ReleaseComObject(ExcelApp) != 0)
{
}
}
if (ExcelDocument != null)
{
while (Marshal.ReleaseComObject(ExcelDocument) != 0)
{
}
}
ExcelApp = null;
ExcelDocument = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
public string GenerarExcel(List <List <String> > datos)
{
string file = StringHelper.HashUnico("excel_", ".xlsx");
string nombreArchivo = HostingEnvironment.ApplicationPhysicalPath + "Archivos\\Temporal\\" + file;
var excel = new ExcelApp();
excel.ActiveSheetSetColumnName("A1", "Empresa");
excel.ActiveSheetSetColumnName("B1", "Cuenta");
excel.ActiveSheetSetColumnName("C1", "Rubro");
excel.ActiveSheetSetColumnName("D1", "Grupo");
excel.ActiveSheetSetColumnName("E1", "Fecha");
excel.ActiveSheetSetColumnName("F1", "Dias Plazo");
excel.ActiveSheetSetColumnName("G1", "Estado");
excel.ActiveSheetSetColumnName("H1", "Analista");
excel.ActiveSheetSetColumnName("I1", "Validador");
excel.ActiveSheetSetColumnName("J1", "Certificador");
excel.ActiveSheetSetColumnName("K1", "Avance");
int i = 1;
foreach (var fila in datos)
{
i++;
excel.ActiveSheetLineWriter("A", i, fila);
}
excel.Save(nombreArchivo);
return(nombreArchivo);
}
public void StartOptionRicGeneratorJob()
{
List <StockOption> stockOptionList = new List <StockOption>();
stockOptionList = GetStockOptionList();
using (ExcelApp app = new ExcelApp(false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, Path.Combine(configObj.RIC_GENERATE_FILE_DIR, NewFileName()));
var worksheet = workbook.Worksheets[1] as Worksheet;
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right))
{
foreach (StockOption stockOption in stockOptionList)
{
for (int i = 0; i < stockOption.PutRicList.Count; i++)
{
writer.WriteLine(stockOption.CallRicList[i]);
writer.WriteLine("D" + stockOption.CallRicList[i]);
writer.WriteLine("/" + stockOption.CallRicList[i]);
writer.PlaceNext(writer.Row + 1, 1);
writer.WriteLine(stockOption.PutRicList[i]);
writer.WriteLine("D" + stockOption.PutRicList[i]);
writer.WriteLine("/" + stockOption.PutRicList[i]);
writer.PlaceNext(writer.Row + 1, 1);
}
}
}
workbook.Save();
}
}
public void GenerateISINFile(List <RicISINInfo> ricInfoList)
{
using (ExcelApp app = new ExcelApp(false, false))
{
string targetFilePath = NewTargetFilePath(Path.Combine(Path.GetDirectoryName(configObj.SourceFilePath), configObj.TargetIsinFileName));
if (File.Exists(targetFilePath))
{
File.Delete(targetFilePath);
}
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, targetFilePath);
var worksheet = (Worksheet)workbook.Worksheets[1];
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right))
{
writer.WriteLine("Ric");
writer.WriteLine("Official Code");
writer.WriteLine("ISIN");
writer.WriteLine("Name");
writer.WriteLine("Type");
writer.PlaceNext(writer.Row + 1, 1);
foreach (RicISINInfo ricISINInfo in ricInfoList)
{
writer.WriteLine(ricISINInfo.ric);
ExcelUtil.GetRange(writer.Row, writer.Col, worksheet).NumberFormat = "@";
writer.WriteLine(ricISINInfo.OfficialCode);
writer.WriteLine(ricISINInfo.ISIN);
writer.WriteLine(ricISINInfo.Name);
writer.WriteLine(ricISINInfo.type);
writer.PlaceNext(writer.Row + 1, 1);
}
}
workbook.Save();
TaskResultList.Add(new TaskResultEntry("Newly Generated ISIN File", "The file contains all the newly generated ISIN.", targetFilePath));
}
}
public static void CloseExcelApp()
{
CloseExcelWorkbook();
if (ExcelApp == null)
{
return;
}
/*var LocalByNameApp = Process.GetProcessesByName("EXCEL");
* if (LocalByNameApp.Length > 0)
* {
* foreach (var App in LocalByNameApp)
* {
* if (!App.HasExited)
* {
* App.Kill();
* }
* }
* }*/
GetWindowThreadProcessId(new IntPtr(ExcelApp.Hwnd), out int ProcessId);
Process.GetProcessById(ProcessId).Kill();
ExcelApp.Workbooks.Close();
Marshal.ReleaseComObject(ExcelApp);
ExcelApp.Quit();
ExcelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
private List <List <string> > GetDataFromFile(string path, int position)
{
try
{
if (!File.Exists(path))
{
Logger.Log(string.Format("file :{0} is not esist.", path), Logger.LogType.Error);
return(null);
}
ExcelApp app = new ExcelApp(false, false);
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.InputFilePath);
if (position > workbook.Sheets.Count)
{
Logger.Log(string.Format("Workbook sheet count:{0}, error file.", workbook.Sheets.Count.ToString()), Logger.LogType.Error);
return(null);
}
return(WorkbookExtension.ToList(workbook, position));
}
catch (Exception ex)
{
string msg = string.Format("\r\n ClassName: {0}\r\n MethodName: {1}\r\n Message: {2}",
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.ToString(),
System.Reflection.MethodBase.GetCurrentMethod().Name,
ex.Message);
Logger.Log(msg, Logger.LogType.Error);
return(null);
}
}
public void UpdateTemplateV2File(Range A2Range, int lastRowContainInfo)
{
A2Range.Copy(Missing.Value);
string TemplateV2FilePath = BackupFiles(configObj.RIC_COWS_TEMPLATE_V2_FILE_PATH);
using (ExcelApp appTemplateV2 = new ExcelApp(false, false))
{
var workbookTemplateV2 = ExcelUtil.CreateOrOpenExcelFile(appTemplateV2, TemplateV2FilePath);
var worksheetTemplateV2 = ExcelUtil.GetWorksheet(configObj.TEMPLATE_V2_WORKSHEET_NAME, workbookTemplateV2);
if (worksheetTemplateV2 == null)
{
logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.TEMPLATE_V2_WORKSHEET_NAME, workbookTemplateV2.Name));
}
Range C3Range = ExcelUtil.GetRange(3, 3, lastRowContainInfo, 18, worksheetTemplateV2);
C3Range.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, Missing.Value, Missing.Value);
ExcelUtil.GetRange(1, 1, worksheetTemplateV2).Copy(Missing.Value);
SetCreateValue(worksheetTemplateV2, lastRowContainInfo);
//Run Macros
appTemplateV2.ExcelAppInstance.GetType().InvokeMember("Run",
BindingFlags.Default | BindingFlags.InvokeMethod,
null,
appTemplateV2.ExcelAppInstance,
new object[] { "FormatData" });
workbookTemplateV2.SaveCopyAs(Path.Combine(Path.GetDirectoryName(workbookTemplateV2.FullName), Path.GetFileName(configObj.RIC_COWS_TEMPLATE_V2_FILE_PATH)));
workbookTemplateV2.Close(false, workbookTemplateV2.FullName, false);
File.Delete(TemplateV2FilePath);
}
}
/// <summary>
///
/// </summary>
/// <param name="results"></param>
private void WriteResultsInCsv(IEnumerable <string> results)
{
using (ExcelApp app = new ExcelApp(false, false))
{
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.WorkingFolder + "result.csv");
try
{
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
ExcelLineWriter lineWriter = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right);
lineWriter.PlaceNext(1, 1);
lineWriter.WriteLine("RIC");
lineWriter.WriteLine("RETIRE DATE");
int line = 2;
foreach (string entry in results)
{
lineWriter.PlaceNext(line, 1);
lineWriter.WriteLine(entry);
lineWriter.WriteLine(DateTime.Now.ToString("dd-MMM-yyyy"));
line++;
}
}
catch (Exception ex)
{
LogMessage("Error while creating CSV", Logger.LogType.Error);
throw new Exception("Error when creating result CSV: " + ex.Message);
}
finally
{
AddResult("TW IO Drop results", configObj.WorkingFolder + "result.csv", "file");
workbook.SaveAs(configObj.WorkingFolder + "result.csv", XlFileFormat.xlCSV);
workbook.Close();
}
}
}
private void updateSZSEIndexListWithRic()
{
foreach (ChinaIndex index in szseIndexList)
{
//http://www.szse.cn/szseWeb/FrontController.szse?ACTIONID=8&CATALOGID=1747&TABKEY=tab1&ENCODE=1&ZSDM=399328
string url = string.Format("{0}/szseWeb/FrontController.szse?ACTIONID=8&CATALOGID=1747&TABKEY=tab1&ENCODE=1&ZSDM={1}", configObj.SzseBaseUri, index.Chain);
//string pageSource = WebClientUtil.GetPageSource(null, url, 180000, "", Encoding.GetEncoding("gb2312"));
string ricFilePath = targetDownloadFileDir() + "\\SZSE\\";
ricFilePath += string.Format("{0}.xls", index.Chain);
WebClientUtil.DownloadFile(url, 180000, ricFilePath);
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, ricFilePath);
var worksheet = workbook.Worksheets[1] as Worksheet;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
for (int i = 2; i <= lastUsedRow; i++)
{
Range aRange = ExcelUtil.GetRange(i, 1, worksheet);
if (aRange != null && !string.IsNullOrEmpty(aRange.Text.ToString().Trim()))
{
index.RicList.Add(generateRic(aRange.Text.ToString().Trim()));
}
}
}
}
}
public void Excel_Context_Menu_Tests()
{
IExcelApp app = new ExcelApp();
app.CreateNewInstance();
IExcelWorkbook workbook = app.CreateAndActivateNewWorkbook();
IExcelSheet sheet = workbook.CreateSheet("Test");
MenuItem contextMenu = new MenuItem {
ItemType = MenuItemType.ContextMenu
};
contextMenu.Children.Add(new MenuItem {
Caption = "First Command", CommandId = "FC01"
});
contextMenu.Children.Add(new MenuItem {
ItemType = MenuItemType.Separator
});
contextMenu.Children.Add(new MenuItem {
Caption = "Second Command", CommandId = "FC01"
});
sheet.SetContextMenu(2, 2, contextMenu, "Application.Id");
}
private void downloadAndParseIndexFile(string url)
{
string szseIndexFilePath = targetDownloadFileDir();
szseIndexFilePath += "\\SZSE";
if (!Directory.Exists(szseIndexFilePath))
{
Directory.CreateDirectory(szseIndexFilePath);
}
szseIndexFilePath += "\\Index.xls";
WebClientUtil.DownloadFile(url, 180000, szseIndexFilePath);
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, szseIndexFilePath);
var worksheet = workbook.Worksheets[1] as Worksheet;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
for (int i = 2; i <= lastUsedRow; i++)
{
Range aRange = ExcelUtil.GetRange(i, 1, worksheet);
if (aRange != null && aRange.Text.ToString().Trim() != string.Empty)
{
ChinaIndex index = new ChinaIndex();
index.Chain = aRange.Text.ToString().Trim();
index.ChineseName = ExcelUtil.GetRange(i, 2, worksheet).Text.ToString();
szseIndexList.Add(index);
}
}
}
}
public void StartGenerator()
{
ExcelApp excelApp = null;
COMAddIn comAddIn = null;
if (IsEikonExcelDisable)
{
SetEikonExcelDisable(ref excelApp, ref comAddIn);
}
try
{
Start();
SaveTaskResult();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (IsEikonExcelDisable)
{
SetEikonExcelAble(ref excelApp, ref comAddIn);
}
AddResult("Log file", Logger.FilePath, "log");
}
}
/// <summary>
/// Open Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonOopenExcel_OnClick(object sender, RoutedEventArgs e)
{
// Open Excel and Read Data
OpenFileDialog m_dlg = new OpenFileDialog
{
Filter = "Excel Docs (*.xlsx)|*.xlsx",
Title = "Select an Excel Mail Merge Source",
Multiselect = false
};
if (m_dlg.ShowDialog() != true)
{
return;
}
if (!string.IsNullOrEmpty(m_dlg.FileName))
{
_excel = new ExcelApp(m_dlg.FileName);
}
// Worksheets
ComboBoxWorksheets.ItemsSource = _excel.WorkSheetTables;
ComboBoxWorksheets.DisplayMemberPath = "Key";
if (ComboBoxWorksheets.Items.Count > 0)
{
ComboBoxWorksheets.SelectedIndex = 0;
}
_excel.ExcelShutDown();
FormIsReady();
}
private void ReadFile(List <WrtQuaNotHK> list, string path)
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, path);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 5, 7, ExcelLineWriter.Direction.Right))
{
string code = string.Empty;
string listingDate = string.Empty;
string issuePrice = string.Empty;
string furtherIssueSize = string.Empty;
for (int i = 1; i <= lastUsedRow; i++)
{
listingDate = reader.ReadLineCellText();
if (listingDate != null && listingDate.Trim() != "")
{
if (NextWorkDay().ToString("M/d/yyyy").Equals(listingDate.Trim()))
{
reader.PlaceNext(reader.Row, 2);
code = reader.ReadLineCellText();
reader.PlaceNext(reader.Row, 8);
issuePrice = reader.ReadLineCellText();
reader.PlaceNext(reader.Row, 9);
furtherIssueSize = reader.ReadLineCellText().Replace(",", "");
if (code == null || issuePrice == null || furtherIssueSize == null)
{
string msg = String.Format("value (row,clo)=({0},{1}) is null!", reader.Row, reader.Col);
Logger.Log(msg, Logger.LogType.Error);
MessageBox.Show(msg);
continue;
}
WrtQuaNotHK wrt = new WrtQuaNotHK();
wrt.LogicalKey = list.Count + 1;
wrt.SecondaryID = code.Trim();
wrt.ISIN = GetISIN(wrt.SecondaryID);
wrt.SecondaryIDType = "HONG KONG CODE";
wrt.EHIssueQuantity = "N";
wrt.IssueQuantity = GetIssueQuantity(wrt.SecondaryID, furtherIssueSize);
wrt.Action = "I";
wrt.Note1Type = "O";
wrt.TranchePrice = issuePrice.Replace("HKD", "").Trim();
wrt.Note1 = string.Format("Further issue with issue price HKD {0} on {1}.", issuePrice.Replace("HKD", "").Trim(), NextWorkDay().ToString("dd-MMM-yyyy"));
wrt.TrancheListingDate = DateTime.Parse(listingDate).ToString("dd-MMM-yyyy");
list.Add(wrt);
}
}
reader.PlaceNext(reader.Row + 1, 7);
}
}
}
}
private JpTRADGInfo printAndGetJpDailyTradingInfo(string filePath)
{
JpTRADGInfo tradgInfo = new JpTRADGInfo();
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, filePath);
var worksheet = workbook.Worksheets[1] as Worksheet;
JapanShared.PrintWorksheet(worksheet, printerName, XlPageOrientation.xlPortrait);
tradgInfo.GSellSum = ExcelUtil.GetRange(7, 5, worksheet).Value2.ToString().Trim();
tradgInfo.GBuySum = ExcelUtil.GetRange(7, 10, worksheet).Value2.ToString().Trim();
tradgInfo.CurrentSellSum = ExcelUtil.GetRange(12, 5, worksheet).Value2.ToString().Trim();
tradgInfo.NextSellSum = ExcelUtil.GetRange(12, 6, worksheet).Value2.ToString().Trim();
tradgInfo.TotalSellSum = ExcelUtil.GetRange(12, 8, worksheet).Value2.ToString().Trim();
tradgInfo.CurrentNChg1 = ExcelUtil.GetRange(13, 5, worksheet).Value2.ToString();
tradgInfo.NextNChg1 = ExcelUtil.GetRange(13, 6, worksheet).Value2.ToString();
tradgInfo.TotalNchg1 = ExcelUtil.GetRange(13, 8, worksheet).Value2.ToString();
tradgInfo.CurrentBuySum = ExcelUtil.GetRange(12, 10, worksheet).Value2.ToString();
tradgInfo.NextBuySum = ExcelUtil.GetRange(12, 12, worksheet).Value2.ToString();
tradgInfo.TotalBuySum = ExcelUtil.GetRange(12, 14, worksheet).Value2.ToString();
tradgInfo.CurrentNChg2 = ExcelUtil.GetRange(13, 10, worksheet).Value2.ToString();
tradgInfo.NextNChg2 = ExcelUtil.GetRange(13, 12, worksheet).Value2.ToString();
tradgInfo.TotalNchg2 = ExcelUtil.GetRange(13, 14, worksheet).Value2.ToString().Trim();
tradgInfo.Date = GetDateTime(ExcelUtil.GetRange(33, 3, worksheet).Value2.ToString().Trim()).ToString("ddMMMyy");
int currentRow = 37;
while (ExcelUtil.GetRange(currentRow, 3, worksheet).Value2.ToString().Trim() != "-")
{
Transaction tran = new Transaction
{
CompanyInfo =
{
OriginalName =
ExcelUtil.GetRange(currentRow, 3, worksheet).Value2.ToString().Replace(" ", "")
}
};
if (nameDic.ContainsKey(tran.CompanyInfo.OriginalName))
{
tran.CompanyInfo.EnglishName = nameDic[tran.CompanyInfo.OriginalName].EnglistName;
tran.CompanyInfo.ShortEnglishName = nameDic[tran.CompanyInfo.OriginalName].ShortName;
tran.CompanyInfo.JapaneseName = nameDic[tran.CompanyInfo.OriginalName].JapaneseName;
}
else
{
Logger.Log(string.Format("There's no such name for {0}, please check the baknote file.", tran.CompanyInfo.OriginalName), Logger.LogType.Warning);
}
tran.SellSum = ExcelUtil.GetRange(currentRow, 7, worksheet).Value2.ToString();
tran.BuySum = ExcelUtil.GetRange(currentRow, 9, worksheet).Value2.ToString();
tran.TotalSum = ExcelUtil.GetRange(currentRow, 13, worksheet).Value2.ToString();
tradgInfo.TransactionList.Add(tran);
currentRow++;
}
tradgInfo.Top15SellSum = ExcelUtil.GetRange(52, 7, worksheet).Value2.ToString().Trim();
tradgInfo.Top15BuySum = ExcelUtil.GetRange(52, 9, worksheet).Value2.ToString().Trim();
tradgInfo.Top15TotalSum = ExcelUtil.GetRange(52, 13, worksheet).Value2.ToString().Trim();
tradgInfo.GTotalSum = ExcelUtil.GetRange(53, 13, worksheet).Value2.ToString().Trim();
workbook.Close(false, workbook.FullName, Missing.Value);
}
return(tradgInfo);
}
/// <summary>
/// Get nameMap from baknote.xls file
/// </summary>
/// <param name="bakNoteFilePath">baknote file path</param>
/// <param name="worksheetName"></param>
/// <returns></returns>
public static Dictionary <string, NameMap> GetNameMap(string bakNoteFilePath, string worksheetName)
{
Dictionary <string, NameMap> nameDic = new Dictionary <string, NameMap>();
try
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, bakNoteFilePath);
var worksheet = ExcelUtil.GetWorksheet(worksheetName, workbook);
if (worksheet == null)
{
throw new System.Exception(string.Format("There's no such worksheet {0}", worksheetName));
}
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
for (int i = 4; i <= lastUsedRow; i++)
{
string key = string.Empty;
if (ExcelUtil.GetRange(i, 4, worksheet).Value2 != null && ExcelUtil.GetRange(i, 4, worksheet).Value2.ToString() != string.Empty)
{
key = ExcelUtil.GetRange(i, 2, worksheet).Value2.ToString().Trim();
}
if (!nameDic.ContainsKey(key))
{
NameMap map = new NameMap();
if (ExcelUtil.GetRange(i, 4, worksheet).Value2 != null)
{
map.JapaneseName = ExcelUtil.GetRange(i, 4, worksheet).Value2.ToString().Trim();
}
if (ExcelUtil.GetRange(i, 3, worksheet).Value2 != null)
{
map.EnglistName = ExcelUtil.GetRange(i, 3, worksheet).Value2.ToString().Trim();
}
if (ExcelUtil.GetRange(i, 5, worksheet).Value2 != null)
{
map.Ric = ExcelUtil.GetRange(i, 5, worksheet).Value2.ToString().Trim();
}
if (ExcelUtil.GetRange(i, 6, worksheet).Value2 != null)
{
map.ShortName = ExcelUtil.GetRange(i, 6, worksheet).Value2.ToString().Trim();
}
nameDic.Add(key, map);
}
}
workbook.Close(false, workbook.FullName, Missing.Value);
}
}
catch (System.Exception)
{
throw new System.Exception("There's an error when getting name map from file baknote");
}
return(nameDic);
}
/// <summary>
/// Creates a new instance of the <see cref="WjaComparisonTool"/> class
/// </summary>
public WjaComparisonTool()
{
_excelApp = new ExcelApp();
_eprReport = new Dictionary <string, string>();
_wjaReport = new Dictionary <string, string>();
InitializeComponent();
}
private void UpdateAddDropFile(string templateFilePath, List <AddDropChangeInfo> addDropEventList)
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, templateFilePath);
var worksheet = ExcelUtil.GetWorksheet("INPUT SHEET", workbook);
if (worksheet == null)
{
Logger.LogErrorAndRaiseException(string.Format("There's no worksheet: {0}", worksheet.Name));
}
((Range)worksheet.Columns["D"]).NumberFormat = "@";
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 3, 1, ExcelLineWriter.Direction.Right))
{
foreach (AddDropChangeInfo changeInfo in addDropEventList)
{
writer.WriteLine(changeInfo.EventAction);
writer.WriteLine(changeInfo.RicSeqId);
writer.WriteLine(changeInfo.ChangeType);
writer.WriteLine(changeInfo.Date.ToString("ddMMMyy"));
writer.WriteLine(changeInfo.DescriptionWas);
writer.WriteLine(changeInfo.DescriptionNow);
writer.WriteLine(changeInfo.RicWas);
writer.WriteLine(changeInfo.RicNow);
writer.WriteLine(changeInfo.ISINWas);
writer.WriteLine(changeInfo.ISINNow);
writer.WriteLine(changeInfo.SecondID);
writer.WriteLine(changeInfo.SecondWas);
writer.WriteLine(changeInfo.SecondNow);
writer.WriteLine(changeInfo.ThomsonWas);
writer.WriteLine(changeInfo.ThomsonNow);
writer.WriteLine("");
writer.WriteLine(changeInfo.Exchange);
writer.WriteLine(changeInfo.Asset);
writer.PlaceNext(writer.Row + 1, 1);
}
}
//Run Macros
app.ExcelAppInstance.GetType().InvokeMember("Run",
BindingFlags.Default | BindingFlags.InvokeMethod,
null,
app.ExcelAppInstance,
new object[] { "FormatData" });
string targetFilePath = Path.GetDirectoryName(templateFilePath);
targetFilePath = Path.Combine(targetFilePath, DateTime.Today.ToString("yyyy-MM-dd"));
if (!Directory.Exists(targetFilePath))
{
Directory.CreateDirectory(targetFilePath);
}
targetFilePath += "\\Result_Add_Drop_Upload_File.xls";
workbook.SaveCopyAs(targetFilePath);
AddResult("Result File", targetFilePath, "");
//workbook.Save();
workbook.Close(false, templateFilePath, false);
}
}
protected override void DoAction()
{
ExcelApp.GetType().InvokeMember("Run", // it'd be nice if there
System.Reflection.BindingFlags.Default | // was an easier way of
System.Reflection.BindingFlags.InvokeMethod, // doing this, but we're
null, // bound by c# being a
ExcelApp, // strongly typed language
this.GetMacroArgArray());
}
private List <FMELWEntity> ReadFile(string filePath, List <FMELWEntity> listFMELW)
{
List <FMELWEntity> list = new List <FMELWEntity>();
if (!File.Exists(filePath))
{
string msg = string.Format("the file [{0}] is not exist.", filePath);
Logger.Log(msg, Logger.LogType.Error);
return(null);
}
try
{
using (ExcelApp eApp = new ExcelApp(false, false))
{
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(eApp, filePath);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (wSheet == null)
{
string msg = "Worksheet could not be created. Check that your office installation and project reference are correct!";
Logger.Log(msg, Logger.LogType.Error);
return(null);
}
//wSheet.Name = "FM";
int lastUsedRow = wSheet.UsedRange.Row + wSheet.UsedRange.Rows.Count - 1;//193=1+193-1
using (ExcelLineWriter reader = new ExcelLineWriter(wSheet, 2, 2, ExcelLineWriter.Direction.Right))
{
while (reader.Row <= lastUsedRow)
{
FMELWEntity fm = new FMELWEntity();
fm.ISIN = reader.ReadLineCellText();//web.StandardCongenial
reader.PlaceNext(reader.Row, 4);
fm.IssuingAuthority = reader.ReadLineValue2();
reader.PlaceNext(reader.Row + 1, 2);
if (!string.IsNullOrEmpty(fm.ISIN.Trim()) && !string.IsNullOrEmpty(fm.IssuingAuthority.Trim()))
{
list.Add(fm);
}
}
}
}
return(list);
}
catch (Exception ex)
{
string msg = string.Format("read xls file error :{0}", ex.ToString());
Logger.Log(msg);
return(null);
}
}
public static void GenerateSecurityOptionTargetFile(string filePath, List <SecurityOptionSector> sectorList, string contentTitle, int rowNumEachSector, int startPageNum, int maxPageNum, string pageNamePre, DateTime lastBusinessDay)
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, filePath);
var worksheet = workbook.Worksheets[1] as Worksheet;
GenerateSecurityOptionTargetFile(worksheet, sectorList, contentTitle, rowNumEachSector, startPageNum, maxPageNum, pageNamePre, lastBusinessDay);
workbook.Save();
}
}
private void FillInListTemplate(List <IssueAssetAddTemplate> listIAATemplate)
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, hkQAAddFileName);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 1, ExcelLineWriter.Direction.Right))
{
string ric = string.Empty;
string warrantIssueQuantity = string.Empty; //18
string tranche = string.Empty; //17
string trancheListingAate = string.Empty; //16
for (int i = 1; i <= lastUsedRow; i++)
{
ric = reader.ReadLineCellText();
if (ric == null || ric.Trim() == "")
{
continue;
}
foreach (var item in listIAATemplate)
{
if (!(item.HongKongCode.Trim() + ".HK").Equals(ric.Trim()))
{
continue;
}
reader.PlaceNext(reader.Row, 16);
trancheListingAate = DateTime.Parse(reader.ReadLineCellText()).ToString("dd-MMM-yyyy");
reader.PlaceNext(reader.Row, 17);
tranche = reader.ReadLineCellText();
reader.PlaceNext(reader.Row, 18);
warrantIssueQuantity = reader.ReadLineValue2();
if (trancheListingAate == null || tranche == null || warrantIssueQuantity == null)
{
string msg = String.Format("value (row,clo)=({0},{1}) is null!", reader.Row, reader.Col);
Logger.Log(msg, Logger.LogType.Error);
MessageBox.Show(msg);
continue;
}
item.TrancheListingDate = trancheListingAate;
item.TranchePrice = tranche;
item.WarrantIssueQuantity = warrantIssueQuantity;
}
reader.PlaceNext(reader.Row + 1, 1);
}
}
}
}
protected void MagicItemCommand(object sender, MagicItemEventArgs e)
{
if (e.CommandName == "Save")
{
if (this.FileUpload1.FileName.Trim().Length <= 0)
{
WebUtil.ShowMsg(this, "请选择盘点结果文件");
return;
}
string fileName = this.FileUpload1.FileName;
if (!fileName.EndsWith(".xls"))
{
WebUtil.ShowMsg(this, "请选择有效的Excel文件");
return;
}
string filePath = System.IO.Path.Combine(DownloadUtil.DownloadFolder, "CK_IMP_" + DateTime.Now.ToString("yyMMdd_HHmmss") + ".xls");
this.FileUpload1.SaveAs(filePath);
IList<INVCheckLine> lines = new List<INVCheckLine>();
#region 读取文件
ExcelApp excelapp = null;
ExcelWorkbook excelBook = null;
ExcelWorksheet excelSheet = null;
try
{
excelapp = new ExcelApp();
excelapp.DisplayAlerts = false;
excelBook = excelapp.Open(filePath, 0);
excelSheet = excelBook.Worksheets(1);
int rowIndex = 2;
string lineNum = Cast.String(excelSheet.Range(rowIndex, rowIndex, 1, 1).Value).Trim();
decimal qty;
while (lineNum.Length==4)
{
qty = Cast.Decimal(excelSheet.Range(rowIndex, rowIndex, 9, 9).Value, 0M);
INVCheckLine line = new INVCheckLine();
line.LineNumber = lineNum;
line.CurrentQty = qty;
lines.Add(line);
rowIndex++;
lineNum = Cast.String(excelSheet.Range(rowIndex, rowIndex, 1, 1).Value).Trim();
}
}
catch (Exception er)
{
WebUtil.ShowError(this, er.Message);
return;
}
finally
{
if (excelSheet != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet.COMObject);
if (excelBook != null)
{
excelBook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook.COMObject);
}
if (excelapp != null)
{
excelapp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp.COMObject);
}
}
#endregion
using (ISession session = new Session())
{
try
{
INVCheckHead head = INVCheckHead.Retrieve(session, WebUtil.Param("ordNum"));
if (head == null)
{
WebUtil.ShowError(this, "盘点单"+WebUtil.Param("ordNum")+"不存在");
return;
}
session.BeginTransaction();
head.ClearCheckQty(session);
head.UpdateLines(session, lines);
session.Commit();
}
catch (Exception er)
{
session.Rollback();
WebUtil.ShowError(this, er.Message);
return;
}
}
this.Response.Redirect(WebUtil.Param("return"));
}
}
/// <summary>
/// 模板格式下载，用<paramref name="items"/>中每个key, value替换模板中的标签（标签名称为key）
/// </summary>
/// <param name="name">用户下载框中显示的保存文件名，例如：SN_08082600012.xls</param>
/// <param name="prefix">内部生成的下载文件前缀，例如：SN</param>
/// <param name="template">模板文件的物理路径（不是IIS虚拟路径）</param>
/// <param name="items">标签的键、值对</param>
/// <returns>返回下载文件的链接地址（使用download.aspx）</returns>
public static string DownloadXls(string name, string prefix, string template, IDictionary<string, string> items)
{
ExcelApp excelApp = null;
ExcelWorkbook workbook = null;
ExcelWorksheet sheet = null;
string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
string filePath = DownloadFolder + fileName;
try
{
excelApp = new ExcelApp();
workbook = excelApp.Open(template, 0);
workbook.SaveAs(filePath);
sheet = workbook.Worksheets(1);
ExcelRange range = sheet.Cells();
foreach (KeyValuePair<string, string> kv in items)
range.Replace(kv.Key, kv.Value, false);
workbook.Save();
}
catch (Exception er)
{
throw er;
}
finally
{
if (sheet != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject);
if (workbook != null)
{
workbook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject);
}
if (excelApp != null)
{
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject);
}
}
return "/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath);
}
/// <summary>
/// 模板格式+简单格式下载，先用<paramref name="items"/>中每个key, value替换模板中的标签（标签名称为key），再下载<paramref name="ds"/>的数据行
/// </summary>
/// <param name="name">用户下载框中显示的保存文件名，例如：SN_08082600012.xls</param>
/// <param name="prefix">内部生成的下载文件前缀，例如：SN</param>
/// <param name="template">模板文件的物理路径（不是IIS虚拟路径）</param>
/// <param name="items">标签的键、值对</param>
/// <param name="rowIndex">数据行的开始位置（1开始的索引，即Excel中的行号）</param>
/// <param name="ds"></param>
/// <returns>返回下载文件的链接地址（使用download.aspx）</returns>
public static string DownloadXls(string name, string prefix, string template, IDictionary<string, string> items, int rowIndex, IList<DownloadFormat> format, DataSet ds)
{
ExcelApp excelApp = null;
ExcelWorkbook workbook = null;
ExcelWorksheet sheet = null;
string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
string filePath = DownloadFolder + fileName;
try
{
excelApp = new ExcelApp();
workbook = excelApp.Open(template, 0);
workbook.SaveAs(filePath);
sheet = workbook.Worksheets(1);
//标签替换
ExcelRange range = sheet.Cells();
if (items != null)
foreach (KeyValuePair<string, string> kv in items)
range.Replace(kv.Key, kv.Value, false);
//数据行
int index = rowIndex;
foreach (DataRow row in ds.Tables[0].Rows)
{
for (int i = 0; i < format.Count; i++)
{
DownloadFormat ft = format[i];
if (ft.ColumnIndex == null)
continue;
for (int j = 0; j < ft.ColumnIndex.Length; j++)
SetCellValue(sheet.Cells(index, i + 1), j, ft.Type, row, ft.ColumnIndex[j]);
}
index++;
}
workbook.Save();
}
catch (Exception er)
{
throw er;
}
finally
{
if (sheet != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject);
if (workbook != null)
{
workbook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject);
}
if (excelApp != null)
{
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject);
}
}
return "/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath);
}
/// <summary>
/// 简单格式下载，只下载<paramref name="ds"/>的数据行
/// </summary>
/// <param name="name">用户下载框中显示的保存文件名，例如：SN_08082600012.xls</param>
/// <param name="prefix">内部生成的下载文件前缀，例如：SN</param>
/// <param name="format">数据列的格式描述信息</param>
/// <param name="ds"></param>
/// <returns>返回下载文件的链接地址（使用download.aspx）</returns>
public static string DownloadXls(string name, string prefix, IList<DownloadFormat> format, DataSet ds)
{
string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
string filePath = DownloadFolder + fileName;
ExcelApp excelapp = null;
ExcelWorkbook excelBook = null;
ExcelWorksheet excelSheet = null;
try
{
excelapp = new ExcelApp();
excelapp.DisplayAlerts = false;
excelBook = excelapp.NewWorkBook();
excelSheet = excelBook.Worksheets(1);
int rowIndex = 1;
for (int i = 0; i < format.Count; i++)
excelSheet.Cells(rowIndex, i + 1).Value = format[i].Title;
ExcelRange rg = excelSheet.Range(rowIndex, rowIndex, 1, format.Count);
rg.SelectRange();
rg.Font.Bold = true;
rg.HorizontalAlignment = 3;
rg.Interior.SetColor(221, 221, 221);
rowIndex++;
#region 写文件
foreach (DataRow row in ds.Tables[0].Rows)
{
for (int i = 0; i < format.Count; i++)
{
DownloadFormat ft = format[i];
if (ft.ColumnIndex == null)
continue;
for (int j = 0; j < ft.ColumnIndex.Length; j++)
SetCellValue(excelSheet.Cells(rowIndex, i + 1), j, ft.Type, row, ft.ColumnIndex[j]);
}
rowIndex++;
}
#endregion
ExcelRange excelRange = excelSheet.Cells();
excelRange.SelectRange();
excelRange.AutoFit();
excelRange.Font.Size = 10;
excelBook.SaveAs(filePath);
}
catch (Exception er)
{
throw er;
}
finally
{
if (excelSheet != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet.COMObject);
if (excelBook != null)
{
excelBook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook.COMObject);
}
if (excelapp != null)
{
excelapp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp.COMObject);
}
}
return "/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath);
}
