using System;
using System.Data;
using System.Data.OleDb;

namespace ZFSoft.Joint
{
    public class ExcelIO
    {
        private int _ReturnStatus;
        private string _ReturnMessage;

        ///
        /// 执行返回状态
        ///
        public int ReturnStatus
        {
            get
            {
                return _ReturnStatus;
            }
        }

        ///
        /// 执行返回信息
        ///
        public string ReturnMessage
        {
            get
            {
                return _ReturnMessage;
            }
        }

        public ExcelIO()
        {
        }

         
        ///
        /// 导入EXCEL到DataSet
        ///
        /// Excel全路径文件名
        /// 导入成功的DataSet
        public DataTable ImportExcel(string fileName)
        {
            //判断是否安装EXCEL
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                _ReturnStatus = -1;
                _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
                return null;
            }

            //判断文件是否被其他进程使用           
            Microsoft.Office.Interop.Excel.Workbook workbook;
            try
            {
                workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
            }
            catch
            {
                _ReturnStatus = -1;
                _ReturnMessage = "Excel文件处于打开状态，请保存关闭";
                return null;
            }

            //获得所有Sheet名称
            int n = workbook.Worksheets.Count;
            string[] SheetSet = new string[n];
            System.Collections.ArrayList al = new System.Collections.ArrayList();
            for (int i = 1; i 0)
            {
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            }
            if (dt.Columns.Count > 1)
            {
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            }

            //保存文件
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    fileSaved = true;
                }
                catch (Exception ex)
                {
                    fileSaved = false;
                    _ReturnStatus = -1;
                    _ReturnMessage = "导出文件时出错,文件可能正被打开！n" + ex.Message;
                }
            }
            else
            {
                fileSaved = false;
            }

            //释放Excel对应的对象
            if (range != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                range = null;
            }
            if (worksheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                worksheet = null;
            }
            if (workbook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
            }
            if (workbooks != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                workbooks = null;
            }
            xlApp.Application.Workbooks.Close();
            xlApp.Quit();
            if (xlApp != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp = null;
            }
            GC.Collect();
            return fileSaved;
        }
    }
}