Exporting GDBG to Excel workbook

[Administrator]

Hi,

I need help to export a GridDataBoundGrid data to a Excel workbook with multiple sheet.

Let me explain about my problem.

I have three DataTables
companyDt
depratmentDt
SunDepartmentDt

these data tables are related. I am Using “Relation” to define the relation between these.

A company can have more than one department and there are more than one Sub-department for each department (basically “Units”).

Using “Relation”my GDBG display a tree view structure within the grid. Everything is fine upto here.

Now I have to import this GDBG view/data to a Excel workbook with multiple Sheets. Each sheet also has custom name based on data.

1St Sheet

All the Companies ( Sheet Name: Companies)

XYZ.........................
ABC........................
WXY.......................

2nd Sheet
All the Departments for “XYZ”Company (Sheet Name: XYZ -Departments)
XYZDept1..................
XYZDept2..................
XYZDept3..................

3rd Sheet
All the Sub-Departments for “XYZ” Company (Sheet Name: XYZ-SubDepartment)
XYZSubDept1..................
XYZSubDept2..................
XYZSubDept3..................

4th Sheet
All the Departments for “ABC”Company (Sheet Name: ABC-Departments)
ABC Dept1..................
ABC Dept2..................
ABC Dept3..................

5th Sheet
All the Sub-Departments for “ABC” Company (Sheet Name: ABC-SubDepartment)
ABC SubDept1..................
ABC SubDept2..................
ABC SubDept3..................

And so on ......

I have no idea how to do it. Please provide some points to start with or point me to a sample where I can get some help.

Please don’t let me down.

Regrads,
David

[Administrator]

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

[Administrator]

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

[Administrator]

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.

[Administrator]

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

[Administrator]

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

[Administrator]

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

[Administrator]

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.

[Administrator]

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

[Administrator]

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

[Administrator]

clay,

Found the way about number of sheets.

Please update about tree view in "XLS" file.

regrads,
David

[Administrator]

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

[Administrator]

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

[Administrator]

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

[Administrator]

http://www.syncfusion.com/Support/user/uploads/GDBG_WriteExcel_c67e8722.zip

[Administrator]

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

[Administrator]

Thanks clay,

Dont'' worry about it. i found the problem.

Once again thanks for your help.

regards,
David

[Administrator]

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;
		}

[Vijay Sagar]

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
>

[Vijay Sagar]

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.