public void Run() { // start application Excel.Application application = new Excel.Application(); application.DisplayAlerts = false; // create new Workbook & attach close event trigger Excel.Workbook book = application.Workbooks.Add(); book.BeforeCloseEvent += new Excel.Workbook_BeforeCloseEventHandler(book_BeforeCloseEvent); // we dispose the instance. the parameter false signals to api dont release the event listener // set parameter to true and the event listener will stopped and you dont get events for the instance // the DisposeChildInstances() method has the same method overload book.Close(); book.Dispose(false); application.Quit(); application.Dispose(); // the application object is ouer root object // dispose them release himself and any childs of application, in this case workbooks and workbook // the excel instance are now removed from process list _hostApplication.ShowFinishDialog(); }
internal bool SaveAs(Person p, string filename) { bool result = false; WorkBook = ExcelApp.Workbooks.Open(ExcelFilename); WorkSheet = (Worksheet)WorkBook.Worksheets[1]; try { WorkSheet.Range(cellInfo.IdCell).Value2 = p.Id; WorkSheet.Range(cellInfo.NameCell).Value2 = p.PersonName; WorkSheet.Range(cellInfo.DateCell).Value2 = p.Date; WorkSheet.Range(cellInfo.CompanyCell).Value2 = p.Company; WorkSheet.Range(cellInfo.ContentCell).Value2 = p.DiaryContent; WorkSheet.Range(cellInfo.DepartCell).Value2 = p.Department; WorkSheet.SaveAs(filename); if (System.IO.File.Exists(filename)) { ExcelFilename = filename; result = true; } return(result); } finally { WorkBook.Close(); } }
public void Dispose() { try { WorkBook.Close(); ExcelApp.Quit(); ExcelApp.Dispose(); } catch { } }
internal string ReadCell(string cell) { string cellstr = string.Empty; WorkBook = ExcelApp.Workbooks.Open(ExcelFilename); WorkSheet = (Worksheet)WorkBook.Worksheets[1]; try { cellstr = WorkSheet.Range(cell).Value2.ToString(); } catch { } finally { WorkBook.Close(); } return(cellstr); }
internal void Write(Person p) { WorkBook = ExcelApp.Workbooks.Open(ExcelFilename); WorkSheet = (Worksheet)WorkBook.Worksheets[1]; try { WorkSheet.Range(cellInfo.IdCell).Value2 = p.Id; WorkSheet.Range(cellInfo.NameCell).Value2 = p.PersonName; WorkSheet.Range(cellInfo.DateCell).Value2 = p.Date; WorkSheet.Range(cellInfo.CompanyCell).Value2 = p.Company; WorkSheet.Range(cellInfo.ContentCell).Value2 = p.DiaryContent; WorkSheet.Range(cellInfo.DepartCell).Value2 = p.Department; } finally { WorkBook.Close(); } }
private void Clear() { string root = @"C:\Workspace\Repo\Git\ecoLLaboMES\doc"; //Excelファイルを探す var fileList = FileUtil.GetAllFileInfo(root); var application = new Excel.Application { Visible = true }; foreach (FileInfo fi in fileList) { //if (fi.Extension.ToLower() == ".xls" || fi.Extension.ToLower() == ".xlsx") { if (fi.Extension.ToLower() == ".xls") { Excel.Workbook book = null; try { Debug.Print(fi.FullName); book = application.Workbooks.Open(fi.FullName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //Debug.Print(books.Name + ":" + books.Names.Count().ToString() + ":" + books.Names.ToString()); if (book.Names.Count() > 0) { Debug.Print("Find!!" + book.FullName); } } catch { //スルー } finally { try { if (book != null) { book.Close(false, Type.Missing, Type.Missing); } } catch { //更にスルー } } } } application.Quit(); }
internal bool SaveAs(string filename) { bool result = false; WorkBook = ExcelApp.Workbooks.Open(ExcelFilename); WorkSheet = (Worksheet)WorkBook.Worksheets[1]; try { WorkSheet.SaveAs(filename); if (System.IO.File.Exists(filename)) { ExcelFilename = filename; result = true; } return(result); } finally { WorkBook.Close(); } }
internal async Task <Person> ReadAsync() { WorkBook = ExcelApp.Workbooks.Open(ExcelFilename); WorkSheet = (Worksheet)WorkBook.Worksheets[1]; Person person = new Person(); try { person.Id = WorkSheet.Range(cellInfo.IdCell).Value2.ToString(); person.PersonName = WorkSheet.Range(cellInfo.NameCell).Value2.ToString(); person.Department = WorkSheet.Range(cellInfo.DepartCell).Value2.ToString(); person.Company = WorkSheet.Range(cellInfo.CompanyCell).Value2.ToString(); person.Date = DateTime.FromOADate((double)WorkSheet.Range(cellInfo.DateCell).Value2).ToShortDateString(); person.DiaryContent = WorkSheet.Range(cellInfo.ContentCell).Value2.ToString(); } catch { } finally { WorkBook.Close(); } return(person); }
private void buttonStartExample_Click(object sender, EventArgs e) { // start excel and turn off msg boxes Excel.Application excelApplication = new Excel.Application(); excelApplication.DisplayAlerts = false; excelApplication.Visible = true; // we register some events. note: the event trigger was called from excel, means another Thread // you can get event notifys from various objects: Application or Workbook or Worksheet for example excelApplication.NewWorkbookEvent += new Excel.Application_NewWorkbookEventHandler(excelApplication_NewWorkbook); excelApplication.WorkbookBeforeCloseEvent += new Excel.Application_WorkbookBeforeCloseEventHandler(excelApplication_WorkbookBeforeClose); excelApplication.WorkbookActivateEvent += new Excel.Application_WorkbookActivateEventHandler(excelApplication_WorkbookActivate); excelApplication.WorkbookDeactivateEvent += new Excel.Application_WorkbookDeactivateEventHandler(excelApplication_WorkbookDeactivate); excelApplication.SheetActivateEvent += new Excel.Application_SheetActivateEventHandler(_excelApplication_SheetActivateEvent); excelApplication.SheetDeactivateEvent += new Excel.Application_SheetDeactivateEventHandler(_excelApplication_SheetDeactivateEvent); // add a new workbook, add a sheet and close Excel.Workbook workBook = excelApplication.Workbooks.Add(); workBook.Worksheets.Add(); workBook.Close(); excelApplication.Quit(); excelApplication.Dispose(); }
public TestResult DoTest() { Excel.Application application = null; DateTime startTime = DateTime.Now; try { // start excel and turn off msg boxes application = new Excel.Application(); application.DisplayAlerts = false; application.Visible = true; application.NewWorkbookEvent += new Excel.Application_NewWorkbookEventHandler(ExcelApplication_NewWorkbook); application.WorkbookBeforeCloseEvent += new Excel.Application_WorkbookBeforeCloseEventHandler(ExcelApplication_WorkbookBeforeClose); application.WorkbookActivateEvent += new Excel.Application_WorkbookActivateEventHandler(ExcelApplication_WorkbookActivate); application.WorkbookDeactivateEvent += new Excel.Application_WorkbookDeactivateEventHandler(ExcelApplication_WorkbookDeactivate); application.SheetActivateEvent += new Excel.Application_SheetActivateEventHandler(ExcelApplication_SheetActivateEvent); application.SheetDeactivateEvent += new Excel.Application_SheetDeactivateEventHandler(ExcelApplication_SheetDeactivateEvent); // add a new workbook add a sheet and close Excel.Workbook workBook = application.Workbooks.Add(); workBook.Worksheets.Add(); workBook.Close(); if (_newWorkbookEvent && _workbookBeforeCloseEvent && _sheetActivateEvent && _sheetDeactivateEvent && _workbookActivateEvent && _workbookDeactivateEvent) { return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, "")); } else { string errorMessage = ""; if (!_newWorkbookEvent) { errorMessage += "NewWorkbookEvent failed "; } if (!_workbookBeforeCloseEvent) { errorMessage += "WorkbookBeforeCloseEvent failed "; } if (!_sheetActivateEvent) { errorMessage += "WorkbookActivateEvent failed "; } if (!_sheetDeactivateEvent) { errorMessage += "WorkbookDeactivateEvent failed "; } if (!_workbookActivateEvent) { errorMessage += "SheetActivateEvent failed "; } if (!_workbookDeactivateEvent) { errorMessage += "SheetDeactivateEvent failed "; } return(new TestResult(true, DateTime.Now.Subtract(startTime), errorMessage, null, "")); } } catch (Exception exception) { return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, "")); } finally { if (null != application) { application.Quit(); application.Dispose(); } } }
public static void Convert(String xlsxFilePath, IDelimitedTextWriter textWriter, WorksheetConverter worksheetConverter = null) { if (xlsxFilePath == null) { throw new ArgumentNullException(nameof(xlsxFilePath)); } if (textWriter == null) { throw new ArgumentNullException(nameof(textWriter)); } if (System.IO.File.Exists(xlsxFilePath) == false) { throw new FileNotFoundException(String.Format("\"{0}\" file has not been found.", xlsxFilePath)); } if (worksheetConverter == null) { worksheetConverter = new WorksheetConverter(); } using (Excel.Application excelApplication = new Excel.Application()) { try { excelApplication.Visible = false; excelApplication.ScreenUpdating = false; excelApplication.EnableEvents = false; using (Excel.Workbooks workbooks = excelApplication.Workbooks) { using (Excel.Workbook emptyExcelWorkbook = workbooks.Count == 0 ? workbooks.Add() : null) { try { excelApplication.Calculation = XlCalculation.xlCalculationManual; using (var excelWorkbook = workbooks.Open(filename: Path.GetFullPath(xlsxFilePath))) { Object activeSheet = excelWorkbook.ActiveSheet; try { if (activeSheet is Excel.Worksheet == false) { throw new ActiveWorksheetException(); } Excel.Worksheet worksheet = (Excel.Worksheet)activeSheet; worksheetConverter.Convert(worksheet, textWriter); } finally { (activeSheet as IDisposable)?.Dispose(); } } } finally { emptyExcelWorkbook?.Close(saveChanges: false); } } } } finally { excelApplication.Quit(); } } }
/// <summary> /// ExcelファイルのA1セル選択処理 /// 引数1:Excelファイルパス /// </summary> /// <param name="args"></param> /// <returns></returns> public static int Main(string[] args) { //引数チェック if (!CheckArgs(args)) { return(NG); } //Excel起動 WriteLog("Excel起動待ち"); using (Excel.Application excelApplication = new Excel.Application()) { WriteLog("Excel起動"); try { //Excel描画停止(速度改善のため) ExcelBeginUpdate(excelApplication); //対象ファイルを開く WriteLog("ファイル開く"); Excel.Workbook workBook = excelApplication.Workbooks.Open(filePath); //A1セルを選択状態にする WriteLog($"全シート数:{workBook.Sheets.Count}"); for (int i = 1; i <= workBook.Sheets.Count; i++) { WriteLog($"{i}シート目処理中"); //シート取得 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Sheets[i]; WriteLog($"シート名:{sheet.Name}"); //非表示のシートは操作に失敗するので無視する if (sheet.Visible != Excel.Enums.XlSheetVisibility.xlSheetVisible) { continue; } //シートを選択状態にする(こうしないとセル選択に失敗する) sheet.Select(); //倍率を100%に変更 excelApplication.ActiveWindow.Zoom = 100; //スクロールを一番先頭に変更(選択セルを変更しても、スクロールバーの位置が元のままなので、先頭に移動) excelApplication.ActiveWindow.ScrollColumn = 1; excelApplication.ActiveWindow.ScrollRow = 1; //一番左上のA1セルを選択状態に変更 sheet.Range("A1").Select(); } //一番左に存在するシートを選択状態にする WriteLog("一番左のシートを選択"); Excel.Worksheet firstSheet = (Excel.Worksheet)workBook.Sheets[1]; firstSheet.Select(); //保存 WriteLog("保存"); workBook.Save(); workBook.Close(); //Excel終了 excelApplication.Quit(); } catch (Exception ex) { //何らかのエラー発生 WriteLog("例外エラー発生", ex); return(NG); } finally { //Excelを閉じる try { excelApplication.Quit(); excelApplication.Dispose(); } catch { } //例外発生時は無視 } } return(OK); }
private void doWork2() { PowerPoint.Presentation presentation = null; PowerPoint.Application applicationPowerPoint = null; Excel.Workbook book = null; Excel.Application applicationExcel = null; try { Dictionary <String, Excel.Shape> excelShapesDictionary = new Dictionary <string, Excel.Shape>(); Log.Information("Opening Excel Applicaiton"); applicationExcel = new Excel.Application(); applicationExcel.DisplayAlerts = false; String xlsx = "D:\\Input\\Input.xlsx"; String output_folder = "D:\\Output"; String pptxSingle = "D:\\Input\\input.pptx"; Log.Information("Opening Excel File {0}", xlsx); book = applicationExcel.Workbooks.Open(xlsx, true, true); Excel.Worksheet worksheet = (Excel.Worksheet)book.Worksheets[1]; int index = 3; List <PresentationItem> presentationItems = new List <PresentationItem>(); do { String s = worksheet.Range("A" + index).Value2.ToString(); Log.Information(worksheet.Name + " " + s); PresentationItem presentationItem = new PresentationItem(); presentationItem.OutputFileName = s; int index2 = 1; do { String attr_type = worksheet.Range(((char)((byte)'A' + index2)).ToString() + 1).Value2.ToString(); String attr_name = worksheet.Range(((char)((byte)'A' + index2)).ToString() + 2).Value2.ToString(); String attr_value = worksheet.Range(((char)((byte)'A' + index2)).ToString() + index).Value2.ToString(); Log.Information("{0} {1} {2} {3}", ((char)((byte)'A' + index2)).ToString() + 1, attr_type, attr_name, attr_value); presentationItem.Attributes.Add(new Attribute(attr_type, attr_name, attr_value)); index2++; if (index2 == 20) { break; } } while (worksheet.Range(((char)((byte)'A' + index2)).ToString() + 1).Value2 != null && worksheet.Range(((char)((byte)'A' + index2)).ToString() + 1).Value2.ToString() != ""); index++; presentationItems.Add(presentationItem); if (index == 100) { break; } } while (worksheet.Range("A" + index).Value2 != null && worksheet.Range("A" + index).Value2.ToString() != ""); Log.Information("Closing Excel File"); book.Close(); Log.Information("Opening PowerPoint Applicaiton"); applicationPowerPoint = new PowerPoint.Application(); applicationPowerPoint.DisplayAlerts = PowerPoint.Enums.PpAlertLevel.ppAlertsNone; foreach (var presentationItem in presentationItems) { Dictionary <int, List <PowerPoint.Shape> > powerpointShapesSheetDictionary = new Dictionary <int, List <PowerPoint.Shape> >(); Log.Information("Opening Presentation {0}", pptxSingle); presentation = applicationPowerPoint.Presentations.Open(pptxSingle, true, true, true); foreach (PowerPoint.Shape shape in presentation.SlideMaster.Shapes) { Log.Information("\tSlide Master: Shape Name : {0} Size(w x h) : {1} x {2} Position(left x top) : {3} , {4}", shape.Name, shape.Width, shape.Height, shape.Left, shape.Top); if (shape.HasTextFrame == Office.Enums.MsoTriState.msoTrue && shape.TextFrame.HasText == Office.Enums.MsoTriState.msoTrue) { foreach (var attribute in presentationItem.Attributes) { if (attribute.type == "Text") { string before = shape.TextFrame.TextRange.Text; shape.TextFrame.TextRange.Replace("|*" + attribute.name + "*|", attribute.value); string after = shape.TextFrame.TextRange.Text; if (before != after) { Log.Information("\t\t{0} >> {1}", before, after); } } } } } Log.Information("Iterating all shapes in all slides and filtering shapes with name staring with \"#\""); foreach (PowerPoint.Slide slide in presentation.Slides) { foreach (PowerPoint.Shape shape in slide.Shapes) { Log.Information("\tSlide No : {0} Shape Name : {1} Size(w x h) : {2} x {3} Position(left x top) : {4} , {5}", slide.SlideNumber, shape.Name, shape.Width, shape.Height, shape.Left, shape.Top); if (shape.HasTextFrame == Office.Enums.MsoTriState.msoTrue && shape.TextFrame.HasText == Office.Enums.MsoTriState.msoTrue) { foreach (var attribute in presentationItem.Attributes) { if (attribute.type == "Text") { string before = shape.TextFrame.TextRange.Text; shape.TextFrame.TextRange.Replace("|*" + attribute.name + "*|", attribute.value); string after = shape.TextFrame.TextRange.Text; if (before != after) { Log.Information("\t\t{0} >> {1}", before, after); } } } } foreach (var attribute in presentationItem.Attributes) { if (attribute.type == "Chart" && shape.Name == "|*" + attribute.name + "*|") { string before = shape.Name; shape.Name = attribute.value; string after = shape.Name; if (before != after) { Log.Information("\t\t{0} >> {1}", before, after); } } } } Log.Information(""); } String outputfile = Path.Combine(output_folder, new FileInfo(pptxSingle).Name); Log.Information("Saving a copy of updated Presentation to {0}", outputfile); presentation.SaveCopyAs(outputfile); Log.Information("Closing Presentation"); } presentation.Close(); Log.Information("Closing PowerPoint Applicaiton"); applicationPowerPoint.Quit(); applicationPowerPoint.Dispose(); Log.Information("Closing Excel Applicaiton"); applicationExcel.Quit(); applicationExcel.Dispose(); Log.Information("Done"); } catch (Exception e) { Log.Error("Fatal Error - " + e.ToString()); thisForm.BeginInvoke((MethodInvoker)(() => { MessageBox.Show(thisForm, e.ToString(), "Fatal Error", MessageBoxButtons.OK, MessageBoxIcon.Error); })); try { presentation.Close(); } catch (Exception ee) { } try { applicationPowerPoint.Quit(); applicationPowerPoint.Dispose(); } catch (Exception ee) { } try { book.Close(); } catch (Exception ee) { } try { applicationExcel.Quit(); applicationExcel.Dispose(); } catch (Exception ee) { } } }
private void doWork() { PowerPoint.Presentation presentation = null; PowerPoint.Application applicationPowerPoint = null; Excel.Workbook book = null; Excel.Application applicationExcel = null; try { Dictionary <String, Excel.Shape> excelShapesDictionary = new Dictionary <string, Excel.Shape>(); Log.Information("Opening Excel Applicaiton"); applicationExcel = new Excel.Application(); applicationExcel.DisplayAlerts = false; Log.Information("Opening Excel File {0}", xlsx); book = applicationExcel.Workbooks.Open(xlsx, true, true); Boolean flagDouble = false; Log.Information("Iterating all shapes in all sheets and filtering shapes with name staring with \"#\""); foreach (Excel.Worksheet sheet in book.Worksheets) { foreach (Excel.Shape shape in sheet.Shapes) { if (shape.Name.Length > 1 && shape.Name.Substring(0, 1) == "#") { if (excelShapesDictionary.ContainsKey(shape.Name.ToLower().Trim())) { Log.Error("\t\tSheet: {0} Shape Name : {1} Error!!! Shape with the same name exists", sheet.Name, shape.Name, shape.Width, shape.Height, shape.Left, shape.Top); flagDouble = true; } else { Log.Information("\tSheet: {0} Shape Name : {1} ", sheet.Name, shape.Name, shape.Width, shape.Height, shape.Left, shape.Top); excelShapesDictionary.Add(shape.Name.ToLower().Trim(), shape); } } } } if (flagDouble) { Log.Error("ERROR -Found shapes with the same name : Duplicated shapes need to be manually renamed in the Excel file before proceeding. Terminating run."); thisForm.BeginInvoke((MethodInvoker)(() => { MessageBox.Show(thisForm, "Duplicated shapes need to be manually renamed in the Excel file before proceeding.\nSee logs for more information", "ERROR - Found shapes with the same name", MessageBoxButtons.OK, MessageBoxIcon.Error); })); goto closeWorkbook; } Log.Information("Opening PowerPoint Applicaiton"); applicationPowerPoint = new PowerPoint.Application(); applicationPowerPoint.DisplayAlerts = PowerPoint.Enums.PpAlertLevel.ppAlertsNone; foreach (string pptxSingle in pptx) { Dictionary <int, List <PowerPoint.Shape> > powerpointShapesSheetDictionary = new Dictionary <int, List <PowerPoint.Shape> >(); Log.Information("Opening Presentation {0}", pptxSingle); presentation = applicationPowerPoint.Presentations.Open(pptxSingle, true, true, false); Log.Information("Iterating all shapes in all slides and filtering shapes with name staring with \"#\""); Boolean datamissing = false; foreach (PowerPoint.Slide slide in presentation.Slides) { foreach (PowerPoint.Shape shape in slide.Shapes) { if (shape.Name.Length > 1 && shape.Name.Substring(0, 1) == "#") { if (!excelShapesDictionary.ContainsKey(shape.Name.ToLower().Trim())) { Log.Information("\t\tData Missing for Slide No : {0} Shape Name : {1} ", slide.SlideNumber, shape.Name, shape.Width, shape.Height, shape.Left, shape.Top); datamissing = true; } else { Log.Information("\tFound data for Slide No : {0} Shape Name : {1} ", slide.SlideNumber, shape.Name, shape.Width, shape.Height, shape.Left, shape.Top); } if (!powerpointShapesSheetDictionary.ContainsKey(slide.SlideNumber)) { powerpointShapesSheetDictionary.Add(slide.SlideNumber, new List <PowerPoint.Shape>()); } powerpointShapesSheetDictionary[slide.SlideNumber].Add(shape); } else { /* * Log.Information("\tFound data for Slide No : {0} Shape Name : {1} Size(w x h) : {2} x {3} Position(left x top) : {4} , {5}", slide.SlideNumber, shape.Name, shape.Width, shape.Height, shape.Left, shape.Top); * if (shape.HasTextFrame == Office.Enums.MsoTriState.msoTrue && shape.TextFrame.HasText == Office.Enums.MsoTriState.msoTrue) * shape.TextFrame.TextRange.Replace("|*test*|", "123"); */ } } } if (datamissing) { Log.Error("ERROR - Match not found for shape/s in presentation : All shapes starting with \"#\"in the presentation should have matching shape in the excel."); thisForm.BeginInvoke((MethodInvoker)(() => { MessageBox.Show(thisForm, "All shapes starting with \"#\"in the presentation should have matching shape in the excel.\nSee logs for more information", "ERROR - Match not found for shape/s in presentation", MessageBoxButtons.OK, MessageBoxIcon.Error); })); goto closePresentation; } Log.Information("Start of Update"); foreach (int slideNo in powerpointShapesSheetDictionary.Keys) { foreach (var shape in powerpointShapesSheetDictionary[slideNo]) { excelShapesDictionary[shape.Name.ToLower()].Copy(); Thread.Sleep(100); PowerPoint.ShapeRange shapes = presentation.Slides[slideNo].Shapes.PasteSpecial(PowerPointEnums.PpPasteDataType.ppPasteJPG); string shape_name = shape.Name; float shape_top = shape.Top; float shape_left = shape.Left; float shape_width = shape.Width; float shape_height = shape.Height; shapes[1].Name = shape_name; shapes[1].Top = shape_top; shapes[1].Left = shape_left; shapes[1].ScaleWidth(shape.Width / shapes[1].Width, Office.Enums.MsoTriState.msoFalse); //shapes[1].Height = shape_height; shape.Delete(); Log.Information(" Updated {0} on slide {1}", shape_name, slideNo); } } Log.Information("End of Update"); String outputfile = Path.Combine(output_folder, new FileInfo(pptxSingle).Name); Log.Information("Saving a copy of updated Presentation to {0}", outputfile); presentation.SaveCopyAs(outputfile); closePresentation: Log.Information("Closing Presentation"); presentation.Close(); } Log.Information("Closing PowerPoint Applicaiton"); applicationPowerPoint.Quit(); applicationPowerPoint.Dispose(); closeWorkbook: Log.Information("Closing Excel File"); book.Close(); Log.Information("Closing Excel Applicaiton"); applicationExcel.Quit(); applicationExcel.Dispose(); Log.Information("Done"); thisForm.BeginInvoke((MethodInvoker)(() => { button1.Enabled = true; button2.Enabled = true; buttonFolder.Enabled = true; button3.Enabled = true; listView1.Enabled = true; listView2.Enabled = true; listView3.Enabled = true; MessageBox.Show(thisForm, "Task Completed. Check logs for more info", "Completed", MessageBoxButtons.OK, MessageBoxIcon.None); })); } catch (Exception e) { Log.Error("Fatal Error - " + e.ToString()); thisForm.BeginInvoke((MethodInvoker)(() => { MessageBox.Show(thisForm, e.ToString(), "Fatal Error", MessageBoxButtons.OK, MessageBoxIcon.Error); button1.Enabled = true; button2.Enabled = true; buttonFolder.Enabled = true; button3.Enabled = true; listView1.Enabled = true; listView2.Enabled = true; listView3.Enabled = true; })); try { presentation.Close(); } catch (Exception ee) { } try { applicationPowerPoint.Quit(); applicationPowerPoint.Dispose(); } catch (Exception ee) { } try { book.Close(); } catch (Exception ee) { } try { applicationExcel.Quit(); applicationExcel.Dispose(); } catch (Exception ee) { } } }
public static void Export <T>( this ICollectionView collectionView, Dictionary <string, Model.DataCellFormats> fieldsAndFormats, string reportTitle, string reportDescription, Func <T, string, string, object> getValueDelegate, Action <string> callBack = null) { if (getValueDelegate == null || fieldsAndFormats == null || reportTitle == null) { return; } const string commentFieldName = nameof(Model.Meter.Коментарий); bool hasCommentColumn = fieldsAndFormats.ContainsKey(commentFieldName); IEnumerable <T> collection = null; Application.Current.Dispatcher.Invoke(() => { collection = collectionView.Cast <T>(); }); int numberOfRows = collection.Count(); // +1 т.к. первый столбец номер по порядку int numberOfColumns = fieldsAndFormats.Count + 1; callBack?.Invoke("чтение данных"); object[,] output = BuildDataArray(); object[,] outputWithTwoRowPerRecord = null; if (hasCommentColumn) { outputWithTwoRowPerRecord = BuildDataArrayWithTwoRowPerRecord(); } callBack?.Invoke("поиск MS Excel"); string fileName = System.IO.Path.GetTempFileName(); fileName = System.IO.Path.ChangeExtension(fileName, "xlsx"); System.Globalization.CultureInfo defaultCulture = System.Threading.Thread.CurrentThread.CurrentCulture; // HACK: Workaround for Excel bug on machines which are set up in the English language, but not an English region. System.Globalization.CultureInfo enusCultureInfo = System.Globalization.CultureInfo.GetCultureInfo("en-US"); System.Threading.Thread.CurrentThread.CurrentCulture = enusCultureInfo; Excel.Application excelApplication = null; Excel.Workbook xlWorkbook = null; Excel.Worksheet xlWorksheet = null; Excel.Worksheet xlWorksheet2 = null; NetOffice.OfficeApi.Tools.Contribution.CommonUtils utils = null; Exception exception = null; Process(output, outputWithTwoRowPerRecord); OpenCreatedWorkBook(); // возвращает значение указанного поля записи object GetValueOfField(T item, string fieldName) { object value = string.Empty; if (string.IsNullOrWhiteSpace(fieldsAndFormats[fieldName].ExcelFormat) == false) { value = getValueDelegate(item, string.Empty, fieldName); } else { value = getValueDelegate(item, fieldsAndFormats[fieldName].ContentDisplayFormat, fieldName); } if (value is DateOnly dateOnlyValue) { value = dateOnlyValue.ToDateTime(TimeOnly.MinValue); } return(value); } // создание массива данных object[,] BuildDataArray() { // +1 т.к. первый столбец номер по порядку int countOfColumns = fieldsAndFormats.Count + 1; // +1 т.к. первая строка шапка object[,] output = new object[numberOfRows + 1, countOfColumns]; // for (int i = 0; i < numberOfRows + 1; i++) // output[i] = new object[numberOfColumns]; output[0, 0] = "№ п/п"; int ind = 1; foreach (KeyValuePair <string, Model.DataCellFormats> field in fieldsAndFormats) { output[0, ind++] = Utils.ConvertFromTitleCase(field.Key); } callBack?.Invoke("заполнение таблицы"); int rowIndex = 1; foreach (T item in collection) { output[rowIndex, 0] = rowIndex; ind = 1; // т.к. первый столбец номер по порядку foreach (string field in fieldsAndFormats.Keys) { output[rowIndex, ind++] = GetValueOfField(item, field); } rowIndex++; } return(output); } // создание массива данных со второй строкой в каждой записи где указан комментарий object[,] BuildDataArrayWithTwoRowPerRecord() { // -1 т.к. исключен столбец Комментарий // +1 т.к. первый столбец номер по порядку int countOfColumns = fieldsAndFormats.Count - 1 + 1; int numberOfRecords = collection.Count(); // +1 т.к. первая строка шапка object[,] output = new object[(2 * numberOfRecords) + 1, countOfColumns]; // for (int i = 0; i < numberOfRows + 1; i++) // output[i] = new object[numberOfColumns]; output[0, 0] = "№ п/п"; int ind = 1; foreach (KeyValuePair <string, Model.DataCellFormats> field in fieldsAndFormats) { if (field.Key == commentFieldName) { continue; } else { output[0, ind++] = Utils.ConvertFromTitleCase(field.Key); } } callBack?.Invoke("заполнение таблицы"); int rowIndex = 1; int recordIndex = 1; foreach (T item in collection) { output[rowIndex, 0] = recordIndex; ind = 1; // т.к. первый столбец номер по порядку foreach (string field in fieldsAndFormats.Keys) { if (field == commentFieldName) { continue; } output[rowIndex, ind++] = GetValueOfField(item, field); } string commentValue = GetValueOfField(item, commentFieldName).ToString().Trim().Replace('\n', '\t'); output[rowIndex + 1, 1] = commentValue; rowIndex += 2; recordIndex++; } return(output); } void ApplyDataFormatForSheet(Excel.Worksheet xlWorksheet, Excel.Range rangeToSetData, bool thisWorkSheetIsForPrint) { // -1 т.к. исключен столбец Комментарий // +1 т.к. первый столбец номер по порядку int countOfColumns = hasCommentColumn ? fieldsAndFormats.Count - 1 + 1 : fieldsAndFormats.Count + 1; callBack?.Invoke("установка формата данных"); int rowIndex = 2; // 1 - шапка таблицы int ind = 1; int recordIndex = 1; foreach (T item in collection) { rangeToSetData[rowIndex, 0 + 1].NumberFormat = "0"; ind = 1; // т.к. первый столбец номер по порядку foreach (string field in fieldsAndFormats.Keys) { if (thisWorkSheetIsForPrint && field == commentFieldName) { continue; } try { object value = string.Empty; if (string.IsNullOrWhiteSpace(fieldsAndFormats[field].ExcelFormat) == false) { string format = string.IsNullOrWhiteSpace(fieldsAndFormats[field].ExcelFormat) ? "General" : fieldsAndFormats[field].ExcelFormat; rangeToSetData[rowIndex, 0 + 1 + ind].NumberFormat = format; } else if (string.IsNullOrWhiteSpace(fieldsAndFormats[field].ContentDisplayFormat) == false) { rangeToSetData[rowIndex, 0 + 1 + ind].NumberFormat = "General"; } } catch (Exception e) { #if DEBUG App.ToDebug(e); #endif } finally { ind++; } } if (thisWorkSheetIsForPrint && hasCommentColumn) { // ячейка с номером по порядку Excel.Range rng = rangeToSetData[rowIndex, 1]; rng = rng.Resize(2, 1); rng.Merge(); rng.HorizontalAlignment = XlHAlign.xlHAlignLeft; rng.VerticalAlignment = XlVAlign.xlVAlignCenter; // ячейка с комментарием rng = rangeToSetData[rowIndex + 1, 2].Resize(1, countOfColumns - 1); rng.Merge(); rng.WrapText = true; rng.NumberFormat = "@"; rng = rangeToSetData[rowIndex, 1].Resize(2, countOfColumns); rng.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic); rng.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDot; rng.Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin; rng.Borders[XlBordersIndex.xlInsideHorizontal].Color = utils.Color.ToDouble(System.Drawing.Color.Gray); rng.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlDot; rng.Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin; rng.Borders[XlBordersIndex.xlInsideVertical].Color = utils.Color.ToDouble(System.Drawing.Color.Gray); if (recordIndex % 2 == 0) { rng = rangeToSetData[rowIndex, 1].Resize(2, countOfColumns); rng.Interior.Color = utils.Color.ToDouble(System.Drawing.Color.WhiteSmoke); } rowIndex += 2; } else { rowIndex++; } recordIndex++; } } void Process(object[,] outputData1, object[,] outputData2) { try { callBack?.Invoke("создание книги MS Excel"); excelApplication = new Excel.Application { DisplayAlerts = false, ScreenUpdating = false, }; utils = new NetOffice.OfficeApi.Tools.Contribution.CommonUtils(excelApplication); xlWorkbook = excelApplication.Workbooks.Add(); xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1]; Excel.Range all = xlWorksheet.Range("A1"); Excel.Range header = xlWorksheet.Range("A1"); header.WrapText = true; header.Resize(1, numberOfColumns).Merge(); using (Excel.Font font = header.Font) { font.Size = 14; font.Bold = true; } header.HorizontalAlignment = XlHAlign.xlHAlignCenter; header.VerticalAlignment = XlVAlign.xlVAlignCenter; double oneRowHeight = (double)header.RowHeight; int rowsCount = reportTitle.Split(new[] { Environment.NewLine }, StringSplitOptions.None).Length; header.RowHeight = oneRowHeight * rowsCount * 1.1; header.Value2 = reportTitle; Excel.Range description = xlWorksheet.Range("A2"); description.Resize(1, numberOfColumns).Merge(); description.WrapText = true; using (Excel.Font font = description.Font) { font.Size = 12; font.Italic = true; } description.HorizontalAlignment = XlHAlign.xlHAlignLeft; description.VerticalAlignment = XlVAlign.xlVAlignCenter; oneRowHeight = (double)description.RowHeight; rowsCount = reportDescription.Split(new[] { Environment.NewLine }, StringSplitOptions.None).Length; description.RowHeight = oneRowHeight * rowsCount * 1.1; description.Value2 = reportDescription; callBack?.Invoke("настройка книги MS Excel"); if (hasCommentColumn) { // создание копии листа xlWorksheet.Copy(xlWorksheet); xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1]; xlWorksheet2 = (Excel.Worksheet)xlWorkbook.Sheets[2]; xlWorksheet.Name = "Данные"; xlWorksheet2.Name = "Для печати"; Excel.Range data2 = xlWorksheet2.Range("A3").Resize((2 * numberOfRows) + 1, numberOfColumns - 1); data2.VerticalAlignment = VerticalAlignment.Center; data2.NumberFormat = "@"; data2.Value = outputData2; ApplyDataFormatForSheet(xlWorksheet2, data2, true); Excel.Range tableHeader = xlWorksheet2.Range("A3").Resize(1, numberOfColumns - 1); tableHeader.WrapText = true; using (Excel.Font font = tableHeader.Font) { font.Size = 12; font.Bold = true; } header.HorizontalAlignment = XlHAlign.xlHAlignCenter; header.VerticalAlignment = XlVAlign.xlVAlignCenter; tableHeader.Interior.Color = utils.Color.ToDouble(System.Drawing.Color.LightGray); tableHeader.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic); tableHeader.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDot; tableHeader.Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin; tableHeader.Borders[XlBordersIndex.xlInsideHorizontal].Color = utils.Color.ToDouble(System.Drawing.Color.Black); } Excel.Range data = xlWorksheet.Range("A3").Resize(numberOfRows + 1, numberOfColumns); data.NumberFormat = "@"; data.Value = outputData1; ApplyDataFormatForSheet(xlWorksheet, data, false); xlWorksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, data, Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "DataTable"; xlWorksheet.ListObjects["DataTable"].TableStyle = "TableStyleMedium6"; SetupWorkSheet(xlWorksheet, numberOfColumns); if (hasCommentColumn) { SetupWorkSheet(xlWorksheet2, numberOfColumns - 1); } callBack?.Invoke("сохранение книги MS Excel"); xlWorkbook.SaveAs(fileName); xlWorkbook.Close(false); callBack?.Invoke("завершение"); logger?.Info($"Export >> файл сформирован и сохранен: '{fileName}'"); } catch (Exception e) { exception = e; #if DEBUG App.ToDebug(e); #endif App.ShowError("Произошла ошибка:\n" + App.GetExceptionDetails(e)); return; } finally { System.Threading.Thread.CurrentThread.CurrentCulture = defaultCulture; excelApplication.Quit(); excelApplication.ScreenUpdating = true; excelApplication.DisplayAlerts = true; if (exception != null) { if (excelApplication.Workbooks.Any()) { foreach (Excel.Workbook workbook in excelApplication.Workbooks.Where(x => !x.IsDisposed)) { workbook.Close(false, System.Reflection.Missing.Value, Missing.Value); workbook.Dispose(); } } if (excelApplication.IsDisposed == false) { excelApplication.Quit(); excelApplication.Dispose(); } } } } void SetupWorkSheet(Excel.Worksheet xlWorksheet, int numberOfColumns) { foreach (int i in Enumerable.Range(1, numberOfColumns)) { xlWorksheet.Columns[i].AutoFit(); } Excel.PageSetup ps = xlWorksheet.PageSetup; ps.PaperSize = XlPaperSize.xlPaperA4; ps.Orientation = XlPageOrientation.xlLandscape; ps.Zoom = false; ps.FitToPagesWide = 1; ps.FitToPagesTall = false; ps.PrintTitleRows = "$3:$3"; ps.LeftMargin = excelApplication.CentimetersToPoints(1.0); ps.RightMargin = excelApplication.CentimetersToPoints(1.0); ps.TopMargin = excelApplication.CentimetersToPoints(2.0); ps.BottomMargin = excelApplication.CentimetersToPoints(1.0); ps.HeaderMargin = excelApplication.CentimetersToPoints(0.6); ps.FooterMargin = excelApplication.CentimetersToPoints(0.6); ps.CenterHorizontally = true; ps.RightHeader = DateTime.Now.ToString("D", defaultCulture); ps.CenterFooter = "Страница &P / &N"; ps.PrintArea = xlWorksheet.Range("A1").Resize(numberOfRows + 3, numberOfColumns).Address; } void OpenCreatedWorkBook() { try { callBack?.Invoke("открытие созданной книги MS Excel"); using System.Diagnostics.Process p = new System.Diagnostics.Process { StartInfo = new System.Diagnostics.ProcessStartInfo(fileName) { UseShellExecute = true, }, }; p.Start(); // System.Diagnostics.Process.Start(fileName); } catch (Exception e) { #if DEBUG App.ToDebug(e); #endif App.ShowError("Произошла ошибка при открытии файла:\n" + App.GetExceptionDetails(e)); } } }
private void bW1_DoWork(object sender, DoWorkEventArgs e) { xlApp.Visible = false; string[] files = e.Argument as string[]; var num = files.Count(); numberOfFiles.Text = num.ToString(); int percent = 0; rowcountFinal = 0; foreach (string fileEntrie in files) { Excel.Workbook wb = xlApp.Workbooks.Open(fileEntrie); Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1]; range = ws.UsedRange; //-2146827864 percent++; //checkboxes status check if (atIM.Checked && chkdOut.Checked) { range.AutoFilter(28, criteria1, Excel.Enums.XlAutoFilterOperator.xlOr, criteria2, true); } else if (atIM.Checked && !chkdOut.Checked) { range.AutoFilter(28, criteria1); } else if (!atIM.Checked && chkdOut.Checked) { range.AutoFilter(28, criteria2); } //--------------------------------------- var visrange = range.SpecialCells(Excel.Enums.XlCellType.xlCellTypeVisible, Type.Missing); var rowcount = visrange.Count / visrange.Columns.Count; if (eHead.Checked) { rowcount = rowcount - 1; } rowcountFinal = rowcountFinal + rowcount; string text = rowcountFinal.ToString(); if (numberOfLines.InvokeRequired) { SetTextCallback d = new SetTextCallback(FormUpdate); Invoke(d, new object[] { text }); } else { // It's on the same thread, no need for Invoke numberOfLines.Text = text; } if (bW1.CancellationPending) { e.Cancel = true; wb.Close(false); xlApp.Application.Quit(); xlApp.Application.Dispose(); break; } double i = ((double)percent / num) * 100; int currentPercentage = (int)i; bW1.ReportProgress(currentPercentage); wb.Close(false); } }