导出Excel方式汇总-- 第一种 利用office插件进行读取

第一种：利用office插件进行读取，缺点是需要安装office才可以使用

 

        /// <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;