Download DataGrid to Excel

Question

User-531104354 posted

Hi,

I have an asp page where users can download datagrid to excel sheet. Its working fine but there is an issue. I have a char field on the grid which may or may not have zero's in front. The problem is when I download this sheet to excel, It ignore zero's. For example if its showing 01567 in a cell in the datagrid, it will download this number as 1567 to excel. How can I fix this issue? I need to download that number as 01567 with zero's to excel.

Thanks

Answer 1

User468813023 posted
I know that there are some style sheets possibilities for excel, when exporting a datagrid to excel. I currently dont have the code with me. I would have to check tommorow. However what you need to do is, to apply the style for the cell which has these numbers in it and append the style to the excel. I will post you an example tommorow. Here are some examples that you can start with.

Answer 2

User-872021455 posted

I haven't tried this out myself, but you can try adding a property to a span tag and include the text to display inside the tag:

<span STYLE='vnd.ms-excel.numberformat:@'> fieldVal </span>

It seems the vnd.ms-excel.numberformat:@ is the key to making this work.

Answer 3

User-531104354 posted

Thanks SonuKapoor, I tried that example from the URL, but it didn't work for me. Its still downloading 01567 as 1567. How can I fix it?

Thanks for your help.

Answer 4

User468813023 posted
1) Open the file which you have exported to excel via asp.net.
2) Format the cells where you want to display the leading zeros.
3) Save the file.
4) Open the file in notepad and see what style the cell uses.
5) Apply that style via the code to the cell. Re-run the application and see what it results.

Answer 5

User-531104354 posted

Hi,

I checked format in notepad, If I format excel cell as text, then its not loosing the zero's.

How can I set the style for that cell in .net code as text or string?

Thanks

Answer 6

User-531104354 posted
Anyone please?

Answer 7

User468813023 posted
You have to basically loop through the items in the datagrid and set the style to the individual cells. Here is an example:

Dim strStyle As String = "<style>.currency { mso-number-format:\0022$\0022\#\,\#\#0\.00; }\n\n"
strStyle = strStyle & ".dateformat { mso-number-format:short date; }"
strStyle = strStyle & " </style>"

Dim rowPos As Integer
For rowPos = 0 To ds.Tables(0).Rows.Count - 1
' Loop through the cols and set the horizontal alignment
Dim colPos As Integer
  For colPos = 0 To ds.Tables(0).Columns.Count() - 1
    dg.Items(rowPos).Cells(colPos).Attributes.Add("class", "currency")
  Next
Next

Hope it helps.

Answer 8

User-531104354 posted

Thanks SonuKapoor,

Here is what I tried

DataGrid1.DataBind()
Dim strStyle As String = "<style>.text { mso-number-format:Text; } </style>"
For intTemp As Integer = 1 To ds.Tables(0).Rows.Count - 1
   DataGrid1.Items(intTemp).Cells(0).Attributes.Add("class", "text")
Next

On the grid, it's displaying its displaying zero's as it was doing before using styles. But when user click on a button to download the datagrid to excel, it still ignoring zero's. Here is the code to download to excel 

Dim strFileName, strFilePath As String
Dim oStringWriter As New System.IO.StringWriter
Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)
Dim objStreamWriter As StreamWriter

strFileName = "EmployeeList.xls"
strFilePath = Server.MapPath(strFileName)
objStreamWriter = File.AppendText(strFilePath)
DataGrid1.RenderControl(oHtmlTextWriter)
objStreamWriter.WriteLine(oStringWriter.ToString())
objStreamWriter.Close()

So after downloading to excel, it's still downloading 001238 as 1238.

How can I fix it? Please help.

Answer 9

User468813023 posted
Please try:

mso-number-format:"\@"

Answer 10

User-531104354 posted
Thanks,  I tried

DataGrid1.DataBind()
Dim strStyle As String = "<style>.text { mso-number-format:\@; } </style>"
For intTemp As Integer = 1 To ds.Tables(0).Rows.Count - 1
   DataGrid1.Items(intTemp).Cells(0).Attributes.Add("class", "text")
Next

Its not working either, I tried to apply "curreny" styles to another column, its not making any difference either. I am wodering if I am missing something else.

