游戏开发— excel一键转换csv文件

xls文件一键转csv

都忘记了有多久没有更新了，时间过得好快，难忘的2020已经过去了5天了。2021从新开始

这类型的插件之前就写过的，当时没有保留，突然项目需要。就回去从新找，找不到了。只能从新写 了，害怕以后需要又找不到，于是就记录在这里吧 。

using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace ConsoleExcelParserF4
{
    class Program
    {
        static string configPath = null;            //配置路径
        static public string writeClent = null;       //客户端 写入路劲
        static public string writeServer = null;      //服务器 写入路劲
        static public string readPath = null;        // 读取路径

        static void Main(string[] args)
        {
            string str = System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName;   //当前路径
            readPath = str + "/../../../../../../" + "/ExcelConfig/excel";
            writeClent = str + "/../../../../../../" + "/ExcelConfig/client_csv";
            writeServer = str + "/../../../../../../" + "/ExcelConfig/server_csv";

            if (System.IO.Directory.Exists(writeClent) == false)//如果不存在就创建file文件夹
            {
                System.IO.Directory.CreateDirectory(writeClent);
                if (System.IO.Directory.Exists(writeClent) == false)
                {
                    Console.WriteLine("路径错误>>>>> " + writeClent);
                    Console.ReadKey();
                    return;
                }
            }

            if (System.IO.Directory.Exists(writeServer) == false)//如果不存在就创建file文件夹
            {
                System.IO.Directory.CreateDirectory(writeServer);
                if (System.IO.Directory.Exists(writeServer) == false)
                {
                    Console.WriteLine("路径错误>>>>> " + writeServer);
                    Console.ReadKey();
                    return;
                }
            }

            if (System.IO.Directory.Exists(readPath) == false)//读取配置文件
            {
                InitPatnCfg();
                if (System.IO.Directory.Exists(readPath) == false)
                {
                    Console.WriteLine("路径错误>>>>> " + readPath);
                    Console.ReadKey();
                    return;
                }
            }

            startRun();
        }

        static public void startRun()
        {
            Console.WriteLine("==================== 开始 ====================");
            int successesNum = 0;

            var files = Directory.GetFiles(readPath);
            foreach (var file in files)
            {
                var ext = Path.GetExtension(file);
                if (file.IndexOf("~$") != -1)   // 缓存数据过滤
                {
                    continue;
                }

                if (ext.Equals(".xls") || ext.Equals(".xlsx") || ext.Equals("xlsm"))
                {
                    IWorkbook workbook = null;
                    using (var stream = File.OpenRead(file))
                    {
                        if (ext.Equals(".xlsx") || ext.Equals(".xlsm"))
                            workbook = new XSSFWorkbook(stream);
                        else if (ext.Equals(".xls"))
                            workbook = new HSSFWorkbook(stream);

                        var vSheetCount = workbook.NumberOfSheets;
                        if (vSheetCount == 0)
                        {
                            Console.WriteLine("无法正确读取 : {0}", file);
                            Console.ReadKey();
                            return;
                        }
                        int curTableIndex = 0;
                        while (curTableIndex < vSheetCount)
                        {
                            ISheet worksheet = workbook.GetSheetAt(curTableIndex);
                            if (worksheet.SheetName.ToLower().Contains("sheet"))
                            {
                                curTableIndex++;
                                continue;
                            }

                            DataTable tb = ExlseToObj(worksheet, worksheet.SheetName, 1);
                            string txtFile = worksheet.SheetName + ".csv";
                            dataTableToCsv(tb, writeClent + "/" + txtFile);

                            DataTable tb1 = ExlseToObj(worksheet, worksheet.SheetName, 2);
                            string txtFil1e = worksheet.SheetName + ".csv";
                            dataTableToCsv(tb, writeServer + "/" + txtFile);

                            curTableIndex++;
                            successesNum++;
                        }
                    }
                }
            }
            Console.WriteLine("转化已完成 转化个数{0}", successesNum);
            Console.WriteLine("==================== 结束 ====================");
        }

        // 初始化文件配置
        static public bool InitPatnCfg()
        {
            configPath = System.Environment.CurrentDirectory + "\\config";    //配置路径
            string strJson = File.ReadAllText(configPath + "\\pathCfg.txt", Encoding.UTF8);
            if (null != strJson)
            {
                string[] strPaths = strJson.Split(',');
                if (strPaths.Length == 4 || strPaths.Length == 5)
                {
                    string[] strs1 = strPaths[0].Split('=');
                    //writeClent = strs1[1].Replace(" ", "");
                    string oldReadPath = readPath;
                    string[] strs2 = strPaths[1].Split('=');
                    readPath = strs2[1].Replace(" ", ""); ;

                    Console.WriteLine("路径错误>>>>> " + oldReadPath + "  读取配置路径 >>>>>> " + readPath);
                    return true
                        ;
                }
                return false;
            }
            else
            {
                return false;
            }
        }

        public static DataTable ExlseToObj(ISheet worksheet, string name, int type)
        {
            int newColums = worksheet.GetRow(1).LastCellNum;
            var columns = worksheet.GetRow(0).LastCellNum;
            m_cToS_list.Clear();
            IRow row = worksheet.GetRow(3);
            for (var j = 0; j < columns; j++)
            {
                if (row.GetCell(j) != null)
                {
                    string tmp = row.GetCell(j).ToString();
                    m_cToS_list.Add(tmp.ToLower().Trim());
                }
                else
                {
                    m_cToS_list.Add("");
                }
            }

            DataTable tb = new DataTable();
            tb.TableName = name;

            if (newColums != columns)
            {
                Console.WriteLine("数据表出错！！！！ 第一行和第二行不匹配" + name);
                Console.ReadKey();
                return null;
            }
            dicsRepeatField.Clear();

            row = worksheet.GetRow(1);
            for (var j = 0; j < columns; j++)
            {
                if (row.GetCell(j) != null)
                {
                    var dc = new DataColumn();
                    string tmp = row.GetCell(j).ToString();

                    if (string.IsNullOrEmpty(tmp))
                    {
                        Console.WriteLine("基础类型不能为空！！！" + tmp + " >>> " + name);
                        Console.ReadKey();
                    }

                    if (IsRepeatField(tmp, name) == false)
                    {
                        dc.ColumnName = GetBasicType(tmp.ToLower().Trim());
                        tb.Columns.Add(dc);
                    }
                }
            }

            // 增加内容
            DataRow dr = tb.NewRow();
            row = worksheet.GetRow(0);
            for (var j = 0; j < columns; j++)
            {
                if (IsNeedWruite(j, type) == false)
                {
                    continue;
                }

                string tmp = row.GetCell(j).ToString();
                if (IsBasicType(tmp) == false)
                {
                    Console.WriteLine("数据表出错！！！！ 基本类型错误" + tmp + " >>> " + name);
                    Console.ReadKey();
                }
                dr[j] = GetBasicType(tmp.ToLower().Trim());
            }
            tb.Rows.Add(dr);

            dr = tb.NewRow();
            row = worksheet.GetRow(1);
            for (var j = 0; j < columns; j++)
            {
                if (IsNeedWruite(j, type) == false)
                {
                    continue;
                }
                string tmp = row.GetCell(j).ToString();
                dr[j] = tmp.ToLower().Trim();
            }
            tb.Rows.Add(dr);

            DataExtractionToDataTable(worksheet, type, ref tb);
            tb.AcceptChanges();
            return tb;
        }

        static void DataExtractionToDataTable(ISheet sheet, int type, ref DataTable tb)
        {
            IRow firstRow = sheet.GetRow(0);
            int rowMax = firstRow.LastCellNum;

            for (int i = 4; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row.GetCell(0) == null)
                    continue;
                string sn = row.GetCell(0).ToString();
                if (string.IsNullOrEmpty(sn))
                    continue;
                DataRow dr = tb.NewRow();
                for (int j = 0; j < rowMax; j++)
                {
                    if (IsNeedWruite(j, type) == false)
                    {
                        continue;
                    }
                    string tmp = "";
                    if (row.GetCell(j) != null)
                    {
                        tmp = row.GetCell(j).ToString();
                    }
                    dr[j] = tmp;
                }
                tb.Rows.Add(dr);
            }
        }

        //转Csv
        public static void dataTableToCsv(DataTable table, string file)
        {
             FileStream fs = new FileStream(file, FileMode.OpenOrCreate);
            StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);

            foreach (DataRow row in table.Rows)
            {
                string line = "";
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    line += row[i].ToString().Trim() + ","; //内容：
                }
                line = line.Substring(0, line.Length - 1) + "\n";
                sw.Write(line);
            }
            sw.Close();
            fs.Close();
        }

        //------------------------------ 条件判断 -------------------------

        // 行列是否相等
        public bool IsRowsColumns()
        {
            return true;
        }

        public static Dictionary<string, string> dicsRepeatField = new Dictionary<string, string>();
        public static List<string> m_cToS_list = new List<string>();


        public static bool IsRepeatField(string str, string tabelName)
        {
            if (dicsRepeatField.ContainsKey(str))
            {
                Console.WriteLine("数据表出错！！！！ 出现重复字段" + str + " >>> " + tabelName);
                Console.ReadKey();
                return true;
            }
            else
            {
                dicsRepeatField.Add(str, str);
            }
            return false;
        }

        // 是否基本类型是否正确
        public static bool IsBasicType(string type)
        {
            switch (type)
            {
                case "tinyint":
                case "int":
                case "float":
                case "bool":
                case "vector2":
                case "vector3":
                case "vector4":
                case "string":
                case "int32":
                case "uint":
                case "long":
                case "ulong":
                case "uint32":
                    return true;
                default:
                    return false;
            }
        }

        //转换正确的格式
        public static string GetBasicType(string tmp)
        {
            if (tmp == "int[]")
            {
                tmp = "string";
            }
            else if (tmp == "vector3" || (tmp == "vector2") || (tmp == "vector4s"))
            {
                tmp = "string";
            }
            else if (tmp == "tinyint")
            {
                tmp = "int";
            }
            return tmp;
        }

        // 是否需要写入
        private static bool IsNeedWruite(int index, int type)
        {
            for (int i = 0; i < m_cToS_list.Count; i++)
            {
                if (i == index && type == 1)
                {
                    string str = m_cToS_list[i].ToLower();
                    if (string.IsNullOrEmpty(str) == false || str == "c" || str == "cs")
                    {
                        return true;
                    }
                }
                else if (i == index && type == 2)
                {
                    string str = m_cToS_list[i].ToLower();
                    if (string.IsNullOrEmpty(str) == false || str == "s" || str == "cs")
                    {
                        return true;
                    }
                }
            }
            return false;
        }
    }

}

https://mp.csdn.net/console/upDetailed
下载链接