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