public void CloseBook(Excel.Workbook book, bool save)
{
Log.Info(LoggerConstants.ENTER);
if (book == null)
{
Log.Warn(LoggerConstants.BAD_VALIDATION);
Log.Info(LoggerConstants.EXIT);
return;
}
try
{
Log.Info("Book name is `" + book.Name + "`");
if (save)
book.Save();
book.Close();
}
catch (COMException e)
{
Log.Warn("COMException with closing `" + book.Name + "`", e);
}
catch (Exception e)
{
Log.Warn("Exception with closing `" + book.Name + "`", e);
}
Log.Info(LoggerConstants.EXIT);
}
public static void CloseWorkingWorkbook(Excel.Workbook workbook)
{
if (workbook != null)
{
if (!workbook.Saved)
workbook.Save();
workbook.Close();
ExcelUtilies.ReleaseComObject(workbook);
}
}
/// <summary>
/// Handels saving CSV files differently by programatically saving under special conditions and canceling the origial save.
///
/// Does nothing is a SaveAs is being performed. Otherwise, checks the file types for any of Excels CSV file formats, and
/// if found performs a programatic save, marks the file as saved, and cancels the original save so as to not save twice.
/// </summary>
/// <param name="Wb"></param>
/// <param name="SaveAsUi"></param>
/// <param name="Cancel"></param>
private void Application_WorkbookBeforeSave(Excel.Workbook Wb, bool SaveAsUi, ref bool Cancel)
{
// Check if a Save As is being performed
if (!SaveAsUi)
{
if ((new[] { XlFileFormat.xlCSV, XlFileFormat.xlCSVMac, XlFileFormat.xlCSVMSDOS, XlFileFormat.xlCSVWindows }).Contains(Wb.FileFormat))
{
// Temporarily un-register this event handler to prevent recursion
this.Application.WorkbookBeforeSave -= Application_WorkbookBeforeSave;
Wb.Save();
// Re-register this event handler
this.Application.WorkbookBeforeSave += Application_WorkbookBeforeSave;
// Mark the file as being saved
Wb.Saved = true;
// Cancel the orginal save
Cancel = true;
}
}
}
private static async Task saveExcelAsync(Excel.Workbook sampleWorkBook)
{
Task t = new Task
(
() =>
{
sampleWorkBook.Save();
}
);
t.Start();
await t;
}
/// <summary>
/// 计算长期稳定性
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="range"></param>
/// <param name="success"></param>
public void StablizeOneKV(MSExcel._Workbook _excelDoc, int sheetIndex, KVCriterion crit, int row, int col, out string range, out bool success)
{
try
{
ExcelPosition pos1 = new ExcelPosition(row - 1, col - 2);
ExcelPosition pos2 = new ExcelPosition(row, col - 2);
ExcelPosition pos = new ExcelPosition(row, col);
MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, pos);
if (HadNumber(_excelDoc, sheetIndex, pos2) && HadNumber(_excelDoc, sheetIndex, pos1))
{
if (crit.Index > 2)
{
_excelRge.Formula = "=(" + pos2.PositionString + "-" + pos1.PositionString + ")/" + pos1.PositionString;
}
else
{
_excelRge.Formula = "=" + pos1.PositionString + "-" + pos2.PositionString;
}
_excelRge.NumberFormatLocal = "0.0%";
_excelDoc.Save();
}
if (_excelRge != null && _excelRge.Value2 != null)
{
range = _excelRge.Value2.ToString();
}
else
{
range = GetText(_excelDoc, sheetIndex, pos);
}
success = true;
}
catch (System.Exception ex)
{
success = false;
range = null;
Log.LogHelper.AddLog(@"异常45", ex.Message, true);
Log.LogHelper.AddLog(@"异常46", " " + ex.TargetSite.ToString(), true);
}
}
/// <summary>
/// ワークブックの比較
/// </summary>
/// <remarks>
/// ・シート名は一致していなければならない
/// </remarks>
/// <param name="book1"></param>
/// <param name="book2"></param>
public void CompareWith(Excel.Workbook book1, Excel.Workbook book2)
{
//シート間の対応
List<Excel.Worksheet> sheetList1 = new List<Excel.Worksheet>();
List<Excel.Worksheet> sheetList2 = new List<Excel.Worksheet>();
foreach (Excel.Worksheet sheet1 in book1.Sheets) {
foreach (Excel.Worksheet sheet2 in book2.Sheets) {
if (sheet1.Name == sheet2.Name) {
sheetList1.Add(sheet1);
sheetList2.Add(sheet2);
break;
}
}
}
//セル範囲取得
foreach (Excel.Worksheet sheet1 in sheetList1) {
foreach (Excel.Worksheet sheet2 in sheetList2) {
OndForExcel.GetDiffStringOND(sheet1.UsedRange, sheet2.UsedRange);
}
}
book1.Save();
book2.Save();
}
/// <summary>
/// Workbookを保存。
/// </summary>
/// <param name="book">Workbook</param>
public void Save(Excel.Workbook book)
{
book.Save();
}
public static void fileSave(Excel.Workbook Wb) { Wb.Save(); }
