public void WriteToStreamAsync_WithCellAndHeaderFormats_WritesFormattedExcelDocumentToStream()
{
var data = new[] { new SimpleTestItem {
Value1 = "2,1", Value2 = "2,2"
},
new SimpleTestItem {
Value1 = "3,1", Value2 = "3,2"
} };
var formatter = new XlsxMediaTypeFormatter(
cellStyle: (ExcelStyle s) =>
{
s.Font.Size = 15f;
s.Font.Bold = true;
},
headerStyle: (ExcelStyle s) =>
{
s.Font.Size = 18f;
s.Border.Bottom.Style = ExcelBorderStyle.Thick;
}
);
var sheet = GetWorksheetFromStream(formatter, data);
Assert.IsTrue(sheet.Cells[1, 1].Style.Font.Bold, "Header in A1 should be bold.");
Assert.IsTrue(sheet.Cells[3, 3].Style.Font.Bold, "Value in C3 should be bold.");
Assert.AreEqual(18f, sheet.Cells[1, 1].Style.Font.Size, "Header in A1 should be in size 18 font.");
Assert.AreEqual(18f, sheet.Cells[1, 3].Style.Font.Size, "Header in C1 should be in size 18 font.");
Assert.AreEqual(15f, sheet.Cells[2, 1].Style.Font.Size, "Value in A2 should be in size 15 font.");
Assert.AreEqual(15f, sheet.Cells[3, 3].Style.Font.Size, "Value in C3 should be in size 15 font.");
Assert.AreEqual(ExcelBorderStyle.Thick, sheet.Cells[1, 1].Style.Border.Bottom.Style, "Header in A1 should have a thick border.");
Assert.AreEqual(ExcelBorderStyle.Thick, sheet.Cells[1, 3].Style.Border.Bottom.Style, "Header in C1 should have a thick border.");
Assert.AreEqual(ExcelBorderStyle.None, sheet.Cells[2, 1].Style.Border.Bottom.Style, "Value in A2 should have no border.");
Assert.AreEqual(ExcelBorderStyle.None, sheet.Cells[3, 3].Style.Border.Bottom.Style, "Value in C3 should have no border.");
}
public void CanWriteType_AnyType_ReturnsTrue()
{
var formatter = new XlsxMediaTypeFormatter();
// Simple types
formatter.CanWriteType(typeof(bool)).Should().BeTrue();
formatter.CanWriteType(typeof(byte)).Should().BeTrue();
formatter.CanWriteType(typeof(sbyte)).Should().BeTrue();
formatter.CanWriteType(typeof(char)).Should().BeTrue();
formatter.CanWriteType(typeof(DateTime)).Should().BeTrue();
formatter.CanWriteType(typeof(DateTimeOffset)).Should().BeTrue();
formatter.CanWriteType(typeof(decimal)).Should().BeTrue();
formatter.CanWriteType(typeof(double)).Should().BeTrue();
formatter.CanWriteType(typeof(float)).Should().BeTrue();
formatter.CanWriteType(typeof(Guid)).Should().BeTrue();
formatter.CanWriteType(typeof(int)).Should().BeTrue();
formatter.CanWriteType(typeof(uint)).Should().BeTrue();
formatter.CanWriteType(typeof(long)).Should().BeTrue();
formatter.CanWriteType(typeof(ulong)).Should().BeTrue();
formatter.CanWriteType(typeof(short)).Should().BeTrue();
formatter.CanWriteType(typeof(TimeSpan)).Should().BeTrue();
formatter.CanWriteType(typeof(ushort)).Should().BeTrue();
formatter.CanWriteType(typeof(string)).Should().BeTrue();
formatter.CanWriteType(typeof(TestEnum)).Should().BeTrue();
// Complex types
var anonymous = new { prop = "val" };
formatter.CanWriteType(anonymous.GetType()).Should().BeTrue();
formatter.CanWriteType(typeof(Array)).Should().BeTrue();
formatter.CanWriteType(typeof(IEnumerable <>)).Should().BeTrue();
formatter.CanWriteType(typeof(object)).Should().BeTrue();
formatter.CanWriteType(typeof(SimpleTestItem)).Should().BeTrue();
}
public static void Register(HttpConfiguration config)
{
// Web API configuration and services
var formatter = new XlsxMediaTypeFormatter(
autoFilter: true,
freezeHeader: true,
headerHeight: 25f,
cellStyle: (ExcelStyle s) =>
{
s.Font.SetFromFont(new Font("Segoe UI", 13f, FontStyle.Regular));
},
headerStyle: (ExcelStyle s) =>
{
s.Fill.PatternType = ExcelFillStyle.Solid;
s.Fill.BackgroundColor.SetColor(Color.FromArgb(0, 114, 51));
s.Font.Color.SetColor(Color.White);
s.Font.Size = 15f;
}
);
config.Formatters.Add(formatter);
// Web API routes
config.MapHttpAttributeRoutes();
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{id}",
defaults: new { id = RouteParameter.Optional }
);
}
public void SupportedMediaTypes_SupportsExcelMediaTypes()
{
var formatter = new XlsxMediaTypeFormatter();
formatter.SupportedMediaTypes.Any(s => s.MediaType == XlsMimeType).Should()
.BeTrue("XLS media type not supported.");
formatter.SupportedMediaTypes.Any(s => s.MediaType == XlsxMimeType).Should()
.BeTrue("XLS media type not supported.");
}
/// <summary>
/// Generate the serialised worksheet and ensure that it is formatted as expected.
/// </summary>
/// <typeparam name="TItem">Type of items to be serialised.</typeparam>
/// <typeparam name="TExpected">Type of items in expected results array, usually <c>object</c>.</typeparam>
/// <param name="data">Data to be serialised.</param>
/// <param name="expected">Expected format of the generated worksheet.</param>
/// <param name="formatter">Optional custom formatter instance to use for serialisation.</param>
/// <returns>The generated <c>ExcelWorksheet</c> containing the serialised data.</returns>
public ExcelWorksheet GenerateAndCompareWorksheet <TItem, TExpected>(TItem data,
TExpected[][] expected,
XlsxMediaTypeFormatter formatter = null)
{
var sheet = GetWorksheetFromStream(formatter ?? new XlsxMediaTypeFormatter(), data);
CompareWorksheet(sheet, expected);
return(sheet);
}
public void SupportedMediaTypes_SupportsExcelMediaTypes()
{
var formatter = new XlsxMediaTypeFormatter();
Assert.IsTrue(formatter.SupportedMediaTypes.Any(s => s.MediaType == XlsMimeType),
"XLS media type not supported.");
Assert.IsTrue(formatter.SupportedMediaTypes.Any(s => s.MediaType == XlsxMimeType),
"XLSX media type not supported.");
}
public void WriteToStreamAsync_WithHeaderRowHeight_WritesFormattedExcelDocumentToStream()
{
var data = new[] { new SimpleTestItem {
Value1 = "2,1", Value2 = "2,2"
},
new SimpleTestItem {
Value1 = "3,1", Value2 = "3,2"
} };
var formatter = new XlsxMediaTypeFormatter(headerHeight: 30f);
var sheet = GetWorksheetFromStream(formatter, data);
Assert.AreEqual(30f, sheet.Row(1).Height, "Row 1 should have height 30.");
}
public void XlsxMediaTypeFormatter_WithDefaultHeaderHeight_DefaultsToSameHeightForAllCells()
{
var data = new[] { new SimpleTestItem {
Value1 = "A1", Value2 = "B1"
},
new SimpleTestItem {
Value1 = "A1", Value2 = "B2"
} };
var formatter = new XlsxMediaTypeFormatter();
var sheet = GetWorksheetFromStream(formatter, data);
Assert.AreNotEqual(sheet.Row(1).Height, 0d, "HeaderHeight should not be zero");
Assert.AreEqual(sheet.Row(1).Height, sheet.Row(2).Height, "HeaderHeight should be the same as other rows");
}
public static Configuration UseWebApi(this Configuration configuration, IAppBuilder app, HttpConfiguration config)
{
//filter
//全局model验证
config.Filters.Add(new ValidateModelAttribute());
//formatting
// Set up the XlsxMediaTypeFormatter
var xlsxFormatter = new XlsxMediaTypeFormatter(autoFilter: true, freezeHeader: true, headerHeight: 25f,
cellStyle: (ExcelStyle s) => s.Font.SetFromFont(new Font("Segoe UI", 13f, FontStyle.Regular)), headerStyle: (ExcelStyle s) =>
{
s.Fill.PatternType = ExcelFillStyle.Solid;
s.Fill.BackgroundColor.SetColor(Color.FromArgb(0, 114, 51));
s.Font.Color.SetColor(Color.White);
s.Font.Size = 15f;
});
// Add XlsxMediaTypeFormatter to the collection
config.Formatters.Add(xlsxFormatter);
config.AddJsonpFormatter();
config.Formatters.JsonFormatter.SerializerSettings = new JsonSerializerSettings
{
Converters = new List <JsonConverter> {
new IsoDateTimeConverter(), new StringEnumConverter {
CamelCaseText = true
}
}
};
//Trace
//config.Services.Replace(typeof(ITraceWriter), new Log4NetTraceWriter());
//ExceptionLogger
config.Services.Add(typeof(IExceptionLogger), new Log4NetExceptionLogger());
config.MapHttpAttributeRoutes();
config.Routes.MapHttpRoute("DefaultApi", "api/{controller}/{id}", new { id = RouteParameter.Optional });
app.UseWebApi(config);
return(configuration);
}
private static ExcelWorksheet GetWorksheetFromStream <TItem>(XlsxMediaTypeFormatter formatter, TItem data)
{
var content = new FakeContent();
content.Headers.ContentType = new MediaTypeHeaderValue("application/atom+xml");
var httpContext = new DefaultHttpContext();
httpContext.Response.Body = new MemoryStream();
httpContext.RequestServices = new FakeServiceProvider();
var context = new OutputFormatterWriteContext(httpContext, new TestHttpResponseStreamWriterFactory().CreateWriter, typeof(TItem), data);
formatter.WriteResponseBodyAsync(context, Encoding.UTF8).GetAwaiter().GetResult();
context.HttpContext.Response.Body.Seek(0, SeekOrigin.Begin);
var package = new ExcelPackage(context.HttpContext.Response.Body);
return(package.Workbook.Worksheets[0]);
}
/// <summary>
/// Generate a worksheet containing the specified data using the provided <c>XlsxMediaTypeFormatter</c>
/// instance.
/// </summary>
/// <typeparam name="TItem">Type of items to be serialised.</typeparam>
/// <param name="formatter">Formatter instance to use for serialisation.</param>
/// <param name="data">Data to be serialised.</param>
/// <returns></returns>
public ExcelWorksheet GetWorksheetFromStream <TItem>(XlsxMediaTypeFormatter formatter, TItem data)
{
var ms = new MemoryStream();
var content = new FakeContent();
content.Headers.ContentType = new MediaTypeHeaderValue("application/atom+xml");
var task = formatter.WriteToStreamAsync(typeof(IEnumerable <TItem>),
data,
ms,
content,
new FakeTransport());
task.Wait();
ms.Seek(0, SeekOrigin.Begin);
var package = new ExcelPackage(ms);
return(package.Workbook.Worksheets[1]);
}
public void CanWriteType_AnyType_ReturnsTrue()
{
var types = new[] { // Simple types
typeof(bool), typeof(byte), typeof(sbyte), typeof(char),
typeof(DateTime), typeof(DateTimeOffset), typeof(decimal),
typeof(float), typeof(Guid), typeof(int), typeof(uint),
typeof(long), typeof(ulong), typeof(short), typeof(ushort),
typeof(TimeSpan), typeof(string), typeof(TestEnum),
// Complex types
new { anonymous = true }.GetType(), typeof(Array),
typeof(IEnumerable <>), typeof(object), typeof(SimpleTestItem)
};
var formatter = new XlsxMediaTypeFormatter();
foreach (var type in types)
{
Assert.IsTrue(formatter.CanWriteType(type));
}
}
public void XlsxMediaTypeFormatter_WithPerRequestColumnResolverCustomOrder_ReturnsSpecifiedProperties()
{
var data = new[] { new ComplexTestItem {
Value1 = "Item 1",
Value2 = DateTime.Today,
Value3 = true,
Value4 = 100.1,
Value5 = TestEnum.First,
Value6 = "Ignored"
},
new ComplexTestItem {
Value1 = "Item 2",
Value2 = DateTime.Today.AddDays(1),
Value3 = false,
Value4 = 200.2,
Value5 = TestEnum.Second,
Value6 = "Also ignored"
} }.ToList();
var expected = new[] { new object[] { "Value1", "Header 4", "Header 5" },
new object[] { data[0].Value1, data[0].Value4, data[0].Value5.ToString() },
new object[] { data[1].Value1, data[1].Value4, data[1].Value5.ToString() } };
var serialiseValues = new[] { "Value1", "Value4", "Value5" };
var formatter = new XlsxMediaTypeFormatter();
formatter.DefaultSerializer.Resolver = new PerRequestColumnResolver(useCustomOrder: true);
HttpContextFactory.SetCurrentContext(new FakeHttpContext());
HttpContextFactory.Current.Items[PerRequestColumnResolver.DEFAULT_KEY] = serialiseValues;
var sheet = GenerateAndCompareWorksheet(data, expected, formatter);
}
public static void Register(HttpConfiguration config)
{
// Web API configuration and services
// Remove all other formatters (not required, used here
// to force XlsxMediaTypeFormatter as default)
config.Formatters.Clear();
// Set up the XlsxMediaTypeFormatter
var formatter = new XlsxMediaTypeFormatter(
autoFilter: true,
freezeHeader: true,
headerHeight: 25f,
cellStyle: (ExcelStyle s) => {
s.Font.SetFromFont(new Font("Segoe UI", 13f, FontStyle.Regular));
},
headerStyle: (ExcelStyle s) => {
s.Fill.PatternType = ExcelFillStyle.Solid;
s.Fill.BackgroundColor.SetColor(Color.FromArgb(0, 114, 51));
s.Font.Color.SetColor(Color.White);
s.Font.Size = 15f;
}
);
// Add XlsxMediaTypeFormatter to the collection
config.Formatters.Add(formatter);
// Web API routes
config.MapHttpAttributeRoutes();
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{id}",
defaults: new { id = RouteParameter.Optional }
);
}
public void CanReadType_TypeObject_ReturnsFalse()
{
var formatter = new XlsxMediaTypeFormatter();
Assert.IsFalse(formatter.CanReadType(typeof(object)));
}
public void CanReadType_TypeObject_ReturnsFalse()
{
var formatter = new XlsxMediaTypeFormatter();
formatter.CanReadType(typeof(object)).Should().BeFalse();
}
