Excel使用单元格格式导入问题 [英] Excel Import problem with cell's format

问题描述

我在asp.net应用程序中使用C＃导入excel电子表格并进行一些处理。如果列中通常格式化为数字的其中一个单元格更改为文本，则不会读取该值。该单元格被视为DBNull。我正在使用DbProviderFactory。我在连接字符串中设置了IMEX = 1。以下是我的代码。有人能告诉我如何导入Excel电子表格而不会丢失格式不正确的值吗？非常感谢！

I am using C# in an asp.net application to import an excel spreadsheet and do some processing. If one of the cells in a column that is normally formatted as 'Number' is changed to 'Text', the value isn't read. That cell is seen as DBNull. I am using the DbProviderFactory. I have set IMEX=1 in my connection string. Below is my code. Can someone tell me how to import an Excel spreadsheet without losing values that are formatted incorrectly? Thanks much!

public static List<Patient> ReadPatientSpreadsheet(string Clinic, DateTime MeasureEndDate)
{
    {
        string CONNECTIONSTRING = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Data\Quality\PCAHTN.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";";
        List<Patient> PatientList = null;
        // Read spreadsheet and populate a patient object
        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
        PatientList = new List<Patient>();
        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = CONNECTIONSTRING;
            using (DbCommand command = connection.CreateCommand())
            {
                command.CommandText = "SELECT * FROM [Query$]";
                connection.Open();
                using (DbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        Patient p = new Patient();
                        if (!(dr["PatientName"] is DBNull))
                        {
                            p.PatientID = (dr["PatientID"].ToString().Trim());
                            p.PatientName = dr["PatientName"].ToString().Trim();
                            p.PatientDOB = (Convert.ToDateTime(dr["PatientDOB"]));
                            p.PatientGender = dr["PatientGender"].ToString().Trim();

推荐答案

;
connection.Open（）;
使用（DbDataReader dr = command.ExecuteReader（））
{
while（dr.Read（））
{
患者p =新患者（）;
if（！（dr [PatientName]为DBNull））
{
p.PatientID =（dr [PatientID]。ToString（） .Trim（））;
p.PatientName = dr [PatientName]。ToString（）。Trim（）;
p.PatientDOB =（Convert.ToDateTime（dr [PatientDOB]）） ;
p.PatientGender = dr [PatientGender]。ToString（）。Trim（）;

"; connection.Open(); using (DbDataReader dr = command.ExecuteReader()) { while (dr.Read()) { Patient p = new Patient(); if (!(dr["PatientName"] is DBNull)) { p.PatientID = (dr["PatientID"].ToString().Trim()); p.PatientName = dr["PatientName"].ToString().Trim(); p.PatientDOB = (Convert.ToDateTime(dr["PatientDOB"])); p.PatientGender = dr["PatientGender"].ToString().Trim();

我终于找到了一个简单的解决方案当我正在阅读excel文件时，我会用tryparse来确定如果值是整数，则为ine。如果是，我将序列日期转换为DateTime。这是我的约会清洁工作。

I finally found an easy solution to this problem. As I'm reading the excel file, I do a tryparse to determine if the value is an integer. If it is, I convert the serial date to a DateTime. Here is my date cleaning job.

<pre lang="cs">public static DateTime? CleanDateField(string DateField)
{
    // Convert the text to DateTime and return the value or null
    DateTime? CleanDate = new DateTime();
    int intDate;
    bool DateIsInt = int.TryParse(DateField, out intDate);
    if (DateIsInt)
    {
        // If this is a serial date, convert it
        CleanDate = DateTime.FromOADate(intDate);
    }
    else if (DateField.Length != 0 && DateField != "1/1/0001 12:00:00 AM" &&
        DateField != "1/1/1753 12:00:00 AM")
    {
        // Convert from a General format
        CleanDate = (Convert.ToDateTime(DateField));
    }
    else
    {
        // Date is blank
        CleanDate = null;
    }
    return CleanDate;
}

你看过这里了吗？ Clickety [ ^ ]



我以前在格式化方面从Excel导入和导出数据时遇到了很多问题。

Have you looked here? Clickety[^]

I have had many problems in the past importing and exporting data from Excel in regards to formatting.

这篇关于Excel使用单元格格式导入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！