【ExcelExport 】，Excel转lua，json，xml开发工具

正文：

基于https://github.com/monkey256/ExcelExport 开发的再次改版，

相比旧版：

1.新版支持公式导出，多语言导出
2.去除不必要的功能，优化核心代码
3.数值校验
4.描述表格与数值表格合二为一，方便查看
5.支持插入空列
6.根据定义字段导出客户端与服务器两份
7.语言包独立处理，根据语言生成多份.lua文件
 

主要功能

1.软件说明：
    “一键导出”
        对指定目录下所有.xlsx与.xls文件分别导出到输出目录下的客户端.lua与服务器.xml文件
    “语言包导出”
        关键词【Language】，检测到相应文件名时，根据不同语言导出多份.lua文件

2.通过命令行启动说明：
    ***查看帮助界面***  -h|-help
        示例: tablegen2.exe -h

    ***一键导出***  -p handyExp
        示例: tablegen2.exe -i fullPath -o outputDir -p handyExp

    ***按指定类型导出***  -p xml|json|lua
        示例: tablegen2.exe -i fullPath -o outputDir -p lua

 

using System;
using System.Collections.Generic;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;
using System.Collections;
using tablegen2.common;

namespace tablegen2.logic
{
    public static class TableExcelReader
    {
        // def表参数占位索引
        private const int DEF_HEADER_INDEX = 3;
        // data表参数索引
        private const int DATA_HEADER_INDEX = DEF_HEADER_INDEX + 1;
        // data表数据索引
        private const int DATA_DATA_INDEX = DEF_HEADER_INDEX + 2;

        // 根据路径读取解析xls
        public static TableExcelData loadFromExcel(string filePath)
        {
            if (!File.Exists(filePath))
                throw new Exception(string.Format("{0} 文件不存在！", filePath));

            var ext = Path.GetExtension(filePath).ToLower();
            if (ext != ".xls" && ext != ".xlsx")
                throw new Exception(string.Format("无法识别的文件扩展名 {0}", ext));

            var headers = new List<TableExcelHeader>();
            var rows = new List<TableExcelRow>();

            var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            // 解析xls文档核心模块
            var workbook = ext == ".xls" ? (IWorkbook)new HSSFWorkbook(fs) : (IWorkbook)new XSSFWorkbook(fs);
            fs.Close();

            _readDataFromWorkbook(workbook, headers, rows);

            return new TableExcelData(headers, rows);
        }

        private static void _readDataFromWorkbook(IWorkbook wb, List<TableExcelHeader> defHeader, List<TableExcelRow> rows)
        {
            var dataSheet = _checkSheetValidity(wb, AppData.Config.SheetNameForData);

            var defHeaderIdx = new ArrayList();
            var dataList = new List<List<string>>();

            // 检查Sheet是否存在空行
            _checkSheetIsNullRow(dataSheet, AppData.Config.SheetNameForData);
            // 读取def
            _readDefSheet(dataSheet, defHeader);
            // 检查data首行定义字段数据
            _checkHeadersFromDataSheet(dataSheet, defHeader);
            // 检查data中的数值是否符合指定类型
            _checkAllDataSheetValidity(dataSheet, defHeader);
            // 检查data中的主键ID是否空值或重复
            _checkDataSheetKeyUnique(dataSheet, defHeader, "Id");
            // 读取data
            _readDataSheet(dataSheet, defHeader, rows);
        }

        private static ISheet _checkSheetValidity(IWorkbook wb, string name)
        {
            var sheet = wb.GetSheet(name);
            if (sheet == null)
            {
                throw new Exception(string.Format("工作簿'{0}'不存在", name));
            }
            return sheet;
        }


        // 检查Sheet是否存在空行
        private static void _checkSheetIsNullRow(ISheet sheet, string sheetName)
        {
            string errMsg = string.Empty;
            for (int idxRow = 1; idxRow <= sheet.LastRowNum; idxRow++)
            {
                var rd = sheet.GetRow(idxRow);
                if (rd == null)
                {
                    errMsg = errMsg + string.Format("工作簿'{0}'中第{1}行为空 \n", sheetName, (idxRow + 1).ToString());
                }
            }

            if (!String.IsNullOrEmpty(errMsg))
            {
                throw new Exception(errMsg);
            }
        }

