C# OleDb读取Excel数据

2018-01-13 10:56:26来源:网络收集作者:程序诗人人点击

分享

阿里云爆款using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
namespace SYS_TEST.BaseClass
{
///OleDB方式
///优点:将Excel直接当做数据源处理,通过SQL直接读取内容,读取速度较快。
///缺点:读取数据方式不够灵活,无法直接读取某一个单元格,只有将整个Sheet页读取出来后(结果为Datatable)再在Datatable中根据行列数来获取指定的值。
public class OLEDBClass
{
///
/// 获取EXCEL的Sheet表名
///

///
///
public static object[] GetExcelSheetName(string fileName)
{
ArrayList sheetNames = new ArrayList();
try
{
if (File.Exists(fileName))
{
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=/"Excel 8.0/";Data Source=" + fileName);
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
conn.Close();
for (int i = 0; i != dt.Rows.Count; i++)
{
sheetNames.Add(dt.Rows[i]["Table_Name"].ToString());
}
}
}
catch { }
return sheetNames.ToArray();
}
///
/// Excel转换成DataSet
///

///
///
public static DataSet ExcelToDataSet(string fileName)
{
DataSet ds = new DataSet();
try
{
if (File.Exists(fileName))
{
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=/"Excel 8.0/";Data Source=" + fileName);
conn.Open();
string comm = "select * from [sheet1$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(comm, conn);
myCommand.Fill(ds, "table1");
conn.Close();
}
}
catch { }
return ds;
}
///
/// DataSet更新到Excel
///

///
///
public static void DataSetToExcel(string fileName, DataSet oldDs)
{
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=/"Excel 8.0/";Data Source=" + fileName);
conn.Open();
string comm = "select * from [sheet1$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(comm, conn);
OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);
//QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。
builder.QuotePrefix = "[";//获取insert语句中保留字符(起始位置)
builder.QuoteSuffix = "]";//获取insert语句中保留字符(结束位置)
DataSet newDs = new DataSet();
myCommand.Fill(newDs, "table1");
for (int i = 0; i < oldDs.Tables[0].Rows.Count; i++)
{
//此处不能使用ImportRow方法将一行导入到newDs中,因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。
//在使用ImportRow后newds内有值但不能更新到Excel中,因为所有导入行的DataRowState!=Added
DataRow newDr = newDs.Tables["Table1"].NewRow();
for (int j = 0; j < newDs.Tables[0].Columns.Count; j++)
{
newDr[j] = oldDs.Tables[0].Rows[i][j];
}
newDs.Tables["Table1"].Rows.Add(newDr);
}
myCommand.Update(newDs, "Table1");
conn.Close();
}
///
/// 将Excel读取到DataSet(多Sheet表)
///

///
///
public static DataSet ExcelToDataTable(string fileName)
{
//选择源
string strConn = "";
strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + fileName + ";Extended Properties=/"Excel 8.0;HDR=Yes;IMEX=1;/"";
if (Path.GetExtension(fileName).Trim().ToUpper() == ".XLSX")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=/"Excel 12.0;HDR=YES/"";
}
//定义数据集
DataSet ds = new DataSet();
//连接数据源
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
string strSheetName = schemaTable.Rows[i][2].ToString().Trim().Trim('/'');
if (strSheetName.Contains("$") && !strSheetName.EndsWith("$"))
continue;
if (schemaTable.Rows[i][3].ToString().Trim().ToUpper().Equals("TABLE"))
{
//Sql语句
string strExcel = string.Format("select * from [{0}]", strSheetName);
//适配到数据源
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
}
}
conn.Close();
//删除空的Excel表单
for (int i = 0; i < ds.Tables.Count; i++)
{
DataTable dt = ds.Tables[i];
if (dt.Columns.Count == 1 && dt.Columns[0].ColumnName.Trim().ToUpper() == "F1")
{
ds.Tables.Remove(dt);
i--;
}
}
return ds;
}
}
}

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台