/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(sdjl_26 obj) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\送电17防护通知书.xls"; int sz = 30;//一行最多30个 ex.Open(fname); //此处写填充内容代码 ex.ActiveSheet(1); //通知单位 ex.SetCellValue(obj.tzdw, 5, 1); //电压 ex.SetCellValue(obj.lineVol, 6, 7); //线路 ex.SetCellValue(obj.c1, 6, 9); //发现问题 List<string> fxwtList = GetList(obj.fxwt, sz); for (int i = 0; i < fxwtList.Count; i++) { ex.SetCellValue(fxwtList[i], 7+i, 1); if (i > 1) break; } //处理措施 List<string> clcsList = GetList(obj.clcs, sz); for (int i = 0; i < clcsList.Count; i++) { ex.SetCellValue(obj.clcs, 12+i, 1); if (i > 2) break; } ex.ShowExcel(); }
void gridViewOperation_AfterAdd(PJ_24 e) { DSOFramerControl dsoFramerControl1 = new DSOFramerControl(); Microsoft.Office.Interop.Excel.Workbook wb;dsoFramerControl1.FileData = e.BigData; wb = dsoFramerControl1.AxFramerControl.ActiveDocument as Microsoft.Office.Interop.Excel.Workbook; ExcelAccess ea = new ExcelAccess(); ea.MyWorkBook = wb; ea.MyExcel = wb.Application; ea.SetCellValue(ParentObj.OrgName , 4, 9); dsoFramerControl1.FileSave(); e.BigData = dsoFramerControl1.FileData; dsoFramerControl1.FileClose(); dsoFramerControl1.Dispose(); dsoFramerControl1 = null; }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(PJ_24 jl) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\24设备变更通知书.xls"; ex.Open(fname); int row = 1; int col = 1; // ex.SetCellValue(jl.sj.Year.ToString(), row + 8, col); ex.SetCellValue(jl.sj.Month.ToString(), row + 8, col+2); ex.SetCellValue(jl.sj.Day.ToString(), row + 8, col+4); ex.SetCellValue(jl.dd, row + 5, col+6); ex.SetCellValue(jl.nr, row + 5, col + 7); ex.SetCellValue(jl.Remark, row + 5, col + 10); ex.ShowExcel(); }
private void btnOK_Click(object sender, EventArgs e) { if (rowData.BigData.Length == 0) { mOrg org = MainHelper.PlatformSqlMap.GetOneByKey<mOrg>(rowData.ParentID); string fname = Application.StartupPath + "\\00记录模板\\23配电线路产权维护范围协议书.xls"; string bhname = org.OrgName.Replace("供电所", ""); DSOFramerControl dsoFramerControl1 = new DSOFramerControl(); dsoFramerControl1.FileOpen(fname); Microsoft.Office.Interop.Excel.Workbook wb = dsoFramerControl1.AxFramerControl.ActiveDocument as Microsoft.Office.Interop.Excel.Workbook; //PJ_23 obj = (PJ_23)MainHelper.PlatformSqlMap.GetObject("SelectPJ_23List", "where ParentID='" + rowData.ParentID + "' and xybh like '" + SelectorHelper.GetPysm(org.OrgName.Replace("供电所", ""), true) + "" + DateTime.Now.Year.ToString() + "%' order by xybh ASC"); //int icount = 1; //if (obj != null && obj.xybh !="") //{ // icount = Convert.ToInt32(obj.xybh.Split('-')[2])+1; //} //string strname = SelectorHelper.GetPysm(bhname, true); ExcelAccess ea = new ExcelAccess(); ea.MyWorkBook = wb; ea.MyExcel = wb.Application; ea.SetCellValue(rowData.xybh.ToUpper(), 4, 8); //strname = DateTime.Now.Year.ToString(); //ea.SetCellValue(strname, 4, 9); //strname = string.Format("{0:D3}", icount); //ea.SetCellValue(strname, 4, 10); ea.SetCellValue(rowData.linename, 10, 7); ea.SetCellValue(rowData.fzlinename, 10, 10); ea.SetCellValue("'" + rowData.gh, 10, 16); ea.SetCellValue(rowData.cqfw, 11, 4); ea.SetCellValue(rowData.cqdw, 13, 4); ea.SetCellValue(rowData.qdrq.Year.ToString(), 21, 7); ea.SetCellValue(rowData.qdrq.Month.ToString(), 21, 9); ea.SetCellValue(rowData.qdrq.Day.ToString(), 21, 11); dsoFramerControl1.FileSave(); rowData.BigData = dsoFramerControl1.FileData; dsoFramerControl1.FileClose(); dsoFramerControl1.Dispose(); dsoFramerControl1 = null; //rowData.xybh = SelectorHelper.GetPysm(bhname, true).ToUpper() + "-" + DateTime.Now.Year.ToString() + "-" + string.Format("{0:D3}", icount); } DSOFramerControl dsoFramerControl2 = new DSOFramerControl(); dsoFramerControl2.FileData = rowData.BigData; Microsoft.Office.Interop.Excel.Workbook wb2 = dsoFramerControl2.AxFramerControl.ActiveDocument as Microsoft.Office.Interop.Excel.Workbook; ExcelAccess ea2 = new ExcelAccess(); ea2.MyWorkBook = wb2; ea2.MyExcel = wb2.Application; ea2.SetCellValue(comboBoxEdit1.Text, 11, 4); dsoFramerControl2.FileSave(); rowData.BigData = dsoFramerControl2.FileData; dsoFramerControl2.FileClose(); dsoFramerControl2.Dispose(); dsoFramerControl2 = null; }
/// <summary> ///创建工作表并将数据加入到工作表中 /// </summary> /// <param name="ex">操作EXCEL表</param> /// <param name="bdzlist">填入的数据</param> /// <param name="hs">一页对应的总行数</param> /// <param name="stawz">一页中填入的开始的位置</param> /// <param name="pageindex">一页填入的列位置</param> /// <param name="strnumcol">第一个字符开始的行数</param> /// <param name="boldnum">加粗的数量</param> public static void CreatandWritesheet1(ExcelAccess ex, List<string> bdzlist, int hs, int star, int clm,int[] strnumcol,int[] boldnum) { int pageindex = 1; if (pageindex < Ecommonjh.GetPagecount(bdzlist.Count, hs)) { pageindex = Ecommonjh.GetPagecount(bdzlist.Count, hs); } for (int j = 1; j <= pageindex; j++) { if (j > 1) { ex.CopySheet(1, 1); } } ex.ShowExcel(); for (int j = 1; j <= pageindex; j++) { ex.ActiveSheet(j); ex.ReNameWorkSheet(j, "Sheet" + (j)); int prepageindex = j - 1; //主题 int starow = prepageindex * hs + 1; int endrow = j * hs; if (bdzlist.Count > endrow) { for (int i = 0; i < hs; i++) { ex.SetCellValue(bdzlist[starow - 1 + i], star + i, clm); //加粗过程 for (int n = 0; n < strnumcol.Length;n++ ) { if (starow-1+i==strnumcol[n]) { ex.SetFontBold(star + i, clm, star + i, clm, true, 0, boldnum[n]); } } //ex.SetCellValue(objlist[starow - 1 + i].rq.Month.ToString(), rowcount + i, 1); //ex.SetCellValue(objlist[starow - 1 + i].rq.Day.ToString(), rowcount + i, 2); //ex.SetCellValue(objlist[starow - 1 + i].rq.Hour.ToString(), rowcount + i, 3); //ex.SetCellValue(objlist[starow - 1 + i].rq.Minute.ToString(), rowcount + i, 4); //ex.SetCellValue(objlist[starow - 1 + i].lxfs, rowcount + i, 5); //ex.SetCellValue(objlist[starow - 1 + i].yhdz, rowcount + i, 6); //ex.SetCellValue(objlist[starow - 1 + i].gzjk, rowcount + i, 7); //ex.SetCellValue(objlist[starow - 1 + i].djr, rowcount + i, 8); //ex.SetCellValue(objlist[starow - 1 + i].clr, rowcount + i, 9); } } else if (bdzlist.Count <= endrow && bdzlist.Count >= starow) { for (int i = 0; i < bdzlist.Count - starow + 1; i++) { ex.SetCellValue(bdzlist[starow - 1 + i], star + i, clm); //加粗过程 for (int n = 0; n < strnumcol.Length; n++) { if (starow - 1 + i == strnumcol[n]) { ex.SetFontBold(star + i, clm, star + i, clm, true, 0, boldnum[n]); } } //ex.SetCellValue(objlist[starow - 1 + i].rq.Month.ToString(), rowcount + i, 1); //ex.SetCellValue(objlist[starow - 1 + i].rq.Day.ToString(), rowcount + i, 2); //ex.SetCellValue(objlist[starow - 1 + i].rq.Hour.ToString(), rowcount + i, 3); //ex.SetCellValue(objlist[starow - 1 + i].rq.Minute.ToString(), rowcount + i, 4); //ex.SetCellValue(objlist[starow - 1 + i].lxfs, rowcount + i, 5); //ex.SetCellValue(objlist[starow - 1 + i].yhdz, rowcount + i, 6); //ex.SetCellValue(objlist[starow - 1 + i].gzjk, rowcount + i, 7); //ex.SetCellValue(objlist[starow - 1 + i].djr, rowcount + i, 8); //ex.SetCellValue(objlist[starow - 1 + i].clr, rowcount + i, 9); } } } }
private void btnOK_Click(object sender, EventArgs e) { //if (comboBoxEdit1.Text == "") //{ // MsgBox.ShowTipMessageBox("变动地点不能为空。"); // comboBoxEdit1.Focus(); // return; //} ////if (rowData.BigData == null) ////{ //// rowData.BigData = new byte[0]; ////} //this.DialogResult = DialogResult.OK; //this.Close(); IList<PJ_24> list = Client.ClientHelper.PlatformSqlMap.GetList<PJ_24>("where ParentID='" + rowData.ParentID + "'"); string bh = (list.Count + 1).ToString("000"); DSOFramerControl dsoFramerControl1 = new DSOFramerControl(); Microsoft.Office.Interop.Excel.Workbook wb; if (rowData.BigData == null || rowData.BigData.Length == 0) { string fname = Application.StartupPath + "\\00记录模板\\24设备变更通知书.xls"; dsoFramerControl1.FileOpen(fname); } else dsoFramerControl1.FileData = rowData.BigData; wb = dsoFramerControl1.AxFramerControl.ActiveDocument as Microsoft.Office.Interop.Excel.Workbook; ExcelAccess ea = new ExcelAccess(); ea.MyWorkBook = wb; ea.MyExcel = wb.Application; DateTime dt = Convert.ToDateTime(dateEdit1.EditValue); ea.SetCellValue(dt.Year.ToString(), 4, 7); ea.SetCellValue(bh, 4, 10); ea.SetCellValue(dt.Year.ToString(), 9, 1); ea.SetCellValue(dt.Month.ToString(), 9, 3); ea.SetCellValue(dt.Day.ToString(), 9, 5); ea.SetCellValue(comboBoxEdit1.Text, 6, 7); ea.SetCellValue(memoEdit1.Text, 6, 8); ea.SetCellValue(memoEdit2.Text, 6, 11); dsoFramerControl1.FileSave(); rowData.BigData = dsoFramerControl1.FileData; dsoFramerControl1.FileClose(); dsoFramerControl1.Dispose(); dsoFramerControl1 = null; }
public void ExportExcel(ExcelAccess ex, IList<PJ_sdytz> datalist) { //此处写填充内容代码 int row = 5; int col = 1; int rowcount =14; // //加页 int pageindex = 1; if (pageindex < Ecommon.GetPagecount(datalist.Count, rowcount)) { pageindex = Ecommon.GetPagecount(datalist.Count, rowcount); } for (int j = 1; j <= pageindex; j++) { if (j > 1) { ex.CopySheet(1, 1); } } for (int j = 0; j < datalist.Count; j++) { if (j % rowcount == 0) { ex.ActiveSheet(j / rowcount + 1); } ex.SetCellValue(((j + 1)).ToString(), row + j % rowcount, col); ex.SetCellValue(datalist[j].khmc, row + j % rowcount, col + 1); ex.SetCellValue(datalist[j].khdz, row + j % rowcount, col + 2); ex.SetCellValue(datalist[j].zdyOrgName, row + j % rowcount, col + 3); ex.SetCellValue(datalist[j].zdyLineName, row + j % rowcount, col + 4); ex.SetCellValue(datalist[j].zdykgModle, row + j % rowcount, col + 5); ex.SetCellValue(datalist[j].fdyOrgName, row + j % rowcount, col + 6); ex.SetCellValue(datalist[j].fdyLineName, row + j % rowcount, col + 7); ex.SetCellValue(datalist[j].fdykgModle, row + j % rowcount, col + 8); ex.SetCellValue(datalist[j].kgfs, row + j % rowcount, col + 9); ex.SetCellValue(datalist[j].Remark, row + j % rowcount, col + 10); } }
/// <summary> /// 设置密封数据 /// </summary> /// <param name="ex"></param> /// <param name="list"></param> /// <param name="modmflist"></param> /// <param name="jstrfilter"></param> /// <param name="strfilter"></param> /// <param name="jstart"></param> /// <param name="j"></param> /// <param name="jmax"></param> /// <param name="istart2"></param> /// <param name="imf"></param> public static void setExcelmfdt(ExcelAccess ex, IList list, IList modmflist, string jstrfilter, string strfilter, int jstart, int j, int jmax, int istart2,int imf) { //string str = "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 + " and b.byqModle='" + modmflist[imf] + "-'+cast(b.byqCapcity as nvarchar(50))+'/'+ cast(b.byqVol as nvarchar(50)) and (b.omniseal='true' )" + jstrfilter; string str = "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 + " and b.byqModle like '" + modmflist[imf] + "%' and (b.omniseal='true' )" + jstrfilter; list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", str); if (list.Count > 0) ex.SetCellValue(list.Count.ToString(), istart2 + (imf % 3) * 2, jstart + j % jmax); //str = "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 + " and b.byqModle='" + modmflist[imf] + "-'+cast(b.byqCapcity as nvarchar(50))+'/'+ cast(b.byqVol as nvarchar(50)) and (b.omniseal='true' )" + jstrfilter; str = "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 + " and b.byqModle like '" + modmflist[imf] + "%' and (b.omniseal='true' )" + jstrfilter; list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", str); //if (list[0] == null) list[0] = 0; if (list[0] != null) ex.SetCellValue(list[0].ToString(), istart2 + (imf % 3) * 2 + 1, jstart + j % jmax); }
/// <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(); }
public static int ExportToExcel(string title, string dw, sdjls_sbpjb pj17) { string fname = Application.StartupPath + "\\00记录模板\\送管22送电线路设备评级表.xls"; DSOFramerControl dsoFramerWordControl1 = new DSOFramerControl(); string outfname = Path.GetTempFileName() + ".xls"; File.Copy(fname, outfname); dsoFramerWordControl1.FileOpen(outfname); Microsoft.Office.Interop.Excel.Worksheet xx; Excel.Workbook wb = dsoFramerWordControl1.AxFramerControl.ActiveDocument as Excel.Workbook; ExcelAccess ex = new ExcelAccess(); ex.MyWorkBook = wb; ex.MyExcel = wb.Application; sd_xl xl = MainHelper.PlatformSqlMap.GetOne<sd_xl>(" where LineCode='" + pj17.LineCode + "'"); try { if (xl == null) { MsgBox.ShowWarningMessageBox("数据出错,没找到线路"); return -1; } string strLinexh = xl.WireType;//导线型号 //IList<sd_gt> gtlis = Client.ClientHelper.PlatformSqlMap.GetList<sd_gt>(" Where LineCode='" + xl.LineCode + "' order by gtcode"); int gtcount = Client.ClientHelper.PlatformSqlMap.GetRowCount<sd_gt>(" Where LineCode='" + xl.LineCode + "'"); ex.ActiveSheet(1); //设置线路值 ex.SetCellValue(xl.LineName, 3, 3); ex.SetCellValue(xl.LineVol, 3, 8); ex.SetCellValue(xl.WireLength.ToString(), 3, 11); ex.SetCellValue(xl.InDate.Year + "年" + xl.InDate.Month + "月", 3, 13); //ex.SetCellValue(xl.InDate.Year.ToString(), 4, 9); //ex.SetCellValue(xl.InDate.Month.ToString(), 4, 11); //评级日期 ex.SetCellValue(DateTime.Now.Year + "年" + DateTime.Now.Month + "月" + DateTime.Now.Day + "日", 4, 3); //杆塔 ex.SetCellValue(gtcount.ToString(), 5, 3);//合计 ex.SetCellValue(gtcount.ToString(), 6, 3);//一类 //导地线 ex.SetCellValue(xl.WireLength.ToString(), 9, 3);//合计 ex.SetCellValue(xl.WireLength.ToString(), 10, 3);//一类 //绝缘子 string sql = "in ("; //foreach (sd_gt gt in gtlis) //{ // sql+="'"+gt.gtID+"',"; //} //sql = sql.Substring(0, sql.Length - 1) + ")"; sql = "in (select gtid from sd_gt Where LineCode='" + xl.LineCode + "' )"; string strSQL = "select sbid from sd_gtsb Where gtID " + sql; IList jdzzList = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", strSQL); strSQL += " and ("+getNamesql()+")"; IList jyuzlist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", strSQL); ex.SetCellValue(jyuzlist.Count.ToString(), 13, 3); ex.SetCellValue(jyuzlist.Count.ToString(), 14, 3); //接地装置 ex.SetCellValue(jdzzList.Count.ToString(), 17, 3); ex.SetCellValue(jdzzList.Count.ToString(), 18, 3); //其它 //评定等级 ex.SetCellValue("一级", 24, 3); //评级负责人 if (MainHelper.User != null) { ex.SetCellValue(MainHelper.User.UserName, 25, 3); } } catch (Exception exmess) { MsgBox.ShowTipMessageBox(exmess.Message.ToString()); } dsoFramerWordControl1.FileSave(); pj17.BigData = dsoFramerWordControl1.FileDataGzip; dsoFramerWordControl1.FileClose(); dsoFramerWordControl1.Dispose(); //#endregion //System.Diagnostics.Process.Start(outfname); return 1; }
public void ExportExcel(ExcelAccess ex, IList<PJ_sbbzqsbgmxb4> datalist, string orgid) { //此处写填充内容代码 int row = 7; int col = 1; int rowcount = 13; // //加页 int pageindex = 1; if (pageindex < Ecommon.GetPagecount(datalist.Count, rowcount)) { pageindex = Ecommon.GetPagecount(datalist.Count, rowcount); } for (int j = 1; j < pageindex; j++) { ex.CopySheet(1, j); } for (int j = 0; j < datalist.Count; j++) { if (j % rowcount == 0) { if (j == 0) ex.ActiveSheet(1); else ex.ActiveSheet((j / rowcount + 1)); if (orgid != "") ex.SetCellValue(datalist[j].OrgName, 4, 2); else ex.SetCellValue(MainHelper.UserCompany, 4, 2); ex.SetCellValue(DateTime.Now.ToString("yyyy年MM月dd日"), 4, 12); } ex.SetCellValue((j + 1).ToString(), row + j % rowcount, col); ex.SetCellValue(datalist[j].sssbmc, row + j % rowcount, col + 1); ex.SetCellValue(datalist[j].sssswz, row + j % rowcount, col + 2); string[] tempstr = datalist[j].sssbbh.Split(','); try { ex.SetCellValue(tempstr[0], row + j % rowcount, col + 3); ex.SetCellValue(tempstr[1], row + j % rowcount, col + 4); ex.SetCellValue(tempstr[2], row + j % rowcount, col + 5); } catch { } string[] tempstr1 = datalist[j].S1.Split(','); try { ex.SetCellValue(tempstr1[0], row + j % rowcount, col + 6); ex.SetCellValue(tempstr1[1], row + j % rowcount, col + 7); ex.SetCellValue(tempstr1[2], row + j % rowcount, col + 8); ex.SetCellValue(tempstr1[3], row + j % rowcount, col + 9); } catch { } ex.SetCellValue(datalist[j].xw, row + j % rowcount, col + 10); ex.SetCellValue(datalist[j].statuts, row + j % rowcount, col + 11); } }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(IList<PJ_qxfl> objlist) { #region 原先的 //if (objlist == null || objlist.Count == 0) //{ // return; //} ////lgm //ExcelAccess ex = new ExcelAccess(); //SaveFileDialog saveFileDialog1 = new SaveFileDialog(); //string fname = Application.StartupPath + "\\00记录模板\\缺陷分类统计表.xls"; //ex.Open(fname); ////每行显示文字长度 //int zc = 20; ////与会人员之间的间隔符号 //char[] jksign = new char[1] { ';' }; //int row = 6; //int col = 2; //int len1 =1; //ex.SetCellValue(objlist[0].OrgName, 3, 2); //int pagecout = Ecommon.GetPagecount(objlist.Count,12); ////复制空模板 //for (int m = 1; m < pagecout; m++) //{ // ex.CopySheet(1, m); // ex.ReNameWorkSheet(m + 1, "Sheet" + (m + 1)); //} //for (int p = 0; p < pagecout; p++) //{ // ex.ActiveSheet(p + 1); // for (int i = 0; i < 9; i++) // { // if (p*9+i>=objlist.Count) // { // break; // } // PJ_qxfl tempobj = objlist[p *12 + i]; // //缺陷情况 // ex.SetCellValue(tempobj.qxnr, row + i * len1, 2); // //缺陷类别 // ex.SetCellValue(tempobj.qxlb, row + i * len1,3); // //发现日期 // if (ComboBoxHelper.CompreDate(tempobj.xssj)) // { // ex.SetCellValue(tempobj.xssj.Year.ToString(), row + i * len1, 4); // ex.SetCellValue(tempobj.xssj.Month.ToString(), row + i * len1, 5); // ex.SetCellValue(tempobj.xssj.Day.ToString(), row + i * len1, 6); // } // //消除时限 // if (tempobj.xcqx!=string.Empty) // { // if (ComboBoxHelper.CompreDate(Convert.ToDateTime(tempobj.xcqx))) // { // ex.SetCellValue(Convert.ToDateTime(tempobj.xcqx).Year.ToString(), row + i * len1, 7); // ex.SetCellValue(Convert.ToDateTime(tempobj.xcqx).Month.ToString(), row + i * len1, 8); // ex.SetCellValue(Convert.ToDateTime(tempobj.xcqx).Day.ToString(), row + i * len1, 9); // } // } // //备注 // ex.SetCellValue(tempobj.remark, row + i * len1,10); // } //} //ex.ActiveSheet(1); //ex.ShowExcel(); #endregion if (objlist == null || objlist.Count == 0) { return; } ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\缺陷分类统计表.xls"; ex.Open(fname); ex.SetCellValue(objlist[0].OrgName, 2, 2); for (int i = 0; i < objlist.Count; i++) { ex.SetCellValue((i + 1).ToString(), i +4, 1); ex.SetCellValue(objlist[i].qxly, i + 4, 2); ex.SetCellValue(objlist[i].xlqd, i + 4, 3); ex.SetCellValue(objlist[i].xssj.ToString("yyyy年MM月dd日"), i + 4, 4); ex.SetCellValue(objlist[i].qxnr, i + 4, 5); ex.SetCellValue(objlist[i].qxlb, i + 4, 6); ex.SetCellValue(objlist[i].xcqx, i + 4, 7); } ex.ActiveSheet(1); ex.ShowExcel(); }
public void ExportExcel(ExcelAccess ex ,IList<PJ_clcrkd> datalist) { //此处写填充内容代码 int row = 7; int col = 1; int rowcount = 10; // if (datalist.Count < 1) return; Regex r1 = new Regex("[0-9]+"); string str = r1.Match(datalist[0].num).Value; if (str == "") { str = datalist[0].num; } string tablename = datalist[0].ssgc + datalist[0].ssxm + str; if (tablename.Length > 30) { tablename = tablename.Substring(tablename.Length - 31); } //加页 int pageindex = 1; if (pageindex < Ecommon.GetPagecount(datalist.Count, rowcount)) { pageindex = Ecommon.GetPagecount(datalist.Count, rowcount); } for (int j = 1; j <= pageindex; j++) { ex.CopySheet(1, j); if (j == 1) ex.ReNameWorkSheet(j + 1, tablename); else ex.ReNameWorkSheet(j + 1, tablename + "(" + (j) + ")"); } for (int j = 0; j < datalist.Count; j++) { if (j % rowcount == 0) { if (j == 0) ex.ActiveSheet(tablename); else ex.ActiveSheet(tablename + "(" + (j / rowcount + 1) + ")"); ex.SetCellValue(datalist[j].ssgc, 2, 3); ex.SetCellValue(datalist[j].ssxm, 4, 2); ex.SetCellValue(datalist[j].ckdate.ToString("yyyy"), 4, 7); ex.SetCellValue(datalist[j].ckdate.ToString("MM"), 4, 9); ex.SetCellValue(datalist[j].ckdate.ToString("dd"), 4, 11); ex.SetCellValue(datalist[j].ssgc, 22, 3); ex.SetCellValue(datalist[j].ssxm, 24, 2); ex.SetCellValue(datalist[j].ckdate.ToString("yyyy"), 24, 7); ex.SetCellValue(datalist[j].ckdate.ToString("MM"), 24, 9); ex.SetCellValue(datalist[j].ckdate.ToString("dd"), 24, 11); ex.SetCellValue(datalist[j].ssgc, 42, 3); ex.SetCellValue(datalist[j].ssxm, 44, 2); ex.SetCellValue(datalist[j].ckdate.ToString("yyyy"), 44, 7); ex.SetCellValue(datalist[j].ckdate.ToString("MM"), 44, 9); ex.SetCellValue(datalist[j].ckdate.ToString("dd"), 44, 11); ex.SetCellValue(datalist[j].ssgc, 62, 3); ex.SetCellValue(datalist[j].ssxm, 64, 2); ex.SetCellValue(datalist[j].indate.ToString("yyyy"), 64, 7); ex.SetCellValue(datalist[j].indate.ToString("MM"), 64, 9); ex.SetCellValue(datalist[j].indate.ToString("dd"), 64, 11); } ex.SetCellValue(datalist[j].wpmc, row + j % rowcount , col); ex.SetCellValue(datalist[j].wpgg, row + j % rowcount , col + 2); ex.SetCellValue(datalist[j].wpdw, row + j % rowcount, col + 4); ex.SetCellValue(datalist[j].cksl, row + j % rowcount, col + 5); ex.SetCellValue(datalist[j].wpdj, row + j % rowcount , col + 7); ex.SetCellValue(datalist[j].wpmc, row + j % rowcount+20, col); ex.SetCellValue(datalist[j].wpgg, row + j % rowcount + 20, col + 2); ex.SetCellValue(datalist[j].wpdw, row + j % rowcount + 20, col + 4); ex.SetCellValue(datalist[j].cksl, row + j % rowcount + 20, col + 5); ex.SetCellValue(datalist[j].wpdj, row + j % rowcount + 20, col + 7); ex.SetCellValue(datalist[j].wpmc, row + j % rowcount + 40, col); ex.SetCellValue(datalist[j].wpgg, row + j % rowcount + 40, col + 2); ex.SetCellValue(datalist[j].wpdw, row + j % rowcount + 40, col + 4); ex.SetCellValue(datalist[j].cksl, row + j % rowcount + 40, col + 5); ex.SetCellValue(datalist[j].wpdj, row + j % rowcount + 40, col + 7); ex.SetCellValue(datalist[j].wpmc, row + j % rowcount + 60, col); ex.SetCellValue(datalist[j].wpgg, row + j % rowcount + 60, col + 2); ex.SetCellValue(datalist[j].wpdw, row + j % rowcount + 60, col + 4); ex.SetCellValue(datalist[j].cksl, row + j % rowcount + 60, col + 5); ex.SetCellValue(datalist[j].wpdj, row + j % rowcount + 60, col + 7); if (datalist[j].wpdj != "") { long value = Convert.ToInt64(Math.Round(Convert.ToDouble(datalist[j].cksl) * Convert.ToDouble(datalist[j].wpdj), 2) * 100); int index = 19; while (value > 0) { long ifen = value % 10; ex.SetCellValue(ifen.ToString(), row + j % rowcount, index); ex.SetCellValue(ifen.ToString(), row + j % rowcount + 20, index); ex.SetCellValue(ifen.ToString(), row + j % rowcount + 40, index); ex.SetCellValue(ifen.ToString(), row + j % rowcount + 60, index); value = value / 10; index--; } } ex.SetCellValue(datalist[j].Remark, 17, 2); ex.SetCellValue(datalist[j].Remark, 17 + 20, 2); ex.SetCellValue(datalist[j].Remark, 17 + 40, 2); ex.SetCellValue(datalist[j].Remark, 17 + 60, 2); //ex.SetCellValue(datalist[j].zrr, row + j % rowcount, col + 7); } }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(PJ_09pxjl obj) { //lgm ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\09培训记录.xls"; ex.Open(fname); //每行显示文字长度 int zc = 60; //与会人员之间的间隔符号 char[] jksign = new char[1] { ';' }; int row = 1; int col = 1; //计算页码 int pagecount = 1; //题目 string timustr = "题目:"; List<string> timulist = Ecommon.ResultStrListByPage(timustr, obj.tm, zc, 4); if (Ecommon.GetPagecount(timulist.Count, 4) > pagecount) { pagecount = Ecommon.GetPagecount(timulist.Count, 4); } //活动内容 string hdstr = "内容:"; List<string> hdlist = Ecommon.ResultStrListByPage(hdstr, obj.nr, zc, 10); if (Ecommon.GetPagecount(hdlist.Count, 10) > pagecount) { pagecount = Ecommon.GetPagecount(hdlist.Count, 10); } //领导评语 string ldpystr = "领导检查评语:"; List<string> ldpylist = Ecommon.ResultStrListByPage(ldpystr, obj.py, zc, 3); if (Ecommon.GetPagecount(ldpylist.Count, 3) > pagecount) { pagecount = Ecommon.GetPagecount(ldpylist.Count, 3); } //复制空模板 for (int m = 1; m < pagecount; m++) { ex.CopySheet(1, m); ex.ReNameWorkSheet(m + 1, "Sheet" + (m + 1)); } for (int p = 0; p < pagecount; p++) { ex.ActiveSheet(p + 1); //题目 for (int i = 0; i < 4; i++) { if (p * 4 + i >= timulist.Count) { break; } string tempstr = timulist[p * 4 + i]; ex.SetCellValue(tempstr, 7 + i, 1); } //活动内容 for (int r = 0; r < 10; r++) { if (p * 10 + r >= hdlist.Count) { break; } string tempstr = hdlist[p * 10 + r]; ex.SetCellValue(tempstr, 11 + r, 1); } //领导评语 for (int t = 0; t < 3; t++) { if (p * 3 + t >= ldpylist.Count) { break; } string tempstr = ldpylist[p * 3 + t]; ex.SetCellValue(tempstr, 21 + t, 1); } } ex.ActiveSheet(1); //培训时间 row = 2; ex.SetCellValue(obj.rq.Year.ToString(), 4, 2); ex.SetCellValue(obj.rq.Month.ToString(), 4, 4); ex.SetCellValue(obj.rq.Day.ToString(), 4, 6); //学习时数 string[] ary = obj.xxss.Split(jksign); if (ary.Length >= 1) { ex.SetCellValue(ary[0], 4, 9); } else { ex.SetCellValue("", 4, 9); } if (ary.Length >= 2) { ex.SetCellValue(ary[1], 4, 11); } else { ex.SetCellValue("", 4, 11); } //参加人数 ex.SetCellValue(obj.cjrs, 4, 14); //主持人 ex.SetCellValue(obj.zcr, 6, 3); ex.SetCellValue(obj.zjr, 6, 9); //会议地点 ex.SetCellValue(obj.hydd, 6, 14); //签字 // ex.SetCellValue(obj.qz, 24, 3); // if (ComboBoxHelper.CompreDate(obj.qzrq)) // { // ex.SetCellValue(obj.qzrq.Year.ToString(), 24, 8); // ex.SetCellValue(obj.qzrq.Month.ToString(), 24, 10); // ex.SetCellValue(obj.qzrq.Day.ToString(), 24, 12); // } ex.ActiveSheet(1); ex.ShowExcel(); }
private static void ExportExcel(PJ_23 obj, ExcelAccess ex) { ex.SetCellValue(obj.jf + ":", 5, 4); ex.SetCellValue(obj.xybh, 4, 8); ex.SetCellValue(obj.cqdw + ":", 6, 4); string linename = ""; if (obj.linename.Contains("线")) { linename = obj.linename.Substring(0, obj.linename.LastIndexOf("线")); } else linename = obj.linename; string[] filtchar = { "V", "v" }; for (int i = 0; i < filtchar.Length; i++) { if (linename.Contains(filtchar[i])) { linename = linename.Substring(linename.LastIndexOf(filtchar[i]) + 1); } } ex.SetCellValue(linename, 10, 7); string fzlinename = ""; if (obj.fzlinename.Contains("支")) { fzlinename = obj.fzlinename.Substring(0, obj.fzlinename.LastIndexOf("支")); } else fzlinename = obj.fzlinename; ex.SetCellValue(fzlinename, 10, 10); ex.SetCellValue("'" + obj.gh, 10, 16); ex.SetCellValue(obj.cqfw, 11, 4); ex.SetCellValue(obj.cqdw + "。", 13, 4); ex.SetCellValue(obj.jf, 17, 4); //ex.SetCellValue(obj.cqdw, 15, 8); ex.SetCellValue(obj.qdrq.Year.ToString(), 21, 7); ex.SetCellValue(obj.qdrq.Month.ToString(), 21, 9); ex.SetCellValue(obj.qdrq.Day.ToString(), 21, 11); }
public void ExportExcel(ExcelAccess ex, IList<PJ_sbbzqsbgmxb3> datalist, string orgid) { //此处写填充内容代码 int row = 6; int col = 1; int rowcount = 15; // //加页 int pageindex = 1; if (pageindex < Ecommon.GetPagecount(datalist.Count, rowcount)) { pageindex = Ecommon.GetPagecount(datalist.Count, rowcount); } for (int j = 1; j <pageindex; j++) { ex.CopySheet(1, j); } for (int j = 0; j < datalist.Count; j++) { if (j % rowcount == 0) { if(j==0)ex.ActiveSheet(1); else ex.ActiveSheet((j / rowcount+1) ); if (orgid!="") ex.SetCellValue(datalist[j].OrgName, 4, 2); else ex.SetCellValue(MainHelper.UserCompany, 4, 2); ex.SetCellValue(DateTime.Now.ToString("yyyy年MM月dd日"), 4,5); } ex.SetCellValue((j + 1).ToString(), row + j % rowcount, col); ex.SetCellValue(datalist[j].sssbmc, row + j % rowcount, col + 1); ex.SetCellValue(datalist[j].sssswz, row + j % rowcount, col + 2); ex.SetCellValue(datalist[j].sssbbh, row + j % rowcount, col + 3); ex.SetCellValue(datalist[j].statuts, row + j % rowcount, col + 4); ex.SetCellValue(datalist[j].Remark, row + j % rowcount, col + 5); //ex.SetCellValue(datalist[j].zrr, row + j % rowcount, col + 7); } }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(string obj) { obj = "317"; ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\10配电变压器汇总表.xls"; IList<PS_sheetTemp> list1 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=10 "); IList<PS_sheetTemp> list2 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=20 "); IList<PS_sheetTemp> list3 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=30 "); IList<PS_sheetTemp> list4 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=50 "); IList<PS_sheetTemp> list5 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=63 "); IList<PS_sheetTemp> list6 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=80 "); IList<PS_sheetTemp> list7 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=100 "); IList<PS_sheetTemp> list8 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=125 "); IList<PS_sheetTemp> list9 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=160 "); IList<PS_sheetTemp> list10 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=200 "); IList<PS_sheetTemp> list11 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=250 "); IList<PS_sheetTemp> list12 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=300 "); IList<PS_sheetTemp> list13 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=315 "); IList<PS_sheetTemp> list14 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=400 "); IList<PS_sheetTemp> list15 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=630 "); ex.Open(fname); //此处写填充内容代码 ex.SetCellValue(list1[0].Col1.ToString(), 7, 5); ex.SetCellValue(list1[0].Col2.ToString(), 8, 5); ex.SetCellValue(list1[0].Col3.ToString(), 9, 5); ex.SetCellValue(list1[0].Col4.ToString(), 10, 5); ex.SetCellValue(list1[0].Col5.ToString(), 11, 5); ex.SetCellValue(list1[0].Col6.ToString(), 12, 5); ex.SetCellValue(list1[0].Col7.ToString(), 13, 5); ex.SetCellValue(list1[0].Col8.ToString(), 14, 5); ex.SetCellValue(Convert.ToString(list1[0].Col1 + list1[0].Col3 + list1[0].Col5 + list1[0].Col7), 5, 5); ex.SetCellValue(Convert.ToString(list1[0].Col2 + list1[0].Col4 + list1[0].Col6 + list1[0].Col8), 6, 5); ex.SetCellValue(Convert.ToString(list1[0].Col1 + list2[0].Col1 + list3[0].Col1 + list4[0].Col1 + list5[0].Col1 + list6[0].Col1 + list7[0].Col1 + list8[0].Col1 + list9[0].Col1 + list10[0].Col1 + list11[0].Col1 + list12[0].Col1 + list13[0].Col1 + list14[0].Col1 + list15[0].Col1), 7, 4); ex.SetCellValue(list2[0].Col1.ToString(), 7, 6); ex.SetCellValue(list2[0].Col2.ToString(), 8, 6); ex.SetCellValue(list2[0].Col3.ToString(), 9, 6); ex.SetCellValue(list2[0].Col4.ToString(), 10, 6); ex.SetCellValue(list2[0].Col5.ToString(), 11, 6); ex.SetCellValue(list2[0].Col6.ToString(), 12, 6); ex.SetCellValue(list2[0].Col7.ToString(), 13, 6); ex.SetCellValue(list2[0].Col8.ToString(), 14, 6); ex.SetCellValue(Convert.ToString(list2[0].Col1 + list2[0].Col3 + list2[0].Col5 + list2[0].Col7), 5, 6); ex.SetCellValue(Convert.ToString(list2[0].Col2 + list2[0].Col4 + list2[0].Col6 + list2[0].Col8), 6, 6); ex.SetCellValue(Convert.ToString(list1[0].Col2 + list2[0].Col2 + list3[0].Col2 + list4[0].Col2 + list5[0].Col2 + list6[0].Col2 + list7[0].Col2 + list8[0].Col2 + list9[0].Col2 + list10[0].Col2 + list11[0].Col2 + list12[0].Col2 + list13[0].Col2 + list14[0].Col2 + list15[0].Col2), 8, 4); ex.SetCellValue(list3[0].Col1.ToString(), 7, 7); ex.SetCellValue(list3[0].Col2.ToString(), 8, 7); ex.SetCellValue(list3[0].Col3.ToString(), 9, 7); ex.SetCellValue(list3[0].Col4.ToString(), 10, 7); ex.SetCellValue(list3[0].Col5.ToString(), 11, 7); ex.SetCellValue(list3[0].Col6.ToString(), 12, 7); ex.SetCellValue(list3[0].Col7.ToString(), 13, 7); ex.SetCellValue(list3[0].Col8.ToString(), 14, 7); ex.SetCellValue(Convert.ToString(list3[0].Col1 + list3[0].Col3 + list3[0].Col5 + list3[0].Col7), 5, 7); ex.SetCellValue(Convert.ToString(list3[0].Col2 + list3[0].Col4 + list3[0].Col6 + list3[0].Col8), 6, 7); ex.SetCellValue(Convert.ToString(list1[0].Col3 + list2[0].Col3 + list3[0].Col3 + list4[0].Col3 + list5[0].Col3 + list6[0].Col3 + list7[0].Col3 + list8[0].Col3 + list9[0].Col3 + list10[0].Col3 + list11[0].Col3 + list12[0].Col3 + list13[0].Col3 + list14[0].Col3 + list15[0].Col3), 9, 4); ex.SetCellValue(list4[0].Col1.ToString(), 7, 8); ex.SetCellValue(list4[0].Col2.ToString(), 8, 8); ex.SetCellValue(list4[0].Col3.ToString(), 9, 8); ex.SetCellValue(list4[0].Col4.ToString(), 10, 8); ex.SetCellValue(list4[0].Col5.ToString(), 11, 8); ex.SetCellValue(list4[0].Col6.ToString(), 12, 8); ex.SetCellValue(list4[0].Col7.ToString(), 13, 8); ex.SetCellValue(list4[0].Col8.ToString(), 14, 8); ex.SetCellValue(Convert.ToString(list4[0].Col1 + list4[0].Col3 + list4[0].Col5 + list4[0].Col7), 5, 8); ex.SetCellValue(Convert.ToString(list4[0].Col2 + list4[0].Col4 + list4[0].Col6 + list4[0].Col8), 6, 8); ex.SetCellValue(Convert.ToString(list1[0].Col4 + list2[0].Col4 + list3[0].Col4 + list4[0].Col4 + list5[0].Col4 + list6[0].Col4 + list7[0].Col4 + list8[0].Col4 + list9[0].Col4 + list10[0].Col4 + list11[0].Col4 + list12[0].Col4 + list13[0].Col4 + list14[0].Col4 + list15[0].Col4), 10, 4); ex.SetCellValue(list5[0].Col1.ToString(), 7, 9); ex.SetCellValue(list5[0].Col2.ToString(), 8, 9); ex.SetCellValue(list5[0].Col3.ToString(), 9, 9); ex.SetCellValue(list5[0].Col4.ToString(), 10, 9); ex.SetCellValue(list5[0].Col5.ToString(), 11, 9); ex.SetCellValue(list5[0].Col6.ToString(), 12, 9); ex.SetCellValue(list5[0].Col7.ToString(), 13, 9); ex.SetCellValue(list5[0].Col8.ToString(), 14, 9); ex.SetCellValue(Convert.ToString(list5[0].Col1 + list5[0].Col3 + list5[0].Col5 + list5[0].Col7), 5, 9); ex.SetCellValue(Convert.ToString(list5[0].Col2 + list5[0].Col4 + list5[0].Col6 + list5[0].Col8), 6, 9); ex.SetCellValue(Convert.ToString(list1[0].Col5 + list2[0].Col5 + list3[0].Col5 + list4[0].Col5 + list5[0].Col4 + list6[0].Col5 + list7[0].Col5 + list8[0].Col5 + list9[0].Col5 + list10[0].Col5 + list11[0].Col5 + list12[0].Col5 + list13[0].Col5 + list14[0].Col5 + list15[0].Col5), 11, 4); ex.SetCellValue(list6[0].Col1.ToString(), 7, 10); ex.SetCellValue(list6[0].Col2.ToString(), 8, 10); ex.SetCellValue(list6[0].Col3.ToString(), 9, 10); ex.SetCellValue(list6[0].Col4.ToString(), 10, 10); ex.SetCellValue(list6[0].Col5.ToString(), 11, 10); ex.SetCellValue(list6[0].Col6.ToString(), 12, 10); ex.SetCellValue(list6[0].Col7.ToString(), 13, 10); ex.SetCellValue(list6[0].Col8.ToString(), 14, 10); ex.SetCellValue(Convert.ToString(list6[0].Col1 + list6[0].Col3 + list6[0].Col5 + list6[0].Col7), 5, 10); ex.SetCellValue(Convert.ToString(list6[0].Col2 + list6[0].Col4 + list6[0].Col6 + list6[0].Col8), 6, 10); ex.SetCellValue(Convert.ToString(list1[0].Col6 + list2[0].Col6 + list3[0].Col6 + list4[0].Col6 + list5[0].Col6 + list6[0].Col6 + list7[0].Col6 + list8[0].Col6 + list9[0].Col6 + list10[0].Col6 + list11[0].Col6 + list12[0].Col6 + list13[0].Col6 + list14[0].Col6 + list15[0].Col6), 12, 4); ex.SetCellValue(list7[0].Col1.ToString(), 7, 11); ex.SetCellValue(list7[0].Col2.ToString(), 8, 11); ex.SetCellValue(list7[0].Col3.ToString(), 9, 11); ex.SetCellValue(list7[0].Col4.ToString(), 10, 11); ex.SetCellValue(list7[0].Col5.ToString(), 11, 11); ex.SetCellValue(list7[0].Col6.ToString(), 12, 11); ex.SetCellValue(list7[0].Col7.ToString(), 13, 11); ex.SetCellValue(list7[0].Col8.ToString(), 14, 11); ex.SetCellValue(Convert.ToString(list7[0].Col1 + list7[0].Col3 + list7[0].Col5 + list7[0].Col7), 5, 11); ex.SetCellValue(Convert.ToString(list7[0].Col2 + list7[0].Col4 + list7[0].Col6 + list7[0].Col8), 6, 11); ex.SetCellValue(Convert.ToString(list1[0].Col7 + list2[0].Col7 + list3[0].Col7 + list4[0].Col7 + list5[0].Col7 + list6[0].Col7 + list7[0].Col7 + list8[0].Col7 + list9[0].Col7 + list10[0].Col7 + list11[0].Col7 + list12[0].Col7 + list13[0].Col7 + list14[0].Col7 + list15[0].Col7), 13, 4); ex.SetCellValue(list8[0].Col1.ToString(), 7, 12); ex.SetCellValue(list8[0].Col2.ToString(), 8, 12); ex.SetCellValue(list8[0].Col3.ToString(), 9, 12); ex.SetCellValue(list8[0].Col4.ToString(), 10, 12); ex.SetCellValue(list8[0].Col5.ToString(), 11, 12); ex.SetCellValue(list8[0].Col6.ToString(), 12, 12); ex.SetCellValue(list8[0].Col7.ToString(), 13, 12); ex.SetCellValue(list8[0].Col8.ToString(), 14, 12); ex.SetCellValue(Convert.ToString(list8[0].Col1 + list8[0].Col3 + list8[0].Col5 + list8[0].Col7), 5, 12); ex.SetCellValue(Convert.ToString(list8[0].Col2 + list8[0].Col4 + list8[0].Col6 + list8[0].Col8), 6, 12); ex.SetCellValue(Convert.ToString(list1[0].Col8 + list2[0].Col8 + list3[0].Col8 + list4[0].Col8 + list5[0].Col8 + list6[0].Col8 + list7[0].Col8 + list8[0].Col8 + list9[0].Col8 + list10[0].Col8 + list11[0].Col8 + list12[0].Col8 + list13[0].Col8 + list14[0].Col8 + list15[0].Col8), 14, 4); ex.SetCellValue(list9[0].Col1.ToString(), 7, 13); ex.SetCellValue(list9[0].Col2.ToString(), 8, 13); ex.SetCellValue(list9[0].Col3.ToString(), 9, 13); ex.SetCellValue(list9[0].Col4.ToString(), 10, 13); ex.SetCellValue(list9[0].Col5.ToString(), 11, 13); ex.SetCellValue(list9[0].Col6.ToString(), 12, 13); ex.SetCellValue(list9[0].Col7.ToString(), 13, 13); ex.SetCellValue(list9[0].Col8.ToString(), 14, 13); ex.SetCellValue(Convert.ToString(list9[0].Col1 + list9[0].Col3 + list9[0].Col5 + list9[0].Col7), 5, 13); ex.SetCellValue(Convert.ToString(list9[0].Col2 + list9[0].Col4 + list9[0].Col6 + list9[0].Col8), 6, 13); ex.SetCellValue(list10[0].Col1.ToString(), 7, 14); ex.SetCellValue(list10[0].Col2.ToString(), 8, 14); ex.SetCellValue(list10[0].Col3.ToString(), 9, 14); ex.SetCellValue(list10[0].Col4.ToString(), 10, 14); ex.SetCellValue(list10[0].Col5.ToString(), 11, 14); ex.SetCellValue(list10[0].Col6.ToString(), 12, 14); ex.SetCellValue(list10[0].Col7.ToString(), 13, 14); ex.SetCellValue(list10[0].Col8.ToString(), 14, 14); ex.SetCellValue(Convert.ToString(list10[0].Col1 + list10[0].Col3 + list10[0].Col5 + list10[0].Col7), 5, 14); ex.SetCellValue(Convert.ToString(list10[0].Col2 + list10[0].Col4 + list10[0].Col6 + list10[0].Col8), 6, 14); ex.SetCellValue(list11[0].Col1.ToString(), 7, 15); ex.SetCellValue(list11[0].Col2.ToString(), 8, 15); ex.SetCellValue(list11[0].Col3.ToString(), 9, 15); ex.SetCellValue(list11[0].Col4.ToString(), 10, 15); ex.SetCellValue(list11[0].Col5.ToString(), 11, 15); ex.SetCellValue(list11[0].Col6.ToString(), 12, 15); ex.SetCellValue(list11[0].Col7.ToString(), 13, 15); ex.SetCellValue(list11[0].Col8.ToString(), 14, 15); ex.SetCellValue(Convert.ToString(list11[0].Col1 + list11[0].Col3 + list11[0].Col5 + list11[0].Col7), 5, 15); ex.SetCellValue(Convert.ToString(list11[0].Col2 + list11[0].Col4 + list11[0].Col6 + list11[0].Col8), 6, 15); ex.SetCellValue(list12[0].Col1.ToString(), 7, 16); ex.SetCellValue(list12[0].Col2.ToString(), 8, 16); ex.SetCellValue(list12[0].Col3.ToString(), 9, 16); ex.SetCellValue(list12[0].Col4.ToString(), 10, 16); ex.SetCellValue(list12[0].Col5.ToString(), 11, 16); ex.SetCellValue(list12[0].Col6.ToString(), 12, 16); ex.SetCellValue(list12[0].Col7.ToString(), 13, 16); ex.SetCellValue(list12[0].Col8.ToString(), 14, 16); ex.SetCellValue(Convert.ToString(list12[0].Col1 + list12[0].Col3 + list12[0].Col5 + list12[0].Col7), 5, 16); ex.SetCellValue(Convert.ToString(list12[0].Col2 + list12[0].Col4 + list12[0].Col6 + list12[0].Col8), 6, 16); ex.SetCellValue(list13[0].Col1.ToString(), 7, 17); ex.SetCellValue(list13[0].Col2.ToString(), 8, 17); ex.SetCellValue(list13[0].Col3.ToString(), 9, 17); ex.SetCellValue(list13[0].Col4.ToString(), 10, 17); ex.SetCellValue(list13[0].Col5.ToString(), 11, 17); ex.SetCellValue(list13[0].Col6.ToString(), 12, 17); ex.SetCellValue(list13[0].Col7.ToString(), 13, 17); ex.SetCellValue(list13[0].Col8.ToString(), 14, 17); ex.SetCellValue(Convert.ToString(list13[0].Col1 + list13[0].Col3 + list13[0].Col5 + list13[0].Col7), 5, 17); ex.SetCellValue(Convert.ToString(list13[0].Col2 + list13[0].Col4 + list13[0].Col6 + list13[0].Col8), 6, 17); ex.SetCellValue(list14[0].Col1.ToString(), 7, 18); ex.SetCellValue(list14[0].Col2.ToString(), 8, 18); ex.SetCellValue(list14[0].Col3.ToString(), 9, 18); ex.SetCellValue(list14[0].Col4.ToString(), 10, 18); ex.SetCellValue(list14[0].Col5.ToString(), 11, 18); ex.SetCellValue(list14[0].Col6.ToString(), 12, 18); ex.SetCellValue(list14[0].Col7.ToString(), 13, 18); ex.SetCellValue(list14[0].Col8.ToString(), 14, 18); ex.SetCellValue(Convert.ToString(list14[0].Col1 + list14[0].Col3 + list14[0].Col5 + list14[0].Col7), 5, 18); ex.SetCellValue(Convert.ToString(list14[0].Col2 + list14[0].Col4 + list14[0].Col6 + list14[0].Col8), 6, 18); ex.SetCellValue(list15[0].Col1.ToString(), 7, 19); ex.SetCellValue(list15[0].Col2.ToString(), 8, 19); ex.SetCellValue(list15[0].Col3.ToString(), 9, 19); ex.SetCellValue(list15[0].Col4.ToString(), 10, 19); ex.SetCellValue(list15[0].Col5.ToString(), 11, 19); ex.SetCellValue(list15[0].Col6.ToString(), 12, 19); ex.SetCellValue(list15[0].Col7.ToString(), 13, 19); ex.SetCellValue(list15[0].Col8.ToString(), 14, 19); ex.SetCellValue(Convert.ToString(list15[0].Col1 + list15[0].Col3 + list15[0].Col5 + list15[0].Col7), 5, 19); ex.SetCellValue(Convert.ToString(list15[0].Col2 + list15[0].Col4 + list15[0].Col6 + list15[0].Col8), 6, 19); ex.SetCellValue(Convert.ToString(list1[0].Col1 + list1[0].Col3 + list1[0].Col5 + list1[0].Col7 + list2[0].Col1 + list2[0].Col3 + list2[0].Col5 + list2[0].Col7 + list3[0].Col1 + list3[0].Col3 + list3[0].Col5 + list3[0].Col7 + list4[0].Col1 + list4[0].Col3 + list4[0].Col5 + list4[0].Col7 + list5[0].Col1 + list5[0].Col3 + list5[0].Col5 + list5[0].Col7 + list6[0].Col1 + list6[0].Col3 + list6[0].Col5 + list6[0].Col7 + list7[0].Col1 + list7[0].Col3 + list7[0].Col5 + list7[0].Col7 + list8[0].Col1 + list8[0].Col3 + list8[0].Col5 + list8[0].Col7 + list9[0].Col1 + list9[0].Col3 + list9[0].Col5 + list9[0].Col7 + list10[0].Col1 + list10[0].Col3 + list10[0].Col5 + list10[0].Col7 + list11[0].Col1 + list11[0].Col3 + list11[0].Col5 + list11[0].Col7 + list12[0].Col1 + list12[0].Col3 + list12[0].Col5 + list12[0].Col7 + list13[0].Col1 + list13[0].Col3 + list13[0].Col5 + list13[0].Col7 + list14[0].Col1 + list14[0].Col3 + list14[0].Col5 + list14[0].Col7 + list15[0].Col1 + list15[0].Col3 + list15[0].Col5 + list15[0].Col7), 5, 4); ex.SetCellValue(Convert.ToString(list1[0].Col2 + list1[0].Col4 + list1[0].Col6 + list1[0].Col8 + list2[0].Col2 + list2[0].Col4 + list2[0].Col6 + list2[0].Col8 + list3[0].Col2 + list3[0].Col4 + list3[0].Col6 + list3[0].Col8 + list4[0].Col2 + list4[0].Col4 + list4[0].Col6 + list4[0].Col8 + list5[0].Col2 + list5[0].Col4 + list5[0].Col6 + list5[0].Col8 + list6[0].Col2 + list6[0].Col4 + list6[0].Col6 + list6[0].Col8 + list7[0].Col2 + list7[0].Col4 + list7[0].Col6 + list7[0].Col8 + list8[0].Col2 + list8[0].Col4 + list8[0].Col6 + list8[0].Col8 + list9[0].Col2 + list9[0].Col4 + list9[0].Col6 + list9[0].Col8 + list10[0].Col2 + list10[0].Col4 + list10[0].Col6 + list10[0].Col8 + list11[0].Col2 + list11[0].Col4 + list11[0].Col6 + list11[0].Col8 + list12[0].Col2 + list12[0].Col4 + list12[0].Col6 + list12[0].Col8 + list13[0].Col2 + list13[0].Col4 + list13[0].Col6 + list13[0].Col8 + list14[0].Col2 + list14[0].Col4 + list14[0].Col6 + list14[0].Col8 + list15[0].Col2 + list15[0].Col4 + list15[0].Col6 + list15[0].Col8), 6, 4); ex.ShowExcel(); }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(PS_tqdlbh jl) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\13剩余电流动作保护器测试记录.xls"; ex.Open(fname); int row = 1; int col = 1; //测量记录 IList<PJ_13dlbhjl> list = Client.ClientHelper.PlatformSqlMap.GetListByWhere<PJ_13dlbhjl>(" where sbID='" + jl.sbID + "' order by CreateDate"); int p = Ecommon.GetPagecount(list.Count, 12); for (int i = 0; i < p - 1; i++) { ex.CopySheet(1, 1 + i); } ex.ActiveSheet(1); //线路名称行 ex.SetCellValue(jl.tqName, 4, 1); ex.SetCellValue(jl.Factory, 5, 13); ex.SetCellValue(jl.InstallAdress, row + 4, col + 3); ex.SetCellValue(jl.dzdl, row + 4, col + 6); ex.SetCellValue(jl.sbModle, row + 4, col + 10); ex.SetCellValue(jl.Factory, row + 4, col + 13); //设备名称行 ex.SetCellValue(jl.glr, row + 6, col + 3); ex.SetCellValue(jl.dzsj, row + 6, col + 6); ex.SetCellValue(jl.InDate.Year.ToString(), row + 7, col + 10); ex.SetCellValue(jl.InDate.Month.ToString(), row + 7, col + 11); ex.SetCellValue(jl.InDate.Day.ToString(), row + 7, col + 13); for (int page = 1; page <= p; page++) { ex.ActiveSheet(page); if (page == 1) { for (int i = 0; i < 12; i++) { if (i + (page - 1) * 12 < list.Count) { PJ_13dlbhjl obj = list[i + (page - 1) * 12]; ex.SetCellValue(obj.rq.Year.ToString(), row + 11 + i, col); ex.SetCellValue(obj.rq.Month.ToString(), row + 11 + i, col + 1); ex.SetCellValue(obj.rq.Day.ToString(), row + 11 + i, col + 2); ex.SetCellValue(obj.dzdl, row + 11 + i, col + 3); ex.SetCellValue(obj.dzsj.ToString(), row + 11 + i, col + 4); ex.SetCellValue(obj.yxqk, row + 11 + i, col + 5); ex.SetCellValue(obj.csr, row + 11 + i, col + 6); } } } else { for (int i = 0; i < 12; i++) { if (i + (page - 1) * 12 < list.Count) { PJ_13dlbhjl obj = list[i + (page - 1) * 12]; ex.SetCellValue(obj.rq.Year.ToString(), row + 11 + i, col); ex.SetCellValue(obj.rq.Month.ToString(), row + 11 + i, col + 1); ex.SetCellValue(obj.rq.Day.ToString(), row + 11 + i, col + 2); ex.SetCellValue(obj.dzdl, row + 11 + i, col + 3); ex.SetCellValue(obj.dzsj.ToString(), row + 11 + i, col + 4); ex.SetCellValue(obj.yxqk, row + 11 + i, col + 5); ex.SetCellValue(obj.csr, row + 11 + i, col + 6); } } } } ex.ActiveSheet(1); ex.ShowExcel(); }
/// <summary> /// 设置Excel数据信息 /// </summary> /// <param name="ex"></param> /// <param name="list"></param> /// <param name="caplist"></param> /// <param name="modtmlist"></param> /// <param name="modmflist"></param> /// <param name="jstrfilter"></param> /// <param name="strfilter"></param> /// <param name="jstart"></param> /// <param name="j"></param> /// <param name="jmax"></param> /// <param name="istart"></param> /// <param name="istart2"></param> public static void setExceldt(ExcelAccess ex, IList list, IList caplist, IList modtmlist, IList modmflist, string jstrfilter, string strfilter, int jstart, int j, int jmax, int istart, int istart2) { //string str = ""; 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); for (int itm = 0; itm < modtmlist.Count; itm++) { ex.ActiveSheet("Sheet" + (1 + (itm / 6) * (int)Math.Ceiling(caplist.Count /( jmax + 0.0)) + j / jmax)); if (j % jmax == 0) { ex.SetCellValue(modtmlist[itm].ToString(), istart + (itm % 6) * 2, 1); //jstrfilter = " and 1=1"; //setExceltmdt(ex, list, modtmlist, jstrfilter, strfilter, jstart, j, jmax, istart, itm); } //jstrfilter = " and 1=1 and b.byqCapcity='" + caplist[j] + "'"; setExceltmdt(ex, list, modtmlist, jstrfilter, strfilter, jstart, j, jmax, istart, itm); } for (int imf = 0; imf < modmflist.Count; imf++) { ex.ActiveSheet("Sheet" + (1 + (imf / 3) * (int)Math.Ceiling(caplist.Count / (jmax + 0.0)) + j / jmax)); if (j % jmax == 0) { ex.SetCellValue(modmflist[imf].ToString(), istart2 + (imf % 3) * 2, 2); //jstrfilter = " and 1=1"; //setExcelmfdt(ex, list, modmflist, jstrfilter, strfilter, jstart, j, jmax, istart2, imf); } //jstrfilter = " and 1=1 and b.byqCapcity='" + caplist[j] + "'"; setExcelmfdt(ex, list, modmflist, jstrfilter, strfilter, jstart, j, jmax, istart2, imf); } }
/// <summary> /// 文档格式预定义好的,生成台账 /// </summary> /// <param name="obj"></param> public static void ExportExcel2(IList<PS_tqdlbh> list, string orgname) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\13剩余电流动作保护器台帐.xls"; ex.Open(fname); int row = 1; int col = 1; //测量记录 int p = Ecommon.GetPagecount(list.Count, 20); for (int i = 0; i < p - 1; i++) { ex.CopySheet(1, 1 + i); } ex.ActiveSheet(1); //单位名称 ex.SetCellValue(orgname, 3, 3); for (int page = 1; page <= p; page++) { ex.ActiveSheet(page); if (page == 1) { for (int i = 0; i < 20; i++) { if (i + (page - 1) * 20 < list.Count) { PS_tqdlbh jl = list[i + (page - 1) * 20]; //ex.SetCellValue(obj.rq.Year.ToString(), row + 11 + i, col); //ex.SetCellValue(obj.rq.Month.ToString(), row + 11 + i, col + 1); //ex.SetCellValue(obj.rq.Day.ToString(), row + 11 + i, col + 2); //ex.SetCellValue(obj.dzdl, row + 11 + i, col + 3); //ex.SetCellValue(obj.dzsj.ToString(), row + 11 + i, col + 4); //ex.SetCellValue(obj.yxqk, row + 11 + i, col + 5); //ex.SetCellValue(obj.csr, row + 11 + i, col + 6); ex.SetCellValue(jl.tqName, row + 5 + i, col + 2); ex.SetCellValue(jl.sbModle, row + 5 + i, col + 3); ex.SetCellValue(jl.Factory, row + 5 + i, col + 4); ex.SetCellValue(jl.dzdl, row + 5 + i, col + 5); ex.SetCellValue(jl.dzsj, row + 5 + i, col + 6); ex.SetCellValue(jl.InDate.Year.ToString(), row + 5 + i, col + 7); ex.SetCellValue(jl.InDate.Month.ToString(), row + 5 + i, col + 8); ex.SetCellValue(jl.InDate.Day.ToString(), row + 5 + i, col + 9); } } } else { for (int i = 0; i < 20; i++) { if (i + (page - 1) * 12 < list.Count) { PS_tqdlbh jl = list[i + (page - 1) * 20]; //ex.SetCellValue(obj.rq.Year.ToString(), row + 11 + i, col); //ex.SetCellValue(obj.rq.Month.ToString(), row + 11 + i, col + 1); //ex.SetCellValue(obj.rq.Day.ToString(), row + 11 + i, col + 2); //ex.SetCellValue(obj.dzdl, row + 11 + i, col + 3); //ex.SetCellValue(obj.dzsj.ToString(), row + 11 + i, col + 4); //ex.SetCellValue(obj.yxqk, row + 11 + i, col + 5); //ex.SetCellValue(obj.csr, row + 11 + i, col + 6); ex.SetCellValue(jl.tqName, row + 5 + i, col + 2); ex.SetCellValue(jl.sbModle, row + 5 + i, col + 3); ex.SetCellValue(jl.Factory, row + 5 + i, col + 4); ex.SetCellValue(jl.dzdl, row + 5 + i, col + 5); ex.SetCellValue(jl.dzsj, row + 5 + i, col + 6); ex.SetCellValue(jl.InDate.Year.ToString(), row + 5 + i, col + 7); ex.SetCellValue(jl.InDate.Month.ToString(), row + 5 + i, col + 8); ex.SetCellValue(jl.InDate.Day.ToString(), row + 5 + i, col + 9); } } } } ex.ActiveSheet(1); ex.ShowExcel(); }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(IList<sdjl_07jdzz> objlist) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\送电08接地装置检测.xls"; ex.Open(fname); //此处写填充内容代码 int rowcount = 6; //加页 int pageindex = 1; if (pageindex < Ecommon.GetPagecount(objlist.Count, 16)) { pageindex = Ecommon.GetPagecount(objlist.Count, 16); } for (int j = 1; j <= pageindex; j++) { ex.SetCellValue(objlist[0].LineName, 4, 3); if (j > 1) { ex.CopySheet(1, 1); } } for (int j = 1; j <= pageindex; j++) { ex.ActiveSheet(j); ex.ReNameWorkSheet(j, "Sheet" + (j)); int prepageindex = j - 1; //主题 int starow = prepageindex * 16 + 1; int endrow = j * 16; if (objlist.Count > endrow) { for (int i = 0; i < 16; i++) { ex.SetCellValue(objlist[starow - 1 + i].gth, rowcount + 1 + i, 1); ex.SetCellValue(objlist[starow - 1 + i].gzwz, rowcount + 1 + i, 2); ex.SetCellValue(objlist[starow - 1 + i].sbmc, rowcount + 1 + i, 3); ex.SetCellValue(objlist[starow - 1 + i].jddz.ToString(), rowcount + 1 + i, 4); ex.SetCellValue(objlist[starow - 1 + i].CreateDate.Year.ToString(), rowcount + 1 + i, 5); ex.SetCellValue(objlist[starow - 1 + i].CreateDate.Month.ToString(), rowcount + 1 + i, 6); ex.SetCellValue(objlist[starow - 1 + i].CreateDate.Day.ToString(), rowcount + 1 + i, 7); ex.SetCellValue(objlist[starow - 1 + i].trdzr.ToString(), rowcount + 1 + i, 8); ex.SetCellValue(objlist[starow - 1 + i].tz.ToString(), rowcount + 1 + i, 9); ex.SetCellValue(objlist[starow - 1 + i].xhgg, rowcount + 1 + i, 10); ex.SetCellValue(objlist[starow - 1 + i].fzxl, rowcount + 1 + i, 11); ex.SetCellValue(objlist[starow - 1 + i].CreateMan, rowcount + 1 + i, 12); } } else if (objlist.Count <= endrow && objlist.Count >= starow) { for (int i = 0; i < objlist.Count - starow + 1; i++) { ex.SetCellValue(objlist[starow - 1 + i].gth, rowcount + 1 + i, 1); ex.SetCellValue(objlist[starow - 1 + i].gzwz, rowcount + 1 + i, 2); ex.SetCellValue(objlist[starow - 1 + i].sbmc, rowcount + 1 + i, 3); ex.SetCellValue(objlist[starow - 1 + i].jddz.ToString(), rowcount + 1 + i, 4); ex.SetCellValue(objlist[starow - 1 + i].CreateDate.Year.ToString(), rowcount + 1 + i, 5); ex.SetCellValue(objlist[starow - 1 + i].CreateDate.Month.ToString(), rowcount + 1 + i, 6); ex.SetCellValue(objlist[starow - 1 + i].CreateDate.Day.ToString(), rowcount + 1 + i, 7); ex.SetCellValue(objlist[starow - 1 + i].trdzr.ToString(), rowcount + 1 + i, 8); ex.SetCellValue(objlist[starow - 1 + i].tz.ToString(), rowcount + 1 + i, 9); ex.SetCellValue(objlist[starow - 1 + i].xhgg, rowcount + 1 + i, 10); ex.SetCellValue(objlist[starow - 1 + i].fzxl, rowcount + 1 + i, 11); ex.SetCellValue(objlist[starow - 1 + i].CreateMan, rowcount + 1 + i, 12); //ex.SetCellValue(objlist[starow - 1 + i].Remark, rowcount + i, 10); } } } ex.ActiveSheet(1); ex.ShowExcel(); }
void ContentChanged(Control ctrl) { LP_Temple lp = (LP_Temple)ctrl.Tag; string str = ctrl.Text; if (dsoFramerWordControl1.MyExcel == null) { return; } //Excel.Workbook wb = dsoFramerWordControl1.AxFramerControl.ActiveDocument as Excel.Workbook; //Excel.Worksheet sheet; ExcelAccess ea = new ExcelAccess(); ea.MyWorkBook = wb; ea.MyExcel = wb.Application; if (lp.KindTable != "" && activeSheetName != lp.KindTable) { if (lp.KindTable != "") { activeSheetName = lp.KindTable; sheet = wb.Application.Sheets[lp.KindTable] as Excel.Worksheet; activeSheetIndex = sheet.Index; } else { sheet = wb.Application.Sheets[1] as Excel.Worksheet; activeSheetIndex = sheet.Index; activeSheetName = sheet.Name; } } ea.ActiveSheet(activeSheetIndex); unLockExcel(wb, sheet); if (lp.CtrlType.Contains("uc_gridcontrol")) { FillTable(ea, lp, (ctrl as uc_gridcontrol).GetContent(String2Int(lp.WordCount.Split(pchar)))); return; } else if (lp.CtrlType.Contains("DevExpress.XtraEditors.DateEdit")) { FillTime(ea, lp, (ctrl as DateEdit).DateTime); return; } string[] arrCellpos = lp.CellPos.Split(pchar); string[] arrtemp = lp.WordCount.Split(pchar); arrCellpos = StringHelper.ReplaceEmpty(arrCellpos).Split(pchar); arrtemp = StringHelper.ReplaceEmpty(arrtemp).Split(pchar); List<int> arrCellCount = String2Int(arrtemp); if (arrCellpos.Length == 1 || string.IsNullOrEmpty(arrCellpos[1])) { ea.SetCellValue("'" + str, GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]); if (valuehs.ContainsKey(lp.LPID + "$" + lp.CellPos)) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + lp.CellPos] as WF_TableFieldValue; tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(lp.CellPos)[0]; tfv.YExcelPos = GetCellPos(lp.CellPos)[1]; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(lp.CellPos)[0]; tfv.YExcelPos = GetCellPos(lp.CellPos)[1]; valuehs.Add(lp.LPID + "$" + lp.CellPos, tfv); } } else if (arrCellpos.Length > 1 && (!string.IsNullOrEmpty(arrCellpos[1]))) { StringHelper help = new StringHelper(); if (lp.CellName == "编号") { for (int j = 0; j < arrCellpos.Length; j++) { if (str.IndexOf("\r\n") == -1 && str.Length <= help.GetFristLen(str, arrCellCount[j])) { string strNew = str.Substring(0, (str.Length > 0 ? str.Length : 1) - 1) + (j + 1).ToString(); ea.SetCellValue("'" + strNew, GetCellPos(arrCellpos[j])[0], GetCellPos(arrCellpos[j])[1]); if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[j])) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[j]] as WF_TableFieldValue; tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[j])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[j])[1]; tfv.ExcelSheetName = sheet.Name; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[j])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[j])[1]; tfv.ExcelSheetName = sheet.Name; valuehs.Add(lp.LPID + "$" + arrCellpos[j], tfv); } } } return; } int i = 0; if (arrCellCount[0] != arrCellCount[1]) { if (str.IndexOf("\r\n") == -1 && str.Length <= help.GetFristLen(str, arrCellCount[0])) { ea.SetCellValue("'" + str, GetCellPos(arrCellpos[0])[0], GetCellPos(arrCellpos[0])[1]); if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[0])) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[0]] as WF_TableFieldValue; tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[0])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[0])[1]; tfv.ExcelSheetName = sheet.Name; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[0])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[0])[1]; tfv.ExcelSheetName = sheet.Name; valuehs.Add(lp.LPID + "$" + arrCellpos[0], tfv); } return; } ea.SetCellValue("'" + str.Substring(0, str.IndexOf("\r\n") != -1 && help.GetFristLen(str, arrCellCount[0]) >= str.IndexOf("\r\n") ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0])), GetCellPos(arrCellpos[0])[0], GetCellPos(arrCellpos[0])[1]); if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[0])) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[0]] as WF_TableFieldValue; tfv.ControlValue = str.Substring(0, str.IndexOf("\r\n") != -1 && help.GetFristLen(str, arrCellCount[0]) >= str.IndexOf("\r\n") ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0])); tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[0])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[0])[1]; tfv.ExcelSheetName = sheet.Name; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = str.Substring(0, str.IndexOf("\r\n") != -1 && help.GetFristLen(str, arrCellCount[0]) >= str.IndexOf("\r\n") ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0])); tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[0])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[0])[1]; tfv.ExcelSheetName = sheet.Name; valuehs.Add(lp.LPID + "$" + arrCellpos[0], tfv); } str = str.Substring(help.GetFristLen(str, arrCellCount[0]) >= str.IndexOf("\r\n") && str.IndexOf("\r\n") != -1 ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0])); i++; } str = help.GetPlitString(str, arrCellCount[1]); FillMutilRows(ea, i, lp, str, arrCellCount, arrCellpos); } LockExcel(wb, sheet); }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(IList<PJ_21gzbxdh> objlist) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\21电力故障电话接听记录.xls"; ex.Open(fname); //此处写填充内容代码 int rowcount = 7; //加页 int pageindex = 1; if (pageindex < Ecommon.GetPagecount(objlist.Count, 19)) { pageindex = Ecommon.GetPagecount(objlist.Count, 19); } for (int j = 1; j <= pageindex; j++) { if (j > 1) { ex.CopySheet(1, 1); } } ex.ShowExcel(); for (int j = 1; j <= pageindex; j++) { ex.ActiveSheet(j); ex.ReNameWorkSheet(j, "Sheet" + (j)); int prepageindex = j - 1; //主题 int starow = prepageindex * 19 + 1; int endrow = j * 19; if (objlist.Count > endrow) { for (int i = 0; i < 19; i++) { ex.SetCellValue(objlist[starow - 1 + i].rq.Month.ToString(), rowcount + i, 1); ex.SetCellValue(objlist[starow - 1 + i].rq.Day.ToString(), rowcount + i, 2); ex.SetCellValue(objlist[starow - 1 + i].rq.Hour.ToString(), rowcount + i, 3); ex.SetCellValue(objlist[starow - 1 + i].rq.Minute.ToString(), rowcount + i, 4); ex.SetCellValue(objlist[starow - 1 + i].lxfs, rowcount + i, 5); ex.SetCellValue(objlist[starow - 1 + i].yhdz, rowcount + i, 6); ex.SetCellValue(objlist[starow - 1 + i].gzjk, rowcount + i, 9); // ex.SetCellValue(objlist[starow - 1 + i].djr, rowcount + i,12); // ex.SetCellValue(objlist[starow - 1 + i].clr, rowcount + i, 13); } } else if (objlist.Count <= endrow && objlist.Count >= starow) { for (int i = 0; i < objlist.Count - starow + 1; i++) { ex.SetCellValue(objlist[starow - 1 + i].rq.Month.ToString(), rowcount + i, 1); ex.SetCellValue(objlist[starow - 1 + i].rq.Day.ToString(), rowcount + i, 2); ex.SetCellValue(objlist[starow - 1 + i].rq.Hour.ToString(), rowcount + i, 3); ex.SetCellValue(objlist[starow - 1 + i].rq.Minute.ToString(), rowcount + i, 4); ex.SetCellValue(objlist[starow - 1 + i].lxfs, rowcount + i, 5); ex.SetCellValue(objlist[starow - 1 + i].yhdz, rowcount + i, 6); ex.SetCellValue(objlist[starow - 1 + i].gzjk, rowcount + i, 9); // ex.SetCellValue(objlist[starow - 1 + i].djr, rowcount + i, 12); // ex.SetCellValue(objlist[starow - 1 + i].clr, rowcount + i, 13); } } } ex.ActiveSheet(1); string orgname=""; if (objlist.Count>0) { orgname = objlist[0].OrgName; } //变电所内容 ex.SetCellValue(orgname, 4, 5); //记录 //for (int i = 0; i < objlist.Count;i++ ) //{ // ex.SetCellValue(objlist[i].rq.Month.ToString(), rowcount + i, 1); // ex.SetCellValue(objlist[i].rq.Day.ToString(), rowcount + i, 2); // ex.SetCellValue(objlist[i].rq.Hour.ToString(), rowcount + i, 3); // ex.SetCellValue(objlist[i].rq.Minute.ToString(), rowcount + i, 4); // ex.SetCellValue(objlist[i].lxfs, rowcount + i, 5); // ex.SetCellValue(objlist[i].yhdz, rowcount + i, 6); // ex.SetCellValue(objlist[i].gzjk, rowcount + i, 7); // ex.SetCellValue(objlist[i].djr, rowcount + i,8); // ex.SetCellValue(objlist[i].clr, rowcount + i,9); //} ex.ShowExcel(); }
void ctrl_Leave(object sender, EventArgs e) { LP_Temple lp = (LP_Temple)(sender as Control).Tag; string str = (sender as Control).Text; if (dsoFramerWordControl1 == null || dsoFramerWordControl1.MyExcel == null) { return; } try { //Excel.Workbook wb = dsoFramerWordControl1.AxFramerControl.ActiveDocument as Excel.Workbook; ExcelAccess ea = new ExcelAccess(); ea.MyWorkBook = wb; ea.MyExcel = wb.Application; //Excel.Worksheet xx; if (lp.KindTable != "" && activeSheetName != lp.KindTable) { if (lp.KindTable != "") { activeSheetName = lp.KindTable; xx = wb.Application.Sheets[lp.KindTable] as Excel.Worksheet; activeSheetIndex = xx.Index; } else { xx = wb.Application.Sheets[1] as Excel.Worksheet; activeSheetIndex = xx.Index; activeSheetName = xx.Name; } if (lp.KindTable != "") { ea.ActiveSheet(lp.KindTable); } else { ea.ActiveSheet(1); } } if (lp.CellPos == "") { if (lp.CellName.IndexOf("绘图") > -1) { //unLockExcel(wb, xx); PJ_tbsj tb = MainHelper.PlatformSqlMap.GetOne<PJ_tbsj>("where picName = '" + str + "'"); if (tb != null) { if (tb.picName != "表箱") { string tempPath = Path.GetTempPath(); string tempfile = tempPath + "~" + Guid.NewGuid().ToString() + tb.S1; FileStream fs; fs = new FileStream(tempfile, FileMode.Create, FileAccess.Write); BinaryWriter bw = new BinaryWriter(fs); bw.Write(tb.picImage); bw.Flush(); bw.Close(); fs.Close(); //IDataObject data = new DataObject(DataFormats.FileDrop, new string[] { tempfile }); //MemoryStream memo = new MemoryStream(4); //byte[] bytes = new byte[] { (byte)(5), 0, 0, 0 }; //memo.Write(bytes, 0, bytes.Length); //data.SetData("ttt", memo); //Clipboard.SetDataObject(data); Image im = Bitmap.FromFile(tempfile); Bitmap bt = new Bitmap(im); DataObject dataObject = new DataObject(); dataObject.SetData(DataFormats.Bitmap, bt); Clipboard.SetDataObject(dataObject, true); } else { Microsoft.Office.Interop.Excel.Shape activShape = null; activShape = xx.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, (float)153.75, (float)162.75, (float)22.5, (float)10.5); activShape.TextFrame.Characters(1, 1).Font.Size = 8; activShape.TextFrame.Characters(1, 1).Text = "1"; activShape.TextFrame.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; activShape.Cut(); } } } else { if (valuehs.ContainsKey(lp.LPID)) { WF_TableFieldValue tfv = valuehs[lp.LPID] as WF_TableFieldValue; tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = -1; tfv.YExcelPos = -1; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = -1; tfv.YExcelPos = -1; tfv.ExcelSheetName = xx.Name; valuehs.Add(lp.LPID, tfv); } } return; } unLockExcel(wb, xx); string[] arrCellpos = lp.CellPos.Split(pchar); string[] arrtemp = lp.WordCount.Split(pchar); arrCellpos = StringHelper.ReplaceEmpty(arrCellpos).Split(pchar); if (lp.CtrlType.Contains("uc_gridcontrol")) { FillTable(ea, lp, (sender as uc_gridcontrol).GetContent(String2Int(lp.WordCount.Split(pchar)))); LockExcel(wb, xx); return; } else if (lp.CtrlType.Contains("DevExpress.XtraEditors.DateEdit")) { FillTime(ea, lp, (sender as DateEdit).DateTime); LockExcel(wb, xx); return; } else if (lp.CtrlType.Contains("DevExpress.XtraEditors.SpinEdit")) { IList<string> strList = new List<string>(); if (arrCellpos.Length == 1 || string.IsNullOrEmpty(arrCellpos[1])) { if (string.IsNullOrEmpty(str)) ea.SetCellValue("", GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]); else ea.SetCellValue("'" + str, GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]); if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[0])) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[0]] as WF_TableFieldValue; tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[0])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[0])[1]; tfv.ExcelSheetName = xx.Name; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[0])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[0])[1]; tfv.ExcelSheetName = xx.Name; valuehs.Add(lp.LPID + "$" + arrCellpos[0], tfv); } } else if (arrCellpos.Length > 1 && (!string.IsNullOrEmpty(arrCellpos[1]))) { int i = 0; if (string.IsNullOrEmpty(str)) ea.SetCellValue("", GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]); else ea.SetCellValue("'" + str, GetCellPos(arrCellpos[i])[0], GetCellPos(arrCellpos[i])[1]); if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[i])) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[i]] as WF_TableFieldValue; tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[i])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[i])[1]; tfv.ExcelSheetName = xx.Name; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[i])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[i])[1]; tfv.ExcelSheetName = xx.Name; valuehs.Add(lp.LPID + "$" + arrCellpos[i], tfv); } } LockExcel(wb, xx); return; } string[] extraWord = lp.ExtraWord.Split(pchar); List<int> arrCellCount = String2Int(arrtemp); if (arrCellpos.Length == 1 || string.IsNullOrEmpty(arrCellpos[1])) { if (string.IsNullOrEmpty(str)) ea.SetCellValue("", GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]); else ea.SetCellValue("'" + str, GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]); if (valuehs.ContainsKey(lp.LPID + "$" + lp.CellPos)) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + lp.CellPos] as WF_TableFieldValue; tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(lp.CellPos)[0]; tfv.YExcelPos = GetCellPos(lp.CellPos)[1]; tfv.ExcelSheetName = xx.Name; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(lp.CellPos)[0]; tfv.YExcelPos = GetCellPos(lp.CellPos)[1]; tfv.ExcelSheetName = xx.Name; valuehs.Add(lp.LPID + "$" + lp.CellPos, tfv); } } else if (arrCellpos.Length > 1 && (!string.IsNullOrEmpty(arrCellpos[1]))) { StringHelper help = new StringHelper(); if (lp.CellName == "编号") { for (int j = 0; j < arrCellpos.Length; j++) { if (str.IndexOf("\r\n") == -1 && str.Length <= help.GetFristLen(str, arrCellCount[j]) && str != "") { string strNew = str.Substring(0, str.Length - 1) + (j + 1).ToString(); ea.SetCellValue("'" + strNew, GetCellPos(arrCellpos[j])[0], GetCellPos(arrCellpos[j])[1]); if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[j])) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[j]] as WF_TableFieldValue; tfv.ControlValue = strNew; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[j])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[j])[1]; tfv.ExcelSheetName = xx.Name; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = strNew; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[j])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[j])[1]; tfv.ExcelSheetName = xx.Name; valuehs.Add(lp.LPID + "$" + arrCellpos[j], tfv); } } } LockExcel(wb, xx); return; } int i = 0; if (arrCellCount[0] != arrCellCount[1]) { if (str.IndexOf("\r\n") == -1 && str.Length <= help.GetFristLen(str, arrCellCount[0])) { if (string.IsNullOrEmpty(str)) ea.SetCellValue("", GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]); else ea.SetCellValue("'" + str, GetCellPos(arrCellpos[0])[0], GetCellPos(arrCellpos[0])[1]); if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[0])) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[0]] as WF_TableFieldValue; tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[0])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[0])[1]; tfv.ExcelSheetName = xx.Name; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = str; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[0])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[0])[1]; tfv.ExcelSheetName = xx.Name; valuehs.Add(lp.LPID + "$" + arrCellpos[0], tfv); } LockExcel(wb, xx); return; } ea.SetCellValue("'" + str.Substring(0, str.IndexOf("\r\n") != -1 && help.GetFristLen(str, arrCellCount[0]) >= str.IndexOf("\r\n") ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0])), GetCellPos(arrCellpos[0])[0], GetCellPos(arrCellpos[0])[1]); if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[0])) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[0]] as WF_TableFieldValue; tfv.ControlValue = (str.Substring(0, str.IndexOf("\r\n") != -1 && help.GetFristLen(str, arrCellCount[0]) >= str.IndexOf("\r\n") ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0]))); tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[0])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[0])[1]; tfv.ExcelSheetName = xx.Name; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = (str.Substring(0, str.IndexOf("\r\n") != -1 && help.GetFristLen(str, arrCellCount[0]) >= str.IndexOf("\r\n") ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0]))); tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellpos[0])[0]; tfv.YExcelPos = GetCellPos(arrCellpos[0])[1]; tfv.ExcelSheetName = xx.Name; valuehs.Add(lp.LPID + "$" + arrCellpos[0], tfv); } str = str.Substring(help.GetFristLen(str, arrCellCount[0]) >= str.IndexOf("\r\n") && str.IndexOf("\r\n") != -1 ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0])); i++; } str = help.GetPlitString(str, arrCellCount[1]); FillMutilRows(ea, i, lp, str, arrCellCount, arrCellpos); } if (lp.CellName == "单位") { IList list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", "select OrgCode from mOrg where OrgName='" + str + "'"); if (list.Count > 0) { switch (kind) { case "电力线路第一种工作票": case "yzgzp": strNumber = "07" + System.DateTime.Now.Year.ToString() + list[0].ToString().Substring(list[0].ToString().Length - 2, 2); break; case "电力线路第二种工作票": case "ezgzp": strNumber = "08" + System.DateTime.Now.Year.ToString() + list[0].ToString().Substring(list[0].ToString().Length - 2, 2); break; case "电力线路倒闸操作票": case "dzczp": strNumber = "BJ" + System.DateTime.Now.Year.ToString(); break; case "电力线路事故应急抢修单": case "xlqxp": strNumber = list[0].ToString().Substring(list[0].ToString().Length - 2, 2) + System.DateTime.Now.Year.ToString(); break; default: strNumber = "07" + System.DateTime.Now.Year.ToString() + list[0].ToString().Substring(list[0].ToString().Length - 2, 2); break; } IList<LP_Record> listLPRecord = ClientHelper.PlatformSqlMap.GetList<LP_Record>("SelectLP_RecordList", " where kind = '" + kind + "' and number like '" + strNumber + "%'"); if (kind == "yzgzp") { strNumber += (listLPRecord.Count + 1).ToString().PadLeft(3, '0') + "-1"; } else { strNumber += (listLPRecord.Count + 1).ToString().PadLeft(3, '0'); } if (ctrlNumber != null) { ctrlNumber.Text = strNumber; } if (currRecord != null) currRecord.OrgName = str; //ContentChanged(ctrlNumber); } } LockExcel(wb, xx); } catch { } }
/// <summary> ///创建工作表并将数据加入到工作表中 /// </summary> /// <param name="ex">操作EXCEL表</param> /// <param name="bdzlist">填入的数据组</param> /// <param name="hs">每一个对应的行数</param> /// <param name="stawz">每一个对应的要填写的位置</param> /// <param name="pageindex">分的工作表的数目</param> /// <returns>返回List<string> </returns> public static void CreatandWritesheet(ExcelAccess ex,List<string>[] bdzlist,int[] hs,int[] stawz ) { int pageindex = 1; for (int i = 0; i < bdzlist.Length;i++ ) { if (pageindex < GetPagecount(bdzlist[i].Count,hs[i])) { pageindex = GetPagecount(bdzlist[i].Count, hs[i]); } } for (int j = 1; j <= pageindex; j++) { if (j > 1) { ex.CopySheet(0, j + 1); ex.ActiveSheet(j + 1); } int prepageindex = j - 1; //主题 int starow = prepageindex * 2 + 1; int endrow = j * 2; for (int num = 0; num < bdzlist.Length;num++ ) { if (bdzlist[num].Count > endrow) { for (int i = 0; i < hs[num]; i++) { ex.SetCellValue(bdzlist[num][starow - 1 + i], stawz[num] + i, 1); } } else if (bdzlist[num].Count <= endrow && bdzlist[num].Count >= starow) { for (int i = 0; i < bdzlist[num].Count - starow + 1; i++) { ex.SetCellValue(bdzlist[num][starow - 1 + i], stawz[num] + i, 1); } } } } }
public void FillMutilRows(ExcelAccess ea, int i, LP_Temple lp, string str, List<int> arrCellCount, string[] arrCellPos) { StringHelper help = new StringHelper(); str = help.GetPlitString(str, arrCellCount[1]); string[] extraWord = lp.ExtraWord.Split(pchar); string[] arrRst = str.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries); int j = 0; for (; i < arrCellPos.Length; i++) { if (j >= arrRst.Length) break; ea.SetCellValue("'" + arrRst[j], GetCellPos(arrCellPos[i])[0], GetCellPos(arrCellPos[i])[1]); if (valuehs.ContainsKey(lp.LPID + "$" + arrCellPos[i])) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellPos[i]] as WF_TableFieldValue; tfv.ControlValue = arrRst[j]; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellPos[i])[0]; tfv.YExcelPos = GetCellPos(arrCellPos[i])[1]; tfv.ExcelSheetName = activeSheetName; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = arrRst[j]; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellPos[i])[0]; tfv.YExcelPos = GetCellPos(arrCellPos[i])[1]; tfv.ExcelSheetName = activeSheetName; valuehs.Add(lp.LPID + "$" + arrCellPos[i], tfv); } j++; } }
private void btExport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { if (MainHelper.UserOrg == null) return; if (gridView1.FocusedRowHandle < 0) return; DataRow dr = gridView1.GetDataRow(gridView1.FocusedRowHandle); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = ""; saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { fname = saveFileDialog1.FileName; try { string slqwhere = " where RecordId='" + dr["recordID"] + "' and UserControlId='" + Temple.LPID + "' and WorkflowId='" + modleGuid + "' and WorkFlowInsId='" + parentObj.OrgName + "' "; IList<WF_TableFieldValue> tfvli = MainHelper.PlatformSqlMap.GetList<WF_TableFieldValue>(slqwhere); DSOFramerControl dsoFramerWordControl1 = new DSOFramerControl(); dsoFramerWordControl1.FileDataGzip = Temple.DocContent; Excel.Workbook wb = dsoFramerWordControl1.AxFramerControl.ActiveDocument as Excel.Workbook; Excel.Worksheet xx; ExcelAccess ea = new ExcelAccess(); ea.MyWorkBook = wb; ea.MyExcel = wb.Application; string activeSheetName = ""; xx = wb.Application.Sheets[1] as Excel.Worksheet; int i = 0; ArrayList al = new ArrayList(); for (i = 1; i <= wb.Application.Sheets.Count; i++) { xx = wb.Application.Sheets[i] as Excel.Worksheet; if (!al.Contains(xx.Name)) al.Add(xx.Name); } for (i = 0; i < tfvli.Count; i++) { if (!al.Contains(tfvli[i].ExcelSheetName)) { continue; } LP_Temple field = MainHelper.PlatformSqlMap.GetOneByKey<LP_Temple>(tfvli[i].FieldId); if (field != null) { if (field.isExplorer == 1) { continue; } } if (activeSheetName != tfvli[i].ExcelSheetName) { if (activeSheetName != "") { xx = wb.Application.Sheets[activeSheetName] as Excel.Worksheet; } xx = wb.Application.Sheets[tfvli[i].ExcelSheetName] as Excel.Worksheet; activeSheetName = tfvli[i].ExcelSheetName; ea.ActiveSheet(xx.Index); } if (tfvli[i].XExcelPos > -1 && tfvli[i].YExcelPos > -1) ea.SetCellValue(tfvli[i].ControlValue, tfvli[i].XExcelPos, tfvli[i].YExcelPos); } dsoFramerWordControl1.FileDataGzip = Temple.DocContent; dsoFramerWordControl1.FileSave(fname, true); dsoFramerWordControl1.FileClose(); if (MsgBox.ShowAskMessageBox("导出成功,是否打开该文档?") != DialogResult.OK) return; System.Diagnostics.Process.Start(fname); } catch (Exception ex) { Console.WriteLine(ex.Message); MsgBox.ShowWarningMessageBox("无法保存" + fname + "。请用其他文件名保存文件,或将文件存至其他位置。"); return; } } }
public void FillTime(ExcelAccess ea, LP_Temple lp, DateTime dt) { string[] arrCellPos = lp.CellPos.Split(pchar); arrCellPos = StringHelper.ReplaceEmpty(arrCellPos).Split(pchar); string[] extraWord = lp.ExtraWord.Split(pchar); IList<string> strList = new List<string>(); //if (arrCellPos.Length == 5) //{ // strList.Add(dt.Year.ToString()); strList.Add(dt.Month.ToString()); // strList.Add(dt.Day.ToString()); strList.Add(dt.Hour.ToString()); strList.Add(dt.Minute.ToString()); //} //else if (arrCellPos.Length == 4) //{ // strList.Add(dt.Day.ToString()); strList.Add(dt.Hour.ToString()); strList.Add(dt.Minute.ToString()); //} //else if (arrCellPos.Length == 3) //{ // // strList.Add(dt.Year.ToString()); strList.Add(dt.Month.ToString()); // //strList.Add(dt.Day.ToString()); strList.Add(dt.Hour.ToString()); strList.Add(dt.Minute.ToString()); // strList.Add(dt.Year.ToString()); // strList.Add(dt.Month.ToString()); // strList.Add(dt.Day.ToString()); //} //else if (arrCellPos.Length == 2) //{ // strList.Add(dt.Hour.ToString()); // strList.Add(dt.Minute.ToString()); //} //else if (arrCellPos.Length == 1) //{ // strList.Add(dt.ToString()); //} switch (lp.WordCount) { case "yyyy-MM-dd": strList.Add(dt.Year.ToString()); strList.Add(dt.Month.ToString()); strList.Add(dt.Day.ToString()); break; case "MM-dd日": strList.Add(dt.Month.ToString()); strList.Add(dt.Day.ToString()); break; case "yyyy-MM-dd HH:mm:ss": strList.Add(dt.Year.ToString()); strList.Add(dt.Month.ToString()); strList.Add(dt.Day.ToString()); strList.Add(dt.Hour.ToString()); strList.Add(dt.Second.ToString()); break; case "HH:mm:ss": strList.Add(dt.Hour.ToString()); strList.Add(dt.Minute.ToString()); strList.Add(dt.Second.ToString()); break; case "MM-dd日 HH:mm": strList.Add(dt.Month.ToString()); strList.Add(dt.Day.ToString()); strList.Add(dt.Hour.ToString()); strList.Add(dt.Minute.ToString()); break; case "dd日 HH:mm": strList.Add(dt.Day.ToString()); strList.Add(dt.Hour.ToString()); strList.Add(dt.Minute.ToString()); break; case "HH:mm": strList.Add(dt.Hour.ToString()); strList.Add(dt.Minute.ToString()); break; default: strList.Add(dt.Year.ToString()); strList.Add(dt.Month.ToString()); strList.Add(dt.Day.ToString()); strList.Add(dt.Hour.ToString()); strList.Add(dt.Minute.ToString()); break; } // int i = 0; for (int i = 0; i < strList.Count; i++) { if (extraWord.Length > i) { ea.SetCellValue("'" + strList[i] + extraWord[i], GetCellPos(arrCellPos[i])[0], GetCellPos(arrCellPos[i])[1]); if (valuehs.ContainsKey(lp.LPID + "$" + arrCellPos[i])) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellPos[i]] as WF_TableFieldValue; tfv.ControlValue = strList[i] + extraWord[i]; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellPos[i])[0]; tfv.YExcelPos = GetCellPos(arrCellPos[i])[1]; tfv.ExcelSheetName = activeSheetName; tfv = valuehs[lp.LPID + "$" + arrCellPos[i] + "时间"] as WF_TableFieldValue; if (tfv != null) { tfv.ControlValue = dt.ToString(); tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName + "完整时间"; tfv.XExcelPos = -1; tfv.YExcelPos = -1; tfv.ExcelSheetName = activeSheetName; } } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = strList[i] + extraWord[i]; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellPos[i])[0]; tfv.YExcelPos = GetCellPos(arrCellPos[i])[1]; tfv.ExcelSheetName = activeSheetName; valuehs.Add(lp.LPID + "$" + arrCellPos[i], tfv); tfv = new WF_TableFieldValue(); tfv.FieldId = lp.LPID; tfv.ControlValue = dt.ToString(); tfv.FieldName = lp.CellName + "完整时间"; tfv.XExcelPos = -1; tfv.YExcelPos = -1; tfv.ExcelSheetName = activeSheetName; valuehs.Add(lp.LPID + "$" + arrCellPos[i] + "完整时间", tfv); } } else { ea.SetCellValue("'" + strList[i], GetCellPos(arrCellPos[i])[0], GetCellPos(arrCellPos[i])[1]); if (valuehs.ContainsKey(lp.LPID + "$" + arrCellPos[i])) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellPos[i]] as WF_TableFieldValue; tfv.ControlValue = strList[i]; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellPos[i])[0]; tfv.YExcelPos = GetCellPos(arrCellPos[i])[1]; tfv.ExcelSheetName = activeSheetName; tfv = valuehs[lp.LPID + "$" + arrCellPos[0] + "时间"] as WF_TableFieldValue; if (tfv != null) { tfv.ControlValue = dt.ToString(); tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName + "完整时间"; tfv.XExcelPos = -1; tfv.YExcelPos = -1; tfv.ExcelSheetName = activeSheetName; } } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = strList[i]; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName; tfv.XExcelPos = GetCellPos(arrCellPos[i])[0]; tfv.YExcelPos = GetCellPos(arrCellPos[i])[1]; tfv.ExcelSheetName = activeSheetName; valuehs.Add(lp.LPID + "$" + arrCellPos[i], tfv); tfv = new WF_TableFieldValue(); tfv.FieldId = lp.LPID; tfv.ControlValue = dt.ToString(); tfv.FieldName = lp.CellName + "完整时间"; tfv.XExcelPos = -1; tfv.YExcelPos = -1; tfv.ExcelSheetName = activeSheetName; valuehs.Add(lp.LPID + "$" + arrCellPos[0] + "完整时间", tfv); } } } //foreach (string str in strList) //{ // ea.SetCellValue(str, GetCellPos(arrCellPos[i])[0], GetCellPos(arrCellPos[i])[1]); // i++; //} }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(PS_tqbyq obj) { //lgm //页数 int pagecount =1; ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\11配电变压器卡片.xls"; ex.Open(fname); //此处写填充内容代码 int row = 1; string strwhere1=" where byqID='"+obj.byqID+"'"; IList<PJ_11byqbd> pjbdlist = Client.ClientHelper.PlatformSqlMap.GetList<PJ_11byqbd>("SelectPJ_11byqbdList", strwhere1); IList<PJ_11byqdydl> pjdydllist = Client.ClientHelper.PlatformSqlMap.GetList<PJ_11byqdydl>("SelectPJ_11byqdydlList", strwhere1); IList<PJ_11byqdzcl> pjdzcllist = Client.ClientHelper.PlatformSqlMap.GetList<PJ_11byqdzcl>("SelectPJ_11byqdzclList", strwhere1); if (pjbdlist.Count == 0) { pjbdlist.Add(new PJ_11byqbd() { azrq = obj.byqInstallDate, azdd = obj.byqInstallAdress }); } //计算页数 int byqhdpage = Ecommon.GetPagecount(pjbdlist.Count, 3); if (byqhdpage > pagecount) { pagecount = byqhdpage; } int byqdypage = Ecommon.GetPagecount(pjdydllist.Count, 22); if (byqdypage > pagecount) { pagecount = byqdypage; } int byqdzpage = Ecommon.GetPagecount(pjdzcllist.Count, 9); if (byqdzpage > pagecount) { pagecount = byqdzpage; } //复制空模版 if (pagecount>1) { for (int i = 1; i < pagecount; i++) { ex.CopySheet(1, i); ex.ReNameWorkSheet(i + 1, "Sheet" + (i + 1)); } } PS_tq temptq = Client.ClientHelper.PlatformSqlMap.GetOneByKey<PS_tq>(obj.tqID); //固定内容 ex.ActiveSheet(1); //变压器内容 row = 4; ex.SetCellValue(obj.byqModle, row, 4); ex.SetCellValue(obj.byqFactory, row, 14); ex.SetCellValue(obj.byqVolOne + "KV", row, 23); row++; ex.SetCellValue(obj.byqPhase, row, 4); ex.SetCellValue(obj.byqNumber, row, 14); ex.SetCellValue(obj.byqVolTwo + "KV", row, 23); row++;//6 ex.SetCellValue(obj.byqCapcity.ToString(), row, 4); ex.SetCellValue(obj.byqMadeDate.Year.ToString(), row, 14); ex.SetCellValue(obj.byqMadeDate.Month.ToString(), row, 16); ex.SetCellValue(obj.byqMadeDate.Day.ToString(), row, 18); ex.SetCellValue(obj.byqCurrentOne.ToString(), row, 23); row += 2;//8 ex.SetCellValue(obj.byqLineGroup, row, 4); ex.SetCellValue(obj.byqCycle, row, 14); ex.SetCellValue(obj.byqCurrentTwo.ToString(), row, 23); //活动页 //标题 ex.SetCellValue("配电变压器卡片(" + temptq.tqName + ")", 2, 1); for (int k = 0; k < pagecount; k++) { ex.ActiveSheet(k + 1); //标题 ex.SetCellValue("配电变压器卡片(" + temptq.tqName + ")", 2, 1); //变压器变动内容 row = 12; for (int i = 0; i < 3; i++) { if (k * 3 + i >= pjbdlist.Count) { break; } PJ_11byqbd tempobj = pjbdlist[k * 3 + i]; ex.SetCellValue(tempobj.azrq.Year.ToString(), row + i, 1); ex.SetCellValue(tempobj.azrq.Month.ToString(), row + i, 3); ex.SetCellValue(tempobj.azrq.Day.ToString(), row + i, 5); ex.SetCellValue(tempobj.azdd, row + i, 7); if (tempobj.ccrq.Year != 1900) { ex.SetCellValue(tempobj.ccrq.Year.ToString(), row + i, 14); ex.SetCellValue(tempobj.ccrq.Month.ToString(), row + i, 16); ex.SetCellValue(tempobj.ccrq.Day.ToString(), row + i, 18); ex.SetCellValue(tempobj.ccyy, row + i, 20); } } //电压电流内容 row = 6; for (int j = 0; j < 22; j++) { if (k * 22 + j >= pjdydllist.Count) { break; } PJ_11byqdydl tempobj = pjdydllist[k * 22 + j]; ex.SetCellValue(tempobj.clrq.Year.ToString(), row + j, 26); ex.SetCellValue(tempobj.clrq.Month.ToString(), row + j, 28); ex.SetCellValue(tempobj.clrq.Day.ToString(), row + j, 30); ex.SetCellValue(tempobj.fjtwz, row + j, 32); ex.SetCellValue(tempobj.ao.ToString(), row + j, 33); ex.SetCellValue(tempobj.bo.ToString(), row + j, 34); ex.SetCellValue(tempobj.co.ToString(), row + j, 35); ex.SetCellValue(tempobj.a.ToString(), row + j, 36); ex.SetCellValue(tempobj.b.ToString(), row + j, 37); ex.SetCellValue(tempobj.c.ToString(), row + j, 38); ex.SetCellValue(tempobj.ao2.ToString(), row + j, 39); ex.SetCellValue(tempobj.bo2.ToString(), row + j, 40); ex.SetCellValue(tempobj.co2.ToString(), row + j, 41); } //绝缘电阻测量内容 row = 16; for (int l = 0; l < 9; l++) { if (k*9+l>=pjdzcllist.Count) { break; } if (l % 3 == 0 && l > 0) { row = 16 - l; } PJ_11byqdzcl tempobj = pjdzcllist[k * 9 + l]; if (l < 3) { ex.SetCellValue(tempobj.clrq.Year.ToString(), row + l, 7); ex.SetCellValue(tempobj.clrq.Month.ToString(), row + l, 10); ex.SetCellValue(tempobj.clrq.Day.ToString(), row + l, 12); ex.SetCellValue(tempobj.one2one.ToString(), row + l + 1, 7); ex.SetCellValue(tempobj.one2d.ToString(), row + l + 2, 7); ex.SetCellValue(tempobj.two2d.ToString(), row + l + 3, 7); row++; } else if (3 <= l && l < 6) { ex.SetCellValue(tempobj.clrq.Year.ToString(), row + l, 14); ex.SetCellValue(tempobj.clrq.Month.ToString(), row + l, 16); ex.SetCellValue(tempobj.clrq.Day.ToString(), row + l, 18); ex.SetCellValue(tempobj.one2one.ToString(), row + l + 1, 14); ex.SetCellValue(tempobj.one2d.ToString(), row + l + 2, 14); ex.SetCellValue(tempobj.two2d.ToString(), row + l + 3, 14); row++; } else if (6 <= l && l < 9) { ex.SetCellValue(tempobj.clrq.Year.ToString(), row + l, 20); ex.SetCellValue(tempobj.clrq.Month.ToString(), row + l, 22); ex.SetCellValue(tempobj.clrq.Day.ToString(), row + l, 24); ex.SetCellValue(tempobj.one2one.ToString(), row + l + 1, 20); ex.SetCellValue(tempobj.one2d.ToString(), row + l + 2, 20); ex.SetCellValue(tempobj.two2d.ToString(), row + l + 3, 20); row++; } } } //设第一工作表为当前工作表 ex.ActiveSheet(1); ex.ShowExcel(); }
public void FillMutilRowsT(ExcelAccess ea, LP_Temple lp, string str, int cellcount, string arrCellPos, string coltemp) { StringHelper help = new StringHelper(); //str = help.GetPlitString(str, cellcount); string[] arrRst = str.Split(new string[] { "\r\n" }, StringSplitOptions.None); if (lp.ExtraWord == "横向") { for (int i = 0; i < arrRst.Length; i++) { ea.SetCellValue("'" + arrRst[i], GetCellPos(arrCellPos)[0], GetCellPos(arrCellPos)[1] + i); if (valuehs.ContainsKey(lp.LPID + "$" + Convert.ToString(GetCellPos(arrCellPos)[0] + i) + "|" + GetCellPos(arrCellPos)[1])) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + Convert.ToString(GetCellPos(arrCellPos)[0] + i) + "|" + GetCellPos(arrCellPos)[1]] as WF_TableFieldValue; tfv.ControlValue = arrRst[i]; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName + "-" + coltemp; tfv.XExcelPos = GetCellPos(arrCellPos)[0]; tfv.YExcelPos = GetCellPos(arrCellPos)[1] + i; tfv.ExcelSheetName = activeSheetName; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = arrRst[i]; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName + "-" + coltemp; tfv.XExcelPos = GetCellPos(arrCellPos)[0]; tfv.YExcelPos = GetCellPos(arrCellPos)[1] + i; tfv.ExcelSheetName = activeSheetName; valuehs.Add(lp.LPID + "$" + Convert.ToString(GetCellPos(arrCellPos)[0] + i) + "|" + GetCellPos(arrCellPos)[1], tfv); } } } else { for (int i = 0; i < arrRst.Length; i++) { ea.SetCellValue("'" + arrRst[i], GetCellPos(arrCellPos)[0] + i, GetCellPos(arrCellPos)[1]); if (valuehs.ContainsKey(lp.LPID + "$" + Convert.ToString(GetCellPos(arrCellPos)[0] + i) + "|" + GetCellPos(arrCellPos)[1])) { WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + Convert.ToString(GetCellPos(arrCellPos)[0] + i) + "|" + GetCellPos(arrCellPos)[1]] as WF_TableFieldValue; tfv.ControlValue = arrRst[i]; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName + "-" + coltemp; tfv.XExcelPos = GetCellPos(arrCellPos)[0] + i; tfv.YExcelPos = GetCellPos(arrCellPos)[1]; tfv.ExcelSheetName = activeSheetName; } else { WF_TableFieldValue tfv = new WF_TableFieldValue(); tfv.ControlValue = arrRst[i]; tfv.FieldId = lp.LPID; tfv.FieldName = lp.CellName + "-" + coltemp; tfv.XExcelPos = GetCellPos(arrCellPos)[0] + i; tfv.YExcelPos = GetCellPos(arrCellPos)[1]; tfv.ExcelSheetName = activeSheetName; valuehs.Add(lp.LPID + "$" + Convert.ToString(GetCellPos(arrCellPos)[0] + i) + "|" + GetCellPos(arrCellPos)[1], tfv); } } } }