出口通过MVC3练成 [英] export to excel through mvc3

问题描述

大家好我试图导出一些数据在mvc3.Here表的形式先争优是我使用生成Excel文件中的类：

Hello everyone i am trying to export some data to excel in the form of table in mvc3.Here is the class i am using to generate the excel file:

//------------------------------------------------------------------------------
// <copyright file="ExcelDocument.cs" company="Microsoft">
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// </copyright>
// <summary>The Export to Excel Document class.</summary>
//  ----------------------------------------------------------------------------
namespace ExporToExcel.Controllers.ControllerExtensions
{
    using System;
    using System.IO;
    using System.Linq;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Extensions;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;


    /// <summary>
    /// Excel document.
    /// </summary>
    public static class ExcelDocument
    {
        /// <summary>
        /// Default spread sheet name. 
        /// </summary>
        private const string DefaultSheetName = "Sheet1";

        /// <summary>
        /// Create the exel document for streaming.
        /// </summary>
        /// <param name="documentName">Excel file name.</param>
        /// <param name="excelWorkSheetName">Excel worksheet name: default: sheet1.</param>
        /// <param name="rowData">Row data to write.</param>
        /// <param name="headerData">Header data.</param>
        /// <param name="rowPointers">Row pointers.</param>
        /// <returns>Memory stream.</returns>
        public static MemoryStream Create(string documentName, string excelWorkSheetName, IQueryable rowData, string[] headerData, string[] rowPointers)
        {
            return CreateSpreadSheet(documentName, excelWorkSheetName, rowData, headerData, rowPointers, null);
        }

        /// <summary>
        /// Create the spreadsheet.
        /// </summary>
        /// <param name="documentName">Excel file name.</param>
        /// <param name="excelWorkSheetName">Excel worksheet name: default: sheet1.</param>
        /// <param name="rowData">Row data to write.</param>
        /// <param name="headerData">Header data.</param>
        /// <param name="rowPointers">Row pointers.</param>
        /// <param name="styleSheet">Style sheet.</param>
        /// <returns>Memory stream.</returns>
        private static MemoryStream CreateSpreadSheet(string documentName, string excelWorkSheetName, IQueryable rowData, string[] headerData, string[] rowPointers, Stylesheet styleSheet)
        {
            int rowNum = 0;
            int colNum = 0;
            int maxWidth = 0;
            int minCol = 1;
            int maxCol = rowPointers == null ? minCol : rowPointers.Length;
            maxCol = maxCol == 1 && headerData == null ? 1 : headerData.Length;

            MemoryStream xmlStream = SpreadsheetReader.Create();
            SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(xmlStream, true);

            SetSheetName(excelWorkSheetName, spreadSheet);

            if (styleSheet == null)
            {
                SetStyleSheet(spreadSheet);
            }
            else
            {
                spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet = styleSheet;
                spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
            }

            WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(spreadSheet, excelWorkSheetName);

            WriteHeaders(headerData, out rowNum, out colNum, out maxWidth, spreadSheet, worksheetPart);
            AddCellWidthStyles(Convert.ToUInt32(minCol), Convert.ToUInt32(maxCol), maxWidth, spreadSheet, worksheetPart);

            if (rowPointers == null || rowPointers.Length == 0)
            {
                WriteRowsFromHeaders(rowData, headerData, rowNum, out maxWidth, spreadSheet, worksheetPart);
            }
            else
            {
                WriteRowsFromKeys(rowData, rowPointers, rowNum, out maxWidth, spreadSheet, worksheetPart);
            }

            // Save to the memory stream
            SpreadsheetWriter.Save(spreadSheet);
            spreadSheet.Close();
            spreadSheet.Dispose();
            return xmlStream;
        }

