点击回首页
我的浏览记录 | | 帮助?
当前位置:
首页>> 学教实践>> HFC个人财务管理系统源码>> 源文件浏览
[免费版 Free] WinForm,下载次数:1537 次 | 关键字: 财务管理 报表 WebForm XML 序列化 反序列化

源码截图

源码目录树

当前路径:CreateExcelFile.cs     using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Reflection;
using System.IO;
using System.Data;
using Microsoft.Office.Interop.Excel;


namespace FinancialManager
{
    public static class CreateExcelFile
    {

        private static Object missing;//参数占位
        private static Application m_objExcel;//Excel Com对象
        private static Workbooks m_objWorkBooks; //Excel 工作簿对象(复数)
        private static Workbook m_objWorkBook; //Excel 工作簿对象
        private static Sheets m_objWorkSheets; //Excel 工作页对象(复数)
        private static Worksheet m_objWorkSheet;//Excel 工作页对象
        /// <summary>
        /// 创建excel并写入表格
        /// </summary>
        /// <param name="dt">要插入的表格</param>
        /// <param name="fileName">要创建excel的路径(包括excel文件名)</param>
        public static void CreateExcel(List<System.Data.DataTable> lsdt, string fileName)
        {
            //System.Diagnostics.Process[] arrProcesses;
            //arrProcesses = System.Diagnostics.Process.GetProcessesByName("Excel");
            //foreach (System.Diagnostics.Process myProcess in arrProcesses)
            //{
            //    //myProcess.Kill();
            //}
            missing = Missing.Value;
            m_objExcel = new Application();
            m_objWorkBooks = m_objExcel.Workbooks;
            m_objWorkBook = m_objWorkBooks.Add(true);
            m_objWorkSheets = m_objWorkBook.Sheets;
            m_objWorkSheet = (Worksheet)m_objExcel.ActiveSheet;
            m_objWorkSheet.Name = GetSheetName(0, lsdt);

            for (int i = 1; i <= lsdt.Count; i++)
            {
                if (i >= 2)
                {
                    m_objWorkSheet = (Worksheet)m_objWorkBook.Worksheets.Add(missing, missing, missing, missing);
                    m_objWorkSheet.Name = GetSheetName(i - 1, lsdt) + i.ToString();
                }
                FillSheet(1, lsdt[i - 1]);
            }
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
            }
            m_objWorkBook.SaveAs(fileName, missing, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);

            //m_objExcel = null;
            Close();
            Dispose();
        }
        private static string GetSheetName(int i, List<System.Data.DataTable> lsdt)
        {
            if (String.IsNullOrEmpty(lsdt[i].TableName))
            {
                lsdt[i].TableName = "Sheet";
            }
            if (i == 0)
            {
                return lsdt[i].TableName;
            }
            else
            {
                string tableName = lsdt[i].TableName;
                int temp = 0;
                for (int j = 0; j < i; j++)
                {
                    if (lsdt[j].TableName == tableName)
                    {
                        temp++;
                    }
                }
                if (temp != 0)
                {
                    return tableName + temp.ToString();
                }
                else
                {
                    return tableName;
                }
            }
        }
        private static void FillSheet(int idx, System.Data.DataTable dt)
        {
            m_objWorkSheet = (Worksheet)m_objWorkSheets[idx];

            int intFeildCount = dt.Columns.Count;
            for (int col = 0; col < intFeildCount; col++)
            {
                m_objWorkSheet.Cells[1, col + 1] = dt.Columns[col].ToString();
            }
            for (int intRowCount = 0; intRowCount < dt.Rows.Count; intRowCount++)
            {
                for (int intCol = 0; intCol < dt.Columns.Count; intCol++)
                {
                    m_objWorkSheet.Cells[intRowCount + 2, intCol + 1] = dt.Rows[intRowCount][intCol].ToString();
                }
            }
            //设置工作页自适应宽度
            m_objWorkSheet.Columns.AutoFit();
        }
        /// <summary>
        /// 创建excel并写入表格
        /// </summary>
        /// <param name="dt">要插入的表格</param>
        /// <param name="fileName">要创建excel的路径(包括excel文件名)</param>
        public static void CreateExcel(System.Data.DataTable dt, string fileName)
        {
            List<System.Data.DataTable> lsdt = new List<System.Data.DataTable>();
            lsdt.Add(dt);
            CreateExcel(lsdt, fileName);
            //System.Diagnostics.Process[] arrProcesses;
            //arrProcesses = System.Diagnostics.Process.GetProcessesByName("Excel");
            //foreach (System.Diagnostics.Process myProcess in arrProcesses)
            //{
            //    myProcess.Kill();
            //}
            //missing = Missing.Value;
            //m_objExcel = new Application();
            //m_objWorkBooks = m_objExcel.Workbooks;
            //m_objWorkBook = m_objWorkBooks.Add(true);
            //m_objWorkSheets = m_objWorkBook.Sheets; 
            //m_objWorkSheet = (Worksheet)m_objWorkSheets[1];

            //int intFeildCount = dt.Columns.Count;
            //for (int col = 0; col < intFeildCount; col++)
            //{
            //    m_objWorkSheet.Cells[1, col + 1] = dt.Columns[col].ToString();
            //}
            //for (int intRowCount = 0; intRowCount < dt.Rows.Count; intRowCount++)
            //{
            //    for (int intCol = 0; intCol < dt.Columns.Count; intCol++)
            //    {
            //        m_objWorkSheet.Cells[intRowCount + 2, intCol + 1] = dt.Rows[intRowCount][intCol].ToString();
            //    }
            //}
            ////设置工作页自适应宽度
            //m_objWorkSheet.Columns.AutoFit();

            //if (File.Exists(fileName))
            //{
            //    File.Delete(fileName);
            //}
            //m_objWorkBook.SaveAs(fileName, missing, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);

            //m_objExcel = null;
            //Close();
            //Dispose();       
        }
        /// <summary>
        /// 释放所引用的COM对象。注意:这个过程一定要执行。
        /// </summary>
        private static void Dispose()
        {
            ReleaseObj(m_objWorkSheets);
            ReleaseObj(m_objWorkBook);
            ReleaseObj(m_objWorkBooks);
            ReleaseObj(m_objExcel);
            System.GC.Collect();
            System.GC.WaitForPendingFinalizers();
        }
        /// <summary>
        /// 释放对象,内部调用
        /// </summary>
        /// <param name="o"></param>
        private static void ReleaseObj(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch { }
            finally { o = null; }
        }
        /// <summary>
        /// 关闭应用程序
        /// </summary>
        private static void Close()
        {
            m_objWorkBook.Close(false, missing, missing);
            //m_objExcel.Quit();
        }

    }
}
关于我们 | 顾问团队 | 发展历程 | 联系我们 | 源码上传
联系电话(Tel):4008-010-151(免长途)
地址:北京市海淀区大恒科技大厦五层 邮编:100080
Floor 5th,Daheng Building,Zhongguancun,Beijing,China,100080
51Aspx.com 版权所有 CopyRight © 2006-2015. 京ICP备09089570号 | 京公网安备11010702000869号
分享该页面
关闭侧边栏