NPOI读取Excel生成DataTable转为Json后利用LitJons读取

NPOI：http://npoi.codeplex.com/

LitJson：https://code.google.com/p/litjsonmd/downloads/list

using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using System.Text;

/// <summary>
/// 获取json字符串的类
/// </summary>
public class GetJson
{
    /// <summary>
    /// 得到DataTable的方法
    /// </summary>
    /// <param name="filePath">传入Excel所在的路径</param>
    /// <param name="tableName">传入Table的name</param>
    /// <returns></returns>
    public static DataTable GetTable(string filePath, string tableName)
    {
        DataTable table = new DataTable(tableName);
        if (File.Exists(filePath))
        {
            using (FileStream fStream = File.OpenRead(filePath))
            {
                IWorkbook workBook = WorkbookFactory.Create(fStream);
                ISheet sheet1 = workBook.GetSheetAt(0);
                IRow headRow = sheet1.GetRow(0);
                int cellCount = headRow.LastCellNum;
                for (int m = headRow.FirstCellNum; m < cellCount; m++)
                {
                    string headCellValue = headRow.GetCell(m).StringCellValue;
                    table.Columns.Add(headCellValue);
                }
                int rowCount = sheet1.LastRowNum;
                for (int i = (sheet1.FirstRowNum + 1); i < rowCount + 1; i++)
                {
                    DataRow dataRow = table.NewRow();
                    IRow row = sheet1.GetRow(i);
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                            dataRow[j] = row.GetCell(j).ToString();
                    }
                    table.Rows.Add(dataRow);
                }
            }
        }
        return table;
    }
    /// <summary>
    /// 得到json字符串的类
    /// </summary>
    /// <param name="dt">传入要生成json字符串的Table</param>
    /// <returns></returns>
    public static string GetJsonStr(DataTable dt)
    {
        StringBuilder jsonBuilder = new StringBuilder();
        int count = dt.Rows.Count;
        if (count != 0)
        {
            jsonBuilder.Append("{");
            jsonBuilder.Append("\'");
            //for (int j = 0; j < dt.Columns.Count; j++)
            //{
            //    jsonBuilder.Append(dt.Rows[i][0].ToString());
            //    break;
            //}
            jsonBuilder.Append(dt.TableName);
            jsonBuilder.Append("\'");
            jsonBuilder.Append(":");
            jsonBuilder.Append("[");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //jsonBuilder.Append("\'");
                //for (int j = 0; j < dt.Columns.Count; j++)
                //{
                //    jsonBuilder.Append(dt.Rows[i][0].ToString());
                //    break;
                //}
                //jsonBuilder.Append("\'");
                //jsonBuilder.Append(":");
                jsonBuilder.Append("{");
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    jsonBuilder.Append("");
                    jsonBuilder.Append("\'");
                    jsonBuilder.Append(dt.Columns[j].ColumnName);
                    jsonBuilder.Append("\'");
                    jsonBuilder.Append(":");

                    if (!IsNum(dt.Rows[i][j].ToString()))
                    {
                        jsonBuilder.Append("\'" + dt.Rows[i][j].ToString() + "\'");
                    }
                    else
                    {
                        jsonBuilder.Append(dt.Rows[i][j].ToString());
                    }

                    jsonBuilder.Append(",");
                }
                jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
                jsonBuilder.Append("},");
            }
            jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
            jsonBuilder.Append("]");
            jsonBuilder.Append("}");
            return jsonBuilder.ToString();
        }
        else
        {
            return null;
        }
    }
    /// <summary>
    /// 判断是否为数字
    /// </summary>
    /// <param name="number">要判断的string字符串</param>
    /// <returns>true/false</returns>
    private static bool IsNum(string number)
    {
        try
        {

            for (int i = 0; i < number.Length; i++)
            {
                if (!char.IsNumber(number, i))
                {
                    return false;
                }
            }
            return true;
        }
        catch
        {
            return false;
        }
    }
}

 

转载于:https://www.cnblogs.com/HelloUnity/p/NPOI%e8%af%bb%e5%8f%96Excel%e7%94%9f%e6%88%90DataTable%e8%bd%ac%e4%b8%baJson%e5%90%8e%e5%88%a9%e7%94%a8LitJons%e8%af%bb%e5%8f%96.html