protected void Workbook_SheetActivate(object Sh) { Excel.Workbook wb = dsoFramerWordControl1.AxFramerControl.ActiveDocument as Excel.Workbook; ExcelAccess ea = new ExcelAccess(); ea.MyWorkBook = wb; ea.MyExcel = wb.Application; Excel.Worksheet sheet; sheet = wb.ActiveSheet as Excel.Worksheet; if (activeSheetName != sheet.Name) { sheet.Name = activeSheetName; } if (wb.Application.Sheets[activeSheetIndex] != wb.Application.Sheets[activeSheetName]) { sheet = wb.Application.Sheets[activeSheetName] as Excel.Worksheet; sheet.Move(Type.Missing, wb.Application.Sheets[activeSheetIndex]); } ea.ActiveSheet(activeSheetName); }
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(); }
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++); } }
/// <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 ctrl_Enter(object sender, EventArgs e) { LP_Temple lp = (LP_Temple)(sender as Control).Tag; if (lp.CellPos == "") return; string str = (sender as Control).Text; if (dsoFramerWordControl1.MyExcel == null) { return; } ExcelAccess ea = new ExcelAccess(); ea.MyWorkBook = wb; ea.MyExcel = wb.Application; if (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; } ea.ActiveSheet(activeSheetIndex); } unLockExcel(wb, xx); string[] arrCellpos = lp.CellPos.Split(pchar); arrCellpos = StringHelper.ReplaceEmpty(arrCellpos).Split(pchar); if (arrCellpos.Length >= 1) { //ea.SetCellValue(str, GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]); Excel.Range range = (Excel.Range)xx.get_Range(xx.Cells[GetCellPos(arrCellpos[0])[0], GetCellPos(arrCellpos[0])[1]], xx.Cells[GetCellPos(arrCellpos[0])[0], GetCellPos(arrCellpos[0])[1]]); range.Select(); bool isfind = false; for (int i = 1; i <= xx.Protection.AllowEditRanges.Count; i++) { Excel.AllowEditRange editRange = xx.Protection.AllowEditRanges.get_Item(i); if (editRange.Title == lp.CellPos.Replace("|", "")) { isfind = true; break; } } if (!isfind) { xx.Protection.AllowEditRanges.Add(lp.CellPos.Replace("|", ""), range, Type.Missing); } } LockExcel(wb, xx); }
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> /// <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 }
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; } } }
/// <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_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_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_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); } }
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(); }
public static void ExportExcelWorkFlow(ref LP_Record currRecord, PJ_03yxfx obj) { DSOFramerControl dsoFramerWordControl1 = new DSOFramerControl(); string fname = Application.StartupPath + "\\00记录模板\\03运行分析记录.xls"; dsoFramerWordControl1.FileOpen(fname); if (currRecord == null) { currRecord = new LP_Record(); currRecord.Status = "文档生成"; } currRecord.DocContent = dsoFramerWordControl1.FileDataGzip; dsoFramerWordControl1.FileSave(); dsoFramerWordControl1.FileClose(); dsoFramerWordControl1.FileDataGzip = currRecord.DocContent; ExcelAccess ex = new ExcelAccess(); Excel.Workbook wb = dsoFramerWordControl1.AxFramerControl.ActiveDocument as Excel.Workbook; ex.MyWorkBook = wb; ex.MyExcel = wb.Application; //与会人员之间的间隔符号 string jksign = "@"; int row = 1; int col = 1; //每行显示文字长度 int zc = 60; //获得创建的工作表个数 // int pagemax = 1; string zt = Ecommon.Comparestring(obj.zt, "主题") ? "" : "主题:"; List<string> listztstring = Ecommon.ResultStrList(zt + obj.zt, zc); Ecommon.Resultstrbystartbd(ref listztstring); //if (pagemax < Ecommon.GetPagecount(listztstring.Count,2)) //{ // pagemax = Ecommon.GetPagecount(listztstring.Count, 2); //} string jy = Ecommon.Comparestring(obj.jy, "纪要") ? "" : "纪要:"; List<string> listjy = Ecommon.ResultStrList(jy + obj.jy, zc); Ecommon.Resultstrbystartbd(ref listjy); //if (pagemax < Ecommon.GetPagecount(listjy.Count, 7)) //{ // pagemax = Ecommon.GetPagecount(listjy.Count, 7); //} string jr = Ecommon.Comparestring(obj.jr, "结论及对策") ? "" : "结论及对策:"; List<string> listjldc = Ecommon.ResultStrList(jr + obj.jr, zc); Ecommon.Resultstrbystartbd(ref listjldc); //if (pagemax < Ecommon.GetPagecount(listjldc.Count, 6)) //{ // pagemax = Ecommon.GetPagecount(listjldc.Count, 6); //} List<string> listpy = Ecommon.ResultStrList("领导检查评语:" + obj.py, zc); Ecommon.Resultstrbystartbd(ref listpy); int[] strnumcol = { 0, listztstring.Count, listztstring.Count + listjy.Count, listztstring.Count + listjy.Count + listjldc.Count }; int[] statnum = { 3, 3, 6,7 }; List<string> strcol = new List<string>(); Ecommon.addstring(listztstring, ref strcol); Ecommon.addstring(listjy, ref strcol); Ecommon.addstring(listjldc, ref strcol); Ecommon.addstring(listpy, ref strcol); //Ecommon.CreatandWritesheet(ex, strcol, 15, 9, 1); Ecommon.CreatandWritesheet1(ex, strcol, 15, 9, 1, strnumcol, statnum); //进行加粗 ex.ActiveSheet(1); //时间 ex.SetCellValue(obj.rq.Year.ToString(), 4, 5); ex.SetCellValue(obj.rq.Month.ToString(), 4, 7); ex.SetCellValue(obj.rq.Day.ToString(), 4, 9); ////出席人员 string[] ary = obj.cjry.Split(';'); int n = ary.Length % 5; for (int i = 0; i < ary.Length; i++) { int tempcol = col + 1 + i % 5; if (i % 5 == 1 || i % 5 == 2 || i % 5 == 3) { tempcol = col + 1 + i % 5 + 1; } if (i % 5 == 4) { tempcol = col + 1 + i % 5 + 2; } ex.SetCellValue(ary[i], row + 4 + i / 5, tempcol); } //主持人 ex.SetCellValue(obj.zcr, 5, 11); //检查人签字 //ex.CopySheet(1, 2); //ex.ActiveSheet(2); //ex.SetCellValue("kakaka", 24, 4); //ex.ActiveSheet(1); ex.SetCellValue(obj.qz, 24, 4); //签字时间 // if (ComboBoxHelper.CompreDate(obj.qzrq)) // { // ex.SetCellValue(obj.qzrq.Year.ToString(), 24, 5); // ex.SetCellValue(obj.qzrq.Month.ToString(), 24, 9); // ex.SetCellValue(obj.qzrq.Day.ToString(), 24, 11); // } //将固定的写完再创建不固定的。 //添加变动内容 //for (int j = 1; j <= pagemax;j++ ) //{ // if (j>1) // { // ex.CopySheet(1, 1); // } //} //ex.ShowExcel(); //for (int j = 1; j <= pagemax;j++ ) //{ // ex.ActiveSheet(j); // //if (j>1) // //{ // // // ex.CopySheet(1, j); // // ex.ActiveSheet(j); // //} // //ex.ShowExcel(); // int prepageindex = j - 1; // //主题 // int starow = prepageindex * 2 + 1; // int endrow = j * 2; // if (listztstring.Count>endrow) // { // for (int i = 0; i < 2;i++ ) // { // ex.SetCellValue(listztstring[starow - 1 + i], 9 + i, 1); // } // } // else if (listztstring.Count<=endrow&&listztstring.Count>=starow) // { // for (int i = 0; i < listztstring.Count - starow + 1;i++ ) // { // ex.SetCellValue(listztstring[starow - 1 + i], 9 + i, 1); // } // } // //纪要 // starow = prepageindex * 7 + 1; // endrow = j * 7; // if (listjy.Count > endrow) // { // for (int i = 0; i < 7; i++) // { // ex.SetCellValue(listjy[starow - 1 + i], 11 + i, 1); // } // } // else if (listjy.Count <= endrow && listjy.Count >= starow) // { // for (int i = 0; i < listjy.Count - starow + 1; i++) // { // ex.SetCellValue(listjy[starow - 1 + i], 11+ i, 1); // } // } // //结论及对策 // starow = prepageindex * 6 + 1; // endrow = j * 6; // if (listjldc.Count > endrow) // { // for (int i = 0; i < 6; i++) // { // ex.SetCellValue(listjldc[starow - 1 + i], 18 + i, 1); // } // } // else if (listjldc.Count <= endrow && listjldc.Count >= starow) // { // for (int i = 0; i < listjldc.Count - starow + 1; i++) // { // ex.SetCellValue(listjldc[starow - 1 + i], 18 + i, 1); // } // } //} ////主题: //ex.SetCellValue("主题: " + obj.zt, 9, 1); ////纪要 //string jystr = "纪要:" + obj.jy; //for (int i = 0; i < 7; i++) //{ // string tempstr = ""; // int startnum = i * zc; // int endnum = (i + 1) * zc; // bool ISempty=false; // if (startnum>=jystr.Length) // { // ISempty=true; // } // else if (endnum >= jystr.Length) // { // endnum = jystr.Length; // } // if (!ISempty) // { // tempstr = jystr.Substring(startnum,endnum-startnum); // } // ex.SetCellValue(tempstr, 11 + i, 1); //} ////结论及对策: //string jlstr = "结论及对策:" + obj.jr; //for (int j = 0; j < 6; j++) //{ // string tempstr = ""; // int startnum = j * zc; // int endnum = (j + 1) * zc; // bool ISempty = false; // if (startnum >= jlstr.Length) // { // ISempty = true; // } // else if (endnum >= jlstr.Length) // { // endnum = jlstr.Length; // } // if (!ISempty) // { // tempstr = jlstr.Substring(startnum, endnum - startnum); // } // ex.SetCellValue(tempstr, 18 + j, 1); //} //ex.CopySheet(1,2); //ex.CopySheet(1, 3); //ex.CopySheet(2, 4); if (currRecord == null) { currRecord = new LP_Record(); currRecord.Status = "文档生成"; } dsoFramerWordControl1.FileSave(); currRecord.DocContent = dsoFramerWordControl1.FileDataGzip; dsoFramerWordControl1.FileSave(); dsoFramerWordControl1.FileClose(); }
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; } } }
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); } }
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> /// 设置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); } }
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(); }
/// <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(); }
/// <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_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_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(); }
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; }
/// <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(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> /// 文档格式预定义好的,只填写内容 /// </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 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(); }