Esempio n. 1
0
        public SpreadBook(SpreadsheetDocument workbook)
        {
            this.doc     = workbook;
            CurrentSheet = 0;

            // IEnumerable<WorksheetPart> WorksheetParts { get; }
        }
        static Dictionary <int, CellFormat> ReadStyleSheet(Spreadsheet document, Package.SpreadsheetDocument importDocument)
        {
            Dictionary <int, CellFormat> cellFormats = new Dictionary <int, CellFormat>();

            var importedStyleSheet = new Excel.Stylesheet();

            if (importDocument.WorkbookPart.WorkbookStylesPart != null)
            {
                importedStyleSheet.Load(importDocument.WorkbookPart.WorkbookStylesPart);
            }

            int index = 0;

            foreach (Excel.CellFormat item in importedStyleSheet.CellFormats)
            {
                if (item.ApplyFill != null && item.ApplyFill.Value && item.FillId != null)
                {
                    int fillID = (int)(uint)item.FillId.Value;
                    var fill   = (Excel.Fill)importedStyleSheet.Fills.ElementAt(fillID);

                    if (fill.PatternFill.ForegroundColor != null)
                    {
                        string fillColor = fill.PatternFill.ForegroundColor.Rgb;
                        cellFormats.Add(index, document.CellFormats().CellFormat(fillColor));
                    }
                }
                index++;
            }
            return(cellFormats);
        }
Esempio n. 3
0
        protected override DataTable ReadFile()
        {
            string    outputPath = CopyFileToAppData();
            DataTable dataTable  = new DataTable();

            using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(outputPath, false))
            {
                DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = GetWorksheetPartByName(spreadsheetDocument, _sheetName);

                DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheetPart.Worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>().First();
                IEnumerable <Row> rows = sheetData.Descendants <Row>();
                foreach (Cell cell in rows.ElementAt(0))
                {
                    dataTable.Columns.Add(GetCellValue(spreadsheetDocument, cell));
                }
                for (int i = 1; i < rows.Count() - 1; i++)
                {
                    DataRow tempRow = dataTable.NewRow();
                    for (int j = 0; j < rows.ElementAt(i).Descendants <Cell>().Count(); j++)
                    {
                        tempRow[j] = GetCellValue(spreadsheetDocument, rows.ElementAt(i).Descendants <Cell>().ElementAt(j));
                    }
                    dataTable.Rows.Add(tempRow);
                }
            }
            return(dataTable);
        }
Esempio n. 4
0
        /// <summary>
        /// Sets a column heading to a cell
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="worksheet">Worksheet to use</param>
        /// <param name="columnIndex">Index of the column</param>
        /// <param name="rowIndex">Index of the row</param>
        /// <param name="stringValue">String value to set</param>
        /// <param name="useSharedString">Use shared strings? If true and the string isn't found in shared strings, it will be added</param>
        /// <param name="save">Save the worksheet</param>
        /// <returns>True if succesful</returns>
        public static bool SetColumnHeadingValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, string stringValue, bool useSharedString, bool save = true)
        {
            string columnValue = stringValue;

            DocumentFormat.OpenXml.Spreadsheet.CellValues cellValueType;

            // Add the shared string if necessary
            if (useSharedString)
            {
                if (ExcelProc.IndexOfSharedString(spreadsheet, stringValue) == -1)
                {
                    ExcelProc.AddSharedString(spreadsheet, stringValue, true);
                }
                columnValue   = ExcelProc.IndexOfSharedString(spreadsheet, stringValue).ToString();
                cellValueType = DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString;
            }
            else
            {
                cellValueType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
            }

            return(SetCellValue(spreadsheet, worksheet, columnIndex, 1, cellValueType, columnValue, 4, save));

            //return SetCellValue(spreadsheet, worksheet, columnIndex, 2, cellValueType, columnValue, 4, save);
        }
Esempio n. 5
0
        /// <summary>
        /// Adds a predefined style from the given xml
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="xml">Style definition as xml</param>
        /// <returns>True if succesful</returns>
        public static bool AddPredefinedStyles(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string xml)
        {
            spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet.InnerXml = xml;
            spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();

            return(true);
        }
