We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Exporting GDBG to Excel workbook

Thread ID:

Created:

Updated:

Platform:

Replies:

28330 Apr 28,2005 11:11 PM Jun 16,2006 01:50 PM Windows Forms 19
loading
Tags: GridControl
Administrator [Syncfusion]
Asked On April 28, 2005 11:11 PM

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 [Syncfusion]
Replied On April 29, 2005 01:34 PM

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 [Syncfusion]
Replied On April 29, 2005 03:09 PM

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 [Syncfusion]
Replied On April 29, 2005 04:34 PM

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 [Syncfusion]
Replied On April 29, 2005 04:43 PM

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 [Syncfusion]
Replied On April 29, 2005 08:47 PM

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 [Syncfusion]
Replied On April 30, 2005 02:45 AM

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 [Syncfusion]
Replied On April 30, 2005 06:41 AM

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 [Syncfusion]
Replied On April 30, 2005 05:44 PM

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 [Syncfusion]
Replied On May 2, 2005 07:00 PM

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 [Syncfusion]
Replied On May 2, 2005 08:04 PM

clay, Found the way about number of sheets. Please update about tree view in "XLS" file. regrads, David

Administrator [Syncfusion]
Replied On May 2, 2005 08:27 PM

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 [Syncfusion]
Replied On May 2, 2005 09:09 PM

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 [Syncfusion]
Replied On May 2, 2005 11:18 PM

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 [Syncfusion]
Replied On May 3, 2005 08:47 AM

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

Administrator [Syncfusion]
Replied On May 3, 2005 10:19 AM

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 [Syncfusion]
Replied On May 3, 2005 12:05 PM

Thanks clay, Dont'' worry about it. i found the problem. Once again thanks for your help. regards, David

Administrator [Syncfusion]
Replied On May 3, 2005 12:21 PM

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
Replied On June 16, 2006 11:51 AM

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
Replied On June 16, 2006 01:50 PM

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.

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;