利用org.in2bits.MyXls.dll实现从服务器端下载EXCEL数据

最新推荐文章于 2016-09-15 15:56:00 发布

原创最新推荐文章于 2016-09-15 15:56:00 发布 · 9.1k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#excel #服务器 #string #dataset #null #microsoft

web开发同时被 2 个专栏收录

30 篇文章

订阅专栏

C# WinForm

8 篇文章

订阅专栏

本文介绍了一种无需在服务器上安装Excel即可实现JQGrid数据导出的方法，并提供了一个示例类ExcelHelper，该类能构造并发送Excel文件供客户端下载。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前些天写了一篇JQGRID导出数据的文章，为什么在客户端实现，其实是被逼无奈，因为在服务器上安装EXCEL，然后配置DCOM一直不成功，老是不能成功调用服务器上的EXCEL应用程序，然后今天发现上次那个JQGRID导出在IE8和FF浏览器中不兼容，只能在IE6使用，因为里面使用了document.execCommand命令来导出，而在IE8或者FF中出于安全性问题这个命令只能用来导出txt或者html文件，不能用来导出xls文件了.今天无意中百度到了一个第三方EXCEL插件，可以不用在服务器上安装EXCEL配置DCOM都能实现从服务器下载EXCEL文件。org.in2bits.MyXls.dll请在百度里搜索一下就能下载到。下面提供一个ExcelHelper，这个类从网上找到的，我自己根据自己的需求改动了一下，用来构造并发送excel文件给客户端下载.

using System;
using System.Data;
using System.Configuration;
using System.Web;

using System.Collections.Generic;
using org.in2bits.MyXls;
using org.in2bits.MyXls.ByteUtil;
/// <summary>
///ExcelHelper 的摘要说明
/// </summary>
public class ExcelHelper
{
    protected ExcelHelper()
    {
    }
    public class TableStruct
    {
        private string _TableName;
        private string _TableCaption;
        private List<ColumnStruct> _ColumnInfoAry;
        public string TableName
        {
            get
            {
                if (string.IsNullOrEmpty(_TableName))
                {
                    return string.Empty;
                }
                return _TableName;
            }
            set
            {
                _TableName = value;
            }
        }
        public string TableCaption
        {
            get
            {
                if (string.IsNullOrEmpty(_TableCaption))
                {
                    return TableName;
                }
                return _TableCaption;
            }
            set
            {
                _TableCaption = value;
            }
        }
        public List<ColumnStruct> ColumnInfoAry
        {
            get
            {
                if (_ColumnInfoAry == null)
                {
                    _ColumnInfoAry = new List<ColumnStruct>();
                }
                return _ColumnInfoAry;
            }
        }
        public void AddColumnInfo(ColumnStruct ColumnInfo)
        {
            if (ColumnInfo == null)
            {
                return;
            }
            if (_ColumnInfoAry == null)
            {
                _ColumnInfoAry = new List<ColumnStruct>();
            }
            foreach (ColumnStruct col in _ColumnInfoAry)
            {
                if (col.ColumnName.Equals(ColumnInfo.ColumnName, StringComparison.OrdinalIgnoreCase))
                {
                    return;
                }
            }
            _ColumnInfoAry.Add(ColumnInfo);
        }
        public ColumnStruct GetColumnInfo(string ColumnName)
        {
            if (string.IsNullOrEmpty(ColumnName))
            {
                return null;
            }
            if (_ColumnInfoAry == null)
            {
                return null;
            }
            ColumnStruct ColumnInfo = null;
            foreach (ColumnStruct col in _ColumnInfoAry)
            {
                if (col.ColumnName.Equals(ColumnName, StringComparison.OrdinalIgnoreCase))
                {
                    ColumnInfo = col;
                }
            }
            return ColumnInfo;
        }

    }
    public class ColumnStruct
    {
        private string _ColumnName;
        private string _ColumnCaption;
        private string _ColumnTextFormat;

