public static void ExportExcel(Dictionary<string, List<PJ_qxfl>> objdic) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\06设备巡视及缺陷消除记录.xls"; ex.Open(fname); int zc = 20; //与会人员之间的间隔符号 char[] jksign = new char[1] { ';' }; int row = 7; int col = 1; int len1 = 3; int pageindex = 1; pageindex = objdic.Count; //根据所有的线路数来确定创建的页数 for (int j = 1; j <= pageindex; j++) { if (j > 1) { ex.CopySheet(1, 1); } } int pags = 0; foreach (KeyValuePair<string, List<PJ_qxfl>> pp in objdic) { List<PJ_qxfl> objlist = pp.Value; ex.ActiveSheet(pags++); } }
public static void ExportExcel10KV(IList<PS_kgjctj> datalist) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\10KV用户供电可靠性基础数据统计表.xls"; ex.Open(fname); int rowcount = 1; if (datalist.Count < 1) return; string tablename = "10KV用户供电可靠性基础数据统计表"; if (tablename.Length > 30) { tablename = tablename.Substring(tablename.Length - 31); } // //加页 int pageindex = datalist.Count; for (int j = 1; j < pageindex; j++) { ex.CopySheet(1, j); ex.ReNameWorkSheet(j + 1, tablename + "(" + (j) + ")"); } for (int j = 0; j < datalist.Count; j++) { if (j == 0) { ex.ActiveSheet(tablename); } else { ex.ActiveSheet(tablename + "(" + j + ")"); } // 填值 ex.SetCellValue(datalist[j].OrgName, 3, 2); ex.SetCellValue(datalist[j].CreateTime.ToString("yyyy年MM月dd日"), 5, 9); ex.SetCellValue(datalist[j].kgModel, 5, 2); ex.SetCellValue(datalist[j].iscxkg == true ? "是" : "否", 5, 7); ex.SetCellValue(datalist[j].kgCode, 5, 9); ex.SetCellValue(datalist[j].jkdxcd, 10, 2); ex.SetCellValue(datalist[j].dlxlcd, 10, 7); ex.SetCellValue(datalist[j].publicusercount.ToString(), 11, 2); ex.SetCellValue(datalist[j].publicbtcount.ToString(), 11, 5); ex.SetCellValue(datalist[j].publicbtrlcount.ToString(), 11, 9); ex.SetCellValue(datalist[j].zyusercount.ToString(), 12, 2); ex.SetCellValue(datalist[j].zybtcount.ToString(), 12, 5); ex.SetCellValue(datalist[j].zybtrlcount.ToString(), 12, 9); ex.SetCellValue(datalist[j].sdyusercount.ToString(), 13, 2); ex.SetCellValue(datalist[j].sdyrlcount.ToString(), 13, 5); ex.SetCellValue(datalist[j].zyuserqtsbcount.ToString(), 13, 9); ex.SetCellValue(datalist[j].drqcount.ToString(), 14, 2); ex.SetCellValue(datalist[j].drqrl, 14, 5); ex.SetCellValue(datalist[j].zyuserqtsbrlcount.ToString(), 14, 9); } ex.ActiveSheet(tablename); ex.ShowExcel(); }
/// <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 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(); }
/// <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(); }
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(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(); }
public void ExportExcel(ExcelAccess ex ,IList<PJ_anqgjcrkd> datalist) { //此处写填充内容代码 int row = 4; int col = 1; int rowcount = 23; // //加页 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, datalist[0].num); else ex.ReNameWorkSheet(j + 1, datalist[0].num + "(" + (j) + ")"); } for (int j = 0; j < datalist.Count; j++) { if (j % rowcount == 0) { if (j == 0) ex.ActiveSheet(datalist[0].num); else ex.ActiveSheet( datalist[0].num + "(" + (j / rowcount + 1) + ")"); ex.SetCellValue(datalist[j].OrgName, 2, 2); } ex.SetCellValue((j + 1).ToString(), row + j % rowcount, col); ex.SetCellValue(datalist[j].wpmc, row + j % rowcount, col + 1); ex.SetCellValue(datalist[j].wpdw, row + j % rowcount, col + 2); ex.SetCellValue(datalist[j].wpgg, row + j % rowcount, col + 3); ex.SetCellValue(datalist[j].cksl, row + j % rowcount, col + 4); ex.SetCellValue(datalist[j].lqdw, row + j % rowcount, col + 5); ex.SetCellValue(datalist[j].Remark, row + j % rowcount, col + 6); //ex.SetCellValue(datalist[j].zrr, row + j % rowcount, col + 7); } }
public void ExportExcel(ExcelAccess ex, IList<PJ_qcxqjh> datalist) { //此处写填充内容代码 int row = 5; int col = 1; int rowcount = 6; // //加页 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)); ex.SetCellValue(DateTime.Now.ToString("yyyy年") + "春查线路设备消缺计划表", 2, 1); } ex.SetCellValue((j + 1).ToString(), row + j % rowcount, col); ex.SetCellValue(datalist[j].xqlr, row + j % rowcount, col + 1); ex.SetCellValue(datalist[j].xqbz, row + j % rowcount, col + 2); ex.SetCellValue(datalist[j].wcsj.ToString("MM月dd日"), row + j % rowcount, col + 3); ex.SetCellValue(datalist[j].lsr, row + j % rowcount, col + 4); } }
public void ExportExcel(ExcelAccess ex ,IList<PJ_jggcjh> datalist) { //此处写填充内容代码 int row = 7; int col = 1; int rowcount = 9; // //加页 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); } } int year = DateTime.Now.Year; string str = "零一二三四五六七八九"; string stryear = ""; while (year > 0) { stryear = str[year % 10]+stryear; year = year / 10; } for (int j = 0; j < datalist.Count; j++) { if (j % rowcount == 0) { ex.ActiveSheet(j / rowcount + 1); ex.SetCellValue(datalist[j].OrgName,4, 3); //年技术改造工程计划 ex.SetCellValue(stryear + "年技术改造工程计划", 2, 1); } ex.SetCellValue((j + 1).ToString(), row + j % rowcount, col); ex.SetCellValue(datalist[j].ProjectName, row + j % rowcount, col + 1); ex.SetCellValue(datalist[j].ProjecNR, row + j % rowcount, col + 5); ex.SetCellValue(datalist[j].Planenddate.ToString("yyyy年MM月"), row + j % rowcount, col + 9); ex.SetCellValue(datalist[j].NeedFunds, row + j % rowcount, col + 10); ex.SetCellValue(datalist[j].Remark, row + j % rowcount, col + 11); } }
//ExcelAccess public static void ExportExcel(PJ_05jcky jl) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\05交叉跨越及对地距离测量记录.xls"; ex.Open(fname); int row = 1; int col = 1; int rowcount = 11; // IList<PJ_05jckyjl> list = Client.ClientHelper.PlatformSqlMap.GetListByWhere<PJ_05jckyjl>(" where jckyID='" + jl.jckyID + "' order by CreateDate"); //加页 int pageindex = 1; if (pageindex < Ecommon.GetPagecount(list.Count, 15)) { pageindex = Ecommon.GetPagecount(list.Count, 15); } for (int j = 1; j <= pageindex; j++) { if (j > 1) { ex.CopySheet(1, 1); } } // PS_xl xlobject = Client.ClientHelper.PlatformSqlMap.GetOneByKey<PS_xl>(jl.LineID); ex.ActiveSheet(1); PS_xl xl = Client.ClientHelper.PlatformSqlMap.GetOne<PS_xl>(" where linecode='" + jl.LineID + "'"); if (xl != null) { string sublinename = ""; string linename = andparentlinename(xl); string[] fz = linename.Split(" ".ToCharArray()); if (fz.Length > 2) { sublinename += fz[0] + " " + fz[1] + " " + fz[fz.Length - 1]; } else sublinename = linename; ex.SetCellValue(linename, row + 3, col + 1); } else { string sublinename = ""; string linename = jl.kywz; string[] fz = linename.Split(" ".ToCharArray()); if (fz.Length > 2) { sublinename += fz[0] + " " + fz[1] + " " + fz[fz.Length - 1]; } else sublinename = linename; ex.SetCellValue(linename, row + 3, col + 1); } //ex.SetCellValue("'" + jl.gtID, row + 3, col + 3); //ex.SetCellValue(jl.kywz, row + 3, col + 6); //交叉跨越行 ex.SetCellValue(jl.kygh, row + 6, col); ex.SetCellValue(jl.gdjl.ToString(), row + 6, col + 3); ex.SetCellValue(jl.kymc, row + 6, col + 4); ex.SetCellValue(jl.ssdw, row + 6, col + 5); ex.SetCellValue(jl.jb, row + 6, col + 7); //测量记录 ex.ShowExcel(); for (int j = 1; j <= pageindex; j++) { ex.ActiveSheet(j); //ex.ReNameWorkSheet(j, "Sheet" + (j)); int prepageindex = j - 1; //主题 int starow = prepageindex * 15 + 1; int endrow = j * 15; if (list.Count > endrow) { for (int i = 0; i < 15; i++) { string str = list[starow - 1 + i].clrqz; string[] mans = str.Split(new char[1] { ';' }); ex.SetCellValue(list[starow - 1 + i].clrq.Year.ToString(), rowcount + i, col); ex.SetCellValue(list[starow - 1 + i].clrq.Month.ToString(), rowcount + i, col + 1); ex.SetCellValue(list[starow - 1 + i].clrq.Day.ToString(), rowcount + i, col + 2); ex.SetCellValue(list[starow - 1 + i].scz.ToString(), rowcount + i, col + 3); ex.SetCellValue(list[starow - 1 + i].qw, rowcount + i, col + 4); ex.SetCellValue(mans[0], rowcount + i, col + 5); ex.SetCellValue(mans[1], rowcount + i, col + 6); ex.SetCellValue(list[starow - 1 + i].jr, rowcount + i, col + 7); } } else if (list.Count <= endrow && list.Count >= starow) { for (int i = 0; i < list.Count - starow + 1; i++) { string str = list[starow - 1 + i].clrqz; string[] mans = str.Split(new char[1] { ';' }); ex.SetCellValue(list[starow - 1 + i].clrq.Year.ToString(), rowcount + i, col); ex.SetCellValue(list[starow - 1 + i].clrq.Month.ToString(), rowcount + i, col + 1); ex.SetCellValue(list[starow - 1 + i].clrq.Day.ToString(), rowcount + i, col + 2); ex.SetCellValue(list[starow - 1 + i].scz.ToString(), rowcount + i, col + 3); ex.SetCellValue(list[starow - 1 + i].qw, rowcount + i, col + 4); ex.SetCellValue(mans[0], rowcount + i, col + 5); ex.SetCellValue(mans[1], rowcount + i, col + 6); ex.SetCellValue(list[starow - 1 + i].jr, rowcount + i, col + 7); } } } ex.ActiveSheet(1); ex.ShowExcel(); }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(IList<PJ_08sbtdjx> objlist) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\08设备停电检修记录.xls"; ex.Open(fname); //此处写填充内容代码 int rowcount = 5; //分页 将要变化的进行分页 int pageindex = 1; if (pageindex < Ecommon.GetPagecount(objlist.Count, 20)) { pageindex = Ecommon.GetPagecount(objlist.Count, 20); } 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 * 20 + 1; int endrow = j * 20; if (objlist.Count > endrow) { for (int i = 0; i < 20; i++) { ex.SetCellValue(objlist[starow - 1 + i].tdsj.Month.ToString(), rowcount + i, 1); ex.SetCellValue(objlist[starow - 1 + i].tdsj.Day.ToString(), rowcount + i, 3); ex.SetCellValue(objlist[starow - 1 + i].LineName, rowcount + i, 5); ex.SetCellValue(objlist[starow - 1 + i].jxnr, rowcount + i, 6); ex.SetCellValue(objlist[starow - 1 + i].tdsj.Hour.ToString(), rowcount + i, 7); ex.SetCellValue(objlist[starow - 1 + i].tdsj.Minute.ToString(), rowcount + i, 9); ex.SetCellValue(objlist[starow - 1 + i].sdsj.Hour.ToString(), rowcount + i, 11); ex.SetCellValue(objlist[starow - 1 + i].sdsj.Minute.ToString(), rowcount + i, 13); ex.SetCellValue(objlist[starow - 1 + i].tdxz, rowcount + i, 15); ex.SetCellValue(objlist[starow - 1 + i].gzfzr, rowcount + i, 16); } } else if (objlist.Count <= endrow && objlist.Count >= starow) { for (int i = 0; i < objlist.Count - starow + 1; i++) { ex.SetCellValue(objlist[starow - 1 + i].tdsj.Month.ToString(), rowcount + i, 1); ex.SetCellValue(objlist[starow - 1 + i].tdsj.Day.ToString(), rowcount + i, 3); ex.SetCellValue(objlist[starow - 1 + i].LineName, rowcount + i, 5); ex.SetCellValue(objlist[starow - 1 + i].jxnr, rowcount + i, 6); ex.SetCellValue(objlist[starow - 1 + i].tdsj.Hour.ToString(), rowcount + i, 7); ex.SetCellValue(objlist[starow - 1 + i].tdsj.Minute.ToString(), rowcount + i, 9); ex.SetCellValue(objlist[starow - 1 + i].sdsj.Hour.ToString(), rowcount + i, 11); ex.SetCellValue(objlist[starow - 1 + i].sdsj.Minute.ToString(), rowcount + i, 13); ex.SetCellValue(objlist[starow - 1 + i].tdxz, rowcount + i, 15); ex.SetCellValue(objlist[starow - 1 + i].gzfzr, rowcount + i, 16); } } } ex.ActiveSheet(1); string gdsname = ""; //记录变电所 if (objlist.Count>0) { gdsname = objlist[0].OrgName; } //工具仪表信息 //for (int i = 0; i < objlist.Count;i++ ) //{ // ex.SetCellValue(objlist[i].tdsj.Month.ToString(), rowcount + i, 1); // ex.SetCellValue(objlist[i].tdsj.Day.ToString(), rowcount + i, 3); // ex.SetCellValue(objlist[i].LineName, rowcount + i, 5); // ex.SetCellValue(objlist[i].jxnr, rowcount + i, 6); // ex.SetCellValue(objlist[i].tdsj.Hour.ToString(), rowcount + i, 7); // ex.SetCellValue(objlist[i].tdsj.Minute.ToString(), rowcount + i, 9); // ex.SetCellValue(objlist[i].sdsj.Hour.ToString(), rowcount + i,11); // ex.SetCellValue(objlist[i].sdsj.Minute.ToString(), rowcount + i, 13); // ex.SetCellValue(objlist[i].tdxz, rowcount + i, 15); // ex.SetCellValue(objlist[i].gzfzr, rowcount + i, 16); //} ex.ShowExcel(); }
/// <summary> /// 导出入库单 /// </summary> /// <param name="orgCode"></param> /// <param name="jingbanren"></param> public void ExportGDSRKDExcel(string orgCode, string sql) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\供电所材料出入库明细.xls"; ex.Open(fname); string orgname = (string)ClientHelper.PlatformSqlMap.GetObject("SelectOneStr", "select top 1 OrgName from mOrg where OrgCode='" + orgCode + "'"); ex.SetCellValue(orgname, 3, 3); IList<PJ_gdscrk> datalist = ClientHelper.PlatformSqlMap.GetList<PJ_gdscrk>(sql); if (datalist.Count <= 0) { return; } #region 开始导出 //此处写填充内容代码 int row = 5; int col = 1; int rowcount = 46; 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 = "供电所材料出入库明细"; 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); 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((j + 1).ToString(), row + j % rowcount, col); if (datalist[j].type == "出库") { ex.SetCellValue(datalist[j].ckdate.ToString("yyyy年MM月dd日"), row + j % rowcount, col + 1); ex.SetCellValue(datalist[j].cksl, row + j % rowcount, col + 6); } else { ex.SetCellValue(datalist[j].indate.ToString("yyyy年MM月dd日"), row + j % rowcount, col + 1); ex.SetCellValue(datalist[j].wpsl, row + j % rowcount, col + 5); } ex.SetCellValue(datalist[j].wpmc, row + j % rowcount, col + 2); ex.SetCellValue(datalist[j].wpgg, row + j % rowcount, col + 3); ex.SetCellValue(datalist[j].wpdw, row + j % rowcount, col + 4); ex.SetCellValue(datalist[j].kcslcount, row + j % rowcount, col + 7); } #endregion ex.ShowExcel(); }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(PS_kg obj) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\12线路开关卡片"; ex.Open(fname); //此处写填充内容代码 int pagecount = 1; string strwhere1 = " where kgID='" + obj.kgID + "'"; IList<PJ_12kgbd> pjbdlist = Client.ClientHelper.PlatformSqlMap.GetList<PJ_12kgbd>("SelectPJ_12kgbdList", strwhere1); IList<PJ_12kgjx> pjjxlist = Client.ClientHelper.PlatformSqlMap.GetList<PJ_12kgjx>("SelectPJ_12kgjxList", strwhere1); IList<PJ_12kgsy> pjsylist = Client.ClientHelper.PlatformSqlMap.GetList<PJ_12kgsy>("SelectPJ_12kgsyList", strwhere1); if (Ecommon.GetPagecount(pjbdlist.Count, 14) > pagecount) { pagecount = Ecommon.GetPagecount(pjbdlist.Count, 14); } if (Ecommon.GetPagecount(pjjxlist.Count, 15) > pagecount) { pagecount = Ecommon.GetPagecount(pjjxlist.Count, 15); } if (Ecommon.GetPagecount(pjsylist.Count, 3) > pagecount) { pagecount = Ecommon.GetPagecount(pjsylist.Count, 3); } //复制空模版 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 < 14; i++) { if (p*14+i>=pjbdlist.Count) { break; } PJ_12kgbd tempobj = pjbdlist[p * 14 + i]; ex.SetCellValue(tempobj.azrq.Year.ToString(), 12 + 2 * i, 1); ex.SetCellValue(tempobj.azrq.Month.ToString(), 12 +2 * i, 3); ex.SetCellValue(tempobj.azrq.Day.ToString(), 12 + 2 * i, 5); ex.SetCellValue(tempobj.azdd, 12 + 2 * i, 7); ex.SetCellValue(tempobj.gtbh, 12 + 2 * i, 9); ex.SetCellValue("'"+tempobj.kgCode, 12 + 2 * i,10); ex.SetFontNameSize(12 + 2 * i, 10, 12 + 2 * i, 10, "", 9); //如果日期未写不不输出 {1900-1-1 0:00:00} DateTime dtCCRQ =DateTime.Parse("1900-1-1 0:00:00"); if (tempobj.ccrq != dtCCRQ) { ex.SetCellValue(tempobj.ccrq.Year.ToString(), 12 + 2 * i, 11); ex.SetCellValue(tempobj.ccrq.Month.ToString(), 12 + 2 * i, 13); ex.SetCellValue(tempobj.ccrq.Day.ToString(), 12 + 2 * i, 15); } ex.SetCellValue(tempobj.ccyy, 12 + 2 * i, 17); ex.SetCellValue(tempobj.Remark, 12 + 2 * i, 19); } //实验记录 for (int j = 0; j < 3; j++) { if (p*3+j>=pjsylist.Count) { break; } PJ_12kgsy tempobj = pjsylist[p * 3 + j]; ex.SetCellValue(tempobj.azrq.Year.ToString(), 4, 27 + j * 6); ex.SetCellValue(tempobj.azrq.Month.ToString(), 4, 29 + j * 6); ex.SetCellValue(tempobj.azrq.Day.ToString(), 4, 31 + j * 6); ex.SetCellValue(tempobj.azdd, 5, 27 + j * 6); ex.SetCellValue(tempobj.gtbh, 6, 27 + j * 6); ex.SetCellValue(tempobj.A_BCO, 7, 27 + j * 6); ex.SetCellValue(tempobj.B_CAO, 8, 27 + j * 6); ex.SetCellValue(tempobj.C_ABO, 9, 27 + j * 6); ex.SetCellValue(tempobj.A_BCO2, 10, 27 + j * 6); ex.SetCellValue(tempobj.B_CAO2, 11, 27 + j * 6); ex.SetCellValue(tempobj.C_ABO2, 12, 27 + j * 6); ex.SetCellValue(tempobj.dy, 13, 27 + j * 6); ex.SetCellValue(tempobj.dysj, 14, 27 + j * 6); ex.SetCellValue(tempobj.dl, 15, 27 + j * 6); ex.SetCellValue(tempobj.dlsj, 16, 27 + j * 6); ex.SetCellValue(tempobj.dzA, 17, 27 + j * 6); ex.SetCellValue(tempobj.dzB, 18, 27 + j * 6); ex.SetCellValue(tempobj.dzC, 19, 27 + j * 6); ex.SetCellValue(tempobj.tqjc, 20, 27 + j * 6); ex.SetCellValue(tempobj.wgjc, 21, 27 + j * 6); ex.SetCellValue(tempobj.syjl, 22, 27 + j * 6); ex.SetCellValue(tempobj.syz, 23, 27 + j * 6); } //检修记录 for (int k = 0; k <15 ; k++) { if (p*15+k>=pjjxlist.Count) { break; } PJ_12kgjx tempobj = pjjxlist[p * 15 + k]; ex.SetCellValue(tempobj.jxsj.Year.ToString(), 25+k, 20); ex.SetCellValue(tempobj.jxsj.Month.ToString(), 25+k, 22); ex.SetCellValue(tempobj.jxsj.Day.ToString(), 25+k, 24); ex.SetCellValue(tempobj.jxnr, 25+k, 26); ex.SetCellValue(tempobj.fzr, 25+k, 39); } } ex.ActiveSheet(1); ex.SetCellValue(obj.kgModle, 4, 7); ex.SetCellValue(obj.kgCapcity+"A", 4, 9); ex.SetCellValue(obj.kgMadeDate.Year.ToString(), 4, 11); ex.SetCellValue(obj.kgMadeDate.Month.ToString(), 4, 13); ex.SetCellValue(obj.kgMadeDate.Day.ToString(), 4, 15); ex.SetCellValue(obj.kgLineGroup, 4, 19); ex.SetCellValue(obj.kgFactory, 6, 7); ex.SetCellValue(obj.kgVol, 6, 9); ex.SetCellValue(obj.kgPhase , 6, 11); ex.SetCellValue(obj.kgCloseVol, 6, 19); ex.SetCellValue(obj.kgNumber, 8, 7); ex.SetCellValue(obj.kgOpenEle, 8, 11); ex.SetCellValue(obj.kgfgb, 8, 19); ex.ActiveSheet(1); ex.ShowExcel(); }
/// <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(); }
/// <summary> /// 文档格式预定义好的,只填写内容 /// </summary> /// <param name="obj"></param> public static void ExportExcel(IList<sdjls_xlwhjl> objlist) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\送电20线路污秽地段记录.xls"; ex.Open(fname); //此处写填充内容代码 int rowcount = 7; //加页 int pageindex = 1; if (pageindex < Ecommon.GetPagecount(objlist.Count, 11)) { pageindex = Ecommon.GetPagecount(objlist.Count, 11); } for (int j = 1; j <= pageindex; j++) { if (j > 1) { ex.CopySheet(1, 1); } ex.SetCellValue(objlist[0].LineName, 4, 2); ex.SetCellValue(objlist[0].LineVol, 4, 6); } for (int j = 1; j <= pageindex; j++) { ex.ActiveSheet(j); int prepageindex = j - 1; //主题 int starow = prepageindex * 11 + 1; int endrow = j * 11; if (objlist.Count > endrow) { for (int i = 0; i < 11; i++) { ex.SetCellValue(objlist[starow - 1 + i].whqd, rowcount + i, 1); ex.SetCellValue(objlist[starow - 1 + i].wyxz, rowcount + i, 2); ex.SetCellValue(objlist[starow - 1 + i].whdj, rowcount + i, 3); ex.SetCellValue(objlist[starow - 1 + i].xlbj, rowcount + i, 4); ex.SetCellValue(objlist[starow - 1 + i].jyzxh, rowcount + i, 5); ex.SetCellValue(objlist[starow - 1 + i].zhfs, rowcount + i, 6); ex.SetCellValue(objlist[starow - 1 + i].bz, rowcount + i, 7); } } else if (objlist.Count <= endrow && objlist.Count >= starow) { for (int i = 0; i < objlist.Count - starow + 1; i++) { ex.SetCellValue(objlist[starow - 1 + i].whqd, rowcount + i, 1); ex.SetCellValue(objlist[starow - 1 + i].wyxz, rowcount + i, 2); ex.SetCellValue(objlist[starow - 1 + i].whdj, rowcount + i, 3); ex.SetCellValue(objlist[starow - 1 + i].xlbj, rowcount + i, 4); ex.SetCellValue(objlist[starow - 1 + i].jyzxh, rowcount + i, 5); ex.SetCellValue(objlist[starow - 1 + i].zhfs, rowcount + i, 6); ex.SetCellValue(objlist[starow - 1 + i].bz, rowcount + i, 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); } }
//ExcelAccess public static void ExportExcel(PJ_01gzrj jl, IList<PJ_gzrjnr> nrList) { nrList = getRjnrList(jl.gzrjID); ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\01工作日志.xls"; ex.Open(fname); int row = 1; int col = 1; int row_nr = 9; int row_num = 36 * 2; List<string> strpy = Ecommon.ResultStrList("领导检查评语:" + jl.py, row_num); List<string> strjs = Ecommon.ResultStrList("记事:" + jl.js, row_num); int nrpage = Ecommon.GetPagecount(nrList.Count, 9); int p = Math.Max(Ecommon.GetPagecount(strpy.Count + strjs.Count, 5), nrpage); System.Collections.ArrayList objlist = new System.Collections.ArrayList(); objlist.Add(strjs); objlist.Add(strpy); List<string> allList = Ecommon.GetCollList(objlist); for (int j = 1; j <= p; j++) { if (j > 1) { ex.CopySheet(1, 1); } } for (int j = 1; j <= p; j++) { ex.ActiveSheet(j); ex.ReNameWorkSheet(j, "Sheet" + (j)); setHead(ex, jl); if (j == 1) { setHead2(ex, jl); } int prepageindex = j - 1; //主题 int starownr = prepageindex * 9 + 1; int js = prepageindex * 5 + 1; int endrownr = j * 9; int endrowjs = j * 5; int bh = 0; if (nrList.Count > endrownr) { for (int i = 0; i < 9; i++) { bh++; PJ_gzrjnr obj = nrList[starownr - 1 + i]; ex.SetCellValue(obj.seq > 0 ? obj.seq.ToString() : "", row + 9 + i, col); ex.SetCellValue(obj.gznr, row + 9 + i, col + 1); ex.SetCellValue(obj.fzr, row + 9 + i, col + 8); string[] rr = obj.cjry.Split(";".ToCharArray()); if (rr.Length > 2) { string cry = rr[0]+ rr[1] + "等" + rr.Length + "人"; ex.SetCellValue(cry, row + 9 + i, col + 11); } else ex.SetCellValue(obj.cjry, row + 9 + i, col + 11); } } else if (nrList.Count <= endrownr && nrList.Count >= starownr) { for (int i = 0; i < nrList.Count - starownr + 1; i++) { PJ_gzrjnr obj = nrList[starownr - 1 + i]; ex.SetCellValue(obj.seq > 0 ? obj.seq.ToString() : "", row + 9 + i, col); ex.SetCellValue(obj.gznr, row + 9 + i, col + 1); ex.SetCellValue(obj.fzr, row + 9 + i, col + 8); string[] rr = obj.cjry.Split(";".ToCharArray()); if (rr.Length > 2) { string cry = rr[0] + rr[1] + "等" + rr.Length + "人"; ex.SetCellValue(cry, row + 9 + i, col + 11); } else ex.SetCellValue(obj.cjry, row + 9 + i, col + 11); } } if (allList.Count > endrowjs) { for (int i = 0; i < 5; i++) { ex.SetCellValue(allList[js - 1 + i], row + 18 + i, col); } } else if (allList.Count <= endrowjs && allList.Count >= js) { for (int i = 0; i < allList.Count - js + 1; i++) { ex.SetCellValue(allList[js - 1 + i], row + 18 + i, col); } } } ex.ActiveSheet(1); ex.ShowExcel(); }
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> /// <summary> /// 绝缘子测试记录 /// </summary> /// <param name="nrList"></param> public static void ExportExcel(IList<sdjls_jyzcsjl> nrList) { #region 绝缘子测试记录 ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\送电10绝缘子测试记录.xls"; ex.Open(fname); int row = 1; //加页 int pageindex = 1; int nzglzs = 0;//耐张杆零值数 int zxglzs = 0;//直线杆零值数 List<sdjls_jyzcsjl> jyzList=(List<sdjls_jyzcsjl>)nrList; jyzList.Sort(delegate(sdjls_jyzcsjl j1, sdjls_jyzcsjl j2) { return Comparer<string>.Default.Compare(j1.gh, j2.gh); }); string startgh = jyzList[0].gh; string endgh = jyzList[jyzList.Count - 1].gh; foreach (sdjls_jyzcsjl jl in nrList) { int result = 0; int.TryParse(jl.c3, out result); if (jl.gx.Trim() == "直线杆") { zxglzs += result; } else { nzglzs += result; } } if (pageindex < Ecommon.GetPagecount(nrList.Count, 12)) { pageindex = Ecommon.GetPagecount(nrList.Count, 12); } for (int j = 1; j <= pageindex; j++) { ex.ActiveSheet(1); if (j > 1) { ex.CopySheet(1, 1); } ex.SetCellValue(nrList[0].LineName,3,5); } for (int j = 1; j <= pageindex; j++) { ex.ActiveSheet(j); //ex.ReNameWorkSheet(j, "Sheet" + (j)); int prepageindex = j - 1; //主题 int starow = prepageindex * 12 + 1; int endrow = j * 12; if (nrList.Count > endrow) { for (int i = 0; i < 12; i++) { //ex.SetCellValue(nrList[starow - 1 + i].jcrq.Year.ToString(), row + 5 + i, 1); ex.SetCellValue(nrList[starow - 1 + i].cssj.Year.ToString(), row + 5 + i, 1); ex.SetCellValue(nrList[starow - 1 + i].cssj.Month.ToString(), row + 5 + i, 2); ex.SetCellValue(nrList[starow - 1 + i].cssj.Day.ToString(), row + 5 + i, 3); ex.SetCellValue(nrList[starow - 1 + i].gh, row + 5 + i, 4); ex.SetCellValue(nrList[starow - 1 + i].gx, row + 5 + i, 5); ex.SetCellValue(nrList[starow - 1 + i].jyzxh, row + 5 + i, 6); ex.SetCellValue(nrList[starow - 1 + i].lzjyzwz, row + 5 + i, 7); ex.SetCellValue(nrList[starow - 1 + i].syfzr, row + 5 + i, 8); ex.SetCellValue(nrList[starow - 1 + i].c1, row + 5 + i, 9); ex.SetCellValue(nrList[starow - 1 + i].clsj.Year.ToString(), row + 5 + i, 10); ex.SetCellValue(nrList[starow - 1 + i].clsj.Month.ToString(), row + 5 + i, 11); ex.SetCellValue(nrList[starow - 1 + i].clsj.Day.ToString(), row + 5 + i, 12); ex.SetCellValue(nrList[starow - 1 + i].bz, row + 5 + i, 13); } } else if (nrList.Count <= endrow && nrList.Count >= starow) { ex.SetCellValue("#"+startgh, 19, 6); ex.SetCellValue("#~" + endgh, 19, 7); ex.SetCellValue(nrList.Count.ToString(), 19, 9); ex.SetCellValue(nzglzs.ToString()+"片;", 20, 6); ex.SetCellValue(zxglzs.ToString(), 20, 10); for (int i = 0; i < nrList.Count - starow + 1; i++) { ex.SetCellValue(nrList[starow - 1 + i].cssj.Year.ToString(), row + 5 + i, 1); ex.SetCellValue(nrList[starow - 1 + i].cssj.Month.ToString(), row + 5 + i, 2); ex.SetCellValue(nrList[starow - 1 + i].cssj.Day.ToString(), row + 5 + i, 3); ex.SetCellValue(nrList[starow - 1 + i].gh, row + 5 + i, 4); ex.SetCellValue(nrList[starow - 1 + i].gx, row + 5 + i, 5); ex.SetCellValue(nrList[starow - 1 + i].jyzxh, row + 5 + i, 6); ex.SetCellValue(nrList[starow - 1 + i].lzjyzwz, row + 5 + i, 7); ex.SetCellValue(nrList[starow - 1 + i].syfzr, row + 5 + i, 8); ex.SetCellValue(nrList[starow - 1 + i].c1, row + 5 + i, 9); ex.SetCellValue(nrList[starow - 1 + i].clsj.Year.ToString(), row + 5 + i, 10); ex.SetCellValue(nrList[starow - 1 + i].clsj.Month.ToString(), row + 5 + i, 11); ex.SetCellValue(nrList[starow - 1 + i].clsj.Day.ToString(), row + 5 + i, 12); ex.SetCellValue(nrList[starow - 1 + i].bz, row + 5 + i, 13); } } } ex.ActiveSheet(1); ex.ShowExcel(); #endregion }
/// <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(); }
public void ExportExcel(ExcelAccess ex, IList<PJ_jsbpjjh> datalist) { //此处写填充内容代码 int row = 4; int col = 1; int rowcount = 4; // //加页 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)); ex.SetCellValue(MainHelper.UserCompany + DateTime.Now.ToString("yyyy年") + "设备评级计划表", 1, 1); ex.SetCellValue(DateTime.Now.ToString("yyyy年MM月dd日") , 2,7); } ex.SetCellValue((j + 1).ToString(), row + j % rowcount, col); ex.SetCellValue(datalist[j].gzxm, row + j % rowcount, col + 1); ex.SetCellValue(datalist[j].wcsj.ToString("yyyy年MM月dd日"), row + j % rowcount, col + 2); ex.SetCellValue(datalist[j].lsr, row + j % rowcount, col +3); ex.SetCellValue(datalist[j].dbr, row + j % rowcount, col + 4); ex.SetCellValue(datalist[j].lsyq, row + j % rowcount, col + 5); ex.SetCellValue(datalist[j].Remark, row + j % rowcount, col +6); } }
/// <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 void ExportExcel(ExcelAccess ex ,IList<PJ_anqgjcrkd> datalist) { //此处写填充内容代码 int row = 5; int col = 1; int rowcount = 41; // //加页 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, datalist[0].num); } else ex.ReNameWorkSheet(j + 1, datalist[0].num+"(" + (j)+")"); } for (int j = 0; j < datalist.Count; j++) { if (j % rowcount == 0) { if (j == 0) ex.ActiveSheet( datalist[0].num); else ex.ActiveSheet( datalist[0].num + "(" + (j / rowcount + 1)+")"); ex.SetCellValue(datalist[j].indate.ToString("yyyy年MM月dd日"), 3, 2); ex.SetCellValue(datalist[j].indate.ToString("yyyy年MM月dd日"), 3, 6); } ex.SetCellValue((j+1).ToString(), row + j % rowcount, col); ex.SetCellValue(datalist[j].wpmc, row + j % rowcount, col+1 ); ex.SetCellValue(datalist[j].wpgg, row + j % rowcount, col + 3); ex.SetCellValue(datalist[j].wpdw, row + j % rowcount, col + 4); ex.SetCellValue(datalist[j].wpsl, row + j % rowcount, col + 5); ex.SetCellValue(datalist[j].wpdj, row + j % rowcount, col + 6); ex.SetCellValue((Convert.ToDouble(datalist[j].wpsl)*Convert.ToDouble(datalist[j].wpdj)).ToString() , row + j % rowcount, col + 7); //ex.SetCellValue(datalist[j].zrr, row + j % rowcount, col + 7); } }
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); } }
public void ExportExcel(ExcelAccess ex ,IList<PJ_clcrkd> datalist) { //此处写填充内容代码 int row = 4; int col = 1; int rowcount = 11; 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, 5); ex.SetCellValue(datalist[j].OrgName , 2, 2); } ex.SetCellValue(datalist[j].wpmc, row + j % rowcount, col ); ex.SetCellValue(datalist[j].wpgg, row + j % rowcount, col +1); ex.SetCellValue(datalist[j].wpdw, row + j % rowcount, col + 2); ex.SetCellValue(datalist[j].wpsl, row + j % rowcount, col + 3); ex.SetCellValue(datalist[j].indate.ToString("yyyy年MM月dd日"), 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="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 ExportExcel(ExcelAccess ex, IList<PJ_clcrkd> datalist, string wpmc) { //此处写填充内容代码 int row = 5; int col = 2; int rowcount = 18; // //加页 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, wpmc); else ex.ReNameWorkSheet(j + 1, wpmc + (j)); } for (int j = 0; j < datalist.Count; j++) { if (j % rowcount == 0) { if (j == 0) ex.ActiveSheet(wpmc); else ex.ActiveSheet(wpmc + (j / rowcount + 1)); } ex.SetCellValue(datalist[j].wpmc, row + j % rowcount, col ); ex.SetCellValue(datalist[j].wpgg, row + j % rowcount, col + 1); ex.SetCellValue(datalist[j].wpdw, row + j % rowcount, col + 2); ex.SetCellValue(datalist[j].wpsl, row + j % rowcount, col + 3); ex.SetCellValue(datalist[j].wpdj, row + j % rowcount, col + 4); ex.SetCellValue(datalist[j].wpcj, row + j % rowcount, col + 5); ex.SetCellValue(datalist[j].indate.ToString("yyyy年MM月dd日"), row + j % rowcount, col + 6); ex.SetCellValue(datalist[j].ssgc, row + j % rowcount, col + 7); if(datalist[j].type.IndexOf ("入库单")==-1) ex.SetCellValue(datalist[j].ckdate.ToString("yyyy年MM月dd日"), row + j % rowcount, col + 8); ex.SetCellValue(datalist[j].yt, row + j % rowcount, col + 9); ex.SetCellValue(datalist[j].cksl, row + j % rowcount, col + 10); ex.SetCellValue(datalist[j].cksl, row + j % rowcount, col + 11); ex.SetCellValue(datalist[j].lqdw, row + j % rowcount, col + 12); //ex.SetCellValue(datalist[j].zrr, row + j % rowcount, col + 7); if (datalist[j].wpmc == "出库合计") { return; } } }
/// <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(); }
/// <summary> /// 文档格式预定义好的,只填写内容(缺陷内容为空时,不填写消除人) /// </summary> /// <param name="obj"></param> public static void ExportExcel(IList<sdjl_06sbxs> objlist,bool xsmxflag) { //lgm ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\送电07设备巡视及缺陷消除记录.xls"; ex.Open(fname); //每行显示文字长度 int zc = 20; //与会人员之间的间隔符号 char[] jksign = new char[1] { ';' }; int row = 7; int col = 1; int len1 = 3; ex.SetCellValue(objlist[0].LineName, 4, 4); ex.SetCellValue(objlist[0].xlqd, 4, 8); int pagecout = Ecommon.GetPagecount(objlist.Count, 9); //复制空模板 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; } sdjl_06sbxs tempobj = objlist[p * 9 + i]; //巡视时间 if (xsmxflag) { ex.SetCellValue(tempobj.xssj.Year.ToString(), row + i * len1, 1); ex.SetCellValue(tempobj.xssj.Month.ToString(), row + i * len1, 2); ex.SetCellValue(tempobj.xssj.Day.ToString(), row + i * len1, 3); } //缺陷内容 List<string> tempstr = Ecommon.ResultStrList(tempobj.qxnr, zc); if (tempstr.Count >= 1) { ex.SetCellValue(tempstr[0], row + i * len1, 4); } else { ex.SetCellValue("", row + i * len1, 4); } if (tempstr.Count >= 2) { ex.SetCellValue(tempstr[1], row + i * len1 + 1, 4); } else { ex.SetCellValue("", row + i * len1 + 1, 4); } //缺陷类别 ex.SetCellValue(tempobj.qxlb, row + i * len1, 7); //巡视人 string[] ary = tempobj.xsr.Split(jksign); if (ary.Length >= 1) { ex.SetCellValue(ary[0], row + 2 + i * len1, 5); } else { ex.SetCellValue("", row + 2 + i * len1, 5); } if (ary.Length >= 2) { ex.SetCellValue(ary[1], row + 2 + i * len1, 6); } else { ex.SetCellValue("", row + 2 + i * len1, 6); } //消除人 string[] ary2 = tempobj.xcr.Split(jksign); if (ary2.Length >= 1 && tempstr.Count > 0) { ex.SetCellValue(ary2[0], row + i * len1, 9); } else { ex.SetCellValue("", row + i * len1, 9); } if (ary2.Length >= 2&&tempstr.Count>0) { ex.SetCellValue(ary2[1], row + i * len1, 12); } else { ex.SetCellValue("", row + i * len1, 12); } //消除时间 if (ComboBoxHelper.CompreDate(tempobj.xcrq) && tempstr.Count>0) { ex.SetCellValue(tempobj.xcrq.Year.ToString(), row + 2 + i * len1, 8); ex.SetCellValue(tempobj.xcrq.Month.ToString(), row + 2 + i * len1, 11); ex.SetCellValue(tempobj.xcrq.Day.ToString(), row + 2 + i * len1, 13); } } } ex.ActiveSheet(1); ex.ShowExcel(); }