AD
Administrator
Syncfusion Team
April 29, 2005 05:34 PM UTC
Here is a link to a sample that output 3 GridDataBoundGrids in an excel workbook using our ExcelRW library.
http://www.syncfusion.com/Support/user/uploads/GDBG_WriteExcel_10516b47.zip
AD
Administrator
Syncfusion Team
April 29, 2005 07:09 PM UTC
Thanks clay,
In your sample you are passing multiple grids.
But in my case I have only one GDBG object. It uses dataset “Relation” between different data tables to show a tree view structure.
Show I don’t have multiple grids, it is only one grid.
Should I create multiple grid based on data on fly and then user or method?
I am including a snapshot of my grid. As per company policy I can’t give you the whole code. But I will give you picture.
Regrads,
David
AD
Administrator
Syncfusion Team
April 29, 2005 08:34 PM UTC
You should probably use our ExcelRW library directly to write the sheets you want to see. I do not think there is any thing to be gained by trying to put the data into grid, and then moving it to excel worksheets.
The sample shows how to create a workbook with multiple sheets using ExcelRW. Instead of getting the data from different grids, you would get it directly from the single hiearchical grid. On any grid rowindex, you can call grid..Binder.GetRecordStateAtRowIndex to get teh GridBoundRecordState object for the row. This object has references to two lists, the parent list holding this record, and the childlist for this record (if one exists). These lists hold DataRowView objects from the different data tables you are using. Using this information, I think you can loop through things and build up the excel sheets you want to put into the workbook.
AD
Administrator
Syncfusion Team
April 29, 2005 08:43 PM UTC
I appreciate it.
I know it would be too much to ask, but would it be possible to modify the sample or provide some lines of code to achieve the goal.
Your help will be much appreciated.
David
AD
Administrator
Syncfusion Team
April 30, 2005 12:47 AM UTC
Here is the sample tweaked to use a hierarchical grid. It writes out the parent table and a tab with each related child table on it.
http://www.syncfusion.com/Support/user/uploads/HierGDBG_WriteExcel_5a0b6f33.zip
AD
Administrator
Syncfusion Team
April 30, 2005 06:45 AM UTC
You rocks clay,
thanks a lot.
Just last question
is it possible to save the my GDBG object as it is to a excel file? I have this grid that display the data in a tree view, we save/export that view/grid to a excel file. This excel file also display the information in same tree view manner.
Is it possible?
Does excel support the relations of data, I mean can we display a tree view?
Have a great weekend if you check this before Monday.
otherwise I hope you had a great weekend.
Anyway I am working over the weekend.
Regrads,
David
AD
Administrator
Syncfusion Team
April 30, 2005 10:41 AM UTC
I do not know if Excel supports related data. (I suspect that it does but have never tried to do it.) I will have to ask someone on the ExcelRW product team if the current ExcelRW library supports this.
AD
Administrator
Syncfusion Team
April 30, 2005 09:44 PM UTC
Thanks clay,
It would be great if it could be done.
Before i say anuthing to my management, i will wait for your reply!
Once agian i really appreciate your help.
regrads,
David
AD
Administrator
Syncfusion Team
May 2, 2005 11:00 PM UTC
clay,
In you sample you used the following code
CurrencyManager cm = grid.BindingContext[grid.DataSource, grid.DataMember] as CurrencyManager;
DataView dv = cm.List as DataView;
DataTable dt = dv.Table;
this.numberSheets = dv.Count + 1;
if(this.numberSheets == 0)
throw new ArgumentException("Must pass some grids");
IWorkbook myWorkBook = ExcelUtils.CreateWorkbook(this.numberSheets);
Sine, you knew that there is only “one level” of relation, and based on that you created the workbook with predefined number of sheets.
If we try to uderstand the following
this.numberSheets = dv.Count + 1
1 sheet for parent + 1 sheet for each child or each row in parent
But in my case I have 3 level of relation
How do I know in advance, what is the total no of sheet?
Or
Tell me how you would have done this if there are three level of relation in your sample.
Please do reply!
I really need answer for this one.
regrds,
David
AD
Administrator
Syncfusion Team
May 3, 2005 12:04 AM UTC
clay,
Found the way about number of sheets.
Please update about tree view in "XLS" file.
regrads,
David
AD
Administrator
Syncfusion Team
May 3, 2005 12:27 AM UTC
Clay,
I am really having hard time to create sheets for third level. You gave me the sample for 1st and 2nd level.
I am trying hard to do it but I am little sort of time. I know it is bad practice but I really need help.
Please help me.
regards,
David
AD
Administrator
Syncfusion Team
May 3, 2005 01:09 AM UTC
I have not heard back whether they know if Excel supports hierarchical data and whether our ExcelRW supports it.
Here is a sample of writing out sheets based on the inner most tables in a 3 level hierarchy.
http://www.syncfusion.com/Support/user/uploads/GDBG_WriteExcel_dc2a9c01.zip
AD
Administrator
Syncfusion Team
May 3, 2005 03:18 AM UTC
clay,
I am sorry. I guess I didn’t explain the problem clear enough.
I will try to explain using your sample.
In you sample, you have
Parents ----- Childs --- Grandchilds
Excel workbook should have the following layout
Sheet # 1
1st sheet should have all the parents
Sheet# 2
This sheet should have all the childs of parent#1
Sheet#3
This sheet should have all the grandchild of parent#1
Sheet#4
This sheet should have all the child of parent#2
Sheet#5
This sheet should have all the grandchild of parent#2
Sheet#6
This sheet should have all the child of parent#3
Sheet#7
This sheet should have all the grandchild of parent#3
.
.
And so on
I have to finish up this soon, i am also trying to solve this.
If you don’t mind and have time please modify the sample ONE LAST Time.
I promise I will not bother you again.
I really appreciate you attention and time you putting here.
Regards,
David
AD
Administrator
Syncfusion Team
May 3, 2005 12:47 PM UTC
http://www.syncfusion.com/Support/user/uploads/GDBG_WriteExcel_c67e8722.zip
AD
Administrator
Syncfusion Team
May 3, 2005 02:19 PM UTC
Morning Clay,
I guess there is small problem on grandchild sheet.
It is printing grandchild X number times. Where X is total no of ganchild for a child.
Example:
Child A
------- GrandChild 1
------- GrandChild 2
------- GrandChild 3
Child B
------- GrandChild 4
------- GrandChild 5
------- GrandChild 6
Child C
------- GrandChild 7
------- GrandChild 8
On the grandchild sheet it is printing
GrandChild 1
GrandChild 2
GrandChild 3
GrandChild 1
GrandChild 2
GrandChild 3
GrandChild 1
GrandChild 2
GrandChild 3
GrandChild 4
GrandChild 5
GrandChild 6
GrandChild 4
GrandChild 5
GrandChild 6
GrandChild 4
GrandChild 5
GrandChild 6
GrandChild 7
GrandChild 8
GrandChild 7
GrandChild 8
While it should only print
GrandChild 1
GrandChild 2
GrandChild 3
GrandChild 4
GrandChild 5
GrandChild 6
GrandChild 7
GrandChild 8
Do you see the problem?
Regards,
David
AD
Administrator
Syncfusion Team
May 3, 2005 04:05 PM UTC
Thanks clay,
Dont'' worry about it. i found the problem.
Once again thanks for your help.
regards,
David
AD
Administrator
Syncfusion Team
May 3, 2005 04:21 PM UTC
The case 2 code only needs to be hit once, not for each grandchild. So, you can move it to the bottom of case 1.
public void WriteFile(GridDataBoundGrid grid, string fileName, string keyColumn1, string keyColumn2)
{
CurrencyManager cm = grid.BindingContext[grid.DataSource, grid.DataMember] as CurrencyManager;
DataView dv = cm.List as DataView;
DataTable dt = dv.Table;
this.numberSheets = 1 + 2 * dv.Count;
int rowIndex = grid.Model.Rows.HeaderCount;
if(this.numberSheets == 0)
throw new ArgumentException("Must pass some grids");
IWorkbook myWorkBook = ExcelUtils.CreateWorkbook(this.numberSheets);
int sheetNumber = 0;
IWorksheet sheet = myWorkBook.Worksheets[sheetNumber];
sheet.Name = dt.TableName;
int numRows = dv.Count;
int numCols = dv[0].Row.ItemArray.GetLength(0);
for(int row = 1; row <= numRows; row++)
{
for(int col = 1; col <= numCols; col++)
{
sheet.Range[row , col].Value2 = dv[row-1][col-1].ToString();
}
}
//move the column widths/ column styles
for(int col = 0; col < numCols; col++)
{
//set the width
sheet.Columns[col].ColumnWidth = ((double)grid.Model.ColWidths[col + grid.Model.Cols.HeaderCount + 1]) / 7.43;
//get the grid style
GridStyleInfo style;
if(grid.GridBoundColumns == null || grid.GridBoundColumns.Count == 0)
{
style = grid.Binder.InternalColumns[col].StyleInfo;
}
else
{
style = grid.GridBoundColumns[col].StyleInfo;
}
//get the excel style
IStyle xStyle = sheet.Columns[col].CellStyle;
//put grid style into excel style
this.ApplyStyleProperties(style, xStyle, myWorkBook, grid);
}
grid.ExpandAll();
rowIndex = grid.Model.Rows.HeaderCount + 1;
int level1 = -1;
int level2 = -1;
int parentIndex = -1;
int level2Ct = 0;
while(rowIndex < grid.Model.RowCount)
{
GridBoundRecordState state = grid.Binder.GetRecordStateAtRowIndex(rowIndex);
switch(state.LevelIndex)
{
case 0:
sheetNumber++;
level1 = sheetNumber;
myWorkBook.Worksheets[level1].Name = grid[rowIndex, grid.Binder.NameToColIndex(keyColumn1)].Text + " Children";
sheetNumber++;
level2 = sheetNumber;
level2Ct = 0;
myWorkBook.Worksheets[level2].Name = grid[rowIndex, grid.Binder.NameToColIndex(keyColumn1)].Text + " GrandChildren";
break;
case 1:
{
sheet = myWorkBook.Worksheets[level1];
DataView dv1 = state.Table as DataView;
parentIndex = rowIndex;
int numRows1 = dv1.Count;
int numCols1 = dv1[0].Row.ItemArray.GetLength(0);
//move the values
for(int row = 1; row <= numRows1; row++)
{
for(int col = 1; col <= numCols1; col++)
{
sheet.Range[row , col].Value2 = dv1[row-1][col-1].ToString();
}
}
//move the column widths/ column styles
for(int col = 0; col < numCols1; col++)
{
//set the width
sheet.Columns[col].ColumnWidth = ((double)grid.Model.ColWidths[col + grid.Model.Cols.HeaderCount + 1]) / 7.43;
//get the grid style
GridStyleInfo style;
if(grid.GridBoundColumns == null || grid.GridBoundColumns.Count == 0)
{
style = grid.Binder.InternalColumns[col].StyleInfo;
}
else
{
style = grid.GridBoundColumns[col].StyleInfo;
}
//get the excel style
IStyle xStyle = sheet.Columns[col].CellStyle;
//put grid style into excel style
this.ApplyStyleProperties(style, xStyle, myWorkBook, grid);
}
//handle the grandchild
sheet = myWorkBook.Worksheets[level2];
dv1 = state.ChildList as DataView;
numRows1 = dv1.Count;
numCols1 = dv1[0].Row.ItemArray.GetLength(0);
//move the values
for(int row = 1; row <= numRows1; row++)
{
for(int col = 1; col <= numCols1; col++)
{
sheet.Range[row + level2Ct , col].Value2 = dv1[row-1][col-1].ToString();
}
}
level2Ct += numRows1;
//move the column widths/ column styles
for(int col = 0; col < numCols1; col++)
{
//set the width
sheet.Columns[col].ColumnWidth = ((double)grid.Model.ColWidths[col + grid.Model.Cols.HeaderCount + 1]) / 7.43;
//get the grid style
GridStyleInfo style;
if(grid.GridBoundColumns == null || grid.GridBoundColumns.Count == 0)
{
style = grid.Binder.InternalColumns[col].StyleInfo;
}
else
{
style = grid.GridBoundColumns[col].StyleInfo;
}
//get the excel style
IStyle xStyle = sheet.Columns[col].CellStyle;
//put grid style into excel style
this.ApplyStyleProperties(style, xStyle, myWorkBook, grid);
}
}
break;
default:
break;
}
rowIndex++;
}
myWorkBook.SaveAs(fileName);
myWorkBook.Close();
ExcelUtils.ThrowNotSavedOnDestroy = false;
}
VS
Vijay Sagar
June 16, 2006 03:51 PM UTC
Is there any chance you could modify this example to work with grid version 4.2? I tried to compile it and I had issues.
Thanks
Vijay
>http://www.syncfusion.com/Support/user/uploads/GDBG_WriteExcel_c67e8722.zip
>
VS
Vijay Sagar
June 16, 2006 05:50 PM UTC
I was able to build it but when I run the example and try to export to EXCEL, the I get an error which says that file Syncfusion.core is mising one of its dependencies.