Geek Answers Handbook

OpenXML sdk Change Sheet in Excel Document

I am creating an empty template in excel. I would like to open a template and edit a document, but I do not know how to modify an existing sheet. What 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, but it does not work. I use this method (GetWorksheetPart ()), but I want to get an existing worksheet, edit it and save a new document.

 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 am creating a document, but it is damaged. What for?

+7
source share
1 answer

To get an existing worksheet for editing, 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 the sheet name exists that you are looking for, or you will get some exceptions. Then use the link to worksheetpart to make any changes you want, and in the end just call worksheetPart.Worksheet.Save();

EDIT

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

+6
source

More articles:


All Articles