I want to change direction of XLSX file that is generated by the bellow code.
In excel software we can do this like:
MSDN says we can do this by Alignment.ReadingOrder but how i use it?
The following code get a set of data and make an excel file.
public class ExcelHelper
{
public class ExcelData
{
public string SheetName { get; set; }
public List<string> Headers { get; set; }
public List<List<string>> Rows { get; set; }
}
public static byte[] GenerateExcel(ExcelData data)
{
var memoryStream = new MemoryStream();
var spreadsheetDocument = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook);
var workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = data.SheetName ?? "Sheet 1",
};
sheets.AppendChild(sheet);
//--------------------------------
UInt32 rowIndex = 0;
//-------------------------------- header
var rowHeader = new Row { RowIndex = ++rowIndex };
foreach (var header in data.Headers)
{
var cell = new Cell
{
DataType = CellValues.InlineString,
//CellReference = header + rowIndex
};
var inlineString = new InlineString();
var text = new Text { Text = header ?? string.Empty };
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
rowHeader.AppendChild(cell);
}
sheetData.AppendChild(rowHeader);
//-------------------------------- data
foreach (var row in data.Rows)
{
var rowData = new Row { RowIndex = ++rowIndex };
foreach (var cellData in row)
{
var cell = new Cell
{
DataType = CellValues.InlineString,
//CellReference = cellData + rowIndex
};
var inlineString = new InlineString();
var text = new Text { Text = cellData ?? string.Empty };
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
rowData.AppendChild(cell);
}
sheetData.AppendChild(rowData);
}
workbookPart.Workbook.Save();
spreadsheetDocument.Close();
var bytes = memoryStream.ToArray();
memoryStream.Dispose();
return bytes;
}
}
I solved the problem by a trick ;)
Best practice is to create a template file by hand in MS Office Excel then open it by code and fill the target cells with the correct data.