/// <summary> /// 文档格式预定义好的,动态填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExceldt(string obj) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\10配电变压器汇总表.xls"; ex.Open(fname ); IList list=new ArrayList (); int pagecount=0; //obj = "317"; string strfilter = " and 1=1"; if (obj != "") strfilter = strfilter + " and a.OrgCode='" + obj+"' "; IList caplist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", "select distinct b.byqCapcity from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " order by b.byqCapcity"); //caplist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", string.Format("select nr from pj_dyk where len(parentid)>1 and dx='{0}' and sx='{1}'", "11配电变压器卡片", "容量")); //IList modmflist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", "select distinct UPPER(replace(b.byqModle,'-'+cast(b.byqCapcity as nvarchar(50))+'/'+cast(b.byqVol as nvarchar(50)),'')) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " and b.omniseal='true'"); //IList modtmlist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", "select distinct UPPER(replace(b.byqModle,'-'+cast(b.byqCapcity as nvarchar(50))+'/'+cast(b.byqVol as nvarchar(50)),'')) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " and (b.omniseal!='true' or b.omniseal is null)"); IList modmflist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", "select distinct UPPER(b.byqModle) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " and b.omniseal='true'"); IList modtmlist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", "select distinct UPPER(b.byqModle) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " and (b.omniseal!='true' or b.omniseal is null)"); int jmax = 18; int i = 0; for ( i = 0; i < modmflist.Count; i++) { if (modmflist[i].ToString().IndexOf("-")>-1) { modmflist[i] = modmflist[i].ToString().Substring(0,modmflist[i].ToString().IndexOf("-")); } } for (i = 0; i < modtmlist.Count; i++) { if (modtmlist[i].ToString().IndexOf("-")>-1) { modtmlist[i] = modtmlist[i].ToString().Substring(0, modtmlist[i].ToString().IndexOf("-")); } } string modtemp = ""; for (i = 0; i < modmflist.Count; i++) { if (modtemp.IndexOf(modmflist[i].ToString())==-1) { if (modtemp!="") modtemp+=","+modmflist[i]; else modtemp = modmflist[i].ToString(); } } if (modtemp != "") { string[] strli = modtemp.Split(','); modmflist.Clear(); for (i = 0; i < strli.Length; i++) { modmflist.Add(strli[i]); } } for (i = 0; i < modtmlist.Count; i++) { if (modtemp.IndexOf(modtmlist[i].ToString()) == -1) { if (modtemp!="") modtemp += "," + modtmlist[i]; else modtemp = modtmlist[i].ToString(); } } if (modtemp != "") { string[] strli = modtemp.Split(','); modtmlist.Clear(); for (i = 0; i < strli.Length; i++) { modtmlist.Add(strli[i]); } } pagecount = (int)Math.Ceiling(caplist.Count / (jmax + 0.0)); int itemp = modtmlist.Count / 6.0 > modmflist.Count / 3.0 ? (int)Math.Ceiling(modtmlist.Count / 6.0) : (int)Math.Ceiling(modmflist.Count / 3.0 ); pagecount = itemp * pagecount; if (pagecount > 1) { ex.DeleteWorkSheet("Sheet2"); ex.DeleteWorkSheet("Sheet3"); } /////计算需要多少个工作表 for (i = 1; i < pagecount; i++) { ex.CopySheet(1,i); ex.ReNameWorkSheet((i + 1), "Sheet" + (i + 1)); } int istart = 7, istart2 = 19, jstart = 4, j=0; string jstrfilter=""; mOrg org = Client.ClientHelper.PlatformSqlMap.GetOneByKey<mOrg>(obj); for ( j = 0; j < caplist.Count; j++) { ex.ActiveSheet("Sheet" + (j / jmax == 0 ? 1 : (int)Math.Ceiling(j / (jmax + 0.0)))); //ex.ActiveSheet("Sheet" + (1 + (j / jmax) * (int)Math.Ceiling(j / (jmax + 0.0)))); if (j % jmax == 0) { jstrfilter = " and 1=1"; ex.ActiveSheet("Sheet" + (1+(j / jmax)*(int)Math.Ceiling(j / (jmax + 0.0)))); list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", "select b.byqCapcity from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + jstrfilter); if (list.Count >0) ex.SetCellValue(list.Count.ToString(), 5, jstart + j % jmax); list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", "select sum(b.byqCapcity) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + jstrfilter); //if (list[0] == null) list[0] = 0; if (list[0] != null) ex.SetCellValue(list[0].ToString(), 6, jstart + j % jmax); setExceldt(ex, list, caplist, modtmlist, modmflist, jstrfilter, strfilter, jstart, j, jmax, istart, istart2); } ex.ActiveSheet("Sheet" + ((j+1) / jmax == 0 ? 1 : (int)Math.Ceiling((j+1) / (jmax + 0.0)))); jstrfilter = " and 1=1 and b.byqCapcity='" + caplist[j] + "'"; //ex.SetCellValue(caplist[j].ToString(), 4, jstart + j % jmax + 1); setExceldt(ex, list, caplist, modtmlist, modmflist, jstrfilter, strfilter, jstart+1, j, jmax, istart, istart2); } for (i = 0; i < pagecount; i++) { ex.ActiveSheet("Sheet" + (i+1)); int jzu=caplist.Count/jmax+1; int itempmin = 0, itempmax = 0; itempmax = (i % jzu+1) * jmax; itempmin = (i % jzu) * jmax; if (org != null) ex.SetCellValue(org.OrgName, 3, 3); else ex.SetCellValue("全局", 3, 3); for (j = itempmin; j < itempmax && j < caplist.Count ; j++) { ex.SetCellValue(caplist[j].ToString(), 4, jstart + j % jmax + 1); } //ex.SetCellValue(caplist[j].ToString(), 4, jstart + j % jmax + 1); } ex.ActiveSheet(1); ex.ShowExcel(); }