none
Problems processing an Excel file with asp.net RRS feed

  • Question

  • I am receiving the following error message:

    Error Message: System.Threading.ThreadAbortException: Thread was being aborted.

    at System.Threading.Thread.AbortInternal()

    at System.Threading.Thread.Abort(Object stateInfo)

    at System.Web.HttpResponse.End()

    at System.Web.HttpResponse.Redirect(String url, Boolean endResponse)

    at System.Web.HttpResponse.Redirect(String url)

    at GenericFunctions.ProcessError(String sSQL, String sErrMsg, String sWebPage, String sFunction, String sUser, SqlCommand objSqlCommand, Int32 nErrMsgID)

    at DefineEmailList.UpdateRecipientTable(String saveFileLocation)

    at DefineEmailList.btnUpload_Click(Object sender, EventArgs e)

     

    Here is the code used to process the file:

            Dim myConnection As SqlConnection
            Dim mySqlDataAdapter As SqlDataAdapter
            Dim GF As New GenericFunctions
            Dim x As Integer
            Dim nUserID As Integer

            ' Create connection string variable. Modify the "Data Source" parameter as
            ' appropriate for your environment.
            Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                        & "Data Source=" & saveFileLocation _
                        & ";" & "Extended Properties=Excel 8.0;"
            Dim objConn As New OleDbConnection(sConnectionString)

            Try
                ''==============================================================
                ''= BEGIN Read Excel file and store into a GridView
                ''==============================================================
                saveFileLocation = Replace(saveFileLocation, "\\", "\")

                ' Create the connection object by using the preceding connection string.
                ' Open connection with the database.
                objConn.Open()

                ' The code to follow uses a SQL SELECT command to display the data from the worksheet.
                ' Create new OleDbCommand to return data from worksheet.
                Dim objCmdSelect As New OleDbCommand("SELECT * FROM [Sheet1$] where email <> """"", objConn)

                ' Create new OleDbDataAdapter that is used to build a DataSet
                ' based on the preceding SQL SELECT statement.
                Dim objAdapter1 As New OleDbDataAdapter()

                ' Pass the Select command to the adapter.
                objAdapter1.SelectCommand = objCmdSelect

                ' Create new DataSet to hold information from the worksheet.
                Dim objDataset1 As New DataSet()

                At this line I get the error message I have provided at the bottom of this post
                ' Fill the DataSet with the information from the worksheet.
                objAdapter1.Fill(objDataset1, "XLData")

                ' Build a table from the original data.
                ExcelUploadRecipients.DataSource = objDataset1.Tables(0).DefaultView
                ExcelUploadRecipients.DataBind()


                ' Clean up objects.
                objConn.Close()
                objConn.Dispose()

    Here is the process:

    1) User accesses an asp.net web page

    2) They click on Browse button and find the XLS file they want to upload and then click an upload button

    3) The XLS file is copied to our server

    4) The asp.net page then runs the code above to read the XLS file

     

    Notes:

    1) All our users in the United States have not had any problems with this process

    2) Our users over in China & Korea have been getting the error message listed at the top of this post

    3) If I copy the file that one of our China/Korea users have uploaded, I get the same error message listed at the top of this post.

    4) If I open one of the files that our China/Korea users have uploaded and then just save it as a new file and then upload the new file, everything works just fine.

    5) I have confirmed that our China/Korea users are using the same version of Excel as myself.

     

    If needed I can provide examples of the Excel files that do not work and ones that do work.

     

    Any suggestions would be greatly appreciated.

    Thursday, August 9, 2007 1:30 PM

Answers

  • What I am not understanding, is the example you provided is taking data from a datagrid and exporting it to an Excel file, but this is not what I am trying to do.

     

    I have an Excel file that is on the server and I am trying to read the contents and load it into a datagrid.

     

    Monday, August 13, 2007 5:20 PM

All replies

  • I am assuming you are missing the line of code below because without that you cannot render Excel to none English countries.  A user have asked me before why Excel uses UTF7 instead of UTF8 I don't know and I don't know why it is affecting your Response.Write.  Hope this helps.

     

    Response.ContentEncoding = System.Text.Encoding.UTF7

     

    Thursday, August 9, 2007 1:41 PM
  • I had the following lines of code on my page so as to be able to support 2-bit character sets:

     

    <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="DefineEmailList.aspx.vb" Inherits="DefineEmailList" title="Untitled Page" CodePage="65001" %>

    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

     

    When I updated the page to use CodePage="65000" and charset=utf-7, when I would:

    1) Click on the browse button

    2) Select a file to upload

    3) Click on the upload button

    4) The page seemed to just refresh and not call the button click event

     

    Once I changed the code back to the original, everything worked fine.

     

    I guess I must be forgetting to set something, but I don't know what?????

    Friday, August 10, 2007 6:35 PM
  • If it works just remember to save the file as Japanese so you don't runtime character conversions.

     

    Friday, August 10, 2007 6:39 PM
  • If I set the page to use UTF-8, I get the following error message:

    Error Message: System.Threading.ThreadAbortException: Thread was being aborted.

    at System.Threading.Thread.AbortInternal()

    at System.Threading.Thread.Abort(Object stateInfo)

    at System.Web.HttpResponse.End()

    at System.Web.HttpResponse.Redirect(String url, Boolean endResponse)

    at System.Web.HttpResponse.Redirect(String url)

    at GenericFunctions.ProcessError(String sSQL, String sErrMsg, String sWebPage, String sFunction, String sUser, SqlCommand objSqlCommand, Int32 nErrMsgID)

    at DefineEmailList.UpdateRecipientTable(String saveFileLocation)

    at DefineEmailList.btnUpload_Click(Object sender, EventArgs e)

     

     

    If I set the page to use UTF-7, The page just refreshes and does not call the buttons on click event.

    So no code is executed. No sure why this is happening.

    Friday, August 10, 2007 8:18 PM
  • (<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="DefineEmailList.aspx.vb" Inherits="DefineEmailList" title="Untitled Page" CodePage="65001" %>

    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> )

     

     

    I see a master page, email list and then your Excel I think you need to create separate files for all and I have found two new office code see if you can use them instead of the one you are running.  And if you are using Office 2007 check the MSDN code samples for Excel 2007 Asp.net samples.  Hope this helps.

     

    http://www.aspnettutorials.com/tutorials/file/gv-word-aspnet2-csharp.aspx

     

    http://www.dotnetjohn.com/articles.aspx?articleid=231

     

     

     

    Saturday, August 11, 2007 1:41 AM
  • The links you provided are to take data from a gridview and export the data into Word.

    I am trying to read an Excel file and load the contents into a GridView.

    I 1st use the ASP.Net code to upload the file to the server.

    Then I read the Excel file from the server and load the data into a Grid View.

     

    Is there sample code that shows how to read an Excel (any version: Korean, Japanese, Chinese, English, etc) file and import the data into a grid view?

    Monday, August 13, 2007 2:23 PM
  • I did not explain to you because I assumed you know Microsoft restricts what you can do with Office applications in a web application so all you have to do is change Word to Excel in the first link and you should have just added the encoding line in the second link.  Encoding may be just one of your problem.  I have made some modification to the codes in the links I posted, it will help you make the changes needed for the Excel part of your code but it is not complete.

     

     

    Code from first link:

     

      protected void Button1_Click(object sender, EventArgs e)
    {
    Response.Clear();
    Response.Buffer = false;

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

    Response.ContentEncoding = System.Text.Encoding.UTF7;
    Response.ContentType = "application/vnd.excel";
    System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
    this.GridView1.RenderControl(oHtmlTextWriter);
    Response.Output.Write(oStringWriter.ToString());
    Response.Flush();
    Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    { }

     

    Code from second link:

     

     

    using System;

    using System.Data;

    using System.Configuration;

    using System.Collections;

    using System.Web;

    using System.Web.Security;

    using System.Web.UI;

    using System.Web.UI.WebControls;

    using System.Web.UI.WebControls.WebParts;

    using System.Web.UI.HtmlControls;

    using System.IO;

     

    public partial class GridViewToExcel : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            VerifyRenderingInServerForm ( form1 );

            gvToExport.GridLines = GridLines.Both;

        }

     

        protected void btnExport_Click ( object sender, EventArgs e )

        {

            Response.Clear ( );

            Response.AddHeader ( "content-disposition", "attachment;filename=FileName.xls" );
           
            Response.ContentEncoding = System.Text.Encoding.UTF7;

            Response.Charset = "";

     

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

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

            HtmlTextWriter HtmlTextWriter = new HtmlTextWriter ( StringWriter );

            gvToExport.RenderControl ( HtmlTextWriter );

            Response.Write ( StringWriter.ToString ( ) );

            Response.End ( );

        }

     

        public override void VerifyRenderingInServerForm ( Control control )

        {

            /* Verifies that a Form control was rendered */

     

     

     

    Monday, August 13, 2007 3:45 PM
  • Dear,

     

    Will you please send me the Excel file on which you are executing your code. I think that i have got the soloution but i want to check first.

     

    Regards,

    Wasif Ahmad

    Monday, August 13, 2007 4:31 PM
  • What I am not understanding, is the example you provided is taking data from a datagrid and exporting it to an Excel file, but this is not what I am trying to do.

     

    I have an Excel file that is on the server and I am trying to read the contents and load it into a datagrid.

     

    Monday, August 13, 2007 5:20 PM