Exemplo n.º 1
0
 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>");
 }
Exemplo n.º 2
0
        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();
                }
            }
        }
Exemplo n.º 3
0
        /// <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);
        }
Exemplo n.º 4
0
        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);
     }
Exemplo n.º 6
0
 private static string GetCellValue(Cell cell, SharedStringTable ssTable)
 {
     if (int.TryParse(cell.InnerText, out int stringTableIndex))
     {
         return(ssTable.ElementAt(stringTableIndex).InnerText);
     }
     else
     {
         return(null);
     }
 }
Exemplo n.º 7
0
        /// <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;
            }
        }
Exemplo n.º 8
0
        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);
        }
Exemplo n.º 9
0
        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);
        }
Exemplo n.º 10
0
        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);
        }
Exemplo n.º 11
0
        /// <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();
            }
        }
Exemplo n.º 12
0
        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);
        }
Exemplo n.º 13
0
        /// <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);
            }
        }
Exemplo n.º 14
0
        //读取指定行和列的内容
        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);
        }
Exemplo n.º 15
0
        /// <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);
        }
Exemplo n.º 16
0
            /// <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);
            }
Exemplo n.º 17
0
        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();
            }
        }
Exemplo n.º 18
0
        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);
            }
        }
Exemplo n.º 19
0
 private static string GetStringValue(SharedStringTable stringTable, Cell c, int id)
 {
     return stringTable.ElementAt(Convert.ToInt32(c.CellValue.Text)).FirstOrDefault().InnerText;
 }
Exemplo n.º 20
0
            public string SharedString(int index)
            {
                var element = SharedStringTable.ElementAt(index);

                return(element.Aggregate("", (str, child) => str + (TextLocalnames.Contains(child.LocalName) ? child.InnerText : "")));
            }
Exemplo n.º 21
0
        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);
        }
Exemplo n.º 22
0
        /// <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);
        }
Exemplo n.º 24
0
        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);
                    }
                }
            }
        }
Exemplo n.º 25
0
        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);
        }
Exemplo n.º 26
0
        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();
            }
        }
Exemplo n.º 27
0
        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);
        }
Exemplo n.º 28
0
        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;
            }
            }
        }
Exemplo n.º 29
0
        /// <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);
        }
Exemplo n.º 30
0
        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!");
            }
        }
Exemplo n.º 31
0
		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;
		}