private ExcelStreamReader(string fileName) { this.fileName = fileName; this.doc = SpreadsheetDocument.Open(fileName, false); this.workbookPart = doc.WorkbookPart; this.sheets = GetSheets(); }
// Given a WorkbookPart, inserts a new worksheet. private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart) { // Add a new worksheet part to the workbook. WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>(); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); // Get a unique ID for the new sheet. uint sheetId = 1; if (sheets.Elements<Sheet>().Count() > 0) { sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1; } string sheetName = "Sheet" + sheetId; // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); workbookPart.Workbook.Save(); return newWorksheetPart; }
public OpenXLRow(WorkbookPart wbPart, WorksheetPart wsPart, List<string> columns, Row xRow) { this.WbPart = wbPart; this.WsPart = wsPart; this.Columns = columns; this._cells = GetCells(xRow); }
public static string GetCellValue(WorkbookPart wbPart, Cell theCell) { string value = theCell.InnerText; if (theCell.DataType != null) { switch (theCell.DataType.Value) { case CellValues.SharedString: var stringTable = wbPart. GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); if (stringTable != null) { value = stringTable.SharedStringTable. ElementAt(int.Parse(value)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } return value; }
public static Sheet FindSheet(string sheetName, WorkbookPart workbookPart) { if (string.IsNullOrEmpty(sheetName)) { return workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(); } return workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>(); }
public string GetValueCell(ref WorkbookPart wbPart, ref WorksheetPart wsPart, string addressName) { string value = null; Cell theCell = wsPart.Worksheet.Descendants<Cell>(). Where(c => c.CellReference == addressName).FirstOrDefault(); value = GetCleanValueCell(theCell, ref wbPart); return value; }
/// <summary> /// Создает экземпляр класса для работы с текущим файлом /// </summary> /// <param name="filePath">Путь к документу</param> /// <param name="removeAfterDestroy">Удалять ли файл после окончания работы с ним</param> public ExcellWorker(string filePath, bool removeAfterDestroy) { _currentFilePath = filePath; _currentDocument = SpreadsheetDocument.Open(filePath, true); _currentWorkBookPart = _currentDocument.WorkbookPart; _currentSheet = _currentWorkBookPart.Workbook.Descendants<Sheet>().FirstOrDefault(); RemoveAfterDestroy = removeAfterDestroy; }
public static WorksheetPart GetWorksheetFromSheetName(WorkbookPart workbookPart, string sheetName) { var sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName); if (sheet == null) { return null; } return workbookPart.GetPartById(sheet.Id) as WorksheetPart; }
public static SharedStringItem GetSharedStringValue(WorkbookPart workbookPart, Cell cell, ref int id) { if (int.TryParse(cell.InnerText, out id)) { var text = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id); return text; } return null; }
static bool AddCellUnit(int columnIndex, ref int exportColumnIndex, SheetConfig Sheetcfg, Cell cell, WorkbookPart workbookPart, List<string> stylesList, List<CellUnit> cellUnitList) { if (columnIndex >= Sheetcfg.ColumnConfigData.Count) goto Error; var columnConfig = Sheetcfg.ColumnConfigData[columnIndex]; if (!columnConfig.Export) return true; var dataType = columnConfig.FieldType; string cellValue = cell == null ? "" : GetCellValue(cell, workbookPart, stylesList); CellUnit cellUnit = null; switch (dataType) { case DesignerDataType.Int: { int intValue = 0; if (!int.TryParse(cellValue, out intValue) && !string.IsNullOrEmpty(cellValue)) goto Error; cellUnit = new CellUnitIntValue(exportColumnIndex, intValue); } break; case DesignerDataType.Float: { float floatValue = 0.0f; if (!float.TryParse(cellValue, out floatValue) && !string.IsNullOrEmpty(cellValue)) goto Error; cellUnit = new CellUnitFloatValue(exportColumnIndex, floatValue); } break; case DesignerDataType.String: { cellUnit = new CellUnitStringValue(exportColumnIndex, cellValue); } break; case DesignerDataType.Bool: { decimal decimalValue = 0; cellUnit = new CellUnitBoolValue(exportColumnIndex, decimal.TryParse(cellValue, out decimalValue) && decimalValue != 0); } break; } if (cellUnit.IsNeedWrite()) cellUnitList.Add(cellUnit); ++exportColumnIndex; return true; Error: string excelFileName = ""; if (!dicOutputFileName.TryGetValue(Sheetcfg.Name, out excelFileName)) excelFileName = ""; Console.Error.WriteLine(string.Format(Resources.ExcelConfigFileInValid, excelFileName, Sheetcfg.Name)); return false; }
public void Close() { if (_doc != null) { _doc.Close(); _expectSheets = null; _wbPart = null; _shareStringPart = null; } }
internal SheetCollection(ref DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart, IList <Spreadsheet.Sheet> list) : this(ref workbookPart) { using (IEnumerator <Spreadsheet.Sheet> enumerator = list.GetEnumerator()) { while (enumerator.MoveNext()) { this.Add(enumerator.Current); } } }
public ExcelHelper Create(string ExcelFile) { System.IO.FileInfo fi = new System.IO.FileInfo(ExcelFile); if (!fi.Directory.Exists) fi.Directory.Create(); _doc = SpreadsheetDocument.Create(ExcelFile, SpreadsheetDocumentType.Workbook); _wbPart = _doc.AddWorkbookPart(); _wbPart.Workbook = new Workbook(); _doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets()); return _instance; }
protected string GetCellValue(WorkbookPart wbPart, Cell theCell) { if (theCell == null) { return null; } var value = theCell.InnerText; if (theCell.DataType == null) { return value; } switch (theCell.DataType.Value) { case (CellValues.SharedString): { var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); if (stringTable != null) { value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText; } break; } case (CellValues.Boolean): { switch (value) { case "0": { value = "FALSE"; break; } default: { value = "TRUE"; break; } } break; } case (CellValues.Date): { if (!string.IsNullOrEmpty(value)) { value = DateTime.FromOADate(Convert.ToDouble(value)).ToString(CultureInfo.CurrentCulture); } break; } } return value; }
// Generates content of workbookPart1. private static void GenerateWorkbookPart1Content(WorkbookPart workbookPart1) { Workbook workbook1 = new Workbook(); workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); Sheets sheets1 = new Sheets(); Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" }; sheets1.Append(sheet1); workbook1.Append(sheets1); workbookPart1.Workbook = workbook1; }
public List<RPCFan> RetrieveFirstObject(WorkbookPart wbPart, Sheet theSheet) { string abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); Cell theCell = wsPart.Worksheet.Descendants<Cell>(). Where(c => c != null).FirstOrDefault(); string first = FindCellInnerValue(wbPart, theCell); var letra = first.Substring(0, 1); int numeros = int.Parse(first.Substring(1)); int i = abc.IndexOf(letra); List<RPCFan> retorno = new List<RPCFan>(); int countFans = 0; int numeroscopy = numeros; while (theCell != null) { string newRef = abc[i].ToString() + (numeroscopy++ + 1).ToString(); theCell = wsPart.Worksheet.Descendants<Cell>(). Where(c => c.CellReference == newRef).FirstOrDefault(); countFans++; } for (int ii = 0; ii < (countFans - 1); ii++) { string newRef; i = abc.IndexOf(letra); theCell = new Cell(); var fan = new RPCFan(); List<string> data = new List<string>(); while (theCell != null) { newRef = abc[i++].ToString() + (numeros + 1).ToString(); theCell = wsPart.Worksheet.Descendants<Cell>(). Where(c => c.CellReference == newRef).FirstOrDefault(); var value = FindCellInnerValue(wbPart, theCell, false); data.Add(value); //i++; } fan.YaEntro = false; fan.LastCell = abc[i-2].ToString() + (numeros + 1).ToString(); fan.ID = data[0]; fan.Nombre = data[1]; fan.CardNum = data[2]; fan.DNI = data[3]; fan.Ingreso = data[4]; if (data[5]!="0") fan.YaEntro = true; retorno.Add(fan); numeros++; } return retorno; }
private void AddReportColumns(WorkbookPart workbookPart, WorksheetPart worksheetPart, List<string> columnNames) { for (int i = 0; i < columnNames.Count; i++) { string fieldName = columnNames[i]; WorkbookHelper.AddCellText( workbookPart, worksheetPart, fieldName, (uint)(i + 1), 1U); } }
public void CreateExcel(string xlFilePath) { FileInfo xlFileInfo = new FileInfo(xlFilePath); if (xlFileInfo.Exists) { xlFileInfo.Delete(); } xlDoc = SpreadsheetDocument.Create(xlFilePath, SpreadsheetDocumentType.Workbook); wbPart = xlDoc.AddWorkbookPart(); wbPart.Workbook = new Workbook(); }
private string ReadExcelCell(Cell cell, WorkbookPart workbookPart) { var cellValue = cell.CellValue; var text = (cellValue == null) ? cell.InnerText : cellValue.Text; if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString)) { text = workbookPart.SharedStringTablePart.SharedStringTable .Elements<SharedStringItem>().ElementAt( Convert.ToInt32(cell.CellValue.Text)).InnerText; } return (text ?? string.Empty).Trim(); }
private static Dictionary<int, string> GetHeaders(SheetData sheetData, WorkbookPart workbookPart) { var headers = new Dictionary<int, string>(); var headerCells = sheetData.Elements<Row>().First().Elements<Cell>().ToList(); for (var i = 0; i < headerCells.Count; i++) { var cellValue = ExcelFileInceptor.GetCellValueAsString(workbookPart, headerCells[i]); headers.Add(i, cellValue); } return headers; }
private WorksheetPart wsSheet = null; // WorkSheetPart #endregion Fields #region Constructors public LogGenerator(string fn) { //// get spreadsheet path from constructor //path = folder; //// File name is based on date and time //DateTime now = DateTime.Now; //// Construct the spreadsheet filename //string fn = string.Format("{0}\\report_{1}-{2}-{3}_{4}{5}{6}.xlsx", // path, now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second); if (File.Exists(fn)) { doc = SpreadsheetDocument.Open(fn, true); } else { // // Create the Spreadsheet document // doc = SpreadsheetDocument.Create(fn, SpreadsheetDocumentType.Workbook); // // Add WoorkBookPart to the document // wbPart = doc.AddWorkbookPart(); wbPart.Workbook = new Workbook(); wbPart.Workbook.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main"); // // Add WorkSheetPart to the WorkBookPart // wsSheet = wbPart.AddNewPart<WorksheetPart>(); wsSheet.Worksheet = new Worksheet(new SheetData()); wsSheet.Worksheet.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main"); // // Add sheets to the WorkBook // sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets()); // // Append new sheet and associate it with the workbook // sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(wsSheet), SheetId = 1, Name = wsName }; sheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); sheets.Append(sheet); wbPart.Workbook.Save(); //CreatePackage(fn); //if (File.Exists(fn)) //{ // TestXls(); //} } }
/// <summary> /// 根据WorkbookPart获取所有SheetName /// </summary> /// <param name="workBookPart"></param> /// <returns>SheetName集合</returns> private List<string> GetSheetNames(WorkbookPart workBookPart) { List<string> sheetNames = new List<string>(); Sheets sheets = workBookPart.Workbook.Sheets; foreach (Sheet sheet in sheets) { string sheetName = sheet.Name; if (!string.IsNullOrEmpty(sheetName)) { sheetNames.Add(sheetName); } } return sheetNames; }
private void AddReportLines(WorkbookPart workbookPart, WorksheetPart worksheetPart, Report report, uint startRow) { for (int iRow = 0; iRow < report.ReportLines.Count; iRow++) { for (int iCol = 0; iCol < report.ColumnNames.Count; iCol++) { WorkbookHelper.AddCellText( workbookPart, worksheetPart, report[iRow][iCol].ToString(), (uint)iCol + 1, (uint)iRow + startRow + 1); } } }
/// <summary> /// 根据WorkbookPart和sheetName获取该Sheet下所有Row数据 /// </summary> /// <param name="workBookPart">WorkbookPart对象</param> /// <param name="sheetName">SheetName</param> /// <returns>该SheetName下的所有Row数据</returns> public IEnumerable<Row> GetWorkBookPartRows(WorkbookPart workBookPart, string sheetName) { IEnumerable<Row> sheetRows = null; //根据表名在WorkbookPart中获取Sheet集合 IEnumerable<Sheet> sheets = workBookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName); if (sheets.Count() == 0) { return null;//没有数据 } WorksheetPart workSheetPart = workBookPart.GetPartById(sheets.First().Id) as WorksheetPart; //获取Excel中得到的行 sheetRows = workSheetPart.Worksheet.Descendants<Row>(); return sheetRows; }
static void SaveDocument(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Spreadsheet document) { var exportedWorkbook = new Excel.Workbook(); exportedWorkbookPart.Workbook = exportedWorkbook; Excel.Sheets exportedSheets = new Excel.Sheets(); exportedWorkbook.AppendChild(exportedSheets); uint sheetId = 1; foreach (var sheet in document.Sheets) { SaveSheet(exportedWorkbookPart, styleSheet, cellFormatList, exportedSheets, sheet, sheetId++); } }
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(); }
/// <summary> /// Creates the sheet /// </summary> /// <param name="sheets">The sheet for this item</param> /// <param name="spreadsheetDocument">The spreadsheet containing the sheets</param> /// <param name="workbookPart">The workbookpart associated with the spreadsheet</param> public void CreateSheet(Sheets sheets, SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookPart) { // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheet sheet = new Sheet { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 3U, // <-- Change for each sheet that is created Name = _groupOrder.SheetName }; sheets.Append(sheet); //Add cells to the sheet InsertTextIntoCells(spreadsheetDocument, worksheetPart); }
public void CreateSheet(Sheets sheets, SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookPart) { // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheet sheet = new Sheet { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 4U, Name = _resources.SheetName }; sheets.Append(sheet); //Add cells to the sheet InsertTextIntoCells(spreadsheetDocument, worksheetPart); }
/// <summary> /// Adds a given worksheet to the document /// </summary> /// <param name="worksheet">Worksheet document to add</param> /// <returns>Worksheet part just added</returns> public OpenXmlSDK.WorksheetPart Add(XDocument worksheet) { // Associates base content to a new worksheet part OpenXmlSDK.WorkbookPart workbook = ((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart; OpenXmlSDK.WorksheetPart worksheetPart = workbook.AddNewPart <OpenXmlSDK.WorksheetPart>(); XDocument worksheetDocument = parentDocument.GetXDocument(worksheetPart); if (worksheetDocument.Root == null) { worksheetDocument.Add( new XElement("root") ); } worksheetDocument.Root.ReplaceWith(worksheet.Root); // Associates the worksheet part to the workbook part XDocument document = parentDocument.GetXDocument(((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart); int sheetId = document.Root .Element(ns + "sheets") .Elements(ns + "sheet") .Count() + 1; int worksheetCount = document.Root .Element(ns + "sheets") .Elements(ns + "sheet") .Where( t => t.Attribute("name").Value.StartsWith("sheet", StringComparison.OrdinalIgnoreCase) ) .Count() + 1; // Adds content to workbook document to reference worksheet document document.Root .Element(ns + "sheets") .Add( new XElement(ns + "sheet", new XAttribute("name", string.Format("sheet{0}", worksheetCount)), new XAttribute("sheetId", sheetId), new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(worksheetPart)) ) ); return(worksheetPart); }
public static void AddCellText(WorkbookPart workbookPart, WorksheetPart worksheetPart, string text, uint colIndex, uint rowIndex) { string columnName = GetColumnIdentifier((int)colIndex); // Insert the text into the SharedStringTablePart. int index = InsertSharedStringItem(workbookPart, text); // Insert cell A1 into the new worksheet. Cell cell = InsertCellInWorksheet(worksheetPart, columnName, rowIndex); // Set the value of cell A1. cell.CellValue = new CellValue(index.ToString()); cell.DataType = new EnumValue<CellValues>(CellValues.SharedString); }
/// <summary> /// Creates an empty (base) SpreadsheetDocument /// </summary> private static SpreadsheetDocument Create(string filePath) { SpreadsheetDocument document = new SpreadsheetDocument(filePath, true); OpenXmlSDK.WorkbookPart workbookPart = ((OpenXmlSDK.SpreadsheetDocument)document.Document).AddWorkbookPart(); XDocument workbookDocument = document.GetXDocument(workbookPart); //Creates an empty workbook XDocument workbookContent = CreateEmptyWorkbook(); if (workbookDocument.Root == null) { workbookDocument.Add(workbookContent.Root); } else { workbookDocument.Root.ReplaceWith(workbookContent.Root); } return(document); }
public static object GetCellValue(WorkbookPart workbookPart, Cell theCell) { int a = 1; if (theCell.DataType == null) { return theCell.CellValue.Text; } var s = theCell.DataType; if (theCell.DataType.InnerText.Equals("str")) { return Convert.ToString(theCell.CellValue.Text); } if (theCell.DataType.InnerText.Equals("n")) { return Convert.ToDecimal(theCell.CellValue.Text); } return theCell.DataType == null ? theCell.CellValue.Text : workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(Convert.ToInt32(theCell.CellValue.Text)).InnerText; }
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; } }
public Worksheet AddSheet(WorkbookPart workbookPart, string name) { var sheets = workbookPart.Workbook.GetFirstChild<Sheets>(); // add single Sheet var workSheetpart = workbookPart.AddNewPart<WorksheetPart>(); workSheetpart.Worksheet = new Worksheet(new SheetData()); workSheetpart.Worksheet.Save(); var sheet = new Sheet { Id = workbookPart.GetIdOfPart(workSheetpart), SheetId = (uint)(workbookPart.Workbook.Sheets.Count() + 1), Name = name }; sheets.Append(sheet); workbookPart.Workbook.Save(); return workSheetpart.Worksheet; }
public WorkbookIndexer(WorkbookPart toIndex) { if (toIndex == null) { throw new ArgumentNullException("workbookPart"); } foreach (Sheet sheet in toIndex.Workbook.Sheets) { var worksheet = (WorksheetPart)toIndex.GetPartById(sheet.Id); if (worksheet != null) { this.worksheets.Add(new WorksheetIndexer(worksheet, sheet)); } } // TODO: This should be modified to create the shared string table part instead of just bailing on an empty worksheet. this.SharedStringTable = toIndex.SharedStringTablePart != null ? new SharedStringTableIndexer(toIndex.SharedStringTablePart) : null; }
internal Workbook(ref SpreadsheetDocument spreadsheetDocument) { this.spreadsheetDocument = spreadsheetDocument; if (this.spreadsheetDocument.WorkbookPart == null) { this.workbookPart = this.spreadsheetDocument.AddWorkbookPart(); } else { this.workbookPart = this.spreadsheetDocument.WorkbookPart; } if (this.spreadsheetDocument.WorkbookPart.Workbook == null) { this.workbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); } this.sheetCollection = new Spreadsheet.SheetCollection(ref this.workbookPart); this.InitializeWorksheets(); }
/* * static Excel.SharedStringTable SaveSharedStringTable() * { * var exportedSharedStringTable = new Excel.SharedStringTable(); * * return exportedSharedStringTable; * } */ static void SaveSheet(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Excel.Sheets exportedSheets, Sheet sheet, uint sheetId) { var exportedWorksheetPart = exportedWorkbookPart.AddNewPart <Package.WorksheetPart>(); string relId = exportedWorkbookPart.GetIdOfPart(exportedWorksheetPart); var exportedWorksheet = new Excel.Worksheet(); exportedWorksheetPart.Worksheet = exportedWorksheet; var exportedColumns = new Excel.Columns(); exportedWorksheet.Append(exportedColumns); var exportedSheetData = new Excel.SheetData(); exportedWorksheet.Append(exportedSheetData); var exportedSheet = new Excel.Sheet() { Name = sheet.Name, Id = relId, SheetId = sheetId }; if (sheet.Hidden) { exportedSheet.State = Excel.SheetStateValues.Hidden; } exportedSheets.Append(exportedSheet); foreach (var column in sheet.Columns.OrderBy(r => r.Index)) { SaveColumn(exportedColumns, column); } foreach (var row in sheet.Rows.OrderBy(r => r.Index)) { SaveRow(exportedSheetData, styleSheet, cellFormatList, row); } exportedWorksheetPart.Worksheet.Save(); }
static Excel.Stylesheet SaveStyleSheet(Package.WorkbookPart exportedWorkbookPart, ref Dictionary <CellFormat, uint> cellFormatList, Spreadsheet document) { var exportedStyleSheetPart = exportedWorkbookPart.AddNewPart <Package.WorkbookStylesPart>(); cellFormatList = new Dictionary <CellFormat, uint>(); Excel.Stylesheet exportedStyleSheet = new Excel.Stylesheet(); exportedStyleSheetPart.Stylesheet = exportedStyleSheet; exportedStyleSheet.CellFormats = new Excel.CellFormats(); exportedStyleSheet.CellFormats.Append(new Excel.CellFormat() { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 }); exportedStyleSheet.Fills = new Excel.Fills(); exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill() { PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.None) })); exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill() { PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.Gray125) })); exportedStyleSheet.Fonts = new Excel.Fonts(); exportedStyleSheet.Fonts.Append(new Excel.Font() { FontSize = new Excel.FontSize() { Val = 11 }, Color = new Excel.Color() { Theme = 1 }, FontName = new Excel.FontName() { Val = "Calibri" }, FontFamilyNumbering = new Excel.FontFamilyNumbering() { Val = 2 }, FontScheme = new Excel.FontScheme() { Val = new OpenXml.EnumValue <Excel.FontSchemeValues>(Excel.FontSchemeValues.Minor) } }); exportedStyleSheet.Borders = new Excel.Borders(); exportedStyleSheet.Borders.Append(new Excel.Border() { LeftBorder = new Excel.LeftBorder(), RightBorder = new Excel.RightBorder(), TopBorder = new Excel.TopBorder(), BottomBorder = new Excel.BottomBorder(), DiagonalBorder = new Excel.DiagonalBorder() }); exportedStyleSheet.CellStyleFormats = new Excel.CellStyleFormats(); exportedStyleSheet.CellStyleFormats.Append(new Excel.CellFormat() { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0 }); exportedStyleSheet.CellStyles = new Excel.CellStyles(); exportedStyleSheet.CellStyles.Append(new Excel.CellStyle() { Name = "Normal", FormatId = 0, BuiltinId = 0 }); exportedStyleSheet.DifferentialFormats = new Excel.DifferentialFormats(); exportedStyleSheet.TableStyles = new Excel.TableStyles() { DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" }; foreach (var cellFormat in document.CellFormats()) { if (cellFormat.Count() > 0) { cellFormatList.Add(cellFormat, (uint)exportedStyleSheet.CellFormats.ChildElements.Count()); exportedStyleSheet.CellFormats.Append(new Excel.CellFormat() { NumberFormatId = 0, FontId = 0, BorderId = 0, FormatId = 0, FillId = (uint)exportedStyleSheet.Fills.ChildElements.Count(), ApplyFill = true }); exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill() { PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.Solid), ForegroundColor = new Excel.ForegroundColor() { Rgb = new OpenXml.HexBinaryValue(cellFormat.FillColor) } })); } } exportedStyleSheet.Save(); return(exportedStyleSheet); }
internal SheetCollection(ref DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart) { this.workbookPart = workbookPart; sheets = this.workbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets()); this.list = new List <Spreadsheet.Sheet>(); }
/// <summary> /// Creates a chartsheet part from given data /// </summary> /// <param name="chartType">Type of chart to generate</param> /// <param name="values">Values to represent in the chart</param> /// <param name="headerReference">Columns to be used as series</param> /// <param name="categoryReference">Column to be used as category</param> /// <returns>Chartsheet part with contents related</returns> public OpenXmlSDK.ChartsheetPart Create(ChartType chartType, List <string> values, List <string> headerReference, string categoryReference) { //Creates base content and associates it to a new chartsheet part XDocument chartsheet = CreateEmptyChartsheet(); OpenXmlSDK.WorkbookPart workbook = ((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart; OpenXmlSDK.ChartsheetPart chartsheetPart = workbook.AddNewPart <OpenXmlSDK.ChartsheetPart>(); XDocument chartsheetDocument = parentDocument.GetXDocument(chartsheetPart); XDocument newChartsheetDocument = CreateEmptyChartsheet(); if (chartsheetDocument.Root == null) { chartsheetDocument.Add( newChartsheetDocument.Root ); } else { chartsheetDocument.Root.ReplaceWith(newChartsheetDocument.Root); } //Creates a base drawings part and associates it to the chartsheet part OpenXmlSDK.DrawingsPart drawingsPart = chartsheetPart.AddNewPart <OpenXmlSDK.DrawingsPart>(); XDocument drawingsDocument = parentDocument.GetXDocument(drawingsPart); XDocument newDrawingDocument = CreateEmptyDrawing(); if (drawingsDocument.Root == null) { drawingsDocument.Add( newDrawingDocument.Root ); } else { drawingsDocument.Root.ReplaceWith(newDrawingDocument.Root); } //Adds content to chartsheet document to reference drawing document chartsheetDocument .Element(ns + "chartsheet") .Add( new XElement(ns + "drawing", new XAttribute(relationshipsns + "id", chartsheetPart.GetIdOfPart(drawingsPart)) ) ); //creates the chart part and associates it to the drawings part OpenXmlSDK.ChartPart chartPart = drawingsPart.AddNewPart <OpenXmlSDK.ChartPart>(); XDocument chartDocument = parentDocument.GetXDocument(chartPart); XDocument newChartDocument = CreateChart(chartType, values, headerReference, categoryReference);// CreateEmptyChart(); if (chartDocument.Root == null) { chartDocument.Add( newChartDocument.Root ); } else { chartDocument.Root.ReplaceWith(newChartDocument.Root); } //Adds content to drawing document to reference chart document drawingsDocument .Descendants(drawingns + "graphicData") .First() .Add( new XAttribute("uri", chartns), new XElement(chartns + "chart", new XAttribute(XNamespace.Xmlns + "c", chartns), new XAttribute(XNamespace.Xmlns + "r", relationshipsns), new XAttribute(relationshipsns + "id", drawingsPart.GetIdOfPart(chartPart)) ) ); //Associates the chartsheet part to the workbook part XDocument document = parentDocument.GetXDocument(((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart); int sheetId = document.Root.Element(ns + "sheets").Elements(ns + "sheet").Count() + 1; int chartsheetCount = document.Root .Element(ns + "sheets") .Elements(ns + "sheet") .Where( t => t.Attribute("name").Value.StartsWith("chart") ) .Count() + 1; //Adds content to workbook document to reference chartsheet document document.Root .Element(ns + "sheets") .Add( new XElement(ns + "sheet", new XAttribute("name", string.Format("chart{0}", chartsheetCount)), new XAttribute("sheetId", sheetId), new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(chartsheetPart)) ) ); return(chartsheetPart); }