Anleitung: Daten nach Microsoft Excel exportieren

Für Berichte ist es oft notwendig, Daten automatisch nach Microsoft Office Excel zu exportieren. Sie könnten beispielsweise ein Exportskript für Zeitbuchungen erstellen, das automatisch einen Excel-Pivot-Bericht pro Benutzer erzeugt und diesen Bericht per E-Mail versendet.

time cockpit unterstützt zwei Wege, Daten nach Microsoft Office Excel zu exportieren:

Verwenden Sie die integrierte Exportfunktion von time cockpit.
Wenn Sie etwas sehr Spezielles benötigen (z. B. eine besondere Formatierung der erzeugten Microsoft-Office-Excel-Datei), können Sie die Microsoft-Office-Automatisierung in IronPython verwenden.

Integrierte Exportfunktion für Microsoft Office Excel verwenden

Das folgende Beispiel zeigt, wie Sie das Ergebnis einer TCQL-Abfrage in einem Skript mithilfe einer Vorlagedatei für den Export exportieren:

Hinweis

Die im folgenden Code gezeigte Methode Export erwartet entweder eine Vorlagedatei oder eine Listendefinition. Wenn Sie eine Vorlage angeben, wird die Listendefinition ignoriert. Wenn Sie keine Vorlagedatei angeben, müssen Sie eine Listendefinition angeben (die Vorlage wird dann automatisch auf Basis der Listendefinition erstellt).

clr.AddReference("TimeCockpit.Data.Export")
from TimeCockpit.Data.Export.Excel import *
from System.Threading import CancellationToken

resultingRows = Context.Select("From T In Timesheet Where T.APP_BeginTime > #2010-01-01# Select T")
sourceList = Context.Model.ModelEntityViews["APP_TimesheetList"].Configuration.Columns

XlsxExporter2.ExportToFile(
  "c:\\temp\\Template.xlsx",    # Path to template file  
  "c:\\temp\\export.xlsx",      # Target file name
  resultingRows,                # Rows that should be exported (has to be a list of entity objects)
  sourceList,                   # List definition that acts as the source for the export
  None,                         # Reserved; always pass None here
  CancellationToken(False))     # Cancellation token that could be used to cancel the export

Microsoft-Office-Automatisierung in einem Skript verwenden

Das folgende Beispiel zeigt, wie Sie eine Microsoft-Office-Excel-Datei mit Microsoft-Office-Automatisierung erzeugen. Das Skript lädt eine Vorlagearbeitsmappe, erstellt durch Kopieren eines Arbeitsblatts ein Blatt pro Benutzer und füllt das Blatt mit Zeitbuchungen. Feiertage und Wochenenden werden besonders formatiert.

import clr
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
clr.AddReference("System.Core")
from Microsoft.Office.Interop import *
from Microsoft.Office.Interop.Excel import *
from System import *
from System.Linq import Enumerable

# Helper function that sets the background of the entire row 
def ColorizeRow(cell):
  cell.EntireRow.Interior.ThemeColor = XlThemeColor.xlThemeColorDark1
  cell.EntireRow.Interior.TintAndShade = -0.249977111117893 

# Helper function that checks if a certain day if no working day 
def IsDayOff(day, holidays):
  return day.DayOfWeek == DayOfWeek.Saturday or day.DayOfWeek == DayOfWeek.Sunday \
    or Enumerable.Count(holidays, lambda h: h.LegalHolidayDate.Day == day.Day) > 0 

# Helper function that colorizes the entire day if the day is off 
def HandleRowColor(currentDayDate, cell, holidays):
  if IsDayOff(currentDayDate, holidays):
    ColorizeRow(cell)

