Создание отчета в формате Microsoft Excel с использованием Xml Spreadsheet

В статье показывается как можно легко создать отчет в формате Microsoft Excel используя Xml Spreadsheet

Eugene Jenihov

report.JPG

Довольно часто возникает задача создать отчет в формате 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 формате.

<LastName>Ringer</LastName>

</TestTable>

<LastName>Ringer</LastName>

<FirstName>Albert</FirstName>

</TestTable>

</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>

</Style>

</Styles>

<Table>

<Row>

<Cell>

</Cell>

<Cell>

<Data ss:Type="String">First Name</Data>

</Cell>

<Cell>

</Cell>

</Row>

<xsl:apply-templates select="//TestTable" />

<Row>

<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>

<xsl:value-of select="LastName" />

</Data>

</Cell>

<Cell>

<xsl:value-of select="FirstName" />

</Data>

</Cell>

<Cell>

<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" %>

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>

Создание отчета в формате Microsoft Excel с использованием Xml Spreadsheet

Статьи

Курсы

Листинги

СУБД

Справочники

Книги

Рассылка