asp.net 导入
开发项目过程中会遇到各种各样的项目需求，我现在遇到的问题是每个部门有不同的excel文件类型，他们每个部门每个文件类型上传成功之后都会在数据库中产生表，表的列名是你excel第一行数据，其他行作为表的内容，表名是上面的文件名称，当然他们上传的文件名和列名都是汉字，上传到数据库之后要转换为英文。
国税局有如下excel表格
上传的节目如下：
国税登记信息的数据如下：
导入成功之后数据库会产生如下的表
c#上传的代码如下:
[HttpPost] public ActionResult SystemSaveToDatabase_withDataSet(HttpPostedFileBase file) { HttpRequestBase requestsss = Request; string DepartmentIdName = requestsss.Form["DepartmentIdName"]; string ExcelFileName = requestsss.Form["ExcelFileName"]; string ExcelFileId = Request.Form["ExcelFileId"]; string DepartmentId = Request.Form["DepartmentId"]; if (ExcelFileId == null && ExcelFileId.ToString().Length == 0) { var JsonData = new { Status = "false", ResultData = "请先选择导入文件类型" }; return Content(JsonData.ToJson()); } string sql3 = "select * from sys_depart_excelfile where DeartmentId='" + Request.Form["DepartmentId"] + "' and ExcelFileId='" + ExcelFileId + "' and state='1' and Convert(varchar(7),addtime,120)='" + System.DateTime.Now.ToString("yyyy-MM") + "' "; DataTable dtinfo = DataFactory.Database().FindTableBySql(sql3); if (dtinfo != null && dtinfo.Rows.Count > 0) { var JsonData = new { Status = "false", ResultData = "本月此文件类型已上传,不能重复上传,如果想重新上传，请先作废." }; return Content(JsonData.ToJson()); } if (file == null) { var JsonData = new { Status = "false", ResultData = "请先选择文件" }; return Content(JsonData.ToJson()); } else { System.IO.FileInfo fi = new System.IO.FileInfo(file.FileName); string owner = "dbo"; string etablename = ""; string departname = PinyinHelper.PinyinString(DepartmentIdName); string excelname = PinyinHelper.PinyinString(ExcelFileName); if (departname.Contains(',')) { departname = departname.Substring(0, departname.IndexOf(',')); } if (excelname.Contains(',')) { excelname = excelname.Substring(0, excelname.IndexOf(',')); } etablename = departname + "_" + excelname; try { if (file == null) { return Content("没有文件！", "text/plain"); } else { DataSet ds = LoadCSV(file, -1); if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0) { DataTable customerTable = ds.Tables[0]; foreach (DataRow copyRow in ds.Tables[0].Rows) { if (copyRow[0].ToString() == "") { copyRow.Delete(); } } ds.AcceptChanges(); this.rowCount = ds.Tables[0].Rows.Count; DataTableReader dtr = ds.Tables[0].CreateDataReader(); DataTable dt; dt = dtr.GetSchemaTable(); CreateTableInDatabase(dt, owner, etablename, DbHelper.ConnectionString); using (SqlBulkCopy bc = new SqlBulkCopy(DbHelper.ConnectionString)) { bc.DestinationTableName = "[" + owner + "].[" + etablename + "]"; bc.NotifyAfter = 100; bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied); bc.WriteToServer(ds.Tables[0]); bc.Close(); } string sql = "insert into sys_depart_excelfile(DeartmentId,ExcelFileId,addtime,filezs,adduser,state,exceltablename)values('" + DepartmentId + "','" + ExcelFileId + "',getdate()," + this.rowCount + ",'" + ManageProvider.Provider.Current().Account + "','1','" + etablename + "')"; DataFactory.Database().ExecuteBySql(new StringBuilder(sql)); var JsonData = new { Status = "true", ResultData = "成功导入:" + this.rowCount.ToString() + "条记录" }; return Content(JsonData.ToJson()); } else { var JsonData = new { Status = "false", ResultData = "没有数据" }; return Content(JsonData.ToJson()); } } } catch (Exception e) { var JsonData = new { Status = "false", ResultData = "导入时出现错误" }; return Content(JsonData.ToJson()); } } }
LoadCSV方法代码如下:他主要对你上传的excel返回dataset数据集
public DataSet LoadCSV(HttpPostedFileBase file, int numberOfRows)
{
System.IO.FileInfo fi = new System.IO.FileInfo(file.FileName);
this.dirCSV = Server.MapPath("~/Content/uploads/");
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(dirCSV);
//判断文件夹否存在,不存在则创建
if (!dir.Exists)
{
dir.Create();
}
this.FileNevCSV = System.DateTime.Now.ToString("yyyyMMddHHmmss") + "-" + file.FileName;
file.SaveAs(dirCSV + this.FileNevCSV);
DataSet ds = new DataSet();
string type = fi.Extension;
try
{
string strConnString = "";
if (type == ".xlsx")
{
strConnString = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + this.dirCSV.Trim() + this.FileNevCSV + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
}
else
{
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.dirCSV.Trim() + this.FileNevCSV + ";Extended Properties='Excel 8.0;IMEX=1;HDR=YES'";
}
OleDbConnection conn = new OleDbConnection(strConnString);
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$] ", conn);
oada.Fill(ds);
conn.Close();
return ds;
}
catch (Exception e) //Error
{
}
return ds;
}
CreateTableInDatabase（）方法主要对上传的excel文件生成数据库的表
private bool CreateTableInDatabase(DataTable dtSchemaTable, string tableOwner, string tableName, string connectionString)
{
FileStream fsOutput = null;
StreamWriter srOutput = null;
try
{
fsOutput = new FileStream(this.dirCSV + "\\" + tableName + ".txt", FileMode.Create, FileAccess.ReadWrite);
srOutput = new StreamWriter(fsOutput);
string chinesename = "";
string englishname = "";
string ctStr = "CREATE TABLE [" + tableOwner + "].[" + tableName + "](\r\n";
for (int i = 0; i < dtSchemaTable.Rows.Count; i++)
{
chinesename += dtSchemaTable.Rows[i][0].ToString() + ",";
string name = PinyinHelper.PinyinString(dtSchemaTable.Rows[i][0].ToString());
if (name.Contains(','))
{
name = name.Substring(0, name.IndexOf(','));
}
englishname += name + ",";
ctStr += " [" + name + "] [nvarchar](4000) NULL";
if (i < dtSchemaTable.Rows.Count)
{
ctStr += ",";
}
ctStr += "\r\n";
}
ctStr += "[addtime] [datetime] default getdate() NULL, ";
ctStr += "[Enabled] char(1) default '1' NULL, ";
ctStr += "[userid] varchar(64) default '" + ManageProvider.Provider.Current().UserId + "' NULL ";
ctStr += ")";
srOutput.Write(chinesename);
srOutput.WriteLine();
srOutput.Write(englishname);
srOutput.Close();
fsOutput.Close();
if (DataFactory.Database().ExecuteBySql(new StringBuilder(ctStr)) > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception e)
{
srOutput.Close();
fsOutput.Close();
return false;
}
}
