Ярлыки

.Net (17) (9) 1с8 (4) 1с8.2 УП (3) документирование (1) интеграция (10) карта (1) собеседование (1) ado (1) ajax (11) ASP (1) asp.net (10) authentication (1) c# (14) coding (2) Crm Ribbon (2) csv (1) datareader (1) delegate (5) dhtml (5) exam (1) excel (10) ext (1) extjs (8) google maps (1) iis (3) javascript (33) JSON (5) linq (1) LN (5) log (1) lotus notes (5) mail (1) MS CRM (63) MS CRM 2015 (3) MS CRM 5 (48) MS CRM 6 (28) ms office (2) msi (1) MVC (1) namespace (1) oData (3) outlook (1) parent-child (2) plugin (1) program (4) Project Management (1) remote debugger (1) REST (1) SharePoint (1) SharePoint 2010 (2) Silverlight (2) soft (1) sql (13) sql reporting service (8) sql2005 (3) ssrs (2) Thread (4) tree (1) vb (6) vba (1) VSTO (1) WCF (4) wmi (1) wsc (2) xml (1) Xrm.Page (1) xslt (1)

среда, 26 сентября 2012 г.

Создание документов Excel на Microsoft OpenXML SDK

Для создания отчетов в формате документов MS Ofce 2007-2010 можно использовать пакет Microsoft OpenXML SDK. Здесь даны общие сведения.
Я написал и использую тип для установки значений отдельных ячеек, иногда это кажется проще, чем генерация всего большого файла. Но есть ньансы...

Код класса:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.IO;

using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;

/// <summary>
/// This class allows for the easy creation of a simple Excel document who's sole purpose is to contain some export data.
/// The document is created using OpenXML.
/// </summary>
internal partial class SimpleExcelDocument : IDisposable
{
    SheetData sheetData;

    /// <summary>
    /// Constructor is nothing special because the work is done at export.
    /// </summary>
    internal SimpleExcelDocument()
    {
        sheetData = new SheetData();
    }

    public SimpleExcelDocument(SheetData sheetData)
    {
        if (!sheetData.HasChildren) sheetData = new SheetData();
        this.sheetData = sheetData;
    }

    #region Get Cell Reference
    /// <summary>
    /// Получить ячейку по адресу
    /// </summary>
    /// <param name="fullAddress">в формате C5 или BF68</param>
    /// <param name="autoCreate"></param>
    public Cell GetCell(string fullAddress, bool autoCreate = true)
    {
        const string _nodigit= @"[^\d+]";
        const string _digit = @"[$\d+]";
        fullAddress = fullAddress.ToUpper();

        var cell = sheetData.Descendants<Cell>().Where(c => c.CellReference == fullAddress).FirstOrDefault();
        if (cell == null && autoCreate)
        {
            uint r = uint.Parse(System.Text.RegularExpressions.Regex.Replace(fullAddress, _nodigit, string.Empty));
            string c = System.Text.RegularExpressions.Regex.Replace(fullAddress, _digit, string.Empty);
            cell = GetCell(c, r, true);
        }
        return cell;
    }
    public Cell GetCell(uint rowId, uint columnId, bool autoCreate = true)
    {
        return GetCell(getColumnName(columnId), rowId, autoCreate);
    }
    public Cell GetCell(string columnName, uint rowId, bool autoCreate = true)
    {
        return getCell(sheetData, columnName, rowId, autoCreate);
    }
    #endregion

    #region Get Cell Contents
    // See: http://msdn.microsoft.com/en-us/library/ff921204.aspx
    // 
    #endregion


