Excel导出到DataSet

#region 导入excel 返回Dataset
public DataSet ExecleDataSet(string filename, string file, string Type)
{
string strConn = "";
if (Type.Equals(".xlsx"))
{
strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filename +
";Extended Properties='Excel 12.0 Xml; HDR=YES; IMEX=1'";
}
else
{
strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filename +
";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
}
OleDbConnection conn;
DataSet ds = new DataSet();
conn = new OleDbConnection(strConn);
conn.Open();
//OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
//odda.Fill(ds, file);

OleDbDataAdapter myCommand = null;
DataTable dt = new DataTable();

//返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
if (strTableNames[k].IndexOf("$") != -1)
{
string strExcel = "select * from [" + strTableNames[k] + "]";
myCommand = new OleDbDataAdapter(strExcel, conn);
myCommand.Fill(ds, strTableNames[k]);
}
}
conn.Close();
return ds;
}
#endregion

if (FileUpload1.HasFile)
{
if (FileUpload1.PostedFile.ContentLength < 1000000000)
{
string isxls = "";
string fistName = "";
string savePath = "";
string strdt = "";
DataTable dt;
try
{
isxls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
fistName = System.IO.Path.GetFileName(FileUpload1.FileName).ToString();
//if (isxls != ".xls") { Label1.Text = "出现异常：请选择正确的Excel文件！"; return; } //检查文件是不是Excel
//文件名组装
strdt = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//获取路径
savePath = Server.MapPath("/Upload/" + strdt);
//文件移动到指定位置
FileUpload1.PostedFile.SaveAs(savePath);
}
catch
{
lab_message.Text = Resources.global1.teap_errorupload;
}
try
{
DataSet ds=ExecleDataSet(savePath, strdt, isxls);

巴特西

Excel导出到DataSet

最新文章

热门文章