        public string ColumnName
        {
            get
            {
                if (string.IsNullOrEmpty(_ColumnName))
                {
                    return string.Empty;
                }
                return _ColumnName;
            }
            set
            {
                _ColumnName = value;
            }
        }
        public string ColumnCaption
        {
            get
            {
                if (string.IsNullOrEmpty(_ColumnCaption))
                {
                    return ColumnName;
                }
                return _ColumnCaption;
            }
            set
            {
                _ColumnCaption = value;
            }
        }

        public string ColumnTextFormat
        {
            get
            {
                if (string.IsNullOrEmpty(_ColumnTextFormat))
                {
                    return string.Empty;
                }
                return _ColumnTextFormat;
            }
            set
            {
                _ColumnTextFormat = value;
            }
        }
    }

    public static void ExportToExcel(DataSet ds, List<TableStruct> TableInfoAry)
    {
        if (ds == null)
        {
            ds = new DataSet();
        }
        if (TableInfoAry == null)
        {
            TableInfoAry = new List<TableStruct>();
        }

        XlsDocument xls = new XlsDocument();
        xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmssffff", System.Globalization.DateTimeFormatInfo.InvariantInfo);
        xls.SummaryInformation.Author = "wangmh"; //填加xls文件作者信息
        xls.SummaryInformation.NameOfCreatingApplication = "Microsoft Excel"; //填加xls文件创建程序信息
        xls.SummaryInformation.LastSavedBy = "wangmh"; //填加xls文件最后保存者信息
        xls.SummaryInformation.Comments = "Gwm"; //填加xls文件作者信息
        xls.SummaryInformation.Title = "Gwm"; //填加xls文件标题信息
        xls.SummaryInformation.Subject = "Gwm";//填加文件主题信息
        xls.DocumentSummaryInformation.Company = "Gwm";//填加文件公司信息
        foreach (TableStruct TableInfo in TableInfoAry)
        {
            DataTable dt = ds.Tables[TableInfo.TableName];
            if (dt == null)
            {
                continue;
            }
            Worksheet sheet = xls.Workbook.Worksheets.Add(TableInfo.TableCaption);

            //设置标头栏
            ushort ColumnIndex = 1;
            foreach (ColumnStruct ColStruct in TableInfo.ColumnInfoAry)
            {
                ushort RowIndex = 1;
                Row row = sheet.Rows.AddRow(RowIndex);
                if (!dt.Columns.Contains(ColStruct.ColumnName))
                {
                    continue;
                }

                Cell cell = null;
                if (row.CellExists(ColumnIndex))
                {
                    cell = row.GetCell(ColumnIndex);
                }
                else
                {
                    cell = sheet.Cells.Add(RowIndex, ColumnIndex, null);
                    //row.AddCell(cell);
                }
                cell.Value = ColStruct.ColumnCaption;
                cell.Font.Weight = FontWeight.Bold;
                cell.HorizontalAlignment = HorizontalAlignments.Centered;
                cell.BottomLineStyle = 2;
                cell.BottomLineColor = Colors.Grey;
                cell.Font.Height = 10 * 20;
                cell.VerticalAlignment = VerticalAlignments.Centered;

                ushort ColumnMaxLength = GetColumnValueMaxLength(dt, ColStruct);
                //设定列宽为自适应宽度
                ColumnInfo colInfo = new ColumnInfo(xls, sheet);//生成列格式对象
                //设定colInfo格式的起作用的列为第1列到第5列(列格式为0-base)
                colInfo.ColumnIndexStart = (ushort)(ColumnIndex - 1);
                colInfo.ColumnIndexEnd = colInfo.ColumnIndexStart;
                colInfo.Width = (ushort)(ColumnMaxLength * 256);//列的宽度计量单位为 1/256 字符宽
                sheet.AddColumnInfo(colInfo);//把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不能把colInfo对象多次附给sheet页）

                ColumnIndex++;
            }

            for (ushort i = 0; i < dt.Rows.Count; i++)
            {
                ushort RowIndex = (ushort)(i + 2);
                Row row = sheet.Rows.AddRow(RowIndex);
                int j = 0;
                foreach (ColumnStruct ColStruct in TableInfo.ColumnInfoAry)
                {
                    if (!dt.Columns.Contains(ColStruct.ColumnName))
                    {
                        continue;
                    }

                    ColumnIndex = (ushort)(j + 1);
                    Cell cell = null;
                    if (row.CellExists(ColumnIndex))
                    {
                        cell = row.GetCell(ColumnIndex);
                    }
                    else
                    {
                        cell = sheet.Cells.Add(RowIndex, ColumnIndex, null);
                        //row.AddCell(cell);
                    }

                    object objValue = dt.Rows[i][ColStruct.ColumnName];
                    cell.Value = GetColumnValueFormat(dt, ColStruct, objValue);
                    cell.Font.Weight = FontWeight.Normal;
                    cell.HorizontalAlignment = HorizontalAlignments.Centered;
                    j++;
                }
            }
        }
        xls.Send();
    }

