Pasting Excel into Tiddler -- Convert to Table

[Mike Crowe]

Hi folks,

I did an experiment, and if you highlight a block of excel cells and
paste into a tiddler, the output looks like:

cell<tab>cell<tab>cell
cell<tab>cell<tab>cell
cell<tab>cell<tab>cell

If you do something to the effect of:

"|"+line.replace(/\t/g,"|")+"|"

it will make the text into a table.

Question: I'd like to implement, cause I really need. Can somebody
point me in right direction?

1) Which function to look at (I've scanned, and this is a very
inter-twined system
2) Is there a way to write a plugin which can do this?

TIA
Mike

[Daniel Baird]

I"ve previously used an excel formula to generate wiki markup for a whole table row in a single excel column. Then I could copy just that single Excel column, paste, and it worked right away.

Basically just concatenating stuff with &.. ="|" & A2 & "|" & A3 & "|" type of thing.

Not as useful what you're asking for, but anyway..

;D

--
Daniel Baird
http://danielbaird.com (TiddlyW;nks! :: Whiteboard Koala :: Blog :: Things That Suck)

[Mike Crowe]

OK, I did it. I added this function:
// Take a tiddler with embedded tabs (indicating table cells)
// Reformat to wiki format
function doMakeTable(t) {
var cells = t.split("\t");
if ( cells.length > 2 ) {
var ret="";
var first=1;
var lines=t.split("\n");
for ( var i=0; i<lines.length; i++ ) {
var l=lines[i];
if ( l.indexOf("\t") == -1 ) {
ret += l + "\n";
} else {
cells = l.split("\t");
var repl = "|" + ( first ? "!" : "" );
first = false;
ret += repl + cells.join(repl) + "|\n";
}
}
return ret;
}
return t;
}

Then, change:
Story.prototype.gatherSaveFields = function(e,fields)

and look for the line:

fields[f] = e.value.replace(/\r/mg,"");

Replace with:

fields[f] = doMakeTable(e.value.replace(/\r/mg,""));

Can anybody suggest a better way? I thought about modifying the wikify
system, but I like the idea of adding "|!" to the first line of the
row.

Any comments?

Mike

[Udo Borkowski]

Here is the idea:

Copy the block of excels cells and paste into a tiddler (just as you
did). Select the just pasted block and select the edit menu item
"convert excel" and this will automatically convert the table from the
tab separated stuff to a TiddlyWiki table, just as you suggested.

The drawback: this edit menu item "convert excel" does not yet exist.
But it should not be to hard to implement it using a new plugin.

If I had to do that plugin I would probably do the following:

* get a copy the "TiddlerSlicer" plugin
(http://yann.perrin.googlepages.com/twkd.html#TiddlerSlicer%20lang:en).
This plugin already contains a lot of the stuff you need for this
"convert excel" stuff, like how to work with a selection of text in the
tiddler, how to change a selected text in the tiddler etc. Using this
code as a sample also makes it easier to understand the "inter-twined
system" a little better, I guess.

* Rename that plugin and adapt it accordingly, and throw away stuff you
don't need.

If there are any specific questions, don't hesitate to ask (preferably
in the TiddlyWikiDev group).

Udo

----------
Udo Borkowski
http://www.abego-software.de

[Mike Crowe]

Yep, that will work better. I just discovered my way made pasted
tiddler code into tables. I'll move to Dev group.

[Simon Baird]

That's a great idea. I think what you need is a new formatter. look at config.formatters.

 

Something that would match a start and end delimiter, then parse the contents into a table, eg your tiddler content might be, like this (tabs between cells)

 

|||

col      col2

foo      bar

etc..

 

|||

 

 

I just invented the ||| delimiter, perhaps you can think of a better one.

 

The good news is it can done easily if you can understand the formatters. The bad news... formatters are very had to understand, at least I know I have lots of trouble. :) Good luck.

 

Simon.

 

On 6/22/06, Mike Crowe <drmik...@gmail.com> wrote:

--
Simon Baird <simon...@gmail.com>

[mmm...@googlemail.com]

Hi!

I also had the problem with pasting Excel tables to my TiddlyWiki. But
since I wanted to have also the Excel formatting (colors!) I wrote an
Excel Macro.
Just select the area and press CTRL-W, you will be asked how many lines
should become table header (just pasting a ! at the beginning of the
text. After that, the selected excel area is in the Clipboard in Wiki
format.

List of features:
# Font and cell background colors are supported (font in black and cell
bg in white are default)
# The styles Italics, Bold, Strikethrough and Super- and Subscript are
supported
# Alignment is supported
# Merged cells are retained
# If the upper-left cell has a comment then that comment is used as
heading above the table!
# Cell values are used as displayed in Excel (precision, date format,
...)
# Errors are shown as in Excel
# Multiline cells are converted using < < br > > macro
# Cells with content starting with § are omitted in the conversion to
the table, but the content is appended as new lines below the table. (I
use this to make-up reminders in a separate cell)
# Conditional formatting is applied (for color only)

If there is interest, I could find a place to put it online.

Best regards
Marcus

[Jeroen]

Hi Marcus,

I also wrote an Excel macro that converts Excel tables to Tiddlywiki
format. It supports formatting like alignment, colors and
bold/italic/underline/strikethrough text. Only horizontally merged
cells are supported. I couldn't figure out how to convert vertically
merged cells to Tidllywiki format, so in the Excel table that I want to
convert I add "~" in the cells beneath the cell I want to vertically
merge with. I'm curious to see your solution.

