ASP.NET Export repeater to excel, excel file open with error "The file format and extension of 'RepeaterExport.xls' don't match

Question

I am working on the export, to improve the performance so I'm exporting repeater data to excel.

This is my repeater aspx

 <asp:Repeater runat="server" ID="rpEmployee" DataSourceID="dsEmployee" OnItemDataBound="rpemployee_ItemDataBound">
    <HeaderTemplate>
        <table id="EmployeeTbl" class="display">
            <thead>
                <tr>
                    <th><input type="checkbox" onclick="SelectAll(this)" /></th>
                    <th align="left">Name</th>
                    <th>Origin Ctry</th>
                    <th>Passport No</th>
                    <th>Passport Expired On</th>
                    <th>Permit Expired On</th>
                    <th align="left">Operating Centre</th>
                    <th align="left">Kdn File</th>
                    <th>Submission Batch</th>
                    <th>Submitted To MyEG On</th>
                    <th>Status</th>
                    <th>Last Updated On</th>
                    <th>Payment Ref</th>
                </tr>
            </thead>
            <tbody>
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
            <td class="cmd">
                <asp:CheckBox runat="server" ID="chkSelect" />
                <asp:HiddenField runat="server" ID="EmployeeId" Value='<%#Eval("EmployeeId") %>' />
                <asp:HiddenField runat="server" ID="PPBatchEmployeeId" Value='<%#Eval("PPBatchEmployeeId") %>' />
            </td>
            <td><a href='../workers/FWBiodata.aspx?Id=<%#Eval("EmpRowGuid") %>'><%#Eval("EmployeeName")%> <span style="color: red"><%#(bool)(Eval("Employee.SLTag"))?"(SL)":"" %></span></a></td>
            <td class="cmd"><%#Eval("OriginCtry")%></td>
            <td class="cmd"><%#Eval("PassportNo")%></td>
            <td class="cmd"><%#AppLib.Format.Date(Eval("PassportExpireOn"))%></td>
            <td class="cmd"><%#AppLib.Format.Date(Eval("PermitExpireOn"))%></td>
            <td><%#Eval("OCName")%></td>
            <td><%#Eval("KdnFileNo") %></td>
            <td class="cmd"><%#Eval("PPDeliveryBatchId.ReferenceNo")%></td>
            <td class="cmd"><%#AppLib.Format.AppDateTime(Eval("SubmitToMyEGOn"))%></td>
            <td class="cmd"><%#Eval("PPStatus.Status") %></td>
            <td class="cmd"><%#AppLib.Format.AppDateTime(Eval("LastUpdatedOn"))%></td>
            <td><asp:Label ID="lblPymtReferenceNo" runat="server" Text="" /></td>
        </tr>
    </ItemTemplate>
    <FooterTemplate>
        </tbody>
        </table>
    </FooterTemplate>
</asp:Repeater>

here is my code behind.

protected void btnDownload_Click(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    foreach (RepeaterItem i in rpEmployee.Items)
    {
        HiddenField EmployeeId = (HiddenField)i.FindControl("EmployeeId");
        EmployeeId.Visible = false;
        HiddenField PPBatchEmployeeId = (HiddenField)i.FindControl("PPBatchEmployeeId");
        PPBatchEmployeeId.Visible = false;
        CheckBox chkitem = (CheckBox)i.FindControl("chkSelect");
        chkitem.Visible = false;
    }

    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=RepeaterExport.xls");
    //Response.Charset = "UTF-8";
    Response.Cache.SetCacheability(HttpCacheability.NoCache);  
    Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);
    rpEmployee.RenderControl(hw);
    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();
}

Everything seems ok, but when I open the excel. it shows this after I clicked Yes, then it works. When I pass to my friend's laptop and try open it, it have another error

I have no idea why. Anyone can help me?

score 0 · Accepted Answer · answered Feb 26 '21 at 04:47

0

are you sure it is a valid excel file? it looks html file to me... with xls extension!

either create a real/valid xml file or check this article from ms

XLS file will not open in Excel 2016, only gray view

answered Feb 26 '21 at 04:47

HainKurt

134
6

yeah. when i change it to .csv, it looks like HTML. is it means i need to change it to dataset first, after that only can be exported to excel? – Jessie Loke Feb 26 '21 at 06:48
it is HTML! it can be opened by browser... Excel is not opening HTML file and try to convert it to tabular data... You can convert your data to csv and use that one instead... Excel can open CSV, but not HTML! – HainKurt Feb 26 '21 at 07:36
How can i export it without those html tags? – Jessie Loke Feb 26 '21 at 07:36
you can use this solution to convert a datatable to csv... https://stackoverflow.com/questions/62300351/how-to-create-csv-string-from-datatable-in-vb-net – HainKurt Feb 26 '21 at 07:40
Do you have some other example in asp.net? – Jessie Loke Feb 26 '21 at 07:42
just search on goggle for "datattable to csv" and you will find lots of working code... copy paste and call it, get the csv as string, and use that one... here for example, there are lots of working code... https://stackoverflow.com/questions/4959722/how-can-i-turn-a-datatable-to-a-csv – HainKurt Feb 26 '21 at 07:55

ASP.NET Export repeater to excel, excel file open with error "The file format and extension of 'RepeaterExport.xls' don't match

1 Answers1