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