        /// <summary>
        /// Set the name of the spreadsheet. 
        /// </summary>
        /// <param name="excelSpreadSheetName">Spread sheet name.</param>
        /// <param name="spreadSheet">Spread sheet.</param>
        private static void SetSheetName(string excelSpreadSheetName, SpreadsheetDocument spreadSheet)
        {
            excelSpreadSheetName = excelSpreadSheetName ?? DefaultSheetName;

            Sheet ss = spreadSheet.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == DefaultSheetName).SingleOrDefault<Sheet>();
            ss.Name = excelSpreadSheetName;
        }

        /// <summary>
        /// Add cell width styles. 
        /// </summary>
        /// <param name="minCol">Minimum column index.</param>
        /// <param name="maxCol">Maximum column index.</param>
        /// <param name="maxWidth">Maximum column width.</param>
        /// <param name="spreadSheet">Spread sheet.</param>
        /// <param name="workSheetPart">Work sheet.</param>
        private static void AddCellWidthStyles(uint minCol, uint maxCol, int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheetPart)
        {
            Columns cols = new Columns(new Column() { Min = minCol, Max = maxCol, Width = maxWidth});


            workSheetPart.Worksheet.InsertBefore<Columns>(cols, workSheetPart.Worksheet.GetFirstChild<SheetData>());
        }

        /// <summary>
        /// Set the style sheet.
        // Note: Setting the style here rather than passing it in ensures that all worksheets will have a common user interface design.
        /// </summary>
        /// <param name="spreadSheet">Spread sheet to change.</param>
        private static void SetStyleSheet(SpreadsheetDocument spreadSheet)
        {
            // Note: Setting the style here rather than passing it in ensures that all worksheets will have a common user interface design.
            Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;

            styleSheet.Fonts.AppendChild(
                new Font(new FontSize() { Val = 11 }, new Color() { Rgb = "FFFFFF" }, new FontName() { Val = "Arial" }));

            styleSheet.Fills.AppendChild(new Fill()
            {
                PatternFill = new PatternFill()
                {
                    PatternType = PatternValues.Solid,
                    BackgroundColor = new BackgroundColor() { Rgb = "D8D8D8" }

                }
            });

            spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
        }

        /// <summary>
        /// Save the styl for worksheet headers. 
        /// </summary>
        /// <param name="cellLocation">Cell location.</param>
        /// <param name="spreadSheet">Spreadsheet to change.</param>
        /// <param name="workSheetPart">Worksheet to change.</param>
        private static void SeatHeaderStyle(string cellLocation, SpreadsheetDocument spreadSheet, WorksheetPart workSheetPart)
        {
            Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
            Cell cell = workSheetPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == cellLocation).FirstOrDefault();

            if (cell == null)
            {
                throw new ArgumentNullException("Cell not found");
            }

            cell.SetAttribute(new OpenXmlAttribute("", "s", "", "1"));
            OpenXmlAttribute cellStyleAttribute = cell.GetAttribute("s", "");
            CellFormats cellFormats = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;

            // pick tthe first cell format.
            CellFormat cellFormat = (CellFormat)cellFormats.ElementAt(0);

            CellFormat cf = new CellFormat(cellFormat.OuterXml);
            cf.FontId = styleSheet.Fonts.Count;
            cf.FillId = styleSheet.Fills.Count;
            cf.Alignment.Horizontal = HorizontalAlignmentValues.General;
            cellFormats.AppendChild(cf);

            int a = (int)styleSheet.CellFormats.Count.Value;

            cell.SetAttribute(cellStyleAttribute);

            cell.StyleIndex = styleSheet.CellFormats.Count;

