/// <summary>
/// 根据路径获取EXCEL的数据源，将其加载到内存中
/// </summary>
/// <param name="path"></param>
/// <param name="fu"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static DataSet GetExcel(string path, FileUpload fu, ref string msg)
{
var data = new DataSet();
try
{
if (path.ToLower().EndsWith(".xls") || (path.ToLower().EndsWith(".xlsx")))
{
string strConn = string.Empty;
if (path.ToLower().EndsWith(".xls"))
{
//Excel 2000/2003 格式
strConn = string.Format("Provider=Microsoft.Jet.OleDb.4.0;data source={0};Extended Properties='Excel 8.0'", path);
}
else if (path.ToLower().EndsWith(".xlsx"))
{
//Excel 2007格式
strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;data source={0};Extended Properties='Excel 12.0;HDR=YES'", path);
}
using (var objConn = new OleDbConnection(strConn))
{
objConn.Open();
DataTable schemaTable = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (schemaTable != null && schemaTable.Rows.Count > 0 && schemaTable.Columns.Count > 1)
{
string tableName = schemaTable.Rows[0][2].ToString().Trim();
string strSql = string.Format("Select * From [Sheet1$A3:K]", tableName);
var objCmd = new OleDbCommand(strSql, objConn);
var sqlada = new OleDbDataAdapter {
SelectCommand = objCmd
};
sqlada.Fill(data);
objConn.Close();
}
}
}
else if (path.ToLower().EndsWith(".csv"))
{
var csvFile = new CSVFile();
csvFile.Open(fu.PostedFile.InputStream);
int i = 0;
var dt = new DataTable();
while (csvFile.ReadNextLine())
{
if (i == 0)
{
for (int j = 0; j < csvFile.Cells.Count; j++)
{
if (!string.IsNullOrEmpty(csvFile.Cells[j]))
{
dt.Columns.Add(csvFile.Cells[j]);
}
}
}
else
{
DataRow dr = dt.NewRow();
int j = 0;
foreach (DataColumn dc in dt.Columns)
{
if (csvFile.Cells.Count > j && csvFile.Cells[j] != null && !string.IsNullOrEmpty(csvFile.Cells[j]))
{
dr[dc.ColumnName] = csvFile.Cells[j];
}
j++;
}
dt.Rows.Add(dr);
}
i++;
}
data.Tables.Add(dt);
}
}
catch (Exception ex)
{
msg = ex.Message;
}
return(data);
}