public IEnumerable <HelperCell> GetCells()
{
foreach (var cell in _row.Elements <Doc.Cell>())
{
yield return(new HelperCell(_parent, _sheetName, cell));
}
}
static void ReadRow(Sheet sheet, Excel.Row importRow, Dictionary <string, string> sharedStringTable, Dictionary <int, CellFormat> cellFormats)
{
Row row = sheet.Row((int)(uint)importRow.RowIndex - 1);
foreach (var importCell in importRow.Elements <Excel.Cell>())
{
ReadCell(row, importCell, sharedStringTable, cellFormats);
}
}
private static OpenXmlSpread.Cell GetCell(DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet,
string columnName, uint rowIndex)
{
OpenXmlSpread.Row row = GetRow(worksheet, rowIndex);
if (row == null)
{
return(null);
}
return(row.Elements <OpenXmlSpread.Cell>()
.Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0).First());
}
private MSOpenXML.Cell CreateCellIfNotExist(MSOpenXML.Worksheet worksheet, string cellName)
{
string columnName = GetColumnName(cellName);
uint rowIndex = GetRowIndex(cellName);
IEnumerable <MSOpenXML.Row> rows = worksheet.Descendants <MSOpenXML.Row>().Where(r => r.RowIndex.Value == rowIndex);
// If the Worksheet does not contain the specified row, create the specified row.
// Create the specified cell in that row, and insert the row into the Worksheet.
if (rows.Count() == 0)
{
MSOpenXML.Row row = new MSOpenXML.Row()
{
RowIndex = rowIndex
};
MSOpenXML.Cell cell = new MSOpenXML.Cell()
{
CellReference = cellName
};
row.Append(cell);
worksheet.Descendants <MSOpenXML.SheetData>().First().Append(row);
return(cell);
}
else
{
MSOpenXML.Row row = rows.First();
IEnumerable <MSOpenXML.Cell> cells = row.Elements <MSOpenXML.Cell>().Where(c => c.CellReference.Value == cellName);
// If the row does not contain the specified cell, create the specified cell.
if (cells.Count() == 0)
{
MSOpenXML.Cell cell = new MSOpenXML.Cell()
{
CellReference = cellName
};
row.Append(cell);
return(cell);
}
else
{
return(cells.First());
}
}
}
public string GenerateJsonTable(SheetFormat sheetFormat, String worksheetUri)
{
// open excel file
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(this.fileStream, false))
{
// get workbookpart
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
_sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ToArray();
_stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;
WorksheetPart worksheetPart = null;
foreach (Sheet worksheet in workbookPart.Workbook.Descendants <Sheet>())
{
//Get the current worksheetpart and see if it is the correct one
WorksheetPart tmp = (WorksheetPart)workbookPart.GetPartById(worksheet.Id);
if (tmp.Uri.ToString() == worksheetUri)
{
//Found the correct WorksheetPart
worksheetPart = tmp;
}
}
using (OpenXmlReader reader = OpenXmlReader.Create(worksheetPart))
{
int expectedRowIndex = 1;
while (reader.Read())
{
if (reader.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Row))
{
do
{
DocumentFormat.OpenXml.Spreadsheet.Row row = (DocumentFormat.OpenXml.Spreadsheet.Row)reader.LoadCurrentElement();
List <String> rowAsStringList = new List <string>();
//Since this library will ignore empty rows, check if we skipped some and add empty rows if necessary
//This will still ignore empty rows at the end of the file but those wouldn't have any influence on the indices of data & header anyway
while (row.RowIndex > expectedRowIndex)
{
List <String> dummyRow = new List <string>();
dummyRow.Add("");
table.Add(dummyRow);
expectedRowIndex++;
}
// create a new cell
Cell c = new Cell();
int expectedIndex = 0; //To check whether we skipped cells because they were empty
for (int i = 0; i < row.ChildElements.Count(); i++)
{
// get current cell at i
c = row.Elements <Cell>().ElementAt(i);
string value = "";
if (c != null)
{
//See if cells have been skipped (empty cells are not contained in the xml and therefore not contained in row.ChildElements)
//See: https://stackoverflow.com/a/3981249
// Gets the column index of the cell with data
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(c.CellReference));
if (expectedIndex < cellColumnIndex)
{
//We skipped one or more cells so add some blank data
do
{
rowAsStringList.Add(""); //Insert blank data
expectedIndex++;
}while (expectedIndex < cellColumnIndex);
}
//We now have the correct index and can grab the value of the cell
if (c.CellValue != null && !string.IsNullOrEmpty(c.CellValue.Text))
{
// if Value a text
if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.SharedString)
{
int sharedStringIndex = int.Parse(c.CellValue.Text, CultureInfo.InvariantCulture);
SharedStringItem sharedStringItem = _sharedStrings[sharedStringIndex];
value = sharedStringItem.InnerText;
}
//If cell contains boolean (doesn't always work for files saved with libre office)
else if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.Boolean)
{
if (c.InnerText == "1")
{
value = "true";
}
else
{
value = "false";
}
}
// not a text
else if (c.StyleIndex != null && c.StyleIndex.HasValue)
{
uint styleIndex = c.StyleIndex.Value;
CellFormat cellFormat = _stylesheet.CellFormats.ChildElements[(int)styleIndex] as CellFormat;
if (cellFormat != null && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.HasValue)
{
uint numberFormatId = cellFormat.NumberFormatId.Value;
NumberingFormat numberFormat = _stylesheet.NumberingFormats?.FirstOrDefault(numFormat => ((NumberingFormat)numFormat).NumberFormatId.Value == numberFormatId) as NumberingFormat;
//
if (numberFormat != null)
{
if (numberFormat != null && numberFormat.FormatCode != null && numberFormat.FormatCode.HasValue)
{
string formatCode = numberFormat.FormatCode.Value;
if ((formatCode.ToLower().Contains("d") && formatCode.ToLower().Contains("m")) ||
(formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("y")) ||
(formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("d")) ||
(formatCode.ToLower().Contains("h") && formatCode.ToLower().Contains("m")) ||
(formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("s"))
)
{
DateTime dateTime = DateTime.FromOADate(double.Parse(c.CellValue.Text, CultureInfo.InvariantCulture));
//value = dateTime.ToString(new CultureInfo("en-us"));
//get c# display pattern
DataTypeDisplayPattern dataTypeDisplayPattern = DataTypeDisplayPattern.GetByExcelPattern(formatCode);
value = dataTypeDisplayPattern != null?dateTime.ToString(dataTypeDisplayPattern.StringPattern) : dateTime.ToString(new CultureInfo("en-us"));
//Debug.WriteLine("----");
//Debug.WriteLine(formatCode);
}
}
} // numberformat not null end
} // (cellFormat != null && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.HasValue)
//It may happen that values are in a cell, but the associated information such as numberformat or style are missing.
// In this case, we decide to display the values, even if they are incorrect.
if (string.IsNullOrEmpty(value) && (!string.IsNullOrEmpty(c?.CellValue?.Text)))
{
value = c.CellValue.Text;
}
}
else
{
value = c.CellValue.Text;
}
rowAsStringList.Add(value);
}//end if cell value null
else
{
rowAsStringList.Add("");
}
}//end if cell null
expectedIndex++;
}//for children of row
//Check if there's a new max length for the length of a row
maxCellCount = Math.Max(maxCellCount, rowAsStringList.Count);
//Just read a row, so increase the expected index for the next one
expectedRowIndex++;
table.Add(rowAsStringList);
} while (reader.ReadNextSibling()); // Skip to the next row
break;
}
}
}
//Make sure each row has the same number of values in it
foreach (List <String> row in table)
{
while (row.Count < maxCellCount)
{
row.Add("");
}
}
//Convert the Lists to Arrays
List <String>[] rowArray = table.ToArray(); //The elements of the Array are the rows in form of String-lists
String[][] tableArray = new String[rowArray.Length][];
for (int i = 0; i < rowArray.Length; i++)
{
tableArray[i] = rowArray[i].ToArray();
}
return(JsonConvert.SerializeObject(tableArray));
}
}