using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Diagnostics;
using System.IO;
using System.Reflection;
using System.Text;
using NetLibrary.Data;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NetLibrary.ReportPrint;


namespace NetLibrary
{
    public class MicrosoftExcel
    {
        #region 导出


        public string ExportTXT(string Content)
        {


            try
            {
                string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
                if (Directory.Exists(DirectoryName) == false) Directory.CreateDirectory(DirectoryName);

                string filename = Guid.NewGuid().ToString() + ".txt";
                string filePath = DirectoryName + "/" + filename;
                File.Create(filePath).Close();
                FileInfo f = new FileInfo(filePath);

                using (StreamWriter sw = new StreamWriter(filePath, false, System.Text.Encoding.UTF8))
                {

                    sw.Write(Content);

                    sw.Close();
                }
                return "ServerCookies/" + filename;
            }
            catch
            {
                return "";
            }
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="HeaderName">表头</param>
        /// <param name="tb">DataTable</param>
        /// <param name="Columns">要导出的列集合</param>
        /// <returns></returns>
        public string Export(string HeaderName, DataTable tb)
        {

            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
            sheet.PrintSetup.FitHeight = 0;
            sheet.PrintSetup.FitWidth = 0;
            sheet.PrintSetup.Scale = 100;

            HSSFRow dataRow = null;
            HSSFCell dataCell = null;
            int RowIndex = 0;
            if (HeaderName != "")
            {
                dataRow = (HSSFRow)sheet.CreateRow(0);
                dataCell = (HSSFCell)dataRow.CreateCell(0);
                dataCell.SetCellValue(HeaderName);
                dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                //边框  
                dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                //边框颜色  
                dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                sheet.AddMergedRegion(new Region(0, 0, 0, tb.Columns.Count - 1));
                RowIndex++;
            }
            //填充内容   
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                dataRow = (HSSFRow)sheet.CreateRow(i + RowIndex);
                foreach (DataColumn item in tb.Columns)
                {
                    int j = tb.Columns.IndexOf(item);
                    dataCell = (HSSFCell)dataRow.CreateCell(j);
                    dataCell.SetCellValue(tb.Rows[i][item.ColumnName].ToString());
                    dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                    //边框  
                    dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                    dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                    dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                    dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                    //边框颜色  
                    dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                }
            }
            for (int i = 0; i < tb.Columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            string filename = Guid.NewGuid().ToString() + ".xls";
            string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
            if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
            string ServerFileName = DirectoryName + "/" + filename;
            //保存   
            using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            workbook.Dispose();
            return "ServerCookies/" + filename;
        }

        public string Export(DataTable tb, ReportPrint.TableColumnCollection Columns)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
            sheet.PrintSetup.FitHeight = 0;
            sheet.PrintSetup.FitWidth = 0;
            sheet.PrintSetup.Scale = 100;
            //填充表头   
            HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
                dataCell.SetCellValue(item.Caption);
                dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                //边框  
                //dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                //dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                //dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                //dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                ////边框颜色  
                //dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                //dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                //dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                //dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
            }

            //填充内容   
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                dataRow = (HSSFRow)sheet.CreateRow(i + 1);
                HSSFCell dataCell = null;
                foreach (ReportPrint.TableColumn item in Columns)
                {
                    int j = Columns.IndexOf(item);
                    dataCell = (HSSFCell)dataRow.CreateCell(j);
                    if (item.Format != "")
                    {
                        dataCell.SetCellValue(DataConvert.Format(tb.Rows[i][item.Name].ToString(), item.Format));
                    }
                    else
                    {
                        if (item.DataType == DbType.Decimal && tb.Rows[i][item.Name] != null && tb.Rows[i][item.Name].ToString() != "")
                            dataCell.SetCellValue(Convert.ToDouble(tb.Rows[i][item.Name]));
                        else if (item.DataType != DbType.Decimal)
                            dataCell.SetCellValue(tb.Rows[i][item.Name].ToString());
                    }



                    dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                    //边框  
                    //dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                    //dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                    //dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                    //dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                    ////边框颜色  
                    //dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    //dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    //dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    //dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                }
            }
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
                else { sheet.AutoSizeColumn(index); }
            }
            string filename = Guid.NewGuid().ToString() + ".xls";
            string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
            if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
            string ServerFileName = DirectoryName + "/" + filename;
            //保存   
            using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            workbook.Dispose();
            return "ServerCookies/" + filename;
        }


        public string ExportTwo(DataTable tb, DataTable tb1, ReportPrint.TableColumnCollection Columns, ReportPrint.TableColumnCollection Columns1)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
            sheet.PrintSetup.FitHeight = 0;
            sheet.PrintSetup.FitWidth = 0;
            sheet.PrintSetup.Scale = 100;
            //填充表头   
            HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
                dataCell.SetCellValue(item.Caption);
                dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                //边框  
                dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                //边框颜色  
                dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
            }

            //填充内容   
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                dataRow = (HSSFRow)sheet.CreateRow(i + 1);
                HSSFCell dataCell = null;
                foreach (ReportPrint.TableColumn item in Columns)
                {
                    int j = Columns.IndexOf(item);
                    dataCell = (HSSFCell)dataRow.CreateCell(j);
                    if (item.Format != "")
                    {
                        dataCell.SetCellValue(DataConvert.Format(tb.Rows[i][item.Name].ToString(), item.Format));
                    }
                    else
                    {
                        dataCell.SetCellValue(tb.Rows[i][item.Name].ToString());
                    }
                    dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                    //边框  
                    dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                    dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                    dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                    dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                    //边框颜色  
                    dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                }
            }
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
                else { sheet.AutoSizeColumn(index); }
            }

            HSSFSheet sheet1 = (HSSFSheet)workbook.CreateSheet("Sheet2");
            sheet1.PrintSetup.FitHeight = 0;
            sheet1.PrintSetup.FitWidth = 0;
            sheet1.PrintSetup.Scale = 100;
            //填充表头   
            HSSFRow dataRow1 = (HSSFRow)sheet1.CreateRow(0);
            foreach (ReportPrint.TableColumn item in Columns1)
            {
                int index = Columns1.IndexOf(item);
                HSSFCell dataCell = (HSSFCell)dataRow1.CreateCell(index);
                dataCell.SetCellValue(item.Caption);
                dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                //边框  
                dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                //边框颜色  
                dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
            }

            //填充内容   
            for (int i = 0; i < tb1.Rows.Count; i++)
            {
                dataRow1 = (HSSFRow)sheet1.CreateRow(i + 1);
                HSSFCell dataCell = null;
                foreach (ReportPrint.TableColumn item in Columns1)
                {
                    int j = Columns1.IndexOf(item);
                    dataCell = (HSSFCell)dataRow1.CreateCell(j);
                    if (item.Format != "")
                    {
                        dataCell.SetCellValue(DataConvert.Format(tb1.Rows[i][item.Name].ToString(), item.Format));
                    }
                    else
                    {
                        dataCell.SetCellValue(tb1.Rows[i][item.Name].ToString());
                    }
                    dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                    //边框  
                    dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                    dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                    dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                    dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                    //边框颜色  
                    dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                }
            }
            foreach (ReportPrint.TableColumn item in Columns1)
            {
                int index = Columns1.IndexOf(item);
                if (item.Width > 0) { sheet1.SetColumnWidth(index, (int)item.Width); }
                else { sheet1.AutoSizeColumn(index); }
            }
            string filename = Guid.NewGuid().ToString() + ".xls";
            string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
            if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
            string ServerFileName = DirectoryName + "/" + filename;
            //保存   
            using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            workbook.Dispose();
            return "ServerCookies/" + filename;
        }
        /// <summary>
        /// 2013-12-27
        /// 石志超
        /// </summary>
        public string Export<T>(List<T> lst, ReportPrint.TableColumnCollection Columns) where T : class
        {
            Type t = typeof(T);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
            //sheet.PrintSetup.PaperSize = 9;
            sheet.PrintSetup.FitHeight = 0;
            sheet.PrintSetup.FitWidth = 0;
            sheet.PrintSetup.Scale = 100;
            //填充表头   
            HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
                dataCell.SetCellValue(item.Caption);
                //边框  
                dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                //边框颜色  
                dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
            }


            //填充内容   
            for (int i = 0; i < lst.Count; i++)
            {
                dataRow = (HSSFRow)sheet.CreateRow(i + 1);
                HSSFCell dataCell = null;
                foreach (ReportPrint.TableColumn item in Columns)
                {
                    int j = Columns.IndexOf(item);
                    dataCell = (HSSFCell)dataRow.CreateCell(j);
                    if (item.Format != "")
                    {
                        dataCell.SetCellValue(
                            DataConvert.Format(Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)), item.Format));
                    }
                    else if (item.DataType == DbType.Int32&& Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)) != "")
                    {
                        dataCell.SetCellValue(Convert.ToInt32(t.GetProperty(item.Name).GetValue(lst[i], null)));
                    }
                    else if (item.DataType == DbType.Decimal&& Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)) != "")
                    {
                        dataCell.SetCellValue(Convert.ToDouble(t.GetProperty(item.Name).GetValue(lst[i], null)));
                    }
                    else
                    {
                        dataCell.SetCellValue(Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)));
                    }
                    dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                    //边框  
                    dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                    dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                    dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                    dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                    //边框颜色  
                    dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                }
            }
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
                else { sheet.AutoSizeColumn(index); }
            }
            string filename = Guid.NewGuid().ToString() + ".xls";
            string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
            if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
            string ServerFileName = DirectoryName + "/" + filename;
            //保存   
            using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            workbook.Dispose();
            return "ServerCookies/" + filename;
        }

        /// <summary>
        /// 2014-01-26
        /// 石志超
        /// </summary>
        public string Export<T>(string HeaderName, List<T> lst, ReportPrint.TableColumnCollection Columns) where T : class
        {
            Type t = typeof(T);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
            //sheet.PrintSetup.PaperSize = 9;
            sheet.PrintSetup.FitHeight = 0;
            sheet.PrintSetup.FitWidth = 0;
            sheet.PrintSetup.Scale = 100;

            HSSFRow dataRow = null;
            int RowIndex = 0;

            if (!string.IsNullOrEmpty(HeaderName))
            {
                dataRow = (HSSFRow)sheet.CreateRow(0);
                HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(0);
                dataCell.SetCellValue(HeaderName);
                dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                sheet.AddMergedRegion(new Region(0, 0, 0, Columns.Count - 1));
                RowIndex++;
            }
            //填充表头   
            dataRow = (HSSFRow)sheet.CreateRow(RowIndex);
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
                dataCell.SetCellValue(item.Caption);
                //边框  
                dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                //边框颜色  
                dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
            }
            RowIndex++;

            //填充内容   
            for (int i = 0; i < lst.Count; i++)
            {
                dataRow = (HSSFRow)sheet.CreateRow(i + RowIndex);
                HSSFCell dataCell = null;
                foreach (ReportPrint.TableColumn item in Columns)
                {
                    int j = Columns.IndexOf(item);
                    dataCell = (HSSFCell)dataRow.CreateCell(j);
                    if (item.Format != "")
                    {
                        dataCell.SetCellValue(
                            DataConvert.Format(Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)), item.Format));
                    }
                    else
                    {
                        dataCell.SetCellValue(Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)));
                    }
                    dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                    //边框  
                    dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                    dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                    dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                    dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                    //边框颜色  
                    dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                }
            }
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
                else { sheet.AutoSizeColumn(index); }
            }
            string filename = Guid.NewGuid().ToString() + ".xls";
            string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
            if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
            string ServerFileName = DirectoryName + "/" + filename;
            //保存   
            using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            workbook.Dispose();
            return "ServerCookies/" + filename;
        }

        public string Export(DataTable dt1, ReportPrint.TableColumnCollection Columns1, DataTable dt2, ReportPrint.TableColumnCollection Columns2)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();

            DataTable tb = dt1;
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
            sheet.PrintSetup.FitHeight = 0;
            sheet.PrintSetup.FitWidth = 0;
            sheet.PrintSetup.Scale = 100;
            //填充表头   
            HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
            foreach (ReportPrint.TableColumn item in Columns1)
            {
                int index = Columns1.IndexOf(item);
                HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
                dataCell.SetCellValue(item.Caption);
                dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                //边框  
                dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                //边框颜色  
                dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
            }

            //填充内容   
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                dataRow = (HSSFRow)sheet.CreateRow(i + 1);
                HSSFCell dataCell = null;
                foreach (ReportPrint.TableColumn item in Columns1)
                {
                    int j = Columns1.IndexOf(item);
                    dataCell = (HSSFCell)dataRow.CreateCell(j);
                    if (item.Format != "")
                    {
                        dataCell.SetCellValue(DataConvert.Format(tb.Rows[i][item.Name].ToString(), item.Format));
                    }
                    else
                    {
                        dataCell.SetCellValue(tb.Rows[i][item.Name].ToString());
                    }
                    dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                    //边框  
                    dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                    dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                    dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                    dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                    //边框颜色  
                    dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                }
            }
            foreach (ReportPrint.TableColumn item in Columns1)
            {
                int index = Columns1.IndexOf(item);
                if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
                else { sheet.AutoSizeColumn(index); }
            }
            if (dt2 != null)
            {


                HSSFSheet sheet2 = (HSSFSheet)workbook.CreateSheet("Sheet2");
                //填充表头   
                HSSFRow dataRow2 = (HSSFRow)sheet2.CreateRow(0);
                foreach (ReportPrint.TableColumn item in Columns2)
                {
                    int index = Columns2.IndexOf(item);
                    HSSFCell dataCell = (HSSFCell)dataRow2.CreateCell(index);
                    dataCell.SetCellValue(item.Caption);
                    dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                    //边框  
                    dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                    dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                    dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                    dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                    //边框颜色  
                    dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                }

                //填充内容   
                for (int i = 0; i < dt2.Rows.Count; i++)
                {
                    dataRow2 = (HSSFRow)sheet2.CreateRow(i + 1);
                    HSSFCell dataCell = null;
                    foreach (ReportPrint.TableColumn item in Columns2)
                    {
                        int j = Columns2.IndexOf(item);
                        dataCell = (HSSFCell)dataRow2.CreateCell(j);
                        if (item.Format != "")
                        {
                            dataCell.SetCellValue(DataConvert.Format(dt2.Rows[i][item.Name].ToString(), item.Format));
                        }
                        else
                        {
                            dataCell.SetCellValue(dt2.Rows[i][item.Name].ToString());
                        }
                        dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                        //边框  
                        dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                        dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                        dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                        dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                        //边框颜色  
                        dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                        dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                        dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                        dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    }
                }
                foreach (ReportPrint.TableColumn item in Columns2)
                {
                    int index = Columns2.IndexOf(item);
                    if (item.Width > 0) { sheet2.SetColumnWidth(index, (int)item.Width); }
                    else { sheet2.AutoSizeColumn(index); }
                }


            }

            string filename = Guid.NewGuid().ToString() + ".xls";
            string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
            if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
            string ServerFileName = DirectoryName + "/" + filename;
            //保存   
            using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            workbook.Dispose();
            return "ServerCookies/" + filename;
        }

        public string Export(string HeaderName, DataTable tb, ReportPrint.TableColumnCollection Columns)
        {

            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
            sheet.PrintSetup.FitHeight = 0;
            sheet.PrintSetup.FitWidth = 0;
            sheet.PrintSetup.Scale = 100;
            HSSFRow dataRow = null;
            HSSFCell dataCell = null;
            int RowIndex = 0;
            if (HeaderName != "")
            {
                dataRow = (HSSFRow)sheet.CreateRow(0);
                dataCell = (HSSFCell)dataRow.CreateCell(0);
                dataCell.SetCellValue(HeaderName);
                dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                //边框  
                dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                //边框颜色  
                dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                sheet.AddMergedRegion(new Region(0, 0, 0, Columns.Count - 1));
                RowIndex++;
            }
            //填充表头   
            dataRow = (HSSFRow)sheet.CreateRow(RowIndex);
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                dataCell = (HSSFCell)dataRow.CreateCell(index);
                dataCell.SetCellValue(item.Caption);
                dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                //边框  
                dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                //边框颜色  
                dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
            }
            RowIndex++;

            //填充内容   
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                dataRow = (HSSFRow)sheet.CreateRow(i + RowIndex);
                foreach (ReportPrint.TableColumn item in Columns)
                {
                    int j = Columns.IndexOf(item);
                    dataCell = (HSSFCell)dataRow.CreateCell(j);
                    if (item.Format != "")
                    {
                        dataCell.SetCellValue(DataConvert.Format(tb.Rows[i][item.Name].ToString(), item.Format));
                    }
                    else
                    {
                        dataCell.SetCellValue(tb.Rows[i][item.Name].ToString());
                    }
                    dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                    //边框  
                    dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                    dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                    dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                    dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                    //边框颜色  
                    dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                }
            }
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
                else { sheet.AutoSizeColumn(index); }
            }
            string filename = Guid.NewGuid().ToString() + ".xls";
            string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
            if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
            string ServerFileName = DirectoryName + "/" + filename;
            //保存   
            using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            workbook.Dispose();
            return "ServerCookies/" + filename;
        }
        #endregion

        #region 导出到指定文件
        public void ExportFile(string FilePath, DataTable tb, ReportPrint.TableColumnCollection Columns)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
            sheet.PrintSetup.FitHeight = 0;
            sheet.PrintSetup.FitWidth = 0;
            sheet.PrintSetup.Scale = 100;
            //填充表头   
            HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
                dataCell.SetCellValue(item.Caption);
                dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                //边框  
                dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                //边框颜色  
                dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
            }

            //填充内容   
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                dataRow = (HSSFRow)sheet.CreateRow(i + 1);
                HSSFCell dataCell = null;
                foreach (ReportPrint.TableColumn item in Columns)
                {
                    int j = Columns.IndexOf(item);
                    dataCell = (HSSFCell)dataRow.CreateCell(j);
                    if (item.Format != "")
                    {
                        dataCell.SetCellValue(DataConvert.Format(tb.Rows[i][item.Name].ToString(), item.Format));
                    }
                    else
                    {
                        dataCell.SetCellValue(tb.Rows[i][item.Name].ToString());
                    }
                    dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                    //边框  
                    dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                    dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                    dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                    dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                    //边框颜色  
                    dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                }
            }
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
                else { sheet.AutoSizeColumn(index); }
            }
            //保存   
            using (FileStream fs = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            workbook.Dispose();
        }
        #endregion

        #region ConvertHSSFWorkbook
        public HSSFWorkbook ConvertHSSFWorkbook(DataTable tb, ReportPrint.TableColumnCollection Columns, int StartRowIndex = 0)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
            sheet.PrintSetup.FitHeight = 0;
            sheet.PrintSetup.FitWidth = 0;
            sheet.PrintSetup.Scale = 100;
            HSSFRow dataRow = null;
            HSSFCell dataCell = null;
            if (StartRowIndex > 0)
            {
                for (int i = 0; i < StartRowIndex; i++)
                {
                    dataRow = (HSSFRow)sheet.CreateRow(i);
                    for (int j = 0; j < Columns.Count; j++)
                    {
                        dataRow.CreateCell(j).SetCellValue("");
                    }
                }
            }
            //填充表头   
            dataRow = (HSSFRow)sheet.CreateRow(StartRowIndex);
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                dataCell = (HSSFCell)dataRow.CreateCell(index);
                dataCell.SetCellValue(item.Caption);
                dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                //边框  
                dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                //边框颜色  
                dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
            }

            //填充内容   
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                dataRow = (HSSFRow)sheet.CreateRow(i + StartRowIndex + 1);
                dataCell = null;
                foreach (ReportPrint.TableColumn item in Columns)
                {
                    int j = Columns.IndexOf(item);
                    dataCell = (HSSFCell)dataRow.CreateCell(j);
                    if (item.Format != "")
                    {
                        dataCell.SetCellValue(DataConvert.Format(tb.Rows[i][item.Name].ToString(), item.Format));
                    }
                    else
                    {
                        dataCell.SetCellValue(tb.Rows[i][item.Name].ToString());
                    }
                    dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                    //边框  
                    dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
                    dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
                    dataCell.CellStyle.BorderRight = CellBorderType.THIN;
                    dataCell.CellStyle.BorderTop = CellBorderType.THIN;
                    //边框颜色  
                    dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                    dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
                }
            }
            foreach (ReportPrint.TableColumn item in Columns)
            {
                int index = Columns.IndexOf(item);
                if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
                else { sheet.AutoSizeColumn(index); }
            }
            return workbook;
        }
        #endregion
        ///<summary>
        ///读取csv格式的Excel文件的方法 
        ///</ummary>
        ///<param name="path">待读取Excel的全路径</param>
        ///<returns></returns>
        public DataTable GetdataFromCVS(string fileName)
        {

            DataTable dt = new DataTable();

            StreamReader sr = new StreamReader(fileName, System.Text.Encoding.GetEncoding("GB2312"));

            string strTitle = sr.ReadLine();

            string[] strColumTitle = strTitle.Split(',');   //CVS 文件默认以逗号隔开

            for (int i = 0; i < strColumTitle.Length; i++)
            {

                dt.Columns.Add(strColumTitle[i].Replace("\"", "").Trim());

            }
            //string[] aryline;
            while (!sr.EndOfStream)
            {

                string strTest = sr.ReadLine();

                string[] strTestAttribute = strTest.Split(',');

                DataRow dr = dt.NewRow();

                for (int i = 0; i < strColumTitle.Length; i++)
                {

                    dr[i] = strTestAttribute[i].Replace("=", "").Replace("\"", "").Replace("'", "").Trim();

                }

                dt.Rows.Add(dr);

            }

            return dt;

        }
        ///<summary>
        ///读取csv格式的Excel文件的方法 
        ///</ummary>
        ///<param name="path">待读取Excel的全路径</param>
        ///<returns></returns>
        public DataTable GetdataFromCVS2(string fileName)
        {

            DataTable dt = new DataTable();

            StreamReader sr = new StreamReader(fileName, System.Text.Encoding.GetEncoding("GB2312"));

            string strTitle = sr.ReadLine();

            string[] strColumTitle = strTitle.Split(',');   //CVS 文件默认以逗号隔开

            for (int i = 0; i < strColumTitle.Length; i++)
            {

                dt.Columns.Add(strColumTitle[i].Replace("\"", "").Trim());

            }
            //string[] aryline;
            while (!sr.EndOfStream)
            {

                string strTest = sr.ReadLine();

                string[] strTestAttribute = strToAry(strTest);//请注意：此处变了strTest.Split(',');

                DataRow dr = dt.NewRow();

                for (int i = 0; i < strColumTitle.Length; i++)
                {

                    dr[i] = strTestAttribute[i].Replace("=", "").Replace("\"\"", "\"").Trim();

                }

                dt.Rows.Add(dr);

            }

            return dt;

        }
        private static string[] strToAry(string strLine)
        {
            string strItem = "";
            int iFenHao = 0;
            System.Collections.ArrayList lstStr = new System.Collections.ArrayList();
            for (int i = 0; i < strLine.Length; i++)
            {
                string strA = strLine.Substring(i, 1);
                if (strA == "\"")
                {
                    iFenHao = iFenHao + 1;
                }
                if (iFenHao == 2)
                {
                    iFenHao = 0;
                }
                if (strA == "," && iFenHao == 0)
                { lstStr.Add(strItem); strItem = ""; }
                else
                { strItem = strItem + strA; }
            }
            if (strItem.Length > 0)
                lstStr.Add(strItem);
            return (String[])lstStr.ToArray(typeof(string));
        }

        #region 写入CSV，附带路径方法

        public string WriteCSV(bool hasHeader, DataTable dtOutputCSV, TableColumnCollection Columns, string _splitChar)
        {

            if (dtOutputCSV == null)
            {
                throw new ArgumentNullException("Argument(dtOutputCSV) is null!");
            }

            StringBuilder sb = new StringBuilder();

            if (hasHeader)
            {

                foreach (ReportPrint.TableColumn item in Columns)
                {
                    sb.Append("\"" + item.Caption + "\"");
                    sb.Append(",");

                }

                sb.Remove(sb.Length - 1, 1);
                sb.Append(Environment.NewLine);
            }
            for (int i = 0; i < dtOutputCSV.Rows.Count; i++)
            {
                foreach (ReportPrint.TableColumn item in Columns)
                {


                    for (int j = 0; j < dtOutputCSV.Columns.Count; j++)
                    {
                        string str = dtOutputCSV.Rows[i][j].ToString();
                          if (str.Contains(",") || str.Contains("\"")  || str.Contains("\r") || str.Contains("\n")) //含逗号 冒号 换行符的需要放到引号中
                  {
                      str = string.Format("\"{0}\"", str);
                  }
                        if (dtOutputCSV.Columns[j].ColumnName == item.Name)
                            sb.Append("\"" + str + "\"" + ",");


                    }
                  
                }  
                  sb.Remove(sb.Length - 1, 1);
                  sb.Append(Environment.NewLine);
            }
            string filename = Guid.NewGuid().ToString() + ".csv";
            string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
            if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
            string ServerFileName = DirectoryName + "/" + filename;


            using (TextWriter tw = new StreamWriter(ServerFileName, false))
            {
                tw.Write(sb.ToString());

                tw.Flush();
                tw.Close();
            }
            return "ServerCookies/" + filename;
        }
        #endregion

        #region 导入
        public DataTable ImportExcel(string fileName)
        {
            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();
            IRow row;

            rows.MoveNext();
            row = (HSSFRow)rows.Current;
            int len = row.LastCellNum;
            for (int i = 0; i < len; i++)
            {
                ICell cell = row.GetCell(i);
                if (cell != null)
                {
                    dt.Columns.Add(cell.ToString().Trim());
                }
            }

            while (rows.MoveNext())
            {
                row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();

                for (int i = 0; i < len; i++)
                {
                    ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        //读取Excel格式，根据格式读取数据类型
                        switch (cell.CellType)
                        {
                            case CellType.BLANK: //空数据类型处理
                                dr[i] = "";
                                break;
                            case CellType.STRING: //字符串类型
                                dr[i] = cell.StringCellValue;
                                break;
                            case CellType.NUMERIC: //数字类型                                   
                                if (DateUtil.IsCellDateFormatted(cell))//日期类型
                                {
                                    dr[i] = cell.DateCellValue;
                                }
                                else//其他数字类型
                                {
                                    dr[i] = cell.NumericCellValue;
                                }

                                break;
                            case CellType.FORMULA:
                                HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssfworkbook);
                                dr[i] = e.Evaluate(cell).StringValue;
                                break;
                            default:
                                dr[i] = "";
                                break;
                        }

                        //if (cell.CellType == CellType.FORMULA || cell.CellType == CellType.NUMERIC)
                        //{
                        //    dr[i] = cell.DateCellValue;
                        //}
                        //else
                        //{


                        //    dr[i] = cell.ToString().Trim();
                        //}
                    }
                }
                dt.Rows.Add(dr);
            }

            return dt;
        }
        #endregion
        #region 导入
        public DataTable ImportExcel2(string fileName)
        {
            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();
            IRow row;

            rows.MoveNext();
            row = (HSSFRow)rows.Current;
            int len = row.LastCellNum;
            for (int i = 0; i < len; i++)
            {
                ICell cell = row.GetCell(i);
                if (cell != null)
                {
                    dt.Columns.Add(cell.ToString().Trim()+i.ToString());
                }
            }

            while (rows.MoveNext())
            {
                row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();

                for (int i = 0; i < len; i++)
                {
                    ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        //读取Excel格式，根据格式读取数据类型
                        switch (cell.CellType)
                        {
                            case CellType.BLANK: //空数据类型处理
                                dr[i] = "";
                                break;
                            case CellType.STRING: //字符串类型
                                dr[i] = cell.StringCellValue;
                                break;
                            case CellType.NUMERIC: //数字类型                                   
                                if (DateUtil.IsCellDateFormatted(cell))//日期类型
                                {
                                    dr[i] = cell.DateCellValue;
                                }
                                else//其他数字类型
                                {
                                    dr[i] = cell.NumericCellValue;
                                }

                                break;
                            case CellType.FORMULA:
                                HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssfworkbook);
                                dr[i] = e.Evaluate(cell).StringValue;
                                break;
                            default:
                                dr[i] = "";
                                break;
                        }

                        //if (cell.CellType == CellType.FORMULA || cell.CellType == CellType.NUMERIC)
                        //{
                        //    dr[i] = cell.DateCellValue;
                        //}
                        //else
                        //{


                        //    dr[i] = cell.ToString().Trim();
                        //}
                    }
                }
                dt.Rows.Add(dr);
            }

            return dt;
        }
        #endregion
        /// <summary>
        /// 解析Excel
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataSet LoadDataFromExcel(string filePath, string name)
        {
            try
            {
                string strConn;
                //   strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";
                strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                string sql = "SELECT * FROM [" + name + "$]";//可是更改Sheet名称，比如sheet2，等等  
                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, name);
                OleConn.Close();
                return OleDsExcle;
            }
            catch (Exception err)
            {
                //MessageBox.Show("数据绑定Excel失败! 失败原因：" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }
        }
    }
}