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