// Adds child parts and generates content of the specified part.
public static void CreateTable(WorkbookPart workbookPart, WorksheetPart worksheetPart, string[] columns, int topLeftColumn, int topLeftRow, int width, int height)
{
List<WorksheetPart> worksheets = workbookPart.GetPartsOfType<WorksheetPart>().ToList();
uint maxTableId = worksheets.Select(ws => ws.TableDefinitionParts.ToList()).SelectMany(tableDefinitions => tableDefinitions).Aggregate<TableDefinitionPart, uint>(0, (current, tableDef) => Math.Max(tableDef.Table.Id, current));
uint tableId = maxTableId + 1;
var tables = new TableParts { Count = 1U };
worksheetPart.Worksheet.Append((IEnumerable<OpenXmlElement>)tables);
var newTableDefnPart = worksheetPart.AddNewPart<TableDefinitionPart>();
string relationshipId = worksheetPart.GetIdOfPart(newTableDefnPart);
string cellReference = string.Format("{0}{1}:{2}{3}", GetColumnIdentifier(topLeftColumn), topLeftRow, GetColumnIdentifier(topLeftColumn + width - 1), topLeftRow + height);
var table1 = new Table { Id = tableId, Name = "Table" + relationshipId, DisplayName = "Table" + relationshipId, Reference = cellReference, TotalsRowShown = false };
var autoFilter1 = new AutoFilter { Reference = cellReference };
var tableColumns1 = new TableColumns { Count = (uint)columns.Length };
for (int iColumn = 0; iColumn < columns.Length; iColumn++)
{
var tableColumn = new TableColumn { Id = (UInt32Value)(uint)iColumn + 1, Name = columns[iColumn] };
tableColumns1.Append((IEnumerable<OpenXmlElement>)tableColumn);
}
var tableStyleInfo1 = new TableStyleInfo { Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table1.Append((IEnumerable<OpenXmlElement>)autoFilter1);
table1.Append((IEnumerable<OpenXmlElement>)tableColumns1);
table1.Append((IEnumerable<OpenXmlElement>)tableStyleInfo1);
newTableDefnPart.Table = table1;
var table = new TablePart { Id = relationshipId };
tables.Append((IEnumerable<OpenXmlElement>)table);
//TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
//worksheetPart.Worksheet.Append(tableStyles1);
}
public ExcelHelper Open(string ExcelFile,bool IsEditable=false)
{
_expectSheets = new List<string>();
OpenSettings os = new OpenSettings();
this._excelFilePath = ExcelFile;
_doc = SpreadsheetDocument.Open(_excelFilePath, IsEditable);
_wbPart = _doc.WorkbookPart;
_shareStringPart = _wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
shareStringDic = new Dictionary<int, string>();
int j = 0;
foreach (var item in _shareStringPart.SharedStringTable.Elements<SharedStringItem>()) {
shareStringDic.Add(j, item.InnerText);
j++;
}
return _instance;
}
public static string GetCellValue(WorkbookPart wbPart, Cell theCell)
{
string value = theCell.InnerText;
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
var stringTable = wbPart.
GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
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;
}
protected string GetCellValue(WorkbookPart wbPart, Cell theCell)
{
if (theCell == null)
{
return null;
}
var value = theCell.InnerText;
if (theCell.DataType == null)
{
return value;
}
switch (theCell.DataType.Value)
{
case (CellValues.SharedString):
{
var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
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;
}
case (CellValues.Date):
{
if (!string.IsNullOrEmpty(value))
{
value = DateTime.FromOADate(Convert.ToDouble(value)).ToString(CultureInfo.CurrentCulture);
}
break;
}
}
return value;
}
public static string GetCellValue(WorkbookPart wbPart, Cell theCell)
{
string value = theCell.InnerText;
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
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;
case CellValues.Date:
var date = new DateTime();
var success = DateTime.TryParse(value, out date);
if (success)
{
value = date.ToString("MM-yyyy");
}
break;
}
}
return value;
}
// Given the main workbook part, and a text value, insert the text into
// the shared string table. Create the table if necessary. If the value
// already exists, return its index. If it doesn't exist, insert it and
// return its new index.
internal static int InsertSharedStringItem(WorkbookPart wbPart, string value)
{
int index = 0;
bool found = false;
var stringTablePart = wbPart
.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
// If the shared string table is missing, something's wrong.
// Just return the index that you found in the cell.
// Otherwise, look up the correct text in the table.
if (stringTablePart == null)
{
// Create it.
stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
}
var stringTable = stringTablePart.SharedStringTable;
if (stringTable == null)
{
stringTable = new SharedStringTable();
stringTablePart.SharedStringTable = stringTable;
}
// Iterate through all the items in the SharedStringTable.
// If the text already exists, return its index.
foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
{
if (item.InnerText == value)
{
found = true;
break;
}
index += 1;
}
if (!found)
{
stringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(value)));
stringTable.Save();
}
return index;
}
//
// Insert the string into the shared string table of the WorkBookPart
//
private int insertSharedStringItem(WorkbookPart wbPart, string Value)
{
//
// Look if the string already exists into the SharedStringTable, if it does return it
// else return a new one
//
int index = 0;
bool found = false;
//
// If the shared string table is missing, something's wrong.
// Just return the index that you found in the cell.
// Otherwise, look up the correct text in the table.
//
var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (stringTablePart == null)
{
// Create a new SharedString
stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
}
var stringTable = stringTablePart.SharedStringTable;
if (stringTable == null)
{
stringTable = new SharedStringTable();
}
//
// Iterate through all the items in the SharedStringTable.
// If the text already exists, return its index.
//
foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
{
if (item.InnerText == Value)
{
found = true;
break;
}
index++;
}
//
// if not found append a new SharedString to the table
//
if (!found)
{
stringTable.AppendChild(new SharedStringItem(new Text(Value)));
try
{
stringTable.Save();
}
catch (Exception) { }
}
return index;
}
private string GetCellValue(Worksheet worksheet, WorkbookPart wbPart, string targetCell)
{
uint targetRow = GetRowIndex(targetCell);
string result = null;
SharedStringTablePart sstPart = wbPart.GetPartsOfType<SharedStringTablePart>().First();
SharedStringTable ssTable = sstPart.SharedStringTable;
try
{
Row row = worksheet.Descendants<Row>().SingleOrDefault(r => r.RowIndex == targetRow);
//non ho la riga
if (row == null)
return result;
Cell cell = row.Descendants<Cell>().SingleOrDefault(c => targetCell.Equals(c.CellReference));
if (cell == null)
return result;
if (cell.DataType != null && cell.DataType == CellValues.SharedString)
{
result = ssTable.ChildElements[Convert.ToInt32(cell.CellValue.Text)].InnerText;
}
else
{
if (cell.CellValue != null)
{
result = cell.CellValue.Text;
}
}
}
catch (Exception)
{
}
return result;
}
/// <summary>
/// restituisce il contenuto di una cella Excel
/// </summary>
/// <param name="wsPart">parametro del foglio excel</param>
/// <param name="wbPart">parametro del foglio excel</param>
/// <param name="i">riga</param>
/// <param name="j">colonna</param>
/// <returns></returns>
public string GetCellValueNew(WorksheetPart wsPart, WorkbookPart wbPart, int i, int j)
{
string value = null;
String addressName = Convert.ToString((char)('A' + j));
addressName = (addressName + (i + 1));
Cell theCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == addressName).FirstOrDefault();
if (theCell != null)
{
string test = null;
value = theCell.InnerText;
if (theCell.DataType != null)
{
test = theCell.DataType.Value.ToString();
switch (theCell.DataType.Value)
{
case CellValues.String:
value = theCell.GetFirstChild<CellValue>()?.Text;
break;
case CellValues.SharedString:
var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (stringTable != null)
{
value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
}
break;
}
}
else {
value = theCell.GetFirstChild<CellValue>()?.Text;
}
}
return value;
}
/// <summary>
/// Заполнение ячейки данными
/// </summary>
/// <param name="wbPart"></param>
/// <param name="value"></param>
/// <returns></returns>
private int InsertSharedStringItem(WorkbookPart wbPart, string value)
{
var index = 0;
var found = false;
var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault() ??
wbPart.AddNewPart<SharedStringTablePart>();
var stringTable = stringTablePart.SharedStringTable ?? new SharedStringTable();
foreach (var item in stringTable.Elements<SharedStringItem>())
{
if (item.InnerText == value)
{
found = true;
break;
}
index += 1;
}
if (found) return index;
stringTable.AppendChild(new SharedStringItem(new Text(value)));
stringTable.Save();
return index;
}
/// <summary>
/// Function is used to read row values for the provided excel sheet
/// </summary>
/// <param name="row">Row from the excel sheet</param>
/// <param name="workbookPart">WorkbookPart object</param>
/// <returns>List of values in row</returns>
private static Collection<string> ReadRow(Row row, WorkbookPart workbookPart)
{
string value = string.Empty;
int rowSize = row.Elements().Count(), emptyValue = 0;
Collection<string> rowValue = new Collection<string>();
try
{
foreach (Cell cell in row.Elements<Cell>())
{
if (null != cell)
{
CellValue cellValue = cell.Descendants<CellValue>().FirstOrDefault();
if (null != cellValue)
{
value = cellValue.Text;
if (null != cell.DataType)
{
switch (cell.DataType.Value)
{
case CellValues.SharedString:
var stringTable = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (null != stringTable)
{
value = stringTable.SharedStringTable.
ElementAt(int.Parse(value, CultureInfo.InvariantCulture)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
}
}
else
{
value = string.Empty;
}
value = value.Trim();
if (string.IsNullOrWhiteSpace(value))
{
emptyValue++;
}
if (string.Equals("NA", value, StringComparison.OrdinalIgnoreCase))
{
value = string.Empty;
}
rowValue.Add(value);
}
}
}
catch (Exception exception)
{
ErrorLogger.LogErrorToTextFile(errorFilePath, "Exception occurred while reading row value: " + exception.Message);
}
if (emptyValue == rowSize)
{
return null;
}
return rowValue;
}
private string FindCellInnerValue(WorkbookPart wbPart, Cell theCell,bool reference=true)
{
if (theCell != null)
{
var value = theCell.InnerText;
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (stringTable != null)
{
value = stringTable.SharedStringTable.
ElementAt(int.Parse(value)).InnerText;
if (reference)
return theCell.CellReference;
else
return value;
}
break;
}
}
if(theCell.CellValue!=null)
return theCell.CellValue.InnerText;
}
return null;
}
static string GetCellValue(WorkbookPart wbPart, Cell theCell)
{
string value = theCell.InnerText;
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
var stringTable = wbPart.
GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (stringTable != null)
{
//var allNoAttributesValue = stringTable.SharedStringTable.
// ElementAt(int.Parse(value)).Where(m => m.HasAttributes == false).Select(m => m.InnerText).Aggregate((m1, m2) => m1 + m2);
//value = allNoAttributesValue;
//var a = stringTable.SharedStringTable.
// ElementAt(int.Parse(value));
//var node = stringTable.SharedStringTable.
// ElementAt(int.Parse(value)).First();
//var nodeA = stringTable.SharedStringTable.
// ElementAt(int.Parse(value));
var allAllLocalNameNode = stringTable.SharedStringTable.
ElementAt(int.Parse(value)).Where(m => allowLocalName.Contains(m.LocalName)).ToList();
if (allAllLocalNameNode.Any())
{
value = allAllLocalNameNode.Select(m => m.InnerText).Aggregate((m1, m2) => m1 + m2);
}
else
{
value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
}
//var a = System.Text.RegularExpressions.Regex.Replace(fieldFirstChild.OuterXml, "<[^>]*>", "");
//!!Old first field
//var fieldFirstChild = stringTable.SharedStringTable.
// ElementAt(int.Parse(value)).FirstChild;
//if (fieldFirstChild != null)
//{
// value = fieldFirstChild.InnerText;
//}
//else
//{
// value = stringTable.SharedStringTable.
// ElementAt(int.Parse(value)).InnerText;
//}
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
case CellValues.String:
if (theCell.CellValue != null)
value = theCell.CellValue.InnerText;
break;
}
}
return value;
}
private string GetCellValue(WorkbookPart wbPart, Cell cell)
{
if (cell.DataType != null && cell.DataType == CellValues.SharedString && !string.IsNullOrWhiteSpace(cell.CellValue.InnerText))
{
var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (stringTable != null)
{
return stringTable.SharedStringTable.ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText;
}
else
{
return cell.CellValue.InnerText;
}
}
else
{
return cell?.CellValue?.InnerText;
}
}
//Temporal hide
/*
public List<SCNNiveldeGestion> GetValuesIndexNiveldeGestion(Stream ExcelStream, string sheetName, string columnName, List<string> GestionListValues, List<string> EstatalValues)
{
List<SCNNiveldeGestion> ListNiveldeGestion = new List<SCNNiveldeGestion>();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(ExcelStream, false))
{
WorkbookPart wbPart = document.WorkbookPart;
Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
Where(s => s.Name == sheetName).FirstOrDefault();
if (theSheet == null)
{
throw new ArgumentException("sheetName");
}
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
IEnumerable<Cell> cells = wsPart.Worksheet.Descendants<Cell>().Where(c => string.Compare(GetColumnName(c.CellReference.Value),
columnName, true) == 0).OrderBy(R => R.CellReference.Value);
foreach (Cell currentcell in cells)
{
string cValue = GetCleanValueCell(currentcell, ref wbPart);
if (!string.IsNullOrEmpty(cValue))
{
if (GestionListValues.IndexOf(cValue) != -1)
{
ListNiveldeGestion.Add(new SCNNiveldeGestion
{
NiveldeGestionIndex = cValue,
Position = GetRowIndex(currentcell.CellReference.Value)
});
}
else
{
if (EstatalValues.IndexOf(cValue) != -1)
{
ListNiveldeGestion.Add(new SCNNiveldeGestion
{
NiveldeGestionIndex = "Estatal",
Position = GetRowIndex(currentcell.CellReference.Value)
});
}
else
{
if (!string.Equals(cValue, "Programa de Cumplimiento Grupo Modelo") && !string.Equals(cValue, "Indicar el órgano que emite el ordenamiento (sin abreviaciones)"))
{
ListNiveldeGestion.Add(new SCNNiveldeGestion
{
NiveldeGestionIndex = cValue,
Position = "-1"
});
}
}
}
//ValuesColumn.Append(" Value: " + GetValueCell(ref wbPart, ref wsPart, currentcell.CellReference.Value));
}
}
}
return ListNiveldeGestion;
}
*/
//Final Temporal Hide
private string GetCleanValueCell(Cell theCell, ref WorkbookPart wbPart)
{
string value = null;
if (theCell != null)
{
value = theCell.InnerText;
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's wrong.
// Just return the index that you found 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;
}
static SharedStringTable GetSharedStringTable(WorkbookPart wbPart)
{
var tablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
return tablePart != null ? tablePart.SharedStringTable : null;
}
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;
}
// Get the value of a cell, given a file name, sheet name, and address name.
string XLGetCellValue(Excel.Cell c, WorkbookPart wbPart)
{
string value = null;
// If the cell does not exist, return an empty string.
if (c != null)
{
value = c.InnerText;
// If the cell represents an integer number, you are finished.
// For dates, this code returns the serialized value that
// represents the date. The code handles strings and Boolean values
// individually. For shared strings, the code looks up the corresponding
// value in the shared string table. For Boolean values, the code converts
// the value into the words TRUE or FALSE.
if (c.DataType != null)
{
switch (c.DataType.Value)
{
case Excel.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.
// Just return the index that you found 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 Excel.CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
}
}
return value;
}
/// <summary>
/// 創建一個SharedStringTablePart(相當於各Sheet共用的存放字符串的容器)
/// </summary>
/// <param name="workbookPart"></param>
/// <returns></returns>
private static SharedStringTablePart CreateSharedStringTablePart(WorkbookPart workbookPart)
{
SharedStringTablePart shareStringPart = null;
if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
{
shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
}
return shareStringPart;
}
private static SharedStringTablePart GetSharedStringPart(WorkbookPart workbookPart)
{
return workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0 ? workbookPart.GetPartsOfType<SharedStringTablePart>().First() : workbookPart.AddNewPart<SharedStringTablePart>();
}
