コード例 #1
0
        //new
        public void ExportDSToExcel(DataSet ds, string destination)
        {
            using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = workbook.AddWorkbookPart();
                workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

                uint sheetId = 1;

                foreach (DataTable table in ds.Tables)
                {
                    var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                    var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                    sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                    string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                    if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                    {
                        sheetId =
                            sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                    sheets.Append(sheet);

                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                    List<String> columns = new List<string>();
                    foreach (DataColumn column in table.Columns)
                    {
                        columns.Add(column.ColumnName);

                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                        headerRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(headerRow);

                    foreach (DataRow dsrow in table.Rows)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        foreach (String col in columns)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                            newRow.AppendChild(cell);
                        }

                        sheetData.AppendChild(newRow);
                    }
                }
            }
        }
コード例 #2
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;
        }
コード例 #3
0
ファイル: Integrator.cs プロジェクト: qreal/tools
        /*private Row createContentRow(
        DataRow dataRow,
        int rowIndex)
        {
          Row row = new Row
          {
        RowIndex = (UInt32)rowIndex
          };

          for (int i = 0; i < dataRow.Table.Columns.Count; i++)
          {
        Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
        row.AppendChild(dataCell);
          }
          return row;
        }*/
        private static SpreadsheetDocument CreateDoc(SheetData data)
        {
            using (SpreadsheetDocument doc = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
              {
            doc.AddWorkbookPart();
            doc.WorkbookPart.Workbook = new Workbook();
            doc.WorkbookPart.Workbook.Save();
            doc.WorkbookPart.Workbook.Sheets = new Sheets();
            doc.WorkbookPart.Workbook.Save();
            Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild<Sheets>();

            // Add the worksheetpart
            WorksheetPart worksheetPart = doc.WorkbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(data);
            worksheetPart.Worksheet.Save();

            // Add the sheet and make relation to workbook
            Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
            {
              Id = doc.WorkbookPart.GetIdOfPart(worksheetPart),
              SheetId = (uint)(doc.WorkbookPart.Workbook.Sheets.Count() + 1),
              Name = "sheet"
            };
            sheets.Append(sheet);
            doc.WorkbookPart.Workbook.Save();
            return doc;
              }
        }
コード例 #4
0
        public static void ExportDSToExcel(System.Data.DataSet ds, string destination)
        {
            using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = workbook.AddWorkbookPart();
                workbook.WorkbookPart.Workbook        = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

                uint sheetId = 1;

                foreach (System.Data.DataTable table in ds.Tables)
                {
                    var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
                    var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                    sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                    string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                    if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                    {
                        sheetId =
                            sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                    {
                        Id = relationshipId, SheetId = sheetId, Name = table.TableName
                    };
                    sheets.Append(sheet);

                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                    List <String> columns = new List <string>();
                    foreach (System.Data.DataColumn column in table.Columns)
                    {
                        columns.Add(column.ColumnName);

                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                        headerRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(headerRow);

                    foreach (System.Data.DataRow dsrow in table.Rows)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        foreach (String col in columns)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                            newRow.AppendChild(cell);
                        }

                        sheetData.AppendChild(newRow);
                    }
                }
            }
        }
コード例 #5
0
ファイル: OpenXMLExtentions.cs プロジェクト: xyyhqq/eXpand
 public static WorksheetPart WorkSheet(this SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Sheet oXmlSheet)
 {
     return(spreadsheet.WorkbookPart.GetPartById(oXmlSheet.Id) as WorksheetPart);
 }
コード例 #6
0
        static public bool ExportarExcel(DataSet ds, string archivo)
        {
            try
            {
                using (var workbook = SpreadsheetDocument.Create(archivo, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
                {
                    var workbookPart = workbook.AddWorkbookPart();

                    workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                    workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

                    foreach (System.Data.DataTable table in ds.Tables)
                    {
                        var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
                        var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                        sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                        DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                        string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                        uint sheetId = 1;
                        if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                        {
                            sheetId =
                                sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                        }

                        DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                        {
                            Id = relationshipId, SheetId = sheetId, Name = table.TableName
                        };
                        sheets.Append(sheet);

                        DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                        List <String> columns = new List <string>();
                        foreach (System.Data.DataColumn column in table.Columns)
                        {
                            columns.Add(column.ColumnName);

                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                            headerRow.AppendChild(cell);
                        }


                        sheetData.AppendChild(headerRow);

                        foreach (System.Data.DataRow dsrow in table.Rows)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                            DateTime val;
                            string   dato;
                            foreach (String col in columns)
                            {
                                DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                dato          = dsrow[col].ToString();

                                if (dato.Length >= 10 && DateTime.TryParse(dato, out val) == true)
                                {
                                    dato = val.ToString("yyyy/MM/dd");
                                }
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dato); //
                                newRow.AppendChild(cell);
                            }

                            sheetData.AppendChild(newRow);
                        }
                    }
                }
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

                excel.Workbooks.Open(archivo);
                excel.Visible = true;
                return(true);
            }
            catch (Exception ex)
            {
                Utiles.WriteErrorLog(ex.Message);
                return(false);
            }
        }
コード例 #7
0
        static public bool ExportaListaAExcel2 <T>(List <T> lista, string archivo, string hoja)
        {
            try
            {
                using (var workbook = SpreadsheetDocument.Create(archivo, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
                {
                    var workbookPart = workbook.AddWorkbookPart();
                    workbook.WorkbookPart.Workbook        = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                    workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
                    var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
                    var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                    sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                    string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                    uint sheetId = 1;
                    if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                    {
                        sheetId =
                            sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                    {
                        Id = relationshipId, SheetId = sheetId, Name = hoja
                    };
                    sheets.Append(sheet);

                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                    List <String>        columns = new List <string>();
                    IList <PropertyInfo> campos  = new List <PropertyInfo>(typeof(T).GetProperties());
                    foreach (PropertyInfo campo in campos)
                    {
                        columns.Add(campo.Name);
                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(campo.Name);
                        headerRow.AppendChild(cell);
                    }
                    sheetData.AppendChild(headerRow);
                    foreach (T row in lista)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        DateTime val;
                        string   dato;
                        foreach (String col in columns)
                        {
                            PropertyInfo campo = campos.ToList().Find(x => x.Name == col);
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            object obj = campo.GetValue(row, null);
                            if (obj == null)
                            {
                                dato = "";
                            }
                            else
                            {
                                dato = obj.ToString();
                            }

                            if (dato.Length >= 10 && DateTime.TryParse(dato, out val) == true)
                            {
                                dato = val.ToString("yyyy/MM/dd");
                            }
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dato); //
                            newRow.AppendChild(cell);
                        }

                        sheetData.AppendChild(newRow);
                    }
                }
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                excel.Workbooks.Open(archivo);
                excel.Visible = true;
                return(true);
            }
            catch (Exception ex)
            {
                Utiles.WriteErrorLog(ex.Message);
                return(false);
            }
        }
コード例 #8
0
        public async override Task ExecuteResultAsync(ActionContext context)
        {
            var response   = context.HttpContext.Request;
            var enumerable = _data as System.Collections.IEnumerable;

            if (enumerable == null)
            {
                throw new ArgumentException("IEnumerable type required");
            }

            byte[] FileContents = null;
            using (MemoryStream mem = new MemoryStream()) {
                using (var workbook = SpreadsheetDocument.Create(mem, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) {
                    var workbookPart = workbook.AddWorkbookPart();
                    workbook.WorkbookPart.Workbook        = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                    workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
                    var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart> ();
                    var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                    sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets> ();
                    string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
                    uint   sheetId        = 1;
                    if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet> ().Count() > 0)
                    {
                        sheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet> ().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                    {
                        Id = relationshipId, SheetId = sheetId, Name = "Sheet1"
                    };
                    sheets.Append(sheet);

                    //add logo
                    string imgPath      = "assets/logo.png";
                    var    drawingsPart = sheetPart.AddNewPart <DrawingsPart> ();

                    if (!sheetPart.Worksheet.ChildElements.OfType <Drawing> ().Any())
                    {
                        sheetPart.Worksheet.Append(new Drawing {
                            Id = sheetPart.GetIdOfPart(drawingsPart)
                        });
                    }

                    if (drawingsPart.WorksheetDrawing == null)
                    {
                        drawingsPart.WorksheetDrawing = new WorksheetDrawing();
                    }

                    var worksheetDrawing = drawingsPart.WorksheetDrawing;

                    var imagePart = drawingsPart.AddImagePart(ImagePartType.Png);

                    using (var stream = new FileStream(imgPath, FileMode.Open)) {
                        imagePart.FeedData(stream);
                    }

                    Bitmap bm = new Bitmap(imgPath);
                    DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
                    var extentsCx = (long)bm.Width * (long)((float)31440 / bm.HorizontalResolution);
                    var extentsCy = (long)bm.Height * (long)((float)31440 / bm.VerticalResolution);
                    bm.Dispose();

                    var colOffset = 2;
                    var rowOffset = 0;
                    int colNumber = 4;
                    int rowNumber = 1;

                    var nvps  = worksheetDrawing.Descendants <DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties> ();
                    var nvpId = nvps.Count() > 0 ?
                                (UInt32Value)worksheetDrawing.Descendants <DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties> ().Max(p => p.Id.Value) + 1 :
                                1U;

                    var oneCellAnchor = new DocumentFormat.OpenXml.Drawing.Spreadsheet.OneCellAnchor(
                        new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker {
                        ColumnId     = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ColumnId((colNumber - 1).ToString()),
                        RowId        = new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowId((rowNumber - 1).ToString()),
                        ColumnOffset = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ColumnOffset(colOffset.ToString()),
                        RowOffset    = new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowOffset(rowOffset.ToString())
                    },
                        new DocumentFormat.OpenXml.Drawing.Spreadsheet.Extent {
                        Cx = extentsCx, Cy = extentsCy
                    },
                        new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture(
                            new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties(
                                new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties {
                        Id = nvpId, Name = "Picture " + nvpId, Description = imgPath
                    },
                                new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties(new DocumentFormat.OpenXml.Drawing.PictureLocks {
                        NoChangeAspect = true
                    })
                                ),
                            new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill(
                                new DocumentFormat.OpenXml.Drawing.Blip {
                        Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print
                    },
                                new DocumentFormat.OpenXml.Drawing.Stretch(new DocumentFormat.OpenXml.Drawing.FillRectangle())
                                ),
                            new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties(
                                new DocumentFormat.OpenXml.Drawing.Transform2D(
                                    new DocumentFormat.OpenXml.Drawing.Offset {
                        X = 0, Y = 0
                    },
                                    new DocumentFormat.OpenXml.Drawing.Extents {
                        Cx = extentsCx, Cy = extentsCy
                    }
                                    ),
                                new DocumentFormat.OpenXml.Drawing.PresetGeometry {
                        Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle
                    }
                                )
                            ),
                        new DocumentFormat.OpenXml.Drawing.Spreadsheet.ClientData()
                        );

                    worksheetDrawing.Append(oneCellAnchor);

                    sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
                    sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
                    sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
                    sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());

                    //company name
                    DocumentFormat.OpenXml.Spreadsheet.Row  company     = new DocumentFormat.OpenXml.Spreadsheet.Row();
                    DocumentFormat.OpenXml.Spreadsheet.Cell cellCompany = new DocumentFormat.OpenXml.Spreadsheet.Cell()
                    {
                        StyleIndex = (UInt32Value)1U
                    };
                    cellCompany.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    cellCompany.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("FOOD, MEDICINE AND HEALTH CARE ADMINISTRATION AND CONTROL AUTHORITY OF ETHIOPIA");
                    company.AppendChild(cellCompany);
                    sheetData.AppendChild(company);

                    sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());

                    //header
                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                    foreach (var column in _columns)
                    {
                        if (!(bool)column["IsVisible"])
                        {
                            continue;
                        }
                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column["Title"].ToString());
                        headerRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(headerRow);

                    foreach (var item in enumerable)
                    {
                        IDictionary <string, object>           row    = (IDictionary <string, object>)item;
                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                        foreach (var header in _columns)
                        {
                            if (!(bool)header["IsVisible"])
                            {
                                continue;
                            }
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;

                            var value = row[header["FieldName"].ToString()];
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value?.ToString()); //
                            newRow.AppendChild(cell);
                        }
                        sheetData.AppendChild(newRow);
                    }

                    //geenrated by
                    sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());

                    DocumentFormat.OpenXml.Spreadsheet.Row  footer     = new DocumentFormat.OpenXml.Spreadsheet.Row();
                    DocumentFormat.OpenXml.Spreadsheet.Cell footerCell = new DocumentFormat.OpenXml.Spreadsheet.Cell(new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowOffset("4"));
                    footerCell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    footerCell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("Generated By eRIS");

                    footer.AppendChild(footerCell);
                    sheetData.AppendChild(footer);

                    sheetPart.Worksheet.Save();
                    workbook.WorkbookPart.Workbook.Save();
                    workbook.Close();
                    FileContents = mem.ToArray();

                    var contentResult = new FileContentResult(FileContents, this.ContentType);
                    await contentResult.ExecuteResultAsync(context);

                    //await response.Body.WriteAsync(FileContents, 0, FileContents.Length);
                }
            }
        }
コード例 #9
0
ファイル: TestXlsx01.cs プロジェクト: eriawan/Open-XML-SDK
        public void X002_XlsxCreation()
        {
            FileInfo fi = new FileInfo(Path.Combine(s_TestFileLocation, Guid.NewGuid().ToString() + ".docx"));
            // By default, AutoSave = true, Editable = true, and Type = xlsx.
            SpreadsheetDocument doc = SpreadsheetDocument.Create(fi.FullName, SpreadsheetDocumentType.Workbook);
            WorkbookPart workbookpart = doc.AddWorkbookPart();
            workbookpart.Workbook = new S.Workbook();
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new S.Worksheet(new S.SheetData());
            S.Sheets sheets = doc.WorkbookPart.Workbook.AppendChild<S.Sheets>(new S.Sheets());
            S.Sheet sheet = new S.Sheet()
            {
                Id = doc.WorkbookPart.
                    GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "mySheet"
            };
            sheets.Append(sheet);
            workbookpart.Workbook.Save();

            OpenXmlValidator v = new OpenXmlValidator(FileFormatVersions.Office2013);
            var errs = v.Validate(doc);
            Assert.Equal(0, errs.Count());

            doc.Close();

            if (TestUtil.DeleteTempFiles)
                fi.Delete();
        }
コード例 #10
0
ファイル: TestXlsx01.cs プロジェクト: eriawan/Open-XML-SDK
        public void X003_XlsxCreation_Stream()
        {
            using (MemoryStream ms = new MemoryStream())
            // By default, AutoSave = true, Editable = true, and Type = xlsx.
            using (SpreadsheetDocument doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookpart = doc.AddWorkbookPart();
                workbookpart.Workbook = new S.Workbook();
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new S.Worksheet(new S.SheetData());
                S.Sheets sheets = doc.WorkbookPart.Workbook.AppendChild<S.Sheets>(new S.Sheets());
                S.Sheet sheet = new S.Sheet()
                {
                    Id = doc.WorkbookPart.
                        GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = "mySheet"
                };
                sheets.Append(sheet);
                workbookpart.Workbook.Save();

                OpenXmlValidator v = new OpenXmlValidator(FileFormatVersions.Office2013);
                var errs = v.Validate(doc);
                Assert.Equal(0, errs.Count());
            }
        }
コード例 #11
0
ファイル: ExcelRenderer.cs プロジェクト: raske1980/report
        public override byte[] Render(Base.Report report)
        {
            byte[] buffer;
            using (var outputMemoryStream = new MemoryStream())
            {
                using (var document = SpreadsheetDocument.Create(outputMemoryStream, SpreadsheetDocumentType.Workbook, true))
                {
                    // Add a WorkbookPart to the document.
                    var workbookpart = document.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();

                    // Add a WorksheetPart to the WorkbookPart.
                    var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());

                    // Add Sheets to the Workbook.
                    var sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());

                    // Append a new worksheet and associate it with the workbook.
                    var sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                    {
                        Id      = document.WorkbookPart.GetIdOfPart(worksheetPart),
                        SheetId = 1,
                        Name    = "Sheet1"
                    };

                    sheets.Append(sheet);

                    // Add a WorkbookStylesPart to the WorkbookPart
                    var stylesPart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();

                    var shData = worksheetPart.Worksheet.GetFirstChild <SheetData>();



                    stylesPart.Stylesheet = InitializeDefaultStyles(stylesPart.Stylesheet);

                    uint styleId = 2; //обязательно 2 потому что уже додано два стиля по умолчанию с индексами 0 и 1;

                    var styles = report.Select(o => o.Style).ToList();

                    //нельзя перенести в отдельный метод :(
                    foreach (var s in styles)
                    {
                        if (s is TableStyle)
                        {
                            var style = s as TableStyle;

                            var border = new DocumentFormat.OpenXml.Spreadsheet.Border();

                            var leftBorder   = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder();
                            var rightBorder  = new DocumentFormat.OpenXml.Spreadsheet.RightBorder();
                            var topBorder    = new DocumentFormat.OpenXml.Spreadsheet.TopBorder();
                            var bottomBorder = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder();

                            leftBorder.Style   = SetLineType(style.BorderLine);
                            rightBorder.Style  = SetLineType(style.BorderLine);
                            topBorder.Style    = SetLineType(style.BorderLine);
                            bottomBorder.Style = SetLineType(style.BorderLine);

                            leftBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                Rgb = ToHexBinaryValue(style.BorderColor)
                            };
                            rightBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                Rgb = ToHexBinaryValue(style.BorderColor)
                            };
                            topBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                Rgb = ToHexBinaryValue(style.BorderColor)
                            };
                            bottomBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                Rgb = ToHexBinaryValue(style.BorderColor)
                            };

                            border.LeftBorder   = leftBorder;
                            border.RightBorder  = rightBorder;
                            border.TopBorder    = topBorder;
                            border.BottomBorder = bottomBorder;

                            stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(border);



                            var patternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill
                            {
                                BackgroundColor = new DocumentFormat.OpenXml.Spreadsheet.BackgroundColor {
                                    Rgb = ToHexBinaryValue(style.Background)
                                },
                                ForegroundColor = new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor {
                                    Rgb = ToHexBinaryValue(style.Foreground)
                                },
                                PatternType = SetPatternType(style.PatternType)
                            };

                            var fill = new DocumentFormat.OpenXml.Spreadsheet.Fill {
                                PatternFill = patternFill
                            };

                            stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(fill);

                            DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font()
                            {
                                FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName {
                                    Val = new StringValue {
                                        Value = s.FontName
                                    }
                                },
                                FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize {
                                    Val = s.FontSize
                                },
                                Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                    Rgb = ToHexBinaryValue(s.FontColor)
                                }
                            };
                            stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font);
                        }
                        else
                        {
                            stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(new DocumentFormat.OpenXml.Spreadsheet.Fill());
                            stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(new DocumentFormat.OpenXml.Spreadsheet.Border());
                            DocumentFormat.OpenXml.Spreadsheet.Font font;
                            if (s != null)
                            {
                                font = new DocumentFormat.OpenXml.Spreadsheet.Font
                                {
                                    FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName {
                                        Val = new StringValue {
                                            Value = s.FontName
                                        }
                                    },
                                    FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize {
                                        Val = s.FontSize
                                    },
                                    Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                        Rgb = ToHexBinaryValue(s.FontColor)
                                    }
                                };


                                foreach (var item in s.TextStyle)
                                {
                                    switch (item)
                                    {
                                    case TextStyleType.Bold: font.Bold = new DocumentFormat.OpenXml.Spreadsheet.Bold {
                                            Val = true
                                    }; break;

                                    case TextStyleType.Italic: font.Italic = new DocumentFormat.OpenXml.Spreadsheet.Italic {
                                            Val = true
                                    }; break;

                                    case TextStyleType.Underline: font.Underline = new DocumentFormat.OpenXml.Spreadsheet.Underline {
                                            Val = DocumentFormat.OpenXml.Spreadsheet.UnderlineValues.Single
                                    }; break;

                                    case TextStyleType.Normal: break;
                                    }
                                }
                            }
                            else
                            {
                                font = new DocumentFormat.OpenXml.Spreadsheet.Font();
                            }

                            stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font);
                        }


                        if (s != null)
                        {
                            var cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat
                            {
                                BorderId = UInt32Value.ToUInt32(styleId),
                                FillId   = UInt32Value.ToUInt32(styleId),
                                FontId   = UInt32Value.ToUInt32(styleId)
                            };

                            if (s.Aligment != null)
                            {
                                var align = new Alignment()
                                {
                                    TextRotation = new UInt32Value(s.Aligment.Rotation),
                                    Horizontal   = AlignmentMapper.MapHorizontalAligment(s.Aligment.HorizontalAligment),
                                    Vertical     = AlignmentMapper.MapVerticalAligment(s.Aligment.VerticalAligment)
                                };

                                cellFormat.Append(align);
                            }

                            stylesPart.Stylesheet.CellFormats.AppendChild <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(cellFormat);
                        }

                        styleId = styleId + 1;
                    }

                    foreach (var x in report)
                    {
                        if (x is ComplexHeaderCell)
                        {
                            var element = x as ComplexHeaderCell;

                            if (Render(element, styles, shData, worksheetPart, workbookpart))
                            {
                                continue;
                            }
                        }

                        if (x is TextElement)
                        {
                            var element = x as TextElement;

                            if (Render(element, styles, shData, workbookpart))
                            {
                                continue;
                            }
                        }

                        if (x is TableElement)
                        {
                            var element = x as TableElement;
                            if (Render(element, styles, shData, workbookpart))
                            {
                                continue;
                            }
                        }

                        if (x is ImageElement)
                        {
                            var element = x as ImageElement;

                            if (Render(element, styles, shData, workbookpart))
                            {
                                continue;
                            }
                        }
                    }

                    //var dateLine = shData.AppendChild(new Spreadsheet.Row());

                    //dateLine.AppendChild(new Spreadsheet.Cell()
                    //{
                    //    CellValue = new Spreadsheet.CellValue(_report.TimeStamp.Date.ToString()),
                    //    DataType = Spreadsheet.CellValues.String,
                    //    StyleIndex = 0
                    //});

                    workbookpart.Workbook.Save();
                }

                buffer = new byte[outputMemoryStream.Position];
                outputMemoryStream.Position = 0;
                outputMemoryStream.Read(buffer, 0, buffer.Length);
            }
            return(buffer);
        }
コード例 #12
0
        /// <summary>
        /// Deletes the supplied sheet instance from the workbook part
        /// </summary>
        /// <param name="workbookPart">The workbook part to delete the sheet from</param>
        /// <param name="sheet">The sheet to delete</param>
        /// <exception cref="ArgumentNullException">
        /// workbookPart
        /// or
        /// sheet
        /// </exception>
        public static void DeleteSheet(this WorkbookPart workbookPart, DocumentFormat.OpenXml.Spreadsheet.Sheet sheet)
        {
            if (workbookPart == null)
            {
                throw new ArgumentNullException("workbookPart");
            }
            if (sheet == null)
            {
                throw new ArgumentNullException("sheet");
            }

            // get the id of the sheet for deletion
            Int32Value sheetId = Int32Value.FromInt32((int)sheet.SheetId.Value);

            // Remove the sheet reference from the workbook.
            WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));

            SheetViews views = worksheetPart.Worksheet.GetFirstChild <SheetViews>();

            if (views != null)
            {
                views.Remove();
                worksheetPart.Worksheet.Save();
            }

            sheet.Remove();

            // Delete the worksheet part.
            workbookPart.DeletePart(worksheetPart);

            // Get the CalculationChainPart
            // Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the
            // workbook whose value is calculated from any formula

            CalculationChainPart calChainPart = workbookPart.CalculationChainPart;

            if (calChainPart != null)
            {
                List <CalculationCell> forRemoval = new List <CalculationCell>();

                var calChainEntries = calChainPart.CalculationChain.Descendants <CalculationCell>().ToList();

                foreach (CalculationCell item in calChainEntries)
                {
                    if (item.SheetId == null)
                    {
                        item.Remove();
                    }
                    else if (item.SheetId.HasValue && item.SheetId.Value.Equals(sheetId))
                    {
                        item.Remove();
                    }
                }
                if (calChainPart.CalculationChain.Count() == 0)
                {
                    workbookPart.DeletePart(calChainPart);
                }
            }

            workbookPart.Workbook.Save();
        }
コード例 #13
0
        private void Export()
        {
            if (ResultModel != null)
            {
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.Title  = "Save Result";
                saveDialog.Filter = "Excel 97-2003 WorkBook|*.xls|Excel WorkBook|*.xlsx|All Excel Files|*.xls;*.xlsx|All Files|*.*";
                if ((bool)saveDialog.ShowDialog())
                {
                    using (var workbook = SpreadsheetDocument.Create(saveDialog.FileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
                    {
                        var workbookPart = workbook.AddWorkbookPart();

                        workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                        workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

                        var table = ResultModel;

                        var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
                        var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                        sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                        DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                        string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                        uint sheetId = 1;
                        if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                        {
                            sheetId =
                                sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                        }

                        DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                        {
                            Id = relationshipId, SheetId = sheetId, Name = table.TableName
                        };
                        sheets.Append(sheet);

                        DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                        List <String> columns = new List <string>();
                        foreach (System.Data.DataColumn column in table.Columns)
                        {
                            columns.Add(column.ColumnName);

                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                            headerRow.AppendChild(cell);
                        }


                        sheetData.AppendChild(headerRow);

                        foreach (System.Data.DataRow dsrow in table.Rows)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                            foreach (String col in columns)
                            {
                                DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                                cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());     //
                                newRow.AppendChild(cell);
                            }

                            sheetData.AppendChild(newRow);
                        }
                    }
                    MessageBox.Show("File Saved Successfully");
                }
            }
        }
コード例 #14
0
        public void DumpSheetRange <T>(T output, string filename, string sheetName, string firstCellName, string lastCellName) where T : TextWriter
        {
            // Create instance of OpenSettings
            OpenSettings openSettings = new OpenSettings
            {
                // Add the MarkupCompatibilityProcessSettings
                MarkupCompatibilityProcessSettings =
                    new MarkupCompatibilityProcessSettings(
                        MarkupCompatibilityProcessMode.ProcessAllParts,
                        FileFormatVersions.Office2013)
            };

            // Open the document with OpenSettings
            using (SpreadsheetDocument excelDocument =
                       SpreadsheetDocument.Open(filename,
                                                false,
                                                openSettings))
            {
                var    wbPart = excelDocument.WorkbookPart;
                Sheets sheets = wbPart.Workbook.Sheets;
                foreach (E sheetElem in sheets)
                {
                    foreach (A attr in sheetElem.GetAttributes())
                    {
                        output.Write("{0}: {1}\t", attr.LocalName, attr.Value);
                    }
                    output.WriteLine();
                }
                S sheet = wbPart.Workbook.Descendants <S>().Where(s => s.Name == sheetName).First();
                // Throw an exception if there is no sheet.
                if (sheet == null)
                {
                    throw new ArgumentException("sheetName is invalid for this workbook");
                }
                // Retrieve a reference to the worksheet part.
                WorksheetPart wsPart =
                    (WorksheetPart)wbPart.GetPartById(sheet.Id);

                W   worksheet   = wsPart.Worksheet;
                var stringTable =
                    wbPart.GetPartsOfType <SharedStringTablePart>()
                    .FirstOrDefault();

                uint   firstRowNum = ColumnComparer.GetRowIndex(firstCellName);
                uint   lastRowNum  = ColumnComparer.GetRowIndex(lastCellName);
                string firstColumn = ColumnComparer.GetColumnName(firstCellName);
                string lastColumn  = ColumnComparer.GetColumnName(lastCellName);

                string headers = firstColumn;
                while (ColumnComparer.CompareColumn(headers, lastColumn) <= 0)
                {
                    output.Write("{0}\t", headers);
                    headers = ColumnComparer.NextColumn(headers);
                }
                output.WriteLine();

                //// Iterate through the cells within the range and do whatever.
                foreach (int row in Enumerable.Range((int)firstRowNum, (int)lastRowNum))
                {
                    string col = firstColumn;
                    while (ColumnComparer.IsColumnInRange(firstColumn, lastColumn, col))
                    {
                        var cellRef = String.Format("{0}{1}", col, row);
                        var cell    = wsPart.Worksheet.Descendants <Cell>().Where(c => c.CellReference == cellRef).FirstOrDefault();
                        output.Write("{0}\t", DecodedCell(cell, stringTable));
                        col = ColumnComparer.NextColumn(col);
                    }
                    output.WriteLine();
                }
            }
        }
コード例 #15
0
        /// <summary>
        /// Adds a new worksheet to the spreadsheet
        /// </summary>
        /// <param name="spreadsheet">The spreadsheet</param>
        /// <param name="name">name of the worksheet</param>
        /// <returns>True if success</returns>
        public bool AddWorkSheet(string name)
        {
            Sheets sheets = this._spreadsheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
            Sheet sheet;
            WorksheetPart worksheetPart;

            //Add the worksheetpart
            worksheetPart = this._spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());
            worksheetPart.Worksheet.Save();

            // Add the sheet to workbook
            sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
            {
                Id = this._spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = (uint)(this._spreadsheet.WorkbookPart.Workbook.Sheets.Count() + 1),
                Name = name
            };

            sheets.Append(sheet);
            this._spreadsheet.WorkbookPart.Workbook.Save();

            return true;
        }
コード例 #16
0
        /// <summary>
        /// Updates a SpreadsheetDocument with new tabular data.
        /// </summary>
        /// <param name="ssdoc">
        /// The excel document to update.</param>
        /// <param name="json">
        /// String in JSON format representing the tabular data for updating the Chart's cached data points.
        /// The JSON object must contain a "fields" attribute as an array containing the field/column names.
        /// The JSON object must contain a "rows" attribute as an array of arrays representing the rows and their values, with values matching the same order and cardinality of the field names.
        /// This is the same data as the underlying Excel spreadsheet contents.</param>
        /// <param name="sheetName">
        /// The name of the Excel worksheet where the chart data originates from.
        /// Used for updating the chart's cell references.</param>
        /// <returns>
        /// Returns the updated SpreadsheetDocument</returns>
        public static SpreadsheetDocument Update(this SpreadsheetDocument ssdoc, string json, string sheetName)
        {
            if ((json == null) || (json == String.Empty))
            {
                json = "{\"fields\": [ \"No Results\" ], \"rows\": [[ \"No Results\" ]]}";
            }

            //Splunk JSON data is a series of objects consisting of multiple key(column)/value(row) pairs in the result attribute.
            dynamic input = JsonConvert.DeserializeObject <dynamic>(json);

            if (input["rows"].Count == 0)
            {
                json  = "{\"fields\": [ \"No Results\" ], \"rows\": [[ \"No Results\" ]]}";
                input = JsonConvert.DeserializeObject <dynamic>(json);
            }

            ss.Sheet sheet = ssdoc.WorkbookPart.Workbook.Descendants <ss.Sheet>().Where(s => s.Name.ToString() == sheetName).FirstOrDefault();
            if (sheet == null)
            {
                sheet = ssdoc.WorkbookPart.Workbook.Descendants <ss.Sheet>().FirstOrDefault();
            }
            WorksheetPart worksheet = (WorksheetPart)ssdoc.WorkbookPart.GetPartById(sheet.Id);

            ss.SheetData data = worksheet.Worksheet.GetFirstChild <ss.SheetData>();



            //Remove all the rows after our column headers row. We'll replace them with new rows as the table is populated from the Splunk search results.
            ss.Row firstRow = data.Elements <ss.Row>().First();
            while (firstRow.NextSibling <ss.Row>() != null)
            {
                firstRow.NextSibling <ss.Row>().Remove();
            }

            ss.Row newHeader = new ss.Row();
            newHeader.DyDescent = 0.25;
            newHeader.RowIndex  = 1;
            var  columnNames          = input["fields"];
            char startingHeaderColumn = 'A';

            foreach (var column in columnNames)
            {
                string       cellRef = startingHeaderColumn.ToString() + 1;
                ss.Cell      newCell = new ss.Cell();
                ss.CellValue cv      = new ss.CellValue(column.ToString());

                newCell.CellReference = cellRef;
                newCell.DataType      = ss.CellValues.String;
                newCell.Append(cv);
                newHeader.Append(newCell);

                startingHeaderColumn++;
            }

            data.InsertAfter(newHeader, data.Elements <ss.Row>().Last());
            data.RemoveChild(firstRow);

            for (int i = 0; i < input["rows"].Count; i++)
            {
                char startingColumn    = 'A';
                int  startingColumnVal = 1;
                int  endingColumnVal   = 0;
                //Set the Excel row index (Excel index starts at 1, not zero and row 1 has headers so add 2 to the for index)
                uint rowIndex = Convert.ToUInt32(i) + 2;

                ss.Row newRow = new ss.Row();
                newRow.DyDescent = 0.25;
                newRow.RowIndex  = rowIndex;


                Debug.WriteLine(String.Format("Writing Excel Row {0}", i + 1));
                var row = input["rows"][i];
                foreach (var cell in row)
                {
                    string       cellRef = startingColumn.ToString() + rowIndex;
                    ss.Cell      newCell = new ss.Cell();
                    ss.CellValue cv      = new ss.CellValue(cell.ToString());

                    newCell.CellReference = cellRef;
                    if (startingColumn == 'A')
                    {
                        newCell.DataType = ss.CellValues.String;
                    }
                    newCell.Append(cv);
                    newRow.Append(newCell);

                    startingColumn++;
                    endingColumnVal++;
                }


                int numberOfCellsInRow        = newRow.Descendants <ss.Cell>().Count();
                ListValue <StringValue> spans = new ListValue <StringValue>();
                spans.Items.Add(string.Format("{0}:{1}", startingColumnVal, endingColumnVal));
                newRow.Spans = spans;
                data.InsertAfter(newRow, data.Elements <ss.Row>().Last());
            }


            // Update Table Reference
            Debug.WriteLine("Updating Table");
            var table = worksheet.TableDefinitionParts.First().Table;

            table.Reference = string.Format("A1:{0}{1}", GetExcelColumnName(input["fields"].Count), input["rows"].Count + 1);
            table.TableColumns.RemoveAllChildren();
            for (int i = 0; i < columnNames.Count; i++)
            {
                var newColumn = new ss.TableColumn();
                newColumn.Id   = new UInt32Value((uint)i + 1);
                newColumn.Name = new StringValue(columnNames[i].ToString());
                table.TableColumns.Append(newColumn);
            }



            return(ssdoc);
        }