    #region Set Cell Contents
    public void SetValue(uint rowId, uint columnId, bool value)
    {
        Cell cell = GetCell(rowId, columnId, true);
        cell.DataType = CellValues.Boolean;
        cell.CellValue = new CellValue(BooleanValue.FromBoolean(value));
    }
    public void SetValue(uint rowId, uint columnId, double value)
    {
        Cell cell = GetCell(rowId, columnId, true);
        cell.DataType = CellValues.Number;
        cell.CellValue = new CellValue(DoubleValue.FromDouble(value));
    }
    public void SetValue(uint rowId, uint columnId, Int64 value)
    {
        Cell cell = GetCell(rowId, columnId, true);
        cell.DataType = CellValues.Number;
        cell.CellValue = new CellValue(IntegerValue.FromInt64(value));
    }
    /// <summary>
    /// установить дату. стиль должен быть определен в шаблоне.
    /// </summary>
    /// <param name="rowId"></param>
    /// <param name="columnId"></param>
    /// <param name="value"></param>
    /// <param name="styleIndex">индекс стиля, определенного в части Styles в шаблоне, или пустой, но тогда я ячейке будет число!</param>
    public void SetValue(uint rowId, uint columnId, DateTime value, uint styleIndex = uint.MaxValue)
    {
        Cell cell = GetCell(rowId, columnId, true);
        //cell.DataType = CellValues.Date;
        cell.CellValue = new CellValue(value.ToOADate().ToString());
        if (styleIndex != uint.MaxValue) cell.StyleIndex = styleIndex;
    }
    public void SetValue(uint rowId, uint columnId, string value)
    {
        Cell cell = GetCell(rowId, columnId, true);
        cell.CellValue = new CellValue(value);
        cell.DataType = CellValues.String;
    }
    public void SetValue(uint rowId, uint columnId, object value)
    {
        bool boolResult;
        Int64 intResult;
        DateTime dateResult;
        Double doubleResult;
        string stringResult = value.ToString();

        if (bool.TryParse(stringResult, out boolResult))
        {
            SetValue(rowId, columnId, boolResult);
        }
        else if (DateTime.TryParse(stringResult, out dateResult))
        {
            SetValue(rowId, columnId, dateResult);
        }
        else if (Int64.TryParse(stringResult, out intResult))
        {
            SetValue(rowId, columnId, intResult);
        }
        else if (Double.TryParse(stringResult, out doubleResult))
        {
            SetValue(rowId, columnId, doubleResult);
        }
        else
        {
            // Just assume that it is a plain string.
            SetValue(rowId, columnId, stringResult);
        }
    }
    #endregion

    #region Части книги и заполнение части SharedString
    /// <summary>
    ///  Given a WorkbookPart, inserts a new worksheet.
    ///  Нифига не работает - лист создается, а программно заполнить ячейки не получается.
    ///  Надо дальше разбираться, но не сейчас.
    /// </summary>
    /// <param name="workbookPart"></param>
    /// <returns></returns>
    public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
    {
        // Add a new worksheet part to the workbook.
        WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();

        Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
        worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
        worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
        worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
        SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1" };

        SheetViews sheetViews1 = new SheetViews();
        SheetView sheetView1 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };

