OpenXML sdk Modify a sheet in the Excel document

openxmlopenxml-sdkworksheet

I create an empty template in excel.
I would like to open the template and edit the document but I do not know how to change the existing sheet.
That's the code: 


using (SpreadsheetDocument xl = SpreadsheetDocument.Open(filename, true)) 
{ 
WorkbookPart wbp = xl.WorkbookPart; 
WorkbookPart workbook = xl.WorkbookPart; 
// Get the worksheet with the required name. 
// To be used to match the ID for the required sheet data 
// because the Sheet class and the SheetData class aren't 
// linked to each other directly. 
Sheet s = null; 
if (wbp.Workbook.Sheets.Elements().Count(nm => nm.Name == sheetName) == 0) 
{ 
// no such sheet with that name 
xl.Close(); 
return; 
} 
else 
{ 
s = (Sheet)wbp.Workbook.Sheets.Elements().Where(nm => nm.Name == sheetName).First(); 
} 



WorksheetPart wsp = (WorksheetPart)xl.WorkbookPart.GetPartById(s.Id.Value); 
Worksheet worksheet = new Worksheet(); 
SheetData sd = new SheetData(); 
//SheetData sd = (SheetData)wsp.Worksheet.GetFirstChild(); 
Stylesheet styleSheet = workbook.WorkbookStylesPart.Stylesheet; 
//SheetData sheetData = new SheetData(); 
//build the formatted header style 
UInt32Value headerFontIndex = 
util.CreateFont( 
styleSheet, 
"Arial", 
10, 
true, 
System.Drawing.Color.Red); 



//build the formatted date style 
UInt32Value dateFontIndex = 
util.CreateFont( 
styleSheet, 
"Arial", 
8, 
true, 
System.Drawing.Color.Black); 



//set the background color style 
UInt32Value headerFillIndex = 
util.CreateFill( 
styleSheet, 
System.Drawing.Color.Black); 



//create the cell style by combining font/background 
UInt32Value headerStyleIndex = 
util.CreateCellFormat( 
styleSheet, 
headerFontIndex, 
headerFillIndex, 
null); 
/* 
* Create a set of basic cell styles for specific formats... 
* If you are controlling your table then you can simply create the styles you need, 
* this set of code is still intended to be generic. 
*/ 
_numberStyleId = util.CreateCellFormat(styleSheet, null, null, UInt32Value.FromUInt32(3)); 
_doubleStyleId = util.CreateCellFormat(styleSheet, null, null, UInt32Value.FromUInt32(4)); 
_dateStyleId = util.CreateCellFormat(styleSheet, null, null, UInt32Value.FromUInt32(14)); 
_textStyleId = util.CreateCellFormat(styleSheet, headerFontIndex, headerFillIndex, null); 
_percentageStyleId = util.CreateCellFormat(styleSheet, null, null, UInt32Value.FromUInt32(9)); 



util.AddNumber(xl, sheetName, (UInt32)3, "E", "27", _numberStyleId); 
util.AddNumber(xl, sheetName, (UInt32)3, "F", "3.6", _doubleStyleId); 
util.AddNumber(xl, sheetName, (UInt32)5, "L", "5", _percentageStyleId); 
util.AddText(xl, sheetName, (UInt32)5, "M", "Dario", _textStyleId); 
util.AddDate(xl, sheetName, (UInt32)3, "J", DateTime.Now, _dateStyleId); 
util.AddImage(xl, sheetName, imagePath, "Smile", "Smile", 30, 30); 
util.MergeCells(xl, sheetName, "D12", "F12"); 
//util.DeleteValueCell(spreadsheet, sheetName, "F", (UInt32)8); 



txtCellText.Text = util.GetCellValue(xl, sheetName, (UInt32)5, "M"); 



double number = util.GetCellDoubleValue(xl, sheetName, (UInt32)3, "E"); 
double numberD = util.GetCellDoubleValue(xl, sheetName, (UInt32)3, "F"); 
DateTime datee = util.GetCellDateTimeValue(xl, sheetName, (UInt32)3, "J"); 



//txtDoubleCell.Text = util.GetCellValue(spreadsheet, sheetName, (UInt32)3, "P"); 
txtPercentualeCell.Text = util.GetCellValue(xl, sheetName, (UInt32)5, "L"); 



string date = util.GetCellValue(xl, sheetName, (UInt32)3, "J"); 
double dateD = Convert.ToDouble(date); 
DateTime dateTime = DateTime.FromOADate(dateD); 
txtDateCell.Text = dateTime.ToShortDateString(); 



//worksheet.Append(sd); 
/* 
Columns columns = new Columns(); 
columns.Append(util.CreateColumnData(10, 10, 40)); 



worksheet.Append(columns); 
*/ 
SheetProtection sheetProtection1 = new SheetProtection() { Sheet = true, Objects = true, Scenarios = true, SelectLockedCells = true, SelectUnlockedCells = true }; 
worksheet.Append(sheetProtection1); 
wsp.Worksheet = worksheet; 
wsp.Worksheet.Save(); 



xl.WorkbookPart.Workbook.Save(); 
xl.Close();

thanks!

Update

I try it but it doesn't work.I'm using yet this method (GetWorksheetPart() ) but i want to get the existing worksheet, edit it and save the new document modified.


using (SpreadsheetDocument xl = SpreadsheetDocument.Open(filename, true))
{
     WorkbookPart wbp = xl.WorkbookPart;


 WorksheetPart worksheetPart = util.GetWorksheetPart(wbp, sheetName);
 SheetProtection sheetProtection1 = new SheetProtection() { Sheet = true, Objects = true, Scenarios = true, SelectLockedCells = true, SelectUnlockedCells = true };

 worksheetPart.Worksheet.Append(sheetProtection1);
 worksheetPart.Worksheet.Save(); </pre></code>

I build the document BUT is corrupt. why?

Best Answer

To get an existing worksheet to edit use this code to get the part:

        public static WorksheetPart GetWorksheetPart(WorkbookPart workbookPart, string sheetName)
        {
            string relId = workbookPart.Workbook.Descendants<Sheet>()
                                 .Where(s => sheetName.Equals(s.Name))
                                 .First()
                                 .Id;

            return (WorksheetPart)workbookPart.GetPartById(relId);
        }

Just make sure that the sheetname exists you are searching for or you will get some exceptions. Then use the reference to that worksheetpart to perform any edits your want and at the end just call worksheetPart.Worksheet.Save();

EDIT

The SheetProtection element needs to be inserted after the SheetData element. Try this line when appending: worksheetPart.Worksheet.Descendants<SheetData>().First().InsertAfterSelf(sheetProtection1);

Related Topic