.NET实现Json(天天基金经理数据)转Excel

Love The Way You Lie 2021-07-04 16:00 713阅读 0赞

## Program.cs ##

using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Net;
    using System.Text;
    using System.Threading.Tasks;
    using Newtonsoft.Json;
    using OfficeOpenXml;
    
    namespace JsonToExel
    { 
        class Program
        { 
            static void Main(string[] args)
            { 
                string result = "";
                string url =
                    "http://fund.eastmoney.com/Data/FundDataPortfolio_Interface.aspx?dt=14&mc=returnjson&ft=all&pn=5000&pi=1&sc=abbname&st=asc";
                HttpWebRequest request = (HttpWebRequest) WebRequest.Create(url);
                request.Method = "GET";
                request.ContentType = "application/json;charset=UTF-8";
    
                var httpResponse = (HttpWebResponse) request.GetResponse();
                using (var dataStream = new StreamReader(httpResponse.GetResponseStream()))
                { 
                    result = dataStream.ReadToEnd().Replace("var returnjson= ", "");
                }
    
                JsonStrToXlsx(result, "DataStore/fundmanager.xlsx");
    
                Console.WriteLine("保存成功!");
                Console.ReadKey();
            }
    
            static int type_row = 2;
            static int header_row = 3;
            static int row = 4;
            static int col = 1;
            static int max_col = 1;
            static int key_index = 0;
    
            static void JsonStrToXlsx(string json_str, string xlsx_file)
            { 
                row = header_row + 1;
                col = 1;
                max_col = 1;
                FundManagerList fmList = JsonConvert.DeserializeObject<FundManagerList>(json_str);
                List<FundManager> fManagers = new List<FundManager>();
                FundManager fmEntity = null;
                foreach (var item in fmList.data)
                { 
                    fmEntity = new FundManager()
                    { 
                        Id = item[0],
                        ManagerName = item[1],
                        CompanyId = item[2],
                        Company = item[3],
                        Code = item[4],
                        FundName = item[5],
                        Totaldays = item[6],
                        BestProfit = item[7],
                        BestFundCode = item[8],
                        BestFundName = item[9],
                        TotalMoney = item[10]
                    };
                    fManagers.Add(fmEntity);
                }
    
                if (File.Exists(xlsx_file))
                { 
                    File.Delete(xlsx_file);
                }
    
                using (var excel = new ExcelPackage(new FileInfo(xlsx_file)))
                { 
                    var ws = excel.Workbook.Worksheets.Add(Path.GetFileNameWithoutExtension(xlsx_file));
    
                    //int fromRow, int fromCol, int toRow, int toCol
                    //ws.Cells[1, 1].Value = "";
    
                    foreach (FundManager item in fManagers)
                    { 
                        col = 1;
                        ws.Cells[row, col++].Value = item.Id;
                        ws.Cells[row, col++].Value = item.ManagerName;
                        ws.Cells[row, col++].Value = item.CompanyId;
                        ws.Cells[row, col++].Value = item.Company;
                        ws.Cells[row, col++].Value = item.Code;
                        ws.Cells[row, col++].Value = item.FundName;
                        ws.Cells[row, col++].Value = item.Totaldays;
                        ws.Cells[row, col++].Value = item.BestProfit;
                        ws.Cells[row, col++].Value = item.BestFundCode;
                        ws.Cells[row, col++].Value = item.BestFundName;
                        ws.Cells[row, col++].Value = item.TotalMoney;
                        row++;
                        max_col = Math.Max(col, max_col);
                    }
    
                    for (int i = 1; i <= max_col; i++)
                    { 
                        if (ws.Column(i) != null)
                            ws.Column(i).AutoFit();
                    }
    
                    //ws.Tables.Add(new ExcelAddress(header_row, 1, row - 1, max_col - 1), "文本");
                    excel.Save();
                }
            }
        }
    }

## FundManagerList.cs ##

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace JsonToExel
    { 
        public class FundManagerList
        { 
            /// <summary>
            /// 
            /// </summary>
            public List<List<string>> data {  get; set; }
    
            /// <summary>
            /// 
            /// </summary>
            public int record {  get; set; }
    
            /// <summary>
            /// 
            /// </summary>
            public int pages {  get; set; }
    
            /// <summary>
            /// 
            /// </summary>
            public int curpage {  get; set; }
        }
    }

## FundManager.cs ##

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace JsonToExel
    { 
        public class FundManager
        { 
            /// <summary>
            /// 经理ID
            /// </summary>
            public string Id {  get; set; }
    
            /// <summary>
            /// 姓名
            /// </summary>
            public string ManagerName {  get; set; }
    
            /// <summary>
            /// 公司ID
            /// </summary>
            public string CompanyId {  get; set; }
    
            /// <summary>
            /// 所属公司
            /// </summary>
            public string Company {  get; set; }
    
            /// <summary>
            /// 基金代码
            /// </summary>
            public string Code {  get; set; }
    
            /// <summary>
            /// 基金名称
            /// </summary>
            public string FundName {  get; set; }
    
            /// <summary>
            /// 累计从业时间
            /// </summary>
            public string Totaldays {  get; set; }
    
            /// <summary>
            /// 现任基金最佳回报
            /// </summary>
            public string BestProfit {  get; set; }
    
            public string BestFundCode {  get; internal set; }
            public string BestFundName {  get; set; }
            public string TotalMoney {  get; set; }
        }
    }

## 如图： ##

![在这里插入图片描述][20200922135409625.png]  
[代码下载][Link 1]

[20200922135409625.png]: /images/20210704/5dc495033b624d289d5616654c4de903.png
[Link 1]: https://download.csdn.net/download/WuLex/12880051