C#将数据库数据导出为Excel

这篇具有很好参考价值的文章主要介绍了C#将数据库数据导出为Excel。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1、创建DataChangeExcel方法

public class DataChangeExcel
    {
       public static void DataSetToExcel(DataTable dataTable, string SaveFile)
       {
               Microsoft.Office.Interop.Excel.Application excel;
               Microsoft.Office.Interop.Excel._Workbook workBook;
               Microsoft.Office.Interop.Excel._Worksheet workSheet;
               object misValue = System.Reflection.Missing.Value;
               excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
               workBook = excel.Workbooks.Add(misValue);
               workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet;
               int rowIndex = 1;
               int colIndex = 0;
               //取得标题
               foreach (DataColumn col in dataTable.Columns)
               {
                   colIndex++;
                   excel.Cells[1, colIndex] = col.ColumnName;
               }
               //取得表格中的数据
               foreach (DataRow row in dataTable.Rows)
               {
                   rowIndex++;
                   colIndex = 0;
                   foreach (DataColumn col in dataTable.Columns)
                   {
                       colIndex++;
                       excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim().Insert(0,"'");
                       //设置表格内容居中对齐
                       workSheet.get_Range(excel.Cells[rowIndex, colIndex],
                           excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                   }
               }
               excel.Visible = false;
               workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
               dataTable = null;
               workBook.Close(true, misValue, misValue);
               excel.Quit();
               PublicMethod.kill(excel);//调用kill当前excel进程
               releaseObject(workSheet);
               releaseObject(workBook);
               releaseObject(excel);
       }
       private static void releaseObject(object obj)
       {
           try
           {
               System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
               obj = null;
           }
           catch
           {
               obj = null;
           }
           finally
           {
               GC.Collect();
           }
 
       }
 
       public class PublicMethod
       {
           [DllImport("User32.dll", CharSet = CharSet.Auto)]
           public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
           public static void kill(Microsoft.Office.Interop.Excel.Application excel)
           {
               try
               {
                   IntPtr t = new IntPtr(excel.Hwnd);
                   int k = 0;
                   GetWindowThreadProcessId(t, out k);
                   System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                   p.Kill();
               }
               catch
               {
               }
           }
       }
    }

2、进行方法调用文章来源地址https://www.toymoban.com/news/detail-577816.html

public class DataBaseHelper
        {
            public static DataTable ExecuterQuery(string connectionString, string commandSql)
            {
                DataTable dataTable = new DataTable();
                try
                {
                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        conn.Open();
                        using (SqlDataAdapter adapter = new SqlDataAdapter(commandSql, conn))
                        {
                            adapter.Fill(dataTable);
                        }
                        conn.Close();
                    }
                }
                catch
                {
                    return null;
                }
                return dataTable;
            }
        }
         
        public class DataBaseDao
        {
            public static DataTable GetDataBaseTable()
            {
                string sql = "select POC_SN,POC_ADDRESS,POC_LAT,POC_LNG,METER_NO,DLMSPASSWORD,SIM_NO,CUSTOMER_NO,CUSTOMER_NAME,[1P/3P],GARDU from Excel1";
                return DataBaseHelper.ExecuterQuery("server=172.16.100.59;DataBase=Load;uid=sa;pwd=123456",sql);
            }
        }
         
 //生成Excel,path为文件存放路径
 DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(), path);

到了这里,关于C#将数据库数据导出为Excel的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包