Следующий тип маркеров, который мы реализуем – это будет тип тиблица. То есть запрос должен вернуть заранее заданное количество рядов.
В шаблоне Excel документа нужно оставить нужное количество ячеек и задать для них нужный формат.
В этот раз мы познакомимся с понятие имени ячейки. Конечно, это не совсем имя, но можно его так называть. Для каждой ячейки задано свойство CellReference, которое соответствует расположению ячейки на листе. Например A16 – ячейка находится в колонке A, в ряде номер 16.
Таким образом, имя состоит из номера колонки и номера ряда.
Мне в коде удобнее работать с числами, чем с буквами, так что мне пригодится следующий метод, который преобразует числовое представление номера колонки в строковое:
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;
}
Для того, чтобы заполнить таблицу, я буду использовать SqlDataAdapter, при помощи которого я заполню таблицу в DataSet.
DataSet set = new DataSet();
using (SqlDataAdapter sqlDA = new SqlDataAdapter(cmd))
{
sqlDA.Fill(set, "Listing");
}
После этого нужно будет определить имя ячейки, в которой находится маркер и дальше заполнять лист Excel полученной таблицей начиная с текущей ячейки.
В принципе ничего сложного:
Номер колонки и ряда можно определить таким простым кодом:
int defaultRowIndex;
int curIndex = 1;
while (!int.TryParse(reference.Value.Substring(curIndex), out defaultRowIndex))
{
curIndex++;
}
А для того, чтобы заполнить таблицу ячеек в Excel, нужно просто определять имя следующей ячейки в цикле:
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;
}
Также стоит обратить внимание на то, что запрос, теоретически (в моем случае это так), может совсем ничего не вернуть, так что имеет смысл добавить следующий код, который будет стирать маркер из ячейки, если запрос не вернул результата.
DataSet set = new DataSet();
using (SqlDataAdapter sqlDA = new SqlDataAdapter(cmd))
{
sqlDA.Fill(set, "Listing");
}
// 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 (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 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
}
}
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;
}
Весь исходный код генератора отчетов сейчас выглядит так:
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 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 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;
}
}
}
Комментариев нет:
Отправить комментарий