发布：2016/8/24 8:53:58作者：管理员 来源：本站 浏览次数：945

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Runtime.InteropServices;
using System.Configuration;
using System.Reflection;
using System.ComponentModel;
using System.IO;
using System.Web;
using System.Data.OleDb;


namespace Business.Common
{
    public class ExcelOperate
    {
        //[DllImport("User32.dll", CharSet = CharSet.Auto)]
        //public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
        /// <summary>
        /// 获取导出路径
        /// </summary>
        public string filepath = ConfigurationManager.AppSettings["ExcelPath"];


        /// <summary>
        /// 获取下载地址
        /// </summary>
        public string fileUrl = ConfigurationManager.AppSettings["ExcelUrl"];


        #region 单例
        /// <summary>
        /// 同步体
        /// </summary>
        private static object mSyn = new object();


        /// <summary>
        /// 唯一实例
        /// </summary>   
        private static ExcelOperate mInstance;


        /// <summary>
        /// 获取对象
        /// </summary>
        public static ExcelOperate Instance
        {
            get
            {
                // 非空判断
                if (mInstance == null)
                {
                    // 锁定
                    lock (mSyn)
                    {
                        // 非空判断
                        if (mInstance == null)
                        {
                            mInstance = new ExcelOperate();
                        }
                    }
                }
                return mInstance;
            }
        }


        /// <summary>
        /// 私有构造函数
        /// </summary>
        private ExcelOperate()
        {
        }
        #endregion 单例 结束


