public static void ExportSheet(ISheet sheet, StreamWriter sw, IFormulaEvaluator formulaEvaluator) { int cellCount = sheet.GetRow(3).LastCellNum; ExcelCellInfo[] cellInfos = new ExcelCellInfo[cellCount]; for (int i = 2; i < cellCount; ++i) { string fieldDesc = GetCellString(sheet, 2, i, formulaEvaluator); string fieldName = GetCellString(sheet, 3, i, formulaEvaluator); string fieldType = GetCellString(sheet, 4, i, formulaEvaluator); cellInfos[i] = new ExcelCellInfo() { Name = fieldName, Type = fieldType, Description = fieldDesc }; } for (int rowIndex = 5; rowIndex <= sheet.LastRowNum; ++rowIndex) { string id = GetCellString(sheet, rowIndex, 2, formulaEvaluator); if (string.IsNullOrEmpty(id)) { continue; } StringBuilder sb = new StringBuilder(); sb.Append("{"); IRow row = sheet.GetRow(rowIndex); for (int columnIndex = 2; columnIndex < cellCount; columnIndex++) { string description = cellInfos[columnIndex].Description.ToLower(); if (description.StartsWith("#")) { continue; } //s开头表示这个字段是服务端专用 if (description.StartsWith("s") && IsClient) { continue; } //c开头表示这个字段是客户端专用 if (description.StartsWith("c") && !IsClient) { continue; } string fieldValue = GetCellString(row, columnIndex, formulaEvaluator); if (columnIndex > 2) { sb.Append(","); } string fieldName = cellInfos[columnIndex].Name; if (fieldName == "Id" || fieldName == "_id") { if (IsClient) { fieldName = "Id"; } else { fieldName = "_id"; } } string fieldType = cellInfos[columnIndex].Type; sb.Append($"\"{fieldName}\":{Convert(fieldType, fieldValue)}"); } sb.Append("}"); sw.WriteLine(sb.ToString()); } }
public static string Export(string fileName) { XSSFWorkbook xssfWorkbook; IFormulaEvaluator formulaEvaluator; using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { xssfWorkbook = new XSSFWorkbook(file); formulaEvaluator = xssfWorkbook.GetCreationHelper().CreateFormulaEvaluator(); Dictionary<string, IFormulaEvaluator> evaluators = new Dictionary<string, IFormulaEvaluator> { {Path.GetFileName(fileName),formulaEvaluator } }; foreach (string linkedFileName in xssfWorkbook.ExternalLinksTable.Select(p => $"{ExcelPath}/{p.LinkedFileName}")) { XSSFWorkbook linkedWorkBook; using (FileStream linkedFile = new FileStream(linkedFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { linkedWorkBook = new XSSFWorkbook(linkedFile); } evaluators.Add(Path.GetFileName(linkedFileName), linkedWorkBook.GetCreationHelper().CreateFormulaEvaluator()); } formulaEvaluator.SetupReferencedWorkbooks(evaluators); } ISheet sheet = xssfWorkbook.GetSheetAt(0); int cellCount = sheet.GetRow(3).LastCellNum; ExcelCellInfo[] cellInfos = new ExcelCellInfo[cellCount]; for (int i = 2; i < cellCount; ++i) { string fieldDesc = GetCellString(sheet, 2, i, formulaEvaluator); string fieldName = GetCellString(sheet, 3, i, formulaEvaluator); string fieldType = GetCellString(sheet, 4, i, formulaEvaluator); cellInfos[i] = new ExcelCellInfo() { Name = fieldName, Type = fieldType, Description = fieldDesc }; } StringBuilder sb = new StringBuilder(); for (int rowIndex = 5; rowIndex <= sheet.LastRowNum; ++rowIndex) { string id = GetCellString(sheet, rowIndex, 2, formulaEvaluator); if (string.IsNullOrEmpty(id)) { continue; } sb.Append("{"); IRow row = sheet.GetRow(rowIndex); for (int columnIndex = 2; columnIndex < cellCount; columnIndex++) { string description = cellInfos[columnIndex].Description.ToLower(); if (description.StartsWith("#")) { continue; } //s开头表示这个字段是服务端专用 if (description.StartsWith("s") && IsClient) { continue; } //c开头表示这个字段是客户端专用 if (description.StartsWith("c") && !IsClient) { continue; } string fieldValue = GetCellString(row, columnIndex, formulaEvaluator); if (columnIndex > 2) { sb.Append(","); } string fieldName = cellInfos[columnIndex].Name; if (fieldName == "Id" || fieldName == "_id") { if (IsClient) { fieldName = "Id"; } else { fieldName = "_id"; } } string fieldType = cellInfos[columnIndex].Type; sb.Append($"\"{fieldName}\":{Convert(fieldType, fieldValue)}"); } sb.Append("}"); if (rowIndex < sheet.LastRowNum) { sb.Append("\n"); } } return sb.ToString(); }