Теперь более подробно рассмотрим нашу задачу экспорта данных в отчет Microsoft Excel 2007.
Данные для отчета будут получаться путем выполнения специальных Sql запросов, которые будут храниться в таблице базы данных.
Причем, у нас будет три различных типа запроса:
Запрос, который возвращает одну ячейку данных. (назовем такой тип Cell)
Запрос, который возвращает набор данных, размер которого заранее известен. (назовем такой тип Table)
Запрос, который возвращает набор рядов, причем их количество заранее не известно. (назовем такой тип Row)
Соотвественно получим следующие скрипты для создания и заполнения таблиц:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MarkerTypes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Type] [nvarchar](50) NULL,
CONSTRAINT [PK_MarkerTypes] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [MarkerTypes]
([Type])
VALUES
('Cell')
INSERT INTO [MarkerTypes]
([Type])
VALUES
('Row')
INSERT INTO [MarkerTypes]
([Type])
VALUES
('Table')
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Markers](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NULL,
[TypeID] [int] NULL,
[Query] [nvarchar](max) NULL,
CONSTRAINT [PK_Markers] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Markers] WITH CHECK ADD CONSTRAINT [FK_Markers_MarkerTypes] FOREIGN KEY([TypeID])
REFERENCES [dbo].[MarkerTypes] ([ID])
GO
ALTER TABLE [dbo].[Markers] CHECK CONSTRAINT [FK_Markers_MarkerTypes]
GO
Для всего этого стоит также сгенерировать Entity Data Model, чтобы было удобнее с ними работать.
Шаблон отчета будет содержать маркеры специального вида ({Название маркера}), который будут заменены на данные, полученные из запросов.
Также у нас будет дополнительный тип маркеров – маркеры, которые должны вставлять данные в заголовок Excel страницы (Insert -> Header and Footer). Запросы для таких маркеров будут возращать только одно значение. Посколько замена такого маркера, пожалуй, является самой простой, с нее и начнем.
Вернемся к нашему класса генератору отчетов, загрузим сущности для макреров и начнем писать код, который позже будет обработывать весь отчет.
Сначала прочитаем все маркеры в список картежей (можно было бы сделать и попроще), которые будут содержать имя маркера, сопутствующий запрос и тип маркера.
List<Tuple<string, string, string>> markers = new List<Tuple<string, string, string>>();
using (var ctx = new MyApplicationEntities())
{
foreach (var marker in ctx.Markers.Include("MarkerType"))
{
markers.Add(new Tuple<string, string, string>(marker.Name, marker.Query, marker.MarkerType.Type));
}
}
Далее откроем Excel файл для обработки, основно класс для работы с Excel файлами – это SpreadSheetDocument. Файл открывает на чтение и на запись одновременно.
Документ представляет из себя книгу, можно получить ее через свойство SpreadsheetDocument.WorkbookPart. Такая книга может содержать большое количество листов, для создания полного отчета нужно пройтись по всем листам:
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
{
var workbookPart = document.WorkbookPart;
var sheets = workbookPart.WorksheetParts;
foreach (var sheet in sheets)
{
}
}
Если посмотреть при помощи IntelliSense, то можно увидеть, что каждый из объектов, содержащий Excel лист, имеет огромное количество свойств, отвечающий за различные элемента листа – заголоки, таблицы, ячейки и т.д.
Причем существуем много разных способов получения этих наборов, другое дело, что не все способы работают одинаково хорошо. Далее я буду приводить только те способы, которые работают в данном случае (это было получено путем бесчисленного количества проб и ошибок).
Поскольку заголовок у листа всего один (думаю, это действительно так), можно воспользоваться методов GetFirstChild с типов HeaderAndFooter. (Есть еще тип Header, но он скорее всего будет содержать null, даже если заголовок присутствует. Не знаю, с чем связана такая путаница, но в работе с Open XML SDK такие странности повсюду.)
Элемент HeaderAndFooter содержит свойство OddHeader, которое содержит свойство Text (InnerText и т.д.), которое хранит текст заголовка.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
{
var workbookPart = document.WorkbookPart;
var sheets = workbookPart.WorksheetParts;
foreach (var sheet in sheets)
{
var headerPart = sheet.Worksheet.GetFirstChild<HeaderFooter>();
if (headerPart != null)
{
var header = headerPart.OddHeader;
if (header != null)
{
//header.Text = ?;
}
}
}
}
Таким образом, если заголовок содержит строку вроде {набор букв и цифр} (шаблон наших маркеров) нужно заменять его на результат выполнения запроса.
Сначала получим список всех вхождений маркеров в строку заголовка при помощи регулярных выражений, далее выполним запрос с параметрами:
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyApplication"].ToString()))
{
con.Open();
var cmd = con.CreateCommand();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
{
var workbookPart = document.WorkbookPart;
var sheets = workbookPart.WorksheetParts;
foreach (var sheet in sheets)
{
var headerPart = sheet.Worksheet.GetFirstChild<HeaderFooter>();
if (headerPart != null)
{
var header = headerPart.OddHeader;
if (header != null)
{
var matches = Regex.Matches(header.Text, "{.*}", RegexOptions.IgnoreCase);
foreach (var item in matches)
{
var value = item.ToString().Replace("{", "").Replace("}", "");
var marker = markers.Find(p => p.Item1 == value);
cmd.CommandText = marker.Item2;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@ProjectId", projectId);
cmd.Parameters.AddWithValue("@DateFrom", from);
cmd.Parameters.AddWithValue("@DateTo", to);
object queryValue = cmd.ExecuteScalar();
header.Text = header.Text.Replace(item.ToString(), queryValue.ToString());
}
}
}
}
}
}
Весь код генератора отчетов пока у нас получился таким:
GetReport.aspx:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GetReport.aspx.cs" Inherits="HuronLoans.Web.GetReport" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>
</html>
GetReport.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.IO;
using MyApplication.BLL;
using System.Text;
namespace MyApplication.Web
{
public partial class GetReport : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int id = 0;
DateTime from = DateTime.Now;
DateTime to = DateTime.Now;
try
{
id = Convert.ToInt32(Request.QueryString["id"]);
from = Convert.ToDateTime(Request.QueryString["from"]);
to = Convert.ToDateTime(Request.QueryString["to"]);
}
catch (Exception ex)
{
return;
}
string newFileName = Server.MapPath(@"~\App_Data\w1"+Guid.NewGuid().ToString().Replace("-", "") +".xlsx");
var pathToReport = Server.MapPath(@"~\App_Data\MyReportTemplate.xlsx");
File.Copy(pathToReport, newFileName);
ReportCreator.Create(newFileName, id, from, to);
Response.Clear();
Response.AppendHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
Response.AppendHeader("Content-Disposition", "attachment; filename=Report.xlsx;");
Response.Charset = Encoding.Unicode.WebName;
Response.TransmitFile(newFileName);
Response.End();
try
{
File.Delete(newFileName);
}
catch (Exception ex)
{
}
}
}
}
ReportCreator.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Text.RegularExpressions;
namespace MyApplication.BLL
{
public class ReportCreator
{
public static Stream Create(string fileName, int projectId, DateTime from, DateTime to)
{
List<Tuple<string, string, string>> markers = new List<Tuple<string, string, string>>();
using (var ctx = new MyApplicationEntities())
{
foreach (var marker in ctx.Markers.Include("MarkerType"))
{
markers.Add(new Tuple<string, string, string>(marker.Name, marker.Query, marker.MarkerType.Type));
}
}
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyApplication"].ToString()))
{
con.Open();
var cmd = con.CreateCommand();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
{
var workbookPart = document.WorkbookPart;
var sheets = workbookPart.WorksheetParts;
foreach (var sheet in sheets)
{
var headerPart = sheet.Worksheet.GetFirstChild<HeaderFooter>();
if (headerPart != null)
{
var header = headerPart.OddHeader;
if (header != null)
{
var matches = Regex.Matches(header.Text, "{.*}", RegexOptions.IgnoreCase);
foreach (var item in matches)
{
var value = item.ToString().Replace("{", "").Replace("}", "");
var marker = markers.Find(p => p.Item1 == value);
cmd.CommandText = marker.Item2;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@ProjectId", projectId);
cmd.Parameters.AddWithValue("@DateFrom", from);
cmd.Parameters.AddWithValue("@DateTo", to);
object queryValue = cmd.ExecuteScalar();
header.Text = header.Text.Replace(item.ToString(), queryValue.ToString());
}
}
}
}
}
}
return null;
}
}
}
На этом простом примере можно было понять, что библиотека Open XML SDK является недостаточно типизированной и значительно больше оперирует понятиями потомков, предков и т.д., чем, например, понятиями ячеек, таблиц, графиков и т.д.
Библиотека Open XML SDK развивается (правда не слишкоа быстро) и, возможно, когда-нибудь станет более пригодной для использования. Пока же разработчикам остается тратить свое время на поиски решения непонятных проблем в интернете.
Дальше мы рассмотрим по очереди меркеры других типов.
Я не читал всю статью, но мне приходилось использовать OpenXML. Я не использовал маркеры, а взял Productivity Tool и модифицировал получившийся код, чтобы в нужных ячейках таблицы были соответствующие значения... Также я просто брал куски кода оттуда для создания строк таблицы. Спасибо, очень интересно читать.
ОтветитьУдалить