        /// <summary>
        /// 将数据集中的数据导出到EXCEL文件
        /// </summary>
        /// <param name="dataSet">输入数据集</param>
        /// <param name="isShowExcle">是否显示该EXCEL文件</param>
        /// <returns>返回下载地址或错误消息</returns>
        /*public string DataTableToExcel(System.Data.DataTable dt,string fileName)
        {
            string url = "";
            
            int rowNumber = dt.Rows.Count;//不包括字段名
            int columnNumber = dt.Columns.Count;
            int colIndex = 0;
            if (rowNumber == 0)
            {
                return "没有数据!";
            }


            Application excel = new Application();  
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            Workbook workbook = null;  
            Worksheet worksheet = null;
            Range range;
            workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            worksheet = (Worksheet)workbook.Worksheets[1];
  
            //设置对象不可见  
            excel.Visible = false;
            excel.DisplayAlerts = false;
            try
            {
                //生成字段名称 
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    excel.Cells[1, colIndex] = col.ColumnName;
                }


                object[,] objData = new object[rowNumber, columnNumber];


                for (int r = 0; r < rowNumber; r++)
                {
                    for (int c = 0; c < columnNumber; c++)
                    {
                        objData[r, c] = dt.Rows[r][c];
                    }
                }


                // 写入Excel,第一行写列名，从第二行开始写数据
                range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];
                range.NumberFormat = "@";//设置单元格为文本格式
                range.Value2 = objData;


                //保存工作表 
                fileName = fileName + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
                workbook.SaveAs(filepath + fileName, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                workbook.Close(false, miss, miss);
                excel.Workbooks.Close();
                excel.Quit();


                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel.Workbooks);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                GC.Collect();
                GC.WaitForPendingFinalizers();
                url = fileUrl + fileName;
            }
            catch (Exception e)
            {
                url = e.Message;
            }
            finally 
            {
                if (excel != null)
                {
                    //关闭进程把程序也关掉了，待修改测试
                    //KillSpecialExcel(excel);
                }  
            }
            return url;
        }


        /// <summary>  
        /// 导出
        /// </summary>  
        /// <param name="list"><列名，数据></param>  
        /// <param name="filepath"></param>  
        /// <returns></returns>  
        public bool NewExport(List<DictionaryEntry> list, string fileName)
        {
            bool bSuccess = true;
            Application appexcel = new Application();
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            appexcel = new Application();
            Workbook workbookdata = null;
            Worksheet worksheetdata = null;
            Range rangedata;


            workbookdata = appexcel.Workbooks.Add();


            //设置对象不可见  
            appexcel.Visible = false;
            appexcel.DisplayAlerts = false;
            try
            {
                foreach (var lv in list)
                {
                    var keys = lv.Key as List<string>;
                    var values = lv.Value as List<IList<object>>;
                    worksheetdata = (Worksheet)workbookdata.Worksheets.Add(miss, workbookdata.ActiveSheet);


                    for (int i = 0; i < keys.Count - 1; i++)
                    {
                        //给工作表赋名称  
                        worksheetdata.Name = keys[0];//列名的第一个数据位表名  
                        worksheetdata.Cells[1, i + 1] = keys[i + 1];
                    }


                    //因为第一行已经写了表头，所以所有数据都应该从a2开始 
                    rangedata = worksheetdata.get_Range("a2", miss);
                    Range xlrang = null;


                    //irowcount为实际行数，最大行  
                    int irowcount = values.Count;
                    int iparstedrow = 0, icurrsize = 0;


                    //ieachsize为每次写行的数值，可以自己设置  
                    int ieachsize = 10000;


                    //icolumnaccount为实际列数，最大列数  
                    int icolumnaccount = keys.Count - 1;


                    //在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数  
                    object[,] objval = new object[ieachsize, icolumnaccount];
                    icurrsize = ieachsize;


                    while (iparstedrow < irowcount)
                    {
                        if ((irowcount - iparstedrow) < ieachsize)
                            icurrsize = irowcount - iparstedrow;


                        //用for循环给数组赋值  
                        for (int i = 0; i < icurrsize; i++)
                        {
                            for (int j = 0; j < icolumnaccount; j++)
                            {
                                var v = values[i + iparstedrow][j];
                                objval[i, j] = v != null ? v.ToString() : "";
                            }
                        }
                        string X = "A" + ((int)(iparstedrow + 2)).ToString();
                        string col = "";
                        if (icolumnaccount <= 26)
                        {
                            col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
                        }
                        else
                        {
                            col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
                        }
                        xlrang = worksheetdata.get_Range(X, col);
                        xlrang.NumberFormat = "@";
                        // 调用range的value2属性，把内存中的值赋给excel  
                        xlrang.Value2 = objval;
                        iparstedrow = iparstedrow + icurrsize;
                    }
                }
                ((Worksheet)workbookdata.Worksheets["Sheet1"]).Delete();
                ((Worksheet)workbookdata.Worksheets["Sheet2"]).Delete();
                ((Worksheet)workbookdata.Worksheets["Sheet3"]).Delete();
                //保存工作表  
                fileName = fileName + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
                workbookdata.SaveAs(filepath + fileName, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                workbookdata.Close(false, miss, miss);
                appexcel.Workbooks.Close();
                appexcel.Quit();


                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookdata);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appexcel.Workbooks);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appexcel);
                GC.Collect();
                GC.WaitForPendingFinalizers();


            }
            catch (Exception ex)
            {
                //ErrorMsg = ex.Message;  
                bSuccess = false;
            }
            finally
            {
                if (appexcel != null)
                {
                    KillSpecialExcel(appexcel);
                }
            }
             
            return bSuccess;
        }


        /// <summary>
        /// 关闭进程
        /// </summary>
        /// <param name="appexcel"></param>
        public void KillSpecialExcel(Application appexcel)
        {
            IntPtr t = new IntPtr(appexcel.Hwnd);
            int k = 0;
            GetWindowThreadProcessId(t, out k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();
        }


        /// <summary>
        /// 导出云机列表
        /// </summary>
        /// <param name="items"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public string ListToExcel(List<VmExceldgExcelOut> items, string fileName)
        {
            string url = "";
            int rowNumber = items.Count;//不包括字段名
            int columnNumber = items.Count;
            int colIndex = 0;
            if (rowNumber == 0)
            {
                return "没有数据!";
            }


            Application excel = new Application();
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            Workbook workbook = null;
            Worksheet worksheet = null;
            Range range;
            workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            worksheet = (Worksheet)workbook.Worksheets[1];


            //设置对象不可见  
            excel.Visible = false;
            excel.DisplayAlerts = false;
            try
            {
                //生成字段名称 --表头
                VmExceldgExcelOut temp = new VmExceldgExcelOut();
                PropertyInfo[] propertys = temp.GetType().GetProperties();
                foreach (PropertyInfo property in propertys)
                {
                    string code = ((DescriptionAttribute)Attribute.GetCustomAttribute(property, typeof(DescriptionAttribute))).Description;// 属性值
                    if (string.IsNullOrEmpty(code))
                        continue;
                    colIndex++;
                    excel.Cells[1, colIndex] = code;
                }
                columnNumber = colIndex;


                object[,] objData = new object[rowNumber, columnNumber];


                for (int r = 0; r < rowNumber; r++)
                {
                    temp = items[r];//获取一行数据
                    for (int c = 0; c < columnNumber; c++)
                    {
                        propertys = temp.GetType().GetProperties();
                        object o = propertys[c].GetValue(temp, null);
                        if (o == null) o = "";
                        objData[r, c] = o.ToString();//dt.Rows[r][c];
                    }
                }


                // 写入Excel,第一行写列名，从第二行开始写数据
                range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];
                range.NumberFormat = "@";//设置单元格为文本格式
                range.Value2 = objData;


                //保存工作表 
                fileName = fileName + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
                workbook.SaveAs(filepath + fileName, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                workbook.Close(false, miss, miss);
                excel.Workbooks.Close();
                excel.Quit();


                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel.Workbooks);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                GC.Collect();
                GC.WaitForPendingFinalizers();
                url = fileUrl + fileName;
            }
            catch (Exception e)
            {
                url = e.Message;
            }
            finally
            {
                if (excel != null)
                {
                    //关闭进程把程序也关掉了，待修改测试
                    //KillSpecialExcel(excel);
                }
            }
            return url;
        }
        */


