asp.Net Excelクラスライブラリコード共有のエクスポート

12114 ワード

 
  
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Web;
using Excel = Microsoft.Office.Interop.Excel;

///


///ExcelClass
///

public class ExcelClass
{
    ///
    /// ExcelClass
    ///

    public ExcelClass()
    {
        this.m_objExcel = new Excel.Application();
    }
    ///
    /// ExcelClass
    ///

    /// Excel.Application
    public ExcelClass(Excel.Application objExcel)
    {
        this.m_objExcel = objExcel;
    }

    ///


    ///
    ///

    private string AList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    ///


    ///
    ///

    ///
    ///
    ///
    public string GetAix(int x, int y)
    {
        char[] AChars = AList.ToCharArray();
        if (x >= 26) { return ""; }
        string s = "";
        s = s + AChars[x - 1].ToString();
        s = s + y.ToString();
        return s;
    }

    ///


    /// 1
    ///

    ///
    ///
    /// (CENTER、LEFT、RIGHT)
    ///
    public void setValue(int y, int x, string align, string text)
    {
        Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
        range.set_Value(miss, text);
        if (align.ToUpper() == "CENTER")
        {
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
        }
        if (align.ToUpper() == "LEFT")
        {
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
        }
        if (align.ToUpper() == "RIGHT")
        {
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
        }


    }

    ///


    /// 2
    ///

    ///
    ///
    ///
    public void setValue(int y, int x, string text)
    {
        Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
        range.set_Value(miss, text);
    }

    ///


    /// 3
    ///

    ///
    ///
    ///
    ///
    ///
    public void setValue(int y, int x, string text, System.Drawing.Font font, System.Drawing.Color color)
    {
        this.setValue(x, y, text);
        Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
        range.Font.Size = font.Size;
        range.Font.Bold = font.Bold;
        range.Font.Color = color;
        range.Font.Name = font.Name;
        range.Font.Italic = font.Italic;
        range.Font.Underline = font.Underline;
    }

    ///


    ///
    ///

    ///
    public void insertRow(int y)
    {
        Excel.Range range = sheet.get_Range(GetAix(1, y), GetAix(25, y));
        range.Copy(miss);
        range.Insert(Excel.XlDirection.xlDown, miss);
        range.get_Range(GetAix(1, y), GetAix(25, y));
        range.Select();
        sheet.Paste(miss, miss);


    }

    ///


    ///
    ///

    public void past()
    {
        string s = "a,b,c,d,e,f,g";
        sheet.Paste(sheet.get_Range(this.GetAix(10, 10), miss), s);
    }
    ///
    ///
    ///

    ///
    ///
    ///
    ///
    ///
    public void setBorder(int x1, int y1, int x2, int y2, int Width)
    {
        Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), miss);


        ((Excel.Range)range.Cells[x1, y1]).ColumnWidth = Width;
    }
    public void mergeCell(int x1, int y1, int x2, int y2)
    {
        Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
        range.Merge(true);
    }

    public Excel.Range getRange(int x1, int y1, int x2, int y2)
    {
        Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
        return range;
    }

    private object miss = Missing.Value; // OLENULL
    private Excel.Application m_objExcel;//Excel
    private Excel.Workbooks m_objBooks;//
    private Excel.Workbook m_objBook;//
    private Excel.Worksheet sheet;//

    public Excel.Worksheet CurrentSheet
    {
        get
        {
            return sheet;
        }
        set
        {
            this.sheet = value;
        }
    }

    public Excel.Workbooks CurrentWorkBooks
    {
        get
        {
            return this.m_objBooks;
        }
        set
        {
            this.m_objBooks = value;
        }
    }

    public Excel.Workbook CurrentWorkBook
    {
        get
        {
            return this.m_objBook;
        }
        set
        {
            this.m_objBook = value;
        }
    }
    ///


    /// Excel
    ///

    ///
    public void OpenExcelFile(string filename)
    {
        UserControl(false);

        m_objExcel.Workbooks.Open(filename, miss, miss, miss, miss, miss, miss, miss,
                               miss, miss, miss, miss, miss, miss, miss);

        m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;

        m_objBook = m_objExcel.ActiveWorkbook;
        sheet = (Excel.Worksheet)m_objBook.ActiveSheet;
    }
    public void UserControl(bool usercontrol)
    {
        if (m_objExcel == null) { return; }
        m_objExcel.UserControl = usercontrol;
        m_objExcel.DisplayAlerts = usercontrol;
        m_objExcel.Visible = usercontrol;
    }
    public void CreateExceFile()
    {
        UserControl(false);
        m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
        m_objBook = (Excel.Workbook)(m_objBooks.Add(miss));
        sheet = (Excel.Worksheet)m_objBook.ActiveSheet;
    }
    public void SaveAs(string FileName)
    {
         m_objBook.SaveAs(FileName, miss, miss, miss, miss,
         miss, Excel.XlSaveAsAccessMode.xlNoChange,
         Excel.XlSaveConflictResolution.xlLocalSessionChanges,
         miss, miss, miss, miss);
        //m_objBook.Close(false, miss, miss);
    }
    public void ReleaseExcel()
    {
        m_objExcel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objExcel);
        System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBooks);
        System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject((object)sheet);
        m_objExcel = null;
        m_objBooks = null;
        m_objBook = null;
        sheet = null;
        GC.Collect();
    }

    public bool KillAllExcelApp()
    {
        try
        {
            if (m_objExcel != null) // isRunning xlApp flag.
            {
                m_objExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
                // COM , 1
                //System.Diagnostics.Process theProc;
                foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
                {
                    // 。 ... excel ,
                    // EXCEL.EXE , :p
                    if (theProc.CloseMainWindow() == false)
                    {
                        theProc.Kill();
                    }
                }
                m_objExcel = null;
                return true;
            }
        }
        catch
        {
            return false;
        }
        return true;
    }
}

   ///


    ///
    ///

    ///
    ///
    protected void Sendbu_Click(object sender, EventArgs e)
    {
        try
        {         
            //
            DataTable Duser = EduOA.DBUtility.DbHelperSQL.Query("select count(*) as count,d.Id as DId FROM OA_User u,OA_Department d where u.DepartmentID=d.Id  group by d.Id").Tables[0];

            ExcelClass Ec = new ExcelClass();// Excel

            int Ycount = 1;

            Ec.CreateExceFile();// Excel

            Ec.setValue(Ycount, 1, "CENTER", " ");
            Ec.setValue(Ycount, 2, "CENTER", " ");
            Ec.setValue(Ycount, 3, "CENTER", " ");
            Ec.setValue(Ycount, 4, "CENTER", " ");
            Ec.setValue(Ycount, 5, "CENTER", " ");
            Ec.setValue(Ycount, 6, "CENTER", " ");
            Ec.setValue(Ycount, 7, "CENTER", " ");
            Ec.setBorder(1, 1, 1, 1, 50);
            Ec.setBorder(1, 2, 2, 2, 20);
            Ec.setBorder(1, 5, 5, 5, 20);
            Ec.setBorder(1, 6, 6, 6, 20);
            Ec.setBorder(1, 7, 7, 7, 20);

            for (int i = 0; i < Duser.Rows.Count; i++)
            {
                Ycount += 1;
                Ec.setValue(Ycount, 1, "CENTER", Common.DeleteHtml(Getdept(Duser.Rows[i]["count"], Duser.Rows[i]["DId"])));
                DataTable dtuser = GetData(Duser.Rows[i]["DId"]);
                for (int k = 0; k < dtuser.Rows.Count; k++)
                {
                    Ec.setValue(Ycount, 2, "CENTER", dtuser.Rows[k]["TrueName"].ToString());
                    Ec.setValue(Ycount, 3, "CENTER", dtuser.Rows[k]["sex"].ToString());
                    Ec.setValue(Ycount, 4, "CENTER", dtuser.Rows[k]["PositionId"].ToString());
                    Ec.setValue(Ycount, 5, "CENTER", dtuser.Rows[k]["Telephone"].ToString());
                    Ec.setValue(Ycount, 6, "CENTER", dtuser.Rows[k]["Mobile"].ToString());
                    Ec.setValue(Ycount, 7, "CENTER", dtuser.Rows[k]["Email"].ToString());
                    Ycount += 1;
                }
            }
            string path = Server.MapPath("Contactfiles\\");
            Ec.SaveAs(path+" .xlsx");

            //******* Excel ***********
            Ec.ReleaseExcel();

            Response.Redirect("Contactfiles/ .xlsx");           
        }
        catch (Exception ex)
        {
            PageError(" !"+ex.ToString(),"");
        }
    }