среда, 25 августа 2010 г.

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

Сейчас мы реализуем следующий тип маркеров: маркер – ячейку.

Для начала нам нужно получить все занятые ячейки на каждом листе. Для этого воспользуемся методом 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;
         }


}



}

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

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