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