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();
}
}
}
}
View Code
