locked
large excel data import into asp.net based application RRS feed

  • Question

  • User1842858928 posted

    Hi All!

    Please suggest me what would be the right solution for following situation !

    i have an asp.net application already developed for a financial domain, now client needs an functionality of importing large excel data (60,000 rows) into that application, also there would be some validation and column mapping during the import process.

    what i am thinking..:
    1) we can't import such a huge file using asp.net page, we may face browser time out problem.
    2) async way of importing data may be possible ?
    3) we should think of wrting a separate winform application to perform data import, and link that from existing asp.net application.
    4) write a separate winform application to perform data import, and one click deploy with asp.net app.
    5) using sqlBulk copy class may be helpful!

    please help me to design an appropriate solution for this situation.

    many thanks in advance,
    Arindam

     

     

     

     

     


     

    Tuesday, November 6, 2012 7:56 AM

All replies

  • User1221318268 posted

    USE THE BELOW CODE:

    DataTable dtExcel = new DataTable();
    string SourceConstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filename + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
    OleDbConnection con = new OleDbConnection(SourceConstr);
    Query = "Select * from [Sheet1$]";
    OleDbDataAdapter data = new OleDbDataAdapter(Query, con);
    data.Fill(dtExcel);

    Friday, December 28, 2012 6:13 AM
  • User1027872733 posted

    populate dtList via some means(dataset)

    protected void btnExportToExcel_Click(object sender, EventArgs e)
        {
            try
            {
                DataTable dt1 = (DataTable)ViewState["dtList"];
                if (dt1 == null)
                {
                    throw new Exception("No Records to Export");
                }
                string Path = "D:\\ImportExcelFromDatabase\\myexcelfile_" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + ".xls";
                FileInfo FI = new FileInfo(Path);
                StringWriter stringWriter = new StringWriter();
                HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
                DataGrid DataGrd = new DataGrid();
                DataGrd.DataSource = dt1;
                DataGrd.DataBind();

                DataGrd.RenderControl(htmlWrite);
                string directory = Path.Substring(0, Path.LastIndexOf("\\"));// GetDirectory(Path);
                if (!Directory.Exists(directory))
                {
                    Directory.CreateDirectory(directory);
                }

                System.IO.StreamWriter vw = new System.IO.StreamWriter(Path, true);
                stringWriter.ToString().Normalize();
                vw.Write(stringWriter.ToString());
                vw.Flush();
                vw.Close();
                WriteAttachment(FI.Name, "application/vnd.ms-excel", stringWriter.ToString());
            }
            catch (Exception ex)
            {
                //throw new Exception(ex.Message);
            }
        }

        public static void WriteAttachment(string FileName, string FileType, string content)
        {
            HttpResponse Response = System.Web.HttpContext.Current.Response;
            Response.ClearHeaders();
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + FileName);
            Response.ContentType = FileType;
            Response.Write(content);
            Response.End();

        }

    Friday, December 28, 2012 6:21 AM