В статье показывается как можно легко создать отчет в формате Microsoft
Excel используя Xml Spreadsheet
Довольно часто возникает задача создать отчет в формате Microsoft Excel и для
его реализации можно использовать разные решения. Типичными решениями
являются:
- запустить Microsoft Excel и с помошью внешнего управления сформировать в нем
отчет;
- воспользоваться Crystal Reports;
- воспользоваться Microsoft Sql Server Reporting Services;
- создать файл руками в формате Xml Spreadsheet.
В данной статье я покажу как создать отчет четвертым способом. Для начала
посмотрим, что же из себя представляет формат Xml Spreadsheet, для этого пройдем
по ссылке http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xmlss.asp.
Надеюсь голова не закружилась от столь огромного документа.
Решение я реализую для ASP.NET. Для Windows Forms приложения решение выглядит
аналогично, только вместо ASP.NET страницы мы записываем файл прямо на диск.
Для начала создадим ASP.NET страничку, которая будет доставать данные для
отчета из БД, и трансформировать их в файл в формате Xml Spreadsheet. В качестве
источника данных я буду использовать стандартную БД для MS Sql Server -
pubs.
Следующий запрос выбирает авторов и количесто их книг, которые были
проданы.
SELECT
authors.au_lname AS LastName,
authors.au_fname AS FirstName,
SUM(sales.qty) AS Qty
FROM titleauthor
INNER JOIN authors ON titleauthor.au_id = authors.au_id
INNER JOIN sales ON titleauthor.title_id = sales.title_id
GROUP BY authors.au_lname, authors.au_fname
ORDER BY SUM(sales.qty) DESC
Напишем код, который помещает результаты данного запроса в DataSet
string connStr = "server=localhost;database=pubs;trusted_connection=true";
using (SqlConnection connection = new SqlConnection(connStr)) {
string sql = @"
SELECT
authors.au_lname AS LastName,
authors.au_fname AS FirstName,
SUM(sales.qty) AS Qty
FROM titleauthor
INNER JOIN authors ON titleauthor.au_id = authors.au_id
INNER JOIN sales ON titleauthor.title_id = sales.title_id
GROUP BY authors.au_lname, authors.au_fname
ORDER BY SUM(sales.qty) DESC";
SqlCommand command = new SqlCommand(sql,connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet("TestDataSet");
adapter.Fill(ds,"TestTable");
}
Далее мы можем получить содержимое этого DataSet'а в XML путем вызова метода
GetXml().Ниже Вы можете посмотреть пример его содержимого в XML формате.
<TestDataSet>
<TestTable>
<LastName>Ringer</LastName>
<FirstName>Anne</FirstName>
<Qty>148</Qty>
</TestTable>
<TestTable>
<LastName>Ringer</LastName>
<FirstName>Albert</FirstName>
<Qty>133</Qty>
</TestTable>
<TestTable>
<LastName>Dull</LastName>
<FirstName>Ann</FirstName>
<Qty>50</Qty>
</TestTable>
...
</TestDataSet>
Создадим шаблон трансформации, который будет формировать наши Xml данные в
отчет в формате Xml Spreadsheet.
Пример шаблона (template.xslt): <?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<xsl:template match="/">
<xsl:processing-instruction name="mso-application">
<xsl:text>progid="Excel.Sheet"</xsl:text>
</xsl:processing-instruction>
<Workbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom" />
<Borders />
<Font />
<Interior />
<NumberFormat />
<Protection />
</Style>
</Styles>
<Worksheet ss:Name="Sheet 1">
<Table>
<Row>
<Cell>
<Data ss:Type="String">Last Name</Data>
</Cell>
<Cell>
<Data ss:Type="String">First Name</Data>
</Cell>
<Cell>
<Data ss:Type="String">Qty</Data>
</Cell>
</Row>
<xsl:apply-templates select="//TestTable" />
<Row>
<Cell></Cell>
<Cell>
<Data ss:Type="String">Total:</Data>
</Cell>
<Cell>
<xsl:attribute name="ss:Formula">=SUM(R[-<xsl:value-of
select="count(/TestDataSet/TestTable)"></xsl:value-of>]C:R[-1]C)</xsl:attribute>
</Cell>
</Row>
</Table>
</Worksheet>
</Workbook>
</xsl:template>
<xsl:template match="TestTable">
<Row>
<Cell>
<Data ss:Type="String">
<xsl:value-of select="LastName" />
</Data>
</Cell>
<Cell>
<Data ss:Type="String">
<xsl:value-of select="FirstName" />
</Data>
</Cell>
<Cell>
<Data ss:Type="Number">
<xsl:value-of select="Qty" />
</Data>
</Cell>
</Row>
</xsl:template>
</xsl:stylesheet>
Далее напишем код, который будет осуществлять трансформацию XmlDocument xslDoc = new XmlDocument();
xslDoc.Load(Server.MapPath("template.xslt"));
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(ds.GetXml());
XslTransform xslt = new XslTransform();
xslt.Load(xslDoc);
using(StringWriter writer = new StringWriter())
{
xslt.Transform(xmlDoc, null, writer, null);
Response.Write(writer.ToString());
}
Теперь давайте посмотрим на cтраничку целиком: <%@ Page Language="c#" %>
<%@ import Namespace="System.IO" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.Xml" %>
<%@ import Namespace="System.Xml.Xsl" %>
<script runat="server">
void Page_Load(object sender, EventArgs e)
{
Response.Clear();
Response.AddHeader("Content-Disposition", "inline; filename=Report.xls");
Response.ContentType = "application/vnd.ms-excel";
DataSet ds = GetDataSet();
XmlDocument xslDoc = new XmlDocument();
xslDoc.Load(Server.MapPath("template.xslt"));
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(ds.GetXml());
XslTransform xslt = new XslTransform();
xslt.Load(xslDoc);
using(StringWriter writer = new StringWriter())
{
xslt.Transform(xmlDoc, null, writer, null);
Response.Write(writer.ToString());
}
Response.End();
}
DataSet GetDataSet() {
string connStr = "server=localhost;database=pubs;trusted_connection=true";
using (SqlConnection connection = new SqlConnection(connStr)) {
string sql = @"
SELECT
authors.au_lname AS LastName,
authors.au_fname AS FirstName,
SUM(sales.qty) AS Qty
FROM titleauthor
INNER JOIN authors ON titleauthor.au_id = authors.au_id
INNER JOIN sales ON titleauthor.title_id = sales.title_id
GROUP BY authors.au_lname, authors.au_fname
ORDER BY SUM(sales.qty) DESC";
SqlCommand command = new SqlCommand(sql,connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet("TestDataSet");
adapter.Fill(ds,"TestTable");
return ds;
}
}
</script>
В результате запуска этой страницы мы должны увидеть, что то вроде этого:
Eugene
Jenihov, .dotsite.ru
