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

最新推荐文章于 2022-09-21 23:05:26 发布

.NET跨平台

最新推荐文章于 2022-09-21 23:05:26 发布

阅读量531

点赞数

分类专栏： NPOI.NET和EPPlus框架投资理财

原文链接：https://blog.csdn.net/WuLex

版权

投资理财同时被 2 个专栏收录

37 篇文章 14 订阅

订阅专栏

NPOI.NET和EPPlus框架

27 篇文章 15 订阅

订阅专栏

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; }
    }
}