/// <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); } } } }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel1(sdjl_09pxjl obj) { //lgm ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\送电18培训记录.xls"; ex.Open(fname); //每行显示文字长度 int zc = 58; //与会人员之间的间隔符号 char[] jksign = new char[1] { ';' }; int row = 1; int col = 1; //计算页码 int pagecount = 1; //题目 string tmstr = Ecommon.Comparestring(obj.tm, "题目") ? "" : "题目:"; List<string> tmlist = Ecommon.ResultStrListByPage(tmstr, obj.tm, zc, 4); //内容 string nrstr = Ecommon.Comparestring(obj.nr, "内容") ? "" : "内容:"; List<string> nrlist = Ecommon.ResultStrListByPage(nrstr, obj.nr, zc, 10); //领导评语 string ldpystr = Ecommon.Comparestring(obj.py, "领导检查评语") ? "" : "领导检查评语:"; List<string> ldpylist = Ecommon.ResultStrListByPage(ldpystr, obj.py, zc, 3); //页数 if (Ecommon.GetPagecount(tmlist.Count + nrlist.Count + ldpylist.Count, 15) > pagecount) { pagecount = Ecommon.GetPagecount(ldpylist.Count + tmlist.Count + nrlist.Count, 15); } //复制空模版 if (pagecount > 1) { for (int i = 1; i < pagecount; i++) { ex.CopySheet(1, i); ex.ReNameWorkSheet(i + 1, "Sheet" + (i + 1)); } } for (int p = 0; p < pagecount; p++) { ex.ActiveSheet(p + 1); //改造后的 for (int i = 0; i < 17; i++) { if (p * 17 + i < tmlist.Count) { string tempstr = tmlist[p * 17 + i]; ex.SetCellValue(tempstr, 7 + i, 1); if (p == 0 && i == 0) { //设定活动内容为粗体 ex.SetFontBold(7 + i, 1, 7 + i, 1, true, 0, 3); } } if (p * 17 + i >= tmlist.Count && p * 17 + i < tmlist.Count + nrlist.Count) { string tempstr = nrlist[p * 17 + i - tmlist.Count]; ex.SetCellValue(tempstr, 7 + i, 1); if (p * 17 + i == tmlist.Count) { ex.SetFontBold(7 + i, 1, 7 + i, 1, true, 0, 3); } //break; } if (p * 17 + i >= tmlist.Count + nrlist.Count && p * 17 + i < tmlist.Count + nrlist.Count + ldpylist.Count) { string tempstr = ldpylist[p * 17 + i - tmlist.Count - nrlist.Count]; ex.SetCellValue(tempstr, 7 + i, 1); if (p * 17 + i == tmlist.Count + nrlist.Count) ex.SetFontBold(7 + i, 1, 7 + i, 1, true, 0, 7); //break; } if (p * 17 + i >= tmlist.Count + nrlist.Count + ldpylist.Count) { break; } } } ex.ActiveSheet(1); //培训时间 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.ShowExcel(); }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(PJ_02aqhd obj) { //lgm ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\02安全活动记录.xls"; ex.Open(fname); //每行显示文字长度 int zc = 58; //与会人员之间的间隔符号 char[] jksign = new char[1] { ';' }; int row = 1; int col = 1; //计算页码 int pagecount = 1; //活动内容 string hdstr = Ecommon.Comparestring(obj.hdnr, "活动内容") ? "" : "活动内容:"; List<string> hdlist = Ecommon.ResultStrListByPage(hdstr, obj.hdnr, zc, 8); //if (Ecommon.GetPagecount(hdlist.Count, 8) > pagecount) //{ // pagecount = Ecommon.GetPagecount(hdlist.Count, 8); //} //活动小结 string hdxjstr = Ecommon.Comparestring(obj.hdxj, "活动小结") ? "" : "活动小结:"; List<string> hdxlist = Ecommon.ResultStrListByPage(hdxjstr, obj.hdxj, zc, 5); //if (Ecommon.GetPagecount(hdxlist.Count, 5) > pagecount) //{ // pagecount = Ecommon.GetPagecount(hdxlist.Count, 5); //} //发言简要记录 //领导评语 string ldpystr = Ecommon.Comparestring(obj.py, "领导检查评语") ? "" : "领导检查评语:"; List<string> ldpylist = Ecommon.ResultStrListByPage(ldpystr, obj.py, zc, 2); if (Ecommon.GetPagecount(ldpylist.Count + hdxlist.Count + hdlist.Count, 15) > pagecount) { pagecount = Ecommon.GetPagecount(ldpylist.Count + hdxlist.Count + hdlist.Count, 15); } List<string> fyjyjllist = Ecommon.ResultStrListByPage("", obj.fyjyjl, zc, 21); if (Ecommon.GetPagecount(fyjyjllist.Count, 21) > pagecount) { pagecount = Ecommon.GetPagecount(fyjyjllist.Count, 21); } //复制空模版 if (pagecount > 1) { for (int i = 1; i < pagecount; i++) { ex.CopySheet(1, i); ex.ReNameWorkSheet(i + 1, "Sheet" + (i + 1)); } } for (int p = 0; p < pagecount; p++) { ex.ActiveSheet(p + 1); //改造后的 for (int i = 0; i < 15; i++) { if (p * 15 + i < hdlist.Count) { string tempstr = hdlist[p * 15 + i]; ex.SetCellValue(tempstr, 10 + i, 1); if (p == 0 && i == 0) { //设定活动内容为粗体 ex.SetFontBold(10 + i, 1, 10 + i, 1, true, 0, 5); } } if (p * 15 + i >= hdlist.Count && p * 15 + i < hdlist.Count + hdxlist.Count) { string tempstr = hdxlist[p * 15 + i - hdlist.Count]; ex.SetCellValue(tempstr, 10 + i, 1); if (p * 15 + i == hdlist.Count) { ex.SetFontBold(10 + i, 1, 10 + i, 1, true, 0, 5); } //break; } if (p * 15 + i >= hdlist.Count + hdxlist.Count && p * 15 + i < hdlist.Count + hdxlist.Count + ldpylist.Count) { string tempstr = ldpylist[p * 15 + i - hdlist.Count - hdxlist.Count]; ex.SetCellValue(tempstr, 10 + i, 1); if (p * 15 + i == hdlist.Count + hdxlist.Count) ex.SetFontBold(10 + i, 1, 10 + i, 1, true, 0, 7); //break; } if (p * 15 + i >= hdlist.Count + hdxlist.Count + ldpylist.Count) { break; } } ////活动内容 //for (int i = 0; i < 8; i++) //{ // if (p * 8 + i>=hdlist.Count) // { // break; // } // string tempstr = hdlist[p * 8 + i]; // ex.SetCellValue(tempstr, 10 + i, 1); //} ////活动小结 //for (int s = 0; s < 5; s++) //{ // if (p * 5 + s >= hdxlist.Count) // { // break; // } // string tempstr = hdxlist[p * 5 + s]; // ex.SetCellValue(tempstr, 18 + s, 1); //} ////领导评语 //for (int t = 0; t < 2; t++) //{ // if (p * 2 + t >= ldpylist.Count) // { // break; // } // string tempstr = ldpylist[p * 2 + t]; // ex.SetCellValue(tempstr, 23 + t, 1); //} //简要记录 int jymaxpage = Ecommon.GetPagecount(fyjyjllist.Count, 21); if (pagecount - jymaxpage <= p) { for (int jy = 0; jy < 21; jy++) { if ((jymaxpage + p - pagecount) * 21 + jy >= fyjyjllist.Count) { break; } string tempstr = fyjyjllist[(jymaxpage + p - pagecount) * 21 + jy]; ex.SetCellValue(tempstr, 30 + jy, 1); if (p == pagecount - jymaxpage && jy == 0) { //设定活动内容为粗体 ex.SetFontBold(30, 1, 30, 1, true, 0, 6); } } } } ex.ActiveSheet(1); //主持人 ex.SetCellValue(obj.zcr, 4, 2); //开始时间 row = 2; ex.SetCellValue(obj.kssj.Year.ToString(), row + 2, col + 4); ex.SetCellValue(obj.kssj.Month.ToString(), row + 2, col + 6); ex.SetCellValue(obj.kssj.Day.ToString(), row + 2, col + 8); ex.SetCellValue(obj.kssj.Hour.ToString(), row + 2, col + 10); ex.SetCellValue(obj.kssj.Minute.ToString(), row + 2, col + 12); //结束时间 ex.SetCellValue(obj.jssj.Year.ToString(), row + 3, col + 4); ex.SetCellValue(obj.jssj.Month.ToString(), row + 3, col + 6); ex.SetCellValue(obj.jssj.Day.ToString(), row + 3, col + 8); ex.SetCellValue(obj.jssj.Hour.ToString(), row + 3, col + 10); ex.SetCellValue(obj.jssj.Minute.ToString(), row + 3, col + 12); //出席人员 string[] ary = obj.cjry.Split(jksign); int m = ary.Length / 8; int n = ary.Length % 8; if (n > 0) { m++; } if (m == 0) { m++; } for (int i = 0; i < ary.Length; i++) { int tempcol = col + 1 + i % 8; if (i % 8 > 3) { tempcol = col + 4 + (i % 8 - 3) * 2; } ex.SetCellValue(ary[i], row + 4 + i / 8, tempcol); } //缺席人员 string[] ary2 = obj.qxry.Split(jksign); for (int j = 0; j < ary2.Length; j++) { int tempcol = col + 2 + j % 7; if (j > 2 && j < 7) { tempcol = col + 4 + (j % 7 - 2) * 2; } if (j < 7) { ex.SetCellValue(ary2[j], row + 7, tempcol); } else//缺席人员大于七个时 { string tempstr = ex.ReadCellValue(row + 7, col + 12); tempstr = tempstr + "/" + ary2[j]; ex.SetCellValue(tempstr, row + 7, col + 12); } } //签字 // ex.SetCellValue("签字: ", 25, 1); // ex.SetCellValue(obj.qz, 25, col + 1); //签字时间小于系统默认时间则不输出 // if (ComboBoxHelper.CompreDate(obj.qzrq)) // { // ex.SetCellValue(obj.qzrq.Year.ToString(), 25, 5); // ex.SetCellValue(obj.qzrq.Month.ToString(), 25, 7); // ex.SetCellValue(obj.qzrq.Day.ToString(), 25, 11); // } ex.ShowExcel(); }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(sdjls_fsgyxjl obj) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\送电05反事故演习记录.xls"; ex.Open(fname); ex.ActiveSheet(1); //每行显示文字长度 int zc = 39; //日期 ex.SetCellValue(obj.yxrq.Year.ToString(),4,2); ex.SetCellValue(obj.yxrq.Month.ToString(), 4, 4); ex.SetCellValue(obj.yxrq.Day.ToString(), 4, 6); //单位 ex.SetCellValue(obj.yxdd,4,11); //时间 ex.SetCellValue(obj.kssj.Hour.ToString(),5,2); ex.SetCellValue(obj.kssj.Minute.ToString(), 5, 6); ex.SetCellValue(obj.jssj.Hour.ToString(), 5, 11); ex.SetCellValue(obj.jssj.Minute.ToString(),5,13); //演习地点 ex.SetCellValue(obj.yxdd, 6, 3); //演习题目 ex.SetCellValue(obj.yxtm, 7, 3); ex.SetCellValue(obj.ldr, 8, 3); ex.SetCellValue(obj.jhr, 8, 11); string jljpj = Ecommon.Comparestring(obj.jljpj, "结论及对参加演习人的评价") ? "" : "结论及对参加演习人的评价:"; jljpj = jljpj + obj.jljpj; //结论及对参加演习人的评价 for (int i = 0; i < 8; i++) { string tempstr = ""; int startnum = i * zc; int endnum = (i + 1) * zc; bool ISempty = false; if (startnum >= jljpj.Length) { ISempty = true; } else if (endnum >= jljpj.Length) { endnum = jljpj.Length; } if (!ISempty) { tempstr = jljpj.Substring(startnum, endnum - startnum); } ex.SetCellValue(tempstr, 9 + i, 1); if (i == 0) { ex.SetFontBold(9, 1, 9, 1, true, 1, 13); } } //根据演习结果拟定的措施 int cszs = 23; for (int i = 0; i < 4; i++) { string tempstr = ""; int startnum = i * cszs; int endnum = (i + 1) * cszs; bool ISempty = false; if (startnum >= obj.ndcs.Length) { ISempty = true; } else if (endnum >= obj.ndcs.Length) { endnum = obj.ndcs.Length; } if (!ISempty) { if (i == 0) { if (endnum == obj.ndcs.Length) { tempstr = obj.ndcs.Substring(startnum, endnum - startnum); } else { tempstr = obj.ndcs.Substring(startnum, endnum - startnum - 2); } } else { if (endnum == obj.ndcs.Length) { tempstr = obj.ndcs.Substring(startnum - 2, endnum - startnum+1); } else { tempstr = obj.ndcs.Substring(startnum - 2, endnum - startnum); } } } ex.SetCellValue(tempstr, 18 + i, 1); } //执行人 ex.SetCellValue(obj.zxr, 18, 10); ex.SetCellValue(obj.zgxq.Year+"年"+obj.zgxq.Month+"月"+obj.zgxq.Day+"日", 18, 11); ex.SetCellValue(obj.qzldr, 22, 2); ex.SetCellValue(obj.qzjhr,22,10); ex.ShowExcel(); }