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();
}