I have a piece of code which generates an excel by first creating a GridView table, and then, from that table, create the excel. Problem is, excel uses the formatting from the OS region setting, which in my case uses , instead of . for decimals. That means that in the excel, most values which are numbers with 2 decimals are auto converted to dates because excel :/
How can I set the format for the columns in my excel which are affected? Some have to be Number, others Text. Anything I found and tried failed.
protected void btn_excel_Click(object sender, ImageClickEventArgs e)
{
foreach (GridViewRow r in grd_viabil.Rows)
{
//tried to write code here to set column formats where necessary
}
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/xml";//vnd.ms-excel
Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", "Nivel interventie"));
Response.Charset = "";
System.IO.StringWriter stringwriter = new System.IO.StringWriter();
HtmlTextWriter htmlwriter = new HtmlTextWriter(stringwriter);
//grd_viabil is the GridView
grd_viabil.RenderControl(htmlwriter);
Response.Write(stringwriter.ToString());
Response.End();
}
As for the GridView itself, it's like this :
<div class="viabil_excel">
<asp:ImageButton ID="img_export_excel" ImageUrl="Content/images/excel.png" CssClass="img_excel" runat="server" OnClick="btn_excel_Click" UseSubmitBehavior="False" />
</div>
<div class="datagrid2" style="overflow-y: auto; overflow-x: auto; height:650px;">
<asp:GridView ID="grd_viabil" runat="server" CellPadding="3" AutoGenerateColumns="False" DataKeyNames="ID_POD" ForeColor="Black"
GridLines="Both" AlternatingRowStyle-BackColor="#EEEEEE" RowStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" RowStyle-Height="3px" Font-Size="14px" HeaderStyle-Wrap="False">
<Columns >
<asp:TemplateField HeaderText="NR." SortExpression="ID_POD" HeaderStyle-BackColor ="#EEEEEE" ControlStyle-Font-Size="12px" HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Middle">
<ItemTemplate> <asp:Label ID="lbl_ID" runat="server" Text='<%#Container.DataItemIndex + 1%>'></asp:Label></ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ID POD" SortExpression="ID_POD" HeaderStyle-BackColor ="#EEEEEE" ControlStyle-Font-Size="12px" HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Middle">
<ItemTemplate> <asp:Label ID="lbl_BRIDGE_ID" runat="server" Text='<%# Bind("ID_POD") %>'></asp:Label></ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Lumina" SortExpression="ID_POD" HeaderStyle-BackColor ="#EEEEEE" ControlStyle-Font-Size="12px" HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Middle">
<ItemTemplate> <asp:Label ID="lbl_lumina" runat="server" Text='<%# Bind("lumina") %>'></asp:Label></ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="LungimeTotala" SortExpression="ID_POD" HeaderStyle-BackColor ="#EEEEEE" ControlStyle-Font-Size="12px" HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Middle">
<ItemTemplate> <asp:Label ID="lbl_lungime" runat="server" Text='<%# Bind("LungPod") %>'></asp:Label></ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="LungSupra" SortExpression="ID_POD" HeaderStyle-BackColor ="#EEEEEE" ControlStyle-Font-Size="12px" HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Middle">
<ItemTemplate> <asp:Label ID="lbl_lsupra" runat="server" Text='<%# Bind("LungSupra") %>'></asp:Label></ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
The Items lbl_lumina, lbl_lungime, and lbl_lsupra are the ones getting messed up, where lbl_lumina should be Text, and the other two Number. All three get converted to dates due to their values.
Given the code I inherited, how could I edit the formatting of the three columns in my code to set them to Text and Number respectively.
And before it is asked, yes I have changed my Region to UK for ex to see if it works with other countries. It does, but since the excel will get passed around after being downloaded, and those computers will be set to my Region, it needs to be done in code. Thanks