Free Support Forum - aspose.com

Populating exceldesigner with datagrid

Hi,

I am currently testing your product to see if it fit my needs. It looks great but I have few problems.
I want to use the exceldesigner with a template of an excel file and smartmarker.
How can I populate an exceldesigner object with a datagrid?

I basically have a page with a datagrid and a chart representing the value of the datagrid and I would like to export the datagrid and the chart to excel. I would like to be able to customize the excel spreadsheet by using smartmarker. I have been able to export the grid by using importDataGrid, but that does not import the column header. Moreover I do not know how to use the smartmarker when we import the data from a datagrid.

Thanks

.NET doesn't provide a method to get the column header of a datagrid so we don't know how to import them. You can use ImportDataGrid method to import data in datagrid and set the column header by your code.

Because we cannot know the column header of a datagrid, we don't have a smooth way to make it work with smart marker. I think you can manually import data in a datagrid and populate them in a DataTable, then set this datatable as a data source.

you can use DataGridColumnCollection to set the headers yourself, but it would be nice if Aspose Excel would have a method for it

Here is a sample which creates smart maker worksheet based on DataGrid column collection and populates it with data from DataTable.

private void importData(Worksheet w,DataGridColumnCollection columns,DataTable dataTable,int startRow)
{

int col=0;

string table="&=["+dataTable.TableName+"].";

int styleid = designer.Excel.Styles.Add();
designer.Excel.Styles[styleid].Custom= "d-mmm-yy";
designer.Excel.Styles[styleid].Font.Size=8;
string []DataColumn;

DataColumn = new string[dataTable.Columns.Count];

foreach (DataGridColumn c in columns)
if ( (c.SortExpression != null) && (c.SortExpression.Length>0) && (c.HeaderText.Length>0) )
{
string []columnName=c.SortExpression.Split(' ');
if ( (columnName[0]== null) || (columnName[0].Length==0) || (dataTable.Columns[columnName[0]] ==null) )
continue;

DataColumn[col]=columnName[0];

string header=c.HeaderText;
w.Cells[startRow,col].PutValue(header);
w.Cells[startRow,col].Style.Font.IsBold=true;
w.Cells[startRow,col].Style.Font.Color=Color.White;
w.Cells[startRow,col].Style.ForegroundColor=Color.DarkBlue;
w.Cells[startRow,col].Style.Font.Size=8;
w.Cells[startRow+1,col].PutValue(table+columnName[0]);
if (dataTable.Columns[columnName[0]].DataType == Type.GetType("System.DateTime"))
w.Cells[startRow+1,col].Style = designer.Excel.Styles[styleid];

// must set the Date/Time type manually
col++;

}

designer.SetDataSource(dataTable);
designer.Process();

}

}

Hi Laurence , thanks for your answer.

From what you are telling and from what I read in the doc, it sounds a lot more flexible to import or setthedatasource to a dataview or Datatable.
However the importDatagrid looks lot more efficient because I do not have to reconnect to the database to get the data and put it into a DataSet or Dataview or Datatable.

The header problem is not a real problem for my case, however, with the importDataGrid, can I have the flexibility of choosing where the Data from the grid is going to be shown on the excel spreadsheat? For example starting showing the data at the cells D15.
In my grid I have hidden column that shows as a blank column in excel after doing the importDatagrid. Is there a way to prevent that without having to use Datatable or Dataview?

Thanks in advance

Yes. You can choose the start cell to populate data with ImportDataGrid method.

cells.ImportDataGrid(dataGrid, 14, Cells.ColumnNameToIndex("D"), true);

For blank column issue, could you please post a sample project to show your problem? That will help us to figure out the solution. Thank you.