using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Data;
using System.IO;
using NPOI.SS.Util;
using System.Data.OleDb;
using System.Linq;

namespace Common
{
    public class NPOIExcel
    {
        private string _title;
        private string _sheetName;
        private string _filePath;

        public NPOIExcel(string title, string sheetName, string filePath)
        {
            this._title = title;
            this._sheetName = sheetName;
            this._filePath = filePath;
        }

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public bool ToExcel(DataTable table)
        {
            FileStream fs = new FileStream(this._filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook workBook = new HSSFWorkbook();
            this._sheetName = this._sheetName.IsEmpty() ? "sheet1" : this._sheetName;
            ISheet sheet = workBook.CreateSheet(this._sheetName);

            //处理表格标题
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue(this._title);
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
            row.Height = 500;

            ICellStyle cellStyle = workBook.CreateCellStyle();
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = 17;
            cellStyle.SetFont(font);
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;
            row.Cells[0].CellStyle = cellStyle;

            //处理表格列头
            row = sheet.CreateRow(1);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
                row.Height = 350;
                sheet.AutoSizeColumn(i);
            }

            //处理数据内容
            for (int i = 0; i < table.Rows.Count; i++)
            {
                row = sheet.CreateRow(2 + i);
                row.Height = 250;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
                    sheet.SetColumnWidth(j, 256 * 15);
                }
            }

            //写入数据流
            workBook.Write(fs);
            fs.Flush();
            fs.Close();

            return true;
        }

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="table"></param>
        /// <param name="title"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public bool ToExcel(DataTable table, string title, string sheetName, string filePath)
        {
            this._title = title;
            this._sheetName = sheetName;
            this._filePath = filePath;
            return ToExcel(table);
        }

        public static System.Data.DataTable InputExcel(string Path)
        {
            System.Data.DataTable dt = null;
            try
            {
                string strExtension = System.IO.Path.GetExtension(Path);
                string strConn = "";
                switch (strExtension)
                {
                    case ".xls":
                        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"";
                        break;
                    case ".xlsx":
                        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"";
                        //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串) 
                        //"HDR=yes;"是说Excel文件的第一行是列名而不是数，"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 
                        break;
                    default:
                        strConn = null;
                        break;
                }

                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    
                    conn.Open();
                    System.Data.DataTable sheetDt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string[] sheet = new string[sheetDt.Rows.Count];
                    for (int i = 0; i < sheetDt.Rows.Count; i++)
                    {
                        sheet[i] = sheetDt.Rows[i]["TABLE_NAME"].ToString();
                    }
                    for (int i = 0; i < sheet.Count(); i++)
                    {
                        if (sheet[i].Contains("$"))
                        {
                            string strExcel = string.Format("select * from [{0}]", sheet[i]);
                            OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
                            dt = new System.Data.DataTable();
                            myCommand.Fill(dt);
                            conn.Close();
                            return dt;
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
            }

            return dt;
        }
    }
}