locked
Header Column RRS feed

  • Question

  • User733430484 posted

    HI ALL,

    I have problem with Header Column from Excel file.It become trouble with dot "."

    In excel Header column is "P 213.67(cps) and to load to webpages in controller as bellow;

    controller
    
    
    
                                foreach (DataRow row in dt.Rows)
                                {
                                    customers.Add(new Checking
                                    {
                                        Price = row["P 213.67(cps)"].ToString(),
                                    });
                                }
    
    

    error message is;

    System.ArgumentException
    HResult=0x80070057
    Message=Column 'P 213.' does not belong to table .
    Source=<Cannot evaluate the exception source>
    StackTrace:
    <Cannot evaluate the exception stack trace>

    somebody help me.

    Fully code

    1.controller

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using System.IO;
    using System.Data;
    using System.Data.OleDb;
    using System.Configuration;
    using Test_Decimal.Models;
    
    namespace Test_Decimal.Controllers
    {
        public class HomeController : Controller
        {
            // GET: Home
            public ActionResult Index()
            {
                return View(new List<Checking>());
            }
            [HttpPost]
            public ActionResult Index(HttpPostedFileBase postedFile)
            {
                List<Checking> customers = new List<Checking>();
                string filePath = string.Empty;
                if (postedFile != null)
                {
                    string path = Server.MapPath("~/Personal/");
                    if (!Directory.Exists(path))
                    {
                        Directory.CreateDirectory(path);
                    }
    
                    filePath = path + Path.GetFileName(postedFile.FileName);
                    string extension = Path.GetExtension(postedFile.FileName);
                    postedFile.SaveAs(filePath);
    
                    string conString = string.Empty;
                    switch (extension)
                    {
                        case ".xls": //Excel 97-03.
                            conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                            break;
                        case ".xlsx": //Excel 07 and above.
                            conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                            break;
                    }
    
                    conString = string.Format(conString, filePath);
    
                    using (OleDbConnection connExcel = new OleDbConnection(conString))
                    {
                        using (OleDbCommand cmdExcel = new OleDbCommand())
                        {
                            using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                            {
                                DataTable dt = new DataTable();
                                cmdExcel.Connection = connExcel;
    
                                //Get the name of First Sheet.
                                connExcel.Open();
                                DataTable dtExcelSchema;
                                dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                                string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                                connExcel.Close();
    
                                //Read Data from First Sheet.
                                connExcel.Open();
                                cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                                odaExcel.SelectCommand = cmdExcel;
                                odaExcel.Fill(dt);
                                connExcel.Close();
    
    
                                foreach (DataRow row in dt.Rows)
                                {
                                    customers.Add(new Checking
                                    {
                                        Price = row["P 213.67(cps)"].ToString(),
                                    });
                                }
                            }
                        }
                    }
                }
    
                return View(customers);
            }
        }
    }

    2.Model

    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.Linq;
    using System.Web;
    
    namespace Test_Decimal.Models
    {
        public class Checking
        {
            public string Price { get; set; }
        }
    }

    3.Index

    @using Test_Decimal.Models
    @model IEnumerable<Checking>
    @{
        Layout = null;
    }
    
    <!DOCTYPE html>
    
    <html>
    <head>
        <meta name="viewport" content="width=device-width" />
        <title>Index</title>
    </head>
    <body>
        @using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
        {
            <input type="file" name="postedFile" />
            <input type="submit" value="Import" />
        }
        @if (Model.Count() > 0)
        {
            <hr />
            <table cellpadding="0" cellspacing="0">
                <tr>
                    <th>P</th>
                </tr>
                @foreach (Checking customer in Model)
                {
                    <tr>
                        <td>@customer.Price</td>
                    </tr>
                }
            </table>
        }
    </body>
    </html>

    Wednesday, May 13, 2020 12:59 AM

Answers

  • User1686398519 posted

    Hi,  sy_60

    When OleDbDataAdapter fills the data in DataTable, it converts dot into hash.

    You can use "Replace" method to fulfill your needs.

    foreach (DataColumn col in dt.Columns)
    {
        string s = col.ColumnName.ToString();
        s = s.Replace("#", ".");
        customers.Add(new Checking{
            Price = s
        });
    }

    Here is the result.

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 15, 2020 7:15 AM

All replies

  • User1686398519 posted

    Hi,  sy_60

    I used your code and found that "row [" P 213.67 (cps) "]" is the wrong way to get the title, you can use the following method to get it. Please refer to it.

    foreach (DataColumn col in dt.Columns)
                                { 
                                    customers.Add(new Checking{
                                            Price = col.ColumnName.ToString()
                                    });
                                }

    Here is the result.

    Best Regards,

    YihuiSun

    Wednesday, May 13, 2020 9:53 AM
  • User733430484 posted

    Thak you very2 much.Can Import to web pages but the Dot(".") changes to (#).

    P 213#67(cps).

    I need it as a original from excel P 213.67(cps).

    I dont how do it.please help me.

    Thursday, May 14, 2020 8:47 AM
  • User1686398519 posted

    Hi,  sy_60

     but the Dot(".") changes to (#).

    I do not understand this sentence.Can you describe your problem in more detail? Or can you give a screenshot of your question?

    Best Regards,

    YihuiSun

    Thursday, May 14, 2020 10:10 AM
  • User733430484 posted

    Hi Yihuisun;

    I try make screenshot but when to paste for reply My browser not support.

    Therefor I try to Illustrate what happening when I run this project.

    https://localhost:44383/

    Choose File :         Import

    P

    P 213#67 This is header Column.

    It should be show after run this project is like bellow

    P

    P 213.67

    Thank you very for your feedback

    Thursday, May 14, 2020 2:05 PM
  • User1686398519 posted

    Hi,  sy_60

    When OleDbDataAdapter fills the data in DataTable, it converts dot into hash.

    You can use "Replace" method to fulfill your needs.

    foreach (DataColumn col in dt.Columns)
    {
        string s = col.ColumnName.ToString();
        s = s.Replace("#", ".");
        customers.Add(new Checking{
            Price = s
        });
    }

    Here is the result.

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 15, 2020 7:15 AM
  • User733430484 posted

    Thank you very much...It working

    Friday, May 15, 2020 2:12 PM