/// <summary> /// Starts writing sheet element /// </summary> /// <param name="sheetName"> Name of the Sheet. Empty or null sheet name will result in default sheet name.</param> /// <param name="sheetProperties"></param> /// <param name="sheetViewProperties"></param> public void WriteStartSheet(string sheetName = default, OpenExcelSheetProperties sheetProperties = default, OpenExcelSheetViewProperties sheetViewProperties = default, OpenExcelSheetFormatProperties sheetFormatProperties = default) { _colCharacterLengths.Clear(); _rowIdx = _rowIdxReset; var wsPart = _xl.WorkbookPart.AddNewPart <WorksheetPart>(); uint newSheetId = _newSheetId; _workBookWriter.WriteElement(new Sheet() { Name = (string.IsNullOrWhiteSpace(sheetName)) ? $"Sheet{newSheetId}" : sheetName, SheetId = newSheetId, Id = _xl.WorkbookPart.GetIdOfPart(wsPart) }); _workSheetWriter = OpenXmlWriter.Create(wsPart); _workSheetWriter.WriteStartElement(new Worksheet()); WriteSheetProperties(sheetProperties); WriteSheetViewProperties(sheetViewProperties); WriteSheetFormatProperties(sheetFormatProperties); _workSheetWriter.WriteStartElement(new SheetData()); }
ISheetBuilder ISheetBuilder.InsertSheetAs(string sheetName, OpenExcelSheetProperties sheetProperties, OpenExcelSheetViewProperties sheetViewProperties, OpenExcelSheetFormatProperties sheetFormatProperties) { _api.WriteEndSheet(); (this as IExcelBuilder).InsertSheetAs(sheetName, sheetProperties, sheetViewProperties, sheetFormatProperties); return(this); }
private void WriteSheetProperties(OpenExcelSheetProperties sheetProperties) { if (sheetProperties == null) { return; } _workSheetWriter.WriteStartElement(new SheetProperties()); { if (sheetProperties.OutlineProperties != null) { _workSheetWriter.WriteElement(new OutlineProperties { SummaryBelow = sheetProperties.OutlineProperties.SummaryBelow, SummaryRight = sheetProperties.OutlineProperties.SummaryRight }); } } _workSheetWriter.WriteEndElement(); }
public static void Run() { var listPersons = new List <Person>(); for (int i = 0; i <= 100000; i++) { listPersons.Add(new Person { Age = 45, DateOfBirth = DateTime.Now.AddYears(-25), Name = "Sam Smith", Income = 55600.28, Children = new List <Child> { new Child { Name = "Tania", Age = 5, IsAdopted = false, IsHomeSchooled = true }, new Child { Name = "Tim", Age = 15, IsAdopted = false, IsHomeSchooled = false }, new Child { Name = "Sheena", Age = 26, IsAdopted = true, IsHomeSchooled = false }, } }); } var columns = new List <OpenExcelColumn <Person> > { new OpenExcelColumn <Person>("Name", CellValues.SharedString, (x) => x.Name) { CellFormat = Styles.CellFormat.C1 }, //new OpenExcelColumn<Person>("Name", CellValues.String, (x) => x.Name) , new OpenExcelColumn <Person>("Age", CellValues.Number, (x) => x.Age.ToString()) { CellFormat = Styles.CellFormat.C3 }, new OpenExcelColumn <Person>("Date Of Birth", CellValues.SharedString, (x) => x.DateOfBirth.ToString()) { CellFormat = Styles.CellFormat.C1 }, new OpenExcelColumn <Person>("Income", CellValues.Number, (x) => x.Income.ToString()) { CellFormat = Styles.CellFormat.C1 } }; var childColumns = new List <OpenExcelColumn <Child> > { new OpenExcelColumn <Child>(string.Empty, CellValues.SharedString, (x) => string.Empty) { CellFormat = Styles.CellFormat.C2 }, new OpenExcelColumn <Child>("Name", CellValues.SharedString, (x) => x.Name) { CellFormat = Styles.CellFormat.C2 }, new OpenExcelColumn <Child>("Age", CellValues.Number, (x) => x.Age.ToString()) { CellFormat = Styles.CellFormat.C2 }, new OpenExcelColumn <Child>("Adopted?", CellValues.SharedString, (x) => x.IsAdopted ? "Yes" : "No") { /*CellFormat = Styles.CellFormat.C2*/ CellFormatRule = (record, rowNum, colNum) => record.IsAdopted ? Styles.CellFormat.C4 : Styles.CellFormat.C2 }, new OpenExcelColumn <Child>("Home Schooled", CellValues.SharedString, (x) => x.IsHomeSchooled ? "Yes" : "No") { CellFormat = Styles.CellFormat.C2 } }; //new ExcelExporter().CreateSpreadsheetWorkbook("D:\\Projects\\Temp\\Persons.xlsx", listPersons, columns); using (var api = OpenExcelFactory.CreateOpenExcelApi("D:\\Projects\\Temp\\Persons2.xlsx")) { var sheetProperties = new OpenExcelSheetProperties { OutlineProperties = new OpenExcelOutlineProperties { SummaryBelow = false } }; api.WriteStartSheet("Prateik Sheet", sheetProperties); api.InsertHeader(columns); var childRowProperties = new OpenExcelRowProperties { OutlineLevel = 1 }; foreach (var person in listPersons) { api.WriteRowSet(new List <Person> { person }, columns); api.InsertHeader(childColumns, childRowProperties); api.WriteRowSet(person.Children, childColumns, childRowProperties); api.WriteRow(new List <string> { string.Empty }, childRowProperties, CellValues.SharedString); } api.WriteEndSheet(); api.WriteStartSheet("Ronas Sheet"); api.WriteRowSet(listPersons, columns); api.WriteEndSheet(); api.Close(); } }
ISheetBuilder IExcelBuilder.InsertSheetWithFirstRowFrozenAs(string sheetName, OpenExcelSheetProperties sheetProperties, OpenExcelSheetFormatProperties sheetFormatProperties) { var sheetViewProperties = new OpenExcelSheetViewProperties { PaneProperties = new OpenExcelSheetViewPaneProperties { XSplit = 0, YSplit = 1, TopLeftCell = "A2", State = PaneStateValues.FrozenSplit } }; (this as IExcelBuilder).InsertSheetAs(sheetName, sheetProperties, sheetViewProperties, sheetFormatProperties); return(this); }