导入Excel文件，并绑定到REPATER当中
1 //导入Excel文件
2 /// <summary>
3 /// 先把Excel当中的数据转换成数据集，之后将数据集当中的数据一一对应添加到数据库当中
4 /// </summary>
5 /// <param name="sender"></param>
6 /// <param name="e"></param>
7 protected void btAddReport_Click(object sender, EventArgs e)
8 {
9 string fileExtend = "";//文件扩展名
10 int fileSize = 0;//文件大小
11
12 if (string.IsNullOrEmpty(FileUpload.Value))
13 {
14 Common.JavascriptHelper.Alert("请选择要导入的Excel文件!", this);
15 return;
16 }
17 //得到文件的大小
18 fileSize = FileUpload.PostedFile.ContentLength;
19 if (fileSize == 0)
20 {
21 Common.JavascriptHelper.Alert("导入的Excel文件大小为0，请检查是否正确！", this);
22 return;
23 }
24 //得到扩展名
25 fileExtend = FileUpload.Value.Substring(FileUpload.Value.LastIndexOf(".") + 1);
26 if (fileExtend.ToLower() != "xls" && fileExtend.ToLower() != "xlsx")
27 {
28 Common.JavascriptHelper.Alert("你选择的文件格式不正确，只能导入EXCEL文件！", this);
29 return;
30 }
31 string filepath = UpLoadXls(FileUpload);
32 //将Excel转换成数据集
33 DataSet ds = Common.ExcelHelper.ImportXlsToData(filepath,fileExtend);
34 DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
35 int rowsnum = ds.Tables[0].Rows.Count;
36 int addcount = 0;
37 string sflag = "";
38 ArrayList arry = new ArrayList();
39 bool btype = true, bunit = true, bprojectname = true, bcompletetime = true, bstarttime = true;
40 Model.ProjectTaskInfo model = new Model.ProjectTaskInfo();
41 if (rowsnum == 0)
42 {
43 Common.JavascriptHelper.Alert("上传的Excel表无数据!", this);
44 return;
45 }
46 else
47 {
48 for (int j = 0; j < dr.Length; j++)
49 {
50 arry.Add(dr[j]["序号"]);
51 }
52 for (int i = 0; i < dr.Length; i++)
53 {
54 model.JointConferenceID = (dr[i]["第几次会议"] == null || dr[i]["第几次会议"].ToString() == "") ? 0 : Convert.ToInt32(dr[i]["第几次会议"]);
55 if (dr[i]["任务类型"] == null || dr[i]["任务类型"].ToString() == "" || GetTaskTypeNo(dr[i]["任务类型"].ToString()) == 0)
56 {
57 //Common.JavascriptHelper.Alert("任务类型不能为空！", this);
58 btype = false;
59 }
60 else
61 {
62 model.TaskType = GetTaskTypeNo(dr[i]["任务类型"].ToString());
63 }
64 model.TaskTitle = dr[i]["任务标题"] == null ? "" : dr[i]["任务标题"].ToString();
65 if (dr[i]["责任单位"] == null || dr[i]["责任单位"].ToString() == "" || GetResponsibleUnitsID(dr[i]["责任单位"].ToString()) == 0)
66 {
67 //Common.JavascriptHelper.Alert("责任单位不能为空！", this);
68 bunit = false;
69 }
70 else
71 {
72 model.ResponsibleUnits = GetResponsibleUnitsID(dr[i]["责任单位"].ToString());
73 }
74 model.ResponsiblePerson = dr[i]["责任人"] == null ? "" : dr[i]["责任人"].ToString();
75 if (dr[i]["项目名称"] == null || dr[i]["项目名称"].ToString() == "" || GetProjectID(dr[i]["项目名称"].ToString()) == 0)
76 {
77 //Common.JavascriptHelper.Alert("项目名称不能为空！", this);
78 bprojectname = false;
79 }
80 else
81 {
82 model.ProjectID = GetProjectID(dr[i]["项目名称"].ToString());
83 }
84 string allunit = "";
85 if (dr[i]["配合单位"] != null)
86 {
87 //将中文状态下的逗号改成英文状态下的逗号
88 string[] unit = dr[i]["配合单位"].ToString().Replace("，", ",").Split(',');
89 foreach (string sunit in unit)
90 {
91 allunit += GetResponsibleUnitsID(sunit) + ",";
92 }
93 }
94 model.CooperateUnit = allunit;
95 model.TaskContent = dr[i]["任务内容"] == null ? "" : dr[i]["任务内容"].ToString();
96 if (dr[i]["完成时间"] == null || dr[i]["完成时间"].ToString() == "")
97 {
98 bcompletetime = false;
99 }
100 else
101 {
102 model.CompleteTime = Convert.ToDateTime(dr[i]["完成时间"]);
103 }
104 if (dr[i]["下达时间"] == null || dr[i]["下达时间"].ToString() == "")
105 {
106 bstarttime = false;
107 }
108 else
109 {
110 model.TaskGiveTime = Convert.ToDateTime(dr[i]["下达时间"]);
111 }
112
113 if (btype && bunit && bprojectname && bcompletetime)
114 {
115 if (DateTime.Compare(DateTime.Today, Convert.ToDateTime(model.CompleteTime)) > 0)
116 {
117 if (model.TaskType == 1)
118 {
119 model.TaskType = 2;
120 model.TaskNo = Common.PublicMethod.FindLastNo(model.ProjectID.ToString(), 1);
121 }
122 else
123 {
124 model.TaskNo = null;
125 }
126 model.SupervisoryTime = DateTime.Today;
127 model.SupervisoryNo = Common.PublicMethod.FindLastNo(model.ProjectID.ToString(), 2);
128 model.CompleteInfo = "逾期";
129 }
130 else
131 {
132 if (model.TaskType == 1)
133 {
134 model.TaskNo = Common.PublicMethod.FindLastNo(model.ProjectID.ToString(), 1);
135 model.SupervisoryNo = null;
136 }
137 else
138 {
139 model.TaskNo = null;
140 model.SupervisoryNo = Common.PublicMethod.FindLastNo(model.ProjectID.ToString(), 2);
141 }
142 model.CompleteInfo = "办理中";
143 }
144 if (new BLL.ProjectTaskInfo().Add(model) > 0)
145 {
146 addcount++;
147 arry.Remove(dr[i]["序号"]);
148 }
149
150 }
151 }
152 foreach (object sf in arry)
153 {
154 sflag += sf.ToString() + ",";
155 }
156 if (!string.IsNullOrEmpty(sflag))
157 {
158 Common.JavascriptHelper.Alert("成功导入" + addcount + "条记录！\\n\\n失败的记录序号为：" + sflag.Substring(0, sflag.Length - 1) + "", this);
159
160 }
161 else
162 {
163 Common.JavascriptHelper.Alert("成功导入" + addcount + "条记录！", this);
164 }
165 BindTaskInfo();
166 }
167
168
169 }
2 /// <summary>
3 /// 先把Excel当中的数据转换成数据集，之后将数据集当中的数据一一对应添加到数据库当中
4 /// </summary>
5 /// <param name="sender"></param>
6 /// <param name="e"></param>
7 protected void btAddReport_Click(object sender, EventArgs e)
8 {
9 string fileExtend = "";//文件扩展名
10 int fileSize = 0;//文件大小
11
12 if (string.IsNullOrEmpty(FileUpload.Value))
13 {
14 Common.JavascriptHelper.Alert("请选择要导入的Excel文件!", this);
15 return;
16 }
17 //得到文件的大小
18 fileSize = FileUpload.PostedFile.ContentLength;
19 if (fileSize == 0)
20 {
21 Common.JavascriptHelper.Alert("导入的Excel文件大小为0，请检查是否正确！", this);
22 return;
23 }
24 //得到扩展名
25 fileExtend = FileUpload.Value.Substring(FileUpload.Value.LastIndexOf(".") + 1);
26 if (fileExtend.ToLower() != "xls" && fileExtend.ToLower() != "xlsx")
27 {
28 Common.JavascriptHelper.Alert("你选择的文件格式不正确，只能导入EXCEL文件！", this);
29 return;
30 }
31 string filepath = UpLoadXls(FileUpload);
32 //将Excel转换成数据集
33 DataSet ds = Common.ExcelHelper.ImportXlsToData(filepath,fileExtend);
34 DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
35 int rowsnum = ds.Tables[0].Rows.Count;
36 int addcount = 0;
37 string sflag = "";
38 ArrayList arry = new ArrayList();
39 bool btype = true, bunit = true, bprojectname = true, bcompletetime = true, bstarttime = true;
40 Model.ProjectTaskInfo model = new Model.ProjectTaskInfo();
41 if (rowsnum == 0)
42 {
43 Common.JavascriptHelper.Alert("上传的Excel表无数据!", this);
44 return;
45 }
46 else
47 {
48 for (int j = 0; j < dr.Length; j++)
49 {
50 arry.Add(dr[j]["序号"]);
51 }
52 for (int i = 0; i < dr.Length; i++)
53 {
54 model.JointConferenceID = (dr[i]["第几次会议"] == null || dr[i]["第几次会议"].ToString() == "") ? 0 : Convert.ToInt32(dr[i]["第几次会议"]);
55 if (dr[i]["任务类型"] == null || dr[i]["任务类型"].ToString() == "" || GetTaskTypeNo(dr[i]["任务类型"].ToString()) == 0)
56 {
57 //Common.JavascriptHelper.Alert("任务类型不能为空！", this);
58 btype = false;
59 }
60 else
61 {
62 model.TaskType = GetTaskTypeNo(dr[i]["任务类型"].ToString());
63 }
64 model.TaskTitle = dr[i]["任务标题"] == null ? "" : dr[i]["任务标题"].ToString();
65 if (dr[i]["责任单位"] == null || dr[i]["责任单位"].ToString() == "" || GetResponsibleUnitsID(dr[i]["责任单位"].ToString()) == 0)
66 {
67 //Common.JavascriptHelper.Alert("责任单位不能为空！", this);
68 bunit = false;
69 }
70 else
71 {
72 model.ResponsibleUnits = GetResponsibleUnitsID(dr[i]["责任单位"].ToString());
73 }
74 model.ResponsiblePerson = dr[i]["责任人"] == null ? "" : dr[i]["责任人"].ToString();
75 if (dr[i]["项目名称"] == null || dr[i]["项目名称"].ToString() == "" || GetProjectID(dr[i]["项目名称"].ToString()) == 0)
76 {
77 //Common.JavascriptHelper.Alert("项目名称不能为空！", this);
78 bprojectname = false;
79 }
80 else
81 {
82 model.ProjectID = GetProjectID(dr[i]["项目名称"].ToString());
83 }
84 string allunit = "";
85 if (dr[i]["配合单位"] != null)
86 {
87 //将中文状态下的逗号改成英文状态下的逗号
88 string[] unit = dr[i]["配合单位"].ToString().Replace("，", ",").Split(',');
89 foreach (string sunit in unit)
90 {
91 allunit += GetResponsibleUnitsID(sunit) + ",";
92 }
93 }
94 model.CooperateUnit = allunit;
95 model.TaskContent = dr[i]["任务内容"] == null ? "" : dr[i]["任务内容"].ToString();
96 if (dr[i]["完成时间"] == null || dr[i]["完成时间"].ToString() == "")
97 {
98 bcompletetime = false;
99 }
100 else
101 {
102 model.CompleteTime = Convert.ToDateTime(dr[i]["完成时间"]);
103 }
104 if (dr[i]["下达时间"] == null || dr[i]["下达时间"].ToString() == "")
105 {
106 bstarttime = false;
107 }
108 else
109 {
110 model.TaskGiveTime = Convert.ToDateTime(dr[i]["下达时间"]);
111 }
112
113 if (btype && bunit && bprojectname && bcompletetime)
114 {
115 if (DateTime.Compare(DateTime.Today, Convert.ToDateTime(model.CompleteTime)) > 0)
116 {
117 if (model.TaskType == 1)
118 {
119 model.TaskType = 2;
120 model.TaskNo = Common.PublicMethod.FindLastNo(model.ProjectID.ToString(), 1);
121 }
122 else
123 {
124 model.TaskNo = null;
125 }
126 model.SupervisoryTime = DateTime.Today;
127 model.SupervisoryNo = Common.PublicMethod.FindLastNo(model.ProjectID.ToString(), 2);
128 model.CompleteInfo = "逾期";
129 }
130 else
131 {
132 if (model.TaskType == 1)
133 {
134 model.TaskNo = Common.PublicMethod.FindLastNo(model.ProjectID.ToString(), 1);
135 model.SupervisoryNo = null;
136 }
137 else
138 {
139 model.TaskNo = null;
140 model.SupervisoryNo = Common.PublicMethod.FindLastNo(model.ProjectID.ToString(), 2);
141 }
142 model.CompleteInfo = "办理中";
143 }
144 if (new BLL.ProjectTaskInfo().Add(model) > 0)
145 {
146 addcount++;
147 arry.Remove(dr[i]["序号"]);
148 }
149
150 }
151 }
152 foreach (object sf in arry)
153 {
154 sflag += sf.ToString() + ",";
155 }
156 if (!string.IsNullOrEmpty(sflag))
157 {
158 Common.JavascriptHelper.Alert("成功导入" + addcount + "条记录！\\n\\n失败的记录序号为：" + sflag.Substring(0, sflag.Length - 1) + "", this);
159
160 }
161 else
162 {
163 Common.JavascriptHelper.Alert("成功导入" + addcount + "条记录！", this);
164 }
165 BindTaskInfo();
166 }
167
168
169 }
//读取excel
public static DataSet ImportXlsToData(string filepath,string filetype)
{
string oleDBConnString="";
if (filetype.ToLower() == "xls")
{
oleDBConnString = "Provider=Microsoft.Jet.oleDb.4.0;data source=" + filepath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
}
else
{
oleDBConnString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + filepath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
}
OleDbConnection oleDBConn = null;
OleDbDataAdapter oleAdMaster = null;
DataTable m_tableName = new DataTable();
DataSet ds = new DataSet();
oleDBConn = new OleDbConnection(oleDBConnString);
oleDBConn.Open();
m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (m_tableName != null && m_tableName.Rows.Count > 0)
{
m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString();
}
string sqlMaster;
sqlMaster = " SELECT * FROM [" + m_tableName.TableName + "] ";
oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
oleAdMaster.Fill(ds, "m_tableName");
oleAdMaster.Dispose();
oleDBConn.Close();
oleDBConn.Dispose();
return ds;
}
public static DataSet ImportXlsToData(string filepath,string filetype)
{
string oleDBConnString="";
if (filetype.ToLower() == "xls")
{
oleDBConnString = "Provider=Microsoft.Jet.oleDb.4.0;data source=" + filepath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
}
else
{
oleDBConnString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + filepath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
}
OleDbConnection oleDBConn = null;
OleDbDataAdapter oleAdMaster = null;
DataTable m_tableName = new DataTable();
DataSet ds = new DataSet();
oleDBConn = new OleDbConnection(oleDBConnString);
oleDBConn.Open();
m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (m_tableName != null && m_tableName.Rows.Count > 0)
{
m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString();
}
string sqlMaster;
sqlMaster = " SELECT * FROM [" + m_tableName.TableName + "] ";
oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
oleAdMaster.Fill(ds, "m_tableName");
oleAdMaster.Dispose();
oleDBConn.Close();
oleDBConn.Dispose();
return ds;
}
