金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 一个简单的Json帮助类,将数据导出成Excel

一个简单的Json帮助类,将数据导出成Excel

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-29 12:42

金沙棋牌app手机下载 1

 

贴上一个简单的Json帮助类,代码如下:

示例:

使用ExcelHelper 封装类,将DataSet 输出成Excel文件的多个Sheet 页。

此类需添加如下引用

.net

 

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Runtime.Serialization.Json;
using System.Web.Script.Serialization;

DataSet ds=....

使用代码如下:


string xml = ds.GetXml();
xml = xml.Replace("'","''");

public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            btn.Click += new EventHandler(btn_Click);
            Button1.Click += new EventHandler(Button1_Click);
        }

public class JsonHelper
    {
        /// <summary>
        /// 实体类转Json
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="jsonString"></param>
        /// <returns></returns>
        public  T JsonStringToClass<T>(string jsonString)
        {
            using (var ms = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(jsonString)))
            { return (T)new DataContractJsonSerializer(typeof(T)).ReadObject(ms); }
        }
        /// <summary>
        /// Json转实体类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="jsonObject"></param>
        /// <returns></returns>
        public  string ClassToJsonString<T>(T jsonObject)
        {
            using (var ms = new MemoryStream())
            {
                new DataContractJsonSerializer(jsonObject.GetType()).WriteObject(ms, jsonObject);
                return System.Text.Encoding.UTF8.GetString(ms.ToArray());
            }
        }
        /// <summary>
        /// Json格式化
        /// </summary>
        /// <param name="json"></param>
        /// <returns></returns>
        public string JsonFormate(string json)
        {
            int Level = 0;
            var JsonArr = json.ToArray();
            string JsonTree = string.Empty;
            for(int i = 0; i < json.Length; i++)
            {
                char c = JsonArr[i];
                if (Level > 0 && 'n' == JsonTree.ToArray()[JsonTree.Length - 1])
                {
                    JsonTree += TreeLevel(Level);
                }
                switch (c)
                {
                    //case '{':
                    //    JsonTree +="rn"+c ;
                    //    break;
                    //case '}':
                    //    JsonTree += "rn"+c;
                        //break;
                    case '[':
                        JsonTree += c + "rn";
                        Level++;
                        break;
                    case ',':
                        JsonTree += c + "rn";
                        break;
                    case ']':
                        JsonTree += "rn";
                        Level--;
                        JsonTree += TreeLevel(Level);
                        JsonTree+=c;
                        break;
                    default:
                        JsonTree += c;
                        break;
                }
            }
            return JsonTree;
        }
        string TreeLevel(int level)
        {
            string leaf = string.Empty;
            for (int t = 0; t < level; t++)
            {
                leaf += "t";
            }
            return leaf;
        }
        /// <summary>
        /// Json文件格式化
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public string JsonFileFormatte(string filepath)
        {
            try
            {
                if (!File.Exists(filepath))
                {
                    return "传入文件不存在!";
                }
                string str = File.ReadAllText(filepath);
                 str = JsonFormate(str);
                File.WriteAllText(filepath, str);
                return "操作已成功!";
            }catch(Exception ex)
            {
                return ex.Message;
            }
        }
        /// <summary>
        /// Json转成实体类
        /// </summary>
        /// <param name="json"></param>
        /// <returns></returns>
        public dynamic ConvertJsonToDynamic(string json)
        {
            JavaScriptSerializer jss = new JavaScriptSerializer();
            jss.RegisterConverters(new JavaScriptConverter[] { new DynamicJsonConverter() });
            dynamic dy = jss.Deserialize(json, typeof(object)) as dynamic;
            return dy;

SQL :

        void Button1_Click(object sender, EventArgs e)
        {
            var ds = new DataSet();
            var dt = new DataTable("TableName For Sheet1");
            dt.Columns.Add("col1");
            dt.Columns.Add("col2");
            dt.Rows.Add("Value1", "Value2");

        }
    }

DECLARE @idoc int; EXEC sp_xml_preparedocument @idoc OUTPUT,
  N'<User>rn
 <Dept>rn   
  <ItemID>1</ItemID>金沙棋牌app手机下载,rn  
  <EmpID>123</EmpID>rn   
  <Type>0</Type>rn
