c# 数据导出Excel

        /// <summary>
        /// 导出到 Excel 文件
        /// </summary>
        /// <param name="rs">当前记录</param>
        /// <param name="flds">要导出的字段</param>
        /// <param name="fn">文件的路径文件名</param>
        /// <param name="dcfg">词典配置工具</param>
        public static void ExportToExcel(DataAccess.Record[] rs, DataAccess.Field[] flds, string fn, DictionaryConfigure dcfg)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();   // 建立Excel对象
            excel.Application.Workbooks.Add(true);         // Excel表为添加状态
            for (int i = 0; i < flds.Length; i++)          // 填充表头
            {
                excel.Cells[1, i + 1] = flds[i].Name;
            }
            for (int i = 0; i < rs.Length; i++)            // 填充数据
            {
                for (int j = 0; j < flds.Length; j++)
                {
                    string txt = rs[i].getStringValue(flds[j].ID);
                    if (!flds[j].DictionaryID.Equals(""))
                    {
                        txt = Business.DictionaryConfigure.GetCatalogNameByDictionary(txt, dcfg.getDictionary(flds[j].DictionaryID));
                    }
                    if (flds[j].EditIsDate)
                    {
                        txt = (txt.Length > 8 ? txt.Substring(0, 8) : txt);
                        txt = Utility.StringUtility.Alt(txt, "????-??-??", "?");
                    }
                    excel.Cells[i + 2, j + 1] = txt;
                }
            }
            excel.Visible = false;
            excel.DisplayAlerts = false;
            excel.ActiveWorkbook.SaveAs(fn, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            KillExcelProcess(excel);
        }

        /// <summary>
        /// 获取进程标识
        /// </summary>
        /// <param name="hwnd">输入参数：句柄</param>
        /// <param name="ID">输出参数：进程标识</param>
        /// <returns></returns>
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

        /// <summary>
        /// 关闭 Excel 进程
        /// </summary>
        /// <param name="excel"></param>
        private static void KillExcelProcess(Microsoft.Office.Interop.Excel.ApplicationClass excel)
        {
            //------------ 方法 1 -----------------
            //excel.Quit();
            //excel = null;
            //GC.Collect();                                            // 垃圾回收
            //------------ 方法 2 -----------------
            IntPtr handler = new IntPtr(excel.Hwnd);     // 句柄
            int processid = 0;                           // 进程标识
            GetWindowThreadProcessId(handler, out processid);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(processid);  // 进程
            p.Kill();             // 杀除进程
            //-----------------------------
        }

        /// <summary>
        /// 从指定的Excel文件导入
        /// </summary>
        /// <param name="strFileName">导入文件</param>
        /// <returns></returns>
        public DataSet importFromExcel(string strFileName)
        {
            if (strFileName == "") return null;
            string strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + strFileName + ";Extended Properties=Excel 8.0";
            OleDbDataAdapter excelDA = new OleDbDataAdapter("select * from [Sheet1$]", strConn);      // 连接字符串
            DataSet ds = new DataSet();        // 建立数据集，用于存放导入Excel的数据
            excelDA.Fill(ds, "ExcelInfo");     // 填充DataSet
            return ds;
        }

        /// <summary>
        /// 将记录集导出到 Excel 文件
        /// </summary>
        /// <param name="rs">当前记录集</param>
        /// <param name="flds">要导出的字段</param>
        /// <param name="fn">文件的路径文件名</param>
        public static void RecordsToExcel(DataAccess.Record[] rs, DataAccess.Field[] flds, string fn)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();   // 建立Excel对象
            excel.Application.Workbooks.Add(true);         // Excel表为添加状态
            for (int i = 0; i < flds.Length; i++)          // 填充表头
            {
                excel.Cells[1, i + 1] = flds[i].Name;
            }
            for (int i = 0; i < rs.Length; i++)            // 填充数据
            {
                for (int j = 0; j < flds.Length; j++)
                {
                    excel.Cells[i + 2, j + 1] = rs[i].getStringValue(flds[j].ID);
                }
            }
            excel.Visible = false;
            excel.DisplayAlerts = false;
            excel.ActiveWorkbook.SaveAs(fn, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            KillExcelProcess(excel);
        }

 

转载于:https://www.cnblogs.com/xsmhero/archive/2011/10/21/2219812.html