public JsonResult ReadExcel()
{
try
{
Dictionary <string, string> dicAppSet = null;
if (dicAppSet == null || dicAppSet.Count == 0)
{
general gn = new general();
dicAppSet = gn.ReadAppseting();
if (dicAppSet == null || dicAppSet.Count == 0)
{
Json("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Đọc appsetting lỗi\"}]}");
}
}
var path = dicAppSet["path_saveonserver"];
string fileName = "";
for (int i = 0; i < Request.Files.Count; i++)
{
HttpPostedFileBase file = Request.Files[i]; //Uploaded file
//Use the following properties to get file's name, size and MIMEType
int fileSize = file.ContentLength;
fileName = file.FileName;
string mimeType = file.ContentType;
System.IO.Stream fileContent = file.InputStream;
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
//To save file, use SaveAs method
file.SaveAs(path + fileName); //File will be saved in application root
var itemp = this.Request.Form;
var parr = new Dictionary <string, string>();
foreach (var key in itemp.AllKeys)
{
parr.Add(key.ToLower(), itemp[key]);
}
gnReadExcel ex = new gnReadExcel();
var dt = ex.readfileExcel(path + fileName, parr["select"] + " ", " " + parr["where"]);
var ds = new DataSet();
ds.Tables.Add(dt);
general gn = new general();
var data = gn.ConvertDatasetToListJson(ds);
if (data == null)
{
return(Json("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Lỗi khi ConvertDatasetToListJson\"}]}"));
}
var json = gn.ConvertListToJson(data);
var strResult = Json("{\"result\":\"OK\",\"data\":" + json + "}");
System.IO.File.Delete(path + fileName);
return(strResult);
}
return(Json("{\"result\":\"OK\",\"data\":[{\"status\":\"Không nhận được file\"}]}"));
}
catch (Exception ex)
{
return(Json("{\"result\":\"ERROR\",\"data\":[{\"status\":\"" + ex.Message.Replace("\n", "") + "\"}]}"));
}
}
public string InportExcelToOrcale()
{
try
{
general gn = new general();
string fileName = "";
HttpPostedFileBase file = Request.Files[0]; //Uploaded file
//Use the following properties to get file's name, size and MIMEType
int fileSize = file.ContentLength;
fileName = file.FileName;
string mimeType = file.ContentType;
System.IO.Stream fileContent = file.InputStream;
if (!Directory.Exists(Server.MapPath("~/UploadFile/")))
{
Directory.CreateDirectory(Server.MapPath("~/UploadFile/"));
}
//To save file, use SaveAs method
string filePath = Server.MapPath("~/UploadFile/") + fileName;
file.SaveAs(filePath); //File will be saved in application root
var itemp = this.Request.Form;
var parr = new Dictionary <string, string>();
foreach (var key in itemp.AllKeys)
{
parr.Add(key.ToLower(), itemp[key]);
}
// CHUYEN SANG DATASET
gnReadExcel ex = new gnReadExcel();
var dt = ex.readfileExcel(filePath, parr["select"] + " ", " " + parr["where"]);
// bulk copy to oracle
if (dt == null)
{
return("{\"result\":\"OK\",\"data\":[{\"status\":\"Lỗi không đọc được file excel\"}]}");
}
// add them 1 so cot
DataColumn tenfile = new DataColumn("tenfile", typeof(System.String));
tenfile.DefaultValue = fileName;
DataColumn ngayup = new DataColumn("ngayup", typeof(System.String));
ngayup.DefaultValue = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
DataColumn idrandom = new DataColumn("idrandom", typeof(System.String));
idrandom.DefaultValue = parr["idrandom"];
dt.Columns.Add(tenfile);
dt.Columns.Add(ngayup);
dt.Columns.Add(idrandom);
// check bang neu chua ton tai thi tao bang
gnOracle or = new gnOracle();
general gns = new general();
var dicAppSet = gns.ReadAppseting();
var checkTonTai = "SELECT COUNT(1) TONTAI FROM USER_TABLES WHERE UPPER(TABLE_NAME) = UPPER('" + parr["insertto"] + "') ";
var count = or.ExcuteReturnDataSet(checkTonTai, null, CommandType.Text, dicAppSet[parr["connstr"].ToLower()]);
if (count != null && count.Tables.Count > 0 && count.Tables[0].Rows.Count > 0 && count.Tables[0].Rows[0]["TONTAI"].ToString() == "0")
{
// TAO COT CHINH
var str = "CREATE TABLE " + parr["insertto"] + " ( ";
foreach (var col in dt.Columns)
{
str += " " + col.ToString().ToUpper() + " VARCHAR2(255),";
}
str = str.Substring(0, str.Length - 1) + " ) ";
or.ExcuteReturnDataSet(str, null, CommandType.Text, dicAppSet[parr["connstr"].ToLower()]);
}
// bulk copy to oracle
gnOracle gnOr = new gnOracle();
string connectOracle = dicAppSet[parr["connstr"].ToLower()];
var dt2 = gnOr.forMartTable(dt, parr["insertto"], connectOracle);
using (var connection = new OracleConnection(connectOracle))
{
connection.Open();
using (var bulkCopy = new OracleBulkCopy(connection, OracleBulkCopyOptions.UseInternalTransaction))
{
bulkCopy.DestinationTableName = parr["insertto"];
var table = gnOr.potentialFix(dt2);
bulkCopy.WriteToServer(table);
}
}
return("{\"result\":\"OK\",\"data\":[{\"idrandom\":\"" + parr["idrandom"] + "\"}]}");
}
catch (Exception ex)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"" + ex.Message.Replace("\n", "") + "\"}]}");
}
}
