private void RowProcess(Row row, StringBuilder sb, SharedStringTable sharedStringTable)
{
sb.Append("<row>");
foreach (Cell cell in row.Elements <Cell>())
{
string cellValue = string.Empty;
sb.Append("<cell>");
if (cell.CellFormula != null)
{
cellValue = cell.CellValue.InnerText;
sb.Append(cellValue);
sb.Append("</cell>");
continue;
}
cellValue = cell.InnerText;
if (cell.DataType != null && cell.DataType == CellValues.SharedString)
{
sb.Append(sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText);
}
else
{
sb.Append(cellValue);
}
sb.Append("</cell>");
}
sb.Append("</row>");
}
static void ReadXLSX(string fileName)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
SharedStringTable sharedStringTable = workbookPart.SharedStringTablePart.SharedStringTable;
foreach (Row row in sheetData.Elements <Row>())
{
foreach (Cell cell in row.Elements <Cell>())
{
if (cell.DataType != null && cell.DataType == CellValues.SharedString)
{
Console.Write(sharedStringTable.ElementAt(int.Parse(cell.InnerText)).InnerText);
}
else if (cell.CellValue != null)
{
Console.Write(cell.CellValue.Text);
}
Console.Write("\t");
}
Console.WriteLine();
}
}
}
/// <summary>
/// 获取单元格值
/// </summary>
/// <param name="cell"></param>
/// <param name="sharedStringTable"></param>
/// <returns></returns>
static string GetCellVal(Cell cell, SharedStringTable sharedStringTable)
{
var val = cell.InnerText;
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
//从共享表中获取值
case CellValues.SharedString:
if (sharedStringTable != null)
{
val = sharedStringTable
.ElementAt(int.Parse(val))
.InnerText;
}
break;
default:
val = string.Empty;
break;
}
}
return(val);
}
private string GetValueAsString(Cell cell, SharedStringTable strings)
{
var cv = cell?.CellValue?.Text;
var dataType = cell.DataType?.Value;
if (cv == null || dataType.HasValue == false)
{
return(cv);
}
switch (dataType.Value)
{
case CellValues.Boolean:
if (cv == "0")
{
return("False");
}
else
{
return("True");
}
case CellValues.SharedString:
return(strings.ElementAt(int.Parse(cv)).InnerText);
default:
return(cv);
}
}
protected static object ReadCell(Cell cell, SharedStringTable sharedStringTable)
{
//Make sure that the Excel has a SharedStringTable, the Cell has a DataType and is a String
if (cell.DataType is not null && sharedStringTable is not null && cell.DataType == CellValues.SharedString)
{
var cellValue = cell.InnerText;
//Return String
return(sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText);
}
private static string GetCellValue(Cell cell, SharedStringTable ssTable)
{
if (int.TryParse(cell.InnerText, out int stringTableIndex))
{
return(ssTable.ElementAt(stringTableIndex).InnerText);
}
else
{
return(null);
}
}
/// <summary>
/// Gets the header (first row) of the specified sheet
/// </summary>
/// <param name="worksheet">The sheet to use</param>
/// <returns>A list of cell values for the first row of the sheet</returns>
public IList <string> GetHeader(Worksheet worksheet)
{
try
{
List <string> result = new List <string>();
SharedStringTable sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
WorksheetPart worksheetData = (WorksheetPart)document.WorkbookPart.GetPartById(worksheet.ID);
using (OpenXmlReader reader = OpenXmlReader.Create(worksheetData))
{
// Read until a Row object is found.
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
if (reader.ReadFirstChild())
{
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
result.Add(sharedStrings.ElementAt(int.Parse(c.CellValue.Text)).InnerText);
}
else
{
result.Add(c.CellValue.Text);
}
}
} while (reader.ReadNextSibling());
return(result);
}
// If logic reaches here, then this row had no cells.
// Skip to the next row and check.
Logger.Warn("Found a row with no cells.");
}
}
}
throw new SpreadsheetReaderException("No header found for worksheet.");
}
catch (Exception ex)
{
Logger.Fatal(ex, "An error occurred while getting header.");
throw ex;
}
}
private string GetData(Cell cell, SharedStringTable sharedStringTable)
{
string data = null;
if (cell.DataType != null)
{
if (cell.DataType == CellValues.SharedString)
{
data = sharedStringTable.ElementAt(Int32.Parse(cell.InnerText)).InnerText;
}
else
{
data = cell.InnerText;
}
}
return(data);
}
private static string GetCellValue(Cell cell, SharedStringTable sharedStringTable, string cellValue)
{
string returnString = null;
if (cell.DataType != null && cell.DataType == CellValues.SharedString)
{
returnString = sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText;
//Console.WriteLine("cell val: " + returnString);
}
else
{
returnString = cellValue;
//Console.WriteLine("cell val: " + cellValue);
}
return(returnString);
}
public string GetCellValue(Cell cell)
{
if (cell != null && cell.CellValue != null)
{
if (cell.DataType != null && cell.DataType.Value.Equals(CellValues.SharedString))
{
int strIndex = int.Parse(cell.CellValue.Text);
return(_stringTable.ElementAt(strIndex).InnerText);
}
else
{
return(cell.CellValue.Text);
}
}
return(string.Empty);
}
/// <summary>
/// Lit et affiche le contenu d'un fichier excel ligne
/// par ligne et cellule par cellule.
/// </summary>
///
/// <param name="pathToFile">
/// Le chemin (nom et extension compris) du fichier à lire
/// </param>
private static void ReadFileByRowsAndCells(String pathToFile)
{
Console.WriteLine("Fichier : " + pathToFile);
// Ouverture du document
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(pathToFile, false))
{
// Parcours de la hiérarchie du document jusqu'à récupération du tableau
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
// Récupération du tableau nommé "Feuil1"
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where(x => x.Name == "Feuil1").FirstOrDefault <Sheet>();
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
// Récupération de la liste des string liées
SharedStringTable listeStringElement = workbookPart.SharedStringTablePart.SharedStringTable;
// Récupération de la liste des cellules
IEnumerable <Row> listRow = worksheetPart.Worksheet.Descendants <Row>().Where(x => x.RowIndex != null);
foreach (Row ligne in listRow)
{
IEnumerable <Cell> listCell = ligne.Descendants <Cell>();
foreach (Cell c in listCell)
{
// Suppression des référencées mais vides
if (c.CellValue == null)
{
continue;
}
// Test si la cellule est de type string
if (c.DataType == null || c.DataType.InnerText != "s")
{ // Si non on affiche directement son contenu
Console.Write(c.CellValue.InnerText + " ");
}
else
{ // Si oui on affiche la string qui y est associée dans la liste des string (via l'id dans la liste)
Console.Write(listeStringElement.ElementAt(Int32.Parse(c.CellValue.InnerText)).InnerText + " ");
}
}
Console.WriteLine();
}
spreadsheetDocument.Close();
}
}
private string getCellvalue(Cell cell, SharedStringTable sharedStringTable)
{
string cellValue = string.Empty;
if (cell.CellFormula != null)
{
cellValue = cell.CellValue.InnerText;
}
else
{
cellValue = cell.InnerText;
if (cell.DataType != null && cell.DataType == CellValues.SharedString)
{
cellValue = sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText;
}
}
return(cellValue);
}
/// <summary>
/// Remplace String dans une cellule par un entier
/// </summary>
///
/// <param name="pathToFile">
/// Le chemin (nom et extension compris) du fichier
/// </param>
/// <param name="sheetName">
/// Le nom du tableau
/// </param>
/// <param name="colName">
/// Le nom de la colonne dans laquelle se trouve la cellule
/// </param>
/// <param name="rowIndex">
/// Le numéro de la ligne dans laquelle se trouve la cellule
/// </param>
/// <param name="newVal">
/// La nouvelle valeur a affecter
/// </param>
public static bool ReplaceAStringValueByAnInt(string pathToFile, string sheetName, string colName,
int rowIndex, int newVal)
{
// Ouverture du document
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(pathToFile, true))
{
// Récupération du tableau nommé "Feuil1"
Sheet sheet = doc.WorkbookPart.Workbook.Descendants <Sheet>()
.Where(x => x.Name == sheetName).FirstOrDefault <Sheet>();
if (sheet == null)
{
return(false);
}
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheet.Id);
if (worksheetPart == null)
{
return(false);
}
// Récupération de la liste des string liées
SharedStringTable listeString = doc.WorkbookPart.SharedStringTablePart.SharedStringTable;
// Récupération de la liste des cellules
IEnumerable <Cell> listCell = worksheetPart.Worksheet.Descendants <Cell>().Where(x => x.CellReference != null);
/* Récupération de la cellule */
Cell cellToModify = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex);
if (cellToModify == null)
{
return(false);
}
int indexOfString = Int32.Parse(cellToModify.CellValue.InnerText);
listeString.ElementAt(indexOfString).Remove();
cellToModify.DataType = null;
cellToModify.CellValue = new CellValue()
{
Text = newVal.ToString()
};
worksheetPart.Worksheet.Save();
doc.Close();
return(true);
}
}
//读取指定行和列的内容
public string Read(int rowIndex, int colIndex)
{
Row row = getRow(rowIndex);
Cell cell = getCell(row, colIndex);
string value = string.Empty;
if (cell.CellValue != null && cell.CellValue.InnerText != null)
{
value = cell.CellValue.InnerText;
if (cell.DataType != null && cell.DataType == CellValues.SharedString)
{
return(sharedStringTable.ElementAt(Int32.Parse(value)).InnerText);
}
else
{
return(value);
}
}
return(value);
}
/// <summary>
/// Метод возвращает значение ячейки в текстовом формате
/// </summary>
/// <param name="cell">Ячейка</param>
/// <param name="stringTable">Таблица строковых значений листа</param>
/// <returns>Текст ячейки</returns>
public static string GetStringValue(this Cell cell, SharedStringTable stringTable)
{
var value = "";
if (cell != null)
{
value = cell.CellValue.Text;
if (value != null)
{
var dataType = cell.DataType;
if (dataType != null)
{
if (dataType.Value == CellValues.SharedString)
{
value = stringTable.ElementAt(int.Parse(value)).InnerText;
}
}
}
}
return(value);
}
/// <summary>
/// Gets the value of a cell, automatically referencing shared strings and formatting numbers
/// </summary>
/// <param name="c">The cell to get the value of</param>
/// <returns>The value of the cell formatted</returns>
private string GetCellValue(Cell c)
{
SharedStringTable sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
string cellText = c.CellValue?.Text;
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
return(sharedStrings.ElementAt(int.Parse(cellText)).InnerText);
}
if (!string.IsNullOrEmpty(cellText) && c.StyleIndex != null)
{
try
{
// Try to find cell formatting information if it's a number.
CellFormats cellFormats = document.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
CellFormat cellFormat = (CellFormat)cellFormats.ElementAt((int)c.StyleIndex.Value);
NumberingFormats numberingFormats = document.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats;
NumberingFormat numberFormat = numberingFormats.Elements <NumberingFormat>()
.Where(f => f.NumberFormatId.Value == cellFormat.NumberFormatId.Value)
.FirstOrDefault();
// Try to parse to double with format.
if (numberFormat != default(NumberingFormat) && double.TryParse(cellText, out double conv))
{
return(conv.ToString(numberFormat.FormatCode));
}
}
catch (Exception ex)
{
Logger.Warn(ex, "Error retrieving cell format information");
}
}
return(cellText);
}
public static void ReadExcelFile(string fileName)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
// open the document read-only
SharedStringTable sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
string cellValue = null;
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.WorksheetParts.Where(x => x.Uri.OriginalString.Contains("sheet1")).Single();
foreach (SheetData sheetData in worksheetPart.Worksheet.Elements <SheetData>())
{
if (sheetData.HasChildren)
{
foreach (Row row in sheetData.Elements <Row>())
{
foreach (Cell cell in row.Elements <Cell>())
{
cellValue = cell.InnerText;
if (cell.DataType != null && cell.DataType == CellValues.SharedString)
{
Console.WriteLine("cell val: " + sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText);
}
else
{
Console.WriteLine("else: " + cellValue);
}
}
}
}
}
document.Close();
}
}
private void ReadExcelFile(string file_path, ref List <Record> records)
{
try
{
using (SpreadsheetDocument excel_doc = SpreadsheetDocument.Open(file_path, false))
{
Sheet sheet = excel_doc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>().FirstOrDefault();
if (sheet != null)
{
SharedStringTable sharedString = excel_doc.WorkbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault().SharedStringTable;
Worksheet worksheet = (excel_doc.WorkbookPart.GetPartById(sheet.Id) as WorksheetPart).Worksheet;
List <Row> rows = worksheet.Descendants <Row>().ToList();
FindHeadRow(excel_doc, rows, sharedString); // Находим заголовки столбцов и их буквенные индексы, вынесенно в отдельный метод
records = new List <Record>(rows.Count);
foreach (Row row in rows)
{
if (row.RowIndex == 1) // Потому что первый мы считываем для заголовков столбов в FindHeadRow
{
continue;
}
temp_values = new List <string>();
temp_task_results = new List <Tuple <double, double> >();
foreach (Cell cell in row)
{
if (cell.CellValue != null && !String.IsNullOrWhiteSpace(cell.CellValue.InnerText) && cell.DataType?.Value != CellValues.Error)
{
var value = cell.CellValue.InnerText;
if (columnName_columnIndex != null)
{
var temp_columnName_columnIndex = columnName_columnIndex.Where(t => t.Item2 == Regex.Match(cell.CellReference, "[A-Z]{1,}", RegexOptions.IgnoreCase).Value).FirstOrDefault();
if (cell.DataType != null && temp_columnName_columnIndex != null)
{
if (cell.DataType == CellValues.SharedString)
{
if (double.TryParse(temp_columnName_columnIndex.Item1, out double result) ||
Regex.IsMatch(temp_columnName_columnIndex.Item1, "сумма", RegexOptions.IgnoreCase))
{
if (Regex.IsMatch(temp_columnName_columnIndex.Item1, "сумма", RegexOptions.IgnoreCase))
{
temp_task_results.Add(new Tuple <double, double>((int)temp_task_results[temp_task_results.Count - 1].Item1 + 1,
double.Parse(sharedString.ElementAt(int.Parse(value)).InnerText, CultureInfo.InvariantCulture.NumberFormat)));
}
else
{
temp_task_results.Add(new Tuple <double, double>(result, double.Parse(cell.CellValue.InnerText, CultureInfo.InvariantCulture.NumberFormat)));
}
}
else
{
temp_values.Add(sharedString.ElementAt(int.Parse(value)).InnerText);
}
}
}
else if (temp_columnName_columnIndex != null)
{
if (double.TryParse(temp_columnName_columnIndex.Item1, NumberStyles.Any, CultureInfo.InvariantCulture.NumberFormat, out double result) ||
Regex.IsMatch(temp_columnName_columnIndex.Item1, "сумма", RegexOptions.IgnoreCase))
{
if (Regex.IsMatch(temp_columnName_columnIndex.Item1, "сумма", RegexOptions.IgnoreCase))
{
temp_task_results.Add(new Tuple <double, double>((int)temp_task_results[temp_task_results.Count - 1].Item1 + 1,
double.Parse(cell.CellValue.InnerText, CultureInfo.InvariantCulture.NumberFormat)));
}
else
{
temp_task_results.Add(new Tuple <double, double>(result, double.Parse(cell.CellValue.InnerText, CultureInfo.InvariantCulture.NumberFormat)));
}
}
else
{
temp_values.Add(cell.CellValue.InnerText);
}
}
}
else
{
MessageBox.Show("Список заголовков пуст (Этап считывания)", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
records.Add(new Record(temp_values, temp_task_results));
double progress = ((double)records.Count / (double)(rows.Count - 1)) * 100;
OnProgress_up((int)progress, file_path);
progress = 0;
}
}
}
if (columnName_columnIndex != null)
{
columnName_columnIndex.Clear();
}
}
catch
{
MessageBox.Show($"Файл {Path.GetFileName(file_path)} уже открыт в другой программе.\nПожалуйста, закройте его и начните процедуру считывания заново.",
"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private static string GetStringValue(SharedStringTable stringTable, Cell c, int id)
{
return stringTable.ElementAt(Convert.ToInt32(c.CellValue.Text)).FirstOrDefault().InnerText;
}
public string SharedString(int index)
{
var element = SharedStringTable.ElementAt(index);
return(element.Aggregate("", (str, child) => str + (TextLocalnames.Contains(child.LocalName) ? child.InnerText : "")));
}
private object GetCellValue(Cell cell)
{
if (cell != null)
{
if (cell.DataType != null && cell.DataType.HasValue)
{
switch (cell.DataType.Value)
{
case CellValues.String:
case CellValues.SharedString:
int sharedStringIndex;
if (int.TryParse(cell.CellValue.InnerText, out sharedStringIndex))
{
if (sharedStringTable == null)
{
throw new Exception("Cell references shared string table, but table not found");
}
return(sharedStringTable.ElementAt(sharedStringIndex).InnerText);
}
else
{
return(cell.CellValue.InnerText);
}
case CellValues.Boolean:
if (cell.CellValue.InnerText == "1")
{
return(true);
}
else if (cell.CellValue.InnerText == "0")
{
return(false);
}
else
{
throw new Exception("Failed to parse boolean cell with value " + cell.CellValue.InnerText + " at " + cell.CellReference.Value);
}
case CellValues.Number:
return(double.Parse(cell.CellValue.InnerText));
case CellValues.InlineString:
return(cell.InlineString.InnerText);
case CellValues.Error:
default:
break;
}
}
else if (cell.CellValue != null)
{
if (ExcelDateHelper.IsDateTimeCell(spreadsheetDocument.WorkbookPart, cell))
{
return(DateTime.FromOADate(double.Parse(cell.CellValue.InnerText)));
}
if (double.TryParse(cell.CellValue.InnerText, out var doubleValue))
{
return(doubleValue);
}
else
{
return(cell.CellValue.InnerText);
}
}
}
return(null);
}
/// <summary>
/// 获取单位格的值
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="workbookPart"></param>
/// <param name="type">1 不去空格 2 前后空格 3 所有空格 </param>
/// <returns></returns>
public static string GetCellValue(Cell cell, WorkbookPart workbookPart, int type = 2)
{
//合并单元格不做处理
if (cell.CellValue == null)
{
return(string.Empty);
}
string cellInnerText = cell.CellValue.InnerXml;
//纯字符串
if (cell.DataType != null && (cell.DataType.Value == CellValues.SharedString || cell.DataType.Value == CellValues.String || cell.DataType.Value == CellValues.Number))
{
//获取spreadsheetDocument中共享的数据
SharedStringTable stringTable = workbookPart.SharedStringTablePart.SharedStringTable;
//如果共享字符串表丢失，则说明出了问题。
if (!stringTable.Any())
{
return(string.Empty);
}
string text = stringTable.ElementAt(int.Parse(cellInnerText)).InnerText;
if (type == 2)
{
return(text.Trim());
}
else if (type == 3)
{
return(text.Replace(" ", ""));
}
else
{
return(text);
}
}
//bool类型
else if (cell.DataType != null && cell.DataType.Value == CellValues.Boolean)
{
return((cellInnerText != "0").ToString().ToUpper());
}
//数字格式代码（numFmtId）小于164是内置的：https://www.it1352.com/736329.html
else
{
//为空为数值
if (cell.StyleIndex == null)
{
return(cellInnerText);
}
Stylesheet styleSheet = workbookPart.WorkbookStylesPart.Stylesheet;
CellFormat cellFormat = (CellFormat)styleSheet.CellFormats.ChildElements[(int)cell.StyleIndex.Value];
uint formatId = cellFormat.NumberFormatId.Value;
double doubleTime; //OLE 自动化日期值
DateTime dateTime; //yyyy/MM/dd HH:mm:ss
switch (formatId)
{
case 0: //常规
return(cellInnerText);
case 9: //百分比【0%】
case 10: //百分比【0.00%】
case 11: //科学计数【1.00E+02】
case 12: //分数【1/2】
return(cellInnerText);
case 14:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM/dd"));
//case 15:
//case 16:
case 17:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM"));
//case 18:
//case 19:
case 20:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("H:mm"));
case 21:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("HH:mm:ss"));
case 22:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM/dd HH:mm"));
//case 45:
//case 46:
case 47:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM/dd"));
case 58: //【中国】11月11日
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("MM/dd"));
case 176: //【中国】2020年11月11日
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM/dd"));
case 177: //【中国】11:22:00
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("HH:mm:ss"));
default:
return(cellInnerText);
}
}
}
private object GetCellValue(Cell cell)
{
if (cell != null)
{
if (cell.DataType != null && cell.DataType.HasValue)
{
switch (cell.DataType.Value)
{
case CellValues.String:
case CellValues.SharedString:
int sharedStringIndex;
if (int.TryParse(cell.CellValue.InnerText, out sharedStringIndex))
{
if (_sharedStringTable == null)
{
throw new Exception("Cell references shared string table, but table not found");
}
return(_sharedStringTable.ElementAt(sharedStringIndex).InnerText);
}
else
{
return(cell.CellValue.InnerText);
}
case CellValues.Boolean:
if (cell.CellValue.InnerText == "1")
{
return(true);
}
else if (cell.CellValue.InnerText == "0")
{
return(false);
}
else
{
throw new Exception("Failed to parse boolean cell with value " + cell.CellValue.InnerText + " at " + cell.CellReference.Value);
}
case CellValues.Number:
return(double.Parse(cell.CellValue.InnerText));
case CellValues.InlineString:
return(cell.InlineString.InnerText);
case CellValues.Error:
default:
break;
}
}
else if (cell.CellValue != null)
{
if (cell.StyleIndex != null && cell.StyleIndex.HasValue)
{
var spreadsheetDocument = _package;
if (spreadsheetDocument != null)
{
var cellFormats = spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
if (cell.StyleIndex.Value < cellFormats.Count)
{
//see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat(v=office.14).aspx
//and http://stackoverflow.com/questions/4730152/what-indicates-an-office-open-xml-cell-contains-a-date-time-value
var cellFormat = cellFormats.ElementAt((int)cell.StyleIndex.Value) as CellFormat;
if (cellFormat != null && cellFormat.NumberFormatId.HasValue)
{
var numberFormat = cellFormat.NumberFormatId.Value;
if (numberFormat >= 14 && numberFormat <= 17 || numberFormat == 22 || numberFormat == 30) // date with or without time
{
return(DateTime.FromOADate(double.Parse(cell.CellValue.InnerText)));
}
else if (numberFormat >= 18 && numberFormat <= 21 || numberFormat == 45 || numberFormat == 46) // time only
{
return(DateTime.FromOADate(double.Parse(cell.CellValue.InnerText)).TimeOfDay);
}
}
}
}
}
double doubleValue;
if (double.TryParse(cell.CellValue.InnerText, out doubleValue))
{
return(doubleValue);
}
else
{
return(cell.CellValue.InnerText);
}
}
}
return(null);
}
public static void ExcelTableToDataTable(this DataTable dt, string path, string sheetName, string tableName)
{
dt.TableName = tableName;
using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, true))
{
//References to the workbook and Shared String Table.
var id = document.WorkbookPart.Workbook.Descendants <Sheet>().First(s => s.Name == sheetName).Id;
//Get sheet my the sheet id
WorksheetPart sheet = (WorksheetPart)document.WorkbookPart.GetPartById(id);
//Get Excel table definition
TableDefinitionPart tableDefinitionPart = (from t in sheet.TableDefinitionParts where t.Table.DisplayName == tableName select t).First();
//Get the cell reference for the Excel table
string cellReference = tableDefinitionPart.Table.Reference.ToString();
//Get start and end Row and Column
Regex regexCellName = new Regex("[A-Za-z]+");
Regex regexRowIndex = new Regex(@"\d+");
int startRow = Convert.ToInt32(regexRowIndex.Match(OpenXmlExt.ReferenceIndex(cellReference, 0)).ToString());
int startColumn = Convert.ToInt32(OpenXmlExt.TranslateColumnNameToIndex(regexCellName.Match(OpenXmlExt.ReferenceIndex(cellReference, 0)).ToString()));
int endRow = Convert.ToInt32(regexRowIndex.Match(OpenXmlExt.ReferenceIndex(cellReference, 1)).ToString());
int endColumn = Convert.ToInt32(OpenXmlExt.TranslateColumnNameToIndex(regexCellName.Match(OpenXmlExt.ReferenceIndex(cellReference, 1)).ToString()));
//Get column names
var columnNames = from n in tableDefinitionPart.Table.TableColumns
select(from a in XDocument.Load(new StringReader(n.OuterXml)).Descendants()
select a.Attribute(XName.Get("name")).Value).First();
//Convert Excel table to ADO.NET DataTable
DataColumn dataColumn;
foreach (var name in columnNames)
{
dataColumn = new DataColumn(name.Replace(" ", string.Empty), typeof(System.String));
dataColumn.Caption = name;
dt.Columns.Add(dataColumn);
}
SharedStringTable sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
IEnumerable <Row> dataRows =
from row in sheet.Worksheet.Descendants <Row>()
select row;
DataRow dataRow;
foreach (Row row in dataRows)
{
if (row.RowIndex > startRow && row.RowIndex <= endRow)
{
var cells = from cell in row.Descendants <Cell>() select cell;
int rowIndex;
int columnIndex;
string cellValue;
int absoluteColumnIndex;
dataRow = dt.NewRow();
foreach (var cell in cells)
{
rowIndex = Convert.ToInt32(regexRowIndex.Match(cell.CellReference.Value).ToString());
columnIndex = OpenXmlExt.TranslateColumnNameToIndex(regexCellName.Match(cell.CellReference.Value).ToString());
absoluteColumnIndex = columnIndex - startColumn;
if (columnIndex >= startColumn && columnIndex <= endColumn)
{
if (cell.CellValue != null)
{
cellValue = cell.CellValue.InnerText;
if (cell.DataType == "s")
{
cellValue = sharedStrings.ElementAt(Convert.ToInt32(cellValue)).InnerText;
}
dataRow[absoluteColumnIndex] = cellValue;
}
}
}
dt.Rows.Add(dataRow);
}
}
}
}
private string EstraiValoreCella(Cell cell, SharedStringTable sharedStringTable, CellFormats cellFormats, NumberingFormats numberingFormats)
{
CellValue cellValue = cell.CellValue;
if (cellValue != null)
{
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.SharedString:
return(sharedStringTable.ElementAt(Int32.Parse(cell.InnerText)).InnerText);
case CellValues.Date:
double oaDateAsDouble;
if (double.TryParse(cell.InnerText, out oaDateAsDouble)) //this line is Culture dependent!
{
DateTime dateTime = DateTime.FromOADate(oaDateAsDouble);
return(dateTime.ToShortDateString());
}
return(string.Empty);
default:
return(cell.InnerText);
}
}
else
{
if (cell.StyleIndex != null)
{
var cellFormat = (CellFormat)cellFormats.ElementAt((int)cell.StyleIndex.Value);
if (cellFormat.NumberFormatId != null)
{
var numberFormatId = cellFormat.NumberFormatId.Value;
var numberingFormat = numberingFormats.Cast <NumberingFormat>()
.SingleOrDefault(f => f.NumberFormatId.Value == numberFormatId);
// Here's yer string! Example: $#,##0.00_);[Red]($#,##0.00)
if (numberingFormat != null && (numberingFormat.FormatCode.Value.Contains("yyyy-mm-dd") || numberingFormat.FormatCode.Value.Contains("yyyy\\-mm\\-dd")))
{
string formatString = numberingFormat.FormatCode.Value;
double oaDateAsDouble;
if (double.TryParse(cell.InnerText, out oaDateAsDouble)) //this line is Culture dependent!
{
DateTime dateTime = DateTime.FromOADate(oaDateAsDouble);
return(dateTime.ToShortDateString());
}
else
{
return(string.Empty);
}
}
else
{
return(cell.InnerText);
}
}
}
else
{
return(cell.InnerText);
}
}
}
return(string.Empty);
}
private void FindHeadRow(SpreadsheetDocument doc, IEnumerable <Row> rows, SharedStringTable sharedStringTable) // Считываем заголовки столбцов и их буквенные инедксы
{
Row head_row = rows.Where(r => r.RowIndex == 1).FirstOrDefault();
if (columnName_columnIndex == null)
{
columnName_columnIndex = new List <Tuple <string, string> >();
}
if (columnName_columnIndex != null)
{
foreach (Cell cell in head_row)
{
if (cell.CellValue != null && !String.IsNullOrWhiteSpace(cell.CellValue.InnerText) && cell.DataType?.Value != CellValues.Error)
{
if (cell.DataType != null)
{
if (cell.DataType == CellValues.SharedString)
{
columnName_columnIndex.Add(new Tuple <string, string>(sharedStringTable.ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText,
Regex.Match(cell.CellReference, "[A-Z]{1,}").Value));
}
}
else
{
columnName_columnIndex.Add(new Tuple <string, string>(cell.CellValue.InnerText,
Regex.Match(cell.CellReference, "[A-Z]{1,}").Value));
}
}
}
RemoveNeedless();
}
}
public static List <object> RetrieveExcelCellsFromRow(Row row, SharedStringTable sharedStrings, List <CellFormats> cellFormats)
{
object obj = null;
List <object> list = new List <object>();
foreach (var theCell in row.Descendants <Cell>())
{
// If the cell represents a numeric value, 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.
string value = theCell.InnerText;
if (theCell.DataType != null && theCell.DataType.HasValue)
{
switch (theCell.DataType.Value)
{
case CellValues.Number:
int intTemp = int.TryParse(value, out intTemp) ? intTemp : 0;
obj = intTemp;
break;
case CellValues.Date:
DateTime dateTimeTemp = DateTime.TryParse(value, out dateTimeTemp) ? dateTimeTemp : new DateTime();
obj = dateTimeTemp;
break;
case CellValues.SharedString:
// If the shared string table is missing, something is
// wrong. Return the index that you found in the cell.
// Otherwise, look up the correct text in the table.
if (sharedStrings != null)
{
obj = sharedStrings.ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
obj = false;
break;
default:
obj = true;
break;
}
break;
default:
obj = value;
break;
}
list.Add(obj);
}
else if (theCell.StyleIndex != null && theCell.StyleIndex.HasValue)
{
// look up the style used for the cell
int formatStyleIndex = Convert.ToInt32(theCell.StyleIndex.Value);
CellFormat cf = cellFormats[0].Descendants <CellFormat>().ToList()[formatStyleIndex];
switch (cf.NumberFormatId.Value)
{
case 14:
double cellValue = double.TryParse(value, out cellValue) ? cellValue : 0;
if (cellValue > 59)
{
cellValue -= 1; //Excel/Lotus 29/2/1900 excel thinks there was a leap year in 1900.
}
obj = new DateTime(1900, 12, 31).AddDays(cellValue);
break;
default:
obj = value;
break;
}
list.Add(obj);
}
else
{
list.Add("");
}
}
return(list);
}
private void WriteToSheet(Worksheet worksheet, SharedStringTable sharedString, List <Record> records, string action, string sheet_name)
{
switch (action)
{
case "fill":
{
List <DataForFill> data_for_fill = new List <DataForFill>(); // Создаем список структур, в которых будут содержаться только интересующие нас данные
Row task_result_row = worksheet.Descendants <Row>().Where(r => r.RowIndex == 11).FirstOrDefault(); // т.к. начинаем писать в файл с 11 строки
foreach (Cell cell in task_result_row) // Собираем буквенные ссылки на ячейки с заданиями
{
if (cell.CellValue != null && !String.IsNullOrWhiteSpace(cell.CellValue.InnerText) && cell.DataType?.Value != CellValues.Error)
{
if (cell.DataType != null)
{
if (cell.DataType == CellValues.SharedString)
{
if (int.TryParse(sharedString.ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText, out int result_int) ||
double.TryParse(sharedString.ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText,
NumberStyles.Any, CultureInfo.InvariantCulture.NumberFormat, out double result_double))
{
columnName_columnIndex.Add(new Tuple <string, string>(sharedString.ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText,
Regex.Match(cell.CellReference, "[A-Z]{1,}").Value));
}
}
}
else
{
if (int.TryParse(sharedString.ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText, out int result_int) ||
double.TryParse(cell.CellValue.InnerText, NumberStyles.Any, CultureInfo.InvariantCulture.NumberFormat, out double result_double))
{
columnName_columnIndex.Add(new Tuple <string, string>(cell.CellValue.InnerText,
Regex.Match(cell.CellReference, "[A-Z]{1,}").Value));
}
}
}
} // Собираем буквенные ссылки на ячейки с заданиями
foreach (Record item in records) // Заполняем список data
{
data_for_fill.Add(new DataForFill(item.student_FIO, item.variant, item.task_results));
}
Row sample_row = worksheet.Descendants <Row>().Where(i => i.RowIndex == 25).FirstOrDefault();
foreach (Cell sample_cell in sample_row)
{
if (sample_cell.CellReference == "D25")
{
cell_style_fio = sample_cell.StyleIndex;
}
if (sample_cell.CellReference == "E25")
{
cell_style_variant = sample_cell.StyleIndex;
}
if (sample_cell.CellReference == "F25")
{
cell_style_tasks = sample_cell.StyleIndex;
}
}
foreach (DataForFill data in data_for_fill) // Бежим по нашей структуре и заполняем ячейки
{
// Insert the text into the SharedStringTablePart.
int index_FIO = InsertSharedStringItem(data.student_FIO, sharedString);
int index_variant = InsertSharedStringItem(data.variant, sharedString);
Cell cellFIO = InsertCellInWorksheet("D", (uint)data_for_fill.IndexOf(data) + 25, worksheet);
Cell cellVariant = InsertCellInWorksheet("E", (uint)data_for_fill.IndexOf(data) + 25, worksheet);
// Задаем значения ячеек.
cellFIO.CellValue = new CellValue(index_FIO.ToString());
cellVariant.CellValue = new CellValue(index_variant.ToString());
cellFIO.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.SharedString);
cellVariant.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.SharedString);
cellFIO.StyleIndex = cell_style_fio;
cellVariant.StyleIndex = cell_style_variant;
foreach (Tuple <double, double> temp_task in data.task_results) // Заполяем ячейки результатов
{
foreach (Tuple <string, string> task_ref in columnName_columnIndex)
{
if (temp_task.Item1 == double.Parse(task_ref.Item1, NumberStyles.Any, CultureInfo.InvariantCulture.NumberFormat))
{
int c_row_index = (int)data_for_fill.IndexOf(data) + 25;
//if (c_row_index > 165)
// MessageBox.Show("Watch");
Cell cell_task_result = InsertCellInWorksheet(task_ref.Item2, (uint)data_for_fill.IndexOf(data) + 25, worksheet);
cell_task_result.CellValue = new CellValue(temp_task.Item2.ToString());
cell_task_result.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_task_result.StyleIndex = cell_style_tasks;
}
}
}
// Save the new worksheet.
worksheet.Save();
double count_record = data_for_fill.IndexOf(data);
double progress = (((double)data_for_fill.IndexOf(data) + 1) / (double)(data_for_fill.Count - 1)) * 100;
OnProgress_up((int)progress, sheet_name);
progress = 0;
}
break;
}
case "calculate_and_fill":
{
var region_records = records.GroupBy(r => r.region).ToList();
List <DataSortedByRegion> region_data_to_fill = new List <DataSortedByRegion>(region_records.Count);
foreach (IGrouping <string, Record> record in region_records) // Заполняем список данных, собранных с кадого региона отдельно
{
string region = record.Key;
int schools_count = record.GroupBy(s => s.school_id).ToList().Count;
int clasess_count = record.GroupBy(s => s.class_id).ToList().Count;
int student_count = record.GroupBy(s => s.student_FIO).ToList().Count;
int student_write_count = record.Where(s => s.variant != "N").ToList().Count;
int low_lvl_score_count = record.Where(l => Regex.IsMatch(l.understand_lvl, "Низкий", RegexOptions.IgnoreCase)).ToList().Count;
int upper_low_lvl_score_count = record.Where(l => Regex.IsMatch(l.understand_lvl, "Пониженный", RegexOptions.IgnoreCase)).ToList().Count;
int base_lvl_score_count = record.Where(l => Regex.IsMatch(l.understand_lvl, "Базовый", RegexOptions.IgnoreCase)).ToList().Count;
int upper_base_lvl_score_count = record.Where(l => Regex.IsMatch(l.understand_lvl, "Повышенный", RegexOptions.IgnoreCase)).ToList().Count;
int high_lvl_score_count = record.Where(l => Regex.IsMatch(l.understand_lvl, "Высокий", RegexOptions.IgnoreCase)).ToList().Count;
double min_total_score = record.Where(v => v.variant != "N").Min(t => t.total_score);
double max_total_score = record.Where(v => v.variant != "N").Max(t => t.total_score);
double total_score = 0;
double base_lvl_count = 0;
double high_lvl_count = 0;
foreach (var data in record)
{
total_score += data.total_score;
base_lvl_count += data.base_lvl;
high_lvl_count += data.high_lvl;
}
region_data_to_fill.Add(new DataSortedByRegion(region, schools_count, clasess_count, student_count, student_write_count,
low_lvl_score_count, upper_low_lvl_score_count, base_lvl_score_count,
upper_base_lvl_score_count, high_lvl_score_count, (int)Math.Truncate(total_score), (int)Math.Truncate(base_lvl_count),
(int)Math.Truncate(high_lvl_count), min_total_score, max_total_score));
}
foreach (DataSortedByRegion data in region_data_to_fill)
{
int index_region = InsertSharedStringItem(data.region, sharedString);
//int index_schools_count = InsertSharedStringItem(data.schools_count.ToString(), sharedString);
//int index_clasess_count = InsertSharedStringItem(data.clasess_count.ToString(), sharedString);
//int index_student_count = InsertSharedStringItem(data.student_count.ToString(), sharedString);
//int index_student_write_count = InsertSharedStringItem(data.student_write_count.ToString(), sharedString);
//int index_low_lvl_score_count = InsertSharedStringItem(data.low_lvl_score_count.ToString(), sharedString);
//int index_upper_low_lvl_score_count = InsertSharedStringItem(data.upper_low_lvl_score_count.ToString(), sharedString);
//int index_base_lvl_score_count = InsertSharedStringItem(data.base_lvl_score_count.ToString(), sharedString);
//int index_upper_base_lvl_score_count = InsertSharedStringItem(data.upper_base_lvl_score_count.ToString(), sharedString);
//int index_high_lvl_score_count = InsertSharedStringItem(data.high_lvl_score_count.ToString(), sharedString);
//int index_total_score = InsertSharedStringItem(data.total_score.ToString(), sharedString);
//int index_base_lvl_count = InsertSharedStringItem(data.base_lvl_count.ToString(), sharedString);
//int index_high_lvl_count = InsertSharedStringItem(data.high_lvl_count.ToString(), sharedString);
//int min_total_score = InsertSharedStringItem(data.min_total_score.ToString(), sharedString);
//int max_total_score = InsertSharedStringItem(data.max_total_score.ToString(), sharedString);
Cell cell_region = InsertCellInWorksheet("B", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_schools_count = InsertCellInWorksheet("D", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_clasess_count = InsertCellInWorksheet("E", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_student_count = InsertCellInWorksheet("F", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_student_write_count = InsertCellInWorksheet("H", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_low_lvl_score_count = InsertCellInWorksheet("M", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_upper_low_lvl_score_count = InsertCellInWorksheet("Q", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_base_lvl_score_count = InsertCellInWorksheet("U", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_upper_base_lvl_score_count = InsertCellInWorksheet("Y", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_high_lvl_score_count = InsertCellInWorksheet("AC", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_total_score = InsertCellInWorksheet("AG", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_base_lvl_count = InsertCellInWorksheet("AK", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_high_lvl_count = InsertCellInWorksheet("AN", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_min_total_score = InsertCellInWorksheet("AS", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_max_total_score = InsertCellInWorksheet("AU", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
cell_region.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.SharedString);
cell_schools_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_clasess_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_student_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_student_write_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_low_lvl_score_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_upper_low_lvl_score_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_base_lvl_score_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_upper_base_lvl_score_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_high_lvl_score_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_total_score.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_base_lvl_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_high_lvl_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_min_total_score.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_max_total_score.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
// Set the value of cell A1.
cell_region.CellValue = new CellValue(index_region.ToString());
cell_schools_count.CellValue = new CellValue(data.schools_count.ToString());
cell_clasess_count.CellValue = new CellValue(data.clasess_count.ToString());
cell_student_count.CellValue = new CellValue(data.student_count.ToString());
cell_student_write_count.CellValue = new CellValue(data.student_write_count.ToString());
cell_low_lvl_score_count.CellValue = new CellValue(data.low_lvl_score_count.ToString());
cell_upper_low_lvl_score_count.CellValue = new CellValue(data.upper_low_lvl_score_count.ToString());
cell_base_lvl_score_count.CellValue = new CellValue(data.base_lvl_score_count.ToString());
cell_upper_base_lvl_score_count.CellValue = new CellValue(data.upper_base_lvl_score_count.ToString());
cell_high_lvl_score_count.CellValue = new CellValue(data.high_lvl_score_count.ToString());
cell_total_score.CellValue = new CellValue(data.total_score.ToString());
cell_base_lvl_count.CellValue = new CellValue(data.base_lvl_count.ToString());
cell_high_lvl_count.CellValue = new CellValue(data.high_lvl_count.ToString());
cell_min_total_score.CellValue = new CellValue(data.min_total_score.ToString());
cell_max_total_score.CellValue = new CellValue(data.max_total_score.ToString());
// Save the new worksheet.
worksheet.Save();
double progress = (((double)region_data_to_fill.IndexOf(data) + 1) / (double)(region_data_to_fill.Count)) * 100;
OnProgress_up((int)progress, sheet_name);
progress = 0;
}
break;
}
}
}
/// <summary>
/// Gets the value from the cell, optionally converting it to a string.
/// </summary>
/// <param name="cell">Cell to get the value from</param>
/// <param name="sharedStringTable">Structure of the spreadsheet that contains the actual string values</param>
/// <param name="stylesheet">Style section of the spreadsheet, containing formatting information</param>
/// <param name="readAsString">When true, a string will be returned rather than the actual cell value</param>
/// <returns>The value inside of the cell, possibly converted to a string</returns>
private static object GetCellValue(Cell cell, SharedStringTable sharedStringTable, Stylesheet stylesheet, bool readAsString)
{
if (cell != null && !string.IsNullOrEmpty(cell.InnerText))
{
if (cell.DataType == null)
{
// For numbers and dates Office sets the data type as null.
// In this case, the type needs to be determined by inspecting the cell format.
var numberFormatId = 0;
if (cell.StyleIndex != null)
{
var cellFormat = (CellFormat)stylesheet.CellFormats.ElementAt((int)cell.StyleIndex.Value);
numberFormatId = (int)cellFormat.NumberFormatId.Value;
}
if (14 <= numberFormatId && numberFormatId <= 22 ||
45 <= numberFormatId && numberFormatId <= 47)
{
// This is a date
if (cell.CellValue.TryGetDouble(out var number))
{
var value = DateTime.FromOADate(number);
if (readAsString)
{
return(value.ToString());
}
return(value);
}
return(cell.InnerText);
}
else
{
// This is a number
if (cell.CellValue.TryGetDouble(out var value))
{
if (readAsString)
{
if (numberFormatId != 0)
{
var numberingFormat = (NumberingFormat)stylesheet.NumberingFormats.ElementAt(numberFormatId);
var formatted = value.ToString(numberingFormat.FormatCode);
return(formatted);
}
return(value.ToString());
}
return(value);
}
return(cell.InnerText);
}
}
else if (cell.DataType.Value == CellValues.SharedString)
{
// In this case, the value is actually an index on the shared string table.
// This is the representation used for strings by Office.
if (sharedStringTable != null)
{
if (cell.CellValue.TryGetInt(out var index))
{
return(sharedStringTable.ElementAt(index).InnerText);
}
}
return(cell.InnerText);
}
else if (cell.DataType.Value == CellValues.Boolean)
{
// For booleans, values 0 or 1 can be converted directly.
if (cell.CellValue.TryGetBoolean(out var value))
{
if (readAsString)
{
return(value.ToString());
}
return(value);
}
return(cell.InnerText);
}
else
{
// Default to raw string value for inline strings and unknown types.
return(cell.InnerText);
}
}
return(null);
}
public Get_ChartGLAccounts(string companyName)
{
const string clientId = "b4b7919a-f51c-4272-9576-dbcccffb8764";
const string clientSecret = "eWfc7W7BQXYZ";
//This can be any url as long as it is identical to the callback url you specified for your app in the App Center.
var callbackUrl = new Uri("http://websocketclient.local");
var connector = new Connector(clientId, clientSecret, callbackUrl);
var client = new ExactOnlineClient(connector.EndPoint, connector.GetAccessToken);
using (SpreadsheetDocument document = SpreadsheetDocument.Open(@"E:\робочий стіл 2016\Gob Progect\exactonline-api-dotnet-client-master11\exactonline-api-dotnet-client-master\src\ConsoleApplication\Excel\1 Chart of Accounts revised.xlsx", false))
{
// open the document read-only
SharedStringTable sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
string cellValue = null;
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.WorksheetParts.Where(x => x.Uri.OriginalString.Contains("sheet1")).Single();
foreach (SheetData sheetData in worksheetPart.Worksheet.Elements <SheetData>())
{
if (sheetData.HasChildren)
{
foreach (Row row in sheetData.Elements <Row>())
{
foreach (Cell cell in row.Elements <Cell>())
{
cellValue = cell.InnerText;
if (cell.DataType != null && cell.DataType == CellValues.SharedString)
{
Console.WriteLine("cell val: " + sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText);
}
else
{
Console.WriteLine("else: " + cellValue);
}
}
}
}
}
document.Close();
}
try
{
//GLAccount document = new GLAccount
//{
// Code = "1001",
// Description = "Stock",
// TypeDescription = "Current Assets",
// BalanceSide = "D",
// BalanceType = "B",
// Type = 32
//};
//bool createdClient = client.For<GLAccount>().Insert(ref document);
//GLAccount document1 = new GLAccount
//{
// Code = "1220",
// Description = "Bank Deposit A/C 19057399",
// TypeDescription = "Current Assets",
// BalanceSide = "D",
// BalanceType = "B",
// Type = 12
//};
//bool createdClient1 = client.For<GLAccount>().Insert(ref document1);
//GLAccount document2 = new GLAccount
//{
// Code = "1250",
// Description = "Credit Card Account 0793 Paul",
// TypeDescription = "Current Assets"
//};
//bool createdClient2 = client.For<GLAccount>().Insert(ref document2);
GLAccount document4 = new GLAccount
{
Code = "1260",
Description = "Pauls Cash Expenses",
TypeDescription = "Current Liabilities"
};
bool createdClient4 = client.For <GLAccount>().Insert(ref document4);
GLAccount document5 = new GLAccount
{
Code = "2210",
Description = "PAYE/PRSI Payable",
TypeDescription = "Current Liabilities"
};
bool createdClient5 = client.For <GLAccount>().Insert(ref document5);
}
catch (Exception ex)
{
Console.WriteLine("SDO Generated the Following Error: \n\n" + ex.Message, "Error!");
}
}
public static string GetCellValue(Cell c, SharedStringTable stringTable, CellFormats formats, Field colmn)
{
string sval = null;
int styleIndex;
CellFormat cellFormat = null;
if (c == null) return null;
if (c.StyleIndex != null)
{
styleIndex = (int)c.StyleIndex.Value;
cellFormat = (CellFormat)formats.ElementAt(styleIndex);
}
if (c == null || c.CellValue == null)
return sval;
if (c.DataType != null && c.CellFormula == null)
{
switch (c.DataType.Value)
{
case CellValues.SharedString:
sval = stringTable.ElementAt(int.Parse(c.InnerText)).InnerText;
break;
default:
sval = "not a string";
break;
}
}
else
sval = string.IsNullOrWhiteSpace(c.CellValue.InnerText) ? null : c.CellValue.InnerText;
if (colmn != null)
{
switch (colmn.DataFormat)
{
case DataFormatType.Date:
var pdt = c.CellValue.InnerText;
pdt = FixDate(pdt);
if (cellFormat != null && cellFormat.NumberFormatId != null && c.DataType == null)
{
var d = DateTime.FromOADate(double.Parse(pdt));
sval = d.ToString("MM/dd/yyyy");
}
else
{
DateTime tv;
if(sval != null && DateTime.TryParse(sval, out tv))
{
if (colmn.DataFormat == DataFormatType.Date)
sval = tv.ToShortDateString();
else
sval = tv.ToString();
}
else
sval = null;
}
break;
case DataFormatType.DateTime:
var dt = DateTime.FromOADate(double.Parse(c.CellValue.InnerText));
sval = dt.ToString();
break;
case DataFormatType.DateMixed:
double aoDate;
if (double.TryParse(c.CellValue.InnerText, out aoDate))
{
var aodt = DateTime.FromOADate(aoDate);
if ((DateTime.Now - aodt).Days < 36500)
sval = aodt.ToString("MM/dd/yyyy");
}
break;
}
}
if (sval != null && colmn != null && colmn.postProcRegex != null)
foreach(var d in colmn.postProcRegex)
sval = Regex.Replace(sval, d.Item1, d.Item2);
if (sval != null)
{
//if (sval.Length > 4000)
//{
// Log.New.Msg($"truncating to length 4000 cell: {c.CellReference.InnerText} contents: {sval}");
// sval = sval.Substring(0, 4000);
//}
sval = sval.Replace("\t", "");
sval.Trim();
}
return sval;
}
