使用openXML 不用插件导出excel

注释很详细，不做解释了，有疑问可以提问

代码语言：javascript复制

using System.IO;
using System.Text;

namespace iLIS.Common
{
    /// <summary>
    /// 生成Excel文档内容
    /// 存入工作流
    /// </summary>
    public class ExcelDocumentx
    {
        private readonly StreamWriter _streamWriter;

        public ExcelDocumentx(Stream stream)
        {
            _streamWriter = new StreamWriter(stream, Encoding.UTF8);
        }

        /// <summary>
        /// 写入Excel文件头
        /// </summary>
        public void Begin()
        {
            const string excelHeader = @"<?xml version='1.0'?>
                <?mso-application progid='Excel.Sheet'?>
                <Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
                 xmlns:o='urn:schemas-microsoft-com:office:office'
                 xmlns:x='urn:schemas-microsoft-com:office:excel'
                 xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'
                 xmlns:html='http://www.w3.org/TR/REC-html40'>
                <DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>
                <Author>Hitek</Author>
                <Company>HitekSoft(C) Ltd.,</Company>
                <Version>12.00</Version>
                </DocumentProperties>
                <Styles>
                      <Style ss:ID='sH'>
                       <Alignment ss:Vertical='Center' ss:WrapText='1'/>
                       <Borders>
                        <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
                        <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
                        <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
                        <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>
                       </Borders>
                       <Font ss:FontName='宋体' x:CharSet='134' ss:Size='11' ss:Color='#000000'
                        ss:Bold='1'/>
                       <Interior ss:Color='#F2F2F2' ss:Pattern='Solid'/>
                       <NumberFormat/>
                       <Protection/>
                      </Style>

                      <Style ss:ID='sBD'>
                       <Alignment ss:Vertical='Center' ss:WrapText='1'/>
                       <Borders>
                        <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
                        <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
                        <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
                        <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>
                       </Borders>
                      </Style>
                </Styles>";
            _streamWriter.WriteLine(excelHeader);
        }

        /// <summary>
        /// 添加工作表
        /// </summary>
        /// <param name="name">表单名称</param>
        /// <param name="defaultRowHeight">默认行高</param>
        /// <param name="defaultColumnWidth">默认列宽</param>
        public void BeginSheet(string name, double defaultRowHeight = 0, double defaultColumnWidth = 0)
        {
            _streamWriter.WriteLine("<Worksheet ss:Name='"   name   "'>");
            _streamWriter.Write("<Table");
            //默认行高
            if (defaultRowHeight > 0.0001)
                _streamWriter.Write(string.Format(" ss:DefaultRowHeight='{0}'", defaultRowHeight));
            //默认列宽
            if (defaultColumnWidth > 0.0001)
                _streamWriter.Write(string.Format(" ss:ss:DefaultColumnWidth='{0}'", defaultColumnWidth));
            _streamWriter.WriteLine(">");
        }

        /// <summary>
        /// 添加标题行
        /// </summary>
        /// <param name="colNames">标题行的名称</param>
        /// <param name="colWidths">标题行的列宽</param>
        public void AddHeaderRow(string[] colNames, double[] colWidths = null)
        {
            //列宽
            if (colWidths != null && colWidths.Length > 0)
            {
                for (int i = 0; i < colWidths.Length; i  )
                {
                    if (colWidths[i] > 0.0001)
                        _streamWriter.WriteLine(string.Format("<Column ss:Index='{0}' ss:AutoFitWidth='0' ss:Width='{1}'/>", i   1, colWidths[i]));
                }
            }
            AddRow(colNames, "sH");
        }

        /// <summary>
        /// 添加一行
        /// </summary>
        /// <param name="styleName">样式名称</param>
        /// <param name="vals"></param>
        public void AddRow(object[] vals, string styleName = null)
        {
            if (string.IsNullOrEmpty(styleName))
                styleName = "sBD";
            _streamWriter.WriteLine("<Row>");
            foreach (var val in vals)
            {
                string strval = val == null ? "" : val.ToString()
                    .Replace("<", "&lt;")
                    .Replace(">", "&gt;");
                _streamWriter.WriteLine("<Cell ss:StyleID='{0}'><Data ss:Type='String'>{1}</Data></Cell>n", styleName, strval);
            }
            _streamWriter.WriteLine("</Row>");
        }
        /// <summary>
        /// 完成表单
        /// </summary>
        public void EndSheet()
        {
            _streamWriter.WriteLine("</Table>");
            _streamWriter.WriteLine("</Worksheet>");
        }
        /// <summary>
        /// 写入Excel文件结束 完成导出
        /// </summary>
        public void End()
        {
            _streamWriter.WriteLine("</Workbook>");
            _streamWriter.Close();
        }
    }
}

excel openxml

0 人点赞