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