using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using DAL;

namespace BLL
{
    public class OperateDataFromExcel
    {
        public string strError = null;//错误信息

        /// <summary>
        /// 通过向单元格写数据的方法将数据写入Excel
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="strExcelFileName">Excel文件的路径名</param>
        /// <param name="strTableName">文件名(sheet名)</param>
        /// <param name="strTitle">数据表的标题(空或null则不写)</param>
        /// <param name="bIsOpenExcel">是否立即打开Excel表</param>
        /// <returns>0--成功,-1--导入到Excel失败,-2--销毁进程失败,-3打开Excel表失败</returns>
        public int WriteToExcelByCell(System.Data.DataTable dt, string strExcelFileName, string strTableName, string strTitle, bool bIsOpenExcel)
        {
            DateTime datetime = DateTime.Now;
            try
            {

                Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApp.DisplayAlerts = true;
                excelApp.SheetsInNewWorkbook = 1;
                Workbook excelBook = excelApp.Workbooks.Add(Type.Missing);
                Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.ActiveSheet;
                excelSheet.Name = strTableName;
                int nRowIndex = 1;//行号
                if (string.IsNullOrEmpty(strTitle) == false)
                {
                    string a = (Convert.ToChar(64 + dt.Columns.Count)).ToString() + "1";
                    Range rH = excelSheet.get_Range("A1", a);
                    rH.Merge(0);
                    rH.HorizontalAlignment = XlVAlign.xlVAlignCenter;
                    rH.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    excelApp.Cells[nRowIndex++, 1] = strTitle;
                }
                for (int i = 1; i <= dt.Columns.Count; i++)
                {
                    excelApp.Cells[nRowIndex, i] = dt.Columns[i - 1].ColumnName;
                }
                nRowIndex++;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        excelApp.Cells[nRowIndex, j + 1] = dt.Rows[i][j];
                    }
                    nRowIndex++;
                }
                excelBook.Saved = true;
                excelBook.SaveCopyAs(strExcelFileName);
                if (excelApp != null)
                {
                    excelApp.Workbooks.Close();
                    excelApp.Quit();
                    int generation = System.GC.GetGeneration(excelApp);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                    System.GC.Collect(generation);
                }
                GC.Collect();//强行销毁
            }
            catch (Exception ex)
            {
                strError = "导入到Excel出错:" + ex.Message;
                return -1;
            }
            if (KillExcelProcess(datetime) == false)
            {
                return -2;
            }
            if (bIsOpenExcel == true)
            {
                try
                {
                    System.Diagnostics.Process.Start(strExcelFileName);
                }
                catch (Exception ex)
                {
                    strError = "打开Excel表失败:" + ex.Message;
                    return -3;
                }
            }
            return 0;

        }

        /// <sum