public void SW002_AllDataTypes()
{
Sw.WorkbookDfn wb = new Sw.WorkbookDfn
{
Worksheets = new Sw.WorksheetDfn[]
{
new Sw.WorksheetDfn
{
Name = "MyFirstSheet",
ColumnHeadings = new Sw.CellDfn[]
{
new Sw.CellDfn
{
Value = "DataType",
Bold = true,
},
new Sw.CellDfn
{
Value = "Value",
Bold = true,
HorizontalCellAlignment = Sw.HorizontalCellAlignment.Right,
},
},
Rows = new Sw.RowDfn[]
{
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "Boolean",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Boolean,
Value = true,
},
}
},
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "Boolean",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Boolean,
Value = false,
},
}
},
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "String",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "A String",
HorizontalCellAlignment = Sw.HorizontalCellAlignment.Right,
},
}
},
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "int",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Number,
Value = (int)100,
},
}
},
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "int?",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Number,
Value = (int?)100,
},
}
},
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "int? (is null)",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Number,
Value = null,
},
}
},
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "uint",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Number,
Value = (uint)101,
},
}
},
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "long",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Number,
Value = Int64.MaxValue,
},
}
},
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "float",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Number,
Value = (float)123.45,
},
}
},
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "double",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Number,
Value = (double)123.45,
},
}
},
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "decimal",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Number,
Value = (decimal)123.45,
},
}
},
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Date,
Value = new DateTime(2012, 1, 8),
FormatCode = "mm-dd-yy",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Date,
Value = new DateTime(2012, 1, 9),
FormatCode = "mm-dd-yy",
Bold = true,
HorizontalCellAlignment = Sw.HorizontalCellAlignment.Center,
},
}
},
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Date,
Value = new DateTimeOffset(new DateTime(2012, 1, 8), TimeSpan.Zero),
FormatCode = "mm-dd-yy",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Date,
Value = new DateTimeOffset(new DateTime(2012, 1, 9), TimeSpan.Zero),
FormatCode = "mm-dd-yy",
Bold = true,
HorizontalCellAlignment = Sw.HorizontalCellAlignment.Center,
},
}
},
}
}
}
};
var outXlsx = new FileInfo(Path.Combine(Sw.TestUtil.TempDir.FullName, "SW002-DataTypes.xlsx"));
Sw.SpreadsheetWriter.Write(outXlsx.FullName, wb);
Validate(outXlsx);
}
public void SW001_Simple()
{
Sw.WorkbookDfn wb = new Sw.WorkbookDfn
{
Worksheets = new Sw.WorksheetDfn[]
{
new Sw.WorksheetDfn
{
Name = "MyFirstSheet",
TableName = "NamesAndRates",
ColumnHeadings = new Sw.CellDfn[]
{
new Sw.CellDfn
{
Value = "Name",
Bold = true,
},
new Sw.CellDfn
{
Value = "Age",
Bold = true,
HorizontalCellAlignment = Sw.HorizontalCellAlignment.Left,
},
new Sw.CellDfn
{
Value = "Rate",
Bold = true,
HorizontalCellAlignment = Sw.HorizontalCellAlignment.Left,
}
},
Rows = new Sw.RowDfn[]
{
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "Eric",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Number,
Value = 50,
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Number,
Value = (decimal)45.00,
FormatCode = "0.00",
},
}
},
new Sw.RowDfn
{
Cells = new Sw.CellDfn[]
{
new Sw.CellDfn {
CellDataType = Sw.CellDataType.String,
Value = "Bob",
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Number,
Value = 42,
},
new Sw.CellDfn {
CellDataType = Sw.CellDataType.Number,
Value = (decimal)78.00,
FormatCode = "0.00",
},
}
},
}
}
}
};
var outXlsx = new FileInfo(Path.Combine(Sw.TestUtil.TempDir.FullName, "SW001-Simple.xlsx"));
Sw.SpreadsheetWriter.Write(outXlsx.FullName, wb);
Validate(outXlsx);
}
public static void Write(string fileName, WorkbookDfn workbook)
{
try
{
if (fileName == null)
{
throw new ArgumentNullException("fileName");
}
if (workbook == null)
{
throw new ArgumentNullException("workbook");
}
FileInfo fi = new FileInfo(fileName);
if (fi.Exists)
{
fi.Delete();
}
// create the blank workbook
char[] base64CharArray = _EmptyXlsx
.Where(c => c != '\r' && c != '\n').ToArray();
byte[] byteArray =
System.Convert.FromBase64CharArray(base64CharArray,
0, base64CharArray.Length);
File.WriteAllBytes(fi.FullName, byteArray);
// open the workbook, and create the TableProperties sheet, populate it
using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(fi.FullName, true))
{
WorkbookPart workbookPart = sDoc.WorkbookPart;
XDocument wXDoc = workbookPart.GetXDocument();
XElement sheetElement = wXDoc
.Root
.Elements(S.sheets)
.Elements(S.sheet)
.Where(s => (string)s.Attribute(SSNoNamespace.name) == "Sheet1")
.FirstOrDefault();
if (sheetElement == null)
{
throw new SpreadsheetWriterInternalException();
}
string id = (string)sheetElement.Attribute(R.id);
sheetElement.Remove();
workbookPart.PutXDocument();
WorksheetPart sPart = (WorksheetPart)workbookPart.GetPartById(id);
workbookPart.DeletePart(sPart);
XDocument appXDoc = sDoc
.ExtendedFilePropertiesPart
.GetXDocument();
XElement vector = appXDoc
.Root
.Elements(EP.TitlesOfParts)
.Elements(VT.vector)
.FirstOrDefault();
if (vector != null)
{
vector.SetAttributeValue(SSNoNamespace.size, 0);
XElement lpstr = vector.Element(VT.lpstr);
lpstr.Remove();
}
XElement vector2 = appXDoc
.Root
.Elements(EP.HeadingPairs)
.Elements(VT.vector)
.FirstOrDefault();
XElement variant = vector2
.Descendants(VT.i4)
.FirstOrDefault();
if (variant != null)
{
variant.Value = "1";
}
sDoc.ExtendedFilePropertiesPart.PutXDocument();
if (workbook.Worksheets != null)
{
foreach (var worksheet in workbook.Worksheets)
{
AddWorksheet(sDoc, worksheet);
}
}
workbookPart.WorkbookStylesPart.PutXDocument();
}
}
catch (Exception e)
{
Console.WriteLine("Unhandled exception: {0} in {1}",
e.ToString(), e.Source);
throw e;
}
}