locked
Read Excel to ASP.NET MCV RRS feed

  • Question

  • I new in this topic .Start learning .What I want to do is read Excel file in my Desktop to web pages.

    This code just take from some site.When Run it error with message "HTTP 404" Not found.

    1. HomeController code.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using Test_WebMCV3.Models;
    
    namespace Test_WebMCV3.Controllers
    {
        public class HomeController : Controller
        {
    
            public ActionResult ImportExcel()
            {
    
    
                return View();
            }
            [ActionName("Importexcel")]
            [HttpPost]
            public ActionResult Importexcel1()
            {
    
    
                if (Request.Files["FileUpload1"].ContentLength > 0)
                {
                    string extension = System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName).ToLower();
                    string query = null;
                    string connString = "";
    
    
    
    
                    string[] validFileTypes = { ".xls", ".xlsx", ".csv" };
    
                    string path1 = string.Format("{0}/{1}", Server.MapPath("~/first.xlsx"), Request.Files["FileUpload1"].FileName);
                    if (!Directory.Exists(path1))
                    {
                        Directory.CreateDirectory(Server.MapPath("~/App_Data/first.xlsx"));
                    }
                    if (validFileTypes.Contains(extension))
                    {
                        if (System.IO.File.Exists(path1))
                        { System.IO.File.Delete(path1); }
                        Request.Files["FileUpload1"].SaveAs(path1);
                        if (extension == ".csv")
                        {
                            DataTable dt = Utility.ConvertCSVtoDataTable(path1);
                            ViewBag.Data = dt;
                        }
                        //Connection String to Excel Workbook  
                        else if (extension.Trim() == ".xls")
                        {
                            connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path1 + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                            DataTable dt = Utility.ConvertXSLXtoDataTable(path1, connString);
                            ViewBag.Data = dt;
                        }
                        else if (extension.Trim() == ".xlsx")
                        {
                            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                            DataTable dt = Utility.ConvertXSLXtoDataTable(path1, connString);
                            ViewBag.Data = dt;
                        }
    
                    }
                    else
                    {
                        ViewBag.Error = "Please Upload Files in .xls, .xlsx or .csv format";
    
                    }
    
                }
    
                return View();
            }
    
    
        }
    }


    2.Models Utility

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Linq;
    using System.Web;
    using System.Data.OleDb;
    
    namespace Test_WebMCV3.Models
    {
        public class Utility
        {
            public static DataTable ConvertCSVtoDataTable(string strFilePath)
            {
                DataTable dt = new DataTable();
                using (StreamReader sr = new StreamReader(strFilePath))
                {
                    string[] headers = sr.ReadLine().Split(',');
                    foreach (string header in headers)
                    {
                        dt.Columns.Add(header);
                    }
    
                    while (!sr.EndOfStream)
                    {
                        string[] rows = sr.ReadLine().Split(',');
                        if (rows.Length > 1)
                        {
                            DataRow dr = dt.NewRow();
                            for (int i = 0; i < headers.Length; i++)
                            {
                                dr[i] = rows[i].Trim();
                            }
                            dt.Rows.Add(dr);
                        }
                    }
    
                }
    
    
                return dt;
            }
    
            public static DataTable ConvertXSLXtoDataTable(string strFilePath, string connString)
            {
                OleDbConnection oledbConn = new OleDbConnection(connString);
                DataTable dt = new DataTable();
                try
                {
    
                    oledbConn.Open();
                    using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn))
                    {
                        OleDbDataAdapter oleda = new OleDbDataAdapter();
                        oleda.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        oleda.Fill(ds);
    
                        dt = ds.Tables[0];
                    }
                }
                catch
                {
                }
                finally
                {
    
                    oledbConn.Close();
                }
    
                return dt;
    
            }
        }
    }  
    

    3.View.cshtml

    @using System.Data;  
    
    @{  
        ViewBag.Title = "ImportExcel";  
        Layout = "~/Views/Shared/_Layout.cshtml";  
    }  
      
    <h2>ImportExcel</h2>  
        <!--[if !IE]><!-->  
    <style type="text/css">  
      
      
      /* Generic Styling, for Desktops/Laptops */  
    table {  
      width: 100%;  
      border-collapse: collapse;  
    }  
    /* Zebra striping */  
    tr:nth-of-type(odd) {  
      background: #eee;  
    }  
    th {  
      background: #333;  
      color: white;  
      font-weight: bold;  
    }  
    td, th {  
      padding: 6px;  
      border: 1px solid #ccc;  
      text-align: left;  
    }  
    /* 
    Max width before this PARTICULAR table gets nasty 
    This query will take effect for any screen smaller than 760px 
    and also iPads specifically. 
    */  
    @@media only screen and (max-width: 760px),  
    (min-device-width: 768px) and (max-device-width: 1024px)  {  
      
     /* Force table to not be like tables anymore */  
     table, thead, tbody, th, td, tr {  
      display: block;  
     }  
      
     /* Hide table headers (but not display: none;, for accessibility) */  
     thead tr {  
      ;  
      top: -9999px;  
      left: -9999px;  
     }  
      
     tr { border: 1px solid #ccc; }  
      
     td {  
      /* Behave  like a "row" */  
      border: none;  
      border-bottom: 1px solid #eee;  
      ;  
      padding-left: 50%;  
     }  
      
     td:before {  
      /* Now like a table header */  
      ;  
      /* Top/left values mimic padding */  
      top: 6px;  
      left: 6px;  
      width: 45%;  
      padding-right: 10px;  
      white-space: nowrap;  
     }  
      
     /* 
     Label the data 
     */  
            td:before {  
                content: attr(data-title);  
            }  
       
    }  
    </style>  
                 
    <!--<![endif]-->  
    @using (Html.BeginForm("ImportExcel","Home",FormMethod.Post,new { enctype = "multipart/form-data" } ))  
      
    {  
        <table>  
            <tr><td>Excel file</td><td><input type="file" id="FileUpload1" name="FileUpload1" /></td></tr>  
            <tr><td></td><td><input type="submit" id="Submit" name="Submit" value="Submit" /></td></tr>  
        </table>  
    }  
      
    <div>  
        <table id="">  
              
                @if (ViewBag.Data != null)  
                {  
                    <thead>  
      
                    @foreach (DataColumn column in (ViewBag.Data as System.Data.DataTable).Columns)  
                    {  
                    <text>@column.ColumnName.ToUpper()</text>  
      
      
                    }  
                   </thead>  
    
                    if ((ViewBag.Data as System.Data.DataTable).Rows.Count > 0)  
                    {  
                        foreach (DataRow dr in (ViewBag.Data as System.Data.DataTable).Rows)  
                        {  
      
                    <tr>  
      
                       @foreach (DataColumn column in (ViewBag.Data as System.Data.DataTable).Columns)  
                       {  
                        <td data-title='@column.ColumnName'>  
                              
                            @dr[column].ToString()   
                        </td>  
                       }  
      
      
      
      
                    </tr>  
      
                        }  
      
                    }  
                    else  
                    {  
                        int count = (ViewBag.Data as System.Data.DataTable).Columns.Count;  
                        <tr>  
      
                              <td colspan='@count' style="color:red;" >  
      
                                   No Data Found.  
                                </td>  
                             
      
      
                        </tr>  
      
                    }  
      
                }  
                else  
                {  
                    if (ViewBag.Error != null)  
                    {  
                        <tr>  
                        <td  style = "color:red;" >  
      
           @(ViewBag.Error != null ? ViewBag.Error.ToString() : "")  
                                </td >  
      
      
      
                        </tr >  
                        }  
                    }  
                </table>  
    </div>  

    somebody can show which code is mistake.

    Thank

    Sunday, April 19, 2020 3:26 AM

All replies

  • Hi Mipakteh,

    From the description of the thread, It looks like you have some issue with ASP.NET MVC Application.

    This forum only handles Internet Explorer development related issues.

    Therefore, for better response and better solution for your issue, I suggest you post your question in MSDN ASP.NET MVC Forum.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

     

    Thank you for your understanding.

     

    Regards,

    Deepak

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 20, 2020 3:06 AM
  • thank Deepak.

    Monday, April 20, 2020 2:05 PM