        // 读取def
        private static void _readDefSheet(ISheet sheet, List<TableExcelHeader> headers)
        {
            string errMsg = string.Empty;
            
            var rowList = new List<IRow>();
            var strList = new List<string>();
            for (int i = 0; i <= DEF_HEADER_INDEX; i++)
            {
                rowList.Add(sheet.GetRow(i));
                strList.Add(string.Empty);
            }

            var maxCellNum = sheet.GetRow(0).LastCellNum;
            for (int idx = 0; idx < maxCellNum; idx++)
            {
                for (int idxRow = 0; idxRow < rowList.Count; idxRow++)
                {
                    if (rowList[idxRow] != null)
                    {
                        strList[idxRow] = _convertCellToString(rowList[idxRow].GetCell(idx));
                    }
                }

                Log.Msg("解析Def ==> Name【{0}】 Type【{1}】 ExpType【{2}】 Desc【{3}】", strList[0], strList[1], strList[2], strList[3]);

                if (!string.IsNullOrEmpty(strList[0]) && !string.IsNullOrEmpty(strList[1]) && !string.IsNullOrEmpty(strList[2]))
                {
                    var header = new TableExcelHeader();
                    header.FieldName = strList[0];
                    header.FieldType = strList[1].ToLower();
                    header.FieldExpType = strList[2];
                    header.FieldDesc = strList[3];
                    header.FieldIndex = -1;
                    headers.Add(header);
                }
                else if (!(string.IsNullOrEmpty(strList[0]) && string.IsNullOrEmpty(strList[1]) && string.IsNullOrEmpty(strList[2])))
                {
                    errMsg = errMsg + string.Format("工作簿Def中[{0},{1}]或[{0},{2}]或[{0},{3}]数据异常缺失！ \n", _sheetNumToEng(idx), 1, 2, 3);
                }          
            }

            if (!String.IsNullOrEmpty(errMsg))
            {
                throw new Exception(errMsg);
            }

            // 检测Def是否包含ID字段
            if (headers.Find(a => a.FieldName == "Id") == null)
            {
                throw new Exception(string.Format("工作簿Def中不存在Id字段！"));
            }
        }

        // 读取Data首行定义字段数据
        private static void _checkHeadersFromDataSheet(ISheet sheet, List<TableExcelHeader> headers)
        {
            string errMsg = string.Empty;
            var rd = sheet.GetRow(DATA_HEADER_INDEX); // 取第五行
            for (int idxCell = 0; idxCell < rd.LastCellNum; idxCell++)
            {
                var cell = rd.GetCell(idxCell);
                if (cell != null && !String.IsNullOrEmpty(cell.StringCellValue))
                {
                    int HeadIdx = headers.FindIndex(item => item.FieldName.Equals(cell.StringCellValue));
                    if (HeadIdx != -1)
                    {
                        headers[HeadIdx].FieldIndex = idxCell;
                    }
                    else
                    {
                        // data缺少相应数据
                        errMsg = errMsg + string.Format("工作簿Data中存在未匹配的数据:'{0}' \n", cell.StringCellValue);
                    }
                }
            }

            foreach (var list in headers)
            {
                if (list.FieldIndex == -1)
                {
                    errMsg = errMsg + string.Format("工作簿Def中存在未匹配的数据:'{0}' \n", list.FieldName);
                }
            }

            if (!String.IsNullOrEmpty(errMsg))
            {
                throw new Exception(errMsg);
            }
        }

        // 检查data中的所有数值是否符合指定类型
        private static void _checkAllDataSheetValidity(ISheet sheet, List<TableExcelHeader> headers)
        {
            //Log.Msg("LastRowNum {0}", sheet.LastRowNum.ToString());
            string errMsg = string.Empty;
            var rd = sheet.GetRow(DATA_HEADER_INDEX); // 取第五行
            for (int idxHead = 0; idxHead < headers.Count; idxHead++)
            {
                errMsg = errMsg + _checkDataSheetKeyValidity(sheet, headers, headers[idxHead].FieldName);
            }
            
            if (!String.IsNullOrEmpty(errMsg))
            {
                throw new Exception(errMsg);
            }
        }

        // 检查data中的键值是否符合类型
        private static string _checkDataSheetKeyValidity(ISheet sheet, List<TableExcelHeader> headers, string key)
        {
            string errMsg = string.Empty;
            int idx = headers.FindIndex(item => item.FieldName.Equals(key));
            if (idx != -1)
            {
                var fieldType = headers[idx].FieldType;
                var fieldIndex = headers[idx].FieldIndex;
                var cellList = _getAllCellByIndex(sheet, fieldIndex);

                for (int idxCell = 0; idxCell < cellList.Count; idxCell++)
                {
                    var cell = cellList[idxCell];
                    var cType = cell.CellType;
                    
                    bool isErr = false;
                    //Log.Msg("index:【{0},{1}】headType:【{2}】cellType:【{3}】toStr:【{4}】", fieldIndex, idxCell, fieldType, cType, _convertCellToString(cell));

                    // 忽略空白单单元(TEST)
                    if (cType == CellType.Blank)
                    {
                        continue;
                    }

                    switch (fieldType)
                    {
                        case "int":
                            if ((cType != CellType.Numeric) || (cType == CellType.Numeric && !StrRegex.IsInteger(cell.NumericCellValue.ToString())))
                            {
                                isErr = true;
                            }
                            break;
                        case "double":
                            if ((cType != CellType.Numeric) || (cType == CellType.Numeric && !StrRegex.IsNumber(cell.NumericCellValue.ToString())))
                            {
                                isErr = true;
                            }
                            break;
                        case "string":
                            //if (cType != CellType.String)
                            //{
                            //    isErr = true;
                            //}
                            break;
                        case "formula":
                            if (cType != CellType.Formula)
                            {
                                isErr = true;
                            }
                            break;
                        case "bool":
                            if (cType != CellType.Boolean)
                            {
                                isErr = true;
                            }
                            break;
                        default:
                            isErr = true;
                            break;
                    }

                    if (isErr == true)
                    {
                        errMsg = errMsg + string.Format("工作簿Data中 [{0},{1}]={3} 不符合或ExcelExport暂不支持{2}类型 \n", _sheetNumToEng(fieldIndex), idxCell + DEF_HEADER_INDEX + 3, fieldType, _convertCellToString(cell));
                    }
                }
            }
            else
            {
                errMsg = errMsg + string.Format("工作簿Data中的未找到{0}主键 \n", key);
            }

            return errMsg;
        }

