We are using openXml to generate spreadsheet,and I need to get worksheetpart to work on the worksheet . Below is the class we are using to generate the SpreadSheetDocument instance.
public class Excelsheet
{
private readonly SpreadsheetDocument _spreadsheet;
private readonly WorkbookPart _workbookPart;
private readonly MemoryStream _memoryStream;
private readonly Sheets _sheets;
public WorksheetPart? _worksheetPart = null;
public Excelsheet()
{
_memoryStream = new MemoryStream();
_spreadsheet = SpreadsheetDocument.Create(_memoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
_workbookPart = _spreadsheet.AddWorkbookPart();
_workbookPart.Workbook = new Workbook();
_sheets = _workbookPart.Workbook.AppendChild<Sheets>(new Sheets());
}
internal Spreadsheet OpenSheet(string name, string prefix,string suffixSheetno ="")
{
string fullName = string.IsNullOrEmpty(prefix) ? name : prefix + name;
fullName += suffixSheetno;
_currentSheetName = fullName ;
_worksheetPart = _workbookPart.AddNewPart<WorksheetPart>();
var sheet = new Sheet() { Id = _workbookPart.GetIdOfPart(_worksheetPart), SheetId = GetNewSheetId(), Name = fullName };
_sheets.Append(sheet);
DataWriter = OpenXmlWriter.Create(_worksheetPart);
DataWriter.WriteStartElement(new Worksheet());
_sheetOpen = true;
_rowNumber = 0;
return this;
}
}
We are creating SpreadSheetDocument instance and working on it. We have a situation where we need more control on worksheetpart to modify values of cells. I was trying to get worksheetpart from Spreadsheet document,but it is giving an exception for worksheet property
WorksheetPart.worksheet is giving "System.Io. exception".
Here is the code to export to excel
public byte[] Export()
{
Excelsheet excelsheet = new();
string namePrefix = string.Empty;
excelsheet
.OpenSheet("TestAbc", namePrefix)
.OpenData();
var worksheetPart=GetWorksheetPartByName(excelsheet,"TestAbc");
spreadsheet
.CloseData()
.CloseSheet();
return spreadsheet.Render();
}
private WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
{
IEnumerable<Sheet> sheets =
document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
Elements<Sheet>().Where(s => s.Name == sheetName);
if (sheets?.Count() == 0)
{
// The specified worksheet does not exist.
return null;
}
string relationshipId = sheets?.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
return worksheetPart;
}
Please let me know how to retrieve this sheet property