public static String GetCellData(this ISheet sheet, Int32 x, Int32 y)
{
String value = null;
var cell = OfficeAssistor.GetCell(sheet, x, y);
if (cell != null)
{
value = cell.ToString();
}
return(value);
}
public static Double?GetCellDataDouble(this ISheet sheet, Int32 x, Int32 y)
{
Double?value = null;
ICell cell = OfficeAssistor.GetCell(sheet, x, y);
if (cell != null)
{
if (cell.CellType == CellType.String)
{
String valueStr = cell.ToString();
if (Double.TryParse(valueStr, out Double doubleValue))
{
value = doubleValue;
}
}
else
{
value = cell.NumericCellValue;
}
}
return(value);
}
public void Export(IList <IExportTargeter> exportTargeters, IList <SchemaInfoTuple> schemas, ExportConfig config)
{
List <SchemaInfoTuple> schemaInfos = new List <SchemaInfoTuple>(schemas);
Dictionary <String, SchemaInfoTuple> schemaTable = new Dictionary <string, SchemaInfoTuple>();
schemaInfos.ForEach(t =>
{
schemaTable.Add(t.ObjectSchema.Name, t);
});
Int32 total = schemaTable.Count;
Int32 current = 0;
ExcelExportConfig exportConfig = config as ExcelExportConfig;
//若有分组，首先写入分组中的结构信息
//在目标表合并一行用于写入分组名称
//后续写入属于此组的 TableSchema 信息
//若需要建立合并分组信息，则以组名创建 Sheet ，属于此组的所有 Schem 信息，写入到此 Sheet 中
//完成一个组的写入，并移除已写入的 Schema 信息
//完成所有分组的写入后，将剩下零散的 Schema 信息单独创建 Sheet 写入信息
var templatePath = Path.Combine(this.ExcelExportTemplateDirectory, exportConfig.ExcelTemplatePath);
//Start
var workbook = OfficeAssistor.OpenExcel(templatePath);
//新建目录 Sheet
EPoint catalogLocation = new EPoint(2, 2);
var catalogTemplateSheet = workbook.GetSheet(ExcelTemplateFormat.CatalogSheetTemplateName);
var tableTemplateSheet = workbook.GetSheet(ExcelTemplateFormat.TableSheetTemplateName);
var catalogSheet = workbook.CreateSheet(ExcelTemplateFormat.CatalogSheetName);
foreach (var pair in exportConfig.GroupInfos)
{
var schemaNames = pair.Value;
var groupName = pair.Key;
ISheet schemaSheet = null;
EPoint sheetLocation = new EPoint(2, 2);
if (exportConfig.IsMergeGroupToSheet)
{
schemaSheet = workbook.CreateSheet(groupName);
}
//复制目录头
catalogTemplateSheet.CopyRow(ExcelTemplateFormat.CSTHeadRowNum, catalogSheet, catalogLocation.X, catalogLocation.Y);
//向目录表中写入 组名
catalogSheet.SetCellValue(catalogLocation.X, 0, groupName);
catalogLocation.X += ExcelTemplateFormat.RowSpan;
//复制目录列
catalogTemplateSheet.CopyRow(ExcelTemplateFormat.CSTColumnHeaderRowNum, catalogSheet, catalogLocation.X, catalogLocation.Y);
catalogLocation.X += ExcelTemplateFormat.RowSpan;
//写入目录行
Int32 internalnum = 1;
foreach (String schemaName in schemaNames)
{
IObjectSchema objectSchema = schemaTable[schemaName].ObjectSchema;
catalogTemplateSheet.CopyRow(ExcelTemplateFormat.CSTRowTemlateNum, catalogSheet, catalogLocation.X, catalogLocation.Y);
var schemaInfo = schemaTable[schemaName];
this.RaiseExportProgressChanged(total, ++current, schemaInfo);
this.WriteCatalogRow(catalogSheet, objectSchema, internalnum++, catalogLocation.X, 0);
//写入对应表的列信息
if (!exportConfig.IsMergeGroupToSheet)
{
schemaSheet = workbook.CreateSheet(schemaInfo.ObjectSchema.Name);
}
this.WriteSchemaInfo(tableTemplateSheet, schemaSheet, sheetLocation, schemaInfo);
schemaTable.Remove(schemaName);
if (exportConfig.IsMergeGroupToSheet)
{
sheetLocation.X += ExcelTemplateFormat.RowSpan;
}
//目录表获取表名单元格，并添加链接
ICell cell = catalogSheet.GetCell(catalogLocation.X, 0 + ExcelTemplateFormat.CatalogNumColLength);
//TODO: 连接R1C1样式单元格 ，暂时只连接到 Sheet
IHyperlink link = OfficeAssistor.CreateHyperlink(schemaSheet /*, sheetLocation.X, sheetLocation.Y*/);
cell.Hyperlink = link;
catalogLocation.X += ExcelTemplateFormat.RowSpan;
Thread.Sleep(10);
}
catalogLocation.X += ExcelTemplateFormat.RowSpan;
}
if (schemaTable.Count > 0)
{
if (!exportConfig.EnableExclude)
{
catalogTemplateSheet.CopyRow(ExcelTemplateFormat.CSTHeadRowNum, catalogSheet, catalogLocation.X, catalogLocation.Y);
catalogSheet.SetCellValue(catalogLocation.X, 0, ExcelTemplateFormat.CatalogSheetName);
catalogLocation.X += ExcelTemplateFormat.RowSpan;
//复制目录列
catalogTemplateSheet.CopyRow(ExcelTemplateFormat.CSTColumnHeaderRowNum, catalogSheet, catalogLocation.X, catalogLocation.Y);
catalogLocation.X += ExcelTemplateFormat.RowSpan;
Int32 num = 1;
foreach (var pair in schemaTable)
{
String schemaName = pair.Key;
var schemaInfo = schemaTable[schemaName];
ISheet schemaSheet = null;
EPoint sheetLocation = new EPoint(2, 2);
schemaSheet = workbook.CreateSheet(schemaName);
IObjectSchema objectSchema = schemaTable[schemaName].ObjectSchema;
catalogTemplateSheet.CopyRow(ExcelTemplateFormat.CSTRowTemlateNum, catalogSheet, catalogLocation.X, catalogLocation.Y);
this.RaiseExportProgressChanged(total, ++current, schemaInfo);
this.WriteCatalogRow(catalogSheet, objectSchema, num++, catalogLocation.X, 0);
this.WriteSchemaInfo(tableTemplateSheet, schemaSheet, sheetLocation, schemaInfo);
//目录表获取表名单元格，并添加链接
ICell cell = catalogSheet.GetCell(catalogLocation.X, 0 + ExcelTemplateFormat.CatalogNumColLength);
//TODO: 连接R1C1样式单元格 ，暂时只连接到 Sheet
IHyperlink link = OfficeAssistor.CreateHyperlink(schemaSheet);
cell.Hyperlink = link;
catalogLocation.X += ExcelTemplateFormat.RowSpan;
Thread.Sleep(10);
}
}
else
{
this.RaiseExportProgressChanged(total - schemaTable.Count, current, null);
}
}
//删除模板 Sheet
workbook.RemoveSheetByName(ExcelTemplateFormat.CatalogSheetTemplateName);
workbook.RemoveSheetByName(ExcelTemplateFormat.TableSheetTemplateName);
this.SaveExcel(workbook, exportTargeters);
}