        public string ListToExcel(List<VmExceldgExcelOut> items, string fileName)
        {
            //IMEX=1 导入模式，可读取不可编辑,修改
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;\"";//IMEX=1


            string url = "";
            int rows = items.Count;//不包括字段名
            int cols = 0;
            int colIndex = 0;
            if (rows == 0)
            {
                return "没有数据!";
            }


            //把EXCEL空文件复制一份
            fileName = fileName + ".xlsx";// + DateTime.Now.ToString("yyyyMMddhhmmss")
            url = fileUrl + fileName;//返回下载路径
            fileName = filepath + fileName;
            File.Copy(filepath + "temp.xlsx", fileName, true);
            File.SetAttributes(fileName, FileAttributes.Normal);


            
            StringBuilder sb;
            string connString,strCreate;
            sb = new StringBuilder();
            connString = string.Format(strConn, fileName);


            //创建表
            sb.Append("create table");
            sb.Append(" [tempData$] " + "(");
            VmExceldgExcelOut temp = new VmExceldgExcelOut();
            PropertyInfo[] propertys = temp.GetType().GetProperties();
            foreach (PropertyInfo property in propertys)
            {
                string code = ((DescriptionAttribute)Attribute.GetCustomAttribute(property, typeof(DescriptionAttribute))).Description;// 属性值
                if (string.IsNullOrEmpty(code))
                    continue;
                colIndex++;
                sb.Append(string.Format("{0} varchar,", code));
            }
            cols = colIndex;
            strCreate = sb.ToString().Trim(',')+ ")";
            using (OleDbConnection objCon = new OleDbConnection(connString))
            {
                OleDbCommand objCmd = new OleDbCommand();
                objCmd.Connection = objCon;
                try
                {
                    objCon.Open();
                    //删除原有表,创建带字段新表
                    objCmd.CommandText = "drop table [tempData$]";
                    objCmd.ExecuteNonQuery();
                    objCmd.CommandText = strCreate;
                    objCmd.ExecuteNonQuery();


                    #region 生成插入数据脚本
                    for (int r = 0; r < rows; r++)
                    {
                        sb.Remove(0, sb.Length);
                        strCreate = "";
                        strCreate = "INSERT INTO [tempData$] values(";
                        temp = items[r];//获取一行数据
                        for (int c = 0; c < cols; c++)
                        {
                            propertys = temp.GetType().GetProperties();
                            object o = propertys[c].GetValue(temp, null);
                            if (o == null) o = "";
                            strCreate += string.Format("'{0}',", o);
                        }
                        strCreate = strCreate.Substring(0, strCreate.Length - 1) + ")";
                        objCmd.CommandText = strCreate;
                        objCmd.ExecuteNonQuery();
                    }
                    objCon.Close();
                    #endregion
                }
                catch (Exception e)
                {
                    return "创建Excel失败，错误信息：" + e.Message;
                }
            }
            return url;
        }


