I am using the Copy and Paste example mentioned in this thread (and in other copy and paste threads). The only thing that I change is to set AutoGenerateColumns="False" and list down the columns. Everything works like the original example.
Code that works:
ASPX
.CS
Now when I change the columns in the grid from GridBoundColumn to GridNumericColumn, here's what I find out:
1. There is an extra <span> element in the GridNumericColumn so I had to update function fillTable to go to an extra parentNode. The data is pasted on the correct cells but if you click on those cells, it removes the pasted data and reverts to the original data.
2. The edit cells changed from an inset border and each cell is slightly indented to a thin solid border where the first column is slightly indented and all succeeding columns mis-aligned. I can deal with the border style, I need to know how to remove the slight indent in the first column or at least make it look like the GridBoundColumn example.
Code that works:
ASPX
<
telerik:RadCodeBlock
ID
=
"rcb1"
runat
=
"server"
>
<
script
type
=
"text/javascript"
>
// ***** Begin Paste from Excel scripts.
var lastFocused;
function pasteFromExcel() {
if (!lastFocused) return;
var clipData = window.clipboardData.getData('Text');
var crlf = String.fromCharCode(13) + String.fromCharCode(10);
var table = clipData.split(crlf);
for (var tRow = 0; tRow <
table.length
- 1; tRow++)
table[tRow] = table[tRow].split(String.fromCharCode(9));
Array.remove(table, table[table.length - 1]);
fillTable(table);
}
function fillTable(table) {
var
pCell
=
lastFocused
.parentNode;
var
pRow
=
pCell
.parentNode;
var
pBody
=
pRow
.parentNode;
var
maxRows
=
pBody
.rows.length;
var
maxCols
=
pRow
.cells.length;
hasChanges
=
true
;
for (var
row
=
0
; row < table.length; row++) {
for (var
col
=
0
; col < table[row].length; col++) {
var
cCellIndex
=
pCell
.cellIndex + col;
var
cRowIndex
=
pRow
.sectionRowIndex + row;
if (cRowIndex < maxRows && cCellIndex < maxCols) {
var
cCell
=
pBody
.rows[cRowIndex].cells[cCellIndex];
var
pInput
=
cCell
.getElementsByTagName("input")[0];
pInput.style.backgroundColor
=
"#F7FAFF"
;
pInput.value
=
table
[row][col];
}
}
}
}
function gridFocus(e) {
e
= e || window.event;
var
target
= e.target || e.srcElement;
if (target.tagName.toLowerCase() == "input" && target.type.toLowerCase() == "text")
lastFocused
=
target
;
}
function clearCells(sender) {
var radGrid = $get('<%= RadGrid1.ClientID %>');
var inputs = radGrid.getElementsByTagName("input");
for (var el = 0; el <
inputs.length
; el++) {
if (inputs[el].type == "text") {
inputs[el]
.value
=
""
;
inputs[el]
.style.backgroundColor
=
""
;
}
}
}
// ***** End Paste from Excel scripts.
</script>
</
telerik:RadCodeBlock
>
<
div
>
<
telerik:RadAjaxPanel
ID
=
"RadAjaxPanel1"
runat
=
"server"
>
<
telerik:RadGrid
ID
=
"RadGrid1"
runat
=
"server"
AllowMultiRowEdit
=
"true"
DataSourceID
=
"SqlDataSource1"
onclick
=
"gridFocus(event)"
Width
=
"830px"
ShowHeader
=
"true"
Skin
=
"Office2007"
OnColumnCreated
=
"RadGrid1_ColumnCreated"
AllowAutomaticUpdates
=
"true"
OnItemUpdated
=
"RadGrid1_ItemUpdated"
AutoGenerateColumns
=
"False"
>
<
MasterTableView
EditMode
=
"InPlace"
CommandItemDisplay
=
"Top"
TableLayout
=
"Fixed"
>
<
CommandItemTemplate
>
<
div
style
=
"float: left"
>
<
input
type
=
"button"
value
=
"Paste from Excel"
onclick
=
"pasteFromExcel()"
/>
<
input
type
=
"button"
value
=
"Clear cells"
onclick
=
"clearCells()"
/>
</
div
>
<
div
style
=
"float: right"
>
<
asp:Button
ID
=
"UpdateEditedButton"
CommandName
=
"UpdateEdited"
Text
=
"Update edited items"
runat
=
"server"
/>
</
div
>
</
CommandItemTemplate
>
<
Columns
>
<
telerik:GridBoundColumn
DataField
=
"ID"
DataType
=
"System.Int32"
HeaderText
=
"ID"
UniqueName
=
"ID"
Visible
=
"true"
ReadOnly
=
"True"
>
<
HeaderStyle
HorizontalAlign
=
"Center"
/>
<
ItemStyle
HorizontalAlign
=
"Left"
/>
</
telerik:GridBoundColumn
>
<
telerik:GridBoundColumn
DataField
=
"Column1"
HeaderText
=
"Column1"
UniqueName
=
"Column1"
Visible
=
"true"
>
</
telerik:GridBoundColumn
>
<
telerik:GridBoundColumn
DataField
=
"Column2"
HeaderText
=
"Column2"
UniqueName
=
"Column2"
Visible
=
"true"
>
</
telerik:GridBoundColumn
>
<
telerik:GridBoundColumn
DataField
=
"Column3"
HeaderText
=
"Column3"
UniqueName
=
"Column3"
Visible
=
"true"
>
</
telerik:GridBoundColumn
>
<
telerik:GridBoundColumn
DataField
=
"Column4"
HeaderText
=
"Column4"
UniqueName
=
"Column4"
Visible
=
"true"
>
</
telerik:GridBoundColumn
>
<
telerik:GridBoundColumn
DataField
=
"Column5"
HeaderText
=
"Column5"
UniqueName
=
"Column5"
Visible
=
"true"
>
</
telerik:GridBoundColumn
>
<
telerik:GridBoundColumn
DataField
=
"Column6"
HeaderText
=
"Column6"
UniqueName
=
"Column6"
Visible
=
"true"
>
</
telerik:GridBoundColumn
>
<
telerik:GridBoundColumn
DataField
=
"Column7"
HeaderText
=
"Column7"
UniqueName
=
"Column7"
Visible
=
"true"
>
</
telerik:GridBoundColumn
>
<
telerik:GridBoundColumn
DataField
=
"Column8"
HeaderText
=
"Column8"
UniqueName
=
"Column8"
Visible
=
"true"
>
</
telerik:GridBoundColumn
>
<
telerik:GridBoundColumn
DataField
=
"Column9"
HeaderText
=
"Column9"
UniqueName
=
"Column9"
Visible
=
"true"
>
</
telerik:GridBoundColumn
>
<
telerik:GridBoundColumn
DataField
=
"Column10"
HeaderText
=
"Column10"
UniqueName
=
"Column10"
Visible
=
"true"
>
</
telerik:GridBoundColumn
>
</
Columns
>
</
MasterTableView
>
</
telerik:RadGrid
>
</
telerik:RadAjaxPanel
>
<
asp:SqlDataSource
ID
=
"SqlDataSource1"
runat
=
"server"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"
DeleteCommand="DELETE FROM [EmptyTable] WHERE [ID] = @ID" InsertCommand="INSERT INTO [EmptyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8], [Column9], [Column10]) VALUES (@ID, @Column1, @Column2, @Column3, @Column4, @Column5, @Column6, @Column7, @Column8, @Column9, @Column10)"
SelectCommand="SELECT * FROM [EmptyTable]" UpdateCommand="UPDATE [EmptyTable] SET [Column1] = @Column1, [Column2] = @Column2, [Column3] = @Column3, [Column4] = @Column4, [Column5] = @Column5, [Column6] = @Column6, [Column7] = @Column7, [Column8] = @Column8, [Column9] = @Column9, [Column10] = @Column10 WHERE [ID] = @ID">
<
DeleteParameters
>
<
asp:Parameter
Name
=
"ID"
Type
=
"Int32"
/>
</
DeleteParameters
>
<
UpdateParameters
>
<
asp:Parameter
Name
=
"Column1"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column2"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column3"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column4"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column5"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column6"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column7"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column8"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column9"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column10"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"ID"
Type
=
"Int32"
/>
</
UpdateParameters
>
<
InsertParameters
>
<
asp:Parameter
Name
=
"ID"
Type
=
"Int32"
/>
<
asp:Parameter
Name
=
"Column1"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column2"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column3"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column4"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column5"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column6"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column7"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column8"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column9"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column10"
Type
=
"String"
/>
</
InsertParameters
>
</
asp:SqlDataSource
>
</
div
>
.CS
protected
void
Page_Load(
object
sender, EventArgs e)
{
for
(
int
i = 0; i < RadGrid1.PageSize; i++)
RadGrid1.EditIndexes.Add(i);
}
protected
void
RadGrid1_ColumnCreated(
object
sender, GridColumnCreatedEventArgs e)
{
if
(e.Column
is
GridBoundColumn)
e.Column.Visible = (e.Column
as
GridBoundColumn).DataField !=
"ID"
;
}
protected
void
RadGrid1_ItemUpdated(
object
source, GridUpdatedEventArgs e)
{
e.KeepInEditMode =
true
;
}
Now when I change the columns in the grid from GridBoundColumn to GridNumericColumn, here's what I find out:
1. There is an extra <span> element in the GridNumericColumn so I had to update function fillTable to go to an extra parentNode. The data is pasted on the correct cells but if you click on those cells, it removes the pasted data and reverts to the original data.
2. The edit cells changed from an inset border and each cell is slightly indented to a thin solid border where the first column is slightly indented and all succeeding columns mis-aligned. I can deal with the border style, I need to know how to remove the slight indent in the first column or at least make it look like the GridBoundColumn example.
<
telerik:RadCodeBlock
ID
=
"rcb1"
runat
=
"server"
>
<
script
type
=
"text/javascript"
>
// ***** Begin Paste from Excel scripts.
var lastFocused;
function pasteFromExcel() {
if (!lastFocused) return;
var clipData = window.clipboardData.getData('Text');
var crlf = String.fromCharCode(13) + String.fromCharCode(10);
var table = clipData.split(crlf);
for (var tRow = 0; tRow <
table.length
- 1; tRow++)
table[tRow] = table[tRow].split(String.fromCharCode(9));
Array.remove(table, table[table.length - 1]);
fillTable(table);
}
function fillTable(table) {
//Changed this line to the 2 lines below.
// var
pCell
=
lastFocused
.parentNode;
var
pSpan
=
lastFocused
.parentNode;
var
pCell
=
pSpan
.parentNode;
var
pRow
=
pCell
.parentNode;
var
pBody
=
pRow
.parentNode;
var
maxRows
=
pBody
.rows.length;
var
maxCols
=
pRow
.cells.length;
hasChanges
=
true
;
for (var
row
=
0
; row < table.length; row++) {
for (var
col
=
0
; col < table[row].length; col++) {
var
cCellIndex
=
pCell
.cellIndex + col;
var
cRowIndex
=
pRow
.sectionRowIndex + row;
if (cRowIndex < maxRows && cCellIndex < maxCols) {
var
cCell
=
pBody
.rows[cRowIndex].cells[cCellIndex];
var
pInput
=
cCell
.getElementsByTagName("input")[0];
pInput.style.backgroundColor
=
"#F7FAFF"
;
pInput.value
=
table
[row][col];
}
}
}
}
function gridFocus(e) {
e
= e || window.event;
var
target
= e.target || e.srcElement;
if (target.tagName.toLowerCase() == "input" && target.type.toLowerCase() == "text")
lastFocused
=
target
;
}
function clearCells(sender) {
var radGrid = $get('<%= RadGrid1.ClientID %>');
var inputs = radGrid.getElementsByTagName("input");
for (var el = 0; el <
inputs.length
; el++) {
if (inputs[el].type == "text") {
inputs[el]
.value
=
""
;
inputs[el]
.style.backgroundColor
=
""
;
}
}
}
// ***** End Paste from Excel scripts.
</script>
</
telerik:RadCodeBlock
>
<
div
>
<
telerik:RadAjaxPanel
ID
=
"RadAjaxPanel1"
runat
=
"server"
>
<
telerik:RadGrid
ID
=
"RadGrid1"
runat
=
"server"
AllowMultiRowEdit
=
"true"
DataSourceID
=
"SqlDataSource1"
onclick
=
"gridFocus(event)"
Width
=
"830px"
ShowHeader
=
"true"
Skin
=
"Office2007"
OnColumnCreated
=
"RadGrid1_ColumnCreated"
AllowAutomaticUpdates
=
"true"
OnItemUpdated
=
"RadGrid1_ItemUpdated"
AutoGenerateColumns
=
"False"
>
<
MasterTableView
EditMode
=
"InPlace"
CommandItemDisplay
=
"Top"
TableLayout
=
"Fixed"
>
<
editformsettings
>
<
editcolumn
filtercontrolalttext
=
"Filter EditCommandColumn column"
>
</
editcolumn
>
</
editformsettings
>
<
CommandItemTemplate
>
<
div
style
=
"float: left"
>
<
input
type
=
"button"
value
=
"Paste from Excel"
onclick
=
"pasteFromExcel()"
/>
<
input
type
=
"button"
value
=
"Clear cells"
onclick
=
"clearCells()"
/>
</
div
>
<
div
style
=
"float: right"
>
<
asp:Button
ID
=
"UpdateEditedButton"
CommandName
=
"UpdateEdited"
Text
=
"Update edited items"
runat
=
"server"
/>
</
div
>
</
CommandItemTemplate
>
<
CommandItemSettings
ExportToPdfText
=
"Export to PDF"
/>
<
rowindicatorcolumn
filtercontrolalttext
=
"Filter RowIndicator column"
visible
=
"True"
>
</
rowindicatorcolumn
>
<
expandcollapsecolumn
filtercontrolalttext
=
"Filter ExpandColumn column"
visible
=
"True"
>
</
expandcollapsecolumn
>
<
Columns
>
<
telerik:GridNumericColumn
DataField
=
"ID"
DataType
=
"System.Int32"
HeaderText
=
"ID"
UniqueName
=
"ID"
Visible
=
"true"
ReadOnly
=
"True"
>
</
telerik:GridNumericColumn
>
<
telerik:GridNumericColumn
DataField
=
"Column1"
HeaderText
=
"Column1"
UniqueName
=
"Column1"
Visible
=
"true"
>
</
telerik:GridNumericColumn
>
<
telerik:GridNumericColumn
DataField
=
"Column2"
HeaderText
=
"Column2"
UniqueName
=
"Column2"
Visible
=
"true"
>
</
telerik:GridNumericColumn
>
<
telerik:GridNumericColumn
DataField
=
"Column3"
HeaderText
=
"Column3"
UniqueName
=
"Column3"
Visible
=
"true"
>
</
telerik:GridNumericColumn
>
<
telerik:GridNumericColumn
DataField
=
"Column4"
HeaderText
=
"Column4"
UniqueName
=
"Column4"
Visible
=
"true"
>
</
telerik:GridNumericColumn
>
<
telerik:GridNumericColumn
DataField
=
"Column5"
HeaderText
=
"Column5"
UniqueName
=
"Column5"
Visible
=
"true"
>
</
telerik:GridNumericColumn
>
<
telerik:GridNumericColumn
DataField
=
"Column6"
HeaderText
=
"Column6"
UniqueName
=
"Column6"
Visible
=
"true"
>
</
telerik:GridNumericColumn
>
<
telerik:GridNumericColumn
DataField
=
"Column7"
HeaderText
=
"Column7"
UniqueName
=
"Column7"
Visible
=
"true"
>
</
telerik:GridNumericColumn
>
<
telerik:GridNumericColumn
DataField
=
"Column8"
HeaderText
=
"Column8"
UniqueName
=
"Column8"
Visible
=
"true"
>
</
telerik:GridNumericColumn
>
<
telerik:GridNumericColumn
DataField
=
"Column9"
HeaderText
=
"Column9"
UniqueName
=
"Column9"
Visible
=
"true"
>
</
telerik:GridNumericColumn
>
<
telerik:GridNumericColumn
DataField
=
"Column10"
HeaderText
=
"Column10"
UniqueName
=
"Column10"
Visible
=
"true"
>
</
telerik:GridNumericColumn
>
</
Columns
>
</
MasterTableView
>
<
filtermenu
enableimagesprites
=
"False"
>
</
filtermenu
>
</
telerik:RadGrid
>
</
telerik:RadAjaxPanel
>
<
asp:SqlDataSource
ID
=
"SqlDataSource1"
runat
=
"server"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"
DeleteCommand="DELETE FROM [EmptyTable] WHERE [ID] = @ID" InsertCommand="INSERT INTO [EmptyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8], [Column9], [Column10]) VALUES (@ID, @Column1, @Column2, @Column3, @Column4, @Column5, @Column6, @Column7, @Column8, @Column9, @Column10)"
SelectCommand="SELECT * FROM [EmptyTable]" UpdateCommand="UPDATE [EmptyTable] SET [Column1] = @Column1, [Column2] = @Column2, [Column3] = @Column3, [Column4] = @Column4, [Column5] = @Column5, [Column6] = @Column6, [Column7] = @Column7, [Column8] = @Column8, [Column9] = @Column9, [Column10] = @Column10 WHERE [ID] = @ID">
<
DeleteParameters
>
<
asp:Parameter
Name
=
"ID"
Type
=
"Int32"
/>
</
DeleteParameters
>
<
UpdateParameters
>
<
asp:Parameter
Name
=
"Column1"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column2"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column3"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column4"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column5"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column6"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column7"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column8"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column9"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column10"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"ID"
Type
=
"Int32"
/>
</
UpdateParameters
>
<
InsertParameters
>
<
asp:Parameter
Name
=
"ID"
Type
=
"Int32"
/>
<
asp:Parameter
Name
=
"Column1"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column2"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column3"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column4"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column5"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column6"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column7"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column8"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column9"
Type
=
"String"
/>
<
asp:Parameter
Name
=
"Column10"
Type
=
"String"
/>
</
InsertParameters
>
</
asp:SqlDataSource
>
</
div
>