private static void InitStyles(WorkbookNode workbook) { StyleNode styleDateTime = new StyleNode(); styleDateTime.ID = "NormalDateTime"; styleDateTime.NumberFormat.Format = "yyyy-mm-dd hh:mm:ss"; workbook.Styles.Add(styleDateTime); }
private static Dictionary<string, CellLocation> BuildNameColumnDictionary(WorkbookNode workbook) { Dictionary<string, CellLocation> result = new Dictionary<string, CellLocation>(); foreach (NamedRangeNode nr in workbook.Names) { result.Add(nr.Name, nr.ParseReferTo()); } return result; }
public static WorkbookNode ExportToSpreadSheet(this DataView view, string worksheetName, DataViewExportOptions options) { ExceptionHelper.FalseThrow <ArgumentNullException>(view != null, "view"); ExceptionHelper.CheckStringIsNullOrEmpty(worksheetName, "worksheetName"); ExceptionHelper.FalseThrow <ArgumentNullException>(options != null, "options"); WorkbookNode workbook = new WorkbookNode(); InitStyles(workbook); workbook.Worksheets.Add(BuildWorkSheet(worksheetName, view, options)); return(workbook); }
public static WorkbookNode ExportToSpreadSheet(this DataView view, string worksheetName, DataViewExportOptions options) { ExceptionHelper.FalseThrow<ArgumentNullException>(view != null, "view"); ExceptionHelper.CheckStringIsNullOrEmpty(worksheetName, "worksheetName"); ExceptionHelper.FalseThrow<ArgumentNullException>(options != null, "options"); WorkbookNode workbook = new WorkbookNode(); InitStyles(workbook); workbook.Worksheets.Add(BuildWorkSheet(worksheetName, view, options)); return workbook; }
public static void FillIntoSpreadSheet(this DataView view, WorkbookNode workbook, string worksheetName, DataViewExportOptions options) { ExceptionHelper.FalseThrow <ArgumentNullException>(view != null, "view"); ExceptionHelper.FalseThrow <ArgumentNullException>(workbook != null, "workbook"); ExceptionHelper.CheckStringIsNullOrEmpty(worksheetName, "worksheetName"); ExceptionHelper.FalseThrow <ArgumentNullException>(options != null, "options"); InitStyles(workbook); ExceptionHelper.FalseThrow(workbook.Worksheets.Contains(worksheetName), "不能找到名称为{0}的Worksheet", worksheetName); WorksheetNode worksheet = workbook.Worksheets[worksheetName]; FillIntoWorksheet(worksheet, view, options); }
public static void FillIntoSpreadSheet(this DataView view, WorkbookNode workbook, string worksheetName, DataViewExportOptions options) { ExceptionHelper.FalseThrow<ArgumentNullException>(view != null, "view"); ExceptionHelper.FalseThrow<ArgumentNullException>(workbook != null, "workbook"); ExceptionHelper.CheckStringIsNullOrEmpty(worksheetName, "worksheetName"); ExceptionHelper.FalseThrow<ArgumentNullException>(options != null, "options"); InitStyles(workbook); ExceptionHelper.FalseThrow(workbook.Worksheets.Contains(worksheetName), "不能找到名称为{0}的Worksheet", worksheetName); WorksheetNode worksheet = workbook.Worksheets[worksheetName]; FillIntoWorksheet(worksheet, view, options); }
public static void FillIntoSpreadSheet(this DataView view, WorkbookNode workbook, string worksheetName) { DataViewExportOptions options = new DataViewExportOptions(); FillIntoSpreadSheet(view, workbook, worksheetName, options); }
/// <summary> /// 导入Excel Xml格式的文件 /// </summary> /// <param name="stream"></param> private void ImportFromXml(Stream stream) { WorkbookNode workbook = new WorkbookNode(); workbook.Load(stream); ExceptionHelper.FalseThrow(workbook.Worksheets.Contains("Matrix"), "The workbook must contains a 'Matrix' worksheet."); SOARole role = null; ServiceBrokerContext.Current.SaveContextStates(); try { if (this.AppCodeName.IsNotEmpty() && this.RoleCodeName.IsNotEmpty()) role = new SOARole(this.AppCodeName + ":" + this.RoleCodeName); else role = new SOARole(this.Definition) { ID = RoleID }; role.Rows.Clear(); NamedLocationCollection fieldLocations = workbook.Names.ToLocations(); TableNode table = workbook.Worksheets["Matrix"].Table; int baseRowIndex = GetStartRow(fieldLocations); RowNode titleRow = table.GetRowByIndex(baseRowIndex); int currentRowIndex = table.Rows.IndexOf(titleRow) + 1; if (table.Rows.Count > currentRowIndex) { UploadProgressStatus status = new UploadProgressStatus(); status.CurrentStep = 1; status.MinStep = 0; status.MaxStep = table.Rows.Count - currentRowIndex; int currentVirtualRow = baseRowIndex; for (int i = status.MinStep; i < status.MaxStep; i++) { RowNode row = table.Rows[currentRowIndex]; if (row.Index > 0) currentVirtualRow = row.Index; else currentVirtualRow++; GenerateMatrixRow(role, row, fieldLocations, i); status.CurrentStep = i; status.Response(); currentRowIndex++; } status.CurrentStep = status.MaxStep; status.Response(); } //插入记录 SOARolePropertiesAdapter.Instance.Update(role); } finally { ServiceBrokerContext.Current.RestoreSavedStates(); } }
//导出带数据的Excel private static void FillMatrixRowsToWorksheet(WorkbookNode workbook, SOARolePropertyRowCollection rows) { NamedLocationCollection locations = workbook.Names.ToLocations(); locations.SortByColumn(); WorksheetNode worksheet = workbook.Worksheets[GetWorksheet(locations)]; int startRowIndex = GetStartRow(locations); int currentRowIndex = -1; foreach (SOARolePropertyRow matrixRow in rows) { RowNode row = new RowNode(); if (currentRowIndex == -1) { currentRowIndex = startRowIndex + 1; row.Index = currentRowIndex; } for (int i = 0; i < locations.Count; i++) { CellNode cell = new CellNode(); CellLocation location = locations[i]; SOARolePropertyValue propertyValue = matrixRow.Values.FindByColumnName(location.Name); string dataValue = null; if (propertyValue != null) dataValue = propertyValue.Value; else { switch (location.Name.ToLower()) { case "operatortype": dataValue = matrixRow.OperatorType.ToString(); break; case "operator": dataValue = matrixRow.Operator; break; } } if (dataValue != null) cell.Data.Value = dataValue; else cell.Data.Value = string.Empty; row.Cells.Add(cell); } worksheet.Table.Rows.Add(row); } }
/// <summary> /// 填充Excel Xml的标题列 /// </summary> /// <param name="workbook"></param> /// <param name="definition"></param> /// <param name="propertySheetName"></param> private static void FillWorkSheetTitle(WorkbookNode workbook, SOARolePropertyDefinitionCollection definition, string propertySheetName) { WorksheetNode worksheet = GetWorksheetFromWorkbook(workbook, propertySheetName); worksheet.Names.Clear(); workbook.Names.Clear(); worksheet.Table.Rows[1].Cells.Clear(); int row = 3; int column = 1; RowNode titleRow = null; if (worksheet.Table.Rows.Count > 0) titleRow = worksheet.Table.Rows[1]; else { titleRow = new RowNode(); worksheet.Table.Rows.Add(titleRow); } foreach (SOARolePropertyDefinition dd in definition) { NamedRangeNode range = new NamedRangeNode(); range.Name = dd.Name; range.RefersTo = string.Format("={0}!R{1}C{2}", worksheet.Name, row, column); workbook.Names.Add(range); CellNode cell = new CellNode(); cell.Data.Value = dd.Description.IsNotEmpty() ? dd.Description : dd.Name; cell.StyleID = "s17"; titleRow.Cells.Add(cell); column++; } }
public static void ExportToExcelXml(WorkbookNode workbook, SOARolePropertyDefinitionCollection definition, string propertySheetName) { workbook.NullCheck("workbook"); propertySheetName.NullCheck("propertySheetName"); FillWorkSheetTitle(workbook, definition, propertySheetName); }
/// <summary> /// 导出到Excel Xml中,非OpenXml /// </summary> /// <param name="coll"></param> /// <param name="propertySheetName"></param> /// <returns></returns> public static WorkbookNode ExportToExcelXml(SOARolePropertyDefinitionCollection definition, string propertySheetName) { WorkbookNode workbook = new WorkbookNode(); string filePath = HttpContext.Current.Server.MapPath("RolePropertyTemplate.xml"); workbook.Load(filePath); ExportToExcelXml(workbook, definition, propertySheetName); return workbook; }
private static WorksheetNode GetWorksheetFromWorkbook(WorkbookNode workbook, string propertySheetName) { WorksheetNode worksheet = null; if (workbook.Worksheets.Count > 0) { if (propertySheetName.IsNotEmpty()) { workbook.Worksheets.Contains(propertySheetName).FalseThrow("不能在模板中找到名称为{0}的工作簿", propertySheetName); worksheet = workbook.Worksheets[propertySheetName]; } else { worksheet = workbook.Worksheets[0]; } } else { worksheet = new WorksheetNode(); workbook.Worksheets.Add(worksheet); } if (propertySheetName.IsNotEmpty()) worksheet.Name = propertySheetName; else worksheet.Name = "Matrix"; return worksheet; }
protected void uploadProgress_DoUploadProgress(HttpPostedFile file, UploadProgressResult result) { string tag = uploadProgress.Tag; const int baseRowIndex = 3; ExceptionHelper.FalseThrow(Path.GetExtension(file.FileName).ToLower() == ".xml", "'{0}' must be a xml file.", file.FileName); WorkbookNode workbook = new WorkbookNode(); workbook.Load(file.InputStream); ExceptionHelper.FalseThrow(workbook.Worksheets.Contains("PC Tracker Form"), "The workbook must contains a 'PC Tracker Form' worksheet."); Dictionary<string, CellLocation> fieldLocations = BuildNameColumnDictionary(workbook); TableNode table = workbook.Worksheets["PC Tracker Form"].Table; StringBuilder strB = new StringBuilder(); if (table.Rows.Count > 3) { int currentRowIndex = baseRowIndex; UploadProgressStatus status = new UploadProgressStatus(); status.CurrentStep = 1; status.MinStep = 0; status.MaxStep = table.Rows.Count - currentRowIndex; int currentVirtualRow = baseRowIndex; for (int i = status.MinStep; i < status.MaxStep; i++) { currentRowIndex = baseRowIndex + i; RowNode row = table.Rows[currentRowIndex]; if (row.Index > 0) currentVirtualRow = row.Index; else currentVirtualRow++; if (strB.Length > 0) strB.Append("\n"); strB.AppendFormat("Processed={0}, Row={1}:", (i + 1), currentVirtualRow); foreach (KeyValuePair<string, CellLocation> kp in fieldLocations) { CellNode cell = row.GetCellByIndex(kp.Value.Column); strB.AppendFormat(";Name={0}, Value={1}", kp.Key, cell != null ? cell.Data.Value : string.Empty); } status.CurrentStep = i; status.Response(); int ms = 1000; if (Request.Form["longProgress"] == "true") ms = 2000; Thread.Sleep(ms); //假装等待 } status.CurrentStep = status.MaxStep; status.Response(); } result.DataChanged = true; result.CloseWindow = false; result.ProcessLog = strB.ToString(); }
protected void uploadProgress_DoUploadProgress(HttpPostedFile file, UploadProgressResult result) { ExceptionHelper.FalseThrow(Path.GetExtension(file.FileName).ToLower() == ".xml", "'{0}' must be a xml file.", file.FileName); WorkbookNode workbook = new WorkbookNode(); workbook.Load(file.InputStream); ExceptionHelper.FalseThrow(workbook.Worksheets.Contains("Matrix"), "The workbook must contains a 'Matrix' worksheet."); NamedLocationCollection fieldLocations = workbook.Names.ToLocations(); TableNode table = workbook.Worksheets["Matrix"].Table; StringBuilder strB = new StringBuilder(); int baseRowIndex = GetStartRow(fieldLocations); RowNode titleRow = table.GetRowByIndex(baseRowIndex); int currentRowIndex = table.Rows.IndexOf(titleRow) + 1; if (table.Rows.Count > currentRowIndex) { UploadProgressStatus status = new UploadProgressStatus(); status.CurrentStep = 1; status.MinStep = 0; status.MaxStep = table.Rows.Count - currentRowIndex; int currentVirtualRow = baseRowIndex; for (int i = status.MinStep; i < status.MaxStep; i++) { RowNode row = table.Rows[currentRowIndex]; if (row.Index > 0) currentVirtualRow = row.Index; else currentVirtualRow++; if (strB.Length > 0) strB.Append("\n"); strB.AppendFormat("Processed={0}, Row={1}:", (i + 1), currentVirtualRow); foreach (CellLocation location in fieldLocations) { CellNode cell = row.GetCellByIndex(location.Column); strB.AppendFormat(";Name={0}, Value={1}", location.Name, cell != null ? cell.Data.Value : string.Empty); } status.CurrentStep = i; status.Response(); currentRowIndex++; } status.CurrentStep = status.MaxStep; status.Response(); } result.DataChanged = true; result.CloseWindow = false; result.ProcessLog = strB.ToString(); }