        public string DataTableToExcel(DataTable dt, string fileName)
        {
            //IMEX=1 导入模式，可读取不可编辑,修改
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;\"";//IMEX=1


            string url = "";
            int rows = dt.Rows.Count;
            int cols = dt.Columns.Count;
            int colIndex = 0;
            if (rows == 0)
            {
                return "没有数据!";
            }


            //把EXCEL空文件复制一份
            fileName = fileName + ".xlsx";// + DateTime.Now.ToString("yyyyMMddhhmmss")
            url = fileUrl + fileName;//返回下载路径
            fileName = filepath + fileName;
            File.Copy(filepath + "temp.xlsx", fileName, true);
            File.SetAttributes(fileName, FileAttributes.Normal);




            StringBuilder sb;
            string connString, strCreate;
            sb = new StringBuilder();
            connString = string.Format(strConn, fileName);


            //创建表
            sb.Append("create table");
            sb.Append(" [tempData$] " + "(");
            VmExceldgExcelOut temp = new VmExceldgExcelOut();
            PropertyInfo[] propertys = temp.GetType().GetProperties();
            foreach (PropertyInfo property in propertys)
            {
                string code = ((DescriptionAttribute)Attribute.GetCustomAttribute(property, typeof(DescriptionAttribute))).Description;// 属性值
                if (string.IsNullOrEmpty(code))
                    continue;
                colIndex++;
                sb.Append(string.Format("{0} varchar,", code));
            }
            strCreate = sb.ToString().Trim(',') + ")";
            using (OleDbConnection objCon = new OleDbConnection(connString))
            {
                OleDbCommand objCmd = new OleDbCommand();
                objCmd.Connection = objCon;
                try
                {
                    objCon.Open();
                    //删除原有表,创建带字段新表
                    objCmd.CommandText = "drop table [tempData$]";
                    objCmd.ExecuteNonQuery();
                    objCmd.CommandText = strCreate;
                    objCmd.ExecuteNonQuery();


                    #region 生成插入数据脚本
                    foreach (DataRow dr in dt.Rows)
                    {
                        sb.Remove(0, sb.Length);
                        strCreate = "";
                        strCreate = "INSERT INTO [tempData$] values(";
                        for (int c = 0; c < cols; c++)
                        {
                            strCreate += string.Format("'{0}',", dr[c]);
                        }
                        strCreate = strCreate.Substring(0, strCreate.Length - 1) + ")";
                        objCmd.CommandText = strCreate;
                        objCmd.ExecuteNonQuery();
                    }
                    objCon.Close();
                    #endregion
                }
                catch (Exception e)
                {
                    return "创建Excel失败，错误信息：" + e.Message;
                }
            }
            return url;
        }
    }

}

服务器需安装oledb