private static StringValue createNewSheet(WorkbookPart workbookPart, string sheetName) { Sheets sheets = workbookPart.Workbook.Descendants <Sheets>().FirstOrDefault(); // will Sheets ever be null ever on an existing workbook? No. uint currentMaxSheetId; if (sheets.Count() == 0) { currentMaxSheetId = 0; } else { currentMaxSheetId = (from t in sheets.Elements() select((uint)((Sheet)t).SheetId)).Max(); } // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); StringValue relationshipId = workbookPart.GetIdOfPart(worksheetPart); // Append a new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = (UInt32Value)currentMaxSheetId + 1, Name = sheetName }; sheets.Append(sheet); return(relationshipId); }
/// <summary> /// Adds a new empty sheet in the document /// </summary> public static SheetData AddSheet(this SpreadsheetDocument document, string sheetName) { WorksheetPart worksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); var data = new SheetData(); worksheetPart.Worksheet = new Worksheet(data); worksheetPart.Worksheet.Save(); var workbookPart = document.WorkbookPart; // get or create colelction of sheets Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>() ?? workbookPart.Workbook.AppendChild(new Sheets()); // create a new one Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = (UInt32)(sheets.Count()) + 1, Name = sheetName }; // add to collection and save sheets.Append(sheet); document.WorkbookPart.Workbook.Save(); return(data); }
/// <summary> /// 将DataTable转化为XML输出 /// </summary> /// <param name="dataSet">dataSet</param> /// <param name="fileName">文件名称</param> //public void DataTableToXML(DataTable dataTable, string fileName) public void DataSetToXML(DataSet dataSet, string filePath) { Dictionary <String, List <OpenXmlElement> > sets = ToSheets(dataSet); using (SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookpart = package.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); Sheets sheets = workbookpart.Workbook.AppendChild(new Sheets()); foreach (KeyValuePair <String, List <OpenXmlElement> > set in sets) { WorksheetPart worksheetpart = workbookpart.AddNewPart <WorksheetPart>(); worksheetpart.Worksheet = new Worksheet(new SheetData(set.Value)); worksheetpart.Worksheet.Save(); Sheet sheet = new Sheet() { Id = workbookpart.GetIdOfPart(worksheetpart), SheetId = (uint)(sheets.Count() + 1), Name = set.Key }; sheets.AppendChild(sheet); } workbookpart.Workbook.Save(); } }
private Worksheet GetWorksheet(WorkbookPart workbookPart, Sheets sheets, int index) { if (index > 0 && index <= sheets.Count()) { return(GetWorksheet(workbookPart, sheets.ChildElements[index - 1] as Sheet)); } return(null); }
private void CalculateSum() { decimal sum = 0; for (int i = 0; i < Sheets.Count(); i++) { sum += Sheets[i].Amount; } Sum = Money.FromDeciaml(sum * _rate); }
private void WriteWorksheet(WorkbookPart workbookPart, Sheets sheets, DotForm form) { //Add data to first sheet WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); Worksheet worksheet = new Worksheet(); worksheetPart.Worksheet = worksheet; Columns columns = new Columns(); columns.Append(new Column { Min = 1, Max = 2, Width = 4, CustomWidth = true }); columns.Append(new Column { Min = 3, Max = 7, Width = 12, CustomWidth = true }); columns.Append(new Column { Min = 8, Max = 8, Width = 40, CustomWidth = true }); columns.Append(new Column { Min = 9, Max = 9, Width = 20, CustomWidth = true }); columns.Append(new Column { Min = 10, Max = 11, Width = 4, CustomWidth = true }); var sheetData = new SheetData(); var dataValidations = new DataValidations(); worksheet.Append(columns); worksheet.Append(sheetData); var mergeCells = CreateOrGetMergeCells(worksheet); worksheet.Append(dataValidations); Sheet sheet = new Sheet { Name = form.EventTreeName, SheetId = (uint)sheets.Count() + 1, Id = workbookPart.GetIdOfPart(worksheetPart), }; sheets.Append(sheet); WriteHeader(sheetData, mergeCells); WriteEventHeader(form, sheetData, mergeCells); WriteExpertInformation(form, sheetData); WriteElicitationCodeInformation(sheetData, mergeCells); AddImage(form.GetFileStream, worksheetPart); var rowNumber = WriteNodes(form, sheetData, dataValidations); WriteSheetBottom(sheetData, rowNumber); worksheetPart.Worksheet.Save(); }
public ITabularPage AddPageAfter(int workSheetIndex) { Sheets sheets = _workbook.GetFirstChild<Sheets>(); Sheet worksheet = SetupWorksheet(sheets); if (sheets.Count() <= workSheetIndex) sheets.Append(worksheet); else sheets.InsertAt<Sheet>(worksheet, workSheetIndex + 1); _workbook.Save(); return GetPage(worksheet.Name); }
WorksheetPart AddEmptySheet(WorkbookPart workbookPart, string sheetName, Sheets sheets) { WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); // craete new sheet ad append to file Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = sheets != null ? (UInt32)sheets.Count() + 1 : 1, Name = sheetName }; sheets.Append(sheet); workbookPart.Workbook.Save(); return(worksheetPart); }
internal WorksheetPart WorksheetPart_FromName(WorkbookPart workbookPart, string sheetName = "") { //if (sheetName == "") return workbookPart.WorksheetParts.Last(); Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>(); var total = sheets.Count(); if (total == 0) { return(null); // The specified worksheet does not exist. } IEnumerable <Sheet> sheetsName = sheets.Elements <Sheet>().Where(s => s.Name == sheetName); var sheet1 = sheetsName.FirstOrDefault(); if (sheet1 == null) { if (sheetName != "") { ($"Error! Worksheet with name '{sheetName}' was not found!").zException_Show(); } // The Sheet name was not found; return the first visible sheet foreach (Sheet sheet in sheets.Elements <Sheet>()) { if (sheet.State == null || (sheet.State != null && sheet.State.HasValue && sheet.State.Value == SheetStateValues.Visible)) { sheet1 = sheet; break; } } } // if (sheet1 == null) return null; // <==================[ Unit test required for this condition string relationshipId = sheet1.Id.Value; WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(relationshipId); return(worksheetPart); }
private void WriteWorksheets(WorkbookPart workbookPart, SaveContext context) { XlSharedStringsTable stringsTable = new XlSharedStringsTable(workbookPart, context); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); foreach (XlWorksheet sheet in Worksheets) { WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(context.RelIdGenerator.GetNext(RelType.Workbook)); SheetData sheetData = new SheetData(); Worksheet worksheet = new Worksheet(sheetData); worksheetPart.Worksheet = worksheet; if ( !worksheet.NamespaceDeclarations.Contains(new KeyValuePair <String, String>("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) { worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); } var sheetProperties = new SheetProperties { TabColor = new TabColor() }; string colorString = sheet.TabColor.HtmlColor; sheetProperties.TabColor.Rgb = colorString; worksheet.SheetProperties = sheetProperties; AddTableData(sheet, sheetData, stringsTable); worksheet.Save(); Sheet s = new Sheet { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = (uint)(sheets.Count() + 1), Name = sheet.Name }; sheets.AppendChild(s); workbookPart.Workbook.Save(); } }
public void AddDataToSheet(string sheetName, IEnumerable <string> columnNames, IEnumerable <Array> data) { SheetData sheetData = new SheetData(); Worksheet worksheet = new Worksheet(sheetData); WorksheetPart worksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = worksheet; Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>(); // Append the new worksheet and associate it with the workbook. uint sheetId = (uint)sheets.Count() + 1; sheetName = String.IsNullOrEmpty(sheetName) ? "Sheet " + sheetId : sheetName; Sheet sheet = new Sheet() { Id = document.WorkbookPart .GetIdOfPart(worksheetPart), SheetId = sheetId, Name = sheetName }; Columns columns = new Columns(); Row headerRow = new Row { RowIndex = 1 }; List <string> keys = new List <string>(); for (int i = 0; i < columnNames.Count(); i++) { var columnName = columnNames.ElementAt(i); Column column = new Column() { Min = (uint)i, Max = (uint)i, CustomWidth = false }; Cell cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(columnName); /*** set the columnWith does not work **/ /* * double columnWith = 8.43; * * columnWith = Math.Max(columnWith, 0.94 * (columnName.Length+1)); * column.Width = columnWith; */ headerRow.Append(cell); columns.Append(column); } sheetData.Append(headerRow); for (int counter = 0; counter < data.Count(); counter++) { var rowData = data.ElementAt(counter); Row row = new Row { RowIndex = (uint)counter + 2 }; for (int i = 0; i < rowData.Length; i++) { var value = rowData.GetValue(i); Cell cell = new Cell(); cell.DataType = GetCellDataType(value); string cellValue = (value == null ? "" : value.ToString()); cell.CellValue = new CellValue(cellValue); row.Append(cell); } sheetData.Append(row); } sheets.Append(sheet); }
public void AddDataToSheet <T>(string sheetName, Dictionary <string, string> fieldMapping, IEnumerable <T> data) { SheetData sheetData = new SheetData(); Worksheet worksheet = new Worksheet(sheetData); WorksheetPart worksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = worksheet; Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>(); // Append the new worksheet and associate it with the workbook. uint sheetId = (uint)sheets.Count() + 1; sheetName = String.IsNullOrEmpty(sheetName) ? "Sheet " + sheetId : sheetName; if (sheetName.Length > 30) { sheetName = sheetName.Substring(0, 30); } Sheet sheet = new Sheet() { Id = document.WorkbookPart .GetIdOfPart(worksheetPart), SheetId = sheetId, Name = sheetName }; Type type = null; IEnumerable <PropertyInfo> propertyInfos = new List <PropertyInfo>(); if (data.Count() > 0) { var obj = data.First(); type = obj.GetType(); propertyInfos = this.getPropertyInfo(type); // if field mapping is null // then we want to export all properties if (fieldMapping == null) { fieldMapping = this.getPropertyFieldMappings(type); } } Columns columns = new Columns(); Row headerRow = new Row { RowIndex = 1 }; List <string> keys = new List <string>(); if (fieldMapping != null) { keys = fieldMapping.Keys.ToList(); for (uint i = 0; i < keys.Count; i++) { Column column = new Column() { Min = i, Max = i, CustomWidth = false }; columns.Append(column); Cell cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(fieldMapping[keys[(int)i]]); headerRow.Append(cell); } } //worksheet.InsertAt(columns, 0); sheetData.Append(headerRow); for (uint counter = 0; counter < data.Count(); counter++) { var obj = data.ElementAt((int)counter); Row row = new Row { RowIndex = counter + 2 }; for (int i = 0; i < keys.Count; i++) { string key = keys[i]; Cell cell = new Cell(); try { var value = getPropertyValue(obj, key.Split('.')); CellValues cellDataType = GetCellDataType(value); cell.DataType = cellDataType; if (cellDataType == CellValues.Date) { var d = (DateTime)value; cell.DataType = CellValues.Number; if (d.TimeOfDay.Seconds == 0) { cell.StyleIndex = UInt32Value.FromUInt32((int)CustomCellFormats.ISODateFormat); } else { cell.StyleIndex = UInt32Value.FromUInt32((int)CustomCellFormats.ISODateTimeFormat); } cell.CellValue = new CellValue(Convert.ToString(d.ToOADate())); } else if (cellDataType == CellValues.Boolean) { cell.DataType = CellValues.Number; cell.CellValue = new CellValue(Convert.ToString(value)); } else { cell.CellValue = new CellValue(Convert.ToString(value)); } } catch (PropertyNotFoundException ex) { cell.DataType = CellValues.String; cell.CellValue = new CellValue(ex.Message); } row.Append(cell); } sheetData.Append(row); } sheets.Append(sheet); }
/// <summary> /// Copy sheet to another document /// </summary> /// <param name="sourceDoc">Source document</param> /// <param name="srcSheetName">Name of source sheet</param> /// <param name="targetDoc">Spreadsheet document to copied</param> /// <param name="targetIndex">Index of copied sheet in target document</param> public static void CopyWorksheet(SpreadsheetDocument sourceDoc, String srcSheetName, SpreadsheetDocument targetDoc, uint targetIndex) { // Locate the source sheet if (sourceDoc.WorkbookPart == null) { throw new InvalidOperationException("WorkbookPart is not exist in sourceDoc!"); } if (sourceDoc.WorkbookPart.Workbook.Sheets == null) { throw new InvalidOperationException("No sheets exist in sourceDoc!"); } var srcSheet = sourceDoc.WorkbookPart.Workbook.Sheets.Descendants <Sheet>() .FirstOrDefault(a => a.Name == srcSheetName); if (srcSheet == null) { throw new InvalidOperationException(String.Format("No sheet found with name {0}!", srcSheetName)); } var srcSheetPart = sourceDoc.WorkbookPart.GetPartById(srcSheet.Id) as WorksheetPart; if (srcSheetPart == null) { throw new InvalidOperationException(String.Format("Cannot find worksheet part with Id {0}!", srcSheet.Id)); } var srcWorkSheet = srcSheetPart.Worksheet; if (srcWorkSheet == null) { throw new InvalidOperationException("Worksheet not exist in source worksheet part!"); } // Locate the position of target sheet WorkbookPart tgtWbPart = targetDoc.WorkbookPart ?? targetDoc.AddWorkbookPart(); Sheets tgtSheets = tgtWbPart.Workbook.Sheets ?? tgtWbPart.Workbook.AppendChild <Sheets>(new Sheets()); if (targetIndex > tgtSheets.Count()) { targetIndex = (uint)tgtSheets.Count(); } // Create a new worksheet and clone data from original worksheet var newSheetPart = tgtWbPart.AddNewPart <WorksheetPart>(); newSheetPart.Worksheet = new Worksheet(); //srcWorkSheet.Clone() as Worksheet; // Create a unique ID for the new worksheet. uint sheetId = 1; if (tgtSheets.Elements <Sheet>().Any()) { sheetId = tgtSheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } // Add cloned worksheet to target workbook var newSheet = new Sheet() { Id = tgtWbPart.GetIdOfPart(newSheetPart), SheetId = sheetId, Name = srcSheet.Name }; tgtSheets.InsertAt(newSheet, (int)targetIndex); // Import data from source sheet to target sheet ImportWorksheet(sourceDoc, srcWorkSheet, targetDoc, newSheetPart.Worksheet); // Import all necessary resources into target document that referenced by cloned sheet //ImportResources(sourceDoc, newSheetPart.Worksheet, targetDoc); // Save it tgtWbPart.Workbook.Save(); }