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);
}
}
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(IXlRowData rowData, SheetData sheetData, XlSharedStringsTable stringsTable, int rowIndex)
{
int columnIndex = 1;
Row row = new Row()
{
RowIndex = (uint)rowIndex
};
//sheetData.AppendChild(row);
foreach (IXlCell cell in rowData)
{
string cellReference = CellIndexHelper.FormatCellIndex(rowIndex, columnIndex);
Cell newCell = new Cell()
{
CellReference = cellReference, StyleIndex = cell.CellStyle
};
row.AppendChild(newCell);
SetCellValue(cell.CellValue, cell.CellType, newCell, stringsTable);
columnIndex++;
}
sheetData.AppendChild(row); //moved from above to ensure all cell writes are on the "standalone" row then just 1 DOM manip to insert row into sheet.
}
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 WriteWorksheets(WorkbookPart workbookPart, SaveContext context)
{
XlSharedStringsTable stringsTable = new XlSharedStringsTable(workbookPart, context);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
foreach (XlWorksheet sheet in Worksheets)
{
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(context.RelIdGenerator.GetNext(RelType.Workbook));
SheetData sheetData = new SheetData();
Worksheet worksheet = new Worksheet(sheetData);
worksheetPart.Worksheet = worksheet;
if (
!worksheet.NamespaceDeclarations.Contains(new KeyValuePair <String, String>("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships")))
{
worksheet.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
}
var sheetProperties = new SheetProperties {
TabColor = new TabColor()
};
string colorString = sheet.TabColor.HtmlColor;
sheetProperties.TabColor.Rgb = colorString;
worksheet.SheetProperties = sheetProperties;
AddTableData(sheet, sheetData, stringsTable);
worksheet.Save();
Sheet s = new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = (uint)(sheets.Count() + 1),
Name = sheet.Name
};
sheets.AppendChild(s);
workbookPart.Workbook.Save();
}
}
private static void AddCell(XlRowData rowData, XlSharedStringsTable stringTable, Cell cell, int intendedIndex)
{
uint style = cell.StyleIndex ?? 0U;
string cellValue = cell.CellValue != null ? cell.CellValue.InnerText : "";
string datatype = null;
if (cell.DataType == null)
{
datatype = CellValues.Number.ToString();
}
else if (cell.DataType.Value == CellValues.SharedString)
{
datatype = CellValues.String.ToString();
int stringIndex = Int32.Parse(cellValue);
cellValue = stringTable[stringIndex];
}
else
{
datatype = cell.DataType.Value.ToString();
}
XlCell newCell = new XlCell(style, typeof(string), cellValue, datatype);
InsertCell(rowData, newCell, intendedIndex);
}
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();
}
}
}
}
private static void SetCellValue(string cellValue, Type cellType, Cell newCell, XlSharedStringsTable stringsTable)
{
if (NumericTypes.IsNumeric(cellType))
{
newCell.CellValue = new CellValue(cellValue.ToString());
newCell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else if ((cellType.Name == "String") || (cellType.Name == "DateTime"))
{
// You can only mark a DateTime as a CellValues.Date if
// It conforms to a specific ISO 8601 format.
// It then is displayed as a number. So for the best presentation,
// we treat them as strings.
int stringIndex = stringsTable.LookupStringIndex(cellValue);
newCell.CellValue = new CellValue(stringIndex.ToString());
newCell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
}