Esempio n. 6
0
 public void Dispose()
 {
     if (doc != null)
     {
         doc.Dispose();
     }
     doc = null;    // dispose
 }
Esempio n. 7
0
        /// <summary>
        /// Adds a list of strings to the shared strings table.
        /// </summary>
        /// <param name="spreadsheet">The spreadsheet</param>
        /// <param name="stringList">Strings to add</param>
        /// <returns></returns>
        public static bool AddSharedStrings(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, System.Collections.Generic.List <string> stringList)
        {
            foreach (string item in stringList)
            {
                ExcelProc.AddSharedString(spreadsheet, item, false);
            }
            spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();

            return(true);
        }
Esempio n. 8
0
        /// <summary>
        /// Sets a cell value with double number
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="worksheet">Worksheet to use</param>
        /// <param name="columnIndex">Index of the column</param>
        /// <param name="rowIndex">Index of the row</param>
        /// <param name="doubleValue">Double value</param>
        /// <param name="styleIndex">Style to use</param>
        /// <param name="save">Save the worksheet</param>
        /// <returns>True if succesful</returns>
        public static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, double doubleValue, uint?styleIndex, bool save = true)
        {
#if EN_US_CULTURE
            string columnValue = doubleValue.ToString();
#else
            string columnValue = doubleValue.ToString().Replace(",", ".");
#endif

            return(SetCellValue(spreadsheet, worksheet, columnIndex, rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues.Number, columnValue, styleIndex, save));
        }
Esempio n. 9
0
 public bool Open(String excelFileName)
 {
     _fileName = excelFileName;
     byte[] bytes = System.IO.File.ReadAllBytes(excelFileName);
     _stream          = new MemoryStream(bytes);
     _excel_file      = SpreadsheetDocument.Open(_stream, false);
     workbookPart     = _excel_file.WorkbookPart;
     sharedStringItem = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>();
     return(true);
 }
Esempio n. 10
0
        public Stream CreatePackage()
        {
            MemoryStream stream = new MemoryStream();

            using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument package = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
            {
                CreateParts(package);
            }
            return(stream);
        }
Esempio n. 11
0
 /// <summary>
 /// Class constructor
 /// </summary>
 /// <param name="document">Document to perform operations on</param>
 /// <param name="fullName">Full path name of document</param>
 public SpreadsheetDocument(OpenXmlSDK.SpreadsheetDocument document, string fullName)
     : base()
 {
     Document         = document;
     FullName         = fullName;
     InnerContent     = new SpreadsheetDocumentManager(this);
     Worksheets       = new WorksheetAccessor(this);
     Chartsheets      = new ChartsheetAccessor(this);
     CustomProperties = new CustomPropertiesAccesor(this);
     Tables           = new SpreadSheetTableAccesor(this);
     Style            = new SpreadSheetStyleAccessor(this);
 }
Esempio n. 12
0
    public void CreateExcel(string xlFilePath)
    {
        FileInfo xlFileInfo = new FileInfo(xlFilePath);

        if (xlFileInfo.Exists)
        {
            xlFileInfo.Delete();
        }

        xlDoc           = SpreadsheetDocument.Create(xlFilePath, SpreadsheetDocumentType.Workbook);
        wbPart          = xlDoc.AddWorkbookPart();
        wbPart.Workbook = new Workbook();
    }
Esempio n. 13
0
 protected virtual void Dispose(bool disposing)
 {
     if (disposing)
     {
         if (_excel_file != null)
         {
             _excel_file.Close();
             _excel_file.Dispose();
             _excel_file = null;
         }
         _stream?.Dispose();
     }
 }
Esempio n. 14
0
        //private int maxRowReadCount = 3;

        public ExcelOLEClient()
        {
            try
            {
                _excel_file      = null;
                workbookPart     = null;
                sharedStringItem = null;
                //_excel_file.Visible = false;
                //_excel_file.DisplayAlerts = false; // 저장할 것인가 확인하지 않도록 설정
                //workbooks = _excel_file.Worksheets;
            }
            catch (Exception)
            {
                //Dispose();
            }
        }
Esempio n. 15
0
 public void ReadExcelFromStream(Stream stream, bool isEditable)
 {
     try
     {
         m_objSpreadsheetDocument = SpreadsheetDocument.Open(stream, isEditable);
         m_objWorkbookPart        = m_objSpreadsheetDocument.WorkbookPart;
         m_objWorksheetPart       = m_objWorkbookPart.WorksheetParts.First();
         m_objWorksheet           = m_objWorksheetPart.Worksheet;
         m_objSheetData           = m_objWorksheet.Elements <SheetData>().First();
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.Message);
         this.DisposeRead();
         throw;
     }
 }
Esempio n. 16
0
        /// <summary>
        /// Returns the index of a shared string.
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="stringItem">String to search for</param>
        /// <returns>Index of a shared string. -1 if not found</returns>
        public static int IndexOfSharedString(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string stringItem)
        {
            DocumentFormat.OpenXml.Spreadsheet.SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
            bool found = false;
            int  index = 0;

            foreach (DocumentFormat.OpenXml.Spreadsheet.SharedStringItem sharedString in sharedStringTable.Elements <DocumentFormat.OpenXml.Spreadsheet.SharedStringItem>())
            {
                if (sharedString.InnerText == stringItem)
                {
                    found = true;
                    break;
                }
                index++;
            }

            return(found ? index : -1);
        }
Esempio n. 17
0
        /// <summary>
        /// Add a single string to shared strings table.
        /// Shared string table is created if it doesn't exist.
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="stringItem">string to add</param>
        /// <param name="save">Save the shared string table</param>
        /// <returns></returns>
        public static bool AddSharedString(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string stringItem, bool save = true)
        {
            DocumentFormat.OpenXml.Spreadsheet.SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;

            if (0 == sharedStringTable.Where(item => item.InnerText == stringItem).Count())
            {
                sharedStringTable.AppendChild(
                    new DocumentFormat.OpenXml.Spreadsheet.SharedStringItem(
                        new DocumentFormat.OpenXml.Spreadsheet.Text(stringItem)));

                // Save the changes
                if (save)
                {
                    sharedStringTable.Save();
                }
            }

            return(true);
        }
        static void ReadSheet(Spreadsheet document, Package.SpreadsheetDocument importDocument, Excel.Sheet importSheet,
                              Dictionary <string, string> sharedStringTable, Dictionary <int, CellFormat> cellFormats)
        {
            Sheet sheet           = document.Sheets.Sheet(importSheet.Name);
            var   importWorksheet = new Excel.Worksheet();

            importWorksheet.Load((Package.WorksheetPart)importDocument.WorkbookPart.GetPartById(importSheet.Id));
            var sheetData = (Excel.SheetData)importWorksheet.Elements <Excel.SheetData>().First();

            foreach (var importColumn in sheetData.Elements <Excel.Column>())
            {
                ReadColumn();
            }

            foreach (var importRow in sheetData.Elements <Excel.Row>())
            {
                ReadRow(sheet, importRow, sharedStringTable, cellFormats);
            }
        }
Esempio n. 19
0
        private static OpenXmlPackaging.WorksheetPart GetWorksheetPartByName(
            DocumentFormat.OpenXml.Packaging.SpreadsheetDocument document, string sheetName)
        {
            IEnumerable <OpenXmlSpread.Sheet> sheets =
                document.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>()
                .Elements <OpenXmlSpread.Sheet>().Where(s => s.Name == sheetName);

            if (sheets.Count() == 0)
            {
                throw new Exception(String.Format("A planilha {0} não existe", sheetName));
            }

            string relationshipId = sheets.First().Id.Value;

            DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart =
                (DocumentFormat.OpenXml.Packaging.WorksheetPart)
                document.WorkbookPart.GetPartById(relationshipId);
            return(worksheetPart);
        }
        static Dictionary <string, string> ReadSharedStringTable(Package.SpreadsheetDocument importDocument)
        {
            var result = new Dictionary <string, string>();

            var importSharedStringTable = new Excel.SharedStringTable();

            if (importDocument.WorkbookPart.SharedStringTablePart != null)
            {
                importSharedStringTable.Load(importDocument.WorkbookPart.SharedStringTablePart);
            }

            int index = 0;

            foreach (var item in importSharedStringTable.Elements <Excel.SharedStringItem>())
            {
                result.Add(index.ToString(), item.Text != null ? item.Text.Text : null);
                index++;
            }
            return(result);
        }
Esempio n. 21
0
        public virtual void Open(string fileName)
        {
            Dispose();
            using (var stream = File.OpenRead(fileName))
            {
                // {"The supplied data appears to be in the Office 2007+ XML. You are calling the part
                // of POI that deals with OLE2 Office Documents. You need to call a different part of POI
                // to process this data (eg XSSF instead of HSSF)"}

                FileName = fileName;

                // System.IO.Packaging.Package
                doc = SpreadsheetDocument.Open(stream, false);
            }

            CurrentSheet  = 0;
            CurrentRow    = 0;
            CurrentColumn = 0;
            Name          = Path.GetFileNameWithoutExtension(FileName);
        }
Esempio n. 22
0
        /// <summary>
        /// Creates the workbook
        /// </summary>
        /// <returns>Spreadsheet created</returns>
        public static DocumentFormat.OpenXml.Packaging.SpreadsheetDocument CreateWorkbook(string fileName)
        {
            DocumentFormat.OpenXml.Packaging.SpreadsheetDocument   spreadSheet = null;
            DocumentFormat.OpenXml.Packaging.SharedStringTablePart sharedStringTablePart;
            DocumentFormat.OpenXml.Packaging.WorkbookStylesPart    workbookStylesPart;

            try
            {
                // Create the Excel workbook
                spreadSheet = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Create(fileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook, false);

                // Create the parts and the corresponding objects

                // Workbook
                spreadSheet.AddWorkbookPart();
                spreadSheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                spreadSheet.WorkbookPart.Workbook.Save();

                // Shared string table
                sharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.SharedStringTablePart>();
                sharedStringTablePart.SharedStringTable = new DocumentFormat.OpenXml.Spreadsheet.SharedStringTable();
                sharedStringTablePart.SharedStringTable.Save();

                // Sheets collection
                spreadSheet.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
                spreadSheet.WorkbookPart.Workbook.Save();

                // Stylesheet
                workbookStylesPart            = spreadSheet.WorkbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.WorkbookStylesPart>();
                workbookStylesPart.Stylesheet = new DocumentFormat.OpenXml.Spreadsheet.Stylesheet();
                workbookStylesPart.Stylesheet.Save();
            }
            catch (System.Exception exception)
            {
                // System.Windows.MessageBox.Show(exception.Message, "Excel OpenXML basics", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Hand);
                throw exception;
            }


            return(spreadSheet);
        }
Esempio n. 23
0
        /// <summary>
        /// Adds a new worksheet to the workbook
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="name">Name of the worksheet</param>
        /// <returns>True if succesful</returns>
        public static bool AddWorksheet(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string name)
        {
            DocumentFormat.OpenXml.Spreadsheet.Sheets      sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
            DocumentFormat.OpenXml.Spreadsheet.Sheet       sheet;
            DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart;

            // Add the worksheetpart
            worksheetPart           = spreadsheet.WorkbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.WorksheetPart>();
            worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
            worksheetPart.Worksheet.Save();

            // Add the sheet and make relation to workbook
            sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
            {
                Id      = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = (uint)(spreadsheet.WorkbookPart.Workbook.Sheets.Count() + 1),
                Name    = name
            };
            sheets.Append(sheet);
            spreadsheet.WorkbookPart.Workbook.Save();

            return(true);
        }
Esempio n. 24
0
 public void DisposeRead()
 {
     if (m_objWorksheet != null)
     {
         Marshal.FinalReleaseComObject(m_objWorksheet);
     }
     if (m_objWorksheetPart != null)
     {
         Marshal.FinalReleaseComObject(m_objWorksheetPart);
     }
     if (m_objWorkbookPart != null)
     {
         Marshal.FinalReleaseComObject(m_objWorkbookPart);
     }
     if (m_objSpreadsheetDocument != null)
     {
         Marshal.FinalReleaseComObject(m_objSpreadsheetDocument);
         int intGeneration = GC.GetGeneration(m_objSpreadsheetDocument);
         m_objSpreadsheetDocument = null;
         GC.Collect(intGeneration);
     }
     GC.Collect();
     GC.Collect();
 }
Esempio n. 25
0
        /// <summary>
        /// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="worksheet">Worksheet to use</param>
        /// <param name="columnIndex">Index of the column</param>
        /// <param name="rowIndex">Index of the row</param>
        /// <param name="valueType">Type of the value</param>
        /// <param name="value">The actual value</param>
        /// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param>
        /// <param name="save">Save the worksheet?</param>
        /// <returns>True if succesful</returns>
        private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint?styleIndex, bool save = true)
        {
            DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>();
            DocumentFormat.OpenXml.Spreadsheet.Row       row;
            DocumentFormat.OpenXml.Spreadsheet.Row       previousRow = null;
            DocumentFormat.OpenXml.Spreadsheet.Cell      cell;
            DocumentFormat.OpenXml.Spreadsheet.Cell      previousCell = null;
            DocumentFormat.OpenXml.Spreadsheet.Columns   columns;
            DocumentFormat.OpenXml.Spreadsheet.Column    previousColumn = null;
            string cellAddress = ExcelProc.ColumnNameFromIndex(columnIndex) + rowIndex;

            // Check if the row exists, create if necessary
            if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First();
            }
            else
            {
                row = new DocumentFormat.OpenXml.Spreadsheet.Row()
                {
                    RowIndex = rowIndex
                };
                //sheetData.Append(row);
                for (uint counter = rowIndex - 1; counter > 0; counter--)
                {
                    previousRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault();
                    if (previousRow != null)
                    {
                        break;
                    }
                }
                sheetData.InsertAfter(row, previousRow);
            }

            // Check if the cell exists, create if necessary
            if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0)
            {
                cell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First();
            }
            else
            {
                // Find the previous existing cell in the row
                for (uint counter = columnIndex - 1; counter > 0; counter--)
                {
                    previousCell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == ExcelProc.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault();
                    if (previousCell != null)
                    {
                        break;
                    }
                }
                cell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
                {
                    CellReference = cellAddress
                };
                row.InsertAfter(cell, previousCell);
            }

            // Check if the column collection exists
            columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
            if (columns == null)
            {
                columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0);
            }
            // Check if the column exists
            if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0)
            {
                // Find the previous existing column in the columns
                for (uint counter = columnIndex - 1; counter > 0; counter--)
                {
                    previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault();
                    if (previousColumn != null)
                    {
                        break;
                    }
                }
                columns.InsertAfter(
                    new DocumentFormat.OpenXml.Spreadsheet.Column()
                {
                    Min         = columnIndex,
                    Max         = columnIndex,
                    CustomWidth = true,
                    Width       = 9
                }, previousColumn);
            }

            // Add the value
            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value);
            if (styleIndex != null)
            {
                cell.StyleIndex = styleIndex;
            }
            if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date)
            {
                cell.DataType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType);
            }

            if (save)
            {
                worksheet.Save();
            }

            return(true);
        }
Esempio n. 26
0
        /// <summary>
        /// Sets a cell value with boolean value
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <param name="worksheet">Worksheet to use</param>
        /// <param name="columnIndex">Index of the column</param>
        /// <param name="rowIndex">Index of the row</param>
        /// <param name="boolValue">Boolean value</param>
        /// <param name="styleIndex">Style to use</param>
        /// <param name="save">Save the worksheet</param>
        /// <returns>True if succesful</returns>
        public static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, bool boolValue, uint?styleIndex, bool save = true)
        {
            string columnValue = boolValue ? "1" : "0";

            return(SetCellValue(spreadsheet, worksheet, columnIndex, rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues.Boolean, columnValue, styleIndex, save));
        }
Esempio n. 27
0
 /// <summary>
 /// Creates a new OpenXmlPackage on the given stream.
 /// </summary>
 /// <param name="stream">The stream on which the concrete OpenXml package will be created.</param>
 /// <returns>A new instance of OpenXmlPackage.</returns>
 protected override OpenXmlPackage CreateClone(Stream stream)
 {
     return(SpreadsheetDocument.Create(stream, DocumentType, OpenSettings.AutoSave));
 }
Esempio n. 28
0
 /// <summary>
 /// Creates a new instance of OpenXmlPackage on the specified instance
 /// of Package.
 /// </summary>
 /// <param name="package">The specified instance of Package.</param>
 /// <returns>A new instance of OpenXmlPackage.</returns>
 protected override OpenXmlPackage CreateClone(Package package)
 {
     return(SpreadsheetDocument.Create(package, DocumentType, OpenSettings.AutoSave));
 }
Esempio n. 29
0
        /// <summary>
        /// Adds the basic styles to the workbook
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <returns>True if succesful</returns>
        public static bool AddBasicStyles(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet)
        {
            DocumentFormat.OpenXml.Spreadsheet.Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet;

            // Numbering formats (x:numFmts)
            stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.NumberingFormats>(new DocumentFormat.OpenXml.Spreadsheet.NumberingFormats(), 0);
            // Currency
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.NumberingFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.NumberingFormat>(
                new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat()
            {
                NumberFormatId = 164,
                FormatCode     = "#,##0.00"
                                 + "\\ \"" + System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencySymbol + "\""
            }, 0);

            // Fonts (x:fonts)
            stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fonts>(new DocumentFormat.OpenXml.Spreadsheet.Fonts(), 1);
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fonts>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Font>(
                new DocumentFormat.OpenXml.Spreadsheet.Font()
            {
                FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize()
                {
                    Val = 11
                },
                FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName()
                {
                    Val = "Calibri"
                }
            }, 0);

            // Fills (x:fills)
            stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fills>(new DocumentFormat.OpenXml.Spreadsheet.Fills(), 2);
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fills>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fill>(
                new DocumentFormat.OpenXml.Spreadsheet.Fill()
            {
                PatternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill()
                {
                    PatternType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.PatternValues>()
                    {
                        Value = DocumentFormat.OpenXml.Spreadsheet.PatternValues.None
                    }
                }
            }, 0);
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fills>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fill>(
                new DocumentFormat.OpenXml.Spreadsheet.Fill()
            {
                PatternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill()
                {
                    PatternType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.PatternValues>()
                    {
                        Value = DocumentFormat.OpenXml.Spreadsheet.PatternValues.Gray125
                    }
                }
            }, 1);

            // Borders (x:borders)
            stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Borders>(new DocumentFormat.OpenXml.Spreadsheet.Borders(), 3);
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Borders>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Border>(
                new DocumentFormat.OpenXml.Spreadsheet.Border()
            {
                LeftBorder     = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(),
                RightBorder    = new DocumentFormat.OpenXml.Spreadsheet.RightBorder(),
                TopBorder      = new DocumentFormat.OpenXml.Spreadsheet.TopBorder(),
                BottomBorder   = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(),
                DiagonalBorder = new DocumentFormat.OpenXml.Spreadsheet.DiagonalBorder()
            }, 0);

            // Cell style formats (x:CellStyleXfs)
            stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>(new DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats(), 4);
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
                new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
            {
                NumberFormatId = 0,
                FontId         = 0,
                FillId         = 0,
                BorderId       = 0
            }, 0);

            // Cell formats (x:CellXfs)
            stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormats>(new DocumentFormat.OpenXml.Spreadsheet.CellFormats(), 5);
            // General text
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
                new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
            {
                FormatId       = 0,
                NumberFormatId = 0
            }, 0);
            // Date
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
                new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
            {
                ApplyNumberFormat = true,
                FormatId          = 0,
                NumberFormatId    = 22,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0
            },
                1);
            // Currency
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
                new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
            {
                ApplyNumberFormat = true,
                FormatId          = 0,
                NumberFormatId    = 164,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0
            },
                2);
            // Percentage
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
                new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
            {
                ApplyNumberFormat = true,
                FormatId          = 0,
                NumberFormatId    = 10,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0
            },
                3);

            stylesheet.Save();

            return(true);
        }
Esempio n. 30
0
 /// <summary>
 /// Opens the cloned OpenXml package on the given file.
 /// </summary>
 /// <param name="path">The path and file name of the target OpenXml package.</param>
 /// <param name="isEditable">In ReadWrite mode. False for Read only mode.</param>
 /// <param name="openSettings">The advanced settings for opening a document.</param>
 /// <returns>A new instance of OpenXmlPackage.</returns>
 protected override OpenXmlPackage OpenClone(string path, bool isEditable, OpenSettings openSettings)
 {
     return(SpreadsheetDocument.Open(path, isEditable, openSettings));
 }