/// <summary> /// Removes the sheet from the document /// using the given sheet unique name /// </summary> /// <param name="document">SpreadSheetDocument reference</param> /// <param name="name">Sheet name</param> public static void RemoveSheet(this SpreadsheetDocument document, string name) { //Check whether the document or the sheet name is a non-valid to work with object value if (document.IsNotValid() || name.IsNotValid()) { return; } //Search for sheet collection reference Sheets sheets = document .WorkbookPart .Workbook .GetFirstChild <Sheets>(); //If there is not any existing sheet with the given name, there is nothing to remove. if (!sheets.Descendants <Sheet>().Any(s => s.Name.Equals(name))) { return; } Sheet sheet = sheets.Descendants <Sheet>() .First(s => s.Name.Equals(name)); int index = sheets .ToList() .IndexOf(sheet); sheet.RemoveAllChildren(); sheet.Remove(); }
public IEnumerable <T> GetRows <T>(string sheetName) where T : IRecordable, new() { Sheet sheet = sheets.Descendants <Sheet>().FirstOrDefault(sheet => sheet.Name == sheetName); if (sheet != null) { return(GetRows <T>(sheet, null)); } else { return(Enumerable.Empty <T>()); } }
/// <summary> /// Adds a new Sheet to the document /// </summary> /// <param name="document">SpreadSheetDocument reference</param> /// <param name="name">Sheet name</param> public static void AddSheet(this SpreadsheetDocument document, string name) { //Check whether the document or the sheet name is a non-valid to work with object value if (document.IsNotValid() || name.IsNotValid()) { return; } //Search for sheet collection reference Sheets sheets = document .WorkbookPart .Workbook .GetFirstChild <Sheets>(); //If there already exists a sheet by the given name, there is no need to create a new one. if (sheets.Descendants <Sheet>().Any(s => s.Name.Equals(name))) { return; } //Get the last sheet pushed into the sheets collection Sheet last = sheets .Descendants <Sheet>() .OrderByDescending(s => s.SheetId) .FirstOrDefault(); //Set current workspace reference WorksheetPart workSpace = document.WorkbookPart.AddNewPart <WorksheetPart>(); //Add new worksheet with data workSpace.Worksheet = new Worksheet(new SheetData()); //Stablish index value uint index = last == null ? 1 : last.SheetId.Value; index = last == null ? 1 : index + 1; //Create the new sheet Sheet sheet = new Sheet() { Id = document.WorkbookPart.GetIdOfPart(workSpace), SheetId = index, Name = name }; //Add new sheet to the collection sheets.Append(sheet); }
public void ChangeSheetName(int idx, string name) { Sheets sheets = this.document.WorkbookPart.Workbook.GetFirstChild <Sheets>(); Sheet sheet = sheets.Descendants <Sheet>().Where(x => x.SheetId == (uint)idx).FirstOrDefault(); if (sheet != null) { sheet.Name = name; } }
private Worksheet GetWorksheet(string sheetName, Sheets sheets, WorkbookPart workbookPart) { Sheet sheet = sheets.Descendants <Sheet>().FirstOrDefault(sheet => sheet.Name == sheetName); if (sheet == null) { return(null); } WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id)); return(worksheetPart.Worksheet); }
/// <summary> /// Gets the next sheet identifier. /// </summary> /// <param name="sheets">The sheets.</param> /// <returns></returns> public static UInt32Value GetNextSheetId(this Sheets sheets) { uint next = (uint)sheets.ChildElements.Count + 1; bool keepTrying = true; while (keepTrying) { bool match = (from s in sheets.Descendants <DocumentFormat.OpenXml.Spreadsheet.Sheet>() where s.SheetId.HasValue && s.SheetId.Value.CompareTo(next) == 0 select s).Any(); if (!match) { keepTrying = false; break; } next++; } return(next); }
public void LoadXlsx(string path) { // Буфер - чтобы не держать файл открытым. MemoryStream memoryStream = new MemoryStream(); using (Stream stream = File.Open(path, FileMode.Open, FileAccess.Read)) { stream.CopyTo(memoryStream); } using (SpreadsheetDocument package = SpreadsheetDocument.Open(memoryStream, false)) { WorkbookPart workbookPart = package.WorkbookPart; SharedStringTablePart sharedStringTablePart = workbookPart.SharedStringTablePart; this.sharedStringTable = sharedStringTablePart.SharedStringTable; WorkbookStylesPart workbookStylesPart = workbookPart.WorkbookStylesPart; this.stylesheet = workbookStylesPart.Stylesheet; Workbook workbook = workbookPart.Workbook; Sheets sheets = workbook.Sheets; foreach (Sheet sheet in sheets.Descendants <Sheet>()) { WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id); Worksheet worksheet = worksheetPart.Worksheet; // размеры XDimention xd = new XDimention(worksheet.SheetDimension.Reference); // добавляем новый лист в представление Net XWorksheet xWorksheet = new XWorksheet(xd); XWorksheets.Add(xWorksheet); xWorksheet.Name = sheet.Name; // разбор свойств листа parseSheetProperties(worksheet, xWorksheet); // разбор данных parseSheetData(worksheet, xWorksheet); // разбор объединений ячеек parseMegreCells(worksheet, xWorksheet); } } memoryStream.Dispose(); }
//見出しを書き込む public override void WriteHeader(List <DataItems> Header) { if (Items != null) { return; } SpreadsheetDocument Doc = null; if (File.Exists(FileName)) { try { Doc = SpreadsheetDocument.Open(FileName, true); } catch (Exception) { throw new Exception("データファイル「" + FileName + "」を開けませんでした"); } } else { Doc = SpreadsheetDocument.Create(FileName, SpreadsheetDocumentType.Workbook); } try { WorkbookPart BPrt = Doc.WorkbookPart; Sheet MeiboSheet = null; if (BPrt == null) { BPrt = Doc.AddWorkbookPart(); BPrt.Workbook = new Workbook(); } else { //シート「名簿作成システム」を探す MeiboSheet = BPrt.Workbook.Descendants <Sheet>().Where(s => s.Name == "名簿作成システム").FirstOrDefault(); } WorksheetPart SPrt; var SData = new SheetData(); if (MeiboSheet != null) { throw new Exception("すでに本プログラム用のシートがあります"); } SPrt = BPrt.AddNewPart <WorksheetPart>(); //新規作成 SPrt.Worksheet = new Worksheet(SData); Sheets sheets = BPrt.Workbook.Descendants <Sheets>().FirstOrDefault(); bool FirstSheetFlg = false; if (sheets == null) {//新規でシート群(Sheets)作成 sheets = BPrt.Workbook. AppendChild <Sheets>(new Sheets()); FirstSheetFlg = true; } MeiboSheet = new Sheet() { Id = Doc.WorkbookPart.GetIdOfPart(SPrt), SheetId = FirstSheetFlg ? 1 : sheets.Descendants <Sheet>().Last <Sheet>().SheetId + 1, Name = "名簿作成システム" }; sheets.Append(MeiboSheet); var FirstRow = new Row(); for (int i = 0; i < Header.Count; i++) //項目を追加 { FirstRow.Append(new Cell() { DataType = CellValues.String, CellReference = (char)((int)'A' + i) + "1", CellValue = new CellValue(EnumToLabel[(int)Header[i]]) }); } SData.Append(FirstRow); } finally { if (Doc != null) { Doc.Close(); } } Items = Header; }