public static WorksheetPart GetWorksheetFromSheetName(WorkbookPart workbookPart, string sheetName) { var sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName); if (sheet == null) { return null; } return workbookPart.GetPartById(sheet.Id) as WorksheetPart; }
public List<RPCFan> RetrieveFirstObject(WorkbookPart wbPart, Sheet theSheet) { string abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); Cell theCell = wsPart.Worksheet.Descendants<Cell>(). Where(c => c != null).FirstOrDefault(); string first = FindCellInnerValue(wbPart, theCell); var letra = first.Substring(0, 1); int numeros = int.Parse(first.Substring(1)); int i = abc.IndexOf(letra); List<RPCFan> retorno = new List<RPCFan>(); int countFans = 0; int numeroscopy = numeros; while (theCell != null) { string newRef = abc[i].ToString() + (numeroscopy++ + 1).ToString(); theCell = wsPart.Worksheet.Descendants<Cell>(). Where(c => c.CellReference == newRef).FirstOrDefault(); countFans++; } for (int ii = 0; ii < (countFans - 1); ii++) { string newRef; i = abc.IndexOf(letra); theCell = new Cell(); var fan = new RPCFan(); List<string> data = new List<string>(); while (theCell != null) { newRef = abc[i++].ToString() + (numeros + 1).ToString(); theCell = wsPart.Worksheet.Descendants<Cell>(). Where(c => c.CellReference == newRef).FirstOrDefault(); var value = FindCellInnerValue(wbPart, theCell, false); data.Add(value); //i++; } fan.YaEntro = false; fan.LastCell = abc[i-2].ToString() + (numeros + 1).ToString(); fan.ID = data[0]; fan.Nombre = data[1]; fan.CardNum = data[2]; fan.DNI = data[3]; fan.Ingreso = data[4]; if (data[5]!="0") fan.YaEntro = true; retorno.Add(fan); numeros++; } return retorno; }
/// <summary> /// 根据WorkbookPart和sheetName获取该Sheet下所有Row数据 /// </summary> /// <param name="workBookPart">WorkbookPart对象</param> /// <param name="sheetName">SheetName</param> /// <returns>该SheetName下的所有Row数据</returns> public IEnumerable<Row> GetWorkBookPartRows(WorkbookPart workBookPart, string sheetName) { IEnumerable<Row> sheetRows = null; //根据表名在WorkbookPart中获取Sheet集合 IEnumerable<Sheet> sheets = workBookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName); if (sheets.Count() == 0) { return null;//没有数据 } WorksheetPart workSheetPart = workBookPart.GetPartById(sheets.First().Id) as WorksheetPart; //获取Excel中得到的行 sheetRows = workSheetPart.Worksheet.Descendants<Row>(); return sheetRows; }
public WorkbookIndexer(WorkbookPart toIndex) { if (toIndex == null) { throw new ArgumentNullException("workbookPart"); } foreach (Sheet sheet in toIndex.Workbook.Sheets) { var worksheet = (WorksheetPart)toIndex.GetPartById(sheet.Id); if (worksheet != null) { this.worksheets.Add(new WorksheetIndexer(worksheet, sheet)); } } // TODO: This should be modified to create the shared string table part instead of just bailing on an empty worksheet. this.SharedStringTable = toIndex.SharedStringTablePart != null ? new SharedStringTableIndexer(toIndex.SharedStringTablePart) : null; }
// http://blogs.msdn.com/b/vsod/archive/2010/02/05/how-to-delete-a-worksheet-from-excel-using-open-xml-sdk-2-0.aspx private void DeleteSheetAndDependencies(WorkbookPart wbPart, string sheetId) { //Get the SheetToDelete from workbook.xml Sheet worksheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Id == sheetId).FirstOrDefault(); if (worksheet == null) { } string sheetName = worksheet.Name; // Get the pivot Table Parts IEnumerable<PivotTableCacheDefinitionPart> pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts; Dictionary<PivotTableCacheDefinitionPart, string> pvtTableCacheDefinationPart = new Dictionary<PivotTableCacheDefinitionPart, string>(); foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts) { PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition; //Check if this CacheSource is linked to SheetToDelete var pvtCahce = pvtCacheDef.Descendants<CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetName); if (pvtCahce.Count() > 0) { pvtTableCacheDefinationPart.Add(Item, Item.ToString()); } } foreach (var Item in pvtTableCacheDefinationPart) { wbPart.DeletePart(Item.Key); } // Remove the sheet reference from the workbook. WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(sheetId)); worksheet.Remove(); // Delete the worksheet part. wbPart.DeletePart(worksheetPart); //Get the DefinedNames var definedNames = wbPart.Workbook.Descendants<DefinedNames>().FirstOrDefault(); if (definedNames != null) { List<DefinedName> defNamesToDelete = new List<DefinedName>(); foreach (DefinedName Item in definedNames) { // This condition checks to delete only those names which are part of Sheet in question if (Item.Text.Contains(worksheet.Name + "!")) defNamesToDelete.Add(Item); } foreach (DefinedName Item in defNamesToDelete) { Item.Remove(); } } // Get the CalculationChainPart //Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the //workbook whose value is calculated from any formula CalculationChainPart calChainPart; calChainPart = wbPart.CalculationChainPart; if (calChainPart != null) { var calChainEntries = calChainPart.CalculationChain.Descendants<CalculationCell>().Where(c => c.SheetId == sheetId); List<CalculationCell> calcsToDelete = new List<CalculationCell>(); foreach (CalculationCell Item in calChainEntries) { calcsToDelete.Add(Item); } foreach (CalculationCell Item in calcsToDelete) { Item.Remove(); } if (calChainPart.CalculationChain.Count() == 0) { wbPart.DeletePart(calChainPart); } } }
private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName) { Sheet foundSheet = null; foreach (Sheet sheet in workbookPart.Workbook.Sheets) { if (sheet.Name == sheetName) { foundSheet = sheet; break; } } if (foundSheet == null) { // Add a new worksheet part to the workbook. WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>(); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); // Get a unique ID for the new sheet. uint sheetId = 1; if (sheets.Elements<Sheet>().Count() > 0) { sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1; } // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); workbookPart.Workbook.Save(); return newWorksheetPart; } else { return (WorksheetPart)workbookPart.GetPartById(foundSheet.Id); } }
private void OpenExistingSpreadsheet(string SheetNameOnOpen) { xl = SpreadsheetDocument.Open(memstream, true); wbp = xl.WorkbookPart; IsNewSpreadsheet = false; slwb = new SLWorkbook(); this.DocumentProperties = new SLDocumentProperties(); this.LoadDocumentProperties(); InitialiseAutoFitCache(); LoadBuiltInNumberingFormats(); InitialiseStylesheetWhatNots(SLThemeTypeValues.Office); LoadSharedStringTable(); giWorksheetIdCounter = 0; using (OpenXmlReader oxr = OpenXmlReader.Create(wbp)) { SLWorkbookView wv; Sheet s; SLSheet sheet; DefinedName dn; SLDefinedName sldn; while (oxr.Read()) { if (oxr.ElementType == typeof(WorkbookView)) { wv = new SLWorkbookView(); wv.FromWorkbookView((WorkbookView)oxr.LoadCurrentElement()); slwb.WorkbookViews.Add(wv); } else if (oxr.ElementType == typeof(Sheet)) { s = (Sheet)oxr.LoadCurrentElement(); sheet = new SLSheet(s.Name.Value, s.SheetId.Value, s.Id.Value, SLSheetType.Unknown); if (s.State != null) sheet.State = s.State.Value; slwb.Sheets.Add(sheet); if (sheet.SheetId > giWorksheetIdCounter) { giWorksheetIdCounter = (int)sheet.SheetId; } } else if (oxr.ElementType == typeof(DefinedName)) { dn = (DefinedName)oxr.LoadCurrentElement(); sldn = new SLDefinedName(dn.Name.Value); sldn.FromDefinedName(dn); slwb.DefinedNames.Add(sldn); } else if (oxr.ElementType == typeof(PivotCache)) { // cache IDs supposed to be unique, so I'm not gonna check for the hash set slwb.PivotTableCacheIds.Add(((PivotCache)oxr.LoadCurrentElement()).CacheId.Value); } } } if (wbp.Workbook.WorkbookProperties != null) { slwb.WorkbookProperties.FromWorkbookProperties(wbp.Workbook.WorkbookProperties); } if (wbp.CalculationChainPart != null) { int iCurrentSheetId = 0; SLCalculationCell slcc = new SLCalculationCell(string.Empty); CalculationCell cc; using (OpenXmlReader oxr = OpenXmlReader.Create(wbp.CalculationChainPart)) { while (oxr.Read()) { if (oxr.ElementType == typeof(CalculationCell)) { cc = (CalculationCell)oxr.LoadCurrentElement(); if (cc.SheetId == null) { cc.SheetId = iCurrentSheetId; } else { if (cc.SheetId.Value != iCurrentSheetId) iCurrentSheetId = cc.SheetId.Value; } slcc.FromCalculationCell(cc); slwb.CalculationCells.Add(slcc.Clone()); } } } } // To determine the type of sheet. Do this before the part // where the table and pivot table parts are set. bool bFound = false; string sRelID = string.Empty; foreach (SLSheet sheet in slwb.Sheets) { bFound = false; foreach (WorksheetPart wspFound in wbp.WorksheetParts) { sRelID = wbp.GetIdOfPart(wspFound); if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase)) { sheet.SheetType = SLSheetType.Worksheet; bFound = true; break; } } if (!bFound) { foreach (ChartsheetPart csp in wbp.ChartsheetParts) { sRelID = wbp.GetIdOfPart(csp); if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase)) { sheet.SheetType = SLSheetType.Chartsheet; bFound = true; break; } } } if (!bFound) { foreach (DialogsheetPart dsp in wbp.DialogsheetParts) { sRelID = wbp.GetIdOfPart(dsp); if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase)) { sheet.SheetType = SLSheetType.DialogSheet; bFound = true; break; } } } if (!bFound) { foreach (MacroSheetPart msp in wbp.MacroSheetParts) { sRelID = wbp.GetIdOfPart(msp); if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase)) { sheet.SheetType = SLSheetType.Macrosheet; bFound = true; break; } } } } WorksheetPart wsp; foreach (SLSheet sheet in slwb.Sheets) { if (sheet.SheetType == SLSheetType.Worksheet) { wsp = (WorksheetPart)wbp.GetPartById(sheet.Id); foreach (TableDefinitionPart tdp in wsp.TableDefinitionParts) { if (tdp.Table.Id != null && !slwb.TableIds.Contains(tdp.Table.Id.Value)) slwb.TableIds.Add(tdp.Table.Id.Value); if (tdp.Table.Name != null && !slwb.TableNames.Contains(tdp.Table.Name.Value)) slwb.TableNames.Add(tdp.Table.Name.Value); } foreach (PivotTablePart ptp in wsp.PivotTableParts) { if (ptp.PivotTableDefinition.Name != null && !slwb.PivotTableNames.Contains(ptp.PivotTableDefinition.Name.Value)) slwb.PivotTableNames.Add(ptp.PivotTableDefinition.Name.Value); // the cache ID should already be added, from the workbook part above. // But we check again just to be sure. Cache IDs have to be unique throughout // the workbook. if (ptp.PivotTableDefinition.CacheId != null && !slwb.PivotTableCacheIds.Contains(ptp.PivotTableDefinition.CacheId.Value)) slwb.PivotTableCacheIds.Add(ptp.PivotTableDefinition.CacheId.Value); } } } string sWorksheetName = SLConstants.DefaultFirstSheetName; int i = 1; bool bCannotFind = true; bool bIsLegit = true; if (wbp.WorksheetParts.Count() == 0) { // no worksheets! Apparently an Excel file with only 1 dialog sheet is perfectly legit... // come up with a legit worksheet name that's not already taken... i = 1; bCannotFind = true; while (bCannotFind) { sWorksheetName = string.Format("Sheet{0}", i); bIsLegit = true; foreach (SLSheet sheet in slwb.Sheets) { if (sheet.Name.Equals(sWorksheetName, StringComparison.OrdinalIgnoreCase)) { bIsLegit = false; break; } } ++i; if (bIsLegit) bCannotFind = false; } AddWorksheet(sWorksheetName); } else { bFound = false; // there's a given worksheet name if (SheetNameOnOpen.Length > 0) { foreach (SLSheet sheet in slwb.Sheets) { if (sheet.Name.Equals(SheetNameOnOpen, StringComparison.OrdinalIgnoreCase) && sheet.SheetType == SLSheetType.Worksheet) { giSelectedWorksheetID = sheet.SheetId; gsSelectedWorksheetName = sheet.Name; gsSelectedWorksheetRelationshipID = sheet.Id; bFound = true; break; } } } else { // we try to get the "actively selected" worksheet already selected. uint iActiveTab = 0; if (slwb.WorkbookViews.Count > 0) { iActiveTab = slwb.WorkbookViews[0].ActiveTab; } // there should be at least *this* number of sheets (whether it's a worksheet // chartsheet or whatnot). if (slwb.Sheets.Count > iActiveTab && slwb.Sheets[(int)iActiveTab].SheetType == SLSheetType.Worksheet) { giSelectedWorksheetID = slwb.Sheets[(int)iActiveTab].SheetId; gsSelectedWorksheetName = slwb.Sheets[(int)iActiveTab].Name; gsSelectedWorksheetRelationshipID = slwb.Sheets[(int)iActiveTab].Id; bFound = true; } } if (!bFound) { // we get here either if there's no given worksheet name (bFound is still false), // or there's a given worksheet name but corresponding values weren't found. // The given worksheet name must be that of a worksheet. A chartsheet name is // considered "invalid". // Either way, we use the first available worksheet as the selected worksheet. wsp = wbp.WorksheetParts.First(); sRelID = wbp.GetIdOfPart(wsp); foreach (SLSheet sheet in slwb.Sheets) { if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase)) { giSelectedWorksheetID = sheet.SheetId; gsSelectedWorksheetName = sheet.Name; gsSelectedWorksheetRelationshipID = sheet.Id; bFound = true; break; } } } if (bFound) { // A viable worksheet should be found by now. Otherwise, it's probably // a corrupted spreadsheet... LoadSelectedWorksheet(); IsNewWorksheet = false; } else { // why is it not found!?! The file is corrupted somehow... we'll try to recover // by adding a new worksheet and selecting it. Same algorithm as above. i = 1; bCannotFind = true; while (bCannotFind) { sWorksheetName = string.Format("Sheet{0}", i); bIsLegit = true; foreach (SLSheet sheet in slwb.Sheets) { if (sheet.Name.Equals(sWorksheetName, StringComparison.OrdinalIgnoreCase)) { bIsLegit = false; break; } } ++i; if (bIsLegit) bCannotFind = false; } AddWorksheet(sWorksheetName); } } }
/// <summary> /// 保存sheet数据生成data文件 /// </summary> /// <param name="FileName">数据文件(.tydd文件)的完整路径及文件名(包括扩展名)</param> /// <param name="configFileName">配置文件(.gzg文件)的完整路径及文件名(包括扩展名)</param> /// <param name="sheet">Sheet对象</param> /// <param name="worksheetPart">WorksheetPart对象</param> /// <param name="ExcelCfg">配置对象</param> /// <param name="ExcelLastWriteTime">Excel文件的最后更新时间</param> private static bool SaveData(string FileName, string configFileName, Sheet sheet, WorkbookPart workbookPart, ExcelConfig ExcelCfg, long ExcelLastWriteTime) { var worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart; if (worksheetPart.Worksheet.Descendants<Row>().Count() <= 0) return false; // Sheet配置 SheetConfig Sheetcfg = ExcelCfg.GetSheetConfig(sheet.Name); if (Sheetcfg == null) { Console.Error.WriteLine(string.Format(Resources.ExcelConfigFileUnMatchExcel, configFileName, sheet.Name)); return false; } //获取WorkbookPart中NumberingFormats样式集合 var stylesList = GetNumberFormatsStyle(workbookPart); // 保存 using (FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write)) { using (var bw = new BinaryWriter(fs)) { // 保存对应excel文件的最后更新时间 bw.Write(ExcelLastWriteTime); // 写入列信息和其MD5 try { var colomnInfoMD5Bytes = Sheetcfg.ColomnInfoBytesMD5.ConvertHexStringToByteArray(); bw.Write(colomnInfoMD5Bytes.Length); bw.Write(colomnInfoMD5Bytes); var colomnInfoBytes = Sheetcfg.ColomnInfoBytesStr.ConvertHexStringToByteArray(); bw.Write(colomnInfoBytes.Length); bw.Write(colomnInfoBytes); } catch (Exception e) { Console.Error.WriteLine(e); return false; } bw.Flush(); using (ProtoWriter protoWriter = ProtoBufExtention.CreateProtoWriterSimplely(fs)) { // 每行数据 foreach (var row in worksheetPart.Worksheet.Descendants<Row>()) { if (row.RowIndex == 1) continue; var cellUnitList = new List<CellUnit>(); int i = 0, exportColumnIndex = 0; foreach (Cell cell in row) { // 将CellReference换算成1起始列序号,以检查空格子 string cellReference = cell.CellReference; int columnIndex = 0; int factor = 1; for (int pos = cellReference.Length - 1; pos >= 0; pos--) // R to L { if (char.IsLetter(cellReference[pos])) // for letters (columnName) { columnIndex += factor * ((cellReference[pos] - 'A') + 1); factor *= 26; } } while (i < columnIndex - 1) // 出现空格子 { //SaveDate(i, Sheetcfg, null, workbookPart, stylesList, protoWriter); if (!AddCellUnit(i, ref exportColumnIndex, Sheetcfg, null, workbookPart, stylesList, cellUnitList)) return false; ++i; } //SaveDate(i, Sheetcfg, cell, workbookPart, stylesList, protoWriter); if (!AddCellUnit(i, ref exportColumnIndex, Sheetcfg, cell, workbookPart, stylesList, cellUnitList)) return false; ++i; } while (i < Sheetcfg.ColumnConfigData.Count) // 末尾出现空格子 { //SaveDate(i, Sheetcfg, null, workbookPart, stylesList, protoWriter); if (!AddCellUnit(i, ref exportColumnIndex, Sheetcfg, null, workbookPart, stylesList, cellUnitList)) return false; ++i; } protoWriter.WriteSimplely(cellUnitList.Count); foreach (var cellUnit in cellUnitList) cellUnit.WriteWithProtoWriter(protoWriter); } } } } return true; }
public static void DeleteWorksheet(WorkbookPart workbookPart, string sheetName) { //string sheetid = ""; // Get the pivot Table Parts IEnumerable<PivotTableCacheDefinitionPart> pvtTableCacheParts = workbookPart.PivotTableCacheDefinitionParts; var pvtTableCacheDefinationPart = (from pivotTableCacheDefinitionPart in pvtTableCacheParts let pvtCacheDef = pivotTableCacheDefinitionPart.PivotCacheDefinition let pvtCache = pvtCacheDef.Descendants<CacheSource>(). Where(s => s.WorksheetSource.Sheet == sheetName) where pvtCache.Count() > 0 select pivotTableCacheDefinitionPart). ToDictionary(pivotTableCacheDefinitionPart => pivotTableCacheDefinitionPart, pivotTableCacheDefinitionPart => pivotTableCacheDefinitionPart.ToString()); foreach (var item in pvtTableCacheDefinationPart) { workbookPart.DeletePart(item.Key); } //Get the SheetToDelete from workbook.xml Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault(); if (sheet == null) { // The specified sheet doesn't exist. return; } //Store the SheetID for the reference var sheetid = sheet.SheetId; // Remove the sheet reference from the workbook. var worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id)); sheet.Remove(); // Delete the worksheet part. workbookPart.DeletePart(worksheetPart); //Get the DefinedNames var definedNames = workbookPart.Workbook.Descendants<DefinedNames>().FirstOrDefault(); if (definedNames != null) { foreach (DefinedName item in definedNames) { // This condition checks to delete only those names which are part of Sheet in question if (item.Text.Contains(sheetName + "!")) item.Remove(); } } // Get the CalculationChainPart //Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the //workbook whose value is calculated from any formula CalculationChainPart calChainPart = workbookPart.CalculationChainPart; if (calChainPart != null) { var calChainEntries = calChainPart.CalculationChain.Descendants<CalculationCell>().Where(c => (uint)c.SheetId.Value == sheetid); foreach (CalculationCell item in calChainEntries) { item.Remove(); } if (calChainPart.CalculationChain.Count() == 0) { workbookPart.DeletePart(calChainPart); } } }
public static void InsertValuesInSheets(string sheetName, SelectQueryBuilder queryBuilder, WorkbookPart workbookPart, DataTable table) { WorksheetPart worksheetPart = null; if (!string.IsNullOrEmpty(sheetName)) { Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>(); worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id); } else { worksheetPart = workbookPart.WorksheetParts.FirstOrDefault(); } if (worksheetPart != null) { SheetData data = worksheetPart.Worksheet.GetFirstChild<SheetData>(); //add column names to the first row Row header = new Row(); header.RowIndex = (UInt32)1; foreach (DataColumn column in table.Columns) { Cell headerCell = createTextCell( table.Columns.IndexOf(column) + 1, 1, column.ColumnName); header.AppendChild(headerCell); } data.AppendChild(header); //loop through each data row DataRow contentRow; for (int i = 0; i < table.Rows.Count; i++) { contentRow = table.Rows[i]; data.AppendChild(createContentRow(contentRow, i + 2)); } worksheetPart.Worksheet.Save(); } }
public static bool UpdateValue( WorkbookPart wbPart, string sheetName, string addressName, string value, UInt32Value styleIndex, bool isString) { // Assume failure. bool updated = false; Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where( (s) => s.Name == sheetName).FirstOrDefault(); if (sheet != null) { Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet; DocumentFormat.OpenXml.Spreadsheet.Cell cell = InsertCellInWorksheet(ws, addressName); if (isString) { // Either retrieve the index of an existing string, // or insert the string into the shared string table // and get the index of the new item. int stringIndex = InsertSharedStringItem(wbPart, value); cell.CellValue = new CellValue(stringIndex.ToString()); cell.DataType = new EnumValue<CellValues>(CellValues.SharedString); } else { cell.CellValue = new CellValue(value); cell.DataType = new EnumValue<CellValues>(CellValues.Number); } if (styleIndex > 0) cell.StyleIndex = styleIndex; // Save the worksheet. ws.Save(); updated = true; } return updated; }
/// <summary> /// Finds the worksheetPart with given sheetName /// </summary> /// <param name="workbookPart">Workbook part which contains the worksheet we are looking for</param> /// <param name="sheetName">Name of the worksheet it's looking for</param> /// <returns>WorksheetPart with given name</returns> private WorksheetPart getWorksheetPart(WorkbookPart workbookPart, string sheetName) { Sheet sheet = workbookPart.Workbook.Descendants<Sheet>() .FirstOrDefault(s => sheetName.Equals(s.Name)); if (sheet == null) { throw new Exception( string.Format("Could not find a sheet with name {0}", sheetName)); } else { return workbookPart.GetPartById(sheet.Id) as WorksheetPart; } }
/// <summary> /// Deletes a sheet given the workbook the sheet is in and the sheetname; /// </summary> /// <param name="wbPart">Workbook the sheet is part of</param> /// <param name="sheetToDelete">The name of the sheet to be deleted</param> private void deleteAWorkSheet(WorkbookPart wbPart, string sheetToDelete) { string Sheetid = ""; // Get the pivot Table Parts IEnumerable<PivotTableCacheDefinitionPart> pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts; var pvtTableCacheDefinationPart = new Dictionary<PivotTableCacheDefinitionPart, string>(); foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts) { PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition; //Check if this CacheSource is linked to SheetToDelete IEnumerable<CacheSource> pvtCahce = pvtCacheDef.Descendants<CacheSource>() .Where( s => s.WorksheetSource.Sheet == sheetToDelete); if (pvtCahce.Count() > 0) { pvtTableCacheDefinationPart.Add(Item, Item.ToString()); } } foreach (var Item in pvtTableCacheDefinationPart) { wbPart.DeletePart(Item.Key); } //Get the SheetToDelete from workbook.xml Sheet theSheet = wbPart.Workbook.Descendants<Sheet>() .FirstOrDefault(s => s.Name == sheetToDelete); if (theSheet == null) { // The specified sheet doesn't exist. } //Store the SheetID for the reference Sheetid = theSheet.SheetId; // Remove the sheet reference from the workbook. var worksheetPart = (WorksheetPart) (wbPart.GetPartById(theSheet.Id)); theSheet.Remove(); // Delete the worksheet part. wbPart.DeletePart(worksheetPart); //Get the DefinedNames DefinedNames definedNames = wbPart.Workbook.Descendants<DefinedNames>().FirstOrDefault(); if (definedNames != null) { var defNamesToDelete = new List<DefinedName>(); foreach (DefinedName Item in definedNames) { // This condition checks to delete only those names which are part of Sheet in question if (Item.Text.Contains(sheetToDelete + "!")) { defNamesToDelete.Add(Item); } } foreach (DefinedName Item in defNamesToDelete) { Item.Remove(); } } // Save the workbook. wbPart.Workbook.Save(); }
public static WorksheetPart XLPart(this Sheet wsheet, WorkbookPart wbPart) { return (WorksheetPart)(wbPart.GetPartById(wsheet.Id)); }
private void LoadSheets(WorkbookPart workbook, Dictionary<string, string> allParameters) { foreach (string k in allParameters.Keys) { string range = allParameters[k]; string[] rangeParts = range.Split('!'); string table = rangeParts[0]; if (!_sheets.ContainsKey(table)) { XmlNamespaceManager nsManager = new XmlNamespaceManager(_workbook.NameTable); nsManager.AddNamespace("d", _workbook.DocumentElement.NamespaceURI); XmlNodeList nodes = _workbook.SelectNodes("//d:sheets/d:sheet", nsManager); foreach (XmlNode node in nodes) { if (node.Attributes["name"].Value == table) { OpenXmlPart part = workbook.GetPartById(node.Attributes["r:id"].Value); _sheets.Add(table, part); } } } } }
public int getRowsNumber(WorkbookPart wbPart) { int res = 0; string cell = "A" + res; Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault(); if (theSheet == null) throw new ArgumentException("Foglio {0} non trovato", sheetName); // Retrieve a reference to the worksheet part. WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); //spreadsheet.GetCellValue(path_desktop + @"\files\in\" + file_excel, "FIPARSER", cell); try { string test = null; while ((test = GetCellValueNew(wsPart, wbPart, res, 0)) != null && !test.Equals("")) { res++; Console.Out.WriteLine(test); cell = "A" + res; } } catch (Exception ex) { Console.Out.WriteLine("getRowsNumber"); Console.Out.WriteLine(ex.Message); Console.WriteLine("Premere un tasto per uscire."); Console.ReadKey(); System.Environment.Exit(-1); } /**/ return res; } /** legge un file excel e riempie la tabella degli atleti */ public String[][] parseSpreadSheet(int columns) { string[][] res = null; string fileName = getSpreadSheetPath(); try { using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false)) { int rows = getRowsNumber(document.WorkbookPart); Console.Out.WriteLine("Righe lette: {0}", rows); if (rows > 0) res = cycleFile(document, rows, columns); else throw new ArrayBoundaryException("numero di righe non valido: la prima riga è vuota?"); } } catch (Exception ex) { Trace.WriteLine(ex.Message); Trace.WriteLine(ex.StackTrace); throw; } return res; }
public static string GetCellValue(WorkbookPart wbPart, Sheet theSheet, string address) { string value = null; if (theSheet != null) { WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); Cell theCell = wsPart.Worksheet.Descendants<Cell>(). Where(c => c.CellReference == address).FirstOrDefault(); if (theCell != null) { value = theCell.InnerText; // If the cell represents an integer number, you are done. // For dates, this code returns the serialized value that // represents the date. The code handles strings and // Booleans individually. For shared strings, the code // looks up the corresponding value in the shared string // table. For Booleans, the code converts the value into // the words TRUE or FALSE. if (theCell.DataType != null) { switch (theCell.DataType.Value) { case CellValues.SharedString: // For shared strings, look up the value in the // shared strings table. var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>() .FirstOrDefault(); // If the shared string table is missing, something // is wrong. Return the index that is in // the cell. Otherwise, look up the correct text in // the table. if (stringTable != null) { value = stringTable.SharedStringTable .ElementAt(int.Parse(value)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } } } return value; }
public static void LoadExcelWorkbook(string fileName) { string Name = Settings.GrabList; //конструктор document = SpreadsheetDocument.Open(fileName, false, new OpenSettings {AutoSave = true}); //Забираем ссылку на workbookpart. wbPart = document.WorkbookPart; if (wbPart.Workbook.Descendants<Sheet>().Count(s => s.Name == Name) != 0) { Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().First(s => s.Name == Name); wsPart = (WorksheetPart) (wbPart.GetPartById(theSheet.Id)); } else { throw new NullReferenceException("Рабочая книга не найдена (необходимое имя - " + Name); } }
/// <summary> /// /// </summary> /// <remarks></remarks> /// <seealso cref=""/> /// <param name="workbookPart"></param> /// <param name="definedName"></param> /// <returns></returns> private static WorksheetPart getWorkSheetPart(WorkbookPart workbookPart, DefinedNameVal definedName) { //get worksheet based on defined name string relId = workbookPart.Workbook.Descendants<Sheet>() .Where(s => definedName.SheetName.Equals(s.Name)) .First() .Id; return (WorksheetPart)workbookPart.GetPartById(relId); }
//private static void AddTextToWorkSheet(string path, string sheetName, string text, string columnName, uint rowIndex) //{ // // Open the document for editing. // using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(path, true)) // { // // Get the SharedStringTablePart. If it does not exist, create a new one. // SharedStringTablePart shareStringPart; // if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0) // { // shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First(); // } // else // { // shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>(); // } // // Insert the text into the SharedStringTablePart. // int index = InsertSharedStringItem(spreadSheet.WorkbookPart, text); // // Insert a new worksheet. // WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart, sheetName); // // Insert cell A1 into the new worksheet. // Cell cell = InsertCellInWorksheet(worksheetPart, columnName, rowIndex); // // Set the value of cell A1. // cell.CellValue = new CellValue(index.ToString()); // cell.DataType = new EnumValue<CellValues>(CellValues.SharedString); // // Save the new worksheet. // worksheetPart.Worksheet.Save(); // } //} public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName) { Sheet foundSheet = workbookPart.Workbook.Sheets.Cast<Sheet>().FirstOrDefault(sheet => sheet.Name == sheetName); if (foundSheet == null) { // Add a new worksheet part to the workbook. var newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); var sheets = workbookPart.Workbook.GetFirstChild<Sheets>(); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); // Get a unique ID for the new sheet. uint sheetId = 1; if (sheets.Elements<Sheet>().Count() > 0) { sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1; } // Append the new worksheet and associate it with the workbook. var sheet = new Sheet { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append((IEnumerable<OpenXmlElement>)sheet); return newWorksheetPart; } return (WorksheetPart)workbookPart.GetPartById(foundSheet.Id); }