public static void AppendSpreadSheet(string filePath, string sheetName) { if (!System.IO.File.Exists(filePath)) { throw new Exception("File Not Exists"); } using (Stream stream = File.Open(filePath, FileMode.Open)) { // Open a SpreadsheetDocument based on a stream. SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, true); Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>(); if (isSheetsAlreadyExists(sheets, sheetName)) { spreadsheetDocument.Close(); stream.Close(); return; } // Add a new worksheet. WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart); // Get a unique ID for the new worksheet. uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); //ready to write column header spreadsheetDocument.WorkbookPart.Workbook.Save(); // Close the document handle. spreadsheetDocument.Close(); stream.Close(); } }
/// <summary> /// Cierra la edicion en memoria y guarda en disco /// </summary> /// <returns></returns> public string CloseAndSave() { _wbPart.SharedStringTablePart.SharedStringTable.Save(); var tempfn = Path.GetTempFileName(); _document.Close(); var temp = File.Open(tempfn, FileMode.Create); _stream.Position = 0; _stream.CopyTo(temp); temp.Close(); return(tempfn); }
public void GetExcelFile() { //Создается файл с расширением .xlsx string filePath = @"C:\Example\Add.xlsx"; SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook); WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); //В созданый файл добавляется новая книга //Sheet sheet = new Sheet() //{ // Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), // SheetId = 1, // Name = "mySheet" //}; //sheets.Append(sheet); workbookPart.Workbook.Save(); spreadsheetDocument.Close(); }
public MemoryStream ToMemoryStream() { workbookpart.Workbook.Save(); spreadsheetDocument.Close(); memoryStream.Position = 0; return(new MemoryStream(memoryStream.ToArray())); }
// 释放所有资源 public void Dispose() { if (stylesheet != null) { stylesheet.Save(); stylesheet = null; } if (workbookPart != null) { workbookPart.Workbook.Save(); workbookPart = null; } if (spreadsheetDocument != null) { spreadsheetDocument.Close(); spreadsheetDocument.Dispose(); spreadsheetDocument = null; } if (fileStream != null) { fileStream.Flush(); fileStream.Close(); fileStream.Dispose(); fileStream = null; } }
public void OpenAndSearchDocument(string filePath, string xmlSplitDefinitionFilePath) { //split XML Read var xml = File.ReadAllText(xmlSplitDefinitionFilePath); Split splitXml; using (MemoryStream stream = new MemoryStream(Encoding.UTF8.GetBytes(xml))) { XmlSerializer serializer = new XmlSerializer(typeof(Split)); splitXml = (Split)serializer.Deserialize(stream); } // Open a SpreadsheetDocumentDocument for editing using the filepath. SpreadsheetDocument wordprocessingDocument = SpreadsheetDocument.Open(filePath, true); // Assign a reference to the existing document body. Workbook body = wordprocessingDocument.WorkbookPart.Workbook; IMarkerMapper <Sheet> mapping = new MarkerExcelMapper(DocumentName, splitXml, body); //DocumentElements = mapping.Run(); // Close the handle explicitly. wordprocessingDocument.Close(); }
public static MemoryStream CreateExcelFileStream(IEnumerable data) { var stream = new MemoryStream(); DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(data), (typeof(DataTable))); using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); var sheetData = new SheetData(); worksheetPart.Worksheet = new Worksheet(sheetData); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" }; sheets.Append(sheet); Row headerRow = new Row(); List <string> columns = new List <string>(); foreach (DataColumn column in table.Columns) { columns.Add(column.ColumnName); Cell cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (DataRow dsrow in table.Rows) { Row newRow = new Row(); foreach (string col in columns) { Cell cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(dsrow[col].ToString()); newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } workbookPart.Workbook.Save(); document.Close(); stream.Position = 0; } return(stream); }
public void Dispose() { if (document != null) { document.Close(); } }
public void Close() { if (_documentFile != null) { _documentFile.Close(); } }
private void PersistEntries(IEnumerable <Entry> entries) { using (SpreadsheetDocument document = SpreadsheetDocument.Open(this.Path, true)) { this.Initialize(document); SheetData sheetData = new SheetData(); Row headerRow = _workSheetPart.Worksheet.Descendants <Row>().Single(p => p.RowIndex == 1).Clone() as Row; sheetData.Append(headerRow); var colRefs = _colRefsToHeaders.Keys.OrderBy(p => p); foreach (Entry entry in entries.OrderBy(p => p.RowIndex)) { Row row = new Row(); row.RowIndex = new UInt32Value(entry.RowIndex); foreach (string colRef in colRefs) { row.Append(this.CreateCell(entry, colRef)); } sheetData.Append(row); } _workSheetPart.Worksheet = new Worksheet(sheetData); document.Close(); } }
public void Save(Stream stream) { if (stream == null) throw new ArgumentNullException(nameof(stream)); Recalculate(); foreach (var worksheetPart in _workbookPart.WorksheetParts) worksheetPart.Worksheet.Save(worksheetPart); _workbookPart.WorkbookStylesPart.Stylesheet.Save(_workbookPart.WorkbookStylesPart); _workbook.Save(_workbookPart); _package.Close(); if (!string.IsNullOrEmpty(_tempFilePath) && File.Exists(_tempFilePath)) { using (var fileStream = new FileStream(_tempFilePath, FileMode.Open, FileAccess.Read)) fileStream.CopyTo(stream); File.Delete(_tempFilePath); } else { _outputStream.Seek(0, SeekOrigin.Begin); _outputStream.CopyTo(stream); } }
public static void CreateSheetIfNotExists(string brand) { SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(GenerateExcelFileName(), true); if (spreadsheetDocument.WorkbookPart.Workbook.Sheets == null) { Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook. AppendChild <Sheets>(new Sheets()); if (spreadsheetDocument.WorkbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == brand).FirstOrDefault() == null) { // Append a new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart. GetIdOfPart(spreadsheetDocument.WorkbookPart.WorksheetParts.FirstOrDefault()), SheetId = 1, Name = brand }; sheets.Append(sheet); spreadsheetDocument.WorkbookPart.Workbook.Save(); } } spreadsheetDocument.Close(); }
public static void CreateSpreadSheetDocument(string brand) { string excelFileName = GenerateExcelFileName(); if (!File.Exists(excelFileName)) { // Create a spreadsheet document by supplying the filepath. // By default, AutoSave = true, Editable = true, and Type = xlsx. SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument. Create(GenerateExcelFileName(), SpreadsheetDocumentType.Workbook); // Add a WorkbookPart to the document. WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); workbookpart.Workbook.Save(); // Close the document. spreadsheetDocument.Close(); } }
private static void CreateDoc(SpreadsheetDocument doc, bool isCreateDefaultSheet) { WorkbookPart wb = doc.AddWorkbookPart(); wb.Workbook = new Workbook(); if (isCreateDefaultSheet) { WorksheetPart ws = wb.AddNewPart <WorksheetPart>(); ws.Worksheet = new Worksheet(new SheetData()); Sheets sheets = wb.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = doc.WorkbookPart. GetIdOfPart(ws), SheetId = 1, Name = "Sheet" }; sheets.Append(sheet); } wb.Workbook.Save(); doc.Close(); }
/// <summary> /// Create Excel stream from an Excel template /// </summary> /// <typeparam name="T">the type of Entity</typeparam> /// <param name="entity">the entity where we get data from</param> /// <param name="excelTemplateFileName">the full path and name of the Excel template file</param> /// <returns></returns> public static MemoryStream CreateStreamFromTemplate <T>(T entity, string excelTemplateFileName) { // copy the template file to memory, so changes won't affect the template file. MemoryStream ms; using (FileStream fs = File.Open(excelTemplateFileName, FileMode.Open)) { ms = new MemoryStream((int)fs.Length); fs.CopyTo(ms); } using (SpreadsheetDocument doc = SpreadsheetDocument.Open(ms, true)) { WorkbookPart wbPart = doc.WorkbookPart; List <DefinedNameData> definedNameDataSet = GetDefinedNames(wbPart); WorksheetPart wsPart = null; string previousSheetName = null; IEnumerable <PropertyInfo> entityProperties = entity.GetType().GetTypeInfo().DeclaredProperties; foreach (DefinedNameData dn in definedNameDataSet) { if (previousSheetName != dn.SheetName) { wsPart = GetWorksheetPart(wbPart, dn.SheetName); previousSheetName = dn.SheetName; } object cellValue = GetEntityPropertyValue(entity, entityProperties, dn.Name); Cell cell = GetCell(wsPart, dn, cellValue); SetCellTypeAndValue(cell, cellValue, wbPart.SharedStringTablePart.SharedStringTable); } doc.Close(); } return(ms); }
public byte[] Run(List <PersonFiles> files) { var mergeXml = GetMergeXml(files); byte[] template = files.Where(p => p.Person == "/" && p.Name == "template.xlsx").Select(d => d.Data).FirstOrDefault(); using (MemoryStream docInMemoryStream = new MemoryStream(template, 0, template.Length, true, true)) { OpenXmlPowerToolsDocument docPowerTools = new OpenXmlPowerToolsDocument(string.Empty, docInMemoryStream); using (OpenXmlMemoryStreamDocument streamEmptyDoc = new OpenXmlMemoryStreamDocument(docPowerTools)) { SpreadsheetDocument excelTemplateDoc = streamEmptyDoc.GetSpreadsheetDocument(); excelTemplateDoc.WorkbookPart.Workbook.Sheets = new Sheets(); MergeDocument documentXml = mergeXml.Items.First(); foreach (MergeDocumentPart part in documentXml.Part) { byte[] byteArray = files.Where(p => p.Person == part.Name && p.Name == part.Id).Select(d => d.Data).FirstOrDefault(); using (MemoryStream docPartInMemoryStream = new MemoryStream(byteArray, 0, byteArray.Length, true, true)) { OpenXmlPowerToolsDocument docPartPowerTools = new OpenXmlPowerToolsDocument(string.Empty, docPartInMemoryStream); using (OpenXmlMemoryStreamDocument streamPartDoc = new OpenXmlMemoryStreamDocument(docPartPowerTools)) { SpreadsheetDocument spreadSheetDocument = streamPartDoc.GetSpreadsheetDocument(); ExcelTools.MergeWorkSheets(excelTemplateDoc, spreadSheetDocument); // Close the handle explicitly. spreadSheetDocument.Close(); } } } return(streamEmptyDoc.GetModifiedDocument().DocumentByteArray); } } }
/// <summary> /// Almacena la informacion y la guarda y checa si existe el archivo /// </summary> /// <param name="direccion">Direccion del archivo</param> /// <param name="ciclo">Semestre al cual se va a considerar</param> /// <param name="tipo">tipo por default para los grupos</param> public LibroExcel(string direccion, string ciclo, string tipo = "") { string[] split = direccion.Split(new char[] { '/', '\\' }, StringSplitOptions.RemoveEmptyEntries); string archivo = split[split.Length - 1]; if (!File.Exists(direccion)) { SpreadsheetDocument doc = SpreadsheetDocument.Create(direccion, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook); //Inicializa el documento para que trabaje como libro WorkbookPart workbookpart = doc.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); //Obtiene el manejador de las hojas Sheets sheets = doc.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); creaHoja(doc, "Hoja1"); doc.Close(); } SetHeaders(); this.archivo = archivo; this.dir = direccion; dHeaders["cicloDefault"] = ciclo; dHeaders["tipoDefault"] = tipo; }
public async Task <string> GenerateExcel(string fileName) { try { StorageFolder folder = ApplicationData.Current.LocalFolder; string fullPath = Path.Combine(folder.Path, fileName); return(await Task <String> .Run(() => { Task.Yield(); SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook); WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" }; sheets.Append(sheet); workbookPart.Workbook.Save(); spreadsheetDocument.Close(); return fullPath; })); } catch (Exception ex) { await MainPage.ShowErrorMessage(ex.Message); return(null); } }
public static void CreateSpreadsheetWorkbook(string filepath) { // Create a spreadsheet document by supplying the filepath. // By default, AutoSave = true, Editable = true, and Type = xlsx. SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument. Create(filepath, SpreadsheetDocumentType.Workbook); // Add a WorkbookPart to the document. WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook. Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook. AppendChild <Sheets>(new Sheets()); workbookpart.Workbook.Save(); // Close the document. spreadsheetDocument.Close(); }
/* ----- Fonction de test ----- */ #region TestToPlaceAStringInCellValue /// <summary> /// Résultat du test /// Tentative de remplacement d'une CellValue par une autre. /// Comme attendu cela fonctionne sur le moment mais peut poser des problèmes par la suite. /// En effet si la cellule que l'on modifie référencait une string alors lors des lectures /// qui peuvent suivre (avec les algorithmes ci-dessus en tout cas) provoqueront des erreurs. /// L'explication est que les algorithmes de lectures détectent la présence de string via le /// champs DataType uniquement. Si c'est le cas alors il récupère l'index contenu dans CellValue /// pour le converti. Sauf que CellValue ne contient pas un Int32 mais une string. /// Cette erreur est évitable cependant cela ne serait pas une bonne chose étant donné que /// CellValue ne contient pas de string dans les cas nominaux. /// /// La bonne pratique pour ce genre de fonction est de placer la string voulu dans la liste des /// string partagée (dans SharedString.xml) comme le fait la fonction ChangeStringByAnother et si /// besoin de procéder à un changement de DataType comme le fait la fonction ChangeIntByString. /// </summary> /// /// <param name="pathToFile"> /// Le chemin (nom et extension compris) du fichier à lire et modifier /// </param> private static void TestToPlaceAStringInValue(string pathToFile) { Console.WriteLine("Fichier : " + pathToFile); // Ouverture du document using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(pathToFile, true)) { // Parcours de la hiérarchie du document jusqu'à récupération du tableau WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; // Récupération du tableau nommé "Feuil1" Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where(x => x.Name == "Feuil1").FirstOrDefault <Sheet>(); WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id); // Récupération de la liste des string liées SharedStringTable listeStringElement = workbookPart.SharedStringTablePart.SharedStringTable; // Récupération de la liste des cellules IEnumerable <Cell> listCell = worksheetPart.Worksheet.Descendants <Cell>().Where(x => x.CellReference != null); int i = 0; foreach (Cell c in listCell) { if (c.CellFormula == null) { // Pour ne pas toucher aux formules c.CellValue = new CellValue("Test " + i); } } worksheetPart.Worksheet.Save(); spreadsheetDocument.Close(); } }
public override void ExportTo(string filename) { SpreadsheetDocument document = CreateSpreadsheetWorkbook(filename); var workbookpart = document.WorkbookPart; var sheetData = workbookpart.WorksheetParts.First().Worksheet.GetFirstChild <SheetData>(); foreach (var value in data) { Row row = new Row(); row.Append(new Cell(new CellValue(value.Id.ToString()))); var name = new Cell(new CellValue(value.Name)); name.DataType = new EnumValue <CellValues>(CellValues.String); row.Append(name); var admission = new Cell(new CellValue(value.Admission.ToString())); admission.DataType = new EnumValue <CellValues>(CellValues.Date); row.Append(admission); var track = new Cell(new CellValue(value.Group.Track.ToString())); track.DataType = new EnumValue <CellValues>(CellValues.String); row.Append(track); row.Append(new Cell(new CellValue(value.Group.Course.ToString()))); row.Append(new Cell(new CellValue(value.Group.Number.ToString()))); sheetData.Append(row); } workbookpart.Workbook.Save(); document.Close(); }
private void CreateExcelFile() { using (SpreadsheetDocument document = SpreadsheetDocument.Create(_path, SpreadsheetDocumentType.Workbook)) { // Add a WorkbookPart to the document. WorkbookPart workbookpart = document.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); // Add Sheets to the Workbook. Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = document.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "HABANERO" }; sheets.Append(sheet); Worksheet worksheet = new Worksheet(); worksheet.Append(_sheetData); worksheetPart.Worksheet = worksheet; workbookpart.Workbook.Save(); document.Close(); } }
//..................................................................... /// <summary> /// http://www.dispatchertimer.com/tutorial/how-to-create-an-excel-file-in-net-using-openxml-part-2-export-a-collection-to-spreadsheet/ /// </summary> /// <param name="filepath"></param> /// public void TestXlsx( ) public void TestXlsx( ) //string filepath ) { // Create a spreadsheet document by supplying the filepath. // By default, AutoSave = true, Editable = true, and Type = xlsx. using (this.spreadsheetDocument = SpreadsheetDocument.Create( this.GetXlsxName( ), SpreadsheetDocumentType.Workbook)) { // Add a WorkbookPart to the document. WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart( ); workbookpart.Workbook = new Workbook( ); workbookpart.Workbook.AppendChild(new Sheets( )); // Adding style WorkbookStylesPart stylePart = workbookpart.AddNewPart <WorkbookStylesPart>( ); stylePart.Stylesheet = this.GenerateStylesheet( ); stylePart.Stylesheet.Save( ); this.GenerateXlsxDocument(workbookpart); //............................................. workbookpart.Workbook.Save( ); // Close the document. spreadsheetDocument.Close( ); } return; }
public void ExportAsXLSXStream(Stream outputStream) { // See: http://blogs.msdn.com/b/chrisquon/archive/2009/07/22/creating-an-excel-spreadsheet-from-scratch-using-openxml.aspx for some ideas... // See: http://stackoverflow.com/questions/1271520/opening-xlsx-in-office-2003 using (SpreadsheetDocument package = SpreadsheetDocument.Create(outputStream, SpreadsheetDocumentType.Workbook)) { // Setup the basics of a spreadsheet document. package.AddWorkbookPart(); package.WorkbookPart.Workbook = new Workbook(); WorksheetPart workSheetPart = package.WorkbookPart.AddNewPart <WorksheetPart>(); workSheetPart.Worksheet = new Worksheet(sheetData); workSheetPart.Worksheet.Save(); // create the worksheet to workbook relation package.WorkbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet { Id = package.WorkbookPart.GetIdOfPart(workSheetPart), SheetId = 1, Name = "Sheet 1" }; package.WorkbookPart.Workbook.GetFirstChild <Sheets>().AppendChild <Sheet>(sheet); package.WorkbookPart.Workbook.Save(); package.Close(); } }
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 static void CreateSpreadsheetWorkbook(string filepath) { // Create a spreadsheet document by supplying the filepath. // By default, AutoSave = true, Editable = true, and Type = xlsx. SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument. Create(filepath, SpreadsheetDocumentType.Workbook); // Add a WorkbookPart to the document. WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook. Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook. AppendChild <Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook. //Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet 1" }; //sheets.Append(sheet); //workbookpart.Workbook.Save(); // Close the document. spreadsheetDocument.Close(); }
public void CreateSheets(string filename, string sheetname, int sheetid) { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; if (sheetid == 1) { workbookPart.Workbook = new Workbook(); } Workbook workbook = myDoc.WorkbookPart.Workbook; //WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); OpenXmlWriter writer = OpenXmlWriter.Create(workbookPart); writer.WriteStartElement(workbook); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet() { Name = sheetname, SheetId = (uint)sheetid }); // this is for Sheets writer.WriteEndElement(); // this is for Workbook writer.WriteEndElement(); writer.Close(); myDoc.Close(); } }
private void ProcessWorkSheet(string filePath, DataTable tbl) { // get worksheetpart using (Stream stream = File.Open(filePath, FileMode.Open)) { // Open a SpreadsheetDocument based on a stream. SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(stream, true); WorkbookPart wbPart = spreadsheet.WorkbookPart; // Assuming last id is , is id for worksheet . Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild <Sheets>(); uint sheetId = 0; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() - 1; } WorksheetPart worksheetPart = wbPart.WorksheetParts.ToList()[Convert.ToInt32(sheetId)]; WriteDataTableToExcelWorksheet(tbl, worksheetPart, CultureInfo.InvariantCulture); spreadsheet.WorkbookPart.Workbook.Save(); spreadsheet.Close(); } }
public string GenerateExcel(string fileName) { //Fix for https://github.com/OfficeDev/Open-XML-SDK/issues/221 Environment.SetEnvironmentVariable("MONO_URI_DOTNETRELATIVEORABSOLUTE", "true"); //Create a new spreadsheet file - remark will overwrite existing ones with the same name! string fullPath = Path.Combine(OfficeFolder.FullPath, fileName); SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook); WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" }; sheets.Append(sheet); workbookPart.Workbook.Save(); spreadsheetDocument.Close(); return(fullPath); }
private static void Finish(OpenXmlWriter writer, SpreadsheetDocument xl, WorksheetPart worksheetpart) { // end worksheet writer.WriteEndElement(); writer.Close(); // write root element writer = OpenXmlWriter.Create(xl.WorkbookPart); writer.WriteStartElement(new Workbook()); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet { Name = "Sheet1", SheetId = 1, Id = xl.WorkbookPart.GetIdOfPart(worksheetpart) }); // end Sheets writer.WriteEndElement(); // end Workbook writer.WriteEndElement(); writer.Close(); xl.Close(); }
public List<object[]> LerPlanilhaDados(Stream stream) { List<object[]> dadosPlanilha = new List<object[]>(); using (_document = SpreadsheetDocument.Open(stream, true)) { try { _wbPart = _document.WorkbookPart; _sharedStringPart = GetSharedStringPart(); if (SelectFirstSheet()) { int indice = 0; foreach (Row row in _activeWorksheet.Descendants<Row>()) { indice++; if (indice == 1) continue; // Pula o cabeçalho. List<object> dadosLinha = new List<object>(); foreach (Cell celula in row.Descendants<Cell>()) dadosLinha.Add(GetCellValue(celula)); dadosPlanilha.Add(dadosLinha.ToArray()); } } } catch { throw; } finally { _document.Close(); } return dadosPlanilha; } }
// Adds child parts and generates content of the specified part private static void CreateParts(SpreadsheetDocument document) { // Create a spreadsheet document by supplying the filepath. // By default, AutoSave = true, Editable = true, and Type = xlsx. // Add a WorkbookPart to the document. WorkbookPart starterWorkbookPart = document.AddWorkbookPart(); starterWorkbookPart.Workbook = new Workbook(); starterWorkbookPart.Workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); // Add a WorksheetPart to the WorkbookPart. WorksheetPart starterWorksheetPart = starterWorkbookPart.AddNewPart<WorksheetPart>(); // SheetData: Represents a cell table. Expresses information about each cell, grouped together by rows in the worksheet. starterWorksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook. Sheets firstSheets = document.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook. Sheet leadingSheet = new Sheet() { Id = document.WorkbookPart.GetIdOfPart(starterWorksheetPart), SheetId = 1, Name = "Sheet1" }; firstSheets.Append(leadingSheet); // Save the document starterWorkbookPart.Workbook.Save(); // Close the document document.Close(); }
/// <summary> /// Add Datatuples to a Excel Template file /// </summary> /// <remarks></remarks> /// <seealso cref=""/> /// <param name="dataTuples"> Datatuples to add</param> /// <param name="filePath">Path of the excel template file</param> /// <param name="dataStructureId">Id of datastructure</param> /// <returns>List of Errors or null</returns> public List<Error> AddDataTuplesToTemplate(DatasetManager datasetManager, List<long> dataTuplesIds, string filePath, long dataStructureId ) { if (File.Exists(filePath)) { //Stream file = Open(filePath); //_dataTuples = dataTuples; // loading datastructure dataStructure = GetDataStructure(dataStructureId); // open excel file spreadsheetDocument = SpreadsheetDocument.Open(filePath, true); // get workbookpart WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; // get all the defined area List<DefinedNameVal> namesTable = buildDefinedNamesTable(workbookPart); // select data area this.areaOfData = namesTable.Where(p => p.Key.Equals("Data")).FirstOrDefault(); // Select variable area this.areaOfVariables = namesTable.Where(p => p.Key.Equals("VariableIdentifiers")).FirstOrDefault(); // Get intergers for reading data startColumn = getColumnNumber(this.areaOfData.StartColumn); endColumn = getColumnNumber(this.areaOfData.EndColumn); numOfColumns = (endColumn - startColumn) + 1; offset = getColumnNumber(getColumnName(this.areaOfData.StartColumn)) - 1; // gerneat Style for cell types generateStyle(spreadsheetDocument); // get styleSheet stylesheet = workbookPart.WorkbookStylesPart.Stylesheet; // Get shared strings sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ToArray(); // select worksheetpart by selected defined name area like data in sheet // sheet where data area is inside WorksheetPart worksheetPart = getWorkSheetPart(workbookPart, this.areaOfData); // Get VarioableIndentifiers this.VariableIdentifiers = getVariableIdentifiers(worksheetPart, this.areaOfVariables.StartRow, this.areaOfVariables.EndRow); AddRows(worksheetPart, this.areaOfData.StartRow, this.areaOfData.EndRow, dataTuplesIds, datasetManager); // set data area foreach (DefinedName name in workbookPart.Workbook.GetFirstChild<DefinedNames>()) { if (name.Name == "Data") { string[] tempArr = name.InnerText.Split('$'); string temp = ""; //$A$10:$C$15 tempArr[tempArr.Count() - 1] = numOfDataRows.ToString(); foreach (string t in tempArr) { if (t == tempArr.First()) { temp = temp + t; } else { temp = temp + "$" + t; } } name.Text = temp; } } spreadsheetDocument.WorkbookPart.Workbook.Save(); spreadsheetDocument.Close(); } return ErrorMessages; }
public override void Save(ResearchResult result) { if (!Directory.Exists(storageStr)) { Directory.CreateDirectory(storageStr); } string fileName = storageStr + result.ResearchName; if (File.Exists(fileName + ".xls") || File.Exists(fileName + ".xlsx")) fileName += result.ResearchID; document = SpreadsheetDocument.Create(fileName + ".xlsx", SpreadsheetDocumentType.Workbook); workbook = document.AddWorkbookPart(); workbook.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbook.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook. Sheets sheets = document.WorkbookPart.Workbook. AppendChild<Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = document.WorkbookPart. GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" }; sheets.Append(sheet); /*SaveResearchInfo(result.ResearchID, result.ResearchName, result.ResearchType, result.ModelType, result.RealizationCount, result.Size, result.Edges); SaveResearchParameters(result.ResearchParameterValues); SaveGenerationParameters(result.GenerationParameterValues); for (int i = 0; i < result.EnsembleResults.Count; ++i) { SaveEnsembleResult(result.EnsembleResults[i], i); }*/ workbook.Workbook.Save(); document.Close(); }
public void PreencherPlanilha(MemoryStream memoryStream, Laudo laudo, Configuracao configuracao) { using (_document = SpreadsheetDocument.Open(memoryStream, true)) { try { _wbPart = _document.WorkbookPart; _sharedStringPart = GetSharedStringPart(); #region Laudo fl 1 if (SelectSheet("Laudo fl 1")) { #region Cabeçalho PreencherCampo("L8", laudo.Solicitante); PreencherCampo("W8", laudo.Referencia); #endregion #region Identificação if (laudo.Produto != null) PreencherCampo("B12", laudo.Produto.Descricao); if (laudo.Linha != null) PreencherCampo("W12", laudo.Linha.Descricao); if (laudo.Fonte != null) PreencherCampo("B15", laudo.Fonte.Descricao); PreencherCampo("B18", laudo.Imovel.NomeCliente); PreencherCampo("W18", laudo.Imovel.TipoLogradouro.Descricao); PreencherCampo("B21", string.Format("{0}, {1}", laudo.Imovel.Endereco, laudo.Imovel.Numero)); PreencherCampo("W21", laudo.Imovel.Complemento); PreencherCampo("B24", laudo.Imovel.Bairro); PreencherCampo("W24", laudo.Imovel.Cidade.Nome.ToUpper()); PreencherCampo("AH24", laudo.Imovel.Cidade.Estado.Sigla); #endregion #region Caracterização da Região SelecionarOpcao(((EnumUsosPredominantes)laudo.UsosPredominantes).ToString()); SelecionarServicosPublicos(laudo); SelecionarInfraEstruturasUrbanas(laudo); #endregion #region Terreno PreencherCampo("B35", EnumHelper.GetDescription((EnumFormaTerreno)laudo.FormaTerreno)); PreencherCampo("G35", EnumHelper.GetDescription((EnumCotaGreide)laudo.CotaGreideTerreno)); PreencherCampo("P35", EnumHelper.GetDescription((EnumInclinacaoTerreno)laudo.InclinacaoTerreno)); PreencherCampo("W35", EnumHelper.GetDescription((EnumSituacaoTerreno)laudo.SituacaoTerreno)); PreencherCampo("AE3", EnumHelper.GetDescription((EnumSuperficieTerreno)laudo.SuperficieTerreno)); PreencherCampo("B38", laudo.MedidaAreaTerreno); PreencherCampo("H38", laudo.MedidaFrenteTerreno); PreencherCampo("M38", laudo.MedidaFundosTerreno); PreencherCampo("R38", laudo.MedidaEsquerdaTerreno); PreencherCampo("X38", laudo.MedidaDireitaTerreno); PreencherCampo("AC38", laudo.FracaoIdealTerreno); #endregion #region Edificação PreencherCampo("B43", EnumHelper.GetDescription((EnumTipoEdificacao)laudo.TipoEdificacao)); PreencherCampo("H43", EnumHelper.GetDescription((EnumUsosPredio)laudo.UsoEdificacao)); PreencherCampo("O43", laudo.NumeroPavimentos); PreencherCampo("U43", laudo.IdadeEdificio); PreencherCampo("AB43", EnumHelper.GetDescription((EnumPosicaoEdificacao)laudo.PosicaoEdificacao)); PreencherCampo("B46", EnumHelper.GetDescription((EnumPadraoAcabamento)laudo.PadraoAcabamento)); PreencherCampo("H46", EnumHelper.GetDescription((EnumEstadoConservacao)laudo.EstadoConservacao)); PreencherCampo("M46", EnumHelper.GetDescription((EnumTetos)laudo.Tetos)); PreencherCampo("Q46", EnumHelper.GetDescription((EnumFechamentoParedes)laudo.FechamentoParedes)); PreencherCampo("X46", laudo.NumeroVagasCobertas); PreencherCampo("AD46", laudo.NumeroVagasDescobertas); PreencherCampo("G49", laudo.AreaUnidadePrivativa); PreencherCampo("L49", laudo.AreaUnidadeComum); PreencherCampo("Q49", laudo.AreaUnidadeTotal); PreencherCampo("G50", laudo.AreaEstacionamentoPrivativa); PreencherCampo("L50", laudo.AreaEstacionamentoComum); PreencherCampo("Q50", laudo.AreaEstacionamentoTotal); PreencherCampo("G51", laudo.AreaOutrosPrivativa); PreencherCampo("L51", laudo.AreaOutrosComum); PreencherCampo("Q51", laudo.AreaOutrosTotal); PreencherCampo("G52", laudo.AreaTotalPrivativa); PreencherCampo("L52", laudo.AreaTotalComum); PreencherCampo("Q52", laudo.AreaTotalAverbada); PreencherCampo("Y52", laudo.AreaTotalNaoAverbada); PreencherCampo("AE52", laudo.SomatorioAreas); PreencherCampo("B56", ObterDivisaoInterna(laudo)); PreencherCampo("B62", EnumHelper.GetDescription((EnumUsosPredio)laudo.UsoPredio)); PreencherCampo("N62", laudo.NumeroPavimentosPredio); PreencherCampo("S62", laudo.NumeroUnidadesPredio); PreencherCampo("X62", laudo.NumeroElevadoresPredio); PreencherCampo("AC62", EnumHelper.GetDescription((EnumPosicaoPredio)laudo.PosicaoPredio)); PreencherCampo("B65", EnumHelper.GetDescription((EnumPadraoAcabamento)laudo.PadraoAcabamento)); PreencherCampo("G65", EnumHelper.GetDescription((EnumEstadoConservacao)laudo.EstadoConservacaoPredio)); PreencherCampo("M65", laudo.IdentificacaoPavimentosPredio); PreencherCampo("AF65", laudo.IdadeAparentePredio); #endregion #region Avaliação PreencherCampo("B69", laudo.ValorAvaliacao); PreencherCampo("H69", laudo.ValorAvaliacaoExtenso); PreencherCampo("G73", laudo.AreaGlobal); PreencherCampo("Q73", laudo.AreaTerreno); PreencherCampo("W73", laudo.AreaEdificacao); PreencherCampo("AD73", laudo.AreaBenfeitorias); PreencherCampo("G74", laudo.ValorMetroQuadradoGlobal); PreencherCampo("Q74", laudo.ValorMetroQuadradoTerreno); PreencherCampo("W74", laudo.ValorMetroQuadradoEdificacao); PreencherCampo("AD74", laudo.ValorMetroQuadradoBenfeitorias); PreencherCampo("Q75", laudo.ProdutoTerreno); PreencherCampo("W75", laudo.ProdutoEdificacao); PreencherCampo("AD75", laudo.ProdutoBenfeitorias); PreencherCampo("G76", laudo.ValorTotalGlobal); PreencherCampo("AD76", laudo.ValorTotalItemizada); PreencherCampo("B80", laudo.PrecisaoFundamentacao); PreencherCampo("M80", EnumHelper.GetDescription((EnumMetodologiaAvaliacao)laudo.MetodologiaAvaliacao)); PreencherCampo("B83", EnumHelper.GetDescription((EnumDesempenhoMercado)laudo.DesempenhoMercado)); PreencherCampo("J83", EnumHelper.GetDescription((EnumAbsorcaoMercado)laudo.AbsorcaoMercado)); PreencherCampo("T83", EnumHelper.GetDescription((EnumNivelImobiliario)laudo.NivelOfertas)); PreencherCampo("AD83", EnumHelper.GetDescription((EnumNivelImobiliario)laudo.NivelDemanda)); #endregion #region Rodapé if (configuracao != null) PreencherCampo("G90", string.Format("{0} / {1}", configuracao.NomeEmpresa, configuracao.CNPJEmpresa)); if (!string.IsNullOrEmpty(laudo.LocalEmissaoLaudo)) PreencherCampo("B93", string.Format("{0} / {1}", laudo.LocalEmissaoLaudo, DateTime.Now.Date.ToString("dd/MM/yyyy"))); if (laudo.ResponsavelTecnico != null) { PreencherCampo("E96", string.Format("{0} / {1}", laudo.ResponsavelTecnico.Nome.ToUpper(), laudo.ResponsavelTecnico.CREA)); PreencherCampo("E97", laudo.ResponsavelTecnico.CPF); } if (laudo.RepresentanteLegalEmpresa != null) { PreencherCampo("T96", laudo.RepresentanteLegalEmpresa.Nome.ToUpper()); PreencherCampo("T97", laudo.RepresentanteLegalEmpresa.CPF); } #endregion } #endregion #region Laudo fl 2 if (SelectSheet("Laudo fl 2")) { #region Cabeçalho PreencherCampo("L6", laudo.Solicitante); PreencherCampo("W6", laudo.Referencia); #endregion #region Informações Complementares //SelecionarOpcao(laudo.EstabilidadeSolidez ? "EstSim" : "EstNao"); PreencherCampo("C12", laudo.EstabilidadeSolidezJustificativa); //SelecionarOpcao(laudo.ViciosConstrucao ? "VicioSim" : "VicioNao"); PreencherCampo("C17", laudo.ViciosConstrucaoRelacao); //SelecionarOpcao(laudo.Habitabilidade ? "HabitSim" : "HabitNao"); PreencherCampo("C22", laudo.HabitabilidadeJustificativa); switch ((EnumFatoresLiquidezValorImovel)laudo.FatoresLiquidezValorImovel) { case EnumFatoresLiquidezValorImovel.Val: //SelecionarOpcao("Val"); break; case EnumFatoresLiquidezValorImovel.Desval: //SelecionarOpcao("Desval"); break; case EnumFatoresLiquidezValorImovel.Nenh: //SelecionarOpcao("Nenh"); break; } PreencherCampo("C28", laudo.FatoresLiquidezExplicitacao); #endregion #region Garantia, Documentação Apresentada e Observações //SelecionarOpcao(laudo.AceitoComoGarantia == 0 ? "GarSim" : "GarNao"); PreencherCampo("B37", laudo.MatriculaRGI); PreencherCampo("I37", laudo.Oficio); PreencherCampo("T37", laudo.Comarca == null ? string.Empty : laudo.Comarca.Nome); PreencherCampo("B40", laudo.OutrosDocumentos); //SelecionarOpcao(laudo.Conformidade == 0 ? "DocSim" : "DocNao"); PreencherCampo("C45", laudo.Divergencia); PreencherCampo("C49", laudo.ObservacoesFinais); #endregion #region Rodapé if (configuracao != null) PreencherCampo("G69", string.Format("{0} / {1}", configuracao.NomeEmpresa, configuracao.CNPJEmpresa)); if (!string.IsNullOrEmpty(laudo.LocalEmissaoLaudo)) PreencherCampo("B72", string.Format("{0} / {1}", laudo.LocalEmissaoLaudo, DateTime.Now.Date.ToString("dd/MM/yyyy"))); if (laudo.ResponsavelTecnico != null) { PreencherCampo("E75", string.Format("{0} / {1}", laudo.ResponsavelTecnico.Nome.ToUpper(), laudo.ResponsavelTecnico.CREA)); PreencherCampo("E76", laudo.ResponsavelTecnico.CPF); } if (laudo.RepresentanteLegalEmpresa != null) { PreencherCampo("T75", laudo.RepresentanteLegalEmpresa.Nome.ToUpper()); PreencherCampo("T76", laudo.RepresentanteLegalEmpresa.CPF); } #endregion } #endregion #region Laudo fl 3 if (SelectSheet("Laudo fl 3")) { #region Cabeçalho PreencherCampo("L6", laudo.Solicitante); PreencherCampo("W6", laudo.Referencia); #endregion #region Identificação if (laudo.Produto != null) PreencherCampo("B10", laudo.Produto.Descricao); if (laudo.Linha != null) PreencherCampo("W10", laudo.Linha.Descricao); if (laudo.Fonte != null) PreencherCampo("B13", laudo.Fonte.Descricao); PreencherCampo("B16", laudo.Imovel.NomeCliente); PreencherCampo("W16", laudo.Imovel.TipoLogradouro.Descricao); PreencherCampo("B19", string.Format("{0}, {1}", laudo.Imovel.Endereco, laudo.Imovel.Numero)); PreencherCampo("W19", laudo.Imovel.Complemento); PreencherCampo("B22", laudo.Imovel.Bairro); PreencherCampo("W22", laudo.Imovel.Cidade.Nome); PreencherCampo("AH22", laudo.Imovel.Cidade.Estado.Sigla); #endregion #region Rodapé if (configuracao != null) PreencherCampo("G34", string.Format("{0} / {1}", configuracao.NomeEmpresa, configuracao.CNPJEmpresa)); if (!string.IsNullOrEmpty(laudo.LocalEmissaoLaudo)) PreencherCampo("B37", string.Format("{0} / {1}", laudo.LocalEmissaoLaudo, DateTime.Now.Date.ToString("dd/MM/yyyy"))); if (laudo.ResponsavelTecnico != null) { PreencherCampo("E40", string.Format("{0} / {1}", laudo.ResponsavelTecnico.Nome.ToUpper(), laudo.ResponsavelTecnico.CREA)); PreencherCampo("E41", laudo.ResponsavelTecnico.CPF); } if (laudo.RepresentanteLegalEmpresa != null) { PreencherCampo("T40", laudo.RepresentanteLegalEmpresa.Nome.ToUpper()); PreencherCampo("T41", laudo.RepresentanteLegalEmpresa.CPF); } #endregion } #endregion } catch { throw; } finally { _document.Close(); } } }