OA系统

开发平台：
ASP/ASPX

report_approvel.aspx.cs：源码内容
							﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop;
using System.Reflection;
using System.Runtime.InteropServices;
using ExcelApplication = Microsoft.Office.Interop.Excel.Application;
using Label = System.Web.UI.WebControls.Label;
public partial class web_123_report_approvel : System.Web.UI.Page
{
    ArrayList array_1 = new ArrayList();
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (!TxtBeginTime.Text.ToString().Equals("") &&!TxtEndTime.Text.ToString().Equals(""))
        {
            SqlConnection con = dbConnection.getConnection();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            if (DrpDownType.SelectedValue.ToString().Equals("1"))
            
            {
                cmd.CommandText = "select f.form_id,e.emp_name,d.name,f.vacation_begin,f.vacation_end,f.days,f.off_type from oa_tw_approvel_form" +
                    " as f inner join oa_employee as e on f.emp_id=e.id inner join oa_department as d on e.department_id=d.id where" +
                    " vacation_begin>='" + TxtBeginTime.Text + "' and  vacation_begin<='" + TxtEndTime.Text + "' and off_type<>1 order by name,emp_name";
                cmd.Connection = con;
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    ArrayList array_2 = new ArrayList();
                    Table1.Visible = true;
                    TableRow tr = new TableRow();
                    TableCell td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["form_id"].ToString();
                    array_2.Add(Convert.ToString(dr["form_id"]));
                    tr.Cells.Add(td);
                     td = new TableCell();
                     td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["name"].ToString();
                    array_2.Add(Convert.ToString(dr["name"]));
                    tr.Cells.Add(td);
                     td = new TableCell();
                     td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["emp_name"].ToString();
                    tr.Cells.Add(td);
                     td = new TableCell();
                     td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["vacation_begin"].ToString();
                    tr.Cells.Add(td);
                     td = new TableCell();
                     td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["vacation_end"].ToString();
                    tr.Cells.Add(td);
                     td = new TableCell();
                     td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["days"].ToString()+"天";
                    tr.Cells.Add(td);
                    Table1.Rows.Add(tr);
                    array_1.Add(array_2);
                }
                dr.Close();
            }
            else if (DrpDownType.SelectedValue.ToString().Equals("4"))
            {
                cmd.CommandText = "select f.form_id,e.emp_name,d.name,f.business_begin,f.business_end,f.days,f.off_type from oa_tw_approvel_form" +
                        " as f inner join oa_employee as e on f.emp_id=e.id inner join oa_department as d on e.department_id=d.id where" +
                        " vacation_begin>='" + TxtBeginTime.Text + "' and  vacation_begin<='" + TxtEndTime.Text + "' and off_type=1 order by name,emp_name";
                cmd.Connection = con;
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Table1.Visible = true;
                    TableRow tr = new TableRow();
                    TableCell td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["form_id"].ToString();
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["name"].ToString();
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["emp_name"].ToString();
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["business_begin"].ToString();
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["business_end"].ToString();
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["days"].ToString() + "天";
                    tr.Cells.Add(td);
                    Table1.Rows.Add(tr);
                }
                dr.Close();
            }
            else
            {
                if (DrpDownType.SelectedValue.ToString().Equals("2"))
                {
                    cmd.CommandText = "select f.form_id,e.emp_name,d.name,f.begin_time,f.end_time,f.days from oa_ch_business_form " +
                        " as f inner join oa_employee as e on f.emp_id=e.id inner join oa_department as d on e.department_id=d.id where" +
                        " begin_time>='" + TxtBeginTime.Text + "' and  begin_time<='" + TxtEndTime.Text + "' order by name,emp_name";
                }
                else if (DrpDownType.SelectedValue.ToString().Equals("3"))
                {
                    cmd.CommandText = "select f.form_id,e.emp_name,d.name,f.begin_time,f.end_time,f.hours from oa_ch_off_form " +
                        " as f inner join oa_employee as e on f.emp_id=e.id inner join oa_department as d on e.department_id=d.id where" +
                        " begin_time>='" + TxtBeginTime.Text + "' and  begin_time<='" + TxtEndTime.Text + "' order by name,emp_name";
                }
                cmd.Connection = con;
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Table1.Visible = true;
                    TableRow tr = new TableRow();
                    TableCell td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["form_id"].ToString();
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["name"].ToString();
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["emp_name"].ToString();
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["begin_time"].ToString();
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["end_time"].ToString();
                    tr.Cells.Add(td);
                    if (DrpDownType.SelectedValue.ToString().Equals("2"))
                    {
                        td = new TableCell();
                        td.BorderWidth = Unit.Pixel(1);
                        td.Text = dr["days"].ToString() + "天";
                    }
                    else if (DrpDownType.SelectedValue.ToString().Equals("3"))
                    {
                        td = new TableCell();
                        td.BorderWidth = Unit.Pixel(1);
                        td.Text = dr["hours"].ToString() + "小時";
                    }
                    tr.Cells.Add(td);
                    Table1.Rows.Add(tr);
                }
                dr.Close();
            }
            con.Close();
           
        }
        else
        {
            Response.Write("<script>alert('日期不能為空！')</script>");
        }
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        foreach (ArrayList i in array_1)
        {
            foreach (string j in i)
            {
                Response.Write(j);
            }
        }
        ExcelApplication exc = new ExcelApplication();
        Workbooks workbooks = exc.Workbooks;
        _Workbook workbook = workbooks.Add(true);
        Sheets shs = workbook.Sheets;
        _Worksheet sh = (_Worksheet)shs.get_Item(1);
        Range rang = sh.get_Range(sh.Cells[1, 1], sh.Cells[1, 11]);
        rang.Merge(true);
        rang.Value2 = "差假統計表";
        rang.HorizontalAlignment = XlHAlign.xlHAlignCenter;
       
        for (int i = 0; i < Table1.Rows.Count; i++)
        {
            for (int j = 0; j < Table1.Rows[i].Cells.Count; j++)
            {
                Range rang1 = sh.get_Range(sh.Cells[i+1, j+2], sh.Cells[i+1, j+2]);
                rang1.Value2 = Table1.Rows[i].Cells[j].Text;
                
            }
        }
      /*  int n=3;
        int m=1;
        foreach(ArrayList i in array_1)
        {
            foreach (string j in i)
            {
                Response.Write(j);
                Range rang1 = sh.get_Range(sh.Cells[n,m],sh.Cells[n,m]);
                rang1.Value2 = j;
                m++;
            }
            n++;
        }*/
        exc.Visible = true;
        exc.UserControl = true;
    }
}

						