        sheetViews1.Append(sheetView1);
        SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };

        SheetData sheetData1 = new SheetData();

        worksheet1.Append(sheetDimension1);
        worksheet1.Append(sheetViews1);
        worksheet1.Append(sheetFormatProperties1);
        worksheet1.Append(sheetData1);

        newWorksheetPart.Worksheet = worksheet1;
        newWorksheetPart.Worksheet.Save();

        Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
        string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

        // Get a unique ID for the new sheet.
        uint sheetId = 1;
        if (sheets.Elements<Sheet>().Count() > 0)
        {
            sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
        }

        string sheetName = "Sheet" + sheetId;

        // Append the new worksheet and associate it with the workbook.
        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
        sheets.Append(sheet);
        workbookPart.Workbook.Save();

        return newWorksheetPart;
    }

    /// <summary>
    /// Вставка (или только получение номера) строки (возможно повторяющейся) в часть SharedString.
    /// После использование метода больше нельзя вставлять просто значения в ячейки листов. 
    /// Почему так - не знаю.
    /// <see cref="http://msdn.microsoft.com/en-us/library/office/cc861607.aspx"/>
    /// </summary>
    /// <param name="ssDoc"></param>
    /// <param name="text"></param>
    /// <returns></returns>
    public int InsertSharedStringItem(SpreadsheetDocument ssDoc, string text)
    {
        SharedStringTablePart part;
        if (ssDoc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count<SharedStringTablePart>() > 0)
        {
            part = ssDoc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First<SharedStringTablePart>();
        }
        else
        {
            part = ssDoc.WorkbookPart.AddNewPart<SharedStringTablePart>("rId6");
            part.SharedStringTable = new SharedStringTable() { Count = 1, UniqueCount = 1 };
        }
        int num = 0;
        foreach (SharedStringItem item in part.SharedStringTable.Elements<SharedStringItem>())
        {
            if (item.InnerText == text)
            {
                return num;
            }
            num++;
        }
        part.SharedStringTable.AppendChild<SharedStringItem>(new SharedStringItem(new OpenXmlElement[] { new Text(text) }));
        part.SharedStringTable.Save();
        return num;
    }

    /// <summary>
    /// Вставка (или только получение номера) строки (возможно повторяющейся) в часть SharedString.
    /// После использование метода больше нельзя вставлять просто значения в ячейки листов. 
    /// Почему так - не знаю.
    /// <see cref="http://msdn.microsoft.com/en-us/library/office/cc861607.aspx"/>
    /// </summary>
    /// <param name="ssDoc"></param>
    /// <param name="text"></param>
    /// <returns></returns>
    public int InsertSharedStringItem(SpreadsheetDocument ssDoc, Cell cell, string valueText)
    {
        var num = InsertSharedStringItem(ssDoc, valueText);
        cell.CellValue = new CellValue(num.ToString());
        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
        return num;
    }

    #endregion

    public SheetData ExportAsSheetData()
    {
        return sheetData;
    }

    public void ExportAsXLSXStream(Stream outputStream)
    {
        // See: http://blogs.msdn.com/b/chrisquon/archive/2009/07/22/creating-an-excel-spreadsheet-from-scratch-using-openxml.aspx for some ideas...
        // See: http://stackoverflow.com/questions/1271520/opening-xlsx-in-office-2003

        using (SpreadsheetDocument package = SpreadsheetDocument.Create(outputStream, SpreadsheetDocumentType.Workbook))
        {
            // Setup the basics of a spreadsheet document.
            package.AddWorkbookPart();
            package.WorkbookPart.Workbook = new Workbook();
            WorksheetPart workSheetPart = package.WorkbookPart.AddNewPart<WorksheetPart>();
            workSheetPart.Worksheet = new Worksheet(sheetData);
            workSheetPart.Worksheet.Save();

            // create the worksheet to workbook relation
            package.WorkbookPart.Workbook.AppendChild(new Sheets());
            Sheet sheet = new Sheet
            {
                Id = package.WorkbookPart.GetIdOfPart(workSheetPart),
                SheetId = 1,
                Name = "Sheet 1"
            };
            package.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild<Sheet>(sheet);
            package.WorkbookPart.Workbook.Save();
            package.Close();
        }
    }

    #region Internal Methods
    private static string getColumnName(uint columnId)
    {
        if (columnId < 1)
        {
            throw new Exception("The column # can't be less then 1.");
        }
        columnId--;
        if (columnId >= 0 && columnId < 26)
            return ((char)('A' + columnId)).ToString();
        else if (columnId > 25)
            return getColumnName(columnId / 26) + getColumnName(columnId % 26 + 1);
        else
            throw new Exception("Invalid Column #" + (columnId + 1).ToString());
    }

    // Given a worksheet, a column name, and a row index, 
    // gets the cell at the specified column 
    private Cell getCell(SheetData worksheet,
              string columnName, uint rowIndex, bool autoCreate)
    {
        Row row = getRow(worksheet, rowIndex, autoCreate);

        if (row == null)
            return null;

        Cell foundCell = row.Elements<Cell>().Where(c => string.Compare
               (c.CellReference.Value, columnName +
               rowIndex, true) == 0).FirstOrDefault();

        if (foundCell == null && autoCreate)
        {
            foundCell = new Cell();
            foundCell.CellReference = columnName + rowIndex.ToString();
            row.AppendChild(foundCell);
        }
        return foundCell;
    }
    #endregion

    /// <summary>
    /// Given a worksheet and a row index, return the row. 
    /// <see cref="http://msdn.microsoft.com/en-us/library/bb508943(v=office.12).aspx#Y2142"/>
    /// </summary>
    public Row getRow(SheetData worksheet, uint rowIndex, bool autoCreate = true)
    {
        if (rowIndex < 1)
        {
            throw new Exception("The row # can't be less then 1.");
        }

        Row foundRow = worksheet.Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();

        if (foundRow == null && autoCreate)
        {
            foundRow = new Row();
            foundRow.RowIndex = rowIndex;
            worksheet.AppendChild(foundRow);
        }
        return foundRow;
    }
    
    #region IDisposable Stuff
    private bool _disposed;
    //private bool _transactionComplete;

    /// <summary>
    /// This will dispose of any open resources.
    /// </summary>
    public void Dispose()
    {
        Dispose(true);

        // Use SupressFinalize in case a subclass
        // of this type implements a finalizer.
        GC.SuppressFinalize(this);
    }

    protected virtual void Dispose(bool disposing)
    {
        // If you need thread safety, use a lock around these 
        // operations, as well as in your methods that use the resource.
        if (!_disposed)
        {
            if (disposing)
            {
                //if (!_transactionComplete)
                //    Commit();
            }

            // Indicate that the instance has been disposed.
            //_transaction = null;
            _disposed = true;
        }
    }
    #endregion

}

Пример использования:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.IO;

using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;

