private void AddTableData(XlWorksheet sheet, SheetData sheetData, XlSharedStringsTable stringsTable) { int rowIndex = 1; AddRow(sheet.Schema, sheetData, stringsTable, rowIndex++); foreach (IXlRowData rowData in sheet.Rows) { AddRow(rowData, sheetData, stringsTable, rowIndex++); } }
private void TempAddTableData(WorksheetPart wsPart, StringValue sheetName, XlSharedStringsTable stringTable) { Worksheet worksheet = wsPart.Worksheet; SheetProperties properties = worksheet.SheetProperties; XlColor color = new XlColor(properties.TabColor.Rgb); XlWorksheet ws = Worksheets.AddWorksheet(sheetName, color, null); List <SheetData> data = new List <SheetData>(worksheet.Elements <SheetData>()); foreach (SheetData sheetData in data) { AddTableData(sheetData, ws, stringTable); } }
public void WorksheetFirstDefaultNameTest() { Color testColor = Color.OldLace; string expectedName = "Sheet 1"; int expectedTabColor = testColor.ToArgb(); Osrs.Oncor.Excel.XlColor tabColor = new Osrs.Oncor.Excel.XlColor(expectedTabColor); Osrs.Oncor.Excel.XlWorksheets sheets = new Osrs.Oncor.Excel.XlWorksheets(); Osrs.Oncor.Excel.XlWorksheet sheet = sheets.AddWorksheet(tabColor); string actualName = sheet.Name; Assert.AreEqual(expectedName, actualName); }
public void WorksheetNameAndTabColorTest() { Color testColor = Color.OldLace; string expectedName = "Sheet 1"; int expectedTabColor = testColor.ToArgb(); Osrs.Oncor.Excel.XlColor tabColor = new Osrs.Oncor.Excel.XlColor(expectedTabColor); Osrs.Oncor.Excel.XlWorksheet sheet = new Osrs.Oncor.Excel.XlWorksheet(expectedName, tabColor, 1, null); int actualTabColor = sheet.TabColor.ArgbColor; Assert.AreEqual(expectedTabColor, actualTabColor); string actualName = sheet.Name; Assert.AreEqual(expectedName, actualName); }
private void AddTableData(SheetData sheetData, XlWorksheet worksheet, XlSharedStringsTable stringTable) { uint expectedRow = 1; bool rowsInOrder = true; IEnumerable <Row> rows = sheetData.Elements <Row>(); foreach (Row row in rows) { if (rowsInOrder) { if (row.RowIndex == expectedRow) { AddRow(worksheet, stringTable, row); expectedRow++; } //else //{ // rowsInOrder = false; //} } } }
private static void AddRow(XlWorksheet worksheet, XlSharedStringsTable stringTable, Row row) { int expectedColumn = 1; bool columnsInOrder = true; XlRowData rowData = new XlRowData(); IEnumerable <Cell> cells = row.Elements <Cell>(); foreach (Cell cell in cells) { //if (columnsInOrder) //{ if (cell.CellReference == null) { columnsInOrder = false; } else { int[] indexes = CellIndexHelper.IndexesFromReference(cell.CellReference); int rowIndex = indexes[0]; int columnIndex = indexes[1]; AddCell(rowData, stringTable, cell, columnIndex); if (columnIndex == expectedColumn && rowIndex == row.RowIndex) { expectedColumn++; } else { columnsInOrder = false; } } //} } //if (columnsInOrder) //{ worksheet.Rows.AddRow(rowData); //} }
private void AddSheetTabColor(XlWorksheet worksheet, SheetProperties properties) { XlColor color = new XlColor(properties.TabColor.Rgb); worksheet.TabColor = color; }
private void LoadSpreadsheetDocument(SpreadsheetDocument dSpreadsheet) { if (dSpreadsheet.CustomFilePropertiesPart != null) { foreach (var m in dSpreadsheet.CustomFilePropertiesPart.Properties.Elements <CustomDocumentProperty>()) { String name = m.Name.Value; if (m.VTLPWSTR != null) { Properties.AddCustomProperty(name, m.VTLPWSTR.Text); } } } WorkbookPart workbookPart = dSpreadsheet.WorkbookPart; XlSharedStringsTable stringTable = new XlSharedStringsTable(workbookPart, null); var sheets = dSpreadsheet.WorkbookPart.Workbook.Sheets; foreach (Sheet dSheet in sheets.OfType <Sheet>()) { var sheetName = dSheet.Name; var wsPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart; if (wsPart != null) { XlWorksheet ws = Worksheets.AddWorksheet(sheetName); using (var reader = OpenXmlReader.Create(wsPart)) { // We ignore lots of parts. Type[] ignoredElements = new Type[] { typeof(SheetFormatProperties), typeof(SheetViews), typeof(MergeCells), typeof(AutoFilter), typeof(SheetProtection), typeof(DataValidations), typeof(ConditionalFormatting), typeof(Hyperlinks), typeof(PrintOptions), typeof(PageMargins), typeof(PageSetup), typeof(HeaderFooter), typeof(RowBreaks), typeof(Columns), typeof(ColumnBreaks), typeof(LegacyDrawing), typeof(CustomSheetViews) // Custom sheet views contain its own auto filter data, and more, which should be ignored for now }; while (reader.Read()) { while (ignoredElements.Contains(reader.ElementType)) { reader.ReadNextSibling(); } if (reader.ElementType == typeof(Row)) { AddRow(ws, stringTable, (Row)reader.LoadCurrentElement()); } else if (reader.ElementType == typeof(SheetProperties)) { AddSheetTabColor(ws, (SheetProperties)reader.LoadCurrentElement()); } } reader.Close(); } } } }