        // 检查data中的键值是否空值或重复
        private static void _checkDataSheetKeyUnique(ISheet sheet, List<TableExcelHeader> headers, string key)
        {
            string errMsg = string.Empty;
            int idx = headers.FindIndex(item => item.FieldName.Equals(key));
            if (idx != -1)
            {
                var ids = new HashSet<string>();
                var cellList = _getAllCellByIndex(sheet, headers[idx].FieldIndex);
                for (int idxCell = 0; idxCell < cellList.Count; idxCell++)
                {
                    var cell = cellList[idxCell];
                    var idKey = _convertCellToString(cell);
                    if (String.IsNullOrEmpty(idKey))
                    {
                        errMsg = errMsg + string.Format("工作簿Data中Id主键第{0}行值为空 \n", idxCell + DEF_HEADER_INDEX + 3);
                    }
                    else if (ids.Contains(idKey))
                    {
                        errMsg = errMsg + string.Format("工作簿Data中Id主键第{0}行已存在：{1} \n", idxCell + DEF_HEADER_INDEX + 3, idKey);
                    }

                    ids.Add(idKey);
                }
            }
            else
            {
                errMsg = errMsg + string.Format("工作簿Data中的未找到{0}主键 \n", key);
            }

            if (!String.IsNullOrEmpty(errMsg))
            {
                throw new Exception(errMsg);
            }
        }

        // 读取Data
        private static void _readDataSheet(ISheet sheet, List<TableExcelHeader> headers, List<TableExcelRow> rows)
        {
            // 索引从Data数据开始
            for (int idxRow = DATA_DATA_INDEX; idxRow <= sheet.LastRowNum; idxRow++)
            {
                var rd = sheet.GetRow(idxRow);
                var ds = new List<string>();
                for (int idxHead = 0; idxHead < headers.Count; idxHead++)
                {
                    var val = _convertCellToString(rd.GetCell(headers[idxHead].FieldIndex));
                    ds.Add(val);
                }

                // 增添excelRow
                rows.Add(new TableExcelRow() { StrList = ds });
            }
        }

        // 数字转英文
        private static string _sheetNumToEng(int idx)
        {
            var cur = idx + 1 + 64;
            string rowIex = string.Empty;
            if (65 <= cur && cur <= 90)
            {
                byte[] btNumber = new byte[] { (byte)cur };
                System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding();
                rowIex = asciiEncoding.GetString(btNumber);
            }
            else
            {
                rowIex = (idx + 1).ToString();
            }

            return rowIex;
        }

        // 获取单列所有数据,索引从Data数据开始
        private static List<ICell> _getAllCellByIndex(ISheet sheet, int index)
        {
            var list = new List<ICell>();

            for (int i = DATA_DATA_INDEX; i <= sheet.LastRowNum; i++)
            {
                var rd = sheet.GetRow(i);
                // 单单元为NULL时，手动赋值为空白格
                if (rd.GetCell(index) == null) {
                    rd.CreateCell(index, CellType.Blank);
                }
                list.Add(rd.GetCell(index));
            }
        
            return list;
        }

        // 单单元转String
        private static string _convertCellToString(ICell cell)
        {
            string r = string.Empty;
            if (cell != null)
            {
                switch (cell.CellType)
                {
                    // 布尔值
                    case CellType.Boolean:
                        r = cell.BooleanCellValue.ToString();
                        break;
                    // 数字类型
                    case CellType.Numeric:
                        r = cell.NumericCellValue.ToString();
                        break;
                    // 公式类型
                    case CellType.Formula:
                        r = cell.NumericCellValue.ToString();
                        break;
                    // 默认String
                    default:
                        r = cell.StringCellValue;
                        break;
                }
            }
            return r;
        }
    }
}

工程下载链接：https://download.csdn.net/download/Le_Sam/12822484