namespace OpenXmlTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            var fileInPath = @"c:\temp\Book1.xlsx";
            var fileOutPath = @"C:\temp\newName.xlsx";

            byte[] byteArray = File.ReadAllBytes(fileInPath);
            using (MemoryStream stream = new MemoryStream())
            {
                stream.Write(byteArray, 0, (int)byteArray.Length);
                //SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true);
                using (var spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
                {
                    Worksheet worksheet = spreadsheetDoc.WorkbookPart.WorksheetParts.Last<WorksheetPart>().Worksheet;
                    
                    var sd = (SheetData)worksheet.Descendants<SheetData>().First();
                     
                    if (!sd.HasChildren)
                    {
                        var strErr = "В книге на первом листе надо что-нибудь написать\r\nИначе фигня получается.";
                        MessageBox.Show(strErr);
                        throw new NullReferenceException(strErr);
                    }
                    
                    SimpleExcelDocument sed = new SimpleExcelDocument(sd);
                    
                    sed.SetValue(3, 3, 499);
                    sed.SetValue(4, 3, 745.7456);
                    sed.SetValue(5, 3, DateTime.Now); // стиль не указан - в ячейке будет число. Если указать стиль, то надо узнать его номер в таблице стилей
                    sed.SetValue(6, 3, "Текст на русском языке");
                    sed.SetValue(7, 3, "15:45");
                    sed.SetValue(8, 3, DateTime.Now.ToString("dd.MM.yyyy"));
                    
                    var c = sed.GetCell("F11");
                    var num11 = sed.InsertSharedStringItem(spreadsheetDoc, "qwerty");
                    c.CellValue = new CellValue(num11.ToString());
                    c.DataType = new EnumValue<CellValues>(CellValues.SharedString);

                    c = sed.GetCell("F12");
                    num11 = sed.InsertSharedStringItem(spreadsheetDoc, "qwerty2");
                    c.CellValue = new CellValue(num11.ToString());
                    c.DataType = new EnumValue<CellValues>(CellValues.SharedString);

                    c = sed.GetCell("F13");
                    num11 = sed.InsertSharedStringItem(spreadsheetDoc, c, "qwerty");

                    // уже нельзя из-за использования SharedString
                    // sed.SetValue(9, 3, "15:46");

                    worksheet.Save();
                    spreadsheetDoc.Close();

                }
                if (File.Exists(fileOutPath)) File.Delete(fileOutPath);
                File.WriteAllBytes(fileOutPath, stream.ToArray());
            }

        }

    }
}



Надо сказать, что
  • в подопытной книге должны существовать листы хоть с каким-то значением в ячейке на которые предполагается вести запись. 
  • После записи в SharedString запись в лист не идет. 
  • Новый лист вставить нельзя (можно, но потом заполнять ячейки не получается)
Мое мнение - годный продукт, но бывает надо набраться терпения и много пробовать.


Загрузка файла
Если отчет готовится на сервере IIS, то на asp.net посредством обработчика ashx передать отчет на клиент можно так:

// через Stream
public void ProcessRequest(HttpContext context)
{
    try
    {
        if (context == null)
        {
            throw new ArgumentNullException("context");
        }

        string path = Path.Combine(context.Request.PhysicalApplicationPath, "App_Data", "шаблон_отчета.xlsx");
        using (MemoryStream stream = new MemoryStream())
        {
            byte[] buffer = File.ReadAllBytes(path);
            stream.Write(buffer, 0, buffer.Count<byte>());
            string str2 = GenerateReport(..., stream); // полезный код, заполнить ячейки эксель
            context.Response.Clear();
            context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlPathEncode("Счет №" + str2) + ".xlsx");
            context.Response.OutputStream.Write(stream.ToArray(), 0, stream.ToArray().Count<byte>());
            context.Response.Flush();
            context.Response.Close();
        }
    }
    catch (Exception exception)
    {
        context.Response.Write(exception.Message);
    }
}

// через WriteFile
public void ProcessRequest(HttpContext context)
{
    try
    {
        string result_path = Path.GetTempFileName();
        string nameFile = "вс_";
        context.Response.Clear();
        context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlPathEncode(nameFile) + ".xlsx");
        if (context.Request["opportunityid"] != null)
        {
            using (this.exportDocument = SpreadsheetDocument.Create(result_path, SpreadsheetDocumentType.Workbook))
            {
                // полезный код, заполнить ячейки эксель
            }
            context.Response.WriteFile(result_path);
            context.Response.Flush();
            context.Response.Close();
            File.Delete(result_path);
        }
    }
    catch (Exception ex)
    {
        context.Response.Write(exception.Message);
    }
}

На WCF передать отчет можно так, правда это на старом. А так на новом (из .net 4-ой версии) или так.


ps
есть альтернатива для OpenXML SDK - EPPlus 3.1 , рекомендую!



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