C#使用CSV及EXCEL

 

专门找到一个插件，但是似乎加载大文件CSV会崩掉，技术支持给出的解释是商业版本不会。

于是后来我自己写了，但还决定把这个代码附上，也算对这个工作总结。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using Spire.Xls;
using Spire.Xls.Collections;
namespace ParsCsv
{
    class Program
    {
        public UInt32 Grp0x01109216Cnt = 0;
        private static bool firstTimeFlg = true;
        private static int timStampre = 0;
        static void Main(string[] args)
        {
            //TransferDoc();
            ReadCsv();
            Console.WriteLine("hello\r\n");
            Console.ReadKey();
        }
        static void TransferDocx()
        {
            //保存excel
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("t1.csv", ",", 1, 1);
            workbook.Worksheets[0].Name = "sheet1";
            workbook.SaveToFile("toExcel.xlsx", ExcelVersion.Version2013);

            workbook.LoadFromFile("toExcel.xlsx");
            //创建工作本
            Worksheet sheet = workbook.Worksheets[0];
            AutoFiltersCollection filters = sheet.AutoFilters;
            filters.Range = sheet.Range[1, 6, sheet.LastRow, 6];
            filters.AddFilter(0, "0x01109216");
            filters.Filter();
            sheet.DeleteColumn(1);
            sheet.DeleteColumn(2);
            sheet.DeleteColumn(2);
            sheet.DeleteColumn(2);
            sheet.DeleteColumn(3);
            sheet.DeleteColumn(3);


            //设置数据自适应列宽
            sheet.AllocatedRange.AutoFitColumns();
            //分列数据
            string[] splitText = null;
            string text = null;
            for (int i = 1; i < sheet.LastRow; i++)
            {
                text = sheet.Range[i + 1, 4].Text;
                //拆分按逗号作为分隔符的数据列(注意这里分隔符以英文输入状态下的符号为准）
                splitText = text.Split(' ');
                //保存被拆分的数据到数组，将数组项写入列
                for (int j = 0; j < splitText.Length; j++)
                {
                    sheet.Range[i + 1, j + 5].Text = splitText[j];
                }
            }

  
            //添加一个新的工作表
            Worksheet sheet1 = workbook.Worksheets.Add("Copy");
            //将第一个工作表复制到新添加的工作表
            sheet1.CopyFrom(sheet);
            //保存文件
            sheet.Remove();

            workbook.SaveToFile("outputx.xlsx", ExcelVersion.Version2013);

           



        }
        static void TransferDoc()
        {
            Workbook workbook = new Workbook();
            //workbook.LoadFromFile("t4_0006.csv", ",", 1, 1);
            workbook.LoadFromFile("t4_0006.csv", ",", 1, 1);
            workbook.Worksheets[0].Name = "sheet";
            workbook.SaveToFile("toExcel.xlsx", ExcelVersion.Version2013);
            //workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");

            workbook.LoadFromFile("toExcel.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            //sheet.DeleteRow(1);
            

            AutoFiltersCollection filters = sheet.AutoFilters;
            filters.Range = sheet.Range[1, 6, sheet.LastRow, 6];
            filters.AddFilter(0, "0x01109216");
            filters.Filter();
            sheet.DeleteColumn(1);
            sheet.DeleteColumn(2);
            sheet.DeleteColumn(2);
            sheet.DeleteColumn(2);
            sheet.DeleteColumn(3);
            sheet.DeleteColumn(3);
            //sheet.DeleteColumn(7);
            //sheet.DeleteColumn(8);

            workbook.SaveToFile("output.xlsx", ExcelVersion.Version2013);

            Workbook book = new Workbook();
            book.LoadFromFile("output.xlsx");
            Worksheet sheet1 = book.Worksheets[0];
            //从第2行数据遍历到最后一行
            string[] splitText = null;
            string text = null;
            for (int i = 1; i < sheet1.LastRow; i++)
            {
                text = sheet1.Range[i + 1, 4].Text;
                //拆分按逗号作为分隔符的数据列(注意这里分隔符以英文输入状态下的符号为准）
                splitText = text.Split(' ');
                //保存被拆分的数据到数组，将数组项写入列
                for (int j = 0; j < splitText.Length; j++)
                {
                    sheet1.Range[i + 1,  j +5].Text = splitText[j];
                }
            }
            //设置数据自适应列宽
            sheet1.AllocatedRange.AutoFitColumns();

            book.SaveToFile("result.xlsx", ExcelVersion.Version2013);

            //加载Excel文件
            Workbook workbook2 = new Workbook();
            workbook.LoadFromFile("result.xlsx");
            //获取第一个工作表
            Worksheet sheet3 = workbook.Worksheets[0];
            //添加一个新的工作表
            Worksheet sheet4 = workbook.Worksheets.Add("Copy");
            //将第一个工作表复制到新添加的工作表
            sheet4.CopyFrom(sheet3);
            //保存文件
            sheet3.Remove();
            workbook.SaveToFile("Final.xlsx");


        }
    }
}