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セル選択処理
/// 引数１：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);
}
}
