/// <summary>
/// 导入Excel文件到数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ImportExcel(object sender, EventArgs e)
{
if (ImportFile.HasFile)
{
string fileName = ImportFile.ShortFileName;
int i = fileName.LastIndexOf(".");
string fileExt = fileName.Substring(i);
if (fileExt.ToUpper().Equals(".XLS") || fileExt.ToUpper().Equals(".XLSX"))
{
fileName = fileName.Replace(":", "_").Replace(" ", "_").Replace("\\", "_").Replace("/", "_");
fileName = DateTime.Now.Ticks.ToString() + "_" + fileName;
ImportFile.SaveAs(Server.MapPath("~/uploadTemp/" + fileName));
//转化为datatable
System.Data.DataTable excelTable = ExcelToDataTable(Server.MapPath("~/uploadTemp/" + fileName));
//导入数据库
InsertDataTableToDb(excelTable);
// 清空文件上传组件
ImportFile.Reset();
BindGrid_port();
System.IO.File.Delete(Server.MapPath("~/uploadTemp/" + fileName));
}
else
{
Alert.Show("无效的文件类型!");
return;
}
}
}
/// <summary>
/// excel转换为datatable
/// </summary>
/// <param name="strExcelFileName"></param>
/// <param name="strSheetName"></param>
/// <returns></returns>
public System.Data.DataTable ExcelToDataTable(string strExcelFileName)
{
string strSheetName;
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
//自动获取第一个表名
System.Data.DataTable _Table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
strSheetName = _Table.Rows[0]["Table_Name"].ToString();
string strExcel = string.Format("select * from [{0}]", strSheetName);
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
conn.Close();
}
return ds.Tables[strSheetName];
}
/// <summary>
/// 将Datatable插入数据库
/// </summary>
/// <param name="table"></param>
public void InsertDataTableToDb(System.Data.DataTable table)
{
if (table.Rows.Count == 0)
{
return;
}
else
{
saveFlag = true;
//删除所有之前save的且没有submit数据
// portTableApapter.DeleteQueryUnSaved(true, rowId, false);
for (int i = 0; i < table.Rows.Count; i++)
{
try
{
string PortName = table.Rows[i]["PortName"].ToString();
int BitHi = int.Parse(table.Rows[i]["BitHi"].ToString());
int BitLow = int.Parse(table.Rows[i]["BitLow"].ToString());
string Direction = table.Rows[i]["Direction"].ToString();
string GroupName = table.Rows[i]["GroupName"].ToString();
string AliasInGroup = table.Rows[i]["AliasInGroup"].ToString();
DateTime CreateDate = DateTime.Now;
int Width = BitHi - BitLow + 1;
portTableApapter.InsertQuery(PortName, Width, Direction, GroupName, AliasInGroup, false, CreateDate, BitHi, BitLow, false, false, 0, rowId);
}
catch
{
Alert.Show("Excel format is error");
}
}
}
}