public void WorksheetSetStylesTest()
{
List <string> values = new List <string>(new string[] { "alpha", "beta", "charlie", "delta" });
List <string> values2 = new List <string>(new string[] { "echo", "foxtrot", "golf", "hotel" });
//Write to the spreadsheet
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteValues("A2", values, CellValues.String);
writer.PasteValues("A3", values2, CellValues.String);
//The centre four styles should be aligned to center
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
style.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
writer.SetStyle(style, "B2", "C3");
//Set style in non existing cells
writer.SetStyle(style, "B5", "C6");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\stylerange.xlsx", GetOutputFolder()), stream);
}
public void WriteRow(string column, int row, List <string> values, SpreadsheetStyle style = null)
{
var startCell = column + row;
_writer.PasteValues(startCell, values, CellValues.String);
if (style != null)
{
var endCell = SpreadsheetReader.GetColumnName(column, values.Count - 1) + row;
_writer.SetStyle(style, startCell, endCell);
}
}
public void WorksheetMultipleStyleTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
style.IsItalic = true;
style.SetBackgroundColor("FF0000");//(red)
WorksheetWriter.PasteText(doc, worksheetPart, "B", 2, "Hello world");
WorksheetWriter.SetStyle(style, doc, worksheetPart, "B", 2);
style = SpreadsheetReader.GetDefaultStyle(doc);
style.IsBold = true;
style.SetBackgroundColor("0000FF");//(blue)
WorksheetWriter.SetStyle(style, doc, worksheetPart, "C", 3);
WorksheetWriter.PasteText(doc, worksheetPart, "C", 3, "Hello world2");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\styled2.xlsx", GetOutputFolder()), stream);
}
public void WorksheetAddStyleTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
SpreadsheetStyle defaultStyle = SpreadsheetReader.GetDefaultStyle(doc);
defaultStyle.IsItalic = true;
defaultStyle.IsBold = true;
defaultStyle.IsUnderline = true;
defaultStyle.SetColor("FF0000");
//r = 255, g = 0, b = 0 (Red)
defaultStyle.SetBackgroundColor("DDDDDD");
//(light grey)
defaultStyle.SetBorder("00FF00", BorderStyleValues.Medium);
//(green medium border)
WorksheetWriter.PasteText(doc, worksheetPart, "E", 5, "Hello world");
WorksheetWriter.SetStyle(defaultStyle, doc, worksheetPart, "E", 5);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\styled.xlsx", GetOutputFolder()), stream);
}
public void WorksheetBorderTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
//Fill a background cell to make sure it is not overwritten
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
style.SetBackgroundColor("C0C0C0");
//(grey)
writer.SetStyle(style, "B2");
writer.DrawBorder("B2", "D4", "FF0000", BorderStyleValues.Medium);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\border.xlsx", GetOutputFolder()), stream);
}
public void WorksheetMergeCellsTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
Cell cell = writer.PasteSharedText("B2", "Merged cells");
style.IsUnderline = true;
writer.SetStyle(style, "B2");
Cell cell2 = writer.FindCell("C2");
cell2.StyleIndex = cell.StyleIndex;
writer.MergeCells("B2", "C2");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\merge.xlsx", GetOutputFolder()), stream);
}