View Code
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using Model; using BLL; using Utility; using System.IO; using System.Data.OleDb; using System.Data; using System.Diagnostics; using ICSharpCode.SharpZipLib.Zip; namespace WebUI.SystemPage.Question { public partial class InsertQuestions : System.Web.UI.Page { protected AdminQuestionManager manager = AdminQuestionManager.GetInstrance(); protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { try { //绑定科目 this.ddlSubject.DataSource = manager.GetSubjectAll(); this.ddlSubject.DataTextField = "SName"; this.ddlSubject.DataValueField = "SID"; this.ddlSubject.DataBind(); //绑定章节 BindChapter(); this.lblTitle.Text = "批量添加题目"; Model.Questions question = manager.GetQuestionByID(Convert.ToInt32(Request.QueryString["qid"])); for (int i = 0; i < this.ddlSubject.Items.Count; i++) { if (this.ddlSubject.Items[i].Value == question.QSID.ToString()) { ddlSubject.ClearSelection(); this.ddlSubject.Items[i].Selected = true; break; } } for (int i = 0; i < this.ddlChapter.Items.Count; i++) { if (this.ddlChapter.Items[i].Value == question.QCID.ToString()) { ddlSubject.ClearSelection(); this.ddlChapter.Items[i].Selected = true; break; } } } catch (Exception ex) { Utility.LogMsg.WriteLogToApplicationFolderByMonth("ChapterEdite.aspx添加或编辑初始化异常:" + ex.ToString()); } } } /// <summary> /// 绑定章节 /// </summary> private void BindChapter() { this.ddlChapter.DataSource = manager.GetChapterList(1000, 1, " and CSID=" + this.ddlSubject.SelectedItem.Value); this.ddlChapter.DataTextField = "CName"; this.ddlChapter.DataValueField = "CID"; this.ddlChapter.DataBind(); } /// <summary> /// 科目改变事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void ddlSubject_SelectedIndexChanged(object sender, EventArgs e) { BindChapter(); } /// <summary> /// 提交 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void BtnExcel_Click(object sender, EventArgs e) { string ExceptionStr = ""; ClientScriptManager cs = Page.ClientScript; if (myfile.PostedFile.ContentLength<=0) { cs.RegisterStartupScript(this.GetType(), "", "ZENG.msgbox.show('请上传压缩文件', 1, 4000);", true); return; } string yasuoFile = myfile.PostedFile.FileName; string fileExt = Path.GetExtension(yasuoFile); //获取文件扩展名 if (fileExt != ".zip") { cs.RegisterStartupScript(this.GetType(), "", "alert('请选择压缩文件类型zip!')", true); return; } try { DataTable dt = new DataTable("Questions"); dt.Columns.Add("QID", typeof(int)); dt.Columns.Add("QSID",typeof(int)); dt.Columns.Add("QCID",typeof(int)); dt.Columns.Add("QDescription",typeof(string)); dt.Columns.Add("QFileName", typeof(string)); dt.Columns.Add("QImageName", typeof(string)); DataTable dt1 = new DataTable(); string _conn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + this.txtExcelURL.Text + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; OleDbConnection MyConnection = new OleDbConnection(_conn); MyConnection.Open(); OleDbDataAdapter da = new OleDbDataAdapter("select [题目内容],[题目内容图片],[flash文件] from [题库导入$]", MyConnection); da.Fill(dt1); MyConnection.Close(); string newPath = "";//解压后的新路径 #region 上传压缩文件 string file = ""; file = myfile.PostedFile.FileName; newPath = "/SystemPage/UpFile/" + file.Substring(0, file.Length - 4); string fileName = file; string savePath = "/SystemPage/UpFile/" + file; file = Server.MapPath(savePath); string filePath = Path.GetDirectoryName(file); if (!System.IO.Directory.Exists(filePath)) { System.IO.Directory.CreateDirectory(filePath); } myfile.PostedFile.SaveAs(file); Decompress(file, Server.MapPath("/SystemPage/UpFile/")); File.Delete(file); #endregion for (int i = 0; i < dt1.Rows.Count; i++) { if (!string.IsNullOrEmpty(dt1.Rows[i][2].ToString().Trim())) { DataRow dr = dt.NewRow(); dr["QID"] = i + 1; dr["QSID"] = Convert.ToInt32(this.ddlSubject.SelectedValue); dr["QCID"] = Convert.ToInt32(this.ddlChapter.SelectedValue); dr["QFileName"] = newPath + "/" + dt1.Rows[i][2].ToString().Trim(); if (!string.IsNullOrEmpty(dt1.Rows[i][1].ToString())) { dr["QImageName"] = newPath + "/" + dt1.Rows[i][1].ToString().Trim(); } else { dr["QImageName"] = ""; } dr["QDescription"] = dt1.Rows[i][0].ToString(); dt.Rows.Add(dr); } } string strSMG = "<span style='color:red;font-size:15px'>以下是Excel文件中的数据,实际上没有上传的</span><br>";//记录少那个文件 int Errot = 0; //在文件夹中过滤Excel中不存在的文件 for (int i = 0; i < dt.Rows.Count; i++) { string folder = Server.MapPath(newPath); DirectoryInfo fileList = new DirectoryInfo(folder); bool isHave = false; foreach (FileInfo item in fileList.GetFiles()) { if (!string.IsNullOrEmpty(dt.Rows[i]["QImageName"].ToString().Trim())) { if (dt.Rows[i]["QImageName"].ToString().IndexOf(item.Name) < 0) { strSMG += dt.Rows[i]["QImageName"].ToString() + "<br>"; } } if (dt.Rows[i]["QFileName"].ToString().IndexOf(item.Name) >= 0) { isHave = true; break; } } if (isHave == false) { strSMG += dt.Rows[i]["QFileName"].ToString() + "<br>"; Errot++; } } if (Errot == 0) { DivMsg.InnerHtml = ""; //如果数据完整则插入数据库manager.InsertQuestionData(dt) if (false) { cs.RegisterStartupScript(this.GetType(), "", "ZENG.msgbox.show('批量添加成功', 4, 3000);", true); } else { #region 删除之前同文件夹名称文件数据 string path = Server.MapPath(newPath); if (Directory.GetFileSystemEntries(path).Length > 0) { //遍历文件夹中所有文件 foreach (string file1 in Directory.GetFiles(path)) { //文件己存在 if (File.Exists(file1)) { FileInfo fi = new FileInfo(file); //判断当前文件属性是否是只读 if (fi.Attributes.ToString().IndexOf("ReadyOnly") >= 0) { fi.Attributes = FileAttributes.Normal; } //删除文件 File.Delete(file1); } } //删除文件夹 Directory.Delete(Server.MapPath(newPath)); } #endregion cs.RegisterStartupScript(this.GetType(), "", "ZENG.msgbox.show('数据插入失败', 1, 3000);", true); } } else { cs.RegisterStartupScript(this.GetType(), "", "ZENG.msgbox.show('Excel和上传文件的数据不对应,添加失败', 1, 3000);", true); DivMsg.InnerHtml = strSMG; } } catch (Exception ex) { } finally { cs.RegisterStartupScript(this.GetType(), "", "ZENG.msgbox.show('批量添加异常,请检查是否Excel正在打开', 1, 3000);", true); } } /// <summary> /// 解压缩文件 /// </summary> /// <param name="GzipFile">压缩包文件名</param> /// <param name="targetPath">解压缩目标路径</param> public static void Decompress(string GzipFile, string targetPath) { //string directoryName = Path.GetDirectoryName(targetPath + "\\") + "\\"; string directoryName = targetPath; if (!Directory.Exists(directoryName)) Directory.CreateDirectory(directoryName);//生成解压目录 string CurrentDirectory = directoryName; byte[] data = new byte[2048]; int size = 2048; ZipEntry theEntry = null; using (ZipInputStream s = new ZipInputStream(File.OpenRead(GzipFile))) { while ((theEntry = s.GetNextEntry()) != null) { if (theEntry.IsDirectory) {// 该结点是目录 if (!Directory.Exists(CurrentDirectory + theEntry.Name)) Directory.CreateDirectory(CurrentDirectory + theEntry.Name); } else { if (theEntry.Name != String.Empty) { //解压文件到指定的目录 using (FileStream streamWriter = File.Create(CurrentDirectory + theEntry.Name)) { while (true) { size = s.Read(data, 0, data.Length); if (size <= 0) break; streamWriter.Write(data, 0, size); } streamWriter.Close(); } } } } s.Close(); } } } }