Exemplo n.º 1
0
        public int GetRowCount(string sheetName)
        {
            var wp        = SpreadsheetReader.GetWorksheetPartByName(_doc, sheetName);
            var worksheet = wp.Worksheet;
            var sheetData = worksheet.GetFirstChild <SheetData>();

            return(sheetData.Elements <Row>().Count());
        }
Exemplo n.º 2
0
        public ExcelWriter()
        {
            _stream = SpreadsheetReader.Create();
            _doc    = SpreadsheetDocument.Open(_stream, true);
            var worksheetPart = SpreadsheetReader.GetWorksheetPartByName(_doc, "Sheet1");

            _writer = new WorksheetWriter(_doc, worksheetPart);
        }
        public static MemoryStream CalcSpreadsheetDocument(MemoryStream ms, bool DeleteFormula, string OutFile)
        {
            bool pg = (progressBar != null);
            SpreadsheetDocument RecultDoc = SpreadsheetDocument.Open(ms, true);

            if (DeleteFormula)
            {
                RecultDoc.WorkbookPart.DeletePart(RecultDoc.WorkbookPart.CalculationChainPart);
            }

            foreach (Sheet curSheet in RecultDoc.WorkbookPart.Workbook.Sheets)
            {
                WorksheetPart RecultWorkSheetPart = SpreadsheetReader.GetWorksheetPartByName(RecultDoc, curSheet.Name.Value);

                IEnumerable <Row> ListRowInRange = RecultWorkSheetPart.Worksheet.Elements <SheetData>().First().Elements <Row>().Where(r => r.Elements <Cell>().Where(c => c.CellFormula != null).Count() > 0);
                if (pg)
                {
                    progressBar.Maximum = ListRowInRange.Count();
                }
                int value = 0;
                foreach (Row Row in ListRowInRange)
                {
                    IEnumerable <Cell> fCell = Row.Elements <Cell>().Where(c => c.CellFormula != null).Where(c => c.CellFormula.Text.Trim() != "");
                    foreach (Cell c in fCell)
                    {
                        var dd = calcFormule(c, RecultWorkSheetPart, RecultDoc, DeleteFormula);
                    }
                    if (pg)
                    {
                        progressBar.Value = ++value;
                    }
                }
            }
            RecultDoc.Close();

            if (OutFile != null)
            {
                if (OutFile.Length > 0)
                {
                    SpreadsheetWriter.StreamToFile(OutFile, ms);
                }
            }

            return(ms);
        }
Exemplo n.º 4
0
        /// <summary>
        /// Create the spreadsheet.
        /// </summary>
        /// <param name="documentName">Excel file name.</param>
        /// <param name="excelWorkSheetName">Excel worksheet name: default: sheet1.</param>
        /// <param name="rowData">Row data to write.</param>
        /// <param name="headerData">Header data.</param>
        /// <param name="rowPointers">Row pointers.</param>
        /// <param name="styleSheet">Style sheet.</param>
        /// <returns>Memory stream.</returns>
        private static MemoryStream CreateSpreadSheet(string documentName, string excelWorkSheetName, IEnumerable <T> rowData, string[] headerData, string[] rowPointers, Stylesheet styleSheet)
        {
            int rowNum   = 0;
            int colNum   = 0;
            int maxWidth = 0;
            int minCol   = 1;
            int maxCol   = rowPointers == null ? minCol : rowPointers.Length;

            maxCol = maxCol == 1 && headerData == null ? 1 : headerData.Length;

            MemoryStream        xmlStream   = SpreadsheetReader.Create();
            SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(xmlStream, true);

            SetSheetName(excelWorkSheetName, spreadSheet);

            if (styleSheet == null)
            {
                SetStyleSheet(spreadSheet);
            }
            else
            {
                spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet = styleSheet;
                spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
            }

            WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(spreadSheet, excelWorkSheetName);

            WriteHeaders(headerData, out rowNum, out colNum, out maxWidth, spreadSheet, worksheetPart);
            AddCellWidthStyles(Convert.ToUInt32(minCol), Convert.ToUInt32(maxCol), maxWidth, spreadSheet, worksheetPart);

            if (rowPointers == null || rowPointers.Length == 0)
            {
                WriteRowsFromHeaders(rowData, headerData, rowNum, out maxWidth, spreadSheet, worksheetPart);
            }
            else
            {
                WriteRowsFromKeys(rowData, rowPointers, rowNum, out maxWidth, spreadSheet, worksheetPart);
            }

            // Save to the memory stream
            SpreadsheetWriter.Save(spreadSheet);
            spreadSheet.Close();
            spreadSheet.Dispose();
            return(xmlStream);
        }
