1.C# oledb方式 此种方式需要测试环境与生产环境都安装注册有access数据库
/// <summary>
/// OLEDB方式读取Excel
/// </summary>
/// <param name="pathName">Excel路径</param>
/// <param name="sheetName">工作表名</param>
/// <returns></returns>
public DataTable ExcelToDataTable(string fileUrl)
{
//支持.xls和.xlsx,即包括office2010等版本的 HDR=Yes代表第一行是标题,不是数据;
const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
DataTable dt = null;
//建立连接
OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));
try
{
//打开连接
if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
{
conn.Open();
}
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//获取Excel的第一个Sheet名称
string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
//查询sheet中的数据
//string strSql = "select * from [" + sheetName + "]" +"where idx not in (select top "+"2" + "from [" + sheetName + "])";
string strSql = "select * from [" + sheetName + "] ";
OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
//for (int i = 0; i < dt.Columns.Count; i++)
//{
// dt.Columns[i].ColumnName = dt.Rows[0][i].ToString();
//}
//dt.Rows.RemoveAt(0);
return dt;
}
catch (Exception e)
{
throw e;
}
finally
{
conn.Close();
conn.Dispose();
}
}
2. C# NPOI方式读取excel 返回datatable
SaveFileAddress 为服务器绝对路径
public DataTable ReadExcel(string SaveFileAddress,string FileName) {
//创建文件流
FileStream fileStream = new FileStream(SaveFileAddress, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
try
{
string filetype = FileName.Split('.')[1];
ISheet sheet = null;
if (filetype == "xls")
{
HSSFWorkbook HSSF = new HSSFWorkbook(fileStream);
sheet = HSSF.GetSheetAt(0);
}
else
{
XSSFWorkbook XSSF = new XSSFWorkbook(fileStream);
sheet = XSSF.GetSheetAt(0);
}
//创建容器
DataTable dt = new DataTable();
//动态创建列
for (int i = 0; i < sheet.GetRow(1).LastCellNum; i++)
{
DataColumn dc = new DataColumn("T" + i, Type.GetType("System.String"));
dt.Columns.Add(dc);
}
//动态添加数据
for (int i = 1; i < sheet.LastRowNum + 1; i++)
{
IRow row = sheet.GetRow(i);
DataRow dr = dt.NewRow();
int count = 0;
for (int j = 0; j < row.LastCellNum; j++)
{
string a = row.GetCell(j)==null?"": row.GetCell(j).ToString();
if (string.IsNullOrEmpty(a))
{
count++;
}
dr[j] = a;
}
if(count!= row.LastCellNum)
dt.Rows.Add(dr);
}
return dt;
}
catch (Exception ex)
{
DataTable dt = new DataTable();
return dt;
}
finally {
fileStream.Close();
}
}