Excel导出代码,Asp.NET代码库,德仔网

代码语言
代码分类
【Asp.NET】 Excel导出
作者:雅竹 / 发布于2017/11/10/ 919
Excel导出
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Reflection;
using System.Data;
using Aspose.Cells;
using System.Text.RegularExpressions;
using System.IO;
using System.Web.Script.Serialization;

namespace PublicBase.Common
{
    public class CommonFunction
    {
        /// <summary>
        /// excel导出
        /// </summary>
        /// <param name="list">需要导出的对象集合</param>
        /// <param name="filename">导出文件名</param>
        /// <returns></returns>
        public static Stream ExportToExcel<T>(List<T> list, string filename, Func<PropertyInfo, bool> func)
        where T : class,new()
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                System.IO.StringWriter tw = new System.IO.StringWriter();
                sb.Append("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
                sb.Append("<head>");
                sb.Append("<style>");
                //设置左右边距，页面打印居中。
                sb.Append("@page{mso-footer-data:\"&C&9第 &P 页，共 &N 页\";margin:.50in .15in .50in .15in;mso-horizontal-page-align:center;");
                //如果是横向打印。
                sb.Append("}");
                sb.Append("</style>");
                sb.Append("<xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:WorksheetOptions><x:Print>");
                sb.Append("<x:ValidPrinterInfo/>");
                sb.Append("</x:Print></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets> </x:ExcelWorkbook></xml>");
                sb.Append("</head>");
                sb.Append("<body rightmargin='0' leftmargin='0'>");
                sb.Append("<form id='form1' runat='server'>");
                int i = 0;
                sb.Append("<table width='100%'>");

                //导入表头
                string rowspan = "1";
                string colspan = "1";
                sb.Append("<tr>");
                int columnsCount = 0;
                var infos = typeof(T).GetProperties();
                infos = infos.Where(item => Attribute.IsDefined(item, typeof(DisplayNameAttribute))).Where(func).ToArray();
                columnsCount = infos.Length;
                sb.Append("<td style='text-align: center;font-weight: bold;border:1px solid #000000;' colspan='" + columnsCount + "' rowspan='" + rowspan + "'>");
                sb.Append(filename);
                sb.Append("</td>");
                sb.Append("</tr>");
                sb.Append("<tr>");
                for (i = 0; i < infos.Length; i++)
                {
                    string fieldName = ((DisplayNameAttribute)Attribute.GetCustomAttribute(infos[i], typeof(DisplayNameAttribute))).DisplayName;
                    sb.Append("<td style='text-align: center;border:1px solid #000000;' colspan='" + colspan + "' rowspan='" + rowspan + "'>");
                    sb.Append(fieldName);
                    sb.Append("</td>");
                }
                sb.Append("</tr>");

                //导入数据
                int j = 0;
                for (i = 0; i < list.Count; i++)
                {
                    sb.Append("<tr>");
                    for (j = 0; j < infos.Length; j++)
                    {
                        string fieldValue = "";
                        if (infos[j].GetValue(list[i], null) != null)
                        {
                            fieldValue = infos[j].GetValue(list[i], null).ToString();
                        }
                        sb.Append("<td style='border:1px solid #000000;' rowspan='" + rowspan + "' align='left'>");
                        sb.Append(fieldValue);
                        sb.Append("</td>");
                    }
                    sb.Append("</tr>");

                }

                sb.Append("</table>");
                sb.Append("</form>");
                sb.Append("</body></html>");

                System.IO.MemoryStream ms = new System.IO.MemoryStream(Encoding.UTF8.GetBytes(sb.ToString()));
                return ms;
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }

        public static void ThrowAppException(string msg)
        {
            throw new Exception(msg);
        }

        public static void ThrowAppException(Exception exp)
        {
            throw new Exception(exp.Message);
        }

        public static DataTable ExcelToDataTable(Stream stream)
        {
            try
            {
                DataTable tb = new DataTable();
                Workbook wb = new Workbook();
                wb.Open(stream);
                var wsCells = wb.Worksheets[0].Cells;
                tb = wsCells.ExportDataTable(0, wsCells.MinColumn, wsCells.MaxDataRow + 1, wsCells.MaxColumn + 1);
                tb.Rows.RemoveAt(0);
                return tb;
            }
            catch
            {
                throw new Exception("您使用的模板格式不正确，请下载使用正确的模板！");
            }
        }