My macro is short enough to be published here. For maximum comfort,
make the macro start with a keystroke, like CTRL-t. Select the table to
convert and hit CTRL-t, and directly paste the clipboard into a
tiddler.

Cheers,
Jeroen

--------------------------------------
Sub XL2TW()
'
' De macro is opgenomen op 25-8-2005 door Jeroen Haffmans.
'
' Sneltoets: CTRL+t
'
Dim x As Integer
Dim y As Integer
Dim sx As Integer
Dim sy As Integer
Dim nx As Integer
Dim ny As Integer
Dim r1 As Range
Dim s1 As String
Dim s2 As String
Dim hs As String
Dim hi As Integer
Dim hb As Boolean
Dim regel As String
Dim c As New DataObject
Dim kleur(56) As String

kleur(1) = "#000000"
kleur(2) = "#FFFFFF"
kleur(3) = "#FF0000"
kleur(4) = "#00FF00"
kleur(5) = "#0000FF"
kleur(6) = "#FFFF00"
kleur(7) = "#FF00FF"
kleur(8) = "#00FFFF"
kleur(9) = "#800000"
kleur(10) = "#008000"
kleur(11) = "#000080"
kleur(12) = "#808000"
kleur(13) = "#800080"
kleur(14) = "#008080"
kleur(15) = "#C0C0C0"
kleur(16) = "#808080"
kleur(17) = "#9999FF"
kleur(18) = "#993366"
kleur(19) = "#FFFFCC"
kleur(20) = "#CCFFFF"
kleur(21) = "#660066"
kleur(22) = "#FF8080"
kleur(23) = "#0066CC"
kleur(24) = "#CCCCFF"
kleur(25) = "#000080"
kleur(26) = "#FF00FF"
kleur(27) = "#FFFF00"
kleur(28) = "#00FFFF"
kleur(29) = "#800080"
kleur(30) = "#800000"
kleur(31) = "#008080"
kleur(32) = "#0000FF"
kleur(33) = "#00CCFF"
kleur(34) = "#CCFFFF"
kleur(35) = "#CCFFCC"
kleur(36) = "#FFFF99"
kleur(37) = "#99CCFF"
kleur(38) = "#FF99CC"
kleur(39) = "#CC99FF"
kleur(40) = "#FFCC99"
kleur(41) = "#3366FF"
kleur(42) = "#33CCCC"
kleur(43) = "#99CC00"
kleur(44) = "#FFCC00"
kleur(45) = "#FF9900"
kleur(46) = "#FF6600"
kleur(47) = "#666699"
kleur(48) = "#969696"
kleur(49) = "#003366"
kleur(50) = "#339966"
kleur(51) = "#003300"
kleur(52) = "#333300"
kleur(53) = "#993300"
kleur(54) = "#993366"
kleur(55) = "#333399"
kleur(56) = "#333333"

Set r1 = Selection
sx = r1.Column
sy = r1.Row
nx = r1.Columns.Count
ny = r1.Rows.Count
regel = ""

For y = sy To sy + ny - 1
s2 = ""
For x = sx To sx + nx - 1

s1 = Trim(Cells(y, x).Text)
If (Cells(y, x).MergeCells) Then 'samengevoegd
If s1 <> "" Then 'eerste cel van samengevoegde cellen
s2 = s1
s1 = ">"
Else
If x = sx + nx - 1 Then 'laatste cel omdat selectie eindigt
s1 = s2
Else
If (Cells(y, x + 1).MergeCells) Then 'volgende cel ook
samengevoegd
If Trim(Cells(y, x + 1).Text) <> "" Then ' volgende
cel: nieuwe samengevoegde cellen
s1 = s2
Else: 'volgende cel hoort bij samenvoeging
s1 = ">"
End If
Else: s1 = s2
End If
End If
End If
End If

If s1 <> ">" And s1 <> "~" Then
If s1 <> "" Then
If (Cells(y, x).Font.Bold) Then s1 = "''" + s1 + "''"
If (Cells(y, x).Font.Italic) Then s1 = "//" + s1 + "//"
If (Cells(y, x).Font.Underline = xlUnderlineStyleSingle) Then
s1 = "__" + s1 + "__"
If (Cells(y, x).Font.Strikethrough) Then s1 = "==" + s1 + "=="
If (Cells(y, x).Font.ColorIndex <> -4105) Then s1 = "@@color("
+ kleur(Cells(y, x).Font.ColorIndex) + "):" + s1 + "@@"
If Cells(y, x).HorizontalAlignment = 1 Then
s1 = s1 + " "
ElseIf Cells(y, x).HorizontalAlignment = -4108 Then
s1 = " " + s1 + " "
ElseIf Cells(y, x).HorizontalAlignment = -4152 Then
s1 = " " + s1
End If
End If
If (Cells(y, x).Interior.ColorIndex > 0) Then s1 = "bgcolor(" +
kleur(Cells(y, x).Interior.ColorIndex) + "):" + s1
End If
regel = regel + "|" + s1
Next x
regel = regel + "|" + Chr(10)
Next y
c.SetText regel
c.PutInClipboard
End Sub
---------------------------------------------

[Mike Crowe]

BTW, Marcus, the TiddlySlice plugin I modified (we're discussing in
TiddlyWikiDev) makes the first line of your table "|!" so it looks like
a header. Not as sophisticated as either of yours, but works for me.