public string sqlBuilder(string path, string tableName)
{
sys_dicService sys_dicservice = new sys_dicService();
string temp = path.Substring(0, path.LastIndexOf('\\'));
string filePath = temp; // 生成mysql脚本要保存在根目录下
string fileName = path; //要读取的文件完整路径
string fileExtension = ".sql"; //文件扩展名
string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(fileName); //文件名前缀
string saveName = fileNameWithoutExtension + fileExtension; //保存文件名称
//这里要写一个创建文件的判断，1/22
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
if (!File.Exists(filePath + "\\" + saveName))
{
File.Create(Path.Combine(filePath, saveName)).Close();
//File.Create(filePath + "\\" + saveName);
}
else
{
File.Delete(filePath + "\\" + saveName);
File.Create(filePath + "\\" + saveName).Close();
}
FileStream stream = File.Open(fileName, FileMode.Open); //获取文件对象
byte[] buffer = new byte[stream.Length];
stream.Read(buffer, 0, (int)stream.Length);
stream.Close();
Workbook workbook = new Workbook();
workbook.Open(fileName);
Cells cells = workbook.Worksheets[0].Cells;
String[,] array = new String[cells.MaxDataRow + 1, cells.MaxDataColumn + 1];
for (int i = 0; i < cells.MaxDataRow + 1; i++)
{
for (int j = 0; j < cells.MaxDataColumn + 1; j++)
{
string s = cells[i, j].StringValue.Trim();
array[i, j] = s;
}
}
#region 制作数据表
string init = "DROP TABLE IF EXISTS `" + tableName + "`;CREATE TABLE `" + tableName + "` ( `ID` char(36) NOT NULL, `HandleDept` char(36) DEFAULT NULL,`HandleStaff` char(36) DEFAULT NULL,`PersonID` char(36) DEFAULT NULL,"; //脚本文件开头初始化
IEnumerable <sys_dic> diclist = null; //定义枚举类型，用于接收字典表的里的记录
diclist = sys_dicservice.getsysdic(tableName); //获取字典表list
List <int> arraylist = new List <int>(); //新建一个集合用于记录表格新增的字段，增强系统容错
for (int i = 0; i < array.GetLength(1); i++) //遍历列
{
Boolean flag = true; //定义一个标志
foreach (sys_dic dic in diclist) //遍历字段表里的记录
{
if (array[2, i] == dic.name_cn) //如果表格标题等于字典表里的name_cn对应
{
string temp1 = "`" + dic.name_en + "`varchar(100) DEFAULT '' COMMENT '" + dic.name_cn + "',\r\n";
init += temp1; //拼接sql脚本
flag = false;
break;
}
}
if (flag)
{
arraylist.Add(i); //如果字典表里没有该字段，那么将该列记录暂存。
Console.WriteLine("字典缺少关于中文" + array[2, i] + "。");
}
}
string end = "PRIMARY KEY (`ID`),KEY `PersonName` (`PersonName`) USING BTREE,KEY `PersonID` (`PersonID`) USING BTREE) ENGINE = InnoDB DEFAULT CHARSET = utf8; ";//SQL 结尾，设置主键、编码、引擎
string resultsql = init + end;
StreamWriter sw = File.AppendText(filePath + "\\" + saveName); //开始写出sql脚本
sw.Write(resultsql);
sw.Flush();
sw.Close(); //关闭输出流
#endregion
string msg = "脚本制作成功，已为您保存至" + filePath + "\r\n\r\n------------------------------------\r\n\r\n\r\n";
string lackof = "当前案管内容已更新,以下是目前缺少的字段：\r\n\r\n";
if (arraylist != null)
{
foreach (int i in arraylist)
{
lackof += array[2, i] + "\r\n";
}
string showmsg = msg + lackof;
return(showmsg);
}
else
{
return(msg);
}
}
public ActionResult MyUpload(HttpPostedFileBase fileData, string guid, string folder)
{
Isys_dicService sys_dicservice = new sys_dicService();
string tableName = "";
if (fileData != null)
{
//try
//{
ControllerContext.HttpContext.Request.ContentEncoding = Encoding.GetEncoding("UTF-8");
ControllerContext.HttpContext.Response.ContentEncoding = Encoding.GetEncoding("UTF-8");
ControllerContext.HttpContext.Response.Charset = "UTF-8";
if (fileData.FileName == "表格一.xls")
{
tableName = "Temp_ClueInfo_History";
}
else if (fileData.FileName == "表格二.xls")
{
tableName = "Temp_CaseInfo_History";
}
else
{
tableName = folder;
}
// 文件上传后的保存路径
string filePath = Server.MapPath("~/UploadFiles/");
//DirectoryUtil.AssertDirExist(filePath);
string fileName = Path.GetFileName(fileData.FileName); //原始文件名称
string fileExtension = Path.GetExtension(fileName); //文件扩展名
string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(fileName);
string saveName = Guid.NewGuid().ToString() + fileExtension; //保存文件名称
string logname = saveName + "/" + fileNameWithoutExtension;
if (!System.IO.Directory.Exists(filePath))
{
System.IO.Directory.CreateDirectory(filePath);
}
fileData.SaveAs(Path.Combine(filePath, saveName));
System.IO.Stream stream = fileData.InputStream;
byte[] buffer = new byte[stream.Length];
stream.Read(buffer, 0, (int)stream.Length);
stream.Close();
Workbook workbook = new Workbook();
workbook.Open(Path.Combine(filePath, saveName));
Cells cells = workbook.Worksheets[0].Cells;
String[,] array = new String[cells.MaxDataRow + 1, cells.MaxDataColumn + 1];
for (int i = 0; i < cells.MaxDataRow + 1; i++)
{
for (int j = 0; j < cells.MaxDataColumn + 1; j++)
{
string s = cells[i, j].StringValue.Trim();
array[i, j] = s;
}
}
#region 新方法
Guid handleDept = (Guid)Session["HandleDept"];//拿到会话值
Guid handleStaff = (Guid)Session["UserID"];
Guid personID = new Guid("00000000-0000-0000-0000-000000000000");
IEnumerable <sys_dic> diclist = null;//定义枚举类型，用于接收字典表的里的记录
string cxsql = "Select * From sys_dic Where table_name = '" + tableName + "'";
string crsql = null;
crsql = "REPLACE into " + tableName + " (id,handleDept,HandleStaff,PersonID,"; //定义一个用于插入数据的全局sql字符串，用于拼接
diclist = sys_dicservice.SqlQuery(cxsql); //将查询到的记录存到枚举里
List <int> arraylist = new List <int>(); //新建一个集合用于记录表格新增的字段，增强系统容错
for (int i = 0; i < array.GetLength(1); i++) //遍历列
{
Boolean flag = true; //定义一个标志
foreach (sys_dic dic in diclist) //遍历字段表里的记录
{
if (array[2, i] == dic.name_cn) //如果表格标题等于字典表里的name_cn对应
{
crsql += dic.name_en + ","; //编辑插入的sql语句，将标题对应的字段名加入到即将插入业务的sql中。
flag = false;
break;
}
}
if (flag)
{
arraylist.Add(i);//如果字段表里没有该字段，那么将该列记录暂存。
}
}
crsql = crsql.Substring(0, crsql.Length - 1) + ")"; //删除sql尾部逗号
string sql2 = "";
for (int i = 3; i < array.GetLength(0); i++) //从表格第四行“值”开始遍历
{
string valuessql = ""; //定义sql语句的values部分
valuessql += "(uuid(),\"" + handleDept + "\",\"" + handleStaff + "\",\"" + personID + "\",";
for (int j = 0; j < array.GetLength(1); j++)
{
Boolean flag = false;
foreach (int num in arraylist)//判断字典表是否有该字段，没有就跳过录入
{
if (j == num)
{
flag = true;
break;
}
}
if (flag)
{
continue;
}
////添加日期格式转换
//string a;
//if (array[2, j].IndexOf("时间") > 0)
//{
// a = MyDateConvert(array[i, j]);
// valuessql += "\'" + a + "\',";
//}
//else
//{
// valuessql += "\'" + array[i, j] + "\',";
//}
valuessql += "\'" + array[i, j] + "\',";//不为空继续
}
valuessql = valuessql.Substring(0, valuessql.Length - 1);
valuessql += "),";
sql2 += valuessql;
}
sql2 = sql2.Substring(0, sql2.Length - 1);
string sql3 = "; update " + tableName + "set handletype = REPLACE(CONCAT(Handle1Time,\",\",HandleType1First,\",\",HandleType1Second,\";\",Handle2Time,\",\",HandleType2First,\",\",HandleType2Second,\";\",Handle3Time,\",\",HandleType3First,\",\",HandleType3Second,\";\",Handle4Time,\",\",HandleType4First,\",\",HandleType4Second,\";\",Handle5Time,\",\",HandleType5First,\",\",HandleType5Second,\";\",Handle6Time,\",\",HandleType6First,\",\",HandleType6Second,\";\",Handle7Time,\",\",HandleType7First,\",\",HandleType7Second,\";\"),\",,;\",\"\")";
string ss = crsql + " values" + sql2; //拼接要插入的字段和其对应的values
sys_dicservice.SqlCommand(ss); //开始导入数据库
#endregion
return(Content("true"));
}
else
{
return(Content("false"));
}
}
