将CSV文件另存为excel

①将csv读取到datatable

②将datatable写到空白exel(使用NPOI)

    /// <summary>
        /// 获取CSV文件
        /// </summary>
        public void GetCSVFile()
        {

            string path = @"E:isec";
            DirectoryInfo root = new DirectoryInfo(path);
            FileInfo[] files = root.GetFiles();
            foreach (FileInfo item in files)
            {


                if (item.FullName.ToLower().Contains(".csv"))
                {
                    CSVFileHelper dddd = new CSVFileHelper();

                    string PATH = item.FullName;
                    DataTable dt = dddd.OpenCSV(PATH);
                    //将datatable转成excel
                    string Path = item.FullName.ToLower().Replace(".csv", string.Format("{0}{1}", DateTime.Now.ToString("yyyyMMddHHmmssfff"), ".xlsx"));

                    ExcelHelperExcel excelHelper = new ExcelHelperExcel();
                    excelHelper.SaveToExcel(Path, dt, ref _IsSuc, ref _Msg);
                }


            }
        }

  

    //自定义一个类

    /// <summary>
    /// CSV文件操作类
    /// </summary>
    public class CSVFileHelper
    {
        public DataTable OpenCSV(string filePath)//从csv读取数据返回table  
        {
            System.Text.Encoding encoding = GetType(filePath); //Encoding.ASCII;//  
            DataTable dt = new DataTable();
            System.IO.FileStream fs = new System.IO.FileStream(filePath, System.IO.FileMode.Open,
                System.IO.FileAccess.Read);

            System.IO.StreamReader sr = new System.IO.StreamReader(fs, encoding);

            //记录每次读取的一行记录  
            string strLine = "";
            //记录每行记录中的各字段内容  
            string[] aryLine = null;
            string[] tableHead = null;
            //标示列数  
            int columnCount = 0;
            //标示是否是读取的第一行  
            bool IsFirst = true;
            //逐行读取CSV中的数据  
            while ((strLine = sr.ReadLine()) != null)
            {
                if (IsFirst == true)
                {
                    tableHead = strLine.Split(',');
                    IsFirst = false;
                    columnCount = tableHead.Length;
                    //创建列  
                    for (int i = 0; i < columnCount; i++)
                    {
                        DataColumn dc = new DataColumn(tableHead[i]);
                        dt.Columns.Add(dc);
                    }
                }
                else
                {
                    aryLine = strLine.Split(',');
                    DataRow dr = dt.NewRow();
                    for (int j = 0; j < columnCount; j++)
                    {
                        try
                        {

                            if (aryLine.Length > j) dr[j] = aryLine[j];
                        }
                        catch (Exception)
                        {
                            continue;
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            if (aryLine != null && aryLine.Length > 0)
            {
                dt.DefaultView.Sort = tableHead[0] + " " + "asc";
            }

            sr.Close();
            fs.Close();
            return dt;
        }



        public DataTable OpenCSV2(string filePath)//从csv读取数据返回table  
        {
            System.Text.Encoding encoding = GetType(filePath); //Encoding.ASCII;//  
            DataTable dt = new DataTable();
            System.IO.FileStream fs = new System.IO.FileStream(filePath, System.IO.FileMode.Open,
                System.IO.FileAccess.Read);

            System.IO.StreamReader sr = new System.IO.StreamReader(fs, encoding);

            //记录每次读取的一行记录  
            string strLine = "";
            //记录每行记录中的各字段内容  
            string[] aryLine = null;
            string[] tableHead = null;
            //标示列数  
            int columnCount = 0;
            //标示是否是读取的第一行  
            bool IsFirst = true;
            //逐行读取CSV中的数据  
            while ((strLine = sr.ReadLine()) != null)
            {
                if (IsFirst == true)
                {
                    tableHead = strLine.Split('	');
                    IsFirst = false;
                    columnCount = tableHead.Length;

                    for (int i = 0; i < 20; i++)
                    {
                        DataColumn dc = new DataColumn(i.ToString());
                        dt.Columns.Add(dc);

                    }
                    //创建列  
                    DataRow dr = dt.NewRow();
                    for (int j = 0; j < tableHead.Count(); j++)
                    {
                        dr[j] = tableHead[j];
                    }
                    dt.Rows.Add(dr);
                }
                else
                {
                    try
                    {
                        aryLine = strLine.Split('	');
                        DataRow dr = dt.NewRow();
                        for (int j = 0; j < aryLine.Count(); j++)
                        {
                            dr[j] = aryLine[j];
                        }
                        dt.Rows.Add(dr);
                    }
                    catch (Exception)
                    {

                        throw;
                    }
                }
            }

            sr.Close();
            fs.Close();
            return dt;
        }


        /// 给定文件的路径,读取文件的二进制数据,判断文件的编码类型  
        /// <param name="FILE_NAME">文件路径</param>  
        /// <returns>文件的编码类型</returns>  

        public System.Text.Encoding GetType(string FILE_NAME)
        {
            System.IO.FileStream fs = new System.IO.FileStream(FILE_NAME, System.IO.FileMode.Open,
                System.IO.FileAccess.Read);
            System.Text.Encoding r = GetType(fs);
            fs.Close();
            return r;
        }

        /// 通过给定的文件流,判断文件的编码类型  
        /// <param name="fs">文件流</param>  
        /// <returns>文件的编码类型</returns>  
        public System.Text.Encoding GetType(System.IO.FileStream fs)
        {
            byte[] Unicode = new byte[] { 0xFF, 0xFE, 0x41 };
            byte[] UnicodeBIG = new byte[] { 0xFE, 0xFF, 0x00 };
            byte[] UTF8 = new byte[] { 0xEF, 0xBB, 0xBF }; //带BOM  
            System.Text.Encoding reVal = System.Text.Encoding.Default;

            System.IO.BinaryReader r = new System.IO.BinaryReader(fs, System.Text.Encoding.Default);
            int i;
            int.TryParse(fs.Length.ToString(), out i);
            byte[] ss = r.ReadBytes(i);
            if (IsUTF8Bytes(ss) || (ss[0] == 0xEF && ss[1] == 0xBB && ss[2] == 0xBF))
            {
                reVal = System.Text.Encoding.UTF8;
            }
            else if (ss[0] == 0xFE && ss[1] == 0xFF && ss[2] == 0x00)
            {
                reVal = System.Text.Encoding.BigEndianUnicode;
            }
            else if (ss[0] == 0xFF && ss[1] == 0xFE && ss[2] == 0x41)
            {
                reVal = System.Text.Encoding.Unicode;
            }
            r.Close();
            return reVal;
        }

        /// 判断是否是不带 BOM 的 UTF8 格式  
        /// <param name="data"></param>  
        /// <returns></returns>  
        private bool IsUTF8Bytes(byte[] data)
        {
            int charByteCounter = 1;  //计算当前正分析的字符应还有的字节数  
            byte curByte; //当前分析的字节.  
            for (int i = 0; i < data.Length; i++)
            {
                curByte = data[i];
                if (charByteCounter == 1)
                {
                    if (curByte >= 0x80)
                    {
                        //判断当前  
                        while (((curByte <<= 1) & 0x80) != 0)
                        {
                            charByteCounter++;
                        }
                        //标记位首位若为非0 则至少以2个1开始 如:110XXXXX...........1111110X   
                        if (charByteCounter == 1 || charByteCounter > 6)
                        {
                            return false;
                        }
                    }
                }
                else
                {
                    //若是UTF-8 此时第一位必须为1  
                    if ((curByte & 0xC0) != 0x80)
                    {
                        return false;
                    }
                    charByteCounter--;
                }
            }
            if (charByteCounter > 1)
            {
                throw new Exception("非预期的byte格式");
            }
            return true;
        }
    }

  

   /// <summary>
        /// 将table写入空白exel
        /// </summary>
        /// <param name="Path"></param>
        /// <param name="dt"></param>
        /// <param name="_IsSuc"></param>
        /// <param name="_Msg"></param>
        public void SaveToExcel(string ExportPath, DataTable dt, ref bool _IsSuc, ref string _Msg)
        {

            _IsSuc = false;
            _Msg = "导出失败";
            IWorkbook workbook = null;

            FileInfo item = new System.IO.FileInfo(System.Environment.CurrentDirectory + @"APP_DataSaveModel.xlsx");
            ExcelHelperExcel excelHelper = new ExcelHelperExcel();
            var excelFileStream = FileToStream(item.FullName);
            int typeExcel = Path.GetExtension(item.Name) == ".xls" ? 0 : 1;




            using (excelFileStream)
            {
                try
                {
                    if (typeExcel == 0)
                    {
                        workbook = new HSSFWorkbook(excelFileStream);
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(excelFileStream);
                    }


                    ISheet sheet = workbook.GetSheetAt(0); //取第一个表


                    int HeaderRow = 1;
                    int FjColumIndex = 0;
                    IRow CurrentRow = sheet.CreateRow(HeaderRow);


                    #region 将数据写入流


                    ///写入header
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        CurrentRow.CreateCell(i);
                        CurrentRow.GetCell(i).SetCellValue(dt.Columns[i].Caption);
                        if (dt.Columns[i].Caption.Contains("附件"))
                        {
                            FjColumIndex = i;
                            sheet.SetColumnWidth(FjColumIndex, 60 * 256);
                        }
                    }

                    HeaderRow = HeaderRow + 1;

                    bool IsNewRow = true;
                    int StartRow = 0;

                    ///写入表格内容
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {

                        DataRow dr = dt.Rows[i];


                        #region 新增行

                        //判断是否是新行
                        if (dr.ItemArray[0].ToString().Contains("附件") && string.IsNullOrEmpty(dr.ItemArray[1].ToString()))
                        {
                            IsNewRow = false;
                        }
                        else
                        {
                            IsNewRow = true;
                        }
                        if (IsNewRow)
                        {

                            if (StartRow != 0)
                            { 
                                //单元格合并
                                for (int c = 0; c < dt.Columns.Count; c++)
                                {
                                    sheet.AddMergedRegion(new CellRangeAddress(StartRow, HeaderRow + i-1, c, c));
                                }

                            }

                            CurrentRow = sheet.CreateRow(HeaderRow + i);
                            StartRow = HeaderRow + i;
                        }

                        #endregion

                        #region 添加数据

                        if (IsNewRow)
                        {
                            for (int a = 0; a < dr.ItemArray.Count(); a++)
                            {

                                if (a == FjColumIndex)
                                {
                                    ICell notesTitle = sheet.GetRow(StartRow).CreateCell(a);
                                    ICellStyle notesStyle = workbook.CreateCellStyle();
                                    notesStyle.WrapText = true;//设置换行这个要先设置 
                                    notesTitle.CellStyle = notesStyle;//设置换行 
                                    notesTitle.SetCellValue(dr.ItemArray[a].ToString());
                                    //sheet.GetRow(StartRow).CreateCell(a).SetCellValue(dr.ItemArray[a].ToString());

                                }
                                else
                                {
                                    sheet.GetRow(StartRow).CreateCell(a).SetCellValue(dr.ItemArray[a].ToString());
                                }
                                
                            }
                        }
                        else
                        {

                            

                            string fj = sheet.GetRow(StartRow).GetCell(FjColumIndex).ToString() + @"
";
                            sheet.GetRow(StartRow).GetCell(FjColumIndex).SetCellValue((fj + dr.ItemArray[0].ToString()).TrimEnd('
'));
                        }


                        #endregion

                    }


                    #endregion



                    //模板数据
                    MemoryStream ms = new MemoryStream();
                    //写入  
                    workbook.Write(ms);
                    using (FileStream fs = new FileStream(ExportPath, FileMode.Create, FileAccess.Write))
                    {

                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                        ms.Close();
                        ms.Dispose();

                    }

                    _IsSuc = true;

                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (workbook != null) workbook.Close();


                }
            }
        }

  

原文地址:https://www.cnblogs.com/lhlong/p/11271153.html