Npoi导入导出Execl

读取csv格式时内容含有逗号的无法处理

using System;
using System.Data;
using System.IO;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;


namespace ProductBLL.download
{
    public class ExcelHelper
    {

        #region NPOI读取导入的文件，将xlsx，xls文件的数据读取到DataTable中

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">文件路径</param>
        ///<param name="fileExtension">后缀名</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTable(string fileName, string fileExtension, bool isFirstRowColumn)
        {
            IWorkbook workbook = null;
            FileStream fs = null;
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {
                //把文件内容导入到工作薄当中，然后关闭文件
                using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    //根据不同版本获取第一个工作表的数据
                    if (fileExtension == ".xls") // 2003版本
                    {
                        workbook = new HSSFWorkbook(fs);
                        // sheet = workbook.GetSheet("SheetName"); 可以根据Sheet工作表的名字来获取
                        sheet = workbook.GetSheetAt(0) as HSSFSheet;
                    }

                    else if (fileExtension == ".xlsx") // 2007版本
                    {
                        workbook = new XSSFWorkbook(fs);
                        sheet = workbook.GetSheetAt(0) as XSSFSheet;
                    }
                }

                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    #region 第一行是否是作为列名
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }
                    #endregion


                    //最后一行的标号
                    int rowCount = sheet.LastRowNum;
                    #region 从第一行开始遍历数据保存到DataRow


                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                    #endregion
                }

                return data;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }


        #endregion

        #region 读取导入的文件，将CSV文件的数据读取到DataTable中
        /// <summary>
        /// 将CSV文件的数据读取到DataTable中
        /// </summary>
        /// <param name="">文件地址</param>
        /// <returns>返回读取了CSV数据的DataTable</returns>
        public static DataTable OpenCSV(string fileName)
        {




            DataTable dt = new DataTable();
            FileStream fs = new FileStream(fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read);
            StreamReader sr = new StreamReader(fs, System.Text.Encoding.Default);
            try
            {
                //记录每次读取的一行记录
                string strLine = "";
                //记录每行记录中的各字段内容
                string[] aryLine;
                //标示列数
                int columnCount = 0;
                //标示是否是读取的第一行
                bool IsFirst = true;

                //逐行读取CSV中的数据  注意要确保文本中没有逗号
                while ((strLine = sr.ReadLine()) != null)
                {
                    aryLine = strLine.Split(','); //把读取到的内容分割
                    if (IsFirst == true)
                    {
                        IsFirst = false;
                        columnCount = aryLine.Length;
                        //创建列
                        for (int i = 0; i < columnCount; i++)
                        {
                            DataColumn dc = new DataColumn(aryLine[i]);
                            dt.Columns.Add(dc);
                        }
                    }
                    else
                    {
                        DataRow dr = dt.NewRow(); //创建行
                        for (int j = 0; j < columnCount; j++)
                        {
                            dr[j] = aryLine[j];
                        }
                        dt.Rows.Add(dr);
                    }
                }

                sr.Close();
                fs.Close();

            }
            catch (Exception ex)
            {
                //  Mes += "文件导入失败，请检查数据格式！" + ex.ToString() + "/r/n";
            }

            finally
            {
                sr.Close();
                fs.Close();
            }

            return dt;
        }
        #endregion


        #region DataTable导出Execl
        public static MemoryStream TableToExcel(DataTable dt, string WorkbookType)
        {
            IWorkbook workbook = null;
            if (WorkbookType.ToLower() == "xls")
                workbook = new HSSFWorkbook();
            else
                workbook = new XSSFWorkbook();//xlxs
            ISheet sheet = workbook.CreateSheet("Sheet1");
            //表头  
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }
            //数据  
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                cell = row1.CreateCell(0);
                //cell.SetCellValue(dt.Rows[i][0].ToString());
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);//生成内存流
            return stream;
        }
        #endregion

   


        #region DataTable生成csv
        public static bool TableToCsv(DataTable dt, string path)
        {

            bool isresult= false;
            System.IO.FileStream fs = new FileStream(path, System.IO.FileMode.Create, System.IO.FileAccess.Write);
            StreamWriter sw = new StreamWriter(fs, new System.Text.UnicodeEncoding());;
            //Tabel header
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sw.Write(dt.Columns[i].ColumnName);
                sw.Write("	");
            }
            sw.WriteLine("");
            //Table body
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    sw.Write(DelQuota(dt.Rows[i][j].ToString()));
                    sw.Write("	");
                }
                sw.WriteLine("");
            }
            sw.Flush();
            sw.Close();
            if (File.Exists(path))
            {
                isresult = true;
            }
            return isresult;
           
        }
        public static string DelQuota(string str)
        {
            string result = str;
            string[] strQuota = { "~", "!", "@", "#", "$", "%", "^", "&", "*", "(", ")", "`", ";", "'", ",", ".", "/", ":", "/,", "<", ">", "?" };
            for (int i = 0; i < strQuota.Length; i++)
            {
                if (result.IndexOf(strQuota[i]) > -1)
                    result = result.Replace(strQuota[i], "");
            }
            return result;
        }
        #endregion

      


    }
}

protected void downloadfile(DataTable dd, string s_path)
        {
               

                #region 下载服务器上生成的execl文件
                System.IO.FileInfo file = new System.IO.FileInfo(s_path);
                HttpContext.Current.Response.ContentType = "application/ms-download";
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.AddHeader("Content-Type", "application/octet-stream");
                HttpContext.Current.Response.Charset = "utf-8";
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(file.Name, System.Text.Encoding.UTF8));
                HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
                HttpContext.Current.Response.WriteFile(file.FullName);
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.Clear();
                //下载完成后删除服务器下生成的文件
                if (File.Exists(s_path))
                {
                    File.Delete(s_path);

                }
                HttpContext.Current.Response.End(); ;
                #endregion

            #region   将二进制字符串数组写入输出流
            
            HttpContext.Current.Response.ContentType = "application/ms-download";
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("Content-Type", "application/octet-stream");
            HttpContext.Current.Response.Charset = "utf-8";

            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("222.xls", Encoding.UTF8));//222.xls下载是默认文件名
           
            HttpContext.Current.Response.BinaryWrite(ProductBLL.download.ExcelHelper.TableToExcel(dd,"xls").GetBuffer());
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.Clear();
           
            HttpContext.Current.Response.End();
            #endregion