- public DataSet GetDataSet(string filePath)
- {
- string Connstr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
- OleDbConnection Conn = new OleDbConnection(Connstr);
- //创建ArrayList对象 存放所有sheetname
- ArrayList sheetNamelist = new ArrayList();
- //获取配置Excel中sheet总数(这里是根据项目需求配置的) 如果需要导入Excel表格所有sheet数据则将此代码删除
- int sheetCount = Convert.ToInt32(ConfigurationManager.AppSettings["sheetCount"].ToString());
- DataSet dsExcel = new DataSet();
- 10. try
- 11. {
- 12. if (Conn.State == ConnectionState.Closed)
- 13. {
- 14. Conn.Open();
- 15. }
- 16. DataTable dtExcelSchema = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
- 17. string sheetName = string.Empty;
- 18. if (dtExcelSchema.Rows.Count > sheetCount)
- 19. {
- 20. Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
21. alert('很抱歉!你上传Excel文件sheet总数过多不能大于10个sheet..!! ')
22. // --></mce:script>");
- 23. return;
- 24. }
- 25. else
- 26. {
- 27. for (int j = 0; j < dtExcelSchema.Rows.Count; j++)
- 28. {
- 29. sheetName = String.Format("Sheet{0}$", j + 1);
- 30. sheetNamelist.Add(sheetName);
- 31. }
- 32. }
- 33.
- 34. }
- 35. catch (Exception ex)
- 36. {
- 37. throw new Exception(ex.Message.ToString(), ex);
- 38. }
- 39. finally
- 40. {
- 41. Conn.Close();
- 42. }
- 43. try
- 44. {
- 45. string strSQL = string.Empty;
- 46. for (int i = 0; i < sheetNamelist.Count; i++)
- 47. {
- 48. strSQL = "select * from [" + sheetNamelist[i].ToString() + "]";
- 49. OleDbDataAdapter da = new OleDbDataAdapter(strSQL, Conn);
- 50. DataTable dtExcel = new DataTable(sheetNamelist[i].ToString());
- 51. da.Fill(dtExcel);
- 52. dsExcel.Tables.Add(dtExcel);
- 53. }
- 54. return dsExcel;
- 55. }
- 56. catch (Exception ex)
- 57. {
- 58. throw new Exception(ex.Message.ToString(), ex);
- 59. }
- 60. }
- 61.
- 62. //从Excel 表中取出数据 将取出来的数据插入到数据库中
- 63. public void InsertData(DataSet ds) {
- 64. string strSQL=string.Empty;
- 65. if (ds.Tables[0].Rows.Count > 0)
- 66. {
- 67. for (int j = 0; j < ds.Tables.Count; j++)
- 68. {
- 69. for(int i=0;i<ds.Tables[j].Rows.Count;i++)
- 70. {
- 71. DataRow dr=ds.Tables[j].Rows[i];
- 72. //组名
- 73. string groupname = dr["组名"].ToString().Trim();
- 74. //联系人
- 75. string contactName = dr["联系人"].ToString().Trim();
- 76. //手机号码
- 77. string mobile = dr["手机号码"].ToString().Trim();
- 78. //公司名称
- 79. string companyName = dr["公司名称"].ToString().Trim();
- 80. //公办号码
- 81. string officeNum = dr["办公号码"].ToString().Trim();
- 82. //家庭号码
- 83. string homeNum = dr["家庭号码"].ToString().Trim();
- 84. //邮箱
- 85. string Email = dr["邮 箱"].ToString().Trim();
- 86. //联系地址
- 87. string address = dr["联系地址"].ToString().Trim();
- 88. //创建时间
- 89. string createtime = dr["创建时间"].ToString().Trim();
- 90. //性别
- 91. string Sex = dr["性别"].ToString().Trim();
- 92. //手机套餐类型
- 93. string mobileType = dr["手机套餐类型"].ToString().Trim();
- 94. //是否开通通信助理
- 95. string isOpen = dr["是否开通通信助理"].ToString().Trim();
- 96. //SQL 语句
- 97. strSQL = "insert into msm_Excel(groupName,Mobile,Name,companyName,officeNum,homeNum,Emial,address,Createtime,Sex,mobileType,isOpen)values('" + groupname + "','" + mobile + "','" + contactName + "','" + companyName + "','" + officeNum + "','" + homeNum + "','" + Email + "','" + address + "','" + createtime + "','" + Sex + "','" + mobileType + "','" + isOpen + "')";
- 98. try
- 99. {
- 100. int n = SQLHelper.SqlDataExecute(strSQL);
- 101. if (n > 0)
- 102. {
- 103. Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
104. alert('数据插入成功!')
105. // --></mce:script>");
- 106. Label1.Text = "一共成功插入" + ds.Tables[j].Rows.Count.ToString() + "条数据";
- 107. }
- 108. else
- 109. {
- 110. Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
111. alert('服务器繁忙！请稍候再试..!')
112. // --></mce:script>");
- 113. }
- 114. }
- 115. catch (Exception ex)
- 116. {
- 117. throw ex;
- 118. }
- 119. }
- 120. }
- 121. }
- 122.
- 123. else {
- 124. Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
125. alert('此Excel文件中无数据!!!')
126. // --></mce:script>");
- 127. }
- 128.
- 129. }
130. //调用
131. //获取上传文件名
- 132. string fileName = FileUpload1.FileName;
- 133. //判断是否存在上传文件
- 134. if (FileUpload1.PostedFile.FileName.Length == 0) {
- 135. Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
136. alert('请选择你要上传的Excel文件!!')
137. // --></mce:script>");
- 138. }
- 139. //判断上传的文件类型是否正确
- 140. else if (!Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower().Equals(".xls") && !Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower().Equals(".xlsx"))
- 141. {
- 142. Page.RegisterStartupScript("", "<script>alert('很抱歉!你上传的文件类型不正确!只能上传Excel类型的文件!')</script.");
- 143. }
- 144. else
- 145. {
- 146. //获取上传的文件路径
- 147. filePath = Server.MapPath("TxtFiles//") + DateTime.Now.ToString("yyyyMMddhhmmss") + fileName;
- 148. this.FileUpload1.PostedFile.SaveAs(filePath);
- 149. ds = GetDataSet(filePath);
- 150. InsertData(ds);
- 151.
- 152. }