利用office导出Excel

来源：互联网发布：酷狗总提示网络异常编辑：程序博客网时间：2024/05/16 02:28

---------------------------------------------------------------------------------------------------------------主要类-------------------------------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.IO;
using System.Collections;
using Excel = Microsoft.Office.Interop.Excel;
using System.Configuration;
using System.Runtime.InteropServices;
namespace LDNSTest
{
    public class ExcelExport
    {
        public ExcelExport() { }
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
        public string ToExcel(string tempEFilePath, string excelTemplateDPath, string excelTemplateName)
        {
            string strMess = "";
            string tempFileName = "";
            string excelTemplate = excelTemplateDPath + "\\" + excelTemplateName + ".xls";
            Excel.Application app = null; //EXCEL对象
            Excel.Workbooks workbooks; //工作簿集合
            Excel._Workbook workbook; //当前工作簿
            Excel.Sheets sheets; //SHEET页集合
            Excel._Worksheet worksheet; //当前SHEET页

            #region 验证模板路径及名称是否存在
            //验证EXCEL模板文件是否存在
            if (!File.Exists(excelTemplate))
            {
                strMess = "配置文件中指定的模板文件不存在";
                return strMess;
            }
            //验证EXCEL临时文件夹是否存在
            if (!File.Exists(tempEFilePath))
            {
                Directory.CreateDirectory(tempEFilePath);
            }
            #endregion
            #region 从配置文件中取列名为填充数据做准备
            ArrayList list = new ArrayList();

            #endregion
            try
            {
                #region 启动excel进程并加载模板
                //启动EXCEL进程
                app = new Excel.Application();
                if (app == null)
                {
                    strMess = "Excel进程启动出错,请确认是否引用EXCEL组件";
                    return strMess;
                }
                app.Visible = false;
                app.UserControl = true;
                app.DisplayAlerts = false;
                //加载读取模板
                workbooks = app.Workbooks;
                workbook = workbooks.Add(excelTemplate);
                sheets = workbook.Worksheets;
                worksheet = (Excel._Worksheet)sheets.get_Item(1);
                #endregion

                #region 填充数据
                for (int i = 2; i < 6; i++)
                {
                    worksheet.Cells[i, 1] = "Emon任务";
                    worksheet.Cells[i, 2] = "343";
                    worksheet.Cells[i, 3] = "233";
                }
                #endregion

                #region 保存临时文件
                worksheet.Columns.AutoFit(); //设置自动列宽
                try
                {
                    Object Missing = System.Reflection.Missing.Value;
                    tempFileName = tempEFilePath + "\\" + excelTemplateName + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                    worksheet.SaveAs(tempFileName, Missing, Missing, Missing,
                            Missing, Missing, Excel.XlSaveAsAccessMode.xlNoChange,
                            Missing, Missing, Missing);
                    return strMess;
                }
                catch (Exception ex)
                {
                    strMess = "EXCEL文件已填充完毕,但在保存时发生错误";
                    return strMess;
                }
                #endregion
            }
            catch (Exception ex)
            {
                strMess = ex.Message;
                return strMess;
            }
            finally
            {
                ExcelDispose(app);
                File.Delete(tempFileName); //清除临时文件
            }
        }
        /// <summary>
        /// 关闭Excel进程
        /// </summary>
        /// <param name="CurExcel"></param>
        protected void ExcelDispose(Excel._Application CurExcel)
        {
            try
            {
                if (CurExcel != null)
                {
                    CurExcel.Workbooks.Close();
                    CurExcel.Quit();
                    #region 强行杀死打开的Excel进程
                    IntPtr excelIPtr = new IntPtr(CurExcel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口
                    int proID = 0;
                    GetWindowThreadProcessId(excelIPtr, out proID); //得到本进程唯一标志k
                    System.Diagnostics.Process pro = System.Diagnostics.Process.GetProcessById(proID); //得到对进程k的引用
                    pro.Kill(); //关闭进程k
                    #endregion
                    CurExcel = null;
                }
            }
            catch (Exception ex)
            {
                throw ex;// "在释放内存时发生错误：" + ex.ToString();
            }
        }

}
}

----------------------------------------------------------------------------------------------调用方法-----------------------------------------------------------------------------------------------------------------------------

string tempEFilePath = ""; //EXCEL临时文件保存服务器物理存放路径
            string excelTemplateDPath=""; //EXCEL模板默认服务器物理存放路径
            string excelTemplateName = "";//EXCEL模板名称
            excelTemplateDPath = Server.MapPath(ConfigurationManager.AppSettings["ExcelTemplateDPath"]);
            excelTemplateName =ConfigurationManager.AppSettings["ExcelTemplateName"].ToString();
            tempEFilePath = ConfigurationManager.AppSettings["TempEFilePath"].ToString();

            ExcelExport ee = new ExcelExport();
            ee.ToExcel(tempEFilePath, excelTemplateDPath, excelTemplateName);

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------webConfig中得配置----------------------------------------------------------------------------------------------------------