# Creates an Excel worksheet for a specific user and month 
def AddWorksheet(user, year, month, workbook):
  # Get all timesheets for the user
  timesheets = Context.SelectWithParams({
    "Query": "From T In Timesheet Where T.UserDetail.Username = @Username And :Year(T.BeginTime) = @YearFilter And :Month(T.BeginTime) = @MonthFilter Order By T.BeginTime Select T",
    "@Username": user.Username,
    "@YearFilter": Decimal(year),
    "@MonthFilter": Decimal(month) })

  # Get holidays for the corresponding month
  holidays = Context.SelectWithParams({
    "Query": "From H In LegalHoliday Where :Year(H.LegalHolidayDate) = @YearFilter And :Month(H.LegalHolidayDate) = @MonthFilter Order By H.LegalHolidayDate Select H",
    "@YearFilter": Decimal(year),
    "@MonthFilter": Decimal(month) })

  # Add sheet for user
  workbook.Worksheets["Tabelle1"].Copy(Before = workbook.Worksheets[1])
  ws = workbook.Worksheets[1]
  ws.Name = user.Lastname + ", " + user.Firstname
  ws.Activate()

  rowIndex = 2
  totalWorkingHours = 0
  hoursPerDay = user.WeeklyHoursOfWork / 5
  prevDay = 0 

  # Loop over all days of the month 
  for currentDay in range(1, DateTime(year, month, 1).AddMonths(1).AddDays(-1).Day + 1):
    currentDayDate = DateTime(year, month, currentDay)
    dailyTimesheets = Enumerable.Where(timesheets, lambda t: t.BeginTime.Day == currentDay)

    if Enumerable.Count(dailyTimesheets) > 0:
      # There are timesheets for this day 
      for timesheet in dailyTimesheets:
        # Add row for timesheet
        HandleRowColor(currentDayDate, ws.Cells[rowIndex, 1], holidays)
        if prevDay != timesheet.BeginTime.Day:
          # Don't repeat day index for mulitple rows of the same day
          ws.Cells[rowIndex, 1].Value2 = timesheet.BeginTime.Day
        ws.Cells[rowIndex, 2].Value2 = timesheet.BeginTime
        ws.Cells[rowIndex, 3].Value2 = timesheet.EndTime
        ws.Cells[rowIndex, 4].Value2 = timesheet.DurationInHours / 24
        ws.Cells[rowIndex, 5].Value2 = timesheet.Description
        totalWorkingHours = totalWorkingHours + timesheet.DurationInHours
        prevDay = timesheet.BeginTime.Day
        rowIndex = rowIndex + 1 
    else:
      # There are no timesheets for this day
      HandleRowColor(currentDayDate, ws.Cells[rowIndex, 1], holidays)
      ws.Cells[rowIndex, 1].Value2 = currentDay 
      rowIndex = rowIndex + 1 

  # Add sums
  ws.Cells[rowIndex,2].Value2 = 'Gesamt:'
  ws.Cells[rowIndex,4].Value2 = (totalWorkingHours)/ 24
  rowIndex = rowIndex + 1 

# Open Excel (visible just for demo purposes)
ex = Excel.ApplicationClass()   
ex.Visible = True
ex.DisplayAlerts = False   
workbook = ex.Workbooks.Open(r"c:\temp\MonthlyReport.xlsx")

users = Context.Select("From U In UserDetail Order By U.Username Select U")
for user in users:
  AddWorksheet( user, 2017, 5, workbook )

print "Done!"

Excel-Export mit einer Aktion in die Benutzeroberfläche integrieren

Sie können ein Exportskript in eine Aktion umwandeln, um in der Benutzeroberfläche von time cockpit eine Schaltfläche anzubieten, die die Daten der ausgewählten Zeile einer Liste exportiert. Das folgende Beispiel zeigt, wie das oben gezeigte Beispiel als Aktion zur Entität APP_UserDetail hinzugefügt werden kann:

Hinweis

Beachten Sie, dass der Code der Aktion nur eine einzige def-Anweisung auf oberster Ebene enthalten darf! Wenn Sie Hilfsfunktionen benötigen, müssen Sie diese als verschachtelte Funktionen innerhalb der einen Funktion auf oberster Ebene definieren (siehe Beispielcode unten).

import clr
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
clr.AddReference("System.Core")
from Microsoft.Office.Interop import *
from Microsoft.Office.Interop.Excel import *
from System import *
from System.Linq import Enumerable

