Json转换为DataTable Excel转换为DataTable

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Newtonsoft.Json.Linq;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;

namespace WindowsFormsApplication1
{
    class DataConvert
    {
        /// <summary>
        /// Json转换为DataTable
        /// </summary>
        /// <param name="jsontext">输入内容，格式：[{'a':'aaa','b':'bbb','c':'ccc'},{'a':'aa','b':'bb,'c':'cc'}]</param>
        /// <param name="msg"></param>
        /// <param name="dt">输入DataTable</param>
        /// <returns></returns>
        public static bool JsonToDataTable(string jsontext, ref string msg, out DataTable dt)
        {
            dt = null;
            try
            {
                JArray arr = JArray.Parse(jsontext);
                if (arr.Count > 0)
                {
                    dt = new DataTable();

                    //创建DataColumn
                    JToken tk = arr[0];
                    foreach (JProperty p in tk.Children())
                    {
                        dt.Columns.Add(p.Name, System.Type.GetType("System.String"));
                        //dt.Columns.Add(new DataColumn(p.Name, typeof(string)));
                        //dt.Columns.Add(new DataColumn(p.Name, typeof(string)) { DefaultValue = "0" });
                    }

                    foreach (JToken ts in arr)
                    {
                        List<object> objs = new List<object>();
                        foreach (JProperty p in ts.Children())
                            objs.Add(p.Value);
                        dt.Rows.Add(objs.ToArray());
                    }
                }
                return true;
            }
            catch (Exception e)
            {
                msg = e.Message;
                return false;
            }
        }



        /// <summary>
        /// Excel转换为DataTable
        /// </summary>
        /// <param name="fileName">xls文件名称</param>
        /// <param name="columnrowindex">列标题行的索引</param>
        /// <param name="dt">导出的DataTable</param>
        /// <param name="titles">必须要有的标题列</param>
        /// <returns></returns>
        public static bool ConvertToDataTable(string fileName, int columnrowindex, out DataTable dt, ref string msg, List<string> titles = null)
        {
            dt = null;
            try
            {
                IWorkbook workbook = null;  //新建IWorkbook对象
                FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook(fileStream);  //xlsx数据读入workbook
                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook(fileStream);  //xls数据读入workbook
                }

                ISheet sheet = workbook.GetSheetAt(0);  //获取第一个工作表

                IRow row = sheet.GetRow(columnrowindex);
                List<string> TitleStr = new List<string>();
                for (int i = 0; i < row.LastCellNum; i++)
                    TitleStr.Add(row.GetCell(i).ToString());

                #region 模板校验
                if (titles != null)
                {
                    bool right = true;
                    foreach (string t in titles)
                    {
                        if (TitleStr.IndexOf(t) < 0)
                        {
                            right = false;
                            break;
                        }
                    }
                    if (right == false)
                    {
                        msg = "导入文件不是标准模板";
                        return false;
                    }
                }
                #endregion

                dt = new DataTable();
                foreach (string t in TitleStr)
                {
                    dt.Columns.Add(t, typeof(string));
                }

                for (int i = columnrowindex + 1; i < 65535; i++)  //对工作表每一行
                {
                    row = sheet.GetRow(i);   //row读入第i行数据
                    if (row != null)
                    {
                        List<object> obj = new List<object>();
                        for (int j = 0; j < row.LastCellNum; j++)
                            obj.Add(row.Cells[j].ToString());
                        dt.Rows.Add(obj.ToArray());
                    }
                    else
                        break;
                }
                fileStream.Close();
                return true;
            }
            catch(Exception e)
            {
                msg = e.Message;
                return false;
            }
        }
    }
}

        private void button3_Click(object sender, EventArgs e)
        {
            string text = "[{\"name\":\"12345\",\"pwd\":\"asdfgh\",\"xh\":15}]";
            //JObject obj = JObject.Parse(text);
            //foreach (JProperty p in obj.Children())
            //{
            //    string t = p.Name + ":" + p.Value.ToString();
            //}

            string msg="";
            DataTable dt;
            if (!DataConvert.JsonToDataTable(text, ref msg, out dt))
            {
                MessageBox.Show(msg);
                return;
            }
            MessageBox.Show(msg);
        }