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();
    ArrayList array_2 = new ArrayList();
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    public void getnum()
    {
        if (!TxtBeginTime.Text.ToString().Equals("") && !TxtEndTime.Text.ToString().Equals(""))
        {
            array_1 = new ArrayList();
            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())
                {
                    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();
                    array_2.Add(Convert.ToString(dr["emp_name"]));
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["vacation_begin"].ToString();
                    array_2.Add(Convert.ToString(dr["vacation_begin"]));
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["vacation_end"].ToString();
                    array_2.Add(Convert.ToString(dr["vacation_end"]));
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["days"].ToString() + "天";
                    array_2.Add(Convert.ToString(td.Text));
                    tr.Cells.Add(td);
                    Table1.Rows.Add(tr);
                    array_1.Add(array_2);
                }
                dr.Close();
            }
            else if (DrpDownType.SelectedValue.ToString().Equals("4"))
            {
                array_1 = new ArrayList();
                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())
                {
                    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();
                    array_2.Add(Convert.ToString(dr["emp_name"]));
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["business_begin"].ToString();
                    array_2.Add(Convert.ToString(dr["business_begin"]));
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["business_end"].ToString();
                    array_2.Add(Convert.ToString(dr["business_end"]));
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["days"].ToString() + "天";
                    array_2.Add(Convert.ToString(td.Text));
                    tr.Cells.Add(td);
                    Table1.Rows.Add(tr);
                    array_1.Add(array_2);
                }
                dr.Close();
            }
            else
            {
                array_1 = new ArrayList();
                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())
                {
                    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();
                    array_2.Add(Convert.ToString(dr["emp_name"]));
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["begin_time"].ToString();
                    array_2.Add(Convert.ToString(dr["begin_time"]));
                    tr.Cells.Add(td);
                    td = new TableCell();
                    td.BorderWidth = Unit.Pixel(1);
                    td.Text = dr["end_time"].ToString();
                    array_2.Add(Convert.ToString(dr["end_time"]));
                    tr.Cells.Add(td);
                    if (DrpDownType.SelectedValue.ToString().Equals("2"))
                    {
                        td = new TableCell();
                        td.BorderWidth = Unit.Pixel(1);
                        td.Text = dr["days"].ToString() + "天";
                        array_2.Add(Convert.ToString(td.Text));
                    }
                    else if (DrpDownType.SelectedValue.ToString().Equals("3"))
                    {
                        td = new TableCell();
                        td.BorderWidth = Unit.Pixel(1);
                        td.Text = dr["hours"].ToString() + "小時";
                        array_2.Add(Convert.ToString(td.Text));
                    }
                    tr.Cells.Add(td);
                    Table1.Rows.Add(tr);
                    array_1.Add(array_2);
                }
                dr.Close();
            }
            con.Close();
        }
        else
        {
            Response.Write("<script>alert('日期不能為空！')</script>");
        }
    }
    public void getexcel()
    {
        ExcelApplication exc = new ExcelApplication();
         exc.Visible = false;
        exc.UserControl = false;
        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 = "差假統計表";
        sh.Cells[2, 1] = "單號";
        sh.Cells[2, 2] = "部門";
        sh.Cells[2, 3] = "姓名";
        sh.Cells[2, 4] = "日期起";
        sh.Cells[2, 5] = "日期迄";
        sh.Cells[2, 6] = "總計";
        rang.HorizontalAlignment = XlHAlign.xlHAlignCenter;
        int n=3;
        int m=1;
        foreach(ArrayList i in array_1)
        {
            m = 1;
            foreach (string j in i)
            {
                Range rang1 = sh.get_Range(sh.Cells[n,m],sh.Cells[n,m]);
                rang1.Value2 = j;
                m++;
            }
            n++;
        }
        exc.Visible = true;
        exc.UserControl = true;
        ReleaseCom(sh);
        ReleaseCom(shs);
        ReleaseCom(workbook);
        ReleaseCom(workbooks);
        ReleaseCom(exc);
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        getnum();
    }
    private void ReleaseCom(object pComObj)
    {
        try
        {
            if (pComObj != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(pComObj);
            }
        }
        catch
        {
        }
        finally
        {
            pComObj = null;
        }
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        getnum();
        getexcel();
    }
}

						