public void WorksheetDataTablePasteColumnsTemplate()
{
DataTable dt = new DataTable();
//Manually create table for mockup purposes
dt.TableName = "tblParameters";
dt.Columns.Add("intID", typeof(int));
dt.Columns.Add("vchrDescription", typeof(string));
dt.Columns.Add("decPrice", typeof(float));
dt.AcceptChanges();
dt.Rows.Add(new object[] { 1, "Parts", 1.00F });
dt.Rows.Add(new object[] { 2, "Cheque", 1.50F });
dt.Rows.Add(new object[] { 3, "Products", 1.45F });
dt.Rows.Add(new object[] { 4, "Gifts", 0.00F });
dt.Rows.Add(new object[] { 5, "DealerRepair", 2.50F });
//Write to the spreadsheet
MemoryStream stream = SpreadsheetReader.Copy(string.Format("{0}\\Templates\\template.xlsx", Directory.GetCurrentDirectory()));
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteDataTable(dt, "B3", new List <string>(new string[] { "vchrDescription" }));
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\datatabletemplate.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 WorksheetDataTablePasteTest()
{
DataTable dt = new DataTable();
//Manually create table for mockup purposes
dt.TableName = "tblParameters";
dt.Columns.Add("intID", typeof(int));
dt.Columns.Add("vchrDescription", typeof(string));
dt.Columns.Add("dteValidFrom", typeof(DateTime));
dt.Columns.Add("decPrice", typeof(float));
dt.Columns.Add("bitFlag", typeof(Boolean));
dt.AcceptChanges();
dt.Rows.Add(new object[] { 1, "Parts", new DateTime(1974, 1, 2), 1.00F, true });
dt.Rows.Add(new object[] { 2, "Cheque", new DateTime(1974, 2, 2), 1.50F, false });
dt.Rows.Add(new object[] { 3, "Products", new DateTime(1974, 3, 2), 1.45F, true });
dt.Rows.Add(new object[] { 4, "Gifts", new DateTime(1974, 4, 2), 0.00F, false });
dt.Rows.Add(new object[] { 5, "DealerRepair", new DateTime(1974, 5, 2), 2.50F, true });
//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);
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
style.IsBold = true;
writer.PasteDataTable(dt, "B3", style);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\datatable.xlsx", GetOutputFolder()), stream);
}
public void WorksheetCreateFillTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorkbookStylesPart styles = SpreadsheetReader.GetWorkbookStyles(doc);
SpreadsheetStyle defaultStyle = SpreadsheetReader.GetDefaultStyle(doc);
uint index = SpreadsheetWriter.CreateFill(defaultStyle, styles);
}
public void WorksheetNumberFormatTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
style.FormatCode = "0.00";
writer.PasteNumber("B3", "123", style);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\numberformat.xlsx", GetOutputFolder()), stream);
}
public void WorksheetPasteValuesTest()
{
List <string> values = new List <string>(new string[] { "alpha", "beta", "charlie", "delta" });
//Write to the spreadsheet
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter.PasteValues(doc, worksheetPart, "A", 2, values, CellValues.String, null);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\pastevalues.xlsx", GetOutputFolder()), stream);
}
public void InsertSheetTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
var worksheetPart = SpreadsheetWriter.InsertWorksheet(doc);
Assert.IsNotNull(worksheetPart, "A worksheet part was not returned.");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteText("B2", "Hello World");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\createsheet.xlsx", GetOutputFolder()), stream);
}
public void WorksheetAddAlignmentTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
style.IsWrapped = true;
writer.PasteText("E5", "Wrapped text", style);
style.IsWrapped = false;
style.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
writer.PasteText("E7", "Aligned Test", style);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\wrapped.xlsx", GetOutputFolder()), stream);
}
public void WorksheetCopyTest()
{
MemoryStream stream = SpreadsheetReader.Copy(string.Format("{0}\\Templates\\template.xlsx", Directory.GetCurrentDirectory()));
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteNumber("B3", "10");
writer.PasteNumber("B4", "20");
writer.PasteNumber("B5", "40");
//Add total without a calc chain
writer.FindCell("B6").CellFormula = new CellFormula("SUM(B3:B5)");
//Change the print area from A1:I30
writer.SetPrintArea("Sheet1", "A1", "D10");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\templatetest.xlsx", GetOutputFolder()), stream);
}