            workSheetPart.Worksheet.Save();
        }

        /// <summary>
        /// Replace special characters. 
        /// </summary>
        /// <param name="value">Value to input.</param>
        /// <returns>Value with special characters replaced.</returns>
        private static string ReplaceSpecialCharacters(string value)
        {
            value = value.Replace("’", "'");
            value = value.Replace(""", "\"");
            value = value.Replace(""", "\"");
            value = value.Replace("–", "-");
            value = value.Replace("…", "...");
            return value;
        }

        /// <summary>
        /// Write values to the spreadsheet.
        /// </summary>
        /// <param name="cellLocation">Row Column Value.</param>
        /// <param name="strValue">Value to write.</param>
        /// <param name="spreadSheet">Spreadsheet to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);

            int intValue = 0;
            if (strValue.Contains("$"))
            {
                strValue = strValue.Replace("$", "");
                strValue = strValue.Replace(",", "");

                workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
            }
            else if (int.TryParse(strValue, out intValue))
            {
                workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
            }
            else if (string.IsNullOrEmpty(strValue))
            {
                workSheetWriter.PasteText(cellLocation, strValue);
            }
            else
            {
                workSheetWriter.PasteText(cellLocation, strValue);
            }
        }

        /// <summary>
        /// Write the excel rows for the spreadsheet.
        /// </summary>
        /// <param name="rowData">Excel row values.</param>
        /// <param name="rowDataKeys">Excel row-key values.</param>
        /// <param name="rowNum">Row number.</param>
        /// <param name="maxWidth">Max width.</param>
        /// <param name="spreadSheet">Spreadsheet to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteRowsFromKeys(IQueryable rowData, string[] rowDataKeys, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            maxWidth = 0;

            foreach (object row in rowData)
            {
                int colNum = 0;
                foreach (string rowKey in rowDataKeys)
                {
                    string strValue = row.GetType().GetProperty(rowKey).GetValue(row, null).ToString();
                    strValue = ReplaceSpecialCharacters(strValue);
                    maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;

                    string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
                    ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);

                    colNum++;
                }

                rowNum++;
            }
        }

        /// <summary>
        /// Convert column number to alpha numeric value.
        /// </summary>
        /// <param name="colNumber">Column number.</param>
        /// <returns>ASCII value for number.</returns>
        private static string GetColumnLetter(string colNumber)
        {
            if (string.IsNullOrEmpty(colNumber))
            {
                throw new ArgumentNullException(colNumber);
            }

            string colName = null;

            try
            {
                for (int i = 0; i < colNumber.Length; i++)
                {
                    string colValue = colNumber.Substring(i, 1);

                    int asc = Convert.ToInt16(colValue) + 65;

                    colName += Convert.ToChar(asc);
                }
            }
            finally
            {
                colName = colName ?? "A";
            }

            return colName;
        }

        /// <summary>
        /// Write the values for the rows from headers.
        /// </summary>
        /// <param name="rowData">Excel row values.</param>
        /// <param name="headerData">Excel header values.</param>
        /// <param name="rowNum">Row number.</param>
        /// <param name="maxWidth">Max width.</param>
        /// <param name="spreadSheet">Spreadsheet to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteRowsFromHeaders(IQueryable rowData, string[] headerData, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
            maxWidth = 0;

            foreach (object row in rowData)
            {
                int colNum = 0;
                foreach (string header in headerData)
                {
                    string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
                    strValue = ReplaceSpecialCharacters(strValue);
                    maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;

                    string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);

                    ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
                    colNum++;
                }

                rowNum++;
            }
        }

        /// <summary>
        /// Write the excel headers for the spreadsheet.
        /// </summary>
        /// <param name="headerData">Excel header values.</param>
        /// <param name="rowNum">Row number.</param>
        /// <param name="colNum">Column Number.</param>
        /// <param name="maxWidth">Max column width</param>
        /// <param name="spreadSheet">Maximum Column Width to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteHeaders(string[] headerData, out int rowNum, out int colNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            rowNum = 1;
            colNum = 0;
            maxWidth = 0;

            foreach (string header in headerData)
            {
                string strValue = ReplaceSpecialCharacters(header);

                string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
                maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
                maxWidth = strValue.Length+25;
                ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
                SeatHeaderStyle(cellLocation, spreadSheet, workSheet);
                colNum++;
            }

            rowNum++;
        }
    }
}

我的问题是，我得到的所有文字左对齐在我的table.I希望它成为的右对齐。也该表中的列标题< STRONG>黑色背景。我想改变它的颜色too.Also如何给任意单元格一些硬codeD文字？任何帮助将大大AP preciated.Thank你

