public DataTable importExcelToDataSet(string FilePath)
{
#region
/*
* DataTable myDt = new DataTable();
* string strConn;
FileInfo file = new FileInfo(FilePath);
if (!file.Exists)
{
throw new Exception("文件不存在");
}
string extension = file.Extension;
switch (extension)
{
case ".xls":
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;'";
break;
case ".xlsx":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
default:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
}
// strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataSet dataSet = new DataSet();
try
{
// myCommand.Fill(dataSet);
// dt = dataSet.Tables[0];
DataRow dr = dt.Rows[0];
for (int i = 0; i < dt.Columns.Count; i++)
{
myDt.Columns.Add(dr[i].ToString());
}
for (int i = 1; i < dt.Rows.Count; i++)
{
DataRow mydr = myDt.NewRow();
mydr = dt.Rows[i];
myDt.Rows.Add(mydr.ItemArray);
}
}
catch (Exception ex)
{
throw new Exception("该Excel文件的工作表的名字不正确," + ex.Message);
}
return myDt;*/
#endregion
HSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
rows.MoveNext();
rows.MoveNext();
HSSFRow row = (HSSFRow)rows.Current;
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
//将第一列作为列表头
dt.Columns.Add(row.GetCell(j).ToString());
}
while (rows.MoveNext())
{
row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return dt;
}
8 个解决方案
#1
这是什么............
分享帖?
分享帖?
#2
oh,my god
oledb就不要再拿出来分享了
oledb就不要再拿出来分享了
#3
分享好啊。还是用NPOI组件吧
#4
#5
分享贴啊,我还以为是求助帖呢?
#6
#7
#8
ssis三分钟写个package路过。。。。
#1
这是什么............
分享帖?
分享帖?
#2
oh,my god
oledb就不要再拿出来分享了
oledb就不要再拿出来分享了
#3
分享好啊。还是用NPOI组件吧
#4
#5
分享贴啊,我还以为是求助帖呢?
#6
#7
#8
ssis三分钟写个package路过。。。。