def ExportTimesheetProtocolToExcel(actionContext):
  # Helper function that sets the background of the entire row 
  def ColorizeRow(cell):
    cell.EntireRow.Interior.ThemeColor = XlThemeColor.xlThemeColorDark1
    cell.EntireRow.Interior.TintAndShade = -0.249977111117893 

  # Helper function that checks if a certain day if no working day 
  def IsDayOff(day, holidays):
    return day.DayOfWeek == DayOfWeek.Saturday or day.DayOfWeek == DayOfWeek.Sunday \
      or Enumerable.Count(holidays, lambda h: h.LegalHolidayDate.Day == day.Day) > 0 

  # Helper function that colorizes the entire day if the day is off 
  def HandleRowColor(currentDayDate, cell, holidays):
    if IsDayOff(currentDayDate, holidays):
      ColorizeRow(cell)

  # Creates an Excel worksheet for a specific user and month 
  def AddWorksheet(user, year, month, workbook):
    # Get all timesheets for the user
    timesheets = Context.SelectWithParams({
      "Query": "From T In Timesheet Where T.UserDetail.Username = @Username And :Year(T.BeginTime) = @YearFilter And :Month(T.BeginTime) = @MonthFilter Order By T.BeginTime Select T",
      "@Username": user.Username,
      "@YearFilter": Decimal(year),
      "@MonthFilter": Decimal(month) })

    # Get holidays for the corresponding month
    holidays = Context.SelectWithParams({
      "Query": "From H In LegalHoliday Where :Year(H.LegalHolidayDate) = @YearFilter And :Month(H.LegalHolidayDate) = @MonthFilter Order By H.LegalHolidayDate Select H",
      "@YearFilter": Decimal(year),
      "@MonthFilter": Decimal(month) })

    # Add sheet for user
    workbook.Worksheets["Tabelle1"].Copy(Before = workbook.Worksheets[1])
    ws = workbook.Worksheets[1]
    ws.Name = user.Lastname + ", " + user.Firstname
    ws.Activate()

    rowIndex = 2
    totalWorkingHours = 0
    hoursPerDay = user.WeeklyHoursOfWork / 5
    prevDay = 0 

    # Loop over all days of the month 
    for currentDay in range(1, DateTime(year, month, 1).AddMonths(1).AddDays(-1).Day + 1):
      currentDayDate = DateTime(year, month, currentDay)
      dailyTimesheets = Enumerable.Where(timesheets, lambda t: t.BeginTime.Day == currentDay)

      if Enumerable.Count(dailyTimesheets) > 0:
        # There are timesheets for this day 
        for timesheet in dailyTimesheets:
          # Add row for timesheet
          HandleRowColor(currentDayDate, ws.Cells[rowIndex, 1], holidays)
          if prevDay != timesheet.BeginTime.Day:
            # Don't repeat day index for mulitple rows of the same day
            ws.Cells[rowIndex, 1].Value2 = timesheet.BeginTime.Day
          ws.Cells[rowIndex, 2].Value2 = timesheet.BeginTime
          ws.Cells[rowIndex, 3].Value2 = timesheet.EndTime
          ws.Cells[rowIndex, 4].Value2 = timesheet.DurationInHours / 24
          ws.Cells[rowIndex, 5].Value2 = timesheet.Description
          totalWorkingHours = totalWorkingHours + timesheet.DurationInHours
          prevDay = timesheet.BeginTime.Day
          rowIndex = rowIndex + 1 
      else:
        # There are no timesheets for this day
        HandleRowColor(currentDayDate, ws.Cells[rowIndex, 1], holidays)
        ws.Cells[rowIndex, 1].Value2 = currentDay 
        rowIndex = rowIndex + 1 

    # Add sums
    ws.Cells[rowIndex,2].Value2 = 'Gesamt:'
    ws.Cells[rowIndex,4].Value2 = (totalWorkingHours)/ 24
    rowIndex = rowIndex + 1 

  # make sure that input set contains at least one object 
  if (Enumerable.Count[EntityObject](actionContext.InputSet) > 0):
    # Open Excel (visible just for demo purposes)
    ex = Excel.ApplicationClass()   
    ex.Visible = True
    ex.DisplayAlerts = False   
    workbook = ex.Workbooks.Open(r"C:\temp\MonthlyReport.xlsx")

    for user in actionContext.InputSet:
      AddWorksheet( user, 2017, 5, workbook )

Table of Contents

Anleitung: Daten nach Microsoft Excel exportieren

Integrierte Exportfunktion für Microsoft Office Excel verwenden

Hinweis

Microsoft-Office-Automatisierung in einem Skript verwenden

Excel-Export mit einer Aktion in die Benutzeroberfläche integrieren

Hinweis