Сейчас мы реализуем следующий тип маркеров: маркер – ячейку.
Для начала нам нужно получить все занятые ячейки на каждом листе. Для этого воспользуемся методом Descendants листа, который возвращает все дочерние элементы определенного типа.
var allCells = sheet.Worksheet.Descendants<Cell>();
Для начала (в первом проходе) мы пройдемся по всем ячейками и найдем такие, текст которых содержит маркер.
Но тут возникает еще одна проблема, непонятно откуда брать содержимое ячейки. Оказывается, что это содержимое может храниться в совершенно различных местах.
CellValue - оно просто содержится в ячейке
InlineString – также хранится где-то в ячейке.
SharedString – хранится где-то в документе, ячейка хранит только ссылку на элемент из этой таблицы, для того, чтобы получить значение, нужно сначала получить всю эту таблицу. Еще более сложно записать что-то в таблицу, хотя для этого может подойти вот такой метод:
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
// If the part does not contain a SharedStringTable, create one.
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
int i = 0;
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
{
if (item.InnerText == text)
{
return i;
}
i++;
}
// The text does not exist in the part. Create the SharedStringItem and return its index.
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}
Таким образом чтобы получить значение, содержащееся в какой-либо ячейке нужно выполнить примерно такой код:
if (cell.DataType != null)
{
if (cell.DataType == CellValues.SharedString)
{
value = ssTable.ChildElements[Convert.ToInt32(cell.CellValue.Text)].InnerText;
}
if (cell.DataType == CellValues.InlineString)
{
value = cell.InlineString.InnerText;
}
if (cell.DataType == CellValues.String)
{
value = cell.CellValue.Text;
}
}
Получив значение ячейки можно проверить хранится ли в ней меркер. Можно сделать это следующий образом:
var workbookPart = document.WorkbookPart;
SharedStringTablePart sstPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
SharedStringTable ssTable = sstPart.SharedStringTable;
var sheets = workbookPart.WorksheetParts;
foreach (var sheet in sheets)
{
var allCells = sheet.Worksheet.Descendants<Cell>();
Dictionary<Cell, Tuple<string, string, string>> cellDict = new Dictionary<Cell, Tuple<string, string, string>>();
foreach (var cell in allCells)
{
string value = null;
if (cell.DataType != null)
{
if (cell.DataType == CellValues.SharedString)
{
value = ssTable.ChildElements[Convert.ToInt32(cell.CellValue.Text)].InnerText;
}
if (cell.DataType == CellValues.InlineString)
{
value = cell.InlineString.InnerText;
}
if (cell.DataType == CellValues.String)
{
value = cell.CellValue.Text;
}
}
if (value != null && value.StartsWith("{"))
{
value = value.Replace("{", "").Replace("}", "");
var marker = markers.Find(p => p.Item1 == value);
if (marker != null)
{
cellDict.Add(cell, marker);
}
}
}
}
Таким образом мы составили словарь, в котором хранятся все ссылки на ячейки, которые нужно заменить. Можно было бы заменять в этом же проходе, но тогда код для других типов маркеров не будем, почему это происходит мы увидим дальше.
Теперь нам нужно пройти по словарю и заменить значения в ячейках на то, что нужно.
foreach (var cellValue in cellDict)
{
var cell = cellValue.Key;
var marker = cellValue.Value;
if (marker != null)
{
// init sql command
cmd.CommandText = marker.Item2;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@ProjectId", projectId);
cmd.Parameters.AddWithValue("@DateFrom", from);
cmd.Parameters.AddWithValue("@DateTo", to);
#region Cell
if (marker.Item3 == "Cell")
{
object queryValue = cmd.ExecuteScalar();
float val;
if (string.IsNullOrWhiteSpace(queryValue.ToString()) ||
float.TryParse(queryValue.ToString(), out val))
{
cell.DataType = CellValues.Number;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue();
cell.CellValue.Text = queryValue.ToString();
}
#endregion
}
}
Ничего сложного. Но теперь, если мы попробуем воспользоваться этим генератором отчетов, то мы увидим, что в результирующием файле все ячейки, в которых хранились формулы содержат неправильные значения. Причем все попытки разобраться с этой проблемой через Excel приводят в окончательное замешательство (можете сами попробовать).
На самом деле, чтобы избавиться от этой досадной ошибки нужно всего лишь сбросить значения все ячеек, содержащий формулы из нашего генератора.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
{
var workbookPart = document.WorkbookPart;
SharedStringTablePart sstPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
SharedStringTable ssTable = sstPart.SharedStringTable;
var sheets = workbookPart.WorksheetParts;
foreach (var sheet in sheets)
{
// recalc formulas
foreach (var cell in sheet.Worksheet.Descendants<Cell>())
{
if (cell.CellFormula != null && cell.CellValue != null)
{
cell.CellValue.Remove();
}
}
}
}
Весь исходный код:
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;
SharedStringTablePart sstPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
SharedStringTable ssTable = sstPart.SharedStringTable;
var sheets = workbookPart.WorksheetParts;
foreach (var sheet in sheets)
{
var allCells = sheet.Worksheet.Descendants<Cell>();
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());
}
}
}
#region find markers
Dictionary<Cell, Tuple<string, string, string>> cellDict = new Dictionary<Cell, Tuple<string, string, string>>();
foreach (var cell in allCells)
{
string value = null;
if (cell.DataType != null)
{
if (cell.DataType == CellValues.SharedString)
{
value = ssTable.ChildElements[Convert.ToInt32(cell.CellValue.Text)].InnerText;
}
if (cell.DataType == CellValues.InlineString)
{
value = cell.InlineString.InnerText;
}
if (cell.DataType == CellValues.String)
{
value = cell.CellValue.Text;
}
}
if (value != null && value.StartsWith("{"))
{
value = value.Replace("{", "").Replace("}", "");
var marker = markers.Find(p => p.Item1 == value);
if (marker != null)
{
cellDict.Add(cell, marker);
}
}
}
#endregion
foreach (var cellValue in cellDict)
{
var cell = cellValue.Key;
var marker = cellValue.Value;
if (marker != null)
{
// init sql command
cmd.CommandText = marker.Item2;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@ProjectId", projectId);
cmd.Parameters.AddWithValue("@DateFrom", from);
cmd.Parameters.AddWithValue("@DateTo", to);
#region Cell
if (marker.Item3 == "Cell")
{
object queryValue = cmd.ExecuteScalar();
float val;
if (string.IsNullOrWhiteSpace(queryValue.ToString()) ||
float.TryParse(queryValue.ToString(), out val))
{
cell.DataType = CellValues.Number;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue();
cell.CellValue.Text = queryValue.ToString();
}
#endregion }
}
// recalc formulas
foreach (var cell in sheet.Worksheet.Descendants<Cell>())
{
if (cell.CellFormula != null && cell.CellValue != null)
{
cell.CellValue.Remove();
}
}
}
}
}
return null;
}
}
}
Комментариев нет:
Отправить комментарий