private void UpdateWorksheetPreview(WorkbookInfo workbookInfo)
{
foreach (var si in workbookInfo)
{
si.UpdatePreviews();
}
}
//Glue method
public async Task GetPagePreview(WorkbookInfo workbookInfo)
{
await StartExcelPreview();
await GetWorksheetPreview(ExcelPreview, workbookInfo);
UpdateWorksheetPreview(workbookInfo);
}
public WorkbookInfo getWorkbookInfo(Excel.Workbook workbook)
{
if (!_workbooks.ContainsKey(workbook))
{
_workbooks[workbook] = new WorkbookInfo(workbook);
}
return _workbooks[workbook];
}
public WorkbookInfo getWorkbookInfo(Excel.Workbook workbook)
{
if (!_workbooks.ContainsKey(workbook))
{
_workbooks[workbook] = new WorkbookInfo(workbook);
}
return(_workbooks[workbook]);
}
public void TryGetWorkbookInfo()
{
var excel = Excel;
Assert.IsTrue(WorkbookInfo.TryGetWorkbookInfo(excel, opt, out var workbookInfo));
Assert.AreEqual(1, workbookInfo.ConstSheets.Length);
Assert.AreEqual(1, workbookInfo.ClassSheets.Length);
Assert.AreEqual(1, workbookInfo.EnumSheets.Length);
}
//Genuine method
private async Task GetPageCountAndSize(Application excel, WorkbookInfo workbookInfo)
{
var result = await workbookInfo.GetWorksheetPageCountAndSize(excel, CancellationToken, UpdateStatus);
if (result)
{
workbookInfo.IsWorksheetPageCountSizeObtained = true;
}
}
private async Task GetWorksheetPreview(Application excel, WorkbookInfo workbookInfo)
{
if (workbookInfo.IsWorksheetPreviewObtained)
{
return;
}
var result = await workbookInfo.GetWorkSheetPreview(excel, CancellationToken, UpdateStatus);
if (result)
{
workbookInfo.IsWorksheetPreviewObtained = true;
}
}
//Get previews of worksheets
public static async Task <bool> GetWorkSheetPreview(this WorkbookInfo workbookInfo, Application excel, CancellationToken cancellationToken, Action <string> updateStatus = null)
{
return(await OperateWorkbook(excel, workbookInfo, (wb) =>
{
foreach (Worksheet ws in wb.Worksheets)
{
//Ignore hidden worksheet
if (ws.Visible != XlSheetVisibility.xlSheetVisible)
{
continue;
}
if (workbookInfo.WorksheetInfos.Any(x => x.SheetName == ws.Name))
{
//Find target worksheet info
var target = workbookInfo.WorksheetInfos.First(x => x.SheetName == ws.Name);
target.PreviewsRaw.Clear();
//If page count is zero, ignore
if (target.Count == 0)
{
if (ws.PageSetup.Pages.Count == 0)
{
continue;
}
}
//Export as pdf
var pdfFilePath = CreatePdfPath();
ws.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, pdfFilePath);
//Add bitmap into preview raw data
foreach (var bmp in PdfToBitmapMethods.ToBitmaps(pdfFilePath))
{
target.PreviewsRaw.Add(bmp);
}
//Delete temp pdf file
if (File.Exists(pdfFilePath))
{
File.Delete(pdfFilePath);
}
}
cancellationToken.ThrowIfCancellationRequested();
}
return true;
}, updateStatus));
}
private async Task AddTagsToWorkbook(WorkbookInfo workbookInfo, IList <string> tagsToAdd, TableauServerRestApi restApi)
{
try
{
await Retry.DoWithRetries <RestApiException, IList <TagInfo> >(
nameof(WorkbookPublisher),
_logger,
async() => await restApi.AddTagsToWorkbook(workbookInfo.Id, tagsToAdd));
}
catch (RestApiException ex)
{
_logger.LogWarning(
"Failed to add tags to workbook '{workbookName}' ({workbookId}). Exception message: {exceptionAddingTags}",
workbookInfo.Name ?? "(null)",
workbookInfo.Id ?? "(null)",
ex.Message);
}
}
//Calc worksheet page size and count
internal static async Task <bool> GetWorksheetPageCountAndSize(this WorkbookInfo workbookInfo, Application excel, CancellationToken cancellationToken, Action <string> updateStatus = null)
{
return(await OperateWorkbook(excel, workbookInfo, (wb) =>
{
foreach (Worksheet ws in wb.Worksheets)
{
if (workbookInfo.WorksheetInfos.Any(x => x.SheetName == ws.Name))
{
var target = workbookInfo.WorksheetInfos.First(x => x.SheetName == ws.Name);
target.Count = ws.PageSetup.Pages.Count;
target.PaperSize = ws.PageSetup.PaperSize;
target.Orientation = ws.PageSetup.Orientation;
target.NotifyPropertyChanged(nameof(target.Count));
target.NotifyPropertyChanged(nameof(target.PaperSize));
}
cancellationToken.ThrowIfCancellationRequested();
}
wb?.Close(SaveChanges: false);
return true;
}, updateStatus));
}
public static WorkbookPublishResult Success(string originalWorkbookName, WorkbookInfo workbookInfo)
{
return(new WorkbookPublishResult(WorkbookPublishState.Success, originalWorkbookName, workbookInfo.Id, workbookInfo.Name));
}
//Get worksheet names of a workbook
public static IEnumerable <string> GetWorksheetNames(this WorkbookInfo workbookInfo)
{
return(GetWorksheetNames(workbookInfo.FilePath));
}
//HOF shell method
//Used by GetWorksheetPageCountAndSize, GetWorkSheetPreview
private static async Task <bool> OperateWorkbook(
Application excel,
WorkbookInfo workbookInfo,
Func <Workbook, bool> func,
Action <string> updateStatus = null)
{
var result = false;
if (excel == null)
{
return(result);
}
await Task.Run(async() =>
{
Workbook wb = null;
try
{
wb = excel.Workbooks.Open(Filename: workbookInfo.FilePath, ReadOnly: true);
var retry = 0;
START:
try
{
if (wb == null)
{
return;
}
try
{
//May throw operation cancel exception
result = func(wb);
}
catch (COMException e)
{
//RPC_E_SERVERCALL_RETRYLATER
if (e.ErrorCode == -2147417846)
{
var rand = new Random();
//Wait for random time and retry
await Task.Delay(rand.Next(100));
retry++;
//Try no more than 5 times
if (retry < 5)
{
goto START;
}
}
}
}
catch (OperationCanceledException e)
{
wb?.Close(SaveChanges: false);
updateStatus?.Invoke(e.Message);
await Task.Delay(2000);
updateStatus?.Invoke("");
}
}
catch (Exception e)
{
wb?.Close(SaveChanges: false);
updateStatus?.Invoke(e.Message);
//Wait for 5s and clear message.
await Task.Delay(5000);
updateStatus?.Invoke("");
}
});
return(result);
}
