using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows;
using System.Windows.Forms;
namespace MyProjectDAL
{
public class ExcelDB
{
private OleDbConnection connection, connWithoutHDR;
private string connectionString, connStringWithoutHDR;
//构造函数
public ExcelDB(string strExcelFileName)
{
string path = Application.ExecutablePath;
FileInfo exeInfo = new FileInfo(path);
path = exeInfo.DirectoryName + "\\";
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + "user data\\excel files\\" + strExcelFileName + ".xls;" + "Extended Properties='Excel 5.0;HDR=YES;'";
connStringWithoutHDR = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + "user data\\excel files\\" + strExcelFileName + ".xls;" + "Extended Properties='Excel 5.0;HDR=NO;'";
connection = new OleDbConnection(connectionString);
connWithoutHDR = new OleDbConnection(connStringWithoutHDR);
}
//将excel文件内容读入DataTable
public DataTable Excel2DataTable(int startLine, int endLine, string columns)
{
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
int readCount = 0;
int i;
try
{
string query = "select " + columns + " from [Sheet1$]";
this.connWithoutHDR.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, connWithoutHDR);
adapter.Fill(dt1);
connWithoutHDR.Close();
}
catch (OleDbException ex)
{
MessageBox.Show("Excel数据读取失败 ErrorCode: " + (ex.ErrorCode).ToString());
}
readCount = dt1.Rows.Count;
if (readCount > endLine)
{
for (i = startLine - 1; i < endLine; i++)
{
dt2.Rows.Add(dt1.Rows[i]);
}
}
if (readCount >= startLine && readCount <= endLine)
{
for (i = startLine - 1; i < readCount; i++)
{
dt2.Rows.Add(dt1.Rows[i]);
}
}
return dt2;
}
}
}