Excel使用单元格格式导入问题 [英] Excel Import problem with cell's format
本文介绍了Excel使用单元格格式导入问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在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屋!
查看全文