C# ExcelAccess.ActiveSheet方法代码示例

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

        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);
        }

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

        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">操作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_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_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);


            }
        }

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

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

        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);
            //   }

            //将固定的写完再创建不固定的。


//.........这里部分代码省略.........

        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;
                }


            }
        }