用C#读取具有复杂表头的Excel表格中的日期数据时,明明表格中存在数据,却读取不出来,不知道个位大哥有没有碰到这种情况阿。
Excel中有相邻的两列:
RetirementType Retirement
1 2004/10/31
0
RetirementType为0时Retirement日期为空,当Excel有第一条数据时,我可以读出来,这应该不是什么代码的问题吧?为什么有了第二条数据时,我读出来的Retirement都为空呢。郁闷,有谁碰到过类似问题,指教以下!!!!
代码如下:
sqlInsert="insert into Test1(EmployeeCode,RetirementType,Retirement) values(";
sqlInsert+="@EmployeeCodeRetirementType,@Retirement)";
SqlCommand sqlCmd =new SqlCommand(sqlInsert,con);
sqlCmd.Parameters.Add(new SqlParameter("@EmployeeCode",SqlDbType.Money,8));
sqlCmd.Parameters.Add(new SqlParameter("@RetirementType",SqlDbType.TinyInt,1));
sqlCmd.Parameters.Add(new SqlParameter("@Retirement",SqlDbType.SmallDateTime,4));
sqlCmd.Parameters["@EmployeeCode"].Value=Decimal.Parse(ds.Tables[0].Rows[flag]["EmployeeCode"].ToString());
if(ds.Tables[0].Rows[flag]["RetirementType"].ToString()=="")
sqlCmd.Parameters["@RetirementType"].Value=System.DBNull.Value;
else
sqlCmd.Parameters["@RetirementType"].Value=Int16.Parse(ds.Tables[0].Rows[flag]["RetirementType"].ToString());
if(ds.Tables[0].Rows[flag]["Retirement"].ToString()=="")
sqlCmd.Parameters["@Retirement"].Value=System.DBNull.Value;
else
sqlCmd.Parameters["@Retirement"].Value=DateTime.Parse(ds.Tables[0].Rows[flag]["Retirement"].ToString());
sqlCmd.ExecuteNonQuery();
Excel中有相邻的两列:
RetirementType Retirement
1 2004/10/31
0
RetirementType为0时Retirement日期为空,当Excel有第一条数据时,我可以读出来,这应该不是什么代码的问题吧?为什么有了第二条数据时,我读出来的Retirement都为空呢。郁闷,有谁碰到过类似问题,指教以下!!!!
代码如下:
sqlInsert="insert into Test1(EmployeeCode,RetirementType,Retirement) values(";
sqlInsert+="@EmployeeCodeRetirementType,@Retirement)";
SqlCommand sqlCmd =new SqlCommand(sqlInsert,con);
sqlCmd.Parameters.Add(new SqlParameter("@EmployeeCode",SqlDbType.Money,8));
sqlCmd.Parameters.Add(new SqlParameter("@RetirementType",SqlDbType.TinyInt,1));
sqlCmd.Parameters.Add(new SqlParameter("@Retirement",SqlDbType.SmallDateTime,4));
sqlCmd.Parameters["@EmployeeCode"].Value=Decimal.Parse(ds.Tables[0].Rows[flag]["EmployeeCode"].ToString());
if(ds.Tables[0].Rows[flag]["RetirementType"].ToString()=="")
sqlCmd.Parameters["@RetirementType"].Value=System.DBNull.Value;
else
sqlCmd.Parameters["@RetirementType"].Value=Int16.Parse(ds.Tables[0].Rows[flag]["RetirementType"].ToString());
if(ds.Tables[0].Rows[flag]["Retirement"].ToString()=="")
sqlCmd.Parameters["@Retirement"].Value=System.DBNull.Value;
else
sqlCmd.Parameters["@Retirement"].Value=DateTime.Parse(ds.Tables[0].Rows[flag]["Retirement"].ToString());
sqlCmd.ExecuteNonQuery();
举个例子:
Excel中:
________________________
| 2004 | 600 |
________________________
|2004年之前 | 1200 |
________________________
“2004年之前”就读不出来
把Excel转换成pdb文件的时候该单元格也是空的,
把Excel转换成csv文件之后,读取csv文件还是读不到DataTable中,
各种郁闷。...最后我是用读Excel文件的方法,把每个单元格读到DataTable中,
我把我写的方法的代码贴出来,供参考,大家有好的方法别忘了告诉一声。/// <summary>
/// 读取excel数据,返回datatable
/// </summary>
/// <param name="excelFilePath">要读取的Excel文件的完整路径</param>
/// <returns>包含Excel数据的DataTable</returns>
public DataTable GetExcelData(string excelFilePath)
{
try
{
Excel.Application app = new Excel.Application();
Excel.Sheets sheets;
Excel.Workbook workbook;
DataTable dt = new DataTable();
if (app == null)
{
return null;
}
//app.Visible = true; object oMissiong=System.Reflection.Missing.Value;
workbook = app.Workbooks.Open(excelFilePath, oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong); //将数据读入到DataTable中——Start
sheets = workbook.Worksheets;
Excel.Worksheet worksheet = (Excel.Worksheet) sheets.get_Item(1);
if(worksheet == null)
return null; string cellContent;
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
Excel.Range range;
for(int iRow = 1; iRow <= iRowCount; iRow ++)
{
DataRow dr = dt.NewRow(); for(int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Excel.Range)worksheet.Cells[iRow,iCol]; cellContent = (range.Value2 == null) ? "" : range.Value2.ToString(); if(iRow == 1)
{
dt.Columns.Add(cellContent);
}
else
{
dr[iCol - 1] = cellContent;
}
}
if(iRow != 1)
dt.Rows.Add(dr);
}
//将数据读入到DataTable中——End
workbook.Close(false,oMissiong,oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null; app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null; GC.Collect();
GC.WaitForPendingFinalizers(); return dt;
}
catch
{
return null;
}
}
不过必须要引用Excel com控件using System.Reflection; // For Missing.Value and BindingFlags
using System.Runtime.InteropServices; // For COMException
using Office = Microsoft.Office;
//using Excel;其中的open方法和office的版本有关系,我用的是office2003这个方法会有很多的问题产生,用着不是很好,希望大家给出更好的代码!
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=F:\\Report\\09月16日晚报表.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [9月份$]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
return myDataSet;
}试试我的,请根据实际情况更改路径和文件名和Sheet名
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.OleDb" %>
<HTML>
<HEAD>
<title>如何读取Excel表格中的数据</title>
<script language="C#" runat="server">
void SubmitBtn_Click(object sender, System.EventArgs e)
{
// 获取Excep文件的完整路径
string source = File1.Value; string ConnStr = "user id=ordtrc;pwd=ordtrc;data source=FRAME2DB;persist security info=False";
string query = "SELECT * FROM [Sheet1$]"; OleDbCommand oleCommand = new OleDbCommand(query, new OleDbConnection(ConnStr));
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
DataSet myDataSet = new DataSet(); // 将 Excel 的[Sheet1]表内容填充到 DataSet 对象
oleAdapter.Fill(myDataSet, "[Sheet1$]"); // 数据绑定
DataGrid1.DataSource = myDataSet;
DataGrid1.DataMember = "[Sheet1$]";
DataGrid1.DataBind();
}
</script>
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<H3>如何读取Excel表格中的数据</H3>
请选择Excel表格:<BR>
<INPUT type="file" id="File1" name="File1" runat="server" size="26"><br>
<asp:Button
id="SubmitBtn"
runat="server"
Text="开始显示"
OnClick="SubmitBtn_Click">
</asp:Button><br>
<br>
<asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid>
</form>
</body>
</HTML>
这是因为在读EXCEL时,会隐式的限制数据类型类型。
你可以通过更改数据的类型去达到你的要求!
比如一列全部为数字型或文本型。