数据库数据生成EXCEL表格

来源：互联网发布：java 邮件发送工具类编辑：程序博客网时间：2024/05/16 15:55

从数据库中查询数据，生成EXCEL表格的方法：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Runtime.InteropServices;

namespace Test2
{
class DataBaseToExcel
{

        public static void DataToExcel (DataTable dataTable,string saveFile) //dataTable 为从数据库中查出的数据放到DataTable里面
        {
            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 ();
                    //置表中的内容为居中
                    Microsoft.Office.Interop.Excel.Range range=(Microsoft.Office.Interop.Excel.Range)worksheet.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]);
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                    range.EntireColumn.AutoFit ();//设置列宽为自动调整

                }
            }
            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 GetWindowThreadProccesID (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;
                GetWindowThreadProccesID (t,out k);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById (k);
                p.Kill ();
            }
            catch (Exception ex)
            {
               // Console.WriteLine (ex.StackTrace+ex.Message);
            }
        }
    }
}