private WorksheetCollection GetWorksheets(IDynamic workBook)
{
List <Worksheet> result = new List <Worksheet>();
IDynamic nativeWorksheets = workBook.Property("Worksheets").Get();
int sheetCount = nativeWorksheets.Property("Count").Get <int>();
for (int i = 1; (i - 1) < sheetCount; i++)
{
Worksheet newSheet = new Worksheet();
IDynamic nativeworkSheet = nativeWorksheets.Index(i).Get();
newSheet.Name = nativeworkSheet.Property("Name").Get <string>();
newSheet.Rows = GetRows(nativeworkSheet);
result.Add(newSheet);
}
return(new WorksheetCollection(result));
}
protected override void EndProcessing()
{
base.EndProcessing();
if (tableStyle == string.Empty)
{
tableStyle = "TableStyleMedium9";
}
WriteVerbose("Setting table styles");
WriteProgress(new ProgressRecord(10, "Out-Excel", "Formatting tables.."));
IDynamic dynamicWorksheets = workBook.Property("Worksheets").Get();
int tableCount = dynamicWorksheets.Property("Count").Get <int>();
for (int i = 1; (i - 1) < tableCount; i++)
{
//worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,worksheet.Range("A1",worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell)), Type.Missing, XlYesNoGuess.xlYes,"Table1",tableStyle);
IDynamic worksheet = dynamicWorksheets.Index(i).Get();
IDynamic endCell = worksheet.Property("Cells").Get().Property("SpecialCells").PropertyParam(11).Get();
IDynamic range = worksheet.Property("Range").PropertyParam("A1").PropertyParam(GetExcelCellName(endCell.Property("Column").Get <int>(), endCell.Property("Row").Get <int>())).Get();
worksheet.Property("Columns").Get().Method("AutoFit").Invoke();
worksheet.Property("ListObjects").Get().Method("Add").AddParameter(1).AddParameter(range.InstanceObject).AddMissingParameters(1).AddParameter(1).AddParameter("Table1").AddParameter(tableStyle).Invoke();
}
WriteProgress(new ProgressRecord(10, "Out-Excel", "Saving document.."));
int format = 0;
switch (parFormat.ToLower())
{
case "excel": format = 51; break;
case "excel97": format = 56; break;
case "pdf": format = 57; break;
case "xps": format = 58; break;
}
WriteVerbose(string.Format("Saving document in {0}", parFormat));
try
{
workBook.Method("SaveAs").AddRefParameter(parPath).AddRefParameter(format).Invoke();
}
catch (Exception ex)
{
WriteVerbose("Save failed, see error log for details");
if (parNoExit)
{
WriteVerbose("-NoExit specified, making word visible if not already");
msExcel.Property("Visible").Set(true);
}
else
{
WriteVerbose("-NoExit not specified, quitting Excel");
workBook.Method("Close").AddParameter(0).Invoke();
msExcel.Method("Quit").Invoke();
}
ThrowTerminatingError(new ErrorRecord(ex, "300", ErrorCategory.FromStdErr, parPath));
}
if (parNoExit)
{
WriteVerbose("-NoExit specified, making Excel visible if not already");
msExcel.Property("Visible").Set(true);
}
else
{
WriteVerbose("-NoExit not specified, quitting word");
workBook.Method("Close").AddParameter(0).Invoke();
msExcel.Method("Quit").Invoke();
}
WriteVerbose("Reverting culture");
Thread.CurrentThread.CurrentCulture = currentCulture;
WriteProgress(new ProgressRecord(10, "Out-Excel", "Done"));
WriteVerbose("Done");
WriteObject(new FileInfo(parPath));
}
protected override void EndProcessing()
{
base.EndProcessing();
WriteProgress(new ProgressRecord(10, "Out-Word", "Creating footers.."));
if (parFooter != string.Empty)
{
WriteVerbose("Adding footer");
IDynamic newPara = wordDoc.Property("Paragraphs").Get().Method("Add").Invoke();
try
{
newPara.Property("Style").Set(wordFooterStyle);
}
catch
{
WriteWarning(string.Format("The style specified for document footer ('{0}') was not found", wordFooterStyle));
}
newPara.Property("Range").Get().Property("Text").Set("");
newPara.Property("Range").Get().Method("InsertParagraphAfter").Invoke();
currentRange = newPara.Property("Range").Get();
try
{
newPara.Property("Style").Set(wordFooterStyle);
}
catch
{
WriteWarning(string.Format("The style specified for document footer ('{0}') was not found", wordFooterStyle));
}
newPara.Property("Range").Get().Property("Text").Set(parFooter);
newPara.Property("Range").Get().Method("InsertParagraphAfter").Invoke();
currentRange = newPara.Property("Range").Get();
}
WriteVerbose("Setting table styles");
WriteProgress(new ProgressRecord(10, "Out-Word", "Formatting tables.."));
IDynamic tables = wordDoc.Property("Tables").Get();
int tableCount = tables.Property("Count").Get <int>();
for (int i = 1; (i - 1) < tableCount; i++)
{
IDynamic table = tables.Index(i).Get();
try
{
table.Property("Style").Set(wordTableStyle);
}
catch
{
WriteWarning(string.Format("The style specified for tables ('{0}') was not found", wordTableStyle));
}
table.Property("Columns").Get().Method("AutoFit").Invoke();
}
WriteProgress(new ProgressRecord(10, "Out-Word", "Saving document.."));
int format = 0;
switch (parFormat.ToLower())
{
case "word": format = 12; break;
case "word97": format = 0; break;
case "pdf": format = 17; break;
case "xps": format = 18; break;
case "odf": format = 23; break;
case "rtf": format = 6; break;
}
WriteVerbose(string.Format("Saving document in {0}", parFormat));
try
{
wordDoc.Method("SaveAs").AddRefParameter(parPath).AddRefParameter(format).Invoke();
}
catch (Exception ex)
{
WriteVerbose("Save failed, see error log for details");
if (parNoExit)
{
WriteVerbose("-NoExit specified, making word visible if not already");
msWord.Property("Visible").Set(true);
}
else
{
WriteVerbose("-NoExit not specified, quitting word");
wordDoc.Method("Close").AddParameter(0).Invoke();
msWord.Method("Quit").AddParameter(0).Invoke();
}
ThrowTerminatingError(new ErrorRecord(ex, "300", ErrorCategory.FromStdErr, parPath));
}
if (parNoExit)
{
WriteVerbose("-NoExit specified, making word visible if not already");
msWord.Property("Visible").Set(true);
}
else
{
WriteVerbose("-NoExit not specified, quitting word");
wordDoc.Method("Close").AddParameter(0).Invoke();
msWord.Method("Quit").AddParameter(0).Invoke();
}
WriteProgress(new ProgressRecord(10, "Out-Word", "Done"));
WriteVerbose("Done");
WriteObject(new FileInfo(parPath));
}