OpenXML sdk Modify a sheet in my Excel document
- by user465202
hi!
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!