</Dept>rn
 <Dept>rn   
    <ItemID>2</ItemID>    rn    
    <EmpID>123</EmpID>rn   
    <Type>1</Type>rn  
 </Dept>rn
</User>';
  INSERT INTO A (ItemID,EmpID,Type)
   SELECT ItemID,EmpID,Type FROM OPENXML (@idoc, '/User/Dept',2)
   WITH (ItemID int,EmpID nvarchar(50),Type int);

            var dt2 = new DataTable("TableName For Sheet2");
            dt2.Columns.Add("col1");
            dt2.Columns.Add("col2");
            dt2.Rows.Add("Value1", "Value2");
            ds.Tables.Add(dt);
            ds.Tables.Add(dt2);
            ExcelHelper.ToExcel(ds, "test.xls", Page.Response);           
        }

    class DynamicJsonConverter : JavaScriptConverter
    {
        public override object Deserialize(IDictionary<string, object> dictionary, Type type, JavaScriptSerializer serializer)
        {
            if (dictionary == null)
                throw new ArgumentNullException("dictionary");

 

        void btn_Click(object sender, EventArgs e)
        {
            var ds = new DataSet();
            DataTable dt = new DataTable();
            dt.Columns.Add("col");
            ds.Tables.Add(dt);
            ExcelHelper.ToExcel(ds, "test.xls", Page.Response);
        }      
    }

            if (type == typeof(object))
            {
                return new DynamicJsonObject(dictionary);
            }

ExcelHelper.cs 封装类,源代码如下:

            return null;
        }

//ExcelHelper.cs

        public override IDictionary<string, object> Serialize(object obj, JavaScriptSerializer serializer)
        {
            throw new NotImplementedException();
        }
        public override IEnumerable<Type> SupportedTypes
        {
            get { return new ReadOnlyCollection<Type>(new List<Type>(new Type[] { typeof(object) })); }
        }
    }
    class DynamicJsonObject : DynamicObject
    {
        private IDictionary<string, object> Dictionary { get; set; }

public class ExcelHelper
{
    //Row limits older Excel version per sheet
        const int rowLimit = 65000;

        public DynamicJsonObject(IDictionary<string, object> dictionary)
        {
            this.Dictionary = dictionary;
        }

        private static string getWorkbookTemplate()
        {
            var sb = new StringBuilder();
            sb.Append("<xml version>rn<Workbook
        xmlns="urn:schemas-microsoft-com:office:spreadsheet"rn");
            sb.Append(" xmlns:o="urn:schemas-microsoft-com:office:office"rn
        xmlns:x="urn:schemas- microsoft-com:office:excel"rn
        xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">rn");
            sb.Append(" <Styles>rn
        <Style ss:ID="Default" ss:Name="Normal">rn
        <Alignment ss:Vertical="Bottom"/>rn <Borders/>");
            sb.Append("rn <Font/>rn <Interior/>rn <NumberFormat/>rn
        <Protection/>rn </Style>rn
        <Style ss:ID="BoldColumn">rn <Font ");
            sb.Append("x:Family="Swiss" ss:Bold="1"/>rn </Style>rn
        <Style ss:ID="s62">rn <NumberFormat");
            sb.Append(" ss:Format="@"/>rn </Style>rn
        <Style ss:ID="Decimal">rn
        <NumberFormat ss:Format="0.0000"/>rn </Style>rn ");
            sb.Append("<Style ss:ID="Integer">rn
        <NumberFormat ss:Format="0"/>rn </Style>rn
        <Style ss:ID="DateLiteral">rn <NumberFormat ");
            sb.Append("ss:Format="mm/dd/yyyy;@"/>rn </Style>rn
        <Style ss:ID="s28">rn");
            sb.Append("<Alignment ss:Horizontal="Left" ss:Vertical="Top"
        ss:ReadingOrder="LeftToRight" ss:WrapText="1"/>rn");
            sb.Append("<Font x:CharSet="1" ss:Size="9"
        ss:Color="#808080" ss:Underline="Single"/>rn");
            sb.Append("<Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>

        public override bool TryGetMember(GetMemberBinder binder, out object result)
        {
            result = this.Dictionary[binder.Name];

        </Style>rn</Styles>rn {0}</Workbook>");
            return sb.ToString();
        }

            if (result is IDictionary<string, object>)
            {
                result = new DynamicJsonObject(result as IDictionary<string, object>);
            }
            else if (result is ArrayList && (result as ArrayList) is IDictionary<string, object>)
            {
                result = new List<DynamicJsonObject>((result as ArrayList).ToArray().Select(x => new DynamicJsonObject(x as IDictionary<string, object>)));
            }
            else if (result is ArrayList)
            {
                result = new List<object>((result as ArrayList).ToArray());
            }

        private static string replaceXmlChar(string input)
        {
            input = input.Replace("&", "&");
            input = input.Replace("<", "<");
            input = input.Replace(">", ">");
            input = input.Replace(""", """);
            input = input.Replace("'", "'");
            return input;
        }

            return this.Dictionary.ContainsKey(binder.Name);
        }
    }

        private static string getWorksheets(DataSet source)
        {
            var sw = new StringWriter();
            if (source == null || source.Tables.Count == 0)
            {
                sw.Write("<Worksheet ss:Name="Sheet1"><Table><Row>

        <Cell  ss:StyleID="s62"><Data ss:Type="String"></Data>
        </Cell></Row></Table></Worksheet>");
                return sw.ToString();
            }
            foreach (DataTable dt in source.Tables)
            {
                if (dt.Rows.Count == 0)
                    sw.Write("<Worksheet ss:Name="" + replaceXmlChar(dt.TableName) +
            ""><Table><Row><Cell  ss:StyleID="s62">

            <Data ss:Type="String"></Data></Cell></Row>
            </Table></Worksheet>");
                else
                {
                    //write each row data
                    var sheetCount = 0;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if ((i % rowLimit) == 0)
                        {
                            //add close tags for previous sheet of the same data table
                            if ((i / rowLimit) > sheetCount)
                            {
                                sw.Write("</Table></Worksheet>");
                                sheetCount = (i / rowLimit);
                            }
                            sw.Write("<Worksheet ss:Name="" +
                replaceXmlChar(dt.TableName) +
                                     (((i / rowLimit) == 0) ? "" :
                Convert.ToString(i / rowLimit)) + ""><Table>");
                            //write column name row
                            sw.Write("<Row>");
                            foreach (DataColumn dc in dt.Columns)
                                sw.Write(
                                    string.Format(
                                        "<Cell ss:StyleID="BoldColumn">

                <Data ss:Type="String">{0}</Data></Cell>",
                                        replaceXmlChar(dc.ColumnName)));
                            sw.Write("</Row>rn");
                        }
                        sw.Write("<Row>rn");
                        foreach (DataColumn dc in dt.Columns)
                            sw.Write(
                                string.Format(
                                    "<Cell ss:StyleID="s62"><Data ss:Type="String">

                    {0}</Data></Cell>",
                                            replaceXmlChar
                    (dt.Rows[i][dc.ColumnName].ToString())));
                        sw.Write("</Row>rn");
                    }
                    sw.Write("</Table></Worksheet>");
                }
            }

            return sw.ToString();
        }
        public static string GetExcelXml(DataTable dtInput, string filename)
        {
            var excelTemplate = getWorkbookTemplate();
            var ds = new DataSet();
            ds.Tables.Add(dtInput.Copy());
            var worksheets = getWorksheets(ds);
            var excelXml = string.Format(excelTemplate, worksheets);
            return excelXml;
        }

        public static string GetExcelXml(DataSet dsInput, string filename)
        {
            var excelTemplate = getWorkbookTemplate();
            var worksheets = getWorksheets(dsInput);
            var excelXml = string.Format(excelTemplate, worksheets);
            return excelXml;
        }

        public static void ToExcel
        (DataSet dsInput, string filename, HttpResponse response)
        {
            var excelXml = GetExcelXml(dsInput, filename);
            response.Clear();
            response.AppendHeader("Content-Type", "application/vnd.ms-excel");
            response.AppendHeader
        ("Content-disposition", "attachment; filename=" + filename);
            response.Write(excelXml);
            response.Flush();
            response.End();
        }

        public static void ToExcel
        (DataTable dtInput, string filename, HttpResponse response)
        {
            var ds = new DataSet();
            ds.Tables.Add(dtInput.Copy());
            ToExcel(ds, filename, response);
        }
    }

来源:

Demo下载

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:一个简单的Json帮助类,将数据导出成Excel

关键词:

上一篇:没有了

下一篇:没有了