my problem is that i am getting all the text aligned left in my table.I want it to be aligned right.Also the column header in the table has black background.I want to change its color too.Also how to give some hard coded text in any cell? Any help would be greatly appreciated.Thank you

推荐答案

而不是在code绑定生成表的列和头，为它创建一个视图，以便您可以轻松地添加在线样式头和列。

Instead of generating columns and headers of table in code bind,create a View for it so that you can easily add in-line styles to header and columns.

添加新类

public class DownloadFileActionResult : ActionResult
    {
        public string ExcelGridView { get; set; }
        public string fileName { get; set; }

        public DownloadFileActionResult(string gv, string pFileName)
        {
            ExcelGridView = gv;
            fileName = pFileName;

        }


        public override void ExecuteResult(ControllerContext context)
        {

            //Create a response stream to create and write the Excel file
            HttpContext curContext = HttpContext.Current;
            curContext.Response.Clear();
            curContext.Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
            curContext.Response.Charset = "";
            curContext.Response.Cache.SetCacheability(HttpCacheability.NoCache);
            curContext.Response.ContentType = "application/ms-excel";

            //Open a memory stream that you can use to write back to the response
            byte[] byteArray = Encoding.ASCII.GetBytes(ExcelGridView);
            MemoryStream s = new MemoryStream(byteArray);
            StreamReader sr = new StreamReader(s, Encoding.Unicode);

            //Write the stream back to the response
            curContext.Response.ContentEncoding = System.Text.Encoding.Unicode;
            curContext.Response.Write(sr.ReadToEnd());
            curContext.Response.End();




        }
    }  

这是从的ActionResult 这是接受两个参数，一个是你的查看字符串formatte，另一个是的 Excel的文件名

which is inherit from ActionResult which is accept two parameter one is your View in string formatte and another is Excel-filename.

//In Controller 

public ActionResult SaveExcel()
{
            string html = RenderPartialViewToString("PartailViewName", model);
            // if your view don't have any model then you can pass as null

            html = html.Replace("\n", "");
            html = html.Replace("\r", "");
            html = html.Replace("  ", "");

            return new DownloadFileActionResult(html, "ExcelSheetName.xls");
}



      protected string RenderPartialViewToString(string viewName, object model)
            {
                if (string.IsNullOrEmpty(viewName))
                    viewName = ControllerContext.RouteData.GetRequiredString("action");

                ViewData.Model = model;

                using (StringWriter sw = new StringWriter())
                {
                    ViewEngineResult viewResult = ViewEngines.Engines.FindPartialView(ControllerContext, viewName);
                    ViewContext viewContext = new ViewContext(ControllerContext, viewResult.View, ViewData, TempData, sw);
                    viewResult.View.Render(viewContext, sw);
                    return sw.GetStringBuilder().ToString();


         }
    }

PartialView的返回类型为 MvcHtmlString 你可以得到它的控制器，所以我有回到一种方法 RenderPartialViewToString 它接受两个参数，一个是的 PartailView 另一个是的模式并会回报你RenderHtmlString局部视图的。

return type of PartialView is MvcHtmlString you can get it in controller, so i have return one method RenderPartialViewToString which accept two parameter one is PartailView and another is Model and it will return you RenderHtmlString of partial view.

创建一个局部视图

//PartailViewName.cshtml

<table>
     <tr>
        <th style="background-color:Gray;">
            Column1
        </th>
       <th style="background-color:Gray;">
            Column1
        </th>
     </tr>
     <tr>
        <td>
             Coulmn data
        </td>
        <td>
            Coulmn data
        </td>
     </tr>
</table> 

请注意：视图必须局部视图2）你的所有数据都必须安排表结构仅3）只给出了在线样式。

NOTE : view must be partial view 2) your all data must be arrange in table structure only 3) give only in-line styles.

这篇关于出口通过MVC3练成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！