    private static string GetColumnValueFormat(DataTable dt, ColumnStruct ColStruct, Object ObjValue)
    {
        string ColumnValue = string.Empty;
        if (ObjValue != null && ObjValue != DBNull.Value)
        {
            string ColumnDataType = dt.Columns[ColStruct.ColumnName].DataType.ToString();
            switch (ColumnDataType)
            {
                case "System.Boolean":
                case "System.Byte":
                    {
                        ColumnValue = ObjValue.ToString();
                        break;
                    }
                case "System.Decimal":
                    {
                        if (string.IsNullOrEmpty(ColStruct.ColumnTextFormat))
                        {
                            ColumnValue = Convert.ToDecimal(ObjValue).ToString();
                        }
                        else
                        {
                            ColumnValue = Convert.ToDecimal(ObjValue).ToString(ColStruct.ColumnTextFormat);
                        }
                        break;
                    }
                case "System.Double":
                    {
                        if (string.IsNullOrEmpty(ColStruct.ColumnTextFormat))
                        {
                            ColumnValue = Convert.ToDouble(ObjValue).ToString();
                        }
                        else
                        {
                            ColumnValue = Convert.ToDouble(ObjValue).ToString(ColStruct.ColumnTextFormat);
                        }
                        break;
                    }
                case "System.Int64":
                    {
                        if (string.IsNullOrEmpty(ColStruct.ColumnTextFormat))
                        {
                            ColumnValue = Convert.ToInt64(ObjValue).ToString();
                        }
                        else
                        {
                            ColumnValue = Convert.ToInt64(ObjValue).ToString(ColStruct.ColumnTextFormat);
                        }
                        break;
                    }
                case "System.Int16":
                    {
                        if (string.IsNullOrEmpty(ColStruct.ColumnTextFormat))
                        {
                            ColumnValue = Convert.ToInt16(ObjValue).ToString();
                        }
                        else
                        {
                            ColumnValue = Convert.ToInt16(ObjValue).ToString(ColStruct.ColumnTextFormat);
                        }
                        break;
                    }
                case "System.Int32":
                    {
                        if (string.IsNullOrEmpty(ColStruct.ColumnTextFormat))
                        {
                            ColumnValue = Convert.ToInt32(ObjValue).ToString();
                        }
                        else
                        {
                            ColumnValue = Convert.ToInt32(ObjValue).ToString(ColStruct.ColumnTextFormat);
                        }
                        break;
                    }
                case "System.DateTime":
                    {
                        if (string.IsNullOrEmpty(ColStruct.ColumnTextFormat))
                        {
                            ColumnValue = Convert.ToDateTime(ObjValue).ToString();
                        }
                        else
                        {
                            ColumnValue = Convert.ToDateTime(ObjValue).ToString(ColStruct.ColumnTextFormat, System.Globalization.DateTimeFormatInfo.InvariantInfo);
                        }
                        break;
                    }
                default:
                    {
                        ColumnValue = ObjValue.ToString();
                        break;
                    }
            }
        }
        return ColumnValue.Trim();
    }

