Excel转DataSet应用
namespace excel转dataset
{
publicpartial class Index : System.Web.UI.Page
{
public DataSet myDataSet;
public string FileName = string.Empty;
public string FilePath = string.Empty;
//表sheet数组
public ArrayList SheetNames = new ArrayList();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
}
}
public void FileLoad(string paramFileName, string paramFilePath)
{
this.FileName = paramFileName;
this.FilePath = paramFilePath;
myDataSet = new DataSet();
this.Fill(myDataSet);
#region 删除所有空行
//由于导入excel底部容易产生空行,执行此处删除所有空行
var emptyRows = (from r inmyDataSet.Tables[0].AsEnumerable()
where r.ItemArray.All(obj =>string.IsNullOrWhiteSpace(obj.ToString()))
select r).ToArray();
foreach (DataRow dr in emptyRows)
{
myDataSet.Tables[0].Rows.Remove(dr);
}
myDataSet.Tables[0].AcceptChanges();
#endregion
}
#region private void Fill(DataSet paramDataSet) 填充数据
/// <summary>
/// 填充数据
/// </summary>
/// <param name="paramDataSet">DataSet数据集</param>
/// <param name="paramPath">路径</param>
private void Fill(DataSet paramDataSet)
{
OleDbConnection myOleDbConnection = new OleDbConnection();
string strCon = string.Empty;
if (this.FilePath.EndsWith("xlsx"))
{
strCon =@"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;DataSource=" + this.FilePath;
}
else
{
strCon =@"Provider=Microsoft.JET.OLEDB.4.0;Extended Properties=Excel 8.0;DataSource=" + this.FilePath;
}
//获取多sheet信息 Add By OuYang At2014-11-24 Start
Microsoft.Office.Interop.Excel.ApplicationClass excelApp =new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks wbs =excelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook wb =wbs.Open(this.FilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelApp.Visible = false;
int count = wb.Worksheets.Count;
for (int i = 1; i <= count; i++)
{
SheetNames.Add(((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Name.ToLower());
}
wbs.Close();
GC.Collect();
try
{
myOleDbConnection = newOleDbConnection(strCon);
myOleDbConnection.Open();
//保留sheet1功能
if (SheetNames.Contains("sheet1"))
{
string strQuery = "SELECT * FROM [Sheet1$] ";
OleDbCommand myOleDbCommand =new OleDbCommand(strQuery, myOleDbConnection);
OleDbDataAdaptermyOleDbDataAdapter = new OleDbDataAdapter(myOleDbCommand);
myOleDbDataAdapter.Fill(paramDataSet);
}
else
{
foreach (string sheetName inSheetNames)
{
string strQuery= " SELECT * FROM [" + sheetName + "$] ";
OleDbCommandmyOleDbCommand = new OleDbCommand(strQuery, myOleDbConnection);
OleDbDataAdaptermyOleDbDataAdapter = new OleDbDataAdapter(myOleDbCommand);
myOleDbDataAdapter.Fill(paramDataSet, sheetName);
}
}
paramDataSet.AcceptChanges();
//获取多sheet信息 AddBy OuYang At 2014-11-24 End
}
catch (Exception myException)
{
throw myException;
}
finally
{
myOleDbConnection.Close();
GC.Collect();
}
}
#endregion
protected void btnUpload_Click(object sender, EventArgs e)
{
string fileName = string.Empty;
string filePath = string.Empty;
fileName = "教师基本信息导入模版.xls";
filePath = Server.MapPath("/ImportTemp/教师基本信息导入模版.xls");
FileLoad(fileName, filePath);
if (myDataSet.Tables[0].Rows.Count > 0)
{
this.Repeater1.DataSource =myDataSet.Tables[0].DefaultView;
this.Repeater1.DataBind();
}
}
}
}