Exemplo n.º 5
0
        private void ClearSpreadsheetDocument(SpreadsheetDocument Document)
        {
            Document.WorkbookPart.DeletePart(Document.WorkbookPart.CalculationChainPart);

            foreach (Sheet curSheet in Document.WorkbookPart.Workbook.Sheets)
            {
                WorksheetPart workSheetPart = SpreadsheetReader.GetWorksheetPartByName(Document, curSheet.Name);
                SheetData     sheetData     = workSheetPart.Worksheet.Elements <SheetData>().First();
                if (!sheetData.HasChildren)
                {
                    MergeCells p = workSheetPart.Worksheet.GetFirstChild <MergeCells>();
                    if (p != null)
                    {
                        p.Remove();
                    }
                }
            }
        }
Exemplo n.º 6
0
        private UInt32 SetValueFromDataTable(DataTable dataTable, SpreadsheetDocument RecultDoc, SpreadsheetDocument TemleytDoc, UInt32 CurOffset)
        {
            UInt32 newOffset = 0;
            string RangeName = dataTable.TableName;
            //   SpreadsheetDocument TemleytDoc = SpreadsheetDocument.Open(Temleyt, true);

            DefinedName DefName = SpreadsheetReader.GetDefinedName(TemleytDoc, RangeName);

            if (DefName != null)
            {
                if (DefName.Text.IndexOf("#REF!") < 0)
                {
                    string definedName     = DefName.Text;
                    string WorkSheetName   = definedName.Substring(0, definedName.IndexOf("!")).TrimEnd('\'').TrimStart('\'');
                    string Range           = definedName.Substring(definedName.IndexOf("!") + 1);
                    UInt32 startRowInRange = FirstRowInRange(Range);
                    UInt32 endRowInRange   = LastRowInRange(Range);
                    UInt32 serviceRow      = 1;
                    UInt32 CountRangeData  = (endRowInRange - startRowInRange + 1) - serviceRow;

                    WorksheetPart TemleytWorkSheet = SpreadsheetReader.GetWorksheetPartByName(TemleytDoc, WorkSheetName);

                    List <Row> ListRowInRange = TemleytWorkSheet.Worksheet.Elements <SheetData>().First().Elements <Row>()
                                                .Where(r => r.RowIndex >= startRowInRange && r.RowIndex < endRowInRange).ToList();
                    List <Row> ListRowBottom = TemleytWorkSheet.Worksheet.Elements <SheetData>().First().Elements <Row>()
                                               .Where(r => r.RowIndex > endRowInRange).ToList();
                    List <Row> ServiceRow = TemleytWorkSheet.Worksheet.Elements <SheetData>().First().Elements <Row>()
                                            .Where(r => r.RowIndex == endRowInRange).ToList();

                    List <MergeCell> ListMCellInRange = new List <MergeCell>();
                    List <MergeCell> ListMCellBottom  = new List <MergeCell>();
                    MergeCells       mergeCells       = TemleytWorkSheet.Worksheet.GetFirstChild <MergeCells>();
                    if (mergeCells != null)
                    {
                        ListMCellInRange = TemleytWorkSheet.Worksheet.Elements <MergeCells>().First().Elements <MergeCell>()
                                           .Where(r => SpreadsheetReader.RowFromReference(FirstRefFromRange(r.Reference.Value)) >= startRowInRange && SpreadsheetReader.RowFromReference(LastRefFromRange(r.Reference.Value)) <= endRowInRange).ToList();
                        ListMCellBottom = TemleytWorkSheet.Worksheet.Elements <MergeCells>().First().Elements <MergeCell>()
                                          .Where(r => SpreadsheetReader.RowFromReference(FirstRefFromRange(r.Reference.Value)) > endRowInRange).ToList();
                    }
                    UInt32 NewStartRow = startRowInRange + CurOffset;

                    WorksheetPart   RecultWorkSheetPart = SpreadsheetReader.GetWorksheetPartByName(RecultDoc, WorkSheetName);
                    WorksheetWriter RecultWriter        = new WorksheetWriter(RecultDoc, RecultWorkSheetPart);
                    DeleteRows(RecultDoc, RecultWriter, NewStartRow, CountRangeData + serviceRow);
                    if (ListRowBottom.Count() > 0)
                    {
                        DeleteRows(RecultDoc, RecultWriter, NewStartRow, ListRowBottom.Last().RowIndex.Value + CurOffset - NewStartRow + 1);
                    }

                    MaximumCount = dataTable.Rows.Count;

                    UInt32 InsideOffset = NewStartRow;
                    foreach (DataRow dataRow in dataTable.Rows)
                    {
                        UInt32 Offset = (InsideOffset - NewStartRow) + CurOffset;
                        if (dataRow.RowState.ToString() != "Deleted")
                        {
                            AppendRows(dataRow, ListRowInRange, ListMCellInRange, RecultWorkSheetPart, Offset);
                            InsideOffset += CountRangeData;
                        }
                        ++ProgressValue;
                    }

                    newOffset = (InsideOffset - NewStartRow - CountRangeData) + CurOffset;
                    AppendRows(null, ListRowBottom, ListMCellBottom, RecultWorkSheetPart, newOffset);

                    DefName      = SpreadsheetReader.GetDefinedName(RecultDoc, RangeName);
                    definedName  = DefName.Text;
                    DefName.Text = definedName.Substring(0, definedName.LastIndexOf('$') + 1) + InsideOffset.ToString();

                    //   DefName.Text = definedName.Substring(0, definedName.IndexOf("!") + 1) + '$' + SpreadsheetReader.ColumnFromReference(FirstRefFromRange(Range)) + '$' + NewStartRow.ToString() + ":$" + SpreadsheetReader.ColumnFromReference(LastRefFromRange(Range)) + '$' + StartClone.ToString();

                    //Обновляем ссилки начала и конца рабочих областей DefinedNames
                    foreach (DefinedName CurdefName in RecultDoc.WorkbookPart.Workbook.DefinedNames)
                    {
                        if (CurdefName.Text.IndexOf("#REF!") < 0)
                        {
                            //   if (CurdefName.Text == definedName)//Текущая облать DefinedName
                            //    {
                            //         CurdefName.Text = definedName.Substring(0, definedName.IndexOf("!") + 1) + '$' + SpreadsheetReader.ColumnFromReference(FirstRefFromRange(Range)) + '$' + NewStartRow.ToString() + ":$" + SpreadsheetReader.ColumnFromReference(LastRefFromRange(Range)) + '$' + StartOffset.ToString();
                            //      }
                            //     else //Все остальные DefinedName текущего листа Sheet
                            //      {
                            string strDefName = CurdefName.Text;
                            string sheetName  = strDefName.Substring(0, strDefName.IndexOf("!")).Trim('\'');
                            string range      = strDefName.Substring(strDefName.IndexOf("!") + 1);
                            if (sheetName == WorkSheetName)
                            {
                                string firstRefInRange = FirstRefFromRange(range);
                                string lastRefInRange  = LastRefFromRange(range);
                                UInt32 firstRow        = SpreadsheetReader.RowFromReference(firstRefInRange);
                                UInt32 lastRow         = SpreadsheetReader.RowFromReference(lastRefInRange);
                                if (firstRow + newOffset > InsideOffset + CurOffset)
                                {
                                    CurdefName.Text = strDefName.Substring(0, strDefName.IndexOf("!") + 1) + '$' + SpreadsheetReader.ColumnFromReference(firstRefInRange) + '$' + (firstRow + newOffset).ToString() + ":$" + SpreadsheetReader.ColumnFromReference(lastRefInRange) + '$' + (lastRow + newOffset).ToString();
                                }
                            }

                            //          }
                        }
                    }
                }
            }
            //   TemleytDoc.Close();
            return(newOffset);
        }
Exemplo n.º 7
0
 public bool SetWorksheet(string sheetName)
 {
     _worksheet = SpreadsheetReader.GetWorksheetPartByName(_doc, sheetName);
     return(_worksheet != null);
 }