Using Excel as a Rainmeter Skin

[Yincognito]

Ok. This is what I'll do:
- change the Excel file to have multiple tabs, one for each working day of week (Monday to Friday), starting with the 2nd tab
- have the 1st tab display a "Summary" that "aggregates" all the following 5 days into one long (from a vertical point of view) list
- since as I pointed out, both the CSV and the TXT don't support multiple tabs, they will take just the 1st tab, which now just happens to be all you need, and this is where the skin will "navigate"
- when editing the Excel file, you will NOT edit the 1st tab (unless you know what you're doing), but edit instead the tab corresponding to the desired day of week (changes will be reflected in the 1st tab instantly in Excel, and of course, after saving, in the skin)

As for the rest, the time thing and such, I will do it no problem, just can't guarantee if it's going to be today or tomorrow.

So, this is what the skin looks like (added some comments - i.e. lines starting with a ; - you'll figure out what they mean, I hope):

Code: Select all

[Variables]
FilePath=F:\Wireless\
FileName=Book1
FileExt=csv
FileDate=13243364371
Weekday=1
TimeSlot=33
EvenColColor=255,128,64,255
OddColColor=64,255,128,255
BackColor=0,0,0,64
HighlightStrokeColor=255,255,0,255
HighlightFillColor=255,0,0,32
HighlightStrokeWidth=2
HighlightRoundness=6
FontFace=Consolas
FontSize=9
Padding=5
TrailingSpaces="             "
TrailingSpacesCount=13
DayRows=49
RowIndex=0
Interval=((24*60*60)/(#DayRows#-1))

[Rainmeter]
Update=1000
DynamicWindowSize=1
AccurateText=1
BackgroundMode=2
SolidColor=#BackColor#

---Measures FileView---

[MeasureFolder]
Measure=Plugin
Plugin=FileView
Path="#FilePath#"
ShowDotDot=0
ShowFolder=0
WildcardSearch="#FileName#.#FileExt#"
Count=1
UpdateDivider=10
OnUpdateAction=[!CommandMeasure MeasureFolder "Update"]

[MeasureFileDate]
Measure=Plugin
Plugin=FileView
Path=[MeasureFolder]
Type=FileDate
IfCondition=([MeasureFileDate:]>#FileDate#)
IfTrueAction=[!SetVariable FileDate "[MeasureFileDate:]"][!WriteKeyValue Variables FileDate "[MeasureFileDate:]"][!CommandMeasure MeasureFile "Update"]
DynamicVariables=1

---Measures WebParser---

[MeasureFile]
Measure=WebParser
URL="file://#FilePath##FileName#.#FileExt#"
RegExp=(?siU)^(.*)$
FinishAction=[!UpdateMeasureGroup "TimeGroup"][!SetVariable RowIndex (#DayRows#*([MeasureWeekday]-1))][!SetVariable Weekday [MeasureWeekday]][!WriteKeyValue Variables Weekday "[MeasureWeekday]"][!SetVariable TimeSlot [MeasureTimeSlot]][!WriteKeyValue Variables TimeSlot "[MeasureTimeSlot]"][!UpdateMeasureGroup "FileGroup"][!UpdateMeter *][!Redraw]
RegExpSubstitute=1
Substitute="(\R)":"[\x0009]\1","[\t,]":"#TrailingSpaces#[\x0009]","(.{#TrailingSpacesCount#}).*?(\t)":"\1\2"
DynamicVariables=1

---Measures Time---

[MeasureWeekday]
Group=TimeGroup
Measure=Time
Format=%u

[MeasureTime]
Group=TimeGroup
Measure=Time

[MeasureTimeSlot]
Group=TimeGroup
Measure=Calc
Formula=(Trunc((MeasureTime%(24*60*60))/#Interval#)+1)
DynamicVariables=1

[MeasureTimeString]
Group=TimeGroup
Measure=String
String="Weekday: [MeasureWeekday], TimeSlot: [MeasureTimeSlot]"
IfMatch=^Weekday: #Weekday#, TimeSlot: #TimeSlot#$
IfNotMatchAction=[!SetVariable RowIndex (#DayRows#*([MeasureWeekday]-1))][!SetVariable Weekday [MeasureWeekday]][!WriteKeyValue Variables Weekday "[MeasureWeekday]"][!SetVariable TimeSlot [MeasureTimeSlot]][!WriteKeyValue Variables TimeSlot "[MeasureTimeSlot]"][!UpdateMeasureGroup "FileGroup"][!UpdateMeter *][!Redraw]
DynamicVariables=1

---Measures String---

[MeasureOutput]
Group=FileGroup
Measure=String
String="[MeasureFile]"
UpdateDivider=-1
RegExpSubstitute=1
Substitute="(?siU)^(?:\N*(?:\R|$)){#RowIndex#}((?:\N*(?:\R|$)){#DayRows#}).*$":"\1","#TrailingSpaces#\t\R$":"","(?:^\\\d+|\\\d+$)":""
DynamicVariables=1

---Meters---

[MeterOutput]
Meter=String
MeasureName=MeasureOutput
FontFace=#FontFace#
FontSize=#FontSize#
FontWeight=400
SolidColor=#BackColor#
StringEffect=Shadow
FontEffectColor=0,0,0,255
Padding=#Padding#,#Padding#,#Padding#,#Padding#
AntiAlias=1
FontColor=255,255,255,255
InlineSetting=Color | 0,112,192,255
InlinePattern=(?U)((?:Lunch|Dinner).*(?:\t|\n))
InlineSetting2=Color | 255,0,0,255
InlinePattern2=(?U)(Homework.*(?:\t|\n))
InlineSetting3=Color | 237,125,49,255
InlinePattern3=(?U)(Drive.*(?:\t|\n))
InlineSetting4=Color | 0,176,80,255
InlinePattern4=(?U)((?:Workout|Running|Coding).*(?:\t|\n))
; Uncomment the 4 lines below to add a bold and a shadow effect to the table headers, i.e. the 1st line
;InlineSetting5=Weight | 700
;InlinePattern5=(?U)^(?:(?:^|\n).*?){0}((?:^|\n).*?)
;InlineSetting6=Shadow | 1 | 1 | 1 | 255,255,255,255
;InlinePattern6=(?U)^(?:(?:^|\n).*?){0}((?:^|\n).*?)
Text="%1"
LeftMouseUpAction=["#FilePath##FileName#.xlsm"]
MiddleMouseUpAction=[!UpdateMeasureGroup "FileGroup"][!UpdateMeter *][!Redraw]
; Uncomment the 2 lines below to scroll between weekdays (middle click resets back the weekday to the correct value)
MouseScrollUpAction=[!SetVariable RowIndex (#DayRows#*7+#RowIndex#-#DayRows#)%(#DayRows#*7)][!UpdateMeasureGroup "FileGroup"][!UpdateMeter *][!Redraw]
MouseScrollDownAction=[!SetVariable RowIndex (#DayRows#*7+#RowIndex#+#DayRows#)%(#DayRows#*7)][!UpdateMeasureGroup "FileGroup"][!UpdateMeter *][!Redraw]
; Uncomment the 2 lines below to scroll between time intervals (the IfNotMatchAction line in [MeasureTimeString] must be commented as well to work)
;MouseScrollUpAction=[!SetVariable TimeSlot ((#DayRows#+(#TimeSlot#-1)-1)%#DayRows#+1)][!UpdateMeasureGroup "FileGroup"][!UpdateMeter *][!Redraw]
;MouseScrollDownAction=[!SetVariable TimeSlot ((#DayRows#+(#TimeSlot#-1)+1)%#DayRows#+1)][!UpdateMeasureGroup "FileGroup"][!UpdateMeter *][!Redraw]
; Move [MeterHiglight] before [MeterOutput] and uncomment the 2 lines below to draw the highlight first and keep the original color of highlighted text
;UpdateDivider=-1
;OnUpdateAction=[!UpdateMeter MeterHighlight][!Redraw]
DynamicVariables=1

[MeterHighlight]
Meter=Shape
Shape=Rectangle ([MeterOutput:X]+#HighlightStrokeWidth#/2),(([MeterOutput:Y]+#Padding#+(([MeterOutput:H]-#Padding#*2)/#DayRows#)*#TimeSlot#)),([MeterOutput:W]-#HighlightStrokeWidth#/2),(([MeterOutput:H]-#Padding#*2)/#DayRows#),#HighlightRoundness# | StrokeWidth #HighlightStrokeWidth# | Stroke Color #HighlightStrokeColor# | Fill Color #HighlightFillColor#
DynamicVariables=1

Added some additional code in the macro, to automatically build the Summary worksheet on workbook's save (this does NOT have error handling, by the way):

Code: Select all

Function LastRow(SH As Worksheet) As Long
    On Error Resume Next
    LastRow = SH.Cells.Find(What:="*", After:=SH.Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    On Error GoTo 0
End Function

Function LastCol(SH As Worksheet) As Long
    On Error Resume Next
    LastCol = SH.Cells.Find(What:="*", After:=SH.Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
    On Error GoTo 0
End Function

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim DestSH As Worksheet, SH As Worksheet, CopyRng As Range, SourceWB As Workbook, DestWB As Workbook
  Dim PreviouslyActiveSHName As String
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    PreviouslyActiveSHName = ActiveWorkbook.ActiveSheet.Name
    ActiveWorkbook.Worksheets("- Summary -").Delete
    Set DestSH = ActiveWorkbook.Worksheets.Add(Before:=ActiveWorkbook.Worksheets("Monday"))
    DestSH.Name = "- Summary -"
    For Each SH In ActiveWorkbook.Worksheets
        If SH.Name <> DestSH.Name Then
            Set CopyRng = SH.Range("A1").CurrentRegion
            CopyRng.Copy DestSH.Cells(LastRow(DestSH) + 1, "A")
        End If
    Next
    DestSH.Columns.AutoFit
    Set SourceWB = ActiveWorkbook
    ActiveSheet.Copy
    Set DestWB = ActiveWorkbook
    DestWB.SaveAs Filename:=Left(SourceWB.FullName, (InStr(SourceWB.FullName, ".") - 1)) + ".csv", FileFormat:=xlCSV, ConflictResolution:=xlLocalSessionChanges
    DestWB.Close SaveChanges:=False
    ActiveWorkbook.Worksheets(PreviouslyActiveSHName).Activate
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
End Sub

Thus the workbook now looks like this:

Book1.xlsm

And the visuals in the skin like this (on the top are some of my skins showing the time, so you can compare with the chosen "timeslot"):

Excel Skin.jpg

There would be many things to say about the skin - I was a bit busy yesterday so I could only work on it today - but I don't feel like writing pages explaining everything, so if you don't understand something, just ask it yourself, it would be easier. That being said, there are a few things that should be said:
- the "highlighting" is done purely based on the #DayRows# and #TimeSlot# and while the latter is indeed set according to the current time, the highlighting itself doesn't compare the actual strings / times in the Interval column to the current time, but rather place the highlight proportionally with the time of day and how it relates to the number of rows in a day. In other words, the days must have the same format in Excel, and if you only have 24 rows for a day (headers excluded), one "timeslot" will equal 1 hour instead of the 30 minutes it has now (since there are 48 rows in a day, headers excluded)
- one can scroll between timeslots and weekdays see the comments (this can be "reset" and also the major time changes or the Excel file's change will override the scrolling, when possible - e.g. scrolling between timeslots and update to proper time isn't really possible at the same time for obvious reasons, unless the time is updated more rarely, so it's either one or the other)
- something that I missed in the previous implementation has now been corrected: the last column will now have the same width as the other columns (i.e. will not stop at the width of the longest string from the column) - this has been done in order to have a stable width for the skin on weekday changes; also, some changes were made to how the filename is stored in variables, in order to easily append the desired extension to the "base name" of the file
- opening the workbook on a specific worksheet is possible, but the approach, while not complicated, isn't quite straight-forward either, considering that in this case the number of the worksheet should have been passed as a parameter to another file, so I decided to skip it this time

And that's about it. Hopefully there aren't any bugs in the code and it all works well.

[JP2K]

This is soooo cool! I like the features that you also included as toggle options! Very neat! This is exactly what i was looking for! Thank you so much for this!! -one quick thing, middle mouse click doesn't reset the day interval to current day.- just a small thing i noticed. Its not a big deal

Thanks for everything!!

[Yincognito]

This is soooo cool! I like the features that you also included as toggle options! Very neat! This is exactly what i was looking for! Thank you so much for this!! -one quick thing, middle mouse click doesn't reset the day interval to current day.- just a small thing i noticed. Its not a big deal

Thanks for everything!!

Ah, yes, my bad - the code from the middle mouse click option was from an earlier state of the skin, and forgot to "update" it (or test it, for that matter) to the current environment. Modify it to:

Code: Select all

MiddleMouseUpAction=[!SetVariable RowIndex (#DayRows#*([MeasureWeekday]-1))][!SetVariable Weekday [MeasureWeekday]][!WriteKeyValue Variables Weekday "[MeasureWeekday]"][!SetVariable TimeSlot [MeasureTimeSlot]][!WriteKeyValue Variables TimeSlot "[MeasureTimeSlot]"][!UpdateMeasureGroup "FileGroup"][!UpdateMeter *][!Redraw]

Normally, the reset of the RowIndex variable followed by updating measures / meters and redraw would have been enough, but setting the other stuff doesn't hurt, especially since I want to make it reset the timeslot as well, so it can also be used with timeslot scrolling. Which leads me to the second code change, as I've discovered something else that I missed when scrolling the timeslot, as the scrolling interval was 1 to 49 when it should have been 1 to 48 (basically forgot to subtract 1 from some variables). Therefore, modify the scrolling between time intervals mouse actions to:

Code: Select all

; Uncomment the 2 lines below to scroll between time intervals (the IfNotMatchAction line in [MeasureTimeString] must be commented as well to work)
;MouseScrollUpAction=[!SetVariable TimeSlot (((#DayRows#-1)+(#TimeSlot#-1)-1)%(#DayRows#-1)+1)][!UpdateMeasureGroup "FileGroup"][!UpdateMeter *][!Redraw]
;MouseScrollDownAction=[!SetVariable TimeSlot (((#DayRows#-1)+(#TimeSlot#-1)+1)%(#DayRows#-1)+1)][!UpdateMeasureGroup "FileGroup"][!UpdateMeter *][!Redraw]

The above is their commented state as it was in my previous post, obviously.

I'm glad you're happy with the result. I'm not entirely happy with it though, as I would have done the whole thing "more cleanly", so to speak. I might post a final version of this if I decide to do things my way and disregard the slight inconveniences - no guarantees though.