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

namespace SiaSun.LMS.Common
{
    public class Excel
    {
        /// <summary>
        /// 导出查询的所有数据,忽略分页
        /// </summary>
        /// <param name="table">数据源</param>
        /// <param name="title">标题备注</param>
        public void ExportAllToExcel(DataTable tableSource, string title)
        {
            Microsoft.Office.Interop.Excel.Application objApp;
            Microsoft.Office.Interop.Excel._Workbook objBook;
            Microsoft.Office.Interop.Excel.Workbooks objBooks;
            Microsoft.Office.Interop.Excel.Sheets objSheets;
            Microsoft.Office.Interop.Excel._Worksheet objSheet;
            Microsoft.Office.Interop.Excel.Range range;

            int columnIndex = 1;
            int rowIndex = 1;

            try
            {
                objApp = new Microsoft.Office.Interop.Excel.Application();
                objBooks = objApp.Workbooks;
                objBook = objBooks.Add(System.Reflection.Missing.Value);
                objSheets = objBook.Worksheets;
                objSheet = (Microsoft.Office.Interop.Excel._Worksheet)objSheets.get_Item(1);

                //设置标题
                objSheet.Cells[rowIndex, columnIndex] = title;
                columnIndex++;
                objSheet.Cells[rowIndex, columnIndex] = DateTime.Now.ToString();
                rowIndex++;

                //设置标题列
                columnIndex = 0;

                //列头
                foreach (DataColumn col in tableSource.Columns)
                {
                    columnIndex++;
                    objSheet.Cells[rowIndex, columnIndex] = col.ColumnName;
                }

                string[,] saRet;
                saRet = new string[tableSource.Rows.Count, columnIndex];
                range = objSheet.get_Range("A3", System.Reflection.Missing.Value);
                range = range.get_Resize(tableSource.Rows.Count, columnIndex);

                //内容                
                columnIndex = 0;
                foreach (DataColumn col in tableSource.Columns)
                {
                    rowIndex = 0;
                    foreach (DataRow row in tableSource.Rows)
                    {
                        saRet[rowIndex, columnIndex] = row[col].ToString();
                        rowIndex++;
                    }
                    columnIndex++;
                }

                range.set_Value(System.Reflection.Missing.Value, saRet);
                objApp.Visible = true;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获得
        /// </summary>
        public DataTable ImportToDataTable(string FileName,bool IsAllData)
        {
            string strConn = null;
            if (FileName.ToLower().Contains(".xlsx"))    //Excel 2007
            {
                strConn = string.Format(@"provider=Microsoft.ACE.OLEDB.12.0;data source={0};Extended Properties=Excel 12.0;", FileName);
            }
            else            //Excel 2003
            {
                strConn = string.Format(@"provider=Microsoft.Jet.OLEDB.4.0;data source={0};Extended Properties=Excel 8.0", FileName);
            }

            DataTable tableExcel = new DataTable();
            using (System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection(strConn))
            {
                System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter(string.Format("select * from [Sheet1$]",IsAllData?string.Empty:" where 0=1"), cnn);
                cnn.Open();
                adp.Fill(tableExcel);
                cnn.Close();
            }
            return tableExcel;
        }
    }
}