SQL Server数据导出到Excel，Excel数据插入到数据库

Model层：Class BookInfo

# region File Information

//**
//* Type        : Class Code  
//* Module      : Class of BookInfo
//* Description : It's the class of deduct data
//* Creator     : Eva Xiao  - Thurday, Jan 6th, 2011
//*

#  endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Model
{
    /// <summary>
    /// class of Book
    /// </summary>
    public class BookInfo
    {
        /// <summary>
        /// bookID
        /// </summary>
        private int _BookID;
        public int BookID
        {
            get { return _BookID; }
            set { _BookID = value; }
        }
        /// <summary>
        /// bookName
        /// </summary>
        private string _BookName;
        public string BookName
        {
            get { return _BookName; }
            set { _BookName = value; }
        }
        /// <summary>
        /// book author
        /// </summary>
        private string _Author;
        public string Author
        {
            get { return _Author; }
            set { _Author = value; }
        }
        /// <summary>
        /// book publisher
        /// </summary>
        private string _Publisher;
        public string Publisher
        {
            get { return _Publisher; }
            set { _Publisher = value; }
        }
        /// <summary>
        /// book price
        /// </summary>
        private decimal _Price;
        public decimal Price
        {
            get { return _Price; }
            set { _Price = value; }
        }
    }
}

 

Class ExcelWrite

# region File Information

//**
//* Type        : Class Code  
//* Module      : Class of ExcelWriter
//* Description : It's the class of ExcelWriter
//* Creator     : Eva Xiao  -Thurday, Jan 6th, 2011
//*

#  endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;//COM-->Microsoft Excel 12.0 Object Library


namespace Excel
{
    public class ExcelWriter
    {
        /// <summary>
        /// define a global variable for excel file connection string
        /// </summary>
        private string excelConn = null;
        /// <summary>
        /// a public method for the main function to write excel
        /// </summary>
        /// <param name="filePath">file export path</param>
        /// <param name="DBPath">database connection string</param>
        /// <returns>sucessfully write or not</returns>
        public bool WriteExcel(string filePath,string DBPath)
        {
            List<BookInfo> bookList = GetAllBooks(DBPath);
            if (bookList != null)
            {
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                excel.Visible = false;
                excel.Application.Workbooks.Add(true);
                Microsoft.Office.Interop.Excel.Sheets excelSheets = excel.Worksheets;
                excel.Cells[1, 1] = "BookID";
                excel.Cells[1, 2] = "BookName";
                excel.Cells[1, 3] = "Author";
                excel.Cells[1, 4] = "Publisher";
                excel.Cells[1, 5] = "Price";
                excel.Columns.ColumnWidth = 25;
                excel.DisplayAlerts = false;
                excel.AlertBeforeOverwriting = false;
                excel.ActiveWorkbook.SaveCopyAs(filePath);
                excel.Workbooks.Close();
                excel.Quit();
                excel = null;
                bool isSucess = false;
                isSucess = WriteExcelByADO(filePath, bookList);
                return isSucess;
            }
            else
            { return false; }
        }
        /// <summary>
        /// Get all books from database
        /// </summary>
        /// <param name="DBPath">DBPath</param>
        /// <returns>bookList</returns>
        private static List<BookInfo> GetAllBooks(string DBPath)
        {
            List<BookInfo> bookList = new List<BookInfo>();
            SqlConnection conn = new SqlConnection(DBPath);
            string queryStr = @"SELECT bookID,bookName,author,publisher,price FROM Books";
            SqlCommand cmd = new SqlCommand(queryStr, conn);
            try
            {
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    BookInfo bookInfo = new BookInfo();
                    bookInfo.BookID = (int)reader["bookID"];
                    bookInfo.BookName = reader["bookName"].ToString();
                    bookInfo.Author = reader["author"].ToString();
                    bookInfo.Publisher = reader["publisher"].ToString();
                    bookInfo.Price = (decimal)reader["price"];
                    bookList.Add(bookInfo);
                }
            }
            catch
            {
                bookList = null;
                return bookList;
            }
            finally { conn.Close(); }
            return bookList;
        }
        /// <summary>
        /// use OleDb to insert record into excel
        /// </summary>
        /// <param name="filePath">file export path</param>
        /// <param name="bookList">list of record</param>
        /// <returns>insert sucessfully or not</returns>
        private bool WriteExcelByADO(string filePath, List<BookInfo> bookList)
        {
            excelConn = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + filePath + "; Extended Properties=\"Excel 12.0;HDR=YES\"";
            OleDbConnection conn = new OleDbConnection(excelConn);
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                for (int i = 0; i < bookList.Count; i++)
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("INSERT INTO [Sheet1$]([BooKID],[BookName],[author],[publisher],[price]) VALUES('");
                    sb.Append(bookList[i].BookID+"','");
                    sb.Append(bookList[i].BookName + "','");
                    sb.Append(bookList[i].Author + "','");
                    sb.Append(bookList[i].Publisher + "','");
                    sb.Append(bookList[i].Price + "')");
                    cmd.CommandText = sb.ToString();
                    cmd.ExecuteNonQuery();
                }
            }
            catch
            { return false; }
            finally
            { conn.Close(); }
            return true;
        }
    }
}