Asp.NET Excel导入数据返回JSON字符

it2022-05-05 161

前端创建ID为ExcelDy的一个按钮以及一个有导入按钮的窗体

点击导入按钮弹出窗体选择Excel表格点击导入跳进一般处理程序

把文件读取到DataSet中

返回Json数据前台把json数据读取到表单中

SELECT * FROM [入库单$] 入库单为单元名称 1 /// <summary> 2 /// upload 的摘要说明 3 /// </summary> 4 public class upload : IHttpHandler 5 { 6 SBCRKBH_BLL_NEW cgrk = new SBCRKBH_BLL_NEW(); 7 public void ProcessRequest(HttpContext context) 8 { 9 //string gys = context.Request["GYS"]; 10 ResultData resultData = new ResultData(); 11 if (context.Request.Files == null) 12 { 13 resultData.Msg = "上传文件为空"; 14 context.Response.Write(JsonConvert.SerializeObject(resultData)); 15 return; 16 } 17 18 var filedata = context.Request.Files["file"]; 19 20 var path = "/Upload/"; 21 var serverPath = context.Server.MapPath("~" + path); 22 if (!System.IO.Directory.Exists(serverPath)) 23 { 24 System.IO.Directory.CreateDirectory(serverPath); 25 } 26 if (string.IsNullOrEmpty(filedata.FileName)) 27 { 28 resultData.Msg = "请选择文件"; 29 context.Response.Write(JsonConvert.SerializeObject(resultData)); 30 return; 31 } 32 var filename = Guid.NewGuid().ToString() + filedata.FileName.Substring(filedata.FileName.LastIndexOf('.')); 33 string filepath = serverPath + filename; 34 filedata.SaveAs(filepath); 35 36 string fileType = System.IO.Path.GetExtension(filepath); 37 38 39 40 using (DataSet ds = new DataSet()) 41 { 42 string strCon = " Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties='Excel 12.0;HDR=Yes;IMEX=2';Data Source=" + filepath; 43 44 string strCom = " SELECT * FROM [入库单$]"; 45 using (OleDbConnection myConn = new OleDbConnection(strCon)) 46 using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn)) 47 { 48 myConn.Open(); 49 myCommand.Fill(ds); 50 } 51 if (ds == null || ds.Tables.Count <= 0) 52 { 53 context.Response.Write("0"); 54 } 55 else 56 { 57 List<ImportHCDR> lst = new List<ImportHCDR>(); 58 59 60 if (ds.Tables[0].Rows.Count > 0) 61 { 62 63 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) 64 { 65 ImportHCDR mod = new ImportHCDR(); 66 67 68 69 70 if (ds.Tables[0].Rows[i]["高值耗材名称"] == null) 71 { 72 resultData.Msg = "高值耗材名称不能为空，请修改后再导入"; 73 context.Response.Write(JsonConvert.SerializeObject(resultData)); 74 return; 75 76 } 77 if (ds.Tables[0].Rows[i]["高值耗材名称"] != null) 78 { 79 string hcbh = ds.Tables[0].Rows[i]["高值耗材名称"] + ""; 80 if (string.IsNullOrEmpty(hcbh)) 81 { 82 resultData.Msg = "高值耗材名称不能为空，请修改后再导入"; 83 context.Response.Write(JsonConvert.SerializeObject(resultData)); 84 return; 85 86 } 87 mod.SBMC = hcbh; 88 } 89 //供应商 90 if (ds.Tables[0].Rows[0]["供应商"] == null || ds.Tables[0].Rows[0]["供应商"].ToString() == "") 91 { 92 resultData.Msg = "请在第二行第一列填写供应商名称!"; 93 context.Response.Write(JsonConvert.SerializeObject(resultData)); 94 return; 95 } 96 97 string GysName = ds.Tables[0].Rows[0]["供应商"] + ""; 98 DataTable GetGysBH = cgrk.GetGYSBH(GysName); 99 if (GetGysBH.Rows.Count <= 0) 100 { 101 resultData.Msg = "请核对供应商是否存在!"; 102 context.Response.Write(JsonConvert.SerializeObject(resultData)); 103 return; 104 } 105 string gys = GetGysBH.Rows[0]["BH"].ToString();//供应商编号 106 mod.GysName = GetGysBH.Rows[0]["MC"].ToString();//供应商名称 107 108 109 //科室 110 if (ds.Tables[0].Rows[0]["科室"] != null) 111 { 112 mod.KsName = ds.Tables[0].Rows[0]["科室"] + ""; 113 DataTable GetKSBH = cgrk.GetKSBH(mod.KsName); 114 if (GetKSBH.Rows.Count <= 0) 115 { 116 resultData.Msg = "请正确填写科室!"; 117 context.Response.Write(JsonConvert.SerializeObject(resultData)); 118 return; 119 } 120 mod.KsBH = GetKSBH.Rows[0]["BH"].ToString(); 121 } 122 123 124 DataTable dt = cgrk.GetGZHCByGYS(gys, mod.SBMC); 125 if (dt.Rows.Count <= 0) 126 { 127 resultData.Msg = "请核对高值耗材名称是否存在或删除不存在的耗材"; 128 context.Response.Write(JsonConvert.SerializeObject(resultData)); 129 return; 130 131 } 132 //判断注册证是否存在 133 if (dt.Rows[0]["SCQY"] == null || dt.Rows[0]["SCQY"].ToString() == "") { 134 resultData.Msg = "该耗材: "+ mod.SBMC + " 注册证不存在,请清除再导入!"; 135 context.Response.Write(JsonConvert.SerializeObject(resultData)); 136 return; 137 } 138 139 140 141 //} 142 143 144 //耗材编号 145 mod.SBMCBH = dt.Rows[0]["SBMCBH"] != null ? dt.Rows[0]["SBMCBH"].ToString() : ""; 146 //规格型号 147 mod.GGXH = dt.Rows[0]["SBGG"] != null ? dt.Rows[0]["SBGG"].ToString() : ""; 148 //单位 149 mod.DW = dt.Rows[0]["SBDW"] != null ? dt.Rows[0]["SBDW"].ToString() : ""; 150 //生产厂家 151 mod.SCCJMC = dt.Rows[0]["MC"] != null ? dt.Rows[0]["MC"].ToString() : ""; 152 //生产企业 153 mod.SCCJ = dt.Rows[0]["SCQY"] != null ? dt.Rows[0]["SCQY"].ToString() : ""; 154 //供应商 155 mod.SUPPLIER = gys; 156 // 157 mod.ISSM = "0"; //0表示普通添加明细 158 if (ds.Tables[0].Rows[i]["数量"] != null) 159 { 160 string shuliang = ds.Tables[0].Rows[i]["数量"] + ""; 161 if (long.Parse(shuliang) <= 0) 162 { 163 resultData.Msg = "数量不能为0或为负数"; 164 context.Response.Write(JsonConvert.SerializeObject(resultData)); 165 return; 166 167 } 168 else if (long.Parse(shuliang) > 100) 169 { 170 resultData.Msg = "数量最大为100"; 171 context.Response.Write(JsonConvert.SerializeObject(resultData)); 172 return; 173 } 174 mod.BHSL = shuliang; 175 } 176 else 177 { 178 resultData.Msg = "请填写数量"; 179 context.Response.Write(JsonConvert.SerializeObject(resultData)); 180 return; 181 } 182 183 184 if (ds.Tables[0].Rows[i]["条码数量"] != null) 185 { 186 string shuliang = ds.Tables[0].Rows[i]["条码数量"] + ""; 187 if (long.Parse(shuliang) <= 0) 188 { 189 resultData.Msg = "条码数量不能为0"; 190 context.Response.Write(JsonConvert.SerializeObject(resultData)); 191 return; 192 193 } 194 mod.TMSL = shuliang; 195 } 196 else if (ds.Tables[0].Rows[i]["条码数量"] == null) 197 { 198 mod.TMSL = mod.BHSL;//默认和数量一致 199 } 200 201 202 203 if (ds.Tables[0].Rows[i]["采购单价"] != null) 204 { 205 string dj = ds.Tables[0].Rows[i]["采购单价"] + ""; 206 if (long.Parse(dj) < 0) 207 { 208 resultData.Msg = "采购单价不能小于0"; 209 context.Response.Write(JsonConvert.SerializeObject(resultData)); 210 return; 211 212 } 213 mod.CGDJ = dj; 214 } 215 else if (ds.Tables[0].Rows[i]["采购单价"] == null) 216 { 217 resultData.Msg = "采购单价不能为空"; 218 context.Response.Write(JsonConvert.SerializeObject(resultData)); 219 return; 220 } 221 222 223 224 225 if (ds.Tables[0].Rows[i]["批号"] != null) 226 { 227 string PH = ds.Tables[0].Rows[i]["批号"] + ""; 228 mod.PH = PH; 229 } 230 else if (ds.Tables[0].Rows[i]["批号"] == null) 231 { 232 resultData.Msg = "批号必填"; 233 context.Response.Write(JsonConvert.SerializeObject(resultData)); 234 return; 235 } 236 237 238 239 string nna = ds.Tables[0].Rows[i]["是否含次条码"].ToString(); 240 if (nna == "Y" || nna == "N") 241 { 242 string Barcode = ds.Tables[0].Rows[i]["是否含次条码"] + ""; 243 244 if (Barcode == "Y") 245 { 246 if (ds.Tables[0].Rows[i]["次条码"] != null) 247 { 248 string CTMH = ds.Tables[0].Rows[i]["次条码"] + ""; 249 mod.CTMH = CTMH; 250 } 251 else 252 { 253 resultData.Msg = "请修改是否含有次条码为N 或者填写次条码"; 254 context.Response.Write(JsonConvert.SerializeObject(resultData)); 255 return; 256 } 257 } 258 else if (Barcode == "N") 259 { 260 mod.CTMH = null; 261 } 262 mod.Barcode = Barcode; 263 } 264 else if (ds.Tables[0].Rows[i]["是否含次条码"] == null || ds.Tables[0].Rows[i]["是否含次条码"] + "" == "") 265 { 266 mod.Barcode = "Y"; 267 268 } 269 else 270 { 271 resultData.Msg = "是否含次条码请按需求填写"; 272 context.Response.Write(JsonConvert.SerializeObject(resultData)); 273 return; 274 } 275 276 277 278 279 280 281 282 283 284 285 286 if (ds.Tables[0].Rows[i]["包装合格"].ToString() == "Y" || ds.Tables[0].Rows[i]["包装合格"].ToString() == "N") 287 { 288 289 string BZHG = ds.Tables[0].Rows[i]["包装合格"] + ""; 290 mod.BZHG = BZHG; 291 if (BZHG == "Y") 292 mod.BZHGMC = "是"; 293 else 294 mod.BZHGMC = "否"; 295 296 } 297 else if (ds.Tables[0].Rows[i]["包装合格"] == null || ds.Tables[0].Rows[i]["包装合格"] + "" == "") 298 { 299 mod.BZHG = "Y"; 300 mod.BZHGMC = "是"; 301 } 302 else 303 { 304 resultData.Msg = "包装合格请按需求填写"; 305 context.Response.Write(JsonConvert.SerializeObject(resultData)); 306 return; 307 } 308 309 310 311 if (ds.Tables[0].Rows[i]["验收合格"].ToString() == "Y" || ds.Tables[0].Rows[i]["验收合格"].ToString() == "N") 312 { 313 string YSHG = ds.Tables[0].Rows[i]["验收合格"] + ""; 314 mod.YSHG = YSHG; 315 if (YSHG == "Y") 316 mod.YSHGMC = "是"; 317 else 318 mod.YSHGMC = "否"; 319 320 } 321 else if (ds.Tables[0].Rows[i]["验收合格"] == null || ds.Tables[0].Rows[i]["验收合格"] + "" == "") 322 { 323 mod.YSHG = "Y"; 324 mod.YSHGMC = "是"; 325 } 326 else 327 { 328 resultData.Msg = "验收合格请按需求填写"; 329 context.Response.Write(JsonConvert.SerializeObject(resultData)); 330 return; 331 } 332 333 334 335 mod.MJPH= ds.Tables[0].Rows[i]["灭菌批号"].ToString(); 336 mod.ZTMH = ds.Tables[0].Rows[i]["主条码"].ToString(); 337 mod.CCBH= ds.Tables[0].Rows[i]["出厂编号"].ToString(); 338 339 mod.JE = (Convert.ToInt32(mod.CGDJ) * Convert.ToInt32(mod.BHSL)).ToString(); 340 341 342 if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["有效期"] + "")) 343 { 344 mod.YXQ = Convert.ToDateTime(ds.Tables[0].Rows[i]["有效期"] + "").ToString("yyyy-MM-dd"); 345 } 346 if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["生产日期"] + "")) 347 { 348 mod.SCRQ = Convert.ToDateTime(ds.Tables[0].Rows[i]["生产日期"] + "").ToString("yyyy-MM-dd"); 349 } 350 362 lst.Add(mod); 363 } 364 resultData.Msg = ""; 365 resultData.Data = lst; 366 context.Response.Write(JsonConvert.SerializeObject(resultData)); 367 return; 368 369 370 } 371 else 372 { 373 resultData.Msg = "表格里没有数据，无需导入"; 374 context.Response.Write(JsonConvert.SerializeObject(resultData)); 375 return; 376 377 } 378 } 379 380 381 } 382 } 383 384 385 386 387 388 public bool IsReusable 389 { 390 get 391 { 392 return false; 393 } 394 } 395 } 396 397 398 public class ImportHCDR 399 { 400 public string BHSL { get; set; } 401 public string BZHG { get; set; } 402 public string Barcode { get; set; } 403 public string CGDJ { get; set; } 404 public string CTMH { get; set; } 405 public string DW { get; set; } 406 public string GGXH { get; set; } 407 public string ISSM { get; set; } 408 public string PH { get; set; } 409 public string SBMC { get; set; } 410 public string SBMCBH { get; set; } 411 public string SCCJ { get; set; } 412 public string SCCJMC { get; set; } 413 public string TMSL { get; set; } 414 public string YSHG { get; set; } 415 public string SUPPLIER { get; set; } 416 public string JE { get; set; } 417 public string KsBH { get; set; } 418 public string GysName { get; set; } 419 public string KsName { get; set; } 420 public string YSHGMC { get; set; } 421 public string BZHGMC { get; set; } 422 public string ZTMH { get; set; } 423 public string MJPH { get; set; } 424 public string CCBH { get; set; } 425 public string YXQ { get; set; } 426 public string SCRQ { get; set; } 427 428 429 } 430 431 public class ResultData 432 { 433 public string Msg { get; set; } 434 public List<ImportHCDR> Data { get; set; } 435 }

posted on 2017-05-18 14:12 Apex_233 阅读( ...) 评论( ...) 编辑收藏

转载于:https://www.cnblogs.com/Apex233/p/6873091.html

专利

最新回复(0)