C# ExcelAccess.SetCellValue方法代码示例

 /// <summary>
 /// 文档格式预定义好的，只填写内容
 /// </summary>
 /// <param name="obj"></param>
 public static void ExportExcel(sdjl_26 obj) {
     ExcelAccess ex = new ExcelAccess();
     SaveFileDialog saveFileDialog1 = new SaveFileDialog();
     string fname = Application.StartupPath + "\\00记录模板\\送电17防护通知书.xls";
     int sz = 30;//一行最多30个
     ex.Open(fname);
     //此处写填充内容代码
     ex.ActiveSheet(1);
     //通知单位
     ex.SetCellValue(obj.tzdw, 5, 1);
     //电压
     ex.SetCellValue(obj.lineVol, 6, 7);
     //线路
     ex.SetCellValue(obj.c1, 6, 9);
     //发现问题
     List<string> fxwtList = GetList(obj.fxwt, sz);
     for (int i = 0; i < fxwtList.Count; i++)
     {
         ex.SetCellValue(fxwtList[i], 7+i, 1);
         if (i > 1)
             break;
     }
     //处理措施
     List<string> clcsList = GetList(obj.clcs, sz);
     for (int i = 0; i < clcsList.Count; i++)
     {
         ex.SetCellValue(obj.clcs, 12+i, 1);
         if (i > 2)
             break;
     }
    
    ex.ShowExcel();
 }

 void gridViewOperation_AfterAdd(PJ_24 e)
 {
     DSOFramerControl dsoFramerControl1 = new DSOFramerControl();
     Microsoft.Office.Interop.Excel.Workbook wb;dsoFramerControl1.FileData = e.BigData;
     wb = dsoFramerControl1.AxFramerControl.ActiveDocument as Microsoft.Office.Interop.Excel.Workbook;
     ExcelAccess ea = new ExcelAccess();
     ea.MyWorkBook = wb;
     ea.MyExcel = wb.Application;
     ea.SetCellValue(ParentObj.OrgName , 4, 9);
     dsoFramerControl1.FileSave();
     e.BigData = dsoFramerControl1.FileData;
     dsoFramerControl1.FileClose();
     dsoFramerControl1.Dispose();
     dsoFramerControl1 = null;
 }

        /// <summary>
        /// 文档格式预定义好的，只填写内容
        /// </summary>
        /// <param name="obj"></param>
        public static void ExportExcel(PJ_24 jl)
        {
            ExcelAccess ex = new ExcelAccess();
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            string fname = Application.StartupPath + "\\00记录模板\\24设备变更通知书.xls";

            ex.Open(fname);
            int row = 1;
            int col = 1;
            //
            ex.SetCellValue(jl.sj.Year.ToString(), row + 8, col);
            ex.SetCellValue(jl.sj.Month.ToString(), row + 8, col+2);
            ex.SetCellValue(jl.sj.Day.ToString(), row + 8, col+4);
            ex.SetCellValue(jl.dd, row + 5, col+6);
            ex.SetCellValue(jl.nr, row + 5, col + 7);
            ex.SetCellValue(jl.Remark, row + 5, col + 10);

            ex.ShowExcel();
        }

 private void btnOK_Click(object sender, EventArgs e)
 {
     if (rowData.BigData.Length == 0)
     {
         mOrg org = MainHelper.PlatformSqlMap.GetOneByKey<mOrg>(rowData.ParentID);
         string fname = Application.StartupPath + "\\00记录模板\\23配电线路产权维护范围协议书.xls";
         string bhname = org.OrgName.Replace("供电所", "");
         DSOFramerControl dsoFramerControl1 = new DSOFramerControl();
         dsoFramerControl1.FileOpen(fname);
         Microsoft.Office.Interop.Excel.Workbook wb = dsoFramerControl1.AxFramerControl.ActiveDocument as Microsoft.Office.Interop.Excel.Workbook;
         //PJ_23 obj = (PJ_23)MainHelper.PlatformSqlMap.GetObject("SelectPJ_23List", "where ParentID='" + rowData.ParentID + "' and xybh like '" + SelectorHelper.GetPysm(org.OrgName.Replace("供电所", ""), true) + "" + DateTime.Now.Year.ToString() + "%' order by xybh ASC");
         //int icount = 1;
         //if (obj != null && obj.xybh !="")
         //{
         //    icount = Convert.ToInt32(obj.xybh.Split('-')[2])+1;
         //}
         //string strname = SelectorHelper.GetPysm(bhname, true);
         ExcelAccess ea = new ExcelAccess();
         ea.MyWorkBook = wb;
         ea.MyExcel = wb.Application;
         ea.SetCellValue(rowData.xybh.ToUpper(), 4, 8);
         //strname = DateTime.Now.Year.ToString();
         //ea.SetCellValue(strname, 4, 9);
         //strname = string.Format("{0:D3}", icount);
         //ea.SetCellValue(strname, 4, 10);
         ea.SetCellValue(rowData.linename, 10, 7);
         ea.SetCellValue(rowData.fzlinename, 10, 10);
         ea.SetCellValue("'" + rowData.gh, 10, 16);
         ea.SetCellValue(rowData.cqfw, 11, 4);
         ea.SetCellValue(rowData.cqdw, 13, 4);
         ea.SetCellValue(rowData.qdrq.Year.ToString(), 21, 7);
         ea.SetCellValue(rowData.qdrq.Month.ToString(), 21, 9);
         ea.SetCellValue(rowData.qdrq.Day.ToString(), 21, 11);
         dsoFramerControl1.FileSave();
         rowData.BigData = dsoFramerControl1.FileData;
         dsoFramerControl1.FileClose();
         dsoFramerControl1.Dispose();
         dsoFramerControl1 = null;
         //rowData.xybh = SelectorHelper.GetPysm(bhname, true).ToUpper() + "-" + DateTime.Now.Year.ToString() + "-" + string.Format("{0:D3}", icount);
     }
     DSOFramerControl dsoFramerControl2 = new DSOFramerControl();
     dsoFramerControl2.FileData = rowData.BigData;
     Microsoft.Office.Interop.Excel.Workbook wb2 = dsoFramerControl2.AxFramerControl.ActiveDocument as Microsoft.Office.Interop.Excel.Workbook;
     ExcelAccess ea2 = new ExcelAccess();
     ea2.MyWorkBook = wb2;
     ea2.MyExcel = wb2.Application;
     ea2.SetCellValue(comboBoxEdit1.Text, 11, 4);
     dsoFramerControl2.FileSave();
     rowData.BigData = dsoFramerControl2.FileData;
     dsoFramerControl2.FileClose();
     dsoFramerControl2.Dispose();
     dsoFramerControl2 = null;
 }

        /// <summary>
        ///创建工作表并将数据加入到工作表中
        /// </summary>
        /// <param name="ex">操作EXCEL表</param>
        /// <param name="bdzlist">填入的数据</param>
        ///  <param name="hs">一页对应的总行数</param>
        ///  <param name="stawz">一页中填入的开始的位置</param>
        ///  <param name="pageindex">一页填入的列位置</param>
        ///  <param name="strnumcol">第一个字符开始的行数</param>
        ///  <param name="boldnum">加粗的数量</param>
        public static void CreatandWritesheet1(ExcelAccess ex, List<string> bdzlist, int hs, int star, int clm,int[] strnumcol,int[] boldnum)
        {
            int pageindex = 1;
            if (pageindex < Ecommonjh.GetPagecount(bdzlist.Count, hs))
            {
                pageindex = Ecommonjh.GetPagecount(bdzlist.Count, hs);
            }
            for (int j = 1; j <= pageindex; j++)
            {
                if (j > 1)
                {
                    ex.CopySheet(1, 1);

                }
            }
            ex.ShowExcel();
            for (int j = 1; j <= pageindex; j++)
            {

                ex.ActiveSheet(j);
                ex.ReNameWorkSheet(j, "Sheet" + (j));
                int prepageindex = j - 1;
                //主题
                int starow = prepageindex * hs + 1;
                int endrow = j * hs;
                if (bdzlist.Count > endrow)
                {
                    for (int i = 0; i < hs; i++)
                    {

                        ex.SetCellValue(bdzlist[starow - 1 + i], star + i, clm);
                        //加粗过程
                        for (int n = 0; n < strnumcol.Length;n++ )
                        {
                            if (starow-1+i==strnumcol[n])
                            {
                                ex.SetFontBold(star + i, clm, star + i, clm, true, 0, boldnum[n]);
                            }
                          
                        }

                        //ex.SetCellValue(objlist[starow - 1 + i].rq.Month.ToString(), rowcount + i, 1);
                        //ex.SetCellValue(objlist[starow - 1 + i].rq.Day.ToString(), rowcount + i, 2);
                        //ex.SetCellValue(objlist[starow - 1 + i].rq.Hour.ToString(), rowcount + i, 3);
                        //ex.SetCellValue(objlist[starow - 1 + i].rq.Minute.ToString(), rowcount + i, 4);
                        //ex.SetCellValue(objlist[starow - 1 + i].lxfs, rowcount + i, 5);
                        //ex.SetCellValue(objlist[starow - 1 + i].yhdz, rowcount + i, 6);
                        //ex.SetCellValue(objlist[starow - 1 + i].gzjk, rowcount + i, 7);
                        //ex.SetCellValue(objlist[starow - 1 + i].djr, rowcount + i, 8);
                        //ex.SetCellValue(objlist[starow - 1 + i].clr, rowcount + i, 9);

                    }
                }
                else if (bdzlist.Count <= endrow && bdzlist.Count >= starow)
                {
                    for (int i = 0; i < bdzlist.Count - starow + 1; i++)
                    {
                        ex.SetCellValue(bdzlist[starow - 1 + i], star + i, clm);
                        //加粗过程
                        for (int n = 0; n < strnumcol.Length; n++)
                        {
                            if (starow - 1 + i == strnumcol[n])
                            {
                                ex.SetFontBold(star + i, clm, star + i, clm, true, 0, boldnum[n]);
                            }

                        }

                        //ex.SetCellValue(objlist[starow - 1 + i].rq.Month.ToString(), rowcount + i, 1);
                        //ex.SetCellValue(objlist[starow - 1 + i].rq.Day.ToString(), rowcount + i, 2);
                        //ex.SetCellValue(objlist[starow - 1 + i].rq.Hour.ToString(), rowcount + i, 3);
                        //ex.SetCellValue(objlist[starow - 1 + i].rq.Minute.ToString(), rowcount + i, 4);
                        //ex.SetCellValue(objlist[starow - 1 + i].lxfs, rowcount + i, 5);
                        //ex.SetCellValue(objlist[starow - 1 + i].yhdz, rowcount + i, 6);
                        //ex.SetCellValue(objlist[starow - 1 + i].gzjk, rowcount + i, 7);
                        //ex.SetCellValue(objlist[starow - 1 + i].djr, rowcount + i, 8);
                        //ex.SetCellValue(objlist[starow - 1 + i].clr, rowcount + i, 9);

                    }
                }
            }
        }

 private void btnOK_Click(object sender, EventArgs e)
 {
     //if (comboBoxEdit1.Text == "")
     //{
     //    MsgBox.ShowTipMessageBox("变动地点不能为空。");
     //    comboBoxEdit1.Focus();
     //    return;
     //}
    
     ////if (rowData.BigData == null)
     ////{
     ////    rowData.BigData = new byte[0];
     ////}
     //this.DialogResult = DialogResult.OK;
     //this.Close();
     IList<PJ_24> list = Client.ClientHelper.PlatformSqlMap.GetList<PJ_24>("where ParentID='" + rowData.ParentID + "'");
     string bh = (list.Count + 1).ToString("000");
     DSOFramerControl dsoFramerControl1 = new DSOFramerControl();
     Microsoft.Office.Interop.Excel.Workbook wb;
     if (rowData.BigData == null || rowData.BigData.Length == 0)
     {
         string fname = Application.StartupPath + "\\00记录模板\\24设备变更通知书.xls";
         dsoFramerControl1.FileOpen(fname);
     }
     else
         dsoFramerControl1.FileData = rowData.BigData;
     wb = dsoFramerControl1.AxFramerControl.ActiveDocument as Microsoft.Office.Interop.Excel.Workbook;
     ExcelAccess ea = new ExcelAccess();
     ea.MyWorkBook = wb;
     ea.MyExcel = wb.Application;
     DateTime dt = Convert.ToDateTime(dateEdit1.EditValue);
     ea.SetCellValue(dt.Year.ToString(), 4, 7);
     ea.SetCellValue(bh, 4, 10);
     ea.SetCellValue(dt.Year.ToString(), 9, 1);
     ea.SetCellValue(dt.Month.ToString(), 9, 3);
     ea.SetCellValue(dt.Day.ToString(), 9, 5);
     ea.SetCellValue(comboBoxEdit1.Text, 6, 7);
     ea.SetCellValue(memoEdit1.Text, 6, 8);
     ea.SetCellValue(memoEdit2.Text, 6, 11);
     dsoFramerControl1.FileSave();
     rowData.BigData = dsoFramerControl1.FileData;
     dsoFramerControl1.FileClose();
     dsoFramerControl1.Dispose();
     dsoFramerControl1 = null;
 }

        public void ExportExcel(ExcelAccess ex, IList<PJ_sdytz> datalist)
        {
            //此处写填充内容代码
            int row = 5;
            int col = 1;
            int rowcount =14;

            //

            //加页
            int pageindex = 1;
            if (pageindex < Ecommon.GetPagecount(datalist.Count, rowcount))
            {
                pageindex = Ecommon.GetPagecount(datalist.Count, rowcount);
            }
            for (int j = 1; j <= pageindex; j++)
            {
                if (j > 1)
                {
                    ex.CopySheet(1, 1);
                }
            }
            for (int j = 0; j < datalist.Count; j++)
            {
                if (j % rowcount == 0)
                {
                    ex.ActiveSheet(j / rowcount + 1);
                }
                ex.SetCellValue(((j + 1)).ToString(), row + j % rowcount, col);

                ex.SetCellValue(datalist[j].khmc, row + j % rowcount, col + 1);
                ex.SetCellValue(datalist[j].khdz, row + j % rowcount, col + 2);
                ex.SetCellValue(datalist[j].zdyOrgName, row + j % rowcount, col + 3);
                ex.SetCellValue(datalist[j].zdyLineName, row + j % rowcount, col + 4);
                ex.SetCellValue(datalist[j].zdykgModle, row + j % rowcount, col + 5);
                ex.SetCellValue(datalist[j].fdyOrgName, row + j % rowcount, col + 6);
                ex.SetCellValue(datalist[j].fdyLineName, row + j % rowcount, col + 7);
                ex.SetCellValue(datalist[j].fdykgModle, row + j % rowcount, col + 8);
                ex.SetCellValue(datalist[j].kgfs, row + j % rowcount, col + 9);
                ex.SetCellValue(datalist[j].Remark, row + j % rowcount, col + 10);

            }
           
        }

        /// <summary>
        /// 设置密封数据
        /// </summary>
        /// <param name="ex"></param>
        /// <param name="list"></param>
        /// <param name="modmflist"></param>
        /// <param name="jstrfilter"></param>
        /// <param name="strfilter"></param>
        /// <param name="jstart"></param>
        /// <param name="j"></param>
        /// <param name="jmax"></param>
        /// <param name="istart2"></param>
        /// <param name="imf"></param>
        public static void setExcelmfdt(ExcelAccess ex, IList list,   IList modmflist, string jstrfilter, string strfilter, int jstart, int j, int jmax, int istart2,int imf)
        {

            //string str = "select   b.byqCapcity from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID  " + strfilter + " and b.byqModle='" + modmflist[imf] + "-'+cast(b.byqCapcity as nvarchar(50))+'/'+ cast(b.byqVol as nvarchar(50))  and (b.omniseal='true' )" + jstrfilter;
            string str = "select   b.byqCapcity from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID  " + strfilter + " and b.byqModle like '" + modmflist[imf] + "%'  and (b.omniseal='true' )" + jstrfilter;
            list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", str);
            if (list.Count > 0) ex.SetCellValue(list.Count.ToString(), istart2 + (imf % 3) * 2, jstart + j % jmax);
            //str = "select  sum( b.byqCapcity) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID  " + strfilter + " and b.byqModle='" + modmflist[imf] + "-'+cast(b.byqCapcity as nvarchar(50))+'/'+ cast(b.byqVol as nvarchar(50)) and (b.omniseal='true' )" + jstrfilter;
            str = "select  sum( b.byqCapcity) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID  " + strfilter + " and b.byqModle like '" + modmflist[imf] + "%' and (b.omniseal='true' )" + jstrfilter;
            list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", str);
            //if (list[0] == null) list[0] = 0;
            if (list[0] != null)
            ex.SetCellValue(list[0].ToString(), istart2 + (imf % 3) * 2 + 1, jstart + j % jmax);
        }

//.........这里部分代码省略.........
            }
            if (modtemp != "")
            {
                string[] strli = modtemp.Split(',');
                modmflist.Clear();
                
                for (i = 0; i < strli.Length; i++)
                {
                    modmflist.Add(strli[i]);
                }
            }
            for (i = 0; i < modtmlist.Count; i++)
            {
                if (modtemp.IndexOf(modtmlist[i].ToString()) == -1)
                {
                    if (modtemp!="")
                        modtemp += "," + modtmlist[i];
                    else
                        modtemp = modtmlist[i].ToString();
                }
            }
            if (modtemp != "")
            {
                string[] strli = modtemp.Split(',');
                modtmlist.Clear();
                for (i = 0; i < strli.Length; i++)
                {
                    modtmlist.Add(strli[i]);
                }
            }
                pagecount = (int)Math.Ceiling(caplist.Count / (jmax + 0.0));
            int itemp = modtmlist.Count / 6.0 > modmflist.Count / 3.0 ? (int)Math.Ceiling(modtmlist.Count / 6.0) : (int)Math.Ceiling(modmflist.Count / 3.0 );
            
            pagecount = itemp * pagecount;
            if (pagecount > 1)
            {
                ex.DeleteWorkSheet("Sheet2");
                ex.DeleteWorkSheet("Sheet3"); 
            }
            /////计算需要多少个工作表
            for (i = 1; i < pagecount; i++)
            {
               
               
                ex.CopySheet(1,i);
                ex.ReNameWorkSheet((i + 1), "Sheet" + (i + 1));
               
            }
            int istart = 7, istart2 = 19, jstart = 4, j=0;
            string jstrfilter="";
            mOrg org = Client.ClientHelper.PlatformSqlMap.GetOneByKey<mOrg>(obj);
            for ( j = 0; j < caplist.Count; j++)
            {
                ex.ActiveSheet("Sheet" + (j / jmax == 0 ? 1 : (int)Math.Ceiling(j / (jmax + 0.0))));
                //ex.ActiveSheet("Sheet" + (1 + (j / jmax) * (int)Math.Ceiling(j / (jmax + 0.0))));
                if (j % jmax == 0)
                {

                    jstrfilter = " and 1=1";
                    ex.ActiveSheet("Sheet" + (1+(j / jmax)*(int)Math.Ceiling(j / (jmax + 0.0))));
                    list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", "select   b.byqCapcity from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID  " + strfilter + jstrfilter);
                    if (list.Count >0) ex.SetCellValue(list.Count.ToString(), 5, jstart + j % jmax);
                    list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", "select   sum(b.byqCapcity) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID    " + strfilter + jstrfilter);
                    //if (list[0] == null) list[0] = 0;
                    if (list[0] != null)
                    ex.SetCellValue(list[0].ToString(), 6, jstart + j % jmax);
                    setExceldt(ex, list, caplist, modtmlist, modmflist, jstrfilter, strfilter, jstart, j, jmax, istart, istart2);    
                }
                ex.ActiveSheet("Sheet" + ((j+1) / jmax == 0 ? 1 : (int)Math.Ceiling((j+1) / (jmax + 0.0))));
               
                    jstrfilter = " and 1=1 and b.byqCapcity='" + caplist[j] + "'";
                    //ex.SetCellValue(caplist[j].ToString(), 4, jstart + j % jmax + 1);
                    setExceldt(ex, list, caplist, modtmlist, modmflist, jstrfilter, strfilter, jstart+1, j, jmax, istart, istart2);    
                
               
            }
            for (i = 0; i < pagecount; i++)
            {
                ex.ActiveSheet("Sheet" + (i+1));
                int jzu=caplist.Count/jmax+1;
                int itempmin = 0, itempmax = 0;
                    itempmax = (i % jzu+1) * jmax;
                    itempmin = (i % jzu) * jmax;

                    if (org != null)
                        ex.SetCellValue(org.OrgName, 3, 3);
                    else
                        ex.SetCellValue("全局", 3, 3);

                for (j = itempmin; j < itempmax && j < caplist.Count ; j++)
                {
                    ex.SetCellValue(caplist[j].ToString(), 4, jstart + j % jmax + 1);
                
                }
                //ex.SetCellValue(caplist[j].ToString(), 4, jstart + j % jmax + 1);

            }
            ex.ActiveSheet(1);
            ex.ShowExcel();
        }

        public static int ExportToExcel(string title, string dw, sdjls_sbpjb pj17) {
            string fname = Application.StartupPath + "\\00记录模板\\送管22送电线路设备评级表.xls";
            DSOFramerControl dsoFramerWordControl1 = new DSOFramerControl();
            string outfname = Path.GetTempFileName() + ".xls";
            File.Copy(fname, outfname);
            dsoFramerWordControl1.FileOpen(outfname);
            Microsoft.Office.Interop.Excel.Worksheet xx;
            Excel.Workbook wb = dsoFramerWordControl1.AxFramerControl.ActiveDocument as Excel.Workbook;        
            ExcelAccess ex = new ExcelAccess();
            ex.MyWorkBook = wb;
            ex.MyExcel = wb.Application;
            sd_xl xl = MainHelper.PlatformSqlMap.GetOne<sd_xl>(" where LineCode='" + pj17.LineCode + "'");
            try {
                if (xl == null) {
                    MsgBox.ShowWarningMessageBox("数据出错，没找到线路");
                    return -1;
                }
                string strLinexh = xl.WireType;//导线型号
                
                //IList<sd_gt> gtlis = Client.ClientHelper.PlatformSqlMap.GetList<sd_gt>(" Where LineCode='" + xl.LineCode + "' order by gtcode");
                int gtcount = Client.ClientHelper.PlatformSqlMap.GetRowCount<sd_gt>(" Where LineCode='" + xl.LineCode + "'");
                ex.ActiveSheet(1);
                //设置线路值
                ex.SetCellValue(xl.LineName, 3, 3);
                ex.SetCellValue(xl.LineVol, 3, 8);
                ex.SetCellValue(xl.WireLength.ToString(), 3, 11);
                ex.SetCellValue(xl.InDate.Year + "年" + xl.InDate.Month + "月", 3, 13);
                //ex.SetCellValue(xl.InDate.Year.ToString(), 4, 9);
                //ex.SetCellValue(xl.InDate.Month.ToString(), 4, 11);
                //评级日期
                ex.SetCellValue(DateTime.Now.Year + "年" + DateTime.Now.Month + "月" + DateTime.Now.Day + "日", 4, 3);

                //杆塔
                ex.SetCellValue(gtcount.ToString(), 5, 3);//合计
                ex.SetCellValue(gtcount.ToString(), 6, 3);//一类
                //导地线
                ex.SetCellValue(xl.WireLength.ToString(), 9, 3);//合计
                ex.SetCellValue(xl.WireLength.ToString(), 10, 3);//一类
                //绝缘子
                string sql = "in (";
                //foreach (sd_gt gt in gtlis)
                //{ 
                //    sql+="'"+gt.gtID+"',";
                //}
                //sql = sql.Substring(0, sql.Length - 1) + ")";
                sql = "in (select gtid from sd_gt Where LineCode='" + xl.LineCode + "' )";
                string strSQL = "select  sbid from sd_gtsb Where  gtID " + sql;
                
                IList jdzzList = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", strSQL);
               
                   
                strSQL += "  and ("+getNamesql()+")";
                

                IList jyuzlist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", strSQL);

                ex.SetCellValue(jyuzlist.Count.ToString(), 13, 3);
                ex.SetCellValue(jyuzlist.Count.ToString(), 14, 3);
                //接地装置
                ex.SetCellValue(jdzzList.Count.ToString(), 17, 3);
                ex.SetCellValue(jdzzList.Count.ToString(), 18, 3);

                //其它
                
                //评定等级
                ex.SetCellValue("一级", 24, 3);
                //评级负责人
                if (MainHelper.User != null)
                {
                    ex.SetCellValue(MainHelper.User.UserName, 25, 3);
                }
            } catch (Exception exmess) {
                MsgBox.ShowTipMessageBox(exmess.Message.ToString());
            }
            dsoFramerWordControl1.FileSave();
            pj17.BigData = dsoFramerWordControl1.FileDataGzip;
            dsoFramerWordControl1.FileClose();
            dsoFramerWordControl1.Dispose();
            //#endregion
            //System.Diagnostics.Process.Start(outfname);
            return 1;
        }

        public void ExportExcel(ExcelAccess ex, IList<PJ_sbbzqsbgmxb4> datalist, string orgid)
        {
            //此处写填充内容代码
            int row = 7;
            int col = 1;
            int rowcount = 13;

            //

            //加页
            int pageindex = 1;
            if (pageindex < Ecommon.GetPagecount(datalist.Count, rowcount))
            {
                pageindex = Ecommon.GetPagecount(datalist.Count, rowcount);
            }
            for (int j = 1; j < pageindex; j++)
            {

                ex.CopySheet(1, j);

            }
            for (int j = 0; j < datalist.Count; j++)
            {

                if (j % rowcount == 0)
                {
                    if (j == 0) ex.ActiveSheet(1);
                    else ex.ActiveSheet((j / rowcount + 1));
                    if (orgid != "") ex.SetCellValue(datalist[j].OrgName, 4, 2);
                    else
                        ex.SetCellValue(MainHelper.UserCompany, 4, 2);
                    ex.SetCellValue(DateTime.Now.ToString("yyyy年MM月dd日"), 4, 12);

                }
                ex.SetCellValue((j + 1).ToString(), row + j % rowcount, col);
                ex.SetCellValue(datalist[j].sssbmc, row + j % rowcount, col + 1);
                ex.SetCellValue(datalist[j].sssswz, row + j % rowcount, col + 2);
                string[] tempstr = datalist[j].sssbbh.Split(',');
                try
                {
                    ex.SetCellValue(tempstr[0], row + j % rowcount, col + 3);
                    ex.SetCellValue(tempstr[1], row + j % rowcount, col + 4);
                    ex.SetCellValue(tempstr[2], row + j % rowcount, col + 5);
                }
                catch
                {
                }

                string[] tempstr1 = datalist[j].S1.Split(',');
                try
                {
                    ex.SetCellValue(tempstr1[0], row + j % rowcount, col + 6);
                    ex.SetCellValue(tempstr1[1], row + j % rowcount, col + 7);
                    ex.SetCellValue(tempstr1[2], row + j % rowcount, col + 8);
                    ex.SetCellValue(tempstr1[3], row + j % rowcount, col + 9);
                }
                catch
                {
                }

                ex.SetCellValue(datalist[j].xw, row + j % rowcount, col + 10);
                ex.SetCellValue(datalist[j].statuts, row + j % rowcount, col + 11);


            }
        }

        /// <summary>
        /// 文档格式预定义好的，只填写内容
        /// </summary>
        /// <param name="obj"></param>
        public static void ExportExcel(IList<PJ_qxfl> objlist)
        {
            #region 原先的
            //if (objlist == null || objlist.Count == 0)
            //{
            //    return;
            //}
            ////lgm
            //ExcelAccess ex = new ExcelAccess();
            //SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            //string fname = Application.StartupPath + "\\00记录模板\\缺陷分类统计表.xls";

            //ex.Open(fname);
            ////每行显示文字长度
            //int zc = 20;
            ////与会人员之间的间隔符号
            //char[] jksign = new char[1] { ';' };
            //int row = 6;
            //int col = 2;
            //int len1 =1;
            //ex.SetCellValue(objlist[0].OrgName, 3, 2);
            //int pagecout = Ecommon.GetPagecount(objlist.Count,12);
            ////复制空模板
            //for (int m = 1; m < pagecout; m++) 
            //{
            //    ex.CopySheet(1, m);
            //    ex.ReNameWorkSheet(m + 1, "Sheet" + (m + 1));
            //}

            //for (int p = 0; p < pagecout; p++)
            //{
            //    ex.ActiveSheet(p + 1);
            //    for (int i = 0; i < 9; i++)
            //    {
            //        if (p*9+i>=objlist.Count)
            //        {
            //            break;
            //        }
            //        PJ_qxfl tempobj = objlist[p *12 + i];
            //        //缺陷情况
            //        ex.SetCellValue(tempobj.qxnr, row + i * len1, 2);

            //        //缺陷类别
            //        ex.SetCellValue(tempobj.qxlb, row + i * len1,3);


            //        //发现日期
            //        if (ComboBoxHelper.CompreDate(tempobj.xssj))
            //        {
            //            ex.SetCellValue(tempobj.xssj.Year.ToString(), row  + i * len1, 4);
            //            ex.SetCellValue(tempobj.xssj.Month.ToString(), row + i * len1, 5);
            //            ex.SetCellValue(tempobj.xssj.Day.ToString(), row + i * len1, 6);
            //        }
            //        //消除时限
            //        if (tempobj.xcqx!=string.Empty)
            //        {
            //            if (ComboBoxHelper.CompreDate(Convert.ToDateTime(tempobj.xcqx)))
            //            {
            //                ex.SetCellValue(Convert.ToDateTime(tempobj.xcqx).Year.ToString(), row + i * len1, 7);
            //                ex.SetCellValue(Convert.ToDateTime(tempobj.xcqx).Month.ToString(), row + i * len1, 8);
            //                ex.SetCellValue(Convert.ToDateTime(tempobj.xcqx).Day.ToString(), row + i * len1, 9);
            //            }
            //        }

            //        //备注
            //        ex.SetCellValue(tempobj.remark, row + i * len1,10);

            //    }

            //}

            //ex.ActiveSheet(1);

            //ex.ShowExcel();
            #endregion
            if (objlist == null || objlist.Count == 0)
            {
                return;
            }
            ExcelAccess ex = new ExcelAccess();
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            string fname = Application.StartupPath + "\\00记录模板\\缺陷分类统计表.xls";
            ex.Open(fname);
            ex.SetCellValue(objlist[0].OrgName, 2, 2);
            for (int i = 0; i < objlist.Count; i++)
            {
                ex.SetCellValue((i + 1).ToString(), i +4, 1);
                ex.SetCellValue(objlist[i].qxly, i + 4, 2);
                ex.SetCellValue(objlist[i].xlqd, i + 4, 3);
                ex.SetCellValue(objlist[i].xssj.ToString("yyyy年MM月dd日"), i + 4, 4);
                ex.SetCellValue(objlist[i].qxnr, i + 4, 5);
                ex.SetCellValue(objlist[i].qxlb, i + 4, 6);
                ex.SetCellValue(objlist[i].xcqx, i + 4, 7);
            }
            ex.ActiveSheet(1);
            ex.ShowExcel();
//.........这里部分代码省略.........

        public void ExportExcel(ExcelAccess ex ,IList<PJ_clcrkd> datalist)
        {
            
            //此处写填充内容代码
            int row = 7;
            int col = 1;
            int rowcount = 10;

            //

            if (datalist.Count < 1) return;
            Regex r1 = new Regex("[0-9]+");
            string str = r1.Match(datalist[0].num).Value;
            if (str == "")
            {
                str = datalist[0].num;
            }
            string tablename = datalist[0].ssgc + datalist[0].ssxm + str;
            if (tablename.Length > 30)
            {
                tablename = tablename.Substring(tablename.Length - 31);
            }
            //加页
            int pageindex = 1;
            if (pageindex < Ecommon.GetPagecount(datalist.Count, rowcount))
            {
                pageindex = Ecommon.GetPagecount(datalist.Count, rowcount);
            }
            for (int j = 1; j <= pageindex; j++)
            {

                ex.CopySheet(1, j);
                if (j == 1) ex.ReNameWorkSheet(j + 1, tablename);
                else
                    ex.ReNameWorkSheet(j + 1, tablename + "(" + (j) + ")");
            }
            for (int j = 0; j < datalist.Count; j++)
            {

                if (j % rowcount == 0)
                {
                    if (j == 0) ex.ActiveSheet(tablename);
                    else ex.ActiveSheet(tablename + "(" + (j / rowcount + 1) + ")");
                    ex.SetCellValue(datalist[j].ssgc, 2, 3);
                    ex.SetCellValue(datalist[j].ssxm, 4, 2);
                    ex.SetCellValue(datalist[j].ckdate.ToString("yyyy"), 4, 7);
                    ex.SetCellValue(datalist[j].ckdate.ToString("MM"), 4, 9);
                    ex.SetCellValue(datalist[j].ckdate.ToString("dd"), 4, 11);

                    ex.SetCellValue(datalist[j].ssgc, 22, 3);
                    ex.SetCellValue(datalist[j].ssxm, 24, 2);
                    ex.SetCellValue(datalist[j].ckdate.ToString("yyyy"), 24, 7);
                    ex.SetCellValue(datalist[j].ckdate.ToString("MM"), 24, 9);
                    ex.SetCellValue(datalist[j].ckdate.ToString("dd"), 24, 11);

                    ex.SetCellValue(datalist[j].ssgc, 42, 3);
                    ex.SetCellValue(datalist[j].ssxm, 44, 2);
                    ex.SetCellValue(datalist[j].ckdate.ToString("yyyy"), 44, 7);
                    ex.SetCellValue(datalist[j].ckdate.ToString("MM"), 44, 9);
                    ex.SetCellValue(datalist[j].ckdate.ToString("dd"), 44, 11);

                    ex.SetCellValue(datalist[j].ssgc, 62, 3);
                    ex.SetCellValue(datalist[j].ssxm, 64, 2);
                    ex.SetCellValue(datalist[j].indate.ToString("yyyy"), 64, 7);
                    ex.SetCellValue(datalist[j].indate.ToString("MM"), 64, 9);
                    ex.SetCellValue(datalist[j].indate.ToString("dd"), 64, 11);
                }

                ex.SetCellValue(datalist[j].wpmc, row + j % rowcount , col);
                ex.SetCellValue(datalist[j].wpgg, row + j % rowcount , col + 2);
                ex.SetCellValue(datalist[j].wpdw, row + j % rowcount, col + 4);

                ex.SetCellValue(datalist[j].cksl, row + j % rowcount, col + 5);
                ex.SetCellValue(datalist[j].wpdj, row + j % rowcount , col + 7);

                ex.SetCellValue(datalist[j].wpmc, row + j % rowcount+20, col);
                ex.SetCellValue(datalist[j].wpgg, row + j % rowcount + 20, col + 2);
                ex.SetCellValue(datalist[j].wpdw, row + j % rowcount + 20, col + 4);

                ex.SetCellValue(datalist[j].cksl, row + j % rowcount + 20, col + 5);
                ex.SetCellValue(datalist[j].wpdj, row + j % rowcount + 20, col + 7);

                ex.SetCellValue(datalist[j].wpmc, row + j % rowcount + 40, col);
                ex.SetCellValue(datalist[j].wpgg, row + j % rowcount + 40, col + 2);
                ex.SetCellValue(datalist[j].wpdw, row + j % rowcount + 40, col + 4);
                ex.SetCellValue(datalist[j].cksl, row + j % rowcount + 40, col + 5);
                ex.SetCellValue(datalist[j].wpdj, row + j % rowcount + 40, col + 7);

                ex.SetCellValue(datalist[j].wpmc, row + j % rowcount + 60, col);
                ex.SetCellValue(datalist[j].wpgg, row + j % rowcount + 60, col + 2);
                ex.SetCellValue(datalist[j].wpdw, row + j % rowcount + 60, col + 4);
                ex.SetCellValue(datalist[j].cksl, row + j % rowcount + 60, col + 5);
                ex.SetCellValue(datalist[j].wpdj, row + j % rowcount + 60, col + 7);
                if (datalist[j].wpdj != "")
                {
                    long value = Convert.ToInt64(Math.Round(Convert.ToDouble(datalist[j].cksl) * Convert.ToDouble(datalist[j].wpdj), 2) * 100);
                    int index = 19;
                    while (value > 0)
                    {
                        long ifen = value % 10;
//.........这里部分代码省略.........

        /// <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);
            }
//.........这里部分代码省略.........

        private static void ExportExcel(PJ_23 obj, ExcelAccess ex)
        {

            ex.SetCellValue(obj.jf + ":", 5, 4);
            ex.SetCellValue(obj.xybh, 4, 8);
            ex.SetCellValue(obj.cqdw + "：", 6, 4);
            string linename = "";
            if (obj.linename.Contains("线"))
            {
                linename = obj.linename.Substring(0, obj.linename.LastIndexOf("线"));
            }
            else
                linename = obj.linename;
            string[] filtchar = { "V", "v" };
            for (int i = 0; i < filtchar.Length; i++)
            {
                if (linename.Contains(filtchar[i]))
                {
                    linename = linename.Substring(linename.LastIndexOf(filtchar[i]) + 1);
                }
            }
            ex.SetCellValue(linename, 10, 7);
            string fzlinename = "";
            if (obj.fzlinename.Contains("支"))
            {
                fzlinename = obj.fzlinename.Substring(0, obj.fzlinename.LastIndexOf("支"));
            }
            else
                fzlinename = obj.fzlinename;
            ex.SetCellValue(fzlinename, 10, 10);
            ex.SetCellValue("'" + obj.gh, 10, 16);
            ex.SetCellValue(obj.cqfw, 11, 4);
            ex.SetCellValue(obj.cqdw + "。", 13, 4);
            ex.SetCellValue(obj.jf, 17, 4);
            //ex.SetCellValue(obj.cqdw, 15, 8);
            ex.SetCellValue(obj.qdrq.Year.ToString(), 21, 7);
            ex.SetCellValue(obj.qdrq.Month.ToString(), 21, 9);
            ex.SetCellValue(obj.qdrq.Day.ToString(), 21, 11);
        }