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)); }