//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); } } } }
/// <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; }
/*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; } }
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); } } } }
public static WorksheetPart WorkSheet(this SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Sheet oXmlSheet) { return(spreadsheet.WorkbookPart.GetPartById(oXmlSheet.Id) as WorksheetPart); }
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); } }
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); } }
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); } } }
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(); }
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()); } }
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); }
/// <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(); }
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"); } } }
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(); } } }
/// <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; }
/// <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); }