        /// <summary>   
        /// 根据Json返回DateTable,JSON数据格式如:   
        /// {table:[{column1:1,column2:2,column3:3},{column1:1,column2:2,column3:3}]}   
        /// </summary>   
        /// <param name="strJson">Json字符串</param>   
        /// <returns></returns>   
        public static DataTable JsonToDataTable(string strJson)
        {
            //取出表名   
            var rg = new Regex(@"(?<={)[^:]+(?=:\[)", RegexOptions.IgnoreCase);
            string strName = rg.Match(strJson).Value;
            DataTable tb = null;
            //去除表名   
            strJson = strJson.Substring(strJson.IndexOf("[") + 1);
            strJson = strJson.Substring(0, strJson.IndexOf("]"));

            //获取数据   
            rg = new Regex(@"(?<={)[^}]+(?=})");
            MatchCollection mc = rg.Matches(strJson);
            for (int i = 0; i < mc.Count; i++)
            {
                string strRow = mc[i].Value;
                string[] strRows = strRow.Split(',');

                //创建表   
                if (tb == null)
                {
                    tb = new DataTable();
                    tb.TableName = strName;
                    foreach (string str in strRows)
                    {
                        var dc = new DataColumn();
                        string[] strCell = str.Split(':');
                        dc.ColumnName = strCell[0];
                        tb.Columns.Add(dc);
                    }
                    tb.AcceptChanges();
                }

                //增加内容   
                DataRow dr = tb.NewRow();
                for (int r = 0; r < strRows.Length; r++)
                {
                    dr[r] = strRows[r].Split(':')[1].Trim().Replace("，", ",").Replace("：", ":").Replace("\"", "");
                }
                tb.Rows.Add(dr);
                tb.AcceptChanges();
            }
            return tb;
        }


        /// <summary> 
        /// Json格式转换成键值对，键值对中的Key需要区分大小写 
        /// </summary> 
        /// <param name="JsonData">需要转换的Json文本数据</param> 
        /// <returns></returns> 
        public static Dictionary<string, object> JsonToDictionary(string JsonData)
        {
            object Data = null;
            Dictionary<string, object> Dic = new Dictionary<string, object>();
            if (JsonData.StartsWith("["))
            {
                //如果目标直接就为数组类型，则将会直接输出一个Key为List的List<Dictionary<string, object>>集合 
                //使用示例List<Dictionary<string, object>> ListDic = (List<Dictionary<string, object>>)Dic["List"]; 
                List<Dictionary<string, object>> List = new List<Dictionary<string, object>>();
                MatchCollection ListMatch = Regex.Matches(JsonData, @"{[\s\S]+?}");//使用正则表达式匹配出JSON数组 
                foreach (Match ListItem in ListMatch)
                {
                    List.Add(JsonToDictionary(ListItem.ToString()));//递归调用 
                }
                Data = List;
                Dic.Add("List", Data);
            }
            else
            {
                MatchCollection Match = Regex.Matches(JsonData, @"""(.+?)"": {0,1}(\[[\s\S]+?\]|null|"".+?""|-{0,1}\d*)");//使用正则表达式匹配出JSON数据中的键与值 
                foreach (Match item in Match)
                {
                    try
                    {
                        if (item.Groups[2].ToString().StartsWith("["))
                        {
                            //如果目标是数组，将会输出一个Key为当前Json的List<Dictionary<string, object>>集合 
                            //使用示例List<Dictionary<string, object>> ListDic = (List<Dictionary<string, object>>)Dic["Json中的Key"]; 
                            List<Dictionary<string, object>> List = new List<Dictionary<string, object>>();
                            MatchCollection ListMatch = Regex.Matches(item.Groups[2].ToString(), @"{[\s\S]+?}");//使用正则表达式匹配出JSON数组 
                            foreach (Match ListItem in ListMatch)
                            {
                                List.Add(JsonToDictionary(ListItem.ToString()));//递归调用 
                            }
                            Data = List;
                        }
                        else if (item.Groups[2].ToString().ToLower() == "null") Data = null;//如果数据为null(字符串类型),直接转换成null 
                        else Data = item.Groups[2].ToString(); //数据为数字、字符串中的一类，直接写入 
                        Dic.Add(item.Groups[1].ToString(), Data);
                    }
                    catch { }
                }
            }
            return Dic;
        }


        /// <summary> 
        /// 对象转JSON 
        /// </summary> 
        /// <param name="obj">对象</param> 
        /// <returns>JSON格式的字符串</returns> 
        public static string ObjectToJson(object obj)
        {
            JavaScriptSerializer jss = new JavaScriptSerializer();
            try
            {
                return jss.Serialize(obj);
            }
            catch (Exception ex)
            {

                throw new Exception("JSONHelper.ObjectToJSON(): " + ex.Message);
            }
        }
    }

}
试试其它关键字
同语言下
可能有用的
雅竹贡献的其它代码(14)