public DataTable ReadToDataTable(string sheetName = null)
{
DataTable dt = new DataTable();
Sheet sheet = null;
IEnumerable <Sheet> sheets = workbookPart.Workbook.Descendants <Sheet>();
if (!string.IsNullOrEmpty(sheetName))
{
sheet = sheets.FirstOrDefault(x => x.Name == sheetName);
}
else
{
sheet = sheets.FirstOrDefault();
}
if (sheet == null)
{
//throw new ArgumentException("sheetName");
throw new InvalidOperationException("Erro na folha de dados");
}
dt.TableName = sheet.Name;
//load sheet
WorksheetPart sheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;
//load shard string
var shareStringPart = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
var shareStrings = shareStringPart.SharedStringTable.Elements <SharedStringItem>().Select(x => x.InnerText).ToList();
SheetData sheetData = sheetPart.Worksheet.Elements <SheetData>().FirstOrDefault();
foreach (Row row in sheetData)
{
if (row.RowIndex != 1)
{
BindRowData(row, shareStrings, ref dt);
}
else
{
BindColumnData(row, shareStrings, ref dt);
}
}
return(dt);
}
public XlsxColumnCopy(string editXlsxName, string refXlsxName)
{
refDoc = SpreadsheetDocument.Open(refXlsxName, false);
refWorkbookPart = refDoc.WorkbookPart;
refSharedStringTablePart = refWorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
refSheets = refWorkbookPart.Workbook.Sheets;
editDoc = SpreadsheetDocument.Open(editXlsxName, true);
editWorkbookPart = editDoc.WorkbookPart;
editSheets = editDoc.WorkbookPart.Workbook.Sheets;
editSharedStringTablePart = editWorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
strings = new Dictionary <string, int>();
foreach (var item in editSharedStringTablePart.SharedStringTable.Elements <SharedStringItem>())
{
strings.Add(item.InnerText, strings.Count);
}
}
public static string GetCellValue(SpreadsheetDocument document, Sheet theSheet, string addressName)
{
string value = null;
WorkbookPart wbPart = document.WorkbookPart;
var wsPart =
(WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Cell theCell = wsPart.Worksheet.Descendants <Cell>().FirstOrDefault(c => c.CellReference == addressName);
if (theCell != null)
{
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);
}
private void Initialize(SpreadsheetDocument doc)
{
WorkbookPart wbPart = doc.WorkbookPart;
Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild <Sheet>();
_workSheetPart = wbPart.GetPartById(sheet.Id) as WorksheetPart;
_stringTablePart = wbPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
this.RegisterColumns(_workSheetPart.Worksheet.Descendants <Row>().Single(p => p.RowIndex == 1));
Row sampleRow = _workSheetPart.Worksheet.Descendants <Row>().Single(p => p.RowIndex == 2);
foreach (Cell cell in sampleRow.Elements <Cell>())
{
string colRef = this.ParseColRefs(cell.CellReference.Value);
_colRefsToStyle.Add(colRef, cell.StyleIndex != null ? cell.StyleIndex.Value : 0);
}
}
private static string ExcelImportGetCellValue(WorkbookPart wbPart, Cell cell)
{
var strintTableValue = wbPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
var cellValue = cell.InnerText;
if (!string.IsNullOrEmpty(cellValue))
{
//Kontrolle nötig, Beabsichtigter Effekt?
try
{
cellValue = strintTableValue.SharedStringTable.ElementAt(int.Parse(cellValue, Helper.CultureInfo)).InnerText;
}
catch (Exception)
{
}
}
return(cellValue);
}
// 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);
}
public string GetIndexBySearch(string search)
{
WorkbookPart workbookPart = ImportExcel("templates\\template_excel.xlsx");
var sheets = workbookPart.Workbook.Descendants <Sheet>();
Sheet sheet = sheets.Where(x => x.Name.Value == "Universal").FirstOrDefault();
string index = string.Empty;
if (sheet != null)
{
var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
var rows = worksheetPart.Worksheet.Descendants <Row>().ToList();
// Remove the header row
rows.RemoveAt(0);
foreach (var row in rows)
{
var cellss = row.Elements <Cell>().ToList();
foreach (var cell in cellss)
{
if (!String.IsNullOrEmpty(cell.InnerText) && int.TryParse(cell.InnerText, out int n))
{
var value = cell.InnerText;
var stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
bool isFound = value.Trim().ToLower().Contains(search.Trim().ToLower());
if (isFound)
{
index = $"{row.RowIndex},{GetColumnIndex(cell.CellReference)}";
return(index);
}
}
}
}
}
if (String.IsNullOrEmpty(index))
{
throw new System.InvalidOperationException(String.Format("Value '{0}' is not present in the Excel file.", search));
}
return(index);
}
string GetCellValue(WorksheetPart worksheetPart, WorkbookPart workbookPart, string cellAddress)
{
string cellValue = null;
// Gebruik het meegegeven adres om de cel te vinden.
Cell cell = worksheetPart.Worksheet.Descendants <Cell>().FirstOrDefault(c => c.CellReference == cellAddress);
// Als de cell leeg is, geef je een lege string terug.
if (cell != null)
{
// Inlezen van de value in de variabele.
// Als het een getal of een datum is ben je klaar.
cellValue = cell.InnerText;
// Als het niet een getal of een datum is, doe je de volgende stappen.
// DataType == null -> numerische en datum types
// DataType != null -> CellValues.SharedString voor strings, CellValues.Boolean voor booleans
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.SharedString:
var stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
if (stringTable != null)
{
cellValue = stringTable.SharedStringTable.ElementAt(int.Parse(cellValue)).InnerText;
}
break;
case CellValues.Boolean:
switch (cellValue)
{
case "0":
cellValue = "FALSE";
break;
default:
cellValue = "TRUE";
break;
}
break;
}
}
}
return(cellValue);
}
public string GetCellValue(WorkbookPart wb, Cell c)
{
if (c == null)
{
return("");
}
string value = c.InnerText;
if (c.DataType != null)
{
switch (c.DataType.Value)
{
case CellValues.SharedString:
var stringTable = wb.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;
default:
value = "";
break;
}
}
return(value);
}
public ExcelReportBuilder AddSheetDataTBD(
IReportRenderer reportRenderer,
IEnumerable <IPropertyContainer> reportRows,
bool fillCellReferences = true)
{
var sheetMetadata = reportRenderer.GetMetadata <ExcelSheetMetadata>() ?? _defaultSheetMetadata;
// FIND ALL PARTS!!!
WorkbookPart workbookPart = _documentContext.Document.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.GetPartsOfType <WorksheetPart>().FirstOrDefault();
Sheets sheets = workbookPart.Workbook.Sheets;
var sheetContext = new SheetContext(_documentContext, worksheetPart, sheetMetadata, reportRenderer);
sheetContext.SheetData = new SheetData();
return(this);
}
public static void ReadExcel(string path, int ignoreRows = 0)
{
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
{
WorkbookPart workbookPart = doc.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
SharedStringTable sst = sstpart.SharedStringTable;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
Worksheet sheet = worksheetPart.Worksheet;
var rows = sheet.Descendants <Row>();
if (ignoreRows > 0)
{
rows = rows.Skip(ignoreRows);
}
foreach (Row row in rows)
{
var cells = row.Elements <Cell>();
foreach (Cell c in cells)
{
if ((c.DataType != null) && (c.DataType == CellValues.SharedString))
{
int ssid = int.Parse(c.CellValue.Text);
string str = sst.ChildElements[ssid].InnerText;
Console.WriteLine("Shared string {0}: {1}", ssid, str);
}
else if (c.CellValue != null)
{
Console.WriteLine("Cell contents: {0}", c.CellValue.Text);
}
}
Console.WriteLine();
}
}
}
}
/// <summary>
/// Lit les cellules du fichier Xlsx passé en paramètre
/// </summary>
/// <param name="filePath">Chemin du fichier Excel (*.xlsx) à lire</param>
/// <param name="takes">Nombre de lignes à prendre en compte</param>
/// <param name="skip">Nombre de lignes à éviter</param>
/// <returns></returns>
public static int CountRows(string filePath)
{
Dictionary <int, List <string> > data = new Dictionary <int, List <string> >();
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
{
WorkbookPart workbookPart = doc.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
SharedStringTable sst = sstpart.SharedStringTable;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
Worksheet sheet = worksheetPart.Worksheet;
return(sheet.Descendants <Row>().Count());
}
}
}
private async Task <string> GetCellValue(Cell cell, WorkbookPart workbookPart)
{
if (cell == null)
{
return(null);
}
var value = cell.CellFormula != null ? cell.CellValue.InnerText : cell.InnerText.Trim();
if (cell.DataType == null)
{
return(value);
}
switch (cell.DataType.Value)
{
case CellValues.SharedString:
var stringTable = workbookPart.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);
}
private static string GetCellValue(WorkbookPart workbookPart, Cell cell)
{
string value = string.Empty;
if (cell != null)
{
value = cell.InnerText;
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.SharedString:
var stringTable =
workbookPart.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);
}
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);
}
private string GetCellValue(WorkbookPart workbookPart, Sheet sheet, string addressName)
{
string value = null;
WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
Cell theCell = worksheetPart.Worksheet.Descendants <Cell>().Where(c => c.CellReference == addressName).FirstOrDefault();
if (theCell != null)
{
value = theCell.InnerText;
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
var stringSharedTable = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
if (stringSharedTable != null)
{
value = stringSharedTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
case "1":
value = "TRUE";
break;
}
break;
}
}
}
return(value);
}
private static SharedStringTablePart GetSharedStringPart(WorkbookPart workbookPart)
{
SharedStringTablePart shareStringPart;
if (workbookPart.GetPartsCountOfType <SharedStringTablePart>() > 0)
{
shareStringPart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
return(null);
}
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
return(shareStringPart);
}
private static string GetSharedStringValue(WorkbookPart workbookPart, CellType cell)
{
var stringTablePart = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
SharedStringTable stringTable = null;
if (stringTablePart != null)
{
stringTable = stringTablePart.SharedStringTable;
}
if (stringTable == null)
{
return(string.Empty);
}
var sharedStringId = int.Parse(cell.CellValue.Text);
var sharedString = stringTable.ChildElements[sharedStringId].InnerText;
return(sharedString);
}
/// <summary> Gets cell value. </summary>
///
/// <param name="cell"> The cell. </param>
///
/// <returns> The cell value. </returns>
private string GetCellValue(Cell cell)
{
string resultValue = string.Empty;
if (cell != null)
{
resultValue = cell.InnerText;
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.SharedString:
// For shared strings, look up the value in the
// shared strings table.
var stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
if (stringTable != null)
{
resultValue = stringTable.SharedStringTable.ElementAt(int.Parse(resultValue)).InnerText;
}
break;
case CellValues.Boolean:
switch (resultValue)
{
case "0":
resultValue = "FALSE";
break;
default:
resultValue = "TRUE";
break;
}
break;
}
}
}
return(resultValue);
}
internal static string TryGetStringFromCell(this WorkbookPart workbookPart, Cell cell)
{
if (cell.DataType == null || !cell.DataType.HasValue)
{
if (cell.CellValue == null)
{
return(null);
}
else
{
return(cell.CellValue.Text);
}
}
if (cell.DataType.Value == CellValues.String)
{
return(cell.CellValue.Text);
}
else if (cell.DataType.Value == CellValues.InlineString)
{
return(cell.InnerText);
}
else if (cell.DataType.Value == CellValues.SharedString)
{
// following based on https://msdn.microsoft.com/en-us/library/office/hh298534.aspx
// For shared strings, look up the value in the
// shared strings table.
var stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
if (stringTable == null)
{
Exception err = new InvalidOperationException(message: "Unable to get shared string table from Excel document");
throw err;
}
else
{
return(stringTable.SharedStringTable.ElementAt(int.Parse(cell.InnerText)).InnerText);
}
}
return(null);
}
/// <summary>
/// Запись строк в ячейки.
/// </summary>
/// <param name="listImportData">Список импортируемых записей.</param>
public void InsertText(List <ArrayList> listImportData, int parametersListCount)
{
uint row = 2;
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(DocPath, true))
{
SharedStringTablePart shareStringPart;
if (doc.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = doc.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = doc.WorkbookPart.AddNewPart <SharedStringTablePart>();
}
WorkbookPart workbookPart = doc.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
SharedStringTable sst = sstpart.SharedStringTable;
WorksheetPart worksheetPart = GetWorksheetPartByName(doc, WorksheetName);
Worksheet sheet = worksheetPart.Worksheet;
foreach (var importItems in listImportData)
{
int index = InsertSharedStringItem(Convert.ToString(importItems[0]), shareStringPart);
Cell cell = InsertCellInWorksheet(Convert.ToString(importItems[1]), Convert.ToUInt32(importItems[2]), worksheetPart);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
if (Convert.ToInt32(importItems[2]) != 1 && (row - 1) % 3 == 0)
{
var percent = (double)((row - 2) / 3) / (double)parametersListCount * 100.00;
logger.Info($"\rОбработано {(row - 2) / 3} строк из {parametersListCount} ({percent:F2}%)");
}
row++;
}
worksheetPart.Worksheet.Save();
}
}
private string GetCellValue(WorkbookPart workbookPart, Cell cell)
{
string value = string.Empty;
// If the cell does not exist, return an empty string.
if (cell != null)
{
CellValue cellValue = cell.CellValue;
value = (cellValue == null) ? cell.InnerText : cellValue.Text;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
var stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
if (stringTable != null)
{
value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
}
}
}
return(value);
}
//_________________________________________________________________________________________________________
//_________________________________________________________________________________________________________
public string Cell(string addressName)
{
string value = null;
// Use its Worksheet property to get a reference to the cell whose address matches the address you supplied.
Cell theCell = mWorksheet.Worksheet.Descendants <Cell>().Where(c => c.CellReference == addressName).FirstOrDefault();
// If the cell does not exist, return an empty string.
if (theCell == null)
{
return(value);
}
value = theCell.CellValue == null ? "" : theCell.CellValue.Text; //.CellFormula.Text;//.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)
{
return(value);
}
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
// For shared strings, look up the value in the shared strings table.
var stringTable = mWorkbook.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:
value = (value == "0") ? "FALSE" : "TRUE";
break;
}
return(value);
}
public static string GetExcelValueById(uint rowIndex, string columnName, WorkbookPart workbookPart, uint i)//由坐标取值
{
IEnumerable <Sheet> sheets = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.SheetId == i);
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheets.First().Id);
Worksheet worksheet = worksheetPart.Worksheet;
SheetData data = worksheetPart.Worksheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = worksheetPart.Worksheet.Descendants <Row>().Where(r => r.RowIndex == rowIndex);
List <string> cellText = new List <string>();
foreach (Row row in rows)
{
foreach (Cell cell in row)
{
if (cell != null && cell.CellReference != null)
{
if (cell.CellReference.Value == columnName + rowIndex)
{
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
SharedStringTablePart shareStringPart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
SharedStringItem[] items = shareStringPart.SharedStringTable.Elements <SharedStringItem>().ToArray();
cellText.Add(items[int.Parse(cell.CellValue.Text)].InnerText);
}
else
{
cellText.Add(cell.InnerText);
}
}
}
}
}
if (cellText == null || cellText.Count == 0)
{
return(null);
}
else
{
return(cellText.First());
}
}
public static string GetCellValue(WorkbookPart workbookPart, string sheetName, string addressName)
{
WorksheetPart worksheetPart = workbookPart.GetPartById(workbookPart.Workbook.Descendants <Sheet>().Where(e => e.Name.ToString().ToUpper().Equals(sheetName.ToUpper())).First().Id.Value) as WorksheetPart;
Cell theCell = worksheetPart.Worksheet.Descendants <Cell>().Where(c => c.CellReference == addressName).FirstOrDefault();
string value = "";
if (theCell != null && theCell.CellValue != null)
{
value = theCell.CellValue.Text;
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
var stringTable = workbookPart.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);
}
public static ArrayList Parsexcel(string fileName = @"D:\C# project\example.xlsx")
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
{
WorkbookPart workbookPart = doc.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
SharedStringTable sst = sstpart.SharedStringTable;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
Worksheet sheet = worksheetPart.Worksheet;
var rows = sheet.Descendants <Row>();
ArrayList myAL = new ArrayList();
// Or... via each row
foreach (Row row in rows)
{
ArrayList mydata = new ArrayList();
foreach (Cell c in row.Elements <Cell>())
{
if ((c.DataType != null) && (c.DataType == CellValues.SharedString))
{
int ssid = int.Parse(c.CellValue.Text);
string str = sst.ChildElements[ssid].InnerText;
mydata.Add(str);
}
else if (c.CellValue != null)
{
mydata.Add(c.CellValue.Text);
}
}
myAL.Add(mydata);
}
return(myAL);
}
}
}
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 CellString(Cell theCell, WorkbookPart wbPart)
{
string value = null;
if (theCell.InnerText.Length > 0)
{
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);
}
private int InsertSharedStringItem(WorkbookPart wbPart, string value)
{
try
{
int index = 0;
bool found = false;
var stringTablePart = wbPart
.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
if (stringTablePart == null)
{
stringTablePart = wbPart.AddNewPart <SharedStringTablePart>();
}
var stringTable = stringTablePart.SharedStringTable;
if (stringTable == null)
{
stringTable = new SharedStringTable();
}
foreach (SharedStringItem item in stringTable.Elements <SharedStringItem>())
{
if (item.InnerText == value)
{
found = true;
break;
}
index += 1;
}
if (!found)
{
stringTable.AppendChild(new SharedStringItem(new Text(value)));
stringTable.Save();
}
return(index);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Creates a SpreadSheetDocument from the specified
/// template
/// </summary>
/// <param name="template">Template url</param>
/// <returns>SpreadSheetDocument reference</returns>
public static SpreadsheetDocument ExcelDocument(string template = null)
{
template ??= $@"{AppDomain.CurrentDomain.BaseDirectory}Extensions\Excel\Templates\Report.xlsx";
if (!template.IsFilePath(out Uri path))
{
return(null);
}
//Initialize via report template
SpreadsheetDocument document = SpreadsheetDocument.CreateFromTemplate(template);
//Compress using maximum value possible
document.CompressionOption = CompressionOption.Maximum;
//Bond the workbook to the document or else create a new workspace
WorkbookPart book = document.WorkbookPart ?? document.AddWorkbookPart();
//Initialize workspace
WorksheetPart workSpace = document.WorkbookPart.WorksheetParts.FirstOrDefault() ?? document.WorkbookPart.AddNewPart <WorksheetPart>();
//Initialize worksheet
if (workSpace.Worksheet == null)
{
workSpace.Worksheet = new Worksheet();
}
//Load default style sheet
document.LoadDefaultStyleSheet();
//Share string table part
StringPart = book.GetPartsOfType <SharedStringTablePart>()
.FirstOrDefault();
TableStrings = StringPart.SharedStringTable
.Select(s => s.InnerText)
.ToList();
//Add Document properties
document.SetProperties(@"Reporte");
//Add extended Document properties
document.SetExtendedProperties(null, null);
//Save all changes
document.Save();
return(document);
}
