C# ExcelAccess.Open方法代码示例

        internal static void ExportCK(string rkid) {
            ExcelAccess ea = new ExcelAccess();
            string filename = AppDomain.CurrentDomain.BaseDirectory + "\\00记录模板\\.xls";
            if (!File.Exists(filename))
            {
                //导出资源文件到本机
                Stream obj = typeof(PrintHelper).Assembly.GetManifestResourceStream("Ebada.Kcgl._00记录模板..xls");
                object[] files = typeof(PrintHelper).Assembly.GetManifestResourceNames();
                if (obj != null) {
                    byte[] buff = new byte[obj.Length];
                    obj.Read(buff, 0, (int)obj.Length);

                    FileStream fs = new FileStream(filename, FileMode.OpenOrCreate);
                    fs.Write(buff, 0, buff.Length);
                    fs.Flush();
                    fs.Close();
                    fs.Dispose();
                }

            }
            ea.Open(filename);

          
            //显示文件
            ea.Print();
            ea.DisPoseExcel();
        }

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

        private void barButtonItem2_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
              SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            string fname = "";
            saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                fname = saveFileDialog1.FileName;
                try
                {
                    dsoFramerControl1.FileSave(saveFileDialog1.FileName, true);
                    if (MsgBox.ShowAskMessageBox("导出成功，是否打开该文档？") != DialogResult.OK)
                    {
                        ExcelAccess ex = new ExcelAccess();
                        ex.Open(fname);
                        //此处写填充内容代码

                        ex.ShowExcel();
                        return;
                    }
                }
                catch
                {
                    MsgBox.ShowSuccessfulMessageBox("无法保存" + fname + "。请用其他文件名保存文件，或将文件存至其他位置。");
                    return;
                }
            }
        }

        public void ExportExcelYear(string orgid, string year) {
            ////lgm
            ExcelAccess ex = new ExcelAccess();
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            string fname = Application.StartupPath + "\\00记录模板\\设备标志缺失变更明细表二.xls";
            ex.Open(fname);

            string strfirst = "";
            string filter = "";

            filter = "  where 1=1 ";
            if (orgid != "") filter += " and OrgCode='" + orgid + "'";

            if (isWorkflowCall) {
                filter = filter + " and id not in (select ModleRecordID from WF_ModleRecordWorkTaskIns where  WorkFlowId='"
                    + WorkFlowData.Rows[0]["WorkFlowId"].ToString() + "') "
                        + " or id in  (select ModleRecordID from WF_ModleRecordWorkTaskIns where "
                    + "    RecordID='" + currRecord.ID + "') "
                    ;
            }
            IList<PJ_sbbzqsbgmxb2> datalist = Client.ClientHelper.PlatformSqlMap.GetListByWhere<PJ_sbbzqsbgmxb2>(
             filter
               );
            ExportExcel(ex, datalist, orgid);

            ex.ShowExcel();
        }

        public void ExportExcelMonth(DateTime dt, DateTime dt2, string orgid)
        {
            //lgm
            ExcelAccess ex = new ExcelAccess();
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            string fname = Application.StartupPath + "\\00记录模板\\所月度停电计划.xls";
            ex.Open(fname);
            string startday = "20";
            IList list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr",
                string.Format("select nr from pj_dyk where  dx='所月度停电计划' and sx like '%{0}%' and nr!=''", "申报截止日期"));
            if (list.Count > 0)
                startday=list[0].ToString();
            string str = " where (TDtime between '" +dt.Year + "-"
                + dt.Month + "-" + dt.Day
                + " 00:00:00' and  '"
                + dt2.Year + "-"
                + dt2.Month + "-" + dt2.Day + " 23:59:59' ) ";
            if (orgid != "") str += " and OrgCode='" + orgid + "'";
            IList<PJ_tdjh> datalist = Client.ClientHelper.PlatformSqlMap.GetListByWhere<PJ_tdjh>(
               str
                );
            ExportExcel(dt, ex, datalist);
            ex.ShowExcel();

        }

        /// <summary>
        /// 文档格式预定义好的，只填写内容
        /// </summary>
        /// <param name="obj"></param>
        public void ExportExcel()
        {
            ////lgm
            ExcelAccess ex = new ExcelAccess();
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            string fname = Application.StartupPath + "\\00记录模板\\局设备评级计划.xls";
            ex.Open(fname);

            string strfirst = "";
            string filter = "";

            filter = "  where 1=1 ";
            if (isWorkflowCall)
            {
                filter = filter + " and id not in (select ModleRecordID from WF_ModleRecordWorkTaskIns where  WorkFlowId='"
                    + WorkFlowData.Rows[0]["WorkFlowId"].ToString() + "') "
                        + " or id in  (select ModleRecordID from WF_ModleRecordWorkTaskIns where "
                    + "    RecordID='" + currRecord.ID + "') "
                    ;
            }
            IList<PJ_jsbpjjh> datalist = Client.ClientHelper.PlatformSqlMap.GetListByWhere<PJ_jsbpjjh>(
             filter
               );
            ExportExcel(ex, datalist);




            ex.ShowExcel();
        }

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

        private void barButtonItem2_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) {
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            string fname = "";
            saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls";


            if (saveFileDialog1.ShowDialog() == DialogResult.OK) {
                fname = saveFileDialog1.FileName;
                try {
                    dsoFramerControl1.FileSave(saveFileDialog1.FileName, true);
                    //dsoFramerControl1.Dispose();

                    //dsoExcelControl1.AxFramerControl.Save(fname,true,null,null);
                    // dsoExcelControl1.FileSaveAs(out fname);
                    //if (MsgBox.Show("导出成功!") != DialogResult.Yes)
                    //////MsgBox.Show("导出成功!");
                    //////dsoExcelControl1.AxFramerControl.Dispose();
                    //////return;

                    if (MsgBox.ShowAskMessageBox("导出成功，是否打开该文档？") != DialogResult.OK) {
                        //dsoExcelControl1.AxFramerControl.Dispose();
                        //dsoExcelControl1.Dispose();
                        ExcelAccess ex = new ExcelAccess();
                        //SaveFileDialog saveFileDialog1 = new SaveFileDialog();
                        //string fname = Application.StartupPath + "\\00记录模板\\26防护通知书.xls";

                        ex.Open(fname);
                        //此处写填充内容代码

                        ex.ShowExcel();
                        //Microsoft.Office.Interop.Excel.Application exc = new Microsoft.Office.Interop.Excel.Application(); //引用Excel对象
                        //Microsoft.Office.Interop.Excel.Workbook book = exc.Application.Workbooks.Add(fname); //引用Excel工作簿
                        //exc.Visible = true; //使Excel可视
                        return;
                    }
                    //System.Diagnostics.Process.Start(fname);

                    //ExcelAccess ex = new ExcelAccess();
                    ////SaveFileDialog saveFileDialog1 = new SaveFileDialog();
                    ////string fname = Application.StartupPath + "\\00记录模板\\26防护通知书.xls";

                    //ex.Open(fname);
                    ////此处写填充内容代码

                    //ex.ShowExcel();




                    //  System.Diagnostics.Process.Start(fname);
                } catch {
                    MsgBox.ShowSuccessfulMessageBox("无法保存" + fname + "。请用其他文件名保存文件，或将文件存至其他位置。");
                    return;
                }
            }
        }

        /// <summary>
        /// 文档格式预定义好的，只填写内容
        /// </summary>
        /// <param name="obj"></param>
        public static void ExportExcel(object obj) {
            ExcelAccess ex = new ExcelAccess();
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            string fname = Application.StartupPath + "\\00记录模板\\17线路条图.xls";

            ex.Open(fname);
            //此处写填充内容代码

           ex.ShowExcel();
        }

 /// <summary>
 /// 文档格式预定义好的，只填写内容
 /// </summary>
 /// <param name="obj"></param>
 public static void ExportExcel(sdjl_23 obj)
 {
     ExcelAccess ex = new ExcelAccess();
     SaveFileDialog saveFileDialog1 = new SaveFileDialog();
     string fname = Application.StartupPath + "\\00记录模板\\送电16电力设备产权、维护范围协议书.xls";
     if (obj.BigData != null)
     {
         if (obj.BigData.Length > 10)
         {
             fname = Path.GetTempPath() + "送电16电力设备产权、维护范围协议书.xls";
             //MemoryStream ms = new MemoryStream(obj.BigData);
             FileStream fs = new FileStream(fname, FileMode.OpenOrCreate);
             fs.Write(obj.BigData, 0, obj.BigData.Length);
             fs.Flush();
             fs.Close();
         }
     }
     ex.Open(fname);
     ex.SetCellValue(obj.jf, 5, 4);
     ex.SetCellValue(obj.xybh, 4, 11);
     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,9, 9);
     //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.cqfw, 10, 4);
     ex.SetCellValue("'" + obj.gh, 9, 12);
     ex.SetCellValue(obj.cqdw+"。", 12, 4);
     ex.SetCellValue(obj.jf, 11, 4);
     ex.SetCellValue(obj.qxydd, 15, 8);
     ex.SetCellValue(obj.qdrq.Year.ToString(), 21, 11);
     ex.SetCellValue(obj.qdrq.Month.ToString(), 21, 13);
     ex.SetCellValue(obj.qdrq.Day.ToString(), 21, 16);
     ex.ShowExcel();
 }

 /// <summary>
 /// 文档格式预定义好的，只填写内容
 /// </summary>
 /// <param name="obj"></param>
 public  void ExportExcel(IList<PJ_tdjh> datalist)
 {
     //lgm
     ExcelAccess ex = new ExcelAccess();
     SaveFileDialog saveFileDialog1 = new SaveFileDialog();
     string fname = Application.StartupPath + "\\00记录模板\\所月度停电计划.xls";
     ex.Open(fname);
     ExportExcel(DateTime.Now, ex, datalist);
     ex.ShowExcel();
    
 }

 /// <summary>
 /// 文档格式预定义好的，只填写内容
 /// </summary>
 /// <param name="obj"></param>
 public  void ExportExcel(IList<PJ_sdytz> datalist)
 {
     //lgm
     ExcelAccess ex = new ExcelAccess();
     SaveFileDialog saveFileDialog1 = new SaveFileDialog();
     string fname = Application.StartupPath + "\\00记录模板\\双电源台账.xls";
     ex.Open(fname);
     ExportExcel(ex, datalist);
     ex.ShowExcel();
    
 }

 /// <summary>
 /// 文档格式预定义好的，只填写内容
 /// </summary>
 /// <param name="obj"></param>
 public  void ExportExcel(IList<PJ_21dyjcdcbkchild> datalist)
 {
     //lgm
     ExcelAccess ex = new ExcelAccess();
     SaveFileDialog saveFileDialog1 = new SaveFileDialog();
     string fname = Application.StartupPath + "\\00记录模板\\电压检测点抄表卡.xls";
     ex.Open(fname);
     ExportExcel(ex, datalist);
     ex.ShowExcel();
    
 }

        public void ExportExcelMonth(string orgid)
        {
            //lgm
            ExcelAccess ex = new ExcelAccess();
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            string fname = Application.StartupPath + "\\00记录模板\\客户电力电容器台帐.xls";
            ex.Open(fname);
           
            ex.ShowExcel();

        }