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();
}
}
}
}
