Header Column

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>

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

User733430484 posted

Thank you very much...It working