    private static ushort GetColumnValueMaxLength(DataTable dt, ColumnStruct ColStruct)
    {
        ushort InitLenth = (ushort)System.Text.Encoding.Default.GetByteCount(ColStruct.ColumnCaption);
        ushort MaxLenth = InitLenth;
        foreach (DataRow Row in dt.Rows)
        {
            object ObjValue = Row[ColStruct.ColumnName];
            if (ObjValue == null || ObjValue == DBNull.Value)
            {
                continue;
            }
            string ColumnValue = GetColumnValueFormat(dt, ColStruct, ObjValue);
            ushort ColumnValueLenth = (ushort)System.Text.Encoding.Default.GetByteCount(ColumnValue);
            MaxLenth = ColumnValueLenth > MaxLenth ? ColumnValueLenth : MaxLenth;
        }

        if (MaxLenth == InitLenth)
        {
            //标题栏字体较大
            MaxLenth += 4;
        }
        else
        {
            //内容文本前后与边框空余出一字的间隔
            MaxLenth += 2;
        }
        return (ushort)(MaxLenth * 1.2);
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="ds">要导出的数据集</param>
    /// <param name="columncaptions">要导出的列标题</param>
    /// <param name="columnnames">要导出的列名，用来与ds数据集中的列进行匹配</param>
    /// <param name="tableCaption">sheet名</param>
    public static void ExportDataToExcel(DataSet ds, string[] columncaptions, string[] columnnames, string tableCaption)
    {
        System.Collections.Generic.List<ExcelHelper.TableStruct> TableInfoAry = new System.Collections.Generic.List<ExcelHelper.TableStruct>();
        ExcelHelper.TableStruct TableInfo = new ExcelHelper.TableStruct();
        TableInfo.TableName = ds.Tables[0].TableName;
        TableInfo.TableCaption = tableCaption;
        if (columncaptions == null && columnnames == null)
        {
            foreach (DataColumn dc in ds.Tables[0].Columns)
            {
                ExcelHelper.ColumnStruct ColStruct = new ExcelHelper.ColumnStruct();
                ColStruct.ColumnName = dc.ColumnName;
                ColStruct.ColumnTextFormat = string.Empty;
                ColStruct.ColumnCaption = dc.Caption;
                TableInfo.AddColumnInfo(ColStruct);
            }
        }
        else if(columnnames.Length!=columncaptions.Length)
        {
            throw new ArgumentException("列名和列标题长度不一致");
        }
        else
        {
            for (int i = 0; i < columnnames.Length; i++)
            {
                ExcelHelper.ColumnStruct ColStruct = new ExcelHelper.ColumnStruct();
                ColStruct.ColumnName = columnnames[i];
                ColStruct.ColumnTextFormat = string.Empty;
                ColStruct.ColumnCaption = columncaptions[i];
                TableInfo.AddColumnInfo(ColStruct);
            }
        }
        TableInfoAry.Add(TableInfo);
        ExcelHelper.ExportToExcel(ds, TableInfoAry);
    }

    public static void ExportDataToExcel(DataSet ds, string tableCaption)
    {
        ExportDataToExcel(ds, null, null, tableCaption);
    }
}

这个HELPER主要就是使用public static void ExportDataToExcel(DataSet ds, string[] columncaptions, string[] columnnames, string tableCaption)这个方法。参数说明已经在方法上注明了。调用可以这样调用：

            string[] columncaptions = { "列1", "列2" };
            string[] columnsname = { "column1", "column2" };//注意这里的值要与 DataTable的列名一致
              DataSet ds2 = new DataSet();
            DataTable dt = new DataTable("test");
            DataColumn col = new DataColumn("column1");
            col.Caption = "我是列1";
            dt.Columns.Add(col);
            col = new DataColumn("column2");
            col.Caption = "我是列2";
            dt.Columns.Add(col);
            DataRow dr = dt.NewRow();
            dr[0] = "列1";
            dr[1] = "列2";
            dt.Rows.Add(dr);
            ds2.Tables.Add(dt);
            
            ExcelHelper.ExportDataToExcel(ds, columncaptions, columnsname, ds.Tables[0].TableName);

具体需求可以自己改动ExcelHelper的代码来实现，而不应该局限于我这种方式。