传统获取Excel都是以OLEDB的方式以Excel表为数据源获取,前提是必须安装了office软件。而且要进行版本判断
网上搜了没找到流的方式获取Excel数据,自己琢磨写了一个
filestream用于访问磁盘文件
streamreader用于读取filestream对象
不说直接上code
private void ExcelToDatatable(string str)
{
string[] _data = str.Split('\t');
int _data_row = _data.Length;
string[,] data = new string[_data_row / 21, 21];//21为列数
for (int i = 0; i < _data.Length-1; i++)
{
data[i / 21, i % 21] = _data[i];//一维数组转换二维数组
}
DataTable dt = new DataTable();//先实例化datatable
//定义列名
string[] ColumnName={"nID",
"sSamsid",
"sName_zh",
"sName_en",
"sBirthday",
"sGender",
"sHouseid",
"sGroup",
"sLoginID",
"sPassword",
"sEmail",
"sBarcode",
"sIDnumber",
"bIsManager",
"sSubject",
"dCreatedDate",
"sCreatedBy",
"dUpdatedDate",
"sUpdatedBy",
"bEnabled",
"bVisible"};
for(int i=0;i<ColumnName.Length;i++)
{
dt.Columns.Add(ColumnName[i]);//循环添加列
}
int data_row = data.GetLength(0);//返回第一维长度(行数)
int data_column = data.GetLength(1);//返回第二维长度(列数)
for (int i = 0; i < data_row; i++)
{
DataRow dr = dt.NewRow();//定义dt的行变量
//DataRow dr = new DataRow();
object[] obj=new object[data_column];
for(int j=0;j<data_column;j++)
{
//object[] obj=new object[data_column];
obj[j] = data[i, j];
}
dr.ItemArray=obj;
dt.Rows.Add(dr);
}
gv1.DataSource = dt;
gv1.DataBind();
}