OpenXML sdk Изменить лист в моем документе Excel

, я создаю пустой шаблон в excel. Я хотел бы открыть шаблон и отредактировать документ, но не знаю, как изменить существующий лист. Это код:


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();

спасибо!

Обновление

Я пробую, но он не работает. Я еще использую этот метод (GetWorksheetPart ()), но хочу получить существующий рабочий лист, отредактировать его и сохранить измененный новый документ.


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>

Я создаю документ, НО поврежден. Почему?

7
задан Tim Post 16 February 2011 в 15:07
поделиться