Thanks for your help again.

Answer 11

User468813023 posted
My fault. I forgot that you have to print the style before the content, so that it takes affect. Its something like:

objStreamWriter.WriteLine(strStyle + oStringWriter.ToString())

Answer 12

User-531104354 posted

Thanks SonuKapoor for your great help, it worked for me this time.

Thanks again.

Answer 13

User43093768 posted

Is that possible that you can post the complete code for the Export Button. Thanks

Answer 14

User43093768 posted
I am doing the same thing but I am not getting the zeros. Can you help !

Here is the Page_Load code:

if(!Page.IsPostBack)
            {
                Database db = DatabaseFactory.CreateDatabase();
                DBCommandWrapper selectCommandWrapper = db.GetSqlStringCommandWrapper("SELECT * FROM tblStudent");
                DataSet ds = new DataSet();
                ds = db.ExecuteDataSet(selectCommandWrapper);
                DataGrid1.DataSource = ds;
                DataGrid1.DataBind();
                strStyle = "<style>.text{mso-number-format:\\@;}</style>";
                for(int i=0;i<ds.Tables[0].Rows.Count - 1 ; i++)
                {
                    DataGrid1.Items[i].Cells[2].Attributes.Add("class","text");
                }

And Here is the Export Button Code:

StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            StreamWriter ow;

            ow = File.AppendText(Server.MapPath("MyFile.xls"));
            DataGrid1.RenderControl(hw);
           
            //objStreamWriter.WriteLine(strStyle + oStringWriter.ToString())
            ow.WriteLine(strStyle + sw.ToString());
            ow.Close();

Answer 15

User43093768 posted
Ohh by the way Suno the code in this link: http://www.ms-inc.net/quickapps.aspx?ProductID=ExportDataSetToExcel

does not work with Excel 2002. No exception is thrown but when you open the file it just dont open.

Answer 16

User468813023 posted
Yeah, it works only with Excel 2003.

Answer 17

User43093768 posted
But the other code that I hvae posted earlier does not work either. Can you check what is wrong with that.

Thanks a lot for fast reply :D

Answer 18

User468813023 posted
Well, here is what you should try. Export the datagrid as it is to excel. Now open it in excel and you should see the column, which doesnt add the 0's at the beginning. Now change the entire column to a format so that it shows the 0's. Now save this file in the XML format. Not sure if Excel 2002 can save it as XML. Now open the XML file in notepad and check what style it has applied to the column. Use the same style in your code.

Cheers

Answer 19

User43093768 posted
I did the same thing and here is my xml file after adding zero. I had to change the column type manually in order to add a 0 in the excel file.

 <<Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="4" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:Index="2" ss:Width="30.75"/>
   <Column ss:Width="52.5"/>
   <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="String">StudentID</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">Name</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">SSN</Data></Cell>

So I guess I have to change my style like this in c#.
before String it was text. But still it does not work.
strStyle = @"<style>.String { mso-number-format:\@; } </style>";

Answer 20

User-531104354 posted

Hi azamsharp,

Here is what I did

Dim strFileName, strFilePath As String
Dim oStringWriter As New System.IO.StringWriter
Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)
Dim objStreamWriter As StreamWriter
Dim strStyle As String = "<style>.text2 { mso-number-format:\@; } </style>"
objStreamWriter = File.AppendText(strFilePath)
DataGrid1.RenderControl(oHtmlTextWriter)
objStreamWriter.WriteLine(strStyle)
objStreamWriter.WriteLine(oStringWriter.ToString())
objStreamWriter.Close()

hope it will help you!

Answer 21

User468813023 posted
Where is the number formatting? See what the Style S21 shows. Maybe Jus55 posts helps you!

Answer 22

User43093768 posted
  <Style ss:ID="s30">
   <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
     ss:Color="#FF8080"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
     ss:Color="#FF8080"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
     ss:Color="#FF8080"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
     ss:Color="#FF8080"/>
   </Borders>
   <Font ss:Color="#FFFFCC" ss:Bold="1"/>
   <Interior ss:Color="#800000" ss:Pattern="Solid"/>
   <NumberFormat ss:Format="000\-00\-0000"/>
  </Style>

So the syle is 000\-00\-0000 so I changed the line to:

string strStyle = @"<style>.text2 { mso-number-format:000\-00\-0000; } </style>"; 

but still it does not work !!!!

Answer 23

User43093768 posted
go it.
I was using

string strStyle = @"<style>.text2 { mso-number-format:000\-00\-0000; } </style>"; 

instead of using
string strStyle = @"<style>.text { mso-number-format:000\-00\-0000; } </style>";

Thanks a lot Suno and juss55.

Thanks a million once again :D

Answer 24

User43093768 posted
One problem.

The number is formatted as 000-00-0000 and I want it to be without dashes.

I will see if I can find a format for this format lets see.

Answer 25

User43093768 posted
okay I removed the dashes in the SSN.

I just changeed the expression back to

string strStyle = @"<style>.text { mso-number-format:\@; } </style>"; 

Answer 26

User43093768 posted
How can I pop a save dialog Box when saving the excel file.

using my code

Answer 27

User43093768 posted
okay Save Dialog Box Problem is also solved now.

Just added the style code to the htmlWriter object and thats it :D

Answer 28

User-285988996 posted
Have you tried this solution with 15000+ records?  We tried something like this and the memory went up very, very high....most of the time taking down the server.  Our server had 4 GB of memory!

Answer 29

User1416329745 posted
Mlogan,
You need DTS like yesterday but don't know it. lol

Answer 30

User-285988996 posted
Yes, I am using DTS now.  I am able to extract records, store them into Excel, upload the file into a DB....and it flies.  I was able to complete the solution in 1 1/2 days.  Now my problem is trying to call the DTS package from a Web Server that does not have access to this server.  I am going to try calling/executing the package like this.

User -->  Web App -->  Web Services Server -->  SQL Server DTS Package.

Answer 31

User1416329745 posted

Try these links for sample code and you can also use SQL Server Agent Job using xp_cmdshell to populate it but it require SQL Server Agent with Admin permissions to run.  Hope this helps.

http://www.sqlteam.com/item.asp?ItemID=19595

http://www.15seconds.com/issue/030909.htm

Answer 32

User1661778150 posted

Similar to Number fomat I wanted to do DateTime format. So that the text displayed same in all language versions of excel.  I am using Number format as [ENG][$-409]dd\-mmm\-yy;@
But it is not working and excel displays complete DateTime in numbers.

How can I achieve this?

Thanks and Regards,
Manu

Answer 33

User43093768 posted
Check out this article it will give you some idea !!

http://azamsharp.net/DataGridExport.htm

Answer 34

User1701577335 posted
I found a problem when I run the example.I use charset is "utf-8",but after I export the datagrid to excel,some coding is wrong . How can I resolve this program ? Thanks.

Answer 35

User1953996818 posted
Hey Guys,

My problem is that Text of the headers in my grid are multi-word. In my datagrid, I have done wrap = true. This prevents width of the column to increase. But when we are exporting to Excel, that wraping is not happening and the output has increased beyond the width of the screen and our user is not accepting it. I was trying to use STYLE or formatting to solve this problem but could not.

So now my question is how can we apply that formatting to the header of the grid in EXCEL. I'll appreciate your help.

Answer 36

User87127998 posted
Hi...
I have a problem while exporting data to excel. Once exported I want to Lock one of the cell...(Users should not be able to edit it....). Does some one know how to do it?
Thanks,
Gaurav

Answer 37

User-1260016562 posted
Hey guys, my export from datagrid to excel is working but it doesn't carry my column headers to the excel file. Any thoughts on why this is happening?  I have already posted on the forum and expect that article to show up soon.  All relevant code is included below including grid, column title setup and export event handler:

I am using bound columns by the way

<asp:datagrid id="dgAgentSummary" runat="server" Width="900px" BorderColor="Gray" BorderStyle="Solid" BorderWidth="1px" Height="166px" Visible="False" CellPadding="1" AutoGenerateColumns="False">
   <ItemStyle HorizontalAlign="Right" CssClass="NormalSmall"></ItemStyle>
   <HeaderStyle HorizontalAlign="Center" ForeColor="Black" CssClass="NormalSmallBold" VerticalAlign="Bottom" BackColor="LightGray"></HeaderStyle>
   <Columns>
    <asp:BoundColumn DataField="Resource_Name" />
    <asp:BoundColumn DataField="Calls_Per_Hour" />
    <asp:BoundColumn DataField="ACD" />
    <asp:BoundColumn DataField="OB" />
    <asp:BoundColumn DataField="RNA" />
    <asp:BoundColumn DataField="IB_AHT" />
    <asp:BoundColumn DataField="OB_AHT" />
    <asp:BoundColumn DataField="IO_AHT" />
    <asp:BoundColumn DataField="Transfer_Count" />
    <asp:BoundColumn DataField="AXferT" />
    <asp:BoundColumn DataField="AAHT" />
    <asp:BoundColumn DataField="Wrap" />
    <asp:BoundColumn DataField="Unav" />
    <asp:BoundColumn DataField="Unav_Pct" />
    <asp:BoundColumn DataField="Avail_Pct" />
    <asp:BoundColumn DataField="Idle_Time" />
    <asp:BoundColumn DataField="Idle_Pct" />
    <asp:BoundColumn DataField="Login_Time" />
   </Columns>
  </asp:datagrid>

code to build column headers:

Private Sub dgAgentSummary_ItemDataBound(ByVal sender As Object, _

ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgAgentSummary.ItemDataBound

If e.Item.ItemType = ListItemType.Header Then

Dim dgItemHeader As New DataGridItem(0, 0, ListItemType.Header)

Dim tcell As TableCell

Dim title As String

Dim Titles() As String = {"Agent", "Per Hr", "ACD", "OB", "RNA", "IB", "OB", _

"IB/OB", "Total", "Average Speed", "Adjusted AHT IB", "IB Wrap", "Unav", _

"Unav %", "Avail %", "Idle Time", "Idle%", "Login Time"}

tcell = New TableCell()

tcell.Controls.Add(New LiteralControl(""))

tcell.BackColor = System.Drawing.Color.MediumSeaGreen

tcell.ColumnSpan = 1

dgItemHeader.Cells.Add(tcell)

tcell = New TableCell()

tcell.Controls.Add(New LiteralControl("Calls"))

tcell.BackColor = System.Drawing.Color.MediumSeaGreen

tcell.ColumnSpan = 4

dgItemHeader.Cells.Add(tcell)

tcell = New TableCell()

tcell.Controls.Add(New LiteralControl("AHT"))

tcell.BackColor = System.Drawing.Color.MediumSeaGreen

tcell.ColumnSpan = 3

dgItemHeader.Cells.Add(tcell)

tcell = New TableCell()

tcell.Controls.Add(New LiteralControl("Inbound Transfer "))

tcell.BackColor = System.Drawing.Color.MediumSeaGreen

tcell.ColumnSpan = 3

dgItemHeader.Cells.Add(tcell)

tcell = New TableCell()

tcell.Controls.Add(New LiteralControl("Availability "))

tcell.BackColor = System.Drawing.Color.MediumSeaGreen

tcell.ColumnSpan = 6

dgItemHeader.Cells.Add(tcell)

tcell = New TableCell()

tcell.Controls.Add(New LiteralControl(""))

tcell.BackColor = System.Drawing.Color.MediumSeaGreen

tcell.ColumnSpan = 1

dgItemHeader.Cells.Add(tcell)

dgAgentSummary.Controls(0).Controls.Add(dgItemHeader)

' '*************** Create Second Header Row ********************

Dim CellNum As Integer

dgItemHeader = New DataGridItem(1, 0, ListItemType.Header)

For Each title In Titles

If title <> "Location" Or cbDisplayRegion.Checked Then

tcell = New TableCell()

tcell.Controls.Add(New LiteralControl("<a href='javascript:showHelp();'>" & title & "</a>"))

dgItemHeader.Cells.Add(tcell)

If CellNum >= 0 And _

CellNum <= 4 + Convert.ToInt32(cbDisplayRegion.Checked) Then

tcell.BackColor = System.Drawing.Color.MediumSeaGreen

ElseIf CellNum > 4 + Convert.ToInt32(cbDisplayRegion.Checked) And _

CellNum <= 7 + Convert.ToInt32(cbDisplayRegion.Checked) Then

tcell.BackColor = System.Drawing.Color.MediumSeaGreen

ElseIf CellNum > 7 + Convert.ToInt32(cbDisplayRegion.Checked) And _

CellNum <= 10 + Convert.ToInt32(cbDisplayRegion.Checked) Then

tcell.BackColor = System.Drawing.Color.MediumSeaGreen

ElseIf CellNum > 10 + Convert.ToInt32(cbDisplayRegion.Checked) And _

CellNum <= 18 + Convert.ToInt32(cbDisplayRegion.Checked) Then

tcell.BackColor = System.Drawing.Color.MediumSeaGreen

End If

CellNum = CellNum + 1

End If

Next

dgAgentSummary.Controls(0).Controls.Add(dgItemHeader)

dgAgentSummary.Controls(0).Controls.RemoveAt(0)

Else

' e.Item.Cells(2).Visible = cbDisplayRegion.Checked

' e.Item.Cells(0).Text = IIf(Val(e.Item.Cells(0).Text) > -1.0, GetHourDisplay(Val(e.Item.Cells(0).Text)), e.Item.Cells(0).Text)

' e.Item.Cells(0).HorizontalAlign = HorizontalAlign.Center

End If

End Sub

code to handle the export event:

Private Sub LoadExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadExcel.Click, btnLoadExcel2.Click

Dim tw As System.IO.StringWriter

Dim hw As System.Web.UI.HtmlTextWriter

' Set the content type to Excel.

Response.Clear()

Response.Buffer = True

Response.ContentType = "application/vnd.ms-excel"

Response.AddHeader("Content-Disposition", "attachment; filename=")

' Response.ContentEncoding = System.Text.Encoding.UTF7

' Me.dgAgentSummary.Controls.Clear()

 

' Remove the charset from the Content-Type header.

Response.Charset = ""

Me.EnableViewState = False

tw = New System.IO.StringWriter()

hw = New System.Web.UI.HtmlTextWriter(tw)

dgAgentSummary.RenderControl(hw)

' Write the HTML back to the browser.

Response.Write(_ConstructExcelHeader())

Response.Write(tw.ToString())

' End the response.

Response.End()

End Sub

Answer 38

User1388854476 posted
I have another sort of formatting issue when exporting a datagrid to excel as html.  I want to have line breaks between items in one of my cells.  So I am just putting <br> tags after each item in a list. 

However, when Excel opens this file, it actually puts each item in its own row, and merges the rows together for each other column.  This means that filtering and sorting don't work. 

As an example, let's say I have a row of data with columns A, B, and C.  Column C should have 2 or 3 strings in it separated by line breaks.  These should all be part of Row 1 in Excel.  Instead, Column C has one item each in Row 1, Row 2, and Row 3, while those 3 rows are merged into one cell in Column A and B.

I tried using a Chr(10) instead of a <br>, but Excel actually just ignores that, so there's no merging problem but now the items are all just running together in a big paragraph with no line breaks.

Any ideas?

Answer 39

User881553223 posted

hi azam

when i am using the code specified by u above..instead of the excel sheet opening the grid is displayed on the page itself(where as when i was using the response object the excel sheet was opening)...Can you please help..
the code in the event handler is as follows..thanks in anticipation.

string strStyle="<style>.text { mso-number-format:/@;}</style>";
            dg_calc_output.DataSource=ds_obj;
           
   dg_calc_output.DataBind();
   foreach(DataGridItem gridItem in dg_calc_output.Items)
   {
    for(int cols=0;cols<dg_calc_output.Columns.Count;cols++)
    {
     gridItem.Cells[cols].Attributes.Add("class", "text");

    }
   }

string strFileName, strFilePath ;
StringWriter oStringWriter =new StringWriter();
HtmlTextWriter oHtmlTextWriter=new HtmlTextWriter(oStringWriter);
StreamWriter objStreamWriter ;

strFileName ="EmployeeList.xls";
strFilePath = Server.MapPath(strFileName);
objStreamWriter =File.AppendText(strFilePath);
dg_calc_output.RenderControl(oHtmlTextWriter);
objStreamWriter.WriteLine(strStyle + oStringWriter.ToString());
objStreamWriter.Close();

 

Answer 40

User546194788 posted

I used code bellow (from Microsoft how to) to export datagrid to excel. Order number (text)

displays in datagrid is ok but in Excel become science number. For example, 200301170066702

become 2.00301E+14. Can someone help me fix it? Thank you.

-----------------------------

Dim objConn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings(MYWEBCONFIG))

objConn.Open()

Dim strSQL As String = "select * from order"

Dim objDataset As New DataSet

Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter

objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, objConn)

' Fill the dataset.

objAdapter.Fill(objDataset)

' Create a new view.

Dim oView As New DataView(objDataset.Tables(0))

' Set up the data grid and bind the data.

Me.DataGrid1.DataSource = oView

Me.DataGrid1.DataBind()

' Verify if the page is to be displayed in Excel.

If Request.QueryString("bExcel") = "1" Then

' Set the content type to Excel.

Response.ContentType = "application/vnd.ms-excel"

' Remove the charset from the Content-Type header.

Response.Charset = ""

' Turn off the view state.

Me.EnableViewState = False

Dim tw As New System.IO.StringWriter

Dim hw As New System.Web.UI.HtmlTextWriter(tw)

' Get the HTML for the control.

Me.DataGrid1.RenderControl(hw)

' Write the HTML back to the browser.

Response.Write(tw.ToString())

' End the response.

Response.End()

End If

Answer 41

User-1188510213 posted

Hello Sonu,

I have read ur comments regarding the formatting issue while exporting into Excel. Here i am exporting the data from winform and i want to preserves the formatting (e.g. '0001' should be exported as '0001').

Shall it be possible in winform?

 

thanks

Amrendra

 

Answer 42

User-1967231171 posted

Hi,

i am having the same problem using the following code:

protected void Button3_Click(object sender, System.EventArgs e)

{

//export to excel

Response.Clear();

Response.AddHeader("content-disposition", "attachment;filename=Leads.xls");

Response.Charset = "";

// If you want the option to open the Excel file without saving than

// comment out the line below

// Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = "application/vnd.xls";

System.IO.StringWriter stringWrite = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

GridView1.RenderControl(htmlWrite);

Response.Write(stringWrite.ToString());

Response.End();

}

public override void VerifyRenderingInServerForm(Control control)

{

// Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.

}

Any Help would be greatly appreciated,

Doug

Answer 43

User-1967231171 posted

Hi,

I am having the same issue with the following code:

protected void Button3_Click(object sender, System.EventArgs e)

{

//export to excel

Response.Clear();

Response.AddHeader("content-disposition", "attachment;filename=Leads.xls");

Response.Charset = "";

// If you want the option to open the Excel file without saving than

// comment out the line below

// Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = "application/vnd.xls";

System.IO.StringWriter stringWrite = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

GridView1.RenderControl(htmlWrite);

Response.Write(stringWrite.ToString());

Response.End();

}

public override void VerifyRenderingInServerForm(Control control)

{

// Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.

}

Any solutions?

Thanks,

Doug

Answer 44

User1416329745 posted

I don't know about Asp.net 2.0 but the code in the thread below works for Asp.net 1.1 datagrid.  Hope this helps.

http://forums.asp.net/921410/ShowPost.aspx

Answer 45

User-1967231171 posted

Juss,

I am trying to export to excel using the code on gridview guy and am hving trouble with leading 0's. I saw your code in vb but wondered if it was available in c#.

Thanks,

Doug

Answer 46

User-1967231171 posted

Sonu,

Do you have a c# solution for this issue?

I am using the following code:

protected void Button3_Click(object sender, System.EventArgs e)

{

//export to excel

Response.Clear();

Response.AddHeader("content-disposition", "attachment;filename=Leads.xls");

Response.Charset = "";

// If you want the option to open the Excel file without saving than

// comment out the line below

// Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = "application/vnd.xls";

System.IO.StringWriter stringWrite = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

GridView1.RenderControl(htmlWrite);

Response.Write(stringWrite.ToString());

Response.End();

}

public override void VerifyRenderingInServerForm(Control control)

{

// Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.

}

Thanks,

Doug

Answer 47

User-1557944714 posted

Hi Sonu

I have a similar problem as posted in this thread, My datagrid has a cost column. When I export it in excel, half of the cost column is displayed as numeric but other half cms out as text, so if I add the column vakues there is always as error. How can I force the frmatting on that column.

I have tried the code like this:

DataGrid1.DataSource = dt;

DataGrid1.DataBind();

strStyle = "<style>.currency { mso-number-format:\0022$\0022#,##0.00} </style>";

foreach (DataGridItem item in DataGrid1.Items)

{

item.Cells[_costsCol].Attributes.Add("class", "text");

}

The [_costsCol] = 11 is the column no. in my grid. Also can the style be anything other than currency. What does "ex" stands for?

The Export button code is as below:

private void Export_Click(object sender, System.EventArgs e)

{

string strFilePath ;

System.IO.StringWriter oStringWriter= new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter oHtmlTextWriter= new System.Web.UI.HtmlTextWriter(oStringWriter);

System.IO.StreamWriter objStreamWriter;

string strFileName = "EmployeeList.xls";

strFilePath = Server.MapPath(strFileName);

objStreamWriter = File.AppendText(strFilePath);

DataGrid1.RenderControl(oHtmlTextWriter);

objStreamWriter.WriteLine(strStyle + oStringWriter.ToString());

objStreamWriter.Close();

}

Please help ASAP!!

 

Answer 48

User1034563799 posted

I am using this code to export my datagrid to excel but I can't get fix the "leading zero" problem.  What am I missing something here?

Dim strStyle As String = "<style> .text { mso-number-format:\@; } </style>"

' Set the content type to Excel.

Response.ContentType = "application/vnd.ms-excel"

' Remove the charset from the Content-Type header.

Response.Charset = ""

' Turn off the view state.

Me.EnableViewState = False

Dim tw As New System.IO.StringWriter

Dim hw As New System.Web.UI.HtmlTextWriter(tw)

Dim dg As New DataGrid

dg.DataSource = dgIssuesList.DataSource

dg.DataBind()

' Get the HTML for the control.

dg.RenderControl(hw)

' Write the HTML back to the browser.

Response.Write(strStyle)

Response.Write(tw.ToString())

' End the response.

Response.End()

Answer 49

User43093768 posted

Hi,

I wrote a detailed artile about GridView export to excel which you can view at the following link:

http://gridviewguy.com/ArticleDetails.aspx?articleID=197

You can get some idea for the Datagrid export.

 

 

 

Answer 50

User468813023 posted
I suggest that you guys take a look at aspose.cells, I have good knowledge about exporting data to excel, however I do not think that it should be done via XML. The reason is that if you lots of data, then the xml files become very huge. I have seen it and I am not using the xml style anymore.

Answer 51

User-147732714 posted

I'm using vs.net  and asp.net2.0 

I tried the following  code 

Dim

strStyle As String = "<style>.currency { mso-number-format:\0022$\0022\#\,\#\#0\.00; }\n\n"
strStyle = strStyle & ".dateformat { mso-number-format:short date; }"
strStyle = strStyle & " </style>"

Dim rowPos As Integer
For rowPos = 0 To ds.Tables(0).Rows.Count - 1
' Loop through the cols and set the horizontal alignment
Dim colPos As Integer
  For colPos = 0 To ds.Tables(0).Columns.Count() - 1
    dg.Items(rowPos).Cells(colPos).Attributes.Add("class", "currency")
  Next
Next

 

but I'm getting that ds is not defined .   I tried to put gridview1 instead but still it give error on the line

any help for that.

Answer 52

User2015550812 posted

 I know this a pretty old post but I just foind it.  I adapted this code to format all cells as a string and didn't do any looping.  For my needs, this ended up being much faster...  I'm just passing this along in case it helps someone:

 

 Instead of just a normal "Response.Write(sw.ToString());" , I replaced it with this:

        string StyleAsText = @"&lt;style> .StyleAsText { mso-number-format:\@; } </style> ";
        Response.Write(StyleAsText + sw.ToString().Replace("&lt;td>", "&lt;td class='StyleAsText'>"));

Good luck!

A. Soong 

  

Answer 53

User2015550812 posted

 D-oh!  Change all of the &lt's to regulat less than symbols in the previous post and you should be good to go.

Answer 54

User1613545115 posted

@asoong 

Thanks buddy, I've been looking for a solution that would help with the "leading zeros" and this is perfect.

My final solution

1    using System;
2    using System.Collections.Generic;
3    using System.Data;
4    using System.IO;
5    using System.Web;
6    using System.Web.UI;
7    using System.Web.UI.WebControls;
8    
9    
10   /// <summary>
11   /// Summary description for ManageData_ExcelExport
12   /// Created by MFB 26-May-2009
13   /// ----------------------------------
14   /// This does the following:
15   ///  1) Exports Dataset to Spreadsheet
16   ///  2) Convert Numbers to Text eg. 000013456
17   /// </summary>
18   public class ManageData_ExcelExport
19   {
20       public void ExcelExport(DataSet ds, string fn)
21       {
22           HttpResponse response = HttpContext.Current.Response;
23   
24           // first let's clean up the response.object
25           response.Clear();
26           response.Charset = "";
27           response.Buffer = true; 
28   
29           // set the response mime type for excel
30           response.ContentType = "application/vnd.ms-excel";
31           response.AddHeader("Content-Disposition", "attachment;filename=\"" + fn + "\"");
32   
33           // create a string writer
34           using (StringWriter sw = new StringWriter())
35           {
36               using (HtmlTextWriter htw = new HtmlTextWriter(sw))
37               {
38                   //// instantiate a datagrid
39                   //DataGrid dg = new DataGrid();
40                   //dg.DataSource = ds.Tables[0];
41                   //dg.DataBind();
42   
43                   //Create a dummy GridView 
44                   GridView GridView1 = new GridView();
45                   GridView1.AllowPaging = false;
46                   GridView1.DataSource = ds;
47                   GridView1.DataBind();
48   
49                   for (int i = 0; i < GridView1.Rows.Count; i++)
50                   {
51                       //Apply text style to each Row 
52                       GridView1.Rows[i].Attributes.Add("class", "textmode");
53                   } 
54   
55   
56                   GridView1.RenderControl(htw);
57   
58                   //style to format numbers to string 
59                   string StyleAsText = @"<style> .StyleAsText { mso-number-format:\@; } </style> ";
60                   response.Write(StyleAsText + sw.ToString().Replace("<td>", "<td class='StyleAsText'>"));
61                   response.Flush();
62                   response.End(); 
63               }
64           }
65       }
66   }
67   

 

Answer 55

User2015550812 posted

@superbDotNetDeveloper

Cool - glad this helped.  BTW, I think you can get rid of your for loop (lines 49 - 53) if that's just left over from AzamSharp's sample.  The .StyleAsText class you're assigning should do the trick.  At least - it did for my needs.

 FYI, if any of your data fields are datatype 'text' - any null fields may render as a weird character (capital A with umlat(?) over it - that sideways colon thing).  Had to do a nasty cast/replace in my proc to get rid of it.  If anyone knows of a better way to adjust for this, please share.

Thanks

A. Soong

Answer 56

User1613545115 posted

@asoong

Thanks for the advice around the looping. The StyleAsText works well i must say. [:D]

i initially i used a solution which was created by Tim Mackey (http://tim.mackey.ie/HowtoExportADatasetToExcelCAspnet.aspx) but then instead of using DataGrid i change it to GridView after going through Mudassar Khan's solution (http://www.aspsnippets.com/post/2009/05/20/Export-DataSet-or-DataTable-to-Word-Excel-PDF-and-CSV-Formats.aspx).

The Data which I'm exporting is User Data therefore there are no null records so i haven't come across this scenario yet. but if i find something i will let you know.

if i may ask how long have u been using ASP.NET for now?

happy programming [:D]

 

Answer 57

User2015550812 posted

@superbDotNetDeveloper

 Thanks for the links.  Responded to your other question in a pm.

Thanks

A. Soong