private void button1_Click(object sender, System.EventArgs e)
        {
            string str_Sql, errorstring;

            useExcel = true;
            int i = 0, j = 0;

            excelApp = new Excel.ApplicationClass();
            Excel.Workbook  excelBook = excelApp.Workbooks.Add(1);
            Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];

            if (conn.ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("无可打印内容!", "提醒!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }
            try
            {
                //设置表头
                excelSheet.Cells[1, 1] = comboBox_QuXian.Text + " 共有学生 " + label_Count.Text + " 个";
                excelSheet.Cells[2, 1] = "学校类型";
                excelSheet.Cells[2, 2] = "区县代码";
                excelSheet.Cells[2, 3] = "区县";
                excelSheet.Cells[2, 4] = "人数";
                //设置表头格式
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 2]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 2]).Font.Bold           = true;

                str_Sql     = "select * FROM View_QuXian_Student_Statistics WHERE 区县代码='" + comboBox_QuXian.SelectedValue.ToString() + "'";
                errorstring = conn.Fill(str_Sql);
                DataGrid1.SetDataBinding(conn.ds, "TableIn");

                //填充表中各单元格
                for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
                {
                    for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
                    {
                        excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
                    }
                }
                //设置报表表格为最适应宽度
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();

                excelSheet.Cells[i + 3, 1] = "完中初中部";
                excelSheet.Cells[i + 3, 2] = label_Class_Type_ID_chu.Text;
                excelSheet.Cells[i + 3, 3] = "人";

                excelSheet.Cells[i + 4, 1] = "完中高中部";
                excelSheet.Cells[i + 4, 2] = label_Class_Type_ID_gao.Text;
                excelSheet.Cells[i + 4, 3] = "人";

                excelApp.Visible = true;
                //excelApp.Quit();
            }
            catch
            {
                throw new Exception("Excel error");
            }
        }
Пример #2
0
 public ExcelOperations()
 {
     if (_mExcelApplication == null)
     {
         _mExcelApplication = new Excel.Application();
     }
 }
        /// <summary>
        /// 清理所有正在使用的资源。
        /// </summary>
        protected override void Dispose(bool disposing)
        {
            if (useExcel == true)
            {
                try
                {
                    excelApp.Application.Workbooks.Close();
                    excelApp.Application.Quit();
                    excelApp.Quit();
                    excelBook  = null;
                    excelSheet = null;
                    excelApp   = null;
                    GC.Collect();
                }
                catch
                {
                    throw new Exception("Excel 关闭错误!");
                }
            }

            if (disposing)
            {
                if (components != null)
                {
                    components.Dispose();
                }
            }
            base.Dispose(disposing);
        }
Пример #4
0
 public QDAddinDrillDown(QDConfig config, string Pos, Excel._Application xls, QueryBuilder.SQLBuilder sqBuilder, string connectDesc)
 {
     InitializeComponent();
     _config = config;
     Init(Pos, xls, sqBuilder);
     //_strConnectDes = sqBuilder.StrConnectDes;
     //ThemeResolutionService.ApplyThemeToControlTree(this, THEME);
 }
        private void button1_Click(object sender, System.EventArgs e)
        {
            string str_Sql;

            useExcel = true;
            int i = 0, j = 0;

            excelApp = new Excel.ApplicationClass();
            Excel.Workbook  excelBook = excelApp.Workbooks.Add(1);
            Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];

            if (conn.ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("无可打印内容!", "提醒!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }
            try
            {
                //设置表头
                String str_SchoolName = conn.School_IDtoWhat(comboBox_School.SelectedValue.ToString(), "School_Name");

                str_Sql = "Select * from Teacher WHERE School_ID='" + comboBox_School.SelectedValue.ToString() + "'";
                string errorstring = conn.Fill(str_Sql);
                excelSheet.Cells[1, 1] = str_SchoolName + " 共有教师 " + conn.ds.Tables[0].Rows.Count.ToString() + " 个";

                excelSheet.Cells[2, 1] = "学校代码";
                excelSheet.Cells[2, 2] = "学校名称";
                excelSheet.Cells[2, 3] = "学历";
                excelSheet.Cells[2, 4] = "人数";

                //设置表头格式
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 4]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 4]).Font.Bold           = true;

                str_Sql     = "Select * from View_Teacher_Statistics   WHERE View_Teacher_Statistics.学校代码='" + comboBox_School.SelectedValue.ToString() + "'";
                errorstring = conn.Fill(str_Sql);
                DataGrid1.SetDataBinding(conn.ds, "TableIn");

                //填充表中各单元格
                for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
                {
                    for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
                    {
                        excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
                    }
                }
                //设置报表表格为最适应宽度
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();

                excelApp.Visible = true;
                //excelApp.Quit();
            }
            catch
            {
                throw new Exception("Excel error");
            }
        }
        private void button3_Click(object sender, System.EventArgs e)
        {
            useExcel = true;
            excelApp = new Excel.ApplicationClass();
            //Excel.Workbook
            excelBook = excelApp.Workbooks.Add(1);
            //Excel.Worksheet
            excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];

            if (conn.ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("无可打印内容!", "提醒!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }
            try
            {
                //创建应用程序,工作表,工作簿
                int i, j = 0;

                excelApp.Visible = true;

                //设置表头
                excelSheet.Cells[2, 1]  = "身份证号码";
                excelSheet.Cells[2, 2]  = "姓名";
                excelSheet.Cells[2, 3]  = "性别";
                excelSheet.Cells[2, 4]  = "出生日期";
                excelSheet.Cells[2, 5]  = "参加工作时间";
                excelSheet.Cells[2, 6]  = "学历";
                excelSheet.Cells[2, 7]  = "职称";
                excelSheet.Cells[2, 8]  = "职务";
                excelSheet.Cells[2, 9]  = "毕业院校";
                excelSheet.Cells[2, 10] = "毕业日期";
                excelSheet.Cells[2, 11] = "所学专业";
                excelSheet.Cells[2, 12] = "是否在编";
                excelSheet.Cells[2, 13] = "是否专任教师";
                excelSheet.Cells[2, 14] = "担任课程";

                //设置表头格式
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 14]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 14]).Font.Bold           = true;

                //填充表中各单元格
                for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
                {
                    for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
                    {
                        excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
                    }
                }
                //设置报表表格为最适应宽度
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();
            }
            catch
            {
                throw new Exception("Excel error");
            }
        }
Пример #7
0
 public void Init(string Pos, Excel._Application xls, QueryBuilder.SQLBuilder sqBuilder)
 {
     _sqlBuilder = new SQLBuilder(sqBuilder);
     //((GridViewComboBoxColumn)dgvSelectNodes.Columns["Agregate"]).DataSource = Parsing.GetListNumberAgregate();
     _sqlBuilder.Pos = Pos;
     _xlsApp         = xls;
     //TopMost = true;
     //GetQueryBuilderFromFomular(formular);
 }
Пример #8
0
 public QDAddinDrillDown(QDConfig config, string Pos, Excel._Application xls, string formular, string connectDesc, string user)
 {
     InitializeComponent();
     _config = config;
     _user   = user;
     //_strConnectDes = connectDesc;
     Init(Pos, xls, formular);
     _sqlBuilder.StrConnectDes = connectDesc;
     //ThemeResolutionService.ApplyThemeToControlTree(this, THEME);
 }
Пример #9
0
 public QDAddIn(QDConfig confi, string Pos, Excel._Application xls, string formular, string connect, string connectDes, string user)
 {
     InitializeComponent();
     ////ThemeResolutionService.ApplyThemeToControlTree(this, THEME);
     _user          = user;
     _strConnect    = connect;
     _strConnectDes = connectDes;
     _config        = confi;
     Init(Pos, xls, formular);
 }
        private void button3_Click(object sender, System.EventArgs e)
        {
            if (conn.ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("无可打印内容!", "提醒!",
                                MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }
            int i, j = 0;

            useExcel = true;
            excelApp = new Excel.ApplicationClass();
            Excel.Workbook  excelBook  = excelApp.Workbooks.Add(1);
            Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];

            try
            {
                //设置表头
                excelSheet.Cells[1, 1]  = "学校代码";
                excelSheet.Cells[1, 2]  = "学校名称";
                excelSheet.Cells[1, 3]  = "学校类别";
                excelSheet.Cells[1, 4]  = "校长";
                excelSheet.Cells[1, 5]  = "电话";
                excelSheet.Cells[1, 6]  = "邮政编码";
                excelSheet.Cells[1, 7]  = "学校地址";
                excelSheet.Cells[1, 8]  = "学校学制年限";
                excelSheet.Cells[1, 9]  = "学生人数";
                excelSheet.Cells[1, 10] = "教师人数";

                //设置报表头格式
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[1, 10]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[1, 10]).Font.Bold           = true;

                //填充表中各单元格
                for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
                {
                    for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
                    {
                        excelSheet.Cells[i + 1, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
                    }
                }

                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();

                excelApp.Visible = true;
            }
            catch
            {
                throw new Exception("Excel error");
            }
        }
Пример #11
0
        //生成Excel文件,以便打印
        private void button1_Click(object sender, System.EventArgs e)
        {
            useExcel = true;
            int i = 0, j = 0;

            excelApp = new Excel.ApplicationClass();
            Excel.Workbook  excelBook = excelApp.Workbooks.Add(1);
            Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];

            if (conn.ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("无可打印内容!", "提醒!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }
            try
            {
                //设置表头
                excelSheet.Cells[1, 1] = conn.School_IDtoWhat(comboBox_School.SelectedValue.ToString(), "School_Name");
                excelSheet.Cells[2, 1] = "班级代码";
                excelSheet.Cells[2, 2] = "班级类别";
                excelSheet.Cells[2, 3] = "班级名";
                excelSheet.Cells[2, 4] = "班主任";
                excelSheet.Cells[2, 5] = "学生人数";

                //设置表头格式
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 5]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 5]).Font.Bold           = true;

                string str_Sql     = "SELECT View_Class.Class_ID AS 班级代码,View_Class.Class_Type_Name AS 班级类别,View_Class.Class_Name AS 班级名,View_Class.TeacherInCharge AS 班主任,Student_Count AS 学生人数 FROM View_Class WHERE View_Class.School_ID='" + comboBox_School.SelectedValue.ToString() + "'";
                string errorstring = conn.Fill(str_Sql);
                DataGrid1.SetDataBinding(conn.ds, "TableIn");

                //填充表中各单元格
                for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
                {
                    for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
                    {
                        excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
                    }
                }
                //设置报表表格为最适应宽度
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();

                excelApp.Visible = true;
                //excelApp.Quit();
            }
            catch
            {
                throw new Exception("Excel error");
            }
        }
Пример #12
0
 public void Init(string Pos, Excel._Application xls, string formular)
 {
     _sqlBuilder = new QueryBuilder.SQLBuilder(processingMode.Balance);
     //((GridViewComboBoxColumn)dgvSelectNodes.Columns["Agregate"]).DataSource = Parsing.GetListNumberAgregate();
     _sqlBuilder.Pos = Pos;
     _xlsApp         = xls;
     //TopMost = true;
     GetQueryBuilderFromFomular(formular);
     GetConnectString();
     if (xls == null)
     {
         btPivotTable.Visible = false;
     }
 }
        private void button1_Click(object sender, System.EventArgs e)
        {
            useExcel = true;
            int i = 0, j = 0;

            excelApp = new Excel.ApplicationClass();
            Excel.Workbook  excelBook = excelApp.Workbooks.Add(1);
            Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];

            if (conn.ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("无可打印内容!", "提醒!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }
            try
            {
                //设置表头
                excelSheet.Cells[1, 1] = " 共有班级" + label_ClassCount.Text + "个";
                excelSheet.Cells[2, 1] = "学校类型";
                excelSheet.Cells[2, 2] = "班级类型";
                excelSheet.Cells[2, 3] = "个数";

                //设置表头格式
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 2]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 2]).Font.Bold           = true;

                string str_Sql     = "Select * from View_City_Class_Statistics";
                string errorstring = conn.Fill(str_Sql);
                DataGrid1.SetDataBinding(conn.ds, "TableIn");

                //填充表中各单元格
                for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
                {
                    for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
                    {
                        excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
                    }
                }
                //设置报表表格为最适应宽度
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();

                excelApp.Visible = true;
                //excelApp.Quit();
            }
            catch
            {
                throw new Exception("Excel error");
            }
        }
Пример #14
0
        private void button1_Click(object sender, System.EventArgs e)
        {
            if (conn.ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("无可打印内容!", "提醒!",
                                MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }
            int i, j = 0;

            useExcel = true;
            excelApp = new Excel.ApplicationClass();
            Excel.Workbook  excelBook  = excelApp.Workbooks.Add(1);
            Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];

            string str_Sql = "Select  School_ID AS 学校代码,School_Name AS 学校名称,Class_Type_Name AS 班级类别,Class_Name AS 班级名 FROM View_ClassSchool inner join Class_Type on View_ClassSchool.Class_Type_ID=Class_Type.Class_Type_ID WHERE (View_ClassSchool.School_Type_ID=0 AND(View_ClassSchool.Class_Type_ID>5 OR View_ClassSchool.Class_Type_ID<0)) OR (View_ClassSchool.School_Type_ID=1 AND(View_ClassSchool.Class_Type_ID<6 OR View_ClassSchool.Class_Type_ID>8)) OR (View_ClassSchool.School_Type_ID=2 AND(View_ClassSchool.Class_Type_ID<9 OR View_ClassSchool.Class_Type_ID>11)) OR (View_ClassSchool.School_Type_ID=3 AND(View_ClassSchool.Class_Type_ID<12 OR View_ClassSchool.Class_Type_ID>17)) OR (View_ClassSchool.School_Type_ID=4 AND(View_ClassSchool.Class_Type_ID<18 OR View_ClassSchool.Class_Type_ID>26)) OR (View_ClassSchool.School_Type_ID=5 AND(View_ClassSchool.Class_Type_ID<30 OR View_ClassSchool.Class_Type_ID>32)) OR (View_ClassSchool.School_Type_ID=6 AND(View_ClassSchool.Class_Type_ID<33 OR View_ClassSchool.Class_Type_ID>44))";

            try
            {
                //设置表头
                excelSheet.Cells[1, 1] = "学校代码";
                excelSheet.Cells[1, 2] = "学校名称";
                excelSheet.Cells[1, 3] = "班级类别";
                excelSheet.Cells[1, 4] = "班级名";

                //设置报表头格式
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[1, 4]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[1, 4]).Font.Bold           = true;

                //填充表中各单元格
                for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
                {
                    for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
                    {
                        excelSheet.Cells[i + 1, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
                    }
                }

                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();

                excelApp.Visible = true;
            }
            catch
            {
                throw new Exception("Excel error");
            }
        }
Пример #15
0
 public void Dispose()
 {
     try
     {
         if (_mExcelApplication != null)
         {
             Close(false);
             _mExcelApplication.Quit();
             _mExcelApplication = null;
         }
     }
     catch (Exception ex)
     {
         Debug.Write(ex.ToString());
     }
 }
Пример #16
0
        public void Init(string Pos, Excel._Application xls, string formular)
        {
            _sqlBuilder = new QueryBuilder.SQLBuilder(processingMode.Balance);
            DataTable dtcom  = QueryBuilder.Parsing.GetListNumberAgregate();
            DataRow   newrow = dtcom.NewRow();

            newrow["Code"] = newrow["Description"] = "";
            dtcom.Rows.Add(newrow);
            ((DataGridViewComboBoxColumn)dgvSelectNodes.Columns["colAgregate"]).DataSource    = dtcom;
            ((DataGridViewComboBoxColumn)dgvSelectNodes.Columns["colAgregate"]).DisplayMember = "Description";
            ((DataGridViewComboBoxColumn)dgvSelectNodes.Columns["colAgregate"]).ValueMember   = "Code";
            _sqlBuilder.Pos = Pos;
            _xlsApp         = xls;
            //TopMost = true;
            GetQueryBuilderFromFomular(formular);
            GetConnectString();
        }
        private void button1_Click(object sender, System.EventArgs e)
        {
            useExcel = true;
            int i = 0, j = 0;

            excelApp = new Excel.ApplicationClass();
            Excel.Workbook  excelBook = excelApp.Workbooks.Add(1);
            Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];

            if (conn.ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("无可打印内容!", "提醒!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }
            try
            {
                //设置表头
                excelSheet.Cells[2, 1] = "区域";
                excelSheet.Cells[2, 2] = "学生人数";

                //设置表头格式
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 2]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 2]).Font.Bold           = true;

                //填充表中各单元格
                for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
                {
                    for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
                    {
                        excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
                    }
                }
                //设置报表表格为最适应宽度
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();

                excelApp.Visible = true;
                //excelApp.Quit();
            }
            catch
            {
                throw new Exception("Excel error");
            }
        }
Пример #18
0
        /// <summary>
		/// Replace all esxiting scritps depend value sheet
		/// </summary>
		/// <param name="sourceDependFile">sourceDependFile</param>
        public void replaceAllValDependList(string sourceDependFile)
        {
        	objExl_Source = new Excel.ApplicationClass();
            string dir = Path.Combine(System.IO.Directory.GetCurrentDirectory(), "../../keywordscripts");
            System.IO.DirectoryInfo sourceDir = new System.IO.DirectoryInfo(dir);
            FileInfo[] files = sourceDir.GetFiles();

            foreach (FileInfo file in files)
            {
                Directory.CreateDirectory(dir + "/newKeywordscripts");

                File.Copy(System.IO.Directory.GetCurrentDirectory() + "/keywordscripts/" + sourceDependFile+".xlsx", dir + "/newKeywordscripts/" + "Temp" +file.Name , true);
                DependentValReplace(dir + "/" + file.Name, dir + "/newKeywordscripts/" + "Temp" + file.Name);
                File.Copy(dir + "/newKeywordscripts/" + "Temp" + file.Name, dir + "/newKeywordscripts/" + file.Name, true);
            }

            foreach (FileInfo file in files)
            {
                File.Delete(dir + "/newKeywordscripts/" + "Temp" + file.Name);
            }
           
        }
Пример #19
0
        /// <summary>
        /// Replace all esxiting scritps depend value sheet
        /// </summary>
        public void replaceAllValDependList(string sourceDependFile)
        {
            objExl_Source = new Excel.ApplicationClass();
            string dir = Path.Combine(System.IO.Directory.GetCurrentDirectory(), "../../keywordscripts");

            System.IO.DirectoryInfo sourceDir = new System.IO.DirectoryInfo(dir);
            FileInfo[] files = sourceDir.GetFiles();

            foreach (FileInfo file in files)
            {
                Directory.CreateDirectory(dir + "/newKeywordscripts");

                File.Copy(System.IO.Directory.GetCurrentDirectory() + "/keywordscripts/" + sourceDependFile + ".xlsx", dir + "/newKeywordscripts/" + "Temp" + file.Name, true);
                DependentValReplace(dir + "/" + file.Name, dir + "/newKeywordscripts/" + "Temp" + file.Name);
                File.Copy(dir + "/newKeywordscripts/" + "Temp" + file.Name, dir + "/newKeywordscripts/" + file.Name, true);
            }

            foreach (FileInfo file in files)
            {
                File.Delete(dir + "/newKeywordscripts/" + "Temp" + file.Name);
            }
        }
Пример #20
0
 private void DoExcel(Excel._Application excelApp, int amountValue)
 {
     for (var i = 2; i < amountValue + 2; i++)
     {
         excelApp.Cells[i, 1]  = _form1.ProductCategory.Text;
         excelApp.Cells[i, 2]  = _form1.StoreClass.Text;
         excelApp.Cells[i, 3]  = _form1.ProductName.Text + (i - 1);
         excelApp.Cells[i, 4]  = _form1.Quantity.Text;
         excelApp.Cells[i, 5]  = _form1.SugestPrice.Text;
         excelApp.Cells[i, 6]  = _form1.Price.Text;
         excelApp.Cells[i, 7]  = _form1.Cost.Text;
         excelApp.Cells[i, 8]  = _form1.HighestBuyQuantity.Text;
         excelApp.Cells[i, 9]  = _form1.StartDateTime.Text;
         excelApp.Cells[i, 10] = _form1.EndDateTime.Text;
         excelApp.Cells[i, 11] = _form1.Delivery.Text;
         excelApp.Cells[i, 12] = _form1.ExpectedShippingDay.Text;
         excelApp.Cells[i, 13] = _form1.AfterPayShippingDay.Text;
         excelApp.Cells[i, 14] = _form1.ShippingType.Text;
         excelApp.Cells[i, 15] = _form1.PayType.Text;
         excelApp.Cells[i, 16] = _form1.ProductOption.Text;
         excelApp.Cells[i, 17] = _form1.ProductOption1.Text;
         excelApp.Cells[i, 18] = _form1.ProductOption2.Text;
         excelApp.Cells[i, 19] = _form1.ProductNumber.Text;
         excelApp.Cells[i, 20] = _form1.ProductOptionImg.Text;
         excelApp.Cells[i, 21] = _form1.ProductSpec.Text;
         excelApp.Cells[i, 22] = (i - 1) + _form1.ProductImg1.Text;
         excelApp.Cells[i, 32] = _form1.SalePoint.Text;
         excelApp.Cells[i, 33] = _form1.ProductFeature.Text;
         excelApp.Cells[i, 34] = _form1.Detail.Text;
         excelApp.Cells[i, 35] = _form1.StoreName.Text;
         excelApp.Cells[i, 36] = _form1.SEOTitle.Text;
         excelApp.Cells[i, 37] = _form1.SEOKeyword.Text;
         excelApp.Cells[i, 38] = _form1.SEODescription.Text;
         excelApp.Cells[i, 39] = _form1.WarmLayerClass.Text;
         excelApp.Cells[i, 40] = _form1.Volume.Text;
         excelApp.Cells[i, 41] = _form1.Weight.Text;
     }
 }
Пример #21
0
 //**********************************************************************
 /// <summary>
 /// Constructer. new excel application process
 /// </summary>
 public LxXlsOper()
 {
     excelMain = new Excel.ApplicationClass();
 }
Пример #22
0
        private void button1_Click(object sender, System.EventArgs e)
        {
            useExcel = true;
            excelApp = new Excel.ApplicationClass();
            //Excel.Workbook
            excelBook = excelApp.Workbooks.Add(1);
            //Excel.Worksheet
            excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];

            if (conn.ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("无可打印内容!", "提醒!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }
            try
            {
                //创建应用程序,工作表,工作簿
                int i, j = 0;

                excelApp.Visible = true;

                //设置表头
                excelSheet.Cells[1, 1]  = conn.School_IDtoWhat(comboBox_School.SelectedValue.ToString(), "School_Name");
                excelSheet.Cells[2, 1]  = "身份证号码";
                excelSheet.Cells[2, 2]  = "姓名";
                excelSheet.Cells[2, 3]  = "性别";
                excelSheet.Cells[2, 4]  = "出生日期";
                excelSheet.Cells[2, 5]  = "参加工作时间";
                excelSheet.Cells[2, 6]  = "学历";
                excelSheet.Cells[2, 7]  = "职称";
                excelSheet.Cells[2, 8]  = "职务";
                excelSheet.Cells[2, 9]  = "毕业院校";
                excelSheet.Cells[2, 10] = "毕业日期";
                excelSheet.Cells[2, 11] = "所学专业";
                excelSheet.Cells[2, 12] = "是否在编";
                excelSheet.Cells[2, 13] = "是否专任教师";
                excelSheet.Cells[2, 14] = "担任课程";

                //设置表头格式
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 14]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 14]).Font.Bold           = true;

                string str_Sql     = "SELECT Teacher_ID As 身份证号码,Name As 姓名,Sex AS 性别,convert(char(10),Birthday,120) As 出生日期,convert(char(10),WorkTime,120) As 参加工作时间,SchoolType AS 学历,PostCan AS 职称,PostIn AS 职务,SchoolGrad AS 毕业院校,convert(char(10),GradTime,120)  毕业日期,SpecialIn AS 所学专业,InWorkSheet As 在编,IsFullTime AS 专任教师,LessonTeach AS 担任课程 FROM Teacher WHERE School_ID='" + comboBox_School.SelectedValue.ToString() + "'";
                string errorstring = conn.Fill(str_Sql);
                if (errorstring != "OK")
                {
                    MessageBox.Show("请检查数据库!" + errorstring, "提醒!",
                                    MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    button1.Enabled = false;
                    return;
                }

                //填充表中各单元格
                for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
                {
                    for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
                    {
                        excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
                    }
                }
                //设置报表表格为最适应宽度
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();
            }
            catch
            {
                throw new Exception("Excel error");
            }
        }
Пример #23
0
 //**********************************************************************
 /// <summary>
 /// Constructer. new excel application process
 /// </summary>
 public LxXlsOper()
 {
     excelMain = new Excel.ApplicationClass();
 }
        //生成Excel文件,以便打印
        private void button1_Click(object sender, System.EventArgs e)
        {
            int i = 0, j = 0;

            useExcel = true;
            excelApp = new Excel.ApplicationClass();
            Excel.Workbook  excelBook = excelApp.Workbooks.Add(1);
            Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];

            if (conn.ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("无可打印内容!", "提醒!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }

            try
            {
                //设置表头
                excelSheet.Cells[1, 1] = comboBox_School.Text + ":" + comboBox_Class_Type.Text + "-" + conn.Class_IDtoWhat(comboBox_Class.SelectedValue.ToString(), "Class_Name");
                excelSheet.Cells[2, 1] = "身份证号码";
                excelSheet.Cells[2, 2] = "姓名";
                excelSheet.Cells[2, 3] = "性别";
                excelSheet.Cells[2, 4] = "出生日期";
                excelSheet.Cells[2, 5] = "父亲";
                excelSheet.Cells[2, 6] = "母亲";
                excelSheet.Cells[2, 7] = "监护人";
                excelSheet.Cells[2, 8] = "联系电话";
                excelSheet.Cells[2, 9] = "家庭住址";

                excelSheet.Cells[2, 10] = "父亲职业";
                excelSheet.Cells[2, 11] = "父亲学历";
                excelSheet.Cells[2, 12] = "母亲职业";
                excelSheet.Cells[2, 13] = "母亲学历";

                excelSheet.Cells[2, 14] = "区县";
                excelSheet.Cells[2, 15] = "办事处";
                excelSheet.Cells[2, 16] = "居委会";

                //设置报表表头格式
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 16]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 16]).Font.Bold           = true;

                string str_Sql = "SELECT Student_ID As 身份证号码,Name As 姓名,Sex AS 性别,convert(char(10),Birthday,120) As 出生日期,Father AS 父亲,Mother AS 母亲,Keeper AS 监护人,StudentTel AS 联系电话,Student_Address AS 家庭住址,Father_Job AS 父亲职业,Father_XueLi AS 父亲学历,Mother_Job AS 母亲职业,Mother_XueLi AS 母亲学历,QuXian_Name AS 区县,Office_Name AS 办事处,Committee_Name AS 居委会 FROM View_StudentClass_Detail_City4 WHERE Class_ID=" + comboBox_Class.SelectedValue + " AND School_ID='" + comboBox_School.SelectedValue.ToString() + "'";
                conn.Fill(str_Sql);
                DataGrid1.SetDataBinding(conn.ds, "TableIn");
                //填充表中各单元格
                for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
                {
                    for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
                    {
                        excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
                    }
                }
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();

                excelApp.Visible = true;
            }
            catch
            {
                throw new Exception("Excel error");
            }
        }
Пример #25
0
        private void button2_Click(object sender, System.EventArgs e)
        {
            string str_School_ID, SchoolPassword;
            int    i;

            useExcel = true;
            excelApp = new Excel.ApplicationClass();
            Excel.Workbook  excelBook  = excelApp.Workbooks.Add(1);
            Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];

            if (textBox_openFile.Text == "")
            {
                MessageBox.Show("必须输入争取文件名!", "警告!",
                                MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }

            string Excelfileloc  = textBox_openFile.Text;
            string Excelfilename = conn.GetLastStr(Excelfileloc, "\\");
            string Excelfiletype = conn.GetLastStr(Excelfilename, ".").Trim();

            if (Excelfiletype != "xls")
            {
                MessageBox.Show("必须输入Excel文件!", "警告!",
                                MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }

            //创建一个数据链接
            string          strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Excelfileloc + ";Extended Properties=Excel 8.0";
            OleDbConnection myConn = new OleDbConnection(strCon);
            string          strCom = " SELECT * FROM [Sheet1$] ";

            myConn.Open();
            //打开数据链接,得到一个数据集
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);

            //创建一个 DataSet对象
            myDataSet = new DataSet();
            //f得到自己的DataSet对象
            myCommand.Fill(myDataSet, "[Sheet1$]");
            //关闭此数据链接
            myConn.Close();

            //取出各行数据,第二列学校名,第一列School_ID—>算出来有效的密码,存入myDataSet
            for (i = 0; i < myDataSet.Tables[0].Rows.Count; i++)
            {
                str_School_ID  = myDataSet.Tables[0].Rows[i][0].ToString();
                SchoolPassword = conn.ValidUploadPassword(str_School_ID);
                myDataSet.Tables[0].Rows[i][2] = SchoolPassword;
            }

            //逐行填入
            for (i = 0; i < myDataSet.Tables[0].Rows.Count; i++)
            {
                excelSheet.Cells[i, 1] = myDataSet.Tables[0].Rows[i][0].ToString();
                excelSheet.Cells[i, 2] = myDataSet.Tables[0].Rows[i][1].ToString();
                excelSheet.Cells[i, 3] = myDataSet.Tables[0].Rows[i][2].ToString();
            }
            excelApp.Visible = true;
        }
Пример #26
0
 //**********************************************************************
 /// <summary>
 /// Close the excel
 /// </summary>
 public void close()
 {
     xBook.Save();
     excelMain.Quit();
     excelMain = null;
 }
        private void button3_Click(object sender, System.EventArgs e)
        {
            if (conn.ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("无可打印内容!", "提醒!",
                                MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }

            int i, j = 0;

            useExcel = true;
            excelApp = new Excel.ApplicationClass();
            Excel.Workbook  excelBook  = excelApp.Workbooks.Add(1);
            Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];

            try
            {
                //设置表头
                excelSheet.Cells[1, 1]  = " 符合条件的学生:";
                excelSheet.Cells[2, 1]  = "学校名";
                excelSheet.Cells[2, 2]  = "年级";
                excelSheet.Cells[2, 3]  = "班级";
                excelSheet.Cells[2, 4]  = "身份证号码";
                excelSheet.Cells[2, 5]  = "姓名";
                excelSheet.Cells[2, 6]  = "性别";
                excelSheet.Cells[2, 7]  = "出生日期";
                excelSheet.Cells[2, 8]  = "父亲";
                excelSheet.Cells[2, 9]  = "母亲";
                excelSheet.Cells[2, 10] = "监护人";
                excelSheet.Cells[2, 11] = "联系电话";
                excelSheet.Cells[2, 12] = "家庭住址";

                excelSheet.Cells[2, 13] = "父亲职业";
                excelSheet.Cells[2, 14] = "父亲学历";
                excelSheet.Cells[2, 15] = "母亲职业";
                excelSheet.Cells[2, 16] = "母亲学历";

                //设置表头格式
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 16]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 16]).Font.Bold           = true;

                //填充表中各单元格
                for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
                {
                    for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
                    {
                        excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
                    }
                }

                //设置报表表格为最适应宽度
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
                excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();

                excelApp.Visible = true;
            }
            catch
            {
                throw new Exception("Excel error");
            }
        }
Пример #28
0
 //**********************************************************************
 /// <summary>
 /// Close the excel
 /// </summary>
 public void close()
 {
     xBook.Save();
     excelMain.Quit();
     excelMain = null;
 }