четверг, 26 августа 2010 г.

Экспорт данных в Microsoft Excel 2007. Часть 5

Нам осталось рассмотреть последний тип маркеров – Ряды. Этот тип маркеров является наиболее сложным поскольку заранее неизвестно количество возвращаемых рядов.

Таким образом, нужно создавать новые ряды а старые данные, которые содержатся ниже этих рядов сдвигать вниз.

Также появляется проблема с тем, что коллекция ячеек будет постоянно изменяться (будут добавляться новые ячейки), так что за один проход обработать такие маркеры не получится. На самом деле сейчас у нас уже все сделано правильно: в первом проходе находятся все маркеры, а во втором они заменяются на данные, полученные из запросов.

Перейдем к написанию кода.

Сначала мы снова загрузим данные из базы в 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;
         }
     }

}


Таким образом мы реализовали макеры на все случаи жизни...

Комментариев нет:

Отправить комментарий