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

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

 <a id="ExcelDy"  class="left disabled" href="javascript:void(0)"><span>
                            <img alt='' src='../images/primeval/import.bmp' /></span>导入入库</a> <span class="left-spner">
                            </span>


<%--导入入库--%>
     <div id="window10" class="hide">
            <div style="margin: 0 auto;  960px;">
                <input type="file" id="file1" name="file" />

            </div>
            <div class="jg-btm" style=" 300px;">
                <input type="button" class="bc-btn" value="上传并导入" id="daoru" />
            </div>
        </div>

<script src="../Scripts/ajaxfileupload.js" type="text/javascript"></script>

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

     window.onload = function () {
                $("#ExcelDy").removeClass("disabled");
        }

        var win10 = $("#window10").window({ title: "导入入库",  420, height: 210, bodyPadding: 0, onClosing: function () { win10.window("close"); } });
        $("#ExcelDy").click(function () {            
            win10.window("show");
        });
        $("#daoru").click(function () {
            ajaxFileUpload();
        });
        function ajaxFileUpload() {
            var gkhm;
            $.ajaxFileUpload(
                {
                    url: 'data/upload.ashx', //用于文件上传的服务器端请求地址
                    secureuri: false, //是否需要安全协议，一般设置为false
                    fileElementId: 'file1', //文件上传域的ID
                    dataType: 'json', //返回值类型 一般设置为json
                    data: { GYS: $("#SUPPLIER").val() },
                    success: function (data)  //服务器成功响应处理函数
                    {
                        debugger
                        //
                        if (data.Msg == "") {

                            $.each(data.Data, function (index, dataObj, c) {
                                                      
                            var e = data.Data[index];
                            $("#text8").dgaddrow(e);
                            $("#SUPPLIER1").val(e.GysName); 
                            $("#SUPPLIER").val(e.SUPPLIER);
                            $("#LYKSMC").val(e.KsName); 
                            $("#LYKS").val(e.KsBH);
                            })
                           
                            win10.window("close");
                        } else {
                            alert(data.Msg);
                        }
                       

                    },
                    error: function (data, status, e)//服务器响应失败处理函数
                    {
                        alert(data.Msg);
                    }
                }
            )
            
        }

把文件读取到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     }