房租管理小软件（三）：导入导出数据
1.把Excel中数据导入到Sqlserver 中，使用Linq 事务的方式的方式
1 string resultFile = ""; 2 3 4 OpenFileDialog openFileDialog1 = new OpenFileDialog(); 5 openFileDialog1.InitialDirectory = "D://"; 6 openFileDialog1.Filter = "All files (*.*)|*.*|EXCEL files (*.xls)|*.xls"; 7 openFileDialog1.FilterIndex = 2; 8 openFileDialog1.RestoreDirectory = true; 9 if (openFileDialog1.ShowDialog() == DialogResult.OK) 10 { 11 resultFile = openFileDialog1.FileName; 12 13 14 15 Workbook SourceBook1 = new Workbook(); 16 try 17 { 18 SourceBook1.Open(resultFile); 19 } 20 catch (Exception ex) { 21 MessageBox.Show("请关闭Excel文件，再导入"); 22 return; 23 } 24 Worksheet workSheet = SourceBook1.Worksheets[0]; 25 Cells cell = workSheet.Cells; 26 DevExpress.Utils.WaitDialogForm wdf = new DevExpress.Utils.WaitDialogForm("正在加载数据，请等待!"); 27 MyFZDataContext dataContext = new MyFZDataContext(); 28 if (dataContext.Connection != null) { dataContext.Connection.Open(); } 29 System.Data.Common.DbTransaction tran = dataContext.Connection.BeginTransaction(); 30 dataContext.Transaction = tran; 31 int j = 0; 32 try 33 { 34 35 int rowNum = cell.MaxDataRow; 36 for (int i = 2; i <= rowNum; i++) 37 { 38 j = i; 39 string s_NO = cell[i, 0].StringValue;// 40 string s_RoomNum = cell[i, 1].StringValue;//商铺号 41 //通过商铺号，得到房账号 42 var vf = from t in dataContext.T2_Enter_PersonGroup where t.RoomNum == s_RoomNum && t.Status == "未退房" select t; 43 if (vf.Count() != 1) { continue; } 44 string fzh = vf.Take(1).Single().FZHNumber; 45 string s_YearMonth = cell[i, 2].StringValue;//月份 46 string s_FZH = fzh;//房账号 47 string s_dds = cell[i, 4].StringValue;//底读数 48 string s_cbs = cell[i, 5].StringValue;//抄表数 49 string s_dlydl = cell[i, 6].StringValue;//独表用电量数 50 string s_dldf = cell[i, 7].StringValue;//独立电费 51 string s_ftdl = cell[i, 8].StringValue;//分滩电量 52 string s_ftdf = cell[i, 9].StringValue;//分滩电费 53 string s_lb = cell[i, 3].StringValue;//类别 54 if (s_lb == "水费") 55 { 56 DateTime tempTime = Date.getFirstDate(s_YearMonth); 57 var vc = from t in dataContext.T2_ZW_Detail where t.FZHNumber == s_FZH && t.FeeCode == 4 && t.FeeTime == tempTime select t; 58 59 if (vc.Count() > 0) 60 { 61 //MessageBox.Show("已导入过本月水费数据，不能再次导入！"); 62 //return; 63 continue; 64 } 65 66 var v1 = from t in dataContext.T2_Enter_PersonGroup where t.FZHNumber == s_FZH && t.ZWStatus == "已结" select t; 67 if (v1.Count() == 1) { continue; }//已结账的不能导入 68 T1_QX_MySystemUser user = (T1_QX_MySystemUser)AppDomain.CurrentDomain.GetData("user"); 69 70 71 //save 72 73 74 75 T2_ZW_Detail tzd = new T2_ZW_Detail(); 76 tzd.DJH = DataFunction.getXFID("XF", dataContext);//XF为消费 77 tzd.FZHNumber = s_FZH; 78 79 tzd.FeeCode = 4; //水费 80 tzd.ThePercent = Double.Parse("1.00"); 81 tzd.TheCount = 1; 82 tzd.Price = Convert.ToDecimal(s_dldf) + Convert.ToDecimal(s_ftdf); 83 tzd.Amount = Convert.ToDecimal(s_dldf) + Convert.ToDecimal(s_ftdf); 84 tzd.FAmount = 0.0m; 85 tzd.EnterTime = DataBase.Date.getNow(); 86 tzd.FeeTime = Date.getFirstDate(s_YearMonth); 87 tzd.EnterPerson = user.UserName; 88 tzd.JRFS = "导录"; 89 tzd.Status = "未结"; 90 tzd.JZFS = "消费"; 91 tzd.XGID = ""; 92 tzd.RoomNum = s_RoomNum; 93 tzd.Memo = "底读数:" + s_dds + " " + "抄表数:" + s_cbs + " " + "独表用水量数:" + s_dlydl + " " + "分滩水量:" + s_ftdl + " " + "分滩水费:" + s_ftdf + " "; 94 tzd.ZZDH = ""; 95 dataContext.T2_ZW_Detail.InsertOnSubmit(tzd); 96 dataContext.SubmitChanges(); 97 98 } 99 100 } 101 tran.Commit(); 102 MessageBox.Show("导入成功！"); 103 } 104 catch (Exception ex) 105 { 106 107 tran.Rollback(); 108 MessageBox.Show("格式错!"+ex.ToString()); 109 } 110 wdf.Hide(); 111 112 }
2.使用aspose.word 模板生成合同
1 //合同生成 2 3 Document doc = new Document(".//ht.doc"); 4 DocumentBuilder builder = new DocumentBuilder(doc); 5 6 7 // 执行数据更新 8 MyFZDataContext dataContext = new MyFZDataContext(false); 9 10 T2_Enter_PersonGroup t2 = dataContext.T2_Enter_PersonGroup.Single(c => c.AutoID.ToString() == this.DanNumber); 11 //楼层 12 var v1 = from t in dataContext.T1_FloorRoom 13 where 14 t.AutoID == 15 ((from t0 in dataContext.T1_FloorRoom 16 where 17 t0.NodeName == t2.RoomNum 18 select new 19 { 20 t0.FID 21 }).First().FID) 22 select new 23 { 24 t.NodeName 25 }; 26 27 //经营范围 28 var v_J = from t in dataContext.T1_JYFW 29 where 30 (from t0 in dataContext.T2_Enter_PersonGroup 31 where 32 t0.AutoID.ToString() == this.DanNumber 33 select new 34 { 35 a = t0.JYFW 36 }).Contains(new { a = t.AutoID.ToString() }) 37 select new 38 { 39 t.JYName 40 }; 41 doc.MailMerge.Execute( 42 new string[] { "CZF", "LC", "RoomNum", "AREA", "JYFW", "JYQS", "COMEDATE", "OUTTIME", "ZJ", "YZZJ", "DXZJ", "SPBZJ", "DXSPBZJ", "GLF" },//承租方，楼层,房号，面积，经营范围,期数,开始时间，离开时间，租金,月租增减,大写租金 43 new object[] { t2.CZF, v1.Take(1).Single().NodeName, t2.RoomNum, Math.Round(t2.Area.Value, 2).ToString(), v_J.Take(1).Single().JYName, t2.QS.ToString(), t2.ComeDate.Value.ToString("yyyy年MM月dd日"), t2.OutTime.Value.ToString("yyyy年MM月dd日"), t2.FZMoney.Value.ToString("f2"), t2.YZZJ, DataFunction.GetNumToUpper(t2.FZMoney.Value), getTotalMoney().ToString("f2"), DataFunction.GetNumToUpper(getTotalMoney()), t2.WYMoney.Value.ToString("f2") } 44 ); 45 46 47 SaveFileDialog saveFileDialog = new SaveFileDialog(); 48 49 saveFileDialog.Filter = "word文件(*.doc)|*.doc|所有文件(*.*)|*.*"; 50 if (saveFileDialog.ShowDialog() == DialogResult.OK) 51 { 52 doc.Save(saveFileDialog.FileName);//也可以保存为1.doc 兼容03-07 53 54 MessageBox.Show("生成成功!"); 55 56 System.Diagnostics.Process.Start(saveFileDialog.FileName); 57 return; 58 }
