public void LoadFrom(Stream stream, ExcelTypeEnum type) { IWorkbook workbook; switch (type) { case ExcelTypeEnum.XLS: workbook = new HSSFWorkbook(stream); break; case ExcelTypeEnum.XLSX: //this is as it should be according to a standard workbook = new XSSFWorkbook(stream); break; default: throw new ArgumentOutOfRangeException("type"); } //refresh cache. This might change in between two loadings _multiRowIndicesCache = new Dictionary <string, int[]>(); _isMultiRowMappingCache = new Dictionary <ClassMapping, bool>(); _referenceContexts = new Dictionary <ClassMapping, ReferenceContext>(); _forwardReferences.Clear(); _globalEntities.Clear(); //create spreadsheet representaion LoadFromWoorkbook(workbook); }
/// <summary> /// 构造 /// </summary> public ExcelExportOptions() { SheetName = "Sheet1"; HeaderRowIndex = 1; DataRowStartIndex = 2; ExcelType = ExcelTypeEnum.Xlsx; }
public ExcelManager(ExcelTypeEnum excelType = ExcelTypeEnum.XLSX) { _wb = CreateWorkbook(excelType); _commonCellStyle = CreateCommonCellStyle(_wb); _dateStyle = GetDateStyle(); _doubleStyle = GetDouleStyle(); }
public virtual IWorkbook Create(ExcelTypeEnum excelTypeEnum) { if (excelTypeEnum == ExcelTypeEnum.XLS) { return(new HSSFWorkbook()); } return(new XSSFWorkbook()); }
public static string FullResourceName(string templateType, ExcelTypeEnum excelFormatType) { var extension = excelFormatType == ExcelTypeEnum.XLS ? "xls" : "xlsx"; return(string.Format("Xbim.CobieLiteUk.Templates.{0}.{1}", templateType, extension.ToLowerInvariant())); }
public void ExportToTable(Stream file, ExcelTypeEnum typeEnum, out string report, ModelMapping mapping = null) { mapping = mapping ?? ModelMapping.Load(Properties.Resources.COBieUK2012); var storage = GetTableStore(this, mapping); storage.Store(file, typeEnum); report = storage.Log.ToString(); }
public void ExportToTable(Stream file, ExcelTypeEnum typeEnum, out string report, ModelMapping mapping = null, Stream template = null) { mapping = mapping ?? GetMapping(); var storage = GetTableStore(this, mapping); storage.Store(file, typeEnum, template); report = storage.Log.ToString(); }
/// <summary> /// 创建工作簿 /// </summary> /// <param name="excelType"></param> /// <returns></returns> public static IWorkbook CreateWorkbook(ExcelTypeEnum excelType = ExcelTypeEnum.XLSX) { IWorkbook wb = null; if (excelType == ExcelTypeEnum.XLS) { wb = new HSSFWorkbook(); } else { wb = new XSSFWorkbook(); } return(wb); }
public static CobieModel ImportFromTable(Stream file, ExcelTypeEnum typeEnum, out string report, ModelMapping mapping = null) { var loaded = new CobieModel(); mapping = mapping ?? ModelMapping.Load(Properties.Resources.COBieUK2012); var storage = GetTableStore(loaded, mapping); using (var txn = loaded.BeginTransaction("Loading XLSX")) { storage.LoadFrom(file, typeEnum); txn.Commit(); } report = storage.Log.ToString(); return(loaded); }
/// <summary> /// Generate a Excel File /// </summary> /// <param name="parameters">Params</param> /// <param name="fileName">Root file name</param> /// <param name="facility">Facility</param> /// <returns>file name</returns> private string CreateExcelFile(Params parameters, string fileName, Facility facility) { ExcelTypeEnum excelType = (ExcelTypeEnum)Enum.Parse(typeof(ExcelTypeEnum), parameters.ExportType.ToString(), true); var excelName = Path.ChangeExtension(fileName, excelType == ExcelTypeEnum.XLS ? ".xls" : ".xlsx"); Worker.ReportProgress(0, string.Format("Creating file: {0}", excelName)); string msg; using (var file = File.Create(excelName)) { facility.ReportProgress.Progress = Worker.ReportProgress; facility.WriteCobie(file, excelType, out msg, parameters.Filter, parameters.TemplateFile, true); } //_worker.ReportProgress(0, msg); //removed for now, kill app for some reason return(excelName); }
public Stream Store(Stream stream, ExcelTypeEnum type, Stream template = null, bool recalculate = false) { Log = new StringWriter(); IWorkbook workbook; switch (type) { case ExcelTypeEnum.XLS: workbook = template != null ? new HSSFWorkbook(template) : new HSSFWorkbook(); break; case ExcelTypeEnum.XLSX: //this is as it should be according to a standard workbook = template != null ? new XSSFWorkbook(template) : new XSSFWorkbook(); break; default: throw new ArgumentOutOfRangeException("type"); } //create spreadsheet representaion Store(workbook); if (!recalculate || template == null) { workbook.Write(stream); return(stream); } //refresh formulas switch (type) { case ExcelTypeEnum.XLS: HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook); break; case ExcelTypeEnum.XLSX: XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook); break; default: throw new ArgumentOutOfRangeException("type"); } //write to output stream workbook.Write(stream); return(stream); }
public static DataSet ExcelToDS(string filePath, ExcelTypeEnum excelType) { DataSet ds = new DataSet(); if (filePath.Length <= 0) { return(ds); } var strConn = ""; var tableName = ""; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串) //"IMEX=1 "如果列中的数据类型不一致,使用"IMEX=1"可必免数据类型冲突。 if (excelType == ExcelTypeEnum.excelXls) { strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; } else if (excelType == ExcelTypeEnum.excelXlsx) { strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + filePath + "; Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; } //strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Server.MapPath("ExcelFiles/Mydata2007.xlsx") + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); var strExcel = ""; OleDbDataAdapter myCommand = null; System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); for (int i = 0; i < schemaTable.Rows.Count; i++) { tableName = schemaTable.Rows[i][2].ToString().Trim(); if (!tableName.Contains("FilterDatabase") && tableName.Substring(tableName.Length - 1, 1) != "_") { ds.Tables.Add(tableName); strExcel = string.Format("select * from [{0}]", tableName); myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, tableName); } } conn.Close(); return(ds); }
public void WriteCobie(Stream stream, ExcelTypeEnum type, out string message, string version = "UK2012", bool useTemplate = true) { Stream templateStream = null; if (useTemplate) { var templateName = version + (type == ExcelTypeEnum.XLS ? ".xls" : ".xlsx"); templateStream = GetType() .Assembly.GetManifestResourceStream(String.Format("{0}.Templates.{1}", GetType().Namespace, templateName)); } IWorkbook workbook; switch (type) { case ExcelTypeEnum.XLS: workbook = templateStream == null ? new HSSFWorkbook() : new HSSFWorkbook(templateStream); break; case ExcelTypeEnum.XLSX: //this is as it should be according to a standard workbook = templateStream == null ? new XSSFWorkbook() : new XSSFWorkbook(templateStream); // This saves around 10 seconds by only refreshing formulas on the first page. if (templateStream != null) { workbook.GetSheetAt(0).ForceFormulaRecalculation = true; } break; default: throw new ArgumentOutOfRangeException("type"); } var log = new StringWriter(); var watch = new Stopwatch(); watch.Start(); WriteToCobie(workbook, log, null, new Dictionary <Type, int>(), new List <string>(), new Dictionary <string, int>(), version); watch.Stop(); Debug.WriteLine("Creating NPOI model: {0}ms", watch.ElapsedMilliseconds); //refresh formulas -- this has quite a significant overhead! switch (type) { case ExcelTypeEnum.XLS: HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook); break; case ExcelTypeEnum.XLSX: if (templateStream == null) { XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook); } break; default: throw new ArgumentOutOfRangeException("type"); } message = log.ToString(); workbook.Write(stream); }
public static Facility ReadCobie(Stream stream, ExcelTypeEnum type, out string message, string version = "UK2012") { //use NPOI to open and access spreadsheet data IWorkbook workbook; switch (type) { case ExcelTypeEnum.XLS: workbook = new HSSFWorkbook(stream); break; case ExcelTypeEnum.XLSX: workbook = new XSSFWorkbook(stream); break; default: throw new ArgumentOutOfRangeException("type"); } Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); string msg; var flatList = ReadAllCobieObjects(workbook, out msg, version); message = msg ?? ""; Debug.WriteLine("Reading all COBie objects: " + stopWatch.ElapsedMilliseconds); stopWatch.Reset(); stopWatch.Start(); var facilities = flatList.OfType <Facility>().ToArray(); if (facilities.Count() > 1) { message += String.Format( "There are {0} facilities in the data. Only first facility {1} will be used. This is an invalid COBie spreadsheet. \n", facilities.Length, facilities[0].Name); } var facility = facilities.FirstOrDefault(); if (facility == null) { message += "There is no facility in the data. Default facility will be created as a root object. This is an invalid COBie spreadsheet. \n"; facility = new Facility { Name = "Default facility" }; flatList.Add(facility); } //create structure hierarchy var parallelMessage = new[] { "" }; var newTypes = new List <AssetType>(); var typeDictionary = CreateTypeDictionary(flatList); Parallel.ForEach(flatList.ToArray(), o => { string addToParentMsg; o.AddToParent(typeDictionary, facility, newTypes, out addToParentMsg, version); lock (parallelMessage) { parallelMessage[0] += addToParentMsg; } }); message += parallelMessage[0]; //foreach (var cobieObject in flatList.ToArray()) //{ // cobieObject.AddToParent(typeDictionary, facility, newTypes, out msg, version); // message += msg; //} flatList.AddRange(newTypes); foreach (var cobieObject in flatList) { cobieObject.AfterCobieRead(); } Debug.WriteLine("Building COBieLite hierarchy: " + stopWatch.ElapsedMilliseconds); stopWatch.Reset(); stopWatch.Start(); //load metadate from the first sheet facility.Metadata = new Metadata(); var log = new StringWriter(); facility.Metadata.LoadFromCobie(workbook, log, version); message += log.ToString(); //set facility for all objects facility.SetFacility(facility); return(facility); }