Нам осталось рассмотреть последний тип маркеров – Ряды. Этот тип маркеров является наиболее сложным поскольку заранее неизвестно количество возвращаемых рядов.
Таким образом, нужно создавать новые ряды а старые данные, которые содержатся ниже этих рядов сдвигать вниз.
Также появляется проблема с тем, что коллекция ячеек будет постоянно изменяться (будут добавляться новые ячейки), так что за один проход обработать такие маркеры не получится. На самом деле сейчас у нас уже все сделано правильно: в первом проходе находятся все маркеры, а во втором они заменяются на данные, полученные из запросов.
Перейдем к написанию кода.
Сначала мы снова загрузим данные из базы в DataSet.
Также нам понадобятся несколько вспомогательных методов, которые будут создавать ряды и ячейки из данных.
private static Cell CreateTextCell(int columnIndex, int rowIndex, object cellValue)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = GetColumnName(columnIndex) + rowIndex;
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = cellValue.ToString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return cell;
}
private static Row CreateContentRow(DataRow dataRow, int rowIndex)
{
Row row = new Row() { RowIndex = (UInt32)rowIndex };
for (int i = 0; i < dataRow.Table.Columns.Count; i++)
{
Cell dataCell = CreateTextCell(i + 1, rowIndex, dataRow[i]);
row.AppendChild(dataCell);
}
return row;
}
private static string GetColumnName(int columnIndex)
{
int dividend = columnIndex;
string columnName = String.Empty;
int modifier;
while (dividend > 0)
{
modifier = (dividend - 1) % 26;
columnName =
Convert.ToChar(65 + modifier).ToString() + columnName;
dividend = (int)((dividend - modifier) / 26);
}
return columnName;
}
В принципе, ничего сложного в этом коде нет: нужно просто грамотно проставить CellReference для каждой ячейки ряда, а для ряда задать его номер.
Более сложный код нужен для вставки нового ряда. Если сейчас просто добавлять полученный ряд на лист Excel, то нижные ряды не сдвинутся и данные в них затрутся.
Следующий код делает сдвиг. Разобраться в нем сложно, но он, кажется, работает.
Row rowCurrent = cell.Parent.PreviousSibling<Row>();
foreach (DataRow row in set.Tables[0].Rows)
{
rowCurrent = rowCurrent.InsertAfterSelf(CreateContentRow(row, rowIndex));
rowIndex++;
}
if (rowCurrent.NextSibling<Row>().NextSibling<Row>() == null)
{
rowCurrent.NextSibling<Row>().Remove();
}
else
{
rowCurrent = rowCurrent.NextSibling<Row>().NextSibling<Row>();
rowCurrent.PreviousSibling<Row>().Remove();
while (rowCurrent != null)
{
rowCurrent.RowIndex = new DocumentFormat.OpenXml.UInt32Value(rowCurrent.RowIndex + (uint)set.Tables[0].Rows.Count - 1);
Cell cellCurrent = rowCurrent.GetFirstChild<Cell>();
while (cellCurrent != null)
{
cellCurrent.CellReference = cellCurrent.CellReference.Value.Replace((rowIndex - set.Tables[0].Rows.Count + 1).ToString(), rowIndex.ToString());
cellCurrent = cellCurrent.NextSibling<Cell>();
}
rowIndex++;
rowCurrent = rowCurrent.NextSibling<Row>();
}
}
Кусок кода для этого типа маркеров выглядит так:
#region Row
if (marker.Item3 == "Row")
{
var reference = cell.CellReference;
DataSet set = new DataSet();
using (SqlDataAdapter sqlDA = new SqlDataAdapter(cmd))
{
sqlDA.Fill(set, "Listing");
}
int rowIndex;
int curIndex = 1;
while (!int.TryParse(reference.Value.Substring(curIndex), out rowIndex))
{
curIndex++;
}
// clear marker if query returned empty result
if (set.Tables[0].Rows.Count < 1)
{
cell.DataType = CellValues.String;
cell.CellValue = new CellValue();
cell.CellValue.Text = "";
}
Row rowCurrent = cell.Parent.PreviousSibling<Row>();
foreach (DataRow row in set.Tables[0].Rows)
{
rowCurrent = rowCurrent.InsertAfterSelf(CreateContentRow(row, rowIndex));
rowIndex++;
}
if (rowCurrent.NextSibling<Row>().NextSibling<Row>() == null)
{
rowCurrent.NextSibling<Row>().Remove();
}
else
{
rowCurrent = rowCurrent.NextSibling<Row>().NextSibling<Row>();
rowCurrent.PreviousSibling<Row>().Remove();
while (rowCurrent != null)
{
rowCurrent.RowIndex = new DocumentFormat.OpenXml.UInt32Value(rowCurrent.RowIndex + (uint)set.Tables[0].Rows.Count - 1);
Cell cellCurrent = rowCurrent.GetFirstChild<Cell>();
while (cellCurrent != null)
{
cellCurrent.CellReference = cellCurrent.CellReference.Value.Replace((rowIndex - set.Tables[0].Rows.Count + 1).ToString(), rowIndex.ToString());
cellCurrent = cellCurrent.NextSibling<Cell>();
}
rowIndex++;
rowCurrent = rowCurrent.NextSibling<Row>();
}
}
}
#endregion
Весь исходный код генератора отчетов:
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 sheetData = sheet.Worksheet.GetFirstChild<SheetData>();
var table = sheet.TableDefinitionParts;
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
#region Row
if (marker.Item3 == "Row")
{
var reference = cell.CellReference;
DataSet set = new DataSet();
using (SqlDataAdapter sqlDA = new SqlDataAdapter(cmd))
{
sqlDA.Fill(set, "Listing");
}
int rowIndex;
int curIndex = 1;
while (!int.TryParse(reference.Value.Substring(curIndex), out rowIndex))
{
curIndex++;
}
// clear marker if query returned empty result
if (set.Tables[0].Rows.Count < 1)
{
cell.DataType = CellValues.String;
cell.CellValue = new CellValue();
cell.CellValue.Text = "";
}
Row rowCurrent = cell.Parent.PreviousSibling<Row>();
foreach (DataRow row in set.Tables[0].Rows)
{
rowCurrent = rowCurrent.InsertAfterSelf(CreateContentRow(row, rowIndex));
rowIndex++;
}
if (rowCurrent.NextSibling<Row>().NextSibling<Row>() == null)
{
rowCurrent.NextSibling<Row>().Remove();
}
else
{
rowCurrent = rowCurrent.NextSibling<Row>().NextSibling<Row>();
rowCurrent.PreviousSibling<Row>().Remove();
while (rowCurrent != null)
{
rowCurrent.RowIndex = new DocumentFormat.OpenXml.UInt32Value(rowCurrent.RowIndex + (uint)set.Tables[0].Rows.Count - 1);
Cell cellCurrent = rowCurrent.GetFirstChild<Cell>();
while (cellCurrent != null)
{
cellCurrent.CellReference = cellCurrent.CellReference.Value.Replace((rowIndex - set.Tables[0].Rows.Count + 1).ToString(), rowIndex.ToString());
cellCurrent = cellCurrent.NextSibling<Cell>();
}
rowIndex++;
rowCurrent = rowCurrent.NextSibling<Row>();
}
}
}
#endregion
#region Table
if (marker.Item3 == "Table")
{
var reference = cell.CellReference;
DataSet set = new DataSet();
using (SqlDataAdapter sqlDA = new SqlDataAdapter(cmd))
{
sqlDA.Fill(set, "Listing");
}
int defaultRowIndex;
int curIndex = 1;
while (!int.TryParse(reference.Value.Substring(curIndex), out defaultRowIndex))
{
curIndex++;
}
string defaultColumnName = reference.Value.Substring(0, curIndex);
int defaultColumnIndex = defaultColumnName.ToCharArray()[0] - 65 + 1;
int columnIndex = defaultColumnIndex;
int rowIndex = defaultRowIndex;
// clear marker if query returned empty result
if (set.Tables[0].Rows.Count < 1)
{
cell.DataType = CellValues.String;
cell.CellValue = new CellValue();
cell.CellValue.Text = "";
}
foreach (DataRow row in set.Tables[0].Rows)
{
foreach (var item in row.ItemArray)
{
var curCell = allCells.FirstOrDefault(p => p.CellReference == GetColumnName(columnIndex) + rowIndex);
curCell.DataType = CellValues.Number;
curCell.CellValue = new CellValue();
curCell.CellValue.Text = item.ToString();
columnIndex++;
}
rowIndex++;
columnIndex = defaultColumnIndex;
}
}
#endregion
}
}
// recalc formulas
foreach (var cell in sheet.Worksheet.Descendants<Cell>())
{
if (cell.CellFormula != null && cell.CellValue != null)
{
cell.CellValue.Remove();
}
}
}
}
}
return null;
}
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;
}
private static Cell CreateTextCell(int columnIndex, int rowIndex, object cellValue)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = GetColumnName(columnIndex) + rowIndex;
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = cellValue.ToString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return cell;
}
private static Row CreateContentRow(DataRow dataRow, int rowIndex)
{
Row row = new Row() { RowIndex = (UInt32)rowIndex };
for (int i = 0; i < dataRow.Table.Columns.Count; i++)
{
Cell dataCell = CreateTextCell(i + 1, rowIndex, dataRow[i]);
row.AppendChild(dataCell);
}
return row;
}
private static string GetColumnName(int columnIndex)
{
int dividend = columnIndex;
string columnName = String.Empty;
int modifier;
while (dividend > 0)
{
modifier = (dividend - 1) % 26;
columnName =
Convert.ToChar(65 + modifier).ToString() + columnName;
dividend = (int)((dividend - modifier) / 26);
}
return columnName;
}
}
}
Таким образом мы реализовали макеры на все случаи жизни...
