469,287 Members | 2,503 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,287 developers. It's quick & easy.

Exporting to Excel and Column Order

Opa
Hi,

I have a DataGrid, whose sourceI am exporting to Excel. This works fine
except for the Column ordering. My datasource is not a datatable, with a
typical
SELECT statement where I can select the column orders. Instead the datasource
is a class which implements IList, containing a collection of my data. My
problem
again is that I don't know how to control the order of the columns that are
exported.

Any Ideas?

Thanks
Sep 11 '06 #1
6 2321
Opa,

This isn't a solution but may lead you in the correct direction. You will
have to find a way to sort the columns. You may need to make a customized
sort. Which can be added on to your class. But here's a simple default
method:

public IList Sort(IList list)
{
((ArrayList)list).Sort();
return list;
}

Regards,

--
S. Justin Gengo
Web Developer / Programmer

Free code library:
http://www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"Opa" <Op*@discussions.microsoft.comwrote in message
news:BA**********************************@microsof t.com...
Hi,

I have a DataGrid, whose sourceI am exporting to Excel. This works fine
except for the Column ordering. My datasource is not a datatable, with a
typical
SELECT statement where I can select the column orders. Instead the
datasource
is a class which implements IList, containing a collection of my data. My
problem
again is that I don't know how to control the order of the columns that
are
exported.

Any Ideas?

Thanks

Sep 11 '06 #2
Opa
Jusin, Thanks for the reply.
Perhaps you misunderstand the question.
I am not trying to sort the rows by a specific column,
but rather I am trying to manipulate the ordering or
or columns. Get it?

Any Ideas?
"S. Justin Gengo" wrote:
Opa,

This isn't a solution but may lead you in the correct direction. You will
have to find a way to sort the columns. You may need to make a customized
sort. Which can be added on to your class. But here's a simple default
method:

public IList Sort(IList list)
{
((ArrayList)list).Sort();
return list;
}

Regards,

--
S. Justin Gengo
Web Developer / Programmer

Free code library:
http://www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"Opa" <Op*@discussions.microsoft.comwrote in message
news:BA**********************************@microsof t.com...
Hi,

I have a DataGrid, whose sourceI am exporting to Excel. This works fine
except for the Column ordering. My datasource is not a datatable, with a
typical
SELECT statement where I can select the column orders. Instead the
datasource
is a class which implements IList, containing a collection of my data. My
problem
again is that I don't know how to control the order of the columns that
are
exported.

Any Ideas?

Thanks


Sep 11 '06 #3

Opa wrote:
Jusin, Thanks for the reply.
Perhaps you misunderstand the question.
I am not trying to sort the rows by a specific column,
but rather I am trying to manipulate the ordering or
or columns. Get it?

Any Ideas?
"S. Justin Gengo" wrote:
Opa,

This isn't a solution but may lead you in the correct direction. You will
have to find a way to sort the columns. You may need to make a customized
sort. Which can be added on to your class. But here's a simple default
method:

public IList Sort(IList list)
{
((ArrayList)list).Sort();
return list;
}

Regards,

--
S. Justin Gengo
Web Developer / Programmer

Free code library:
http://www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"Opa" <Op*@discussions.microsoft.comwrote in message
news:BA**********************************@microsof t.com...
Hi,
>
I have a DataGrid, whose sourceI am exporting to Excel. This works fine
except for the Column ordering. My datasource is not a datatable, with a
typical
SELECT statement where I can select the column orders. Instead the
datasource
is a class which implements IList, containing a collection of my data. My
problem
again is that I don't know how to control the order of the columns that
are
exported.
>
Any Ideas?
>
Thanks
I'm not sure i follow either, but...
I persume your DataGrid is set to autogenerate columns.
Dont.
Define bound (or whatever) columns, in the order that you want.

hope this helps
-- a --

Sep 11 '06 #4
Opa

I tried setting AutoGenerateColumns to false and then adding a single column,
but this does not work. Funny thing is that it seems to ignore the
AutoGenerateColumns=false, and yet it generate all the columns anyway.
Any Ideas?

Here is my code:

public static void Convert(IList list, HttpResponse response, string fileName)
{
//first let's clean up the response.object
response.Clear();
response.Charset = string.Empty;

//This will make the browser interpret the output as an Excel file
response.AddHeader("Content-Disposition", "attachment; filename=" +
fileName + ".xls");

//set the response mime type for excel
response.ContentType = "application/vnd.ms-excel";
//create a string writer
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

//instantiate a datagrid and set the datagrid datasource to the dataset
passed in
DataGrid dg = new DataGrid();
dg.DataSource = list;
dg.DataBind();

dg.AutoGenerateColumns = false;

//DataGridColumnCollection colCol = new DataGridColumnCollection(gd,
cols);
BoundColumn col = new BoundColumn();
col.DataField = "WorkOrderNumber";
col.HeaderText= "Work Order #";
col.ItemStyle.ForeColor = System.Drawing.Color.Blue;

dg.Columns.Add(col);

//tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite);

response.Write("<B>" + fileName + "</B><BR/>");
response.Write("<B>" + DateTime.Now.ToString("F") + "</B><BR/><BR/>");

//all that's left is to output the html
response.Write(stringWrite.ToString());
response.End();
}


"addup" wrote:
>
Opa wrote:
Jusin, Thanks for the reply.
Perhaps you misunderstand the question.
I am not trying to sort the rows by a specific column,
but rather I am trying to manipulate the ordering or
or columns. Get it?

Any Ideas?
"S. Justin Gengo" wrote:
Opa,
>
This isn't a solution but may lead you in the correct direction. You will
have to find a way to sort the columns. You may need to make a customized
sort. Which can be added on to your class. But here's a simple default
method:
>
public IList Sort(IList list)
{
((ArrayList)list).Sort();
return list;
}
>
Regards,
>
--
S. Justin Gengo
Web Developer / Programmer
>
Free code library:
http://www.aboutfortunate.com
>
"Out of chaos comes order."
Nietzsche
>
>
"Opa" <Op*@discussions.microsoft.comwrote in message
news:BA**********************************@microsof t.com...
Hi,

I have a DataGrid, whose sourceI am exporting to Excel. This works fine
except for the Column ordering. My datasource is not a datatable, with a
typical
SELECT statement where I can select the column orders. Instead the
datasource
is a class which implements IList, containing a collection of my data. My
problem
again is that I don't know how to control the order of the columns that
are
exported.

Any Ideas?

Thanks

I'm not sure i follow either, but...
I persume your DataGrid is set to autogenerate columns.
Dont.
Define bound (or whatever) columns, in the order that you want.

hope this helps
-- a --

Sep 11 '06 #5

Opa wrote:
I tried setting AutoGenerateColumns to false and then adding a single column,
but this does not work. Funny thing is that it seems to ignore the
AutoGenerateColumns=false, and yet it generate all the columns anyway.
Any Ideas?

Here is my code:

public static void Convert(IList list, HttpResponse response, string fileName)
{
//first let's clean up the response.object
response.Clear();
response.Charset = string.Empty;

//This will make the browser interpret the output as an Excel file
response.AddHeader("Content-Disposition", "attachment; filename=" +
fileName + ".xls");

//set the response mime type for excel
response.ContentType = "application/vnd.ms-excel";
//create a string writer
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

//instantiate a datagrid and set the datagrid datasource to the dataset
passed in
DataGrid dg = new DataGrid();
dg.DataSource = list;
dg.DataBind();

dg.AutoGenerateColumns = false;

//DataGridColumnCollection colCol = new DataGridColumnCollection(gd,
cols);
BoundColumn col = new BoundColumn();
col.DataField = "WorkOrderNumber";
col.HeaderText= "Work Order #";
col.ItemStyle.ForeColor = System.Drawing.Color.Blue;

dg.Columns.Add(col);

//tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite);

response.Write("<B>" + fileName + "</B><BR/>");
response.Write("<B>" + DateTime.Now.ToString("F") + "</B><BR/><BR/>");

//all that's left is to output the html
response.Write(stringWrite.ToString());
response.End();
}


"addup" wrote:

Opa wrote:
Jusin, Thanks for the reply.
Perhaps you misunderstand the question.
I am not trying to sort the rows by a specific column,
but rather I am trying to manipulate the ordering or
or columns. Get it?
>
Any Ideas?
>
>
"S. Justin Gengo" wrote:
>
Opa,

This isn't a solution but may lead you in the correct direction. You will
have to find a way to sort the columns. You may need to make a customized
sort. Which can be added on to your class. But here's a simple default
method:

public IList Sort(IList list)
{
((ArrayList)list).Sort();
return list;
}

Regards,

--
S. Justin Gengo
Web Developer / Programmer

Free code library:
http://www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche


"Opa" <Op*@discussions.microsoft.comwrote in message
news:BA**********************************@microsof t.com...
Hi,
>
I have a DataGrid, whose sourceI am exporting to Excel. This works fine
except for the Column ordering. My datasource is not a datatable, with a
typical
SELECT statement where I can select the column orders. Instead the
datasource
is a class which implements IList, containing a collection of my data. My
problem
again is that I don't know how to control the order of the columns that
are
exported.
>
Any Ideas?
>
Thanks
I'm not sure i follow either, but...
I persume your DataGrid is set to autogenerate columns.
Dont.
Define bound (or whatever) columns, in the order that you want.

hope this helps
-- a --

That's because you're doing your dg.DataBind first.
Do your databind *after* setting up the columns

i.e. just before the dg.RenderControl

-- a --

Sep 11 '06 #6
Opa
That's it! I did overlook this.
Thanks for your help man.

Case closed!
"addup" wrote:
>
Opa wrote:
I tried setting AutoGenerateColumns to false and then adding a single column,
but this does not work. Funny thing is that it seems to ignore the
AutoGenerateColumns=false, and yet it generate all the columns anyway.
Any Ideas?

Here is my code:

public static void Convert(IList list, HttpResponse response, string fileName)
{
//first let's clean up the response.object
response.Clear();
response.Charset = string.Empty;

//This will make the browser interpret the output as an Excel file
response.AddHeader("Content-Disposition", "attachment; filename=" +
fileName + ".xls");

//set the response mime type for excel
response.ContentType = "application/vnd.ms-excel";
//create a string writer
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

//instantiate a datagrid and set the datagrid datasource to the dataset
passed in
DataGrid dg = new DataGrid();
dg.DataSource = list;
dg.DataBind();

dg.AutoGenerateColumns = false;

//DataGridColumnCollection colCol = new DataGridColumnCollection(gd,
cols);
BoundColumn col = new BoundColumn();
col.DataField = "WorkOrderNumber";
col.HeaderText= "Work Order #";
col.ItemStyle.ForeColor = System.Drawing.Color.Blue;

dg.Columns.Add(col);

//tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite);

response.Write("<B>" + fileName + "</B><BR/>");
response.Write("<B>" + DateTime.Now.ToString("F") + "</B><BR/><BR/>");

//all that's left is to output the html
response.Write(stringWrite.ToString());
response.End();
}


"addup" wrote:
>
Opa wrote:
Jusin, Thanks for the reply.
Perhaps you misunderstand the question.
I am not trying to sort the rows by a specific column,
but rather I am trying to manipulate the ordering or
or columns. Get it?

Any Ideas?


"S. Justin Gengo" wrote:

Opa,
>
This isn't a solution but may lead you in the correct direction. You will
have to find a way to sort the columns. You may need to make a customized
sort. Which can be added on to your class. But here's a simple default
method:
>
public IList Sort(IList list)
{
((ArrayList)list).Sort();
return list;
}
>
Regards,
>
--
S. Justin Gengo
Web Developer / Programmer
>
Free code library:
http://www.aboutfortunate.com
>
"Out of chaos comes order."
Nietzsche
>
>
"Opa" <Op*@discussions.microsoft.comwrote in message
news:BA**********************************@microsof t.com...
Hi,

I have a DataGrid, whose sourceI am exporting to Excel. This works fine
except for the Column ordering. My datasource is not a datatable, with a
typical
SELECT statement where I can select the column orders. Instead the
datasource
is a class which implements IList, containing a collection of my data. My
problem
again is that I don't know how to control the order of the columns that
are
exported.

Any Ideas?

Thanks
>
I'm not sure i follow either, but...
I persume your DataGrid is set to autogenerate columns.
Dont.
Define bound (or whatever) columns, in the order that you want.
>
hope this helps
-- a --


That's because you're doing your dg.DataBind first.
Do your databind *after* setting up the columns

i.e. just before the dg.RenderControl

-- a --

Sep 11 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Gal Zilberman | last post: by
6 posts views Thread by Kevin Humphreys | last post: by
21 posts views Thread by bobh | last post: by
4 posts views Thread by Tom | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.