2019年2月 – 我的部落格

Excel数据转Json

var size = Request.Form.Files.Sum(f => f.Length);
IFormFile file = Request.Form.Files.FirstOrDefault();
if (file != null && file.Length > 0)
{
    #region 先将Excel数据转为json,再将json转为对象列表,再PostCollection
    OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage(file.OpenReadStream());
    OfficeOpenXml.ExcelWorksheet worksheet = pck.Workbook.Worksheets[0];
    //获取worksheet的行数
    int rows = worksheet.Dimension.End.Row;
    //获取worksheet的列数
    int cols = worksheet.Dimension.End.Column;

    //1,第9行固定为标题
    List<string> jsonKVList = new List<string>();//json对象键值对字符串表示
    List<string> jsonStringList = new List<string>();//json对象字符串表示
    List<string> jsonArrayList = new List<string>();//json数组字符串表示
    string jsonTemplate = @"'{0}':'{1}'";

    //最终数据格式:[{key1:value1,key2:value2,key3:value3.....}]
    string jkey = string.Empty, jvalue = string.Empty, jsonStr = string.Empty, tempJsonStr = string.Empty;
    for (int i = 12; i <= rows; i++)
    {
        //导入数据id由系统生成
        jsonKVList.Add($"'id':'{Guid.NewGuid().ToString("N")}'");
        for (int j = 1; j <= cols; j++)
        {
            try
            {
                jkey = worksheet.Cells[11, j].Value.ToString();
                jvalue = worksheet.Cells[i, j].Value?.ToString();
                jvalue = ReplaceString(jvalue);//特殊字符必须转移转义
                jsonStr = string.Format(jsonTemplate, jkey, jvalue);
                jsonKVList.Add(jsonStr);
            }
            catch (Exception)
            {
                _logger.LogError($"ClientBase ImportData :第{i}行{j}列有误!");
                return BadRequest($"第{i}行{j}列有误!");
            }
        }
        tempJsonStr = "{" + string.Join(",", jsonKVList.ToArray()) + "}";
        jsonStringList.Add(tempJsonStr);
    }
    string targetJsonArrayStr = $"[{ string.Join(",", jsonStringList.ToArray())}]";
    List<T> taregetEntityListData = Newtonsoft.Json.JsonConvert.DeserializeObject<List<T>>(targetJsonArrayStr, new JsonSerializerSettings() { NullValueHandling = NullValueHandling.Ignore });

    var path = $"/api/{_currentController}/Collection";
    _logger.LogError($"ClientBase ImportData PostCollection param:{path},{targetJsonArrayStr}");
    var resp = await _cusservice.PostCollection(path, taregetEntityListData);
    var badResult = CheckResponseMessge(resp); if (badResult != null) { return badResult; }
    return Ok(new { success = true, code = 200, message = "success" });
    #endregion

var size = Request.Form.Files.Sum(f => f.Length);

IFormFile file = Request.Form.Files.FirstOrDefault();

if (file != null && file.Length > 0)

{

#region 先将Excel数据转为json,再将json转为对象列表,再PostCollection

OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage(file.OpenReadStream());

OfficeOpenXml.ExcelWorksheet worksheet = pck.Workbook.Worksheets[0];

//获取worksheet的行数

int rows = worksheet.Dimension.End.Row;

//获取worksheet的列数

int cols = worksheet.Dimension.End.Column;

//1,第9行固定为标题

List<string> jsonKVList = new List<string>();//json对象键值对字符串表示

List<string> jsonStringList = new List<string>();//json对象字符串表示

List<string> jsonArrayList = new List<string>();//json数组字符串表示

string jsonTemplate = @"'{0}':'{1}'";

//最终数据格式:[{key1:value1,key2:value2,key3:value3.....}]

string jkey = string.Empty, jvalue = string.Empty, jsonStr = string.Empty, tempJsonStr = string.Empty;

for (int i = 12; i <= rows; i++)

{

//导入数据id由系统生成

jsonKVList.Add($"'id':'{Guid.NewGuid().ToString("N")}'");

for (int j = 1; j <= cols; j++)

{

try

{

jkey = worksheet.Cells[11, j].Value.ToString();

jvalue = worksheet.Cells[i, j].Value?.ToString();

jvalue = ReplaceString(jvalue);//特殊字符必须转移转义

jsonStr = string.Format(jsonTemplate, jkey, jvalue);

jsonKVList.Add(jsonStr);

}

catch (Exception)

{

_logger.LogError($"ClientBase ImportData :第{i}行{j}列有误!");

return BadRequest($"第{i}行{j}列有误!");

}

tempJsonStr = "{" + string.Join(",", jsonKVList.ToArray()) + "}";

jsonStringList.Add(tempJsonStr);

}

string targetJsonArrayStr = $"[{ string.Join(",", jsonStringList.ToArray())}]";

List<T> taregetEntityListData = Newtonsoft.Json.JsonConvert.DeserializeObject<List<T>>(targetJsonArrayStr, new JsonSerializerSettings() { NullValueHandling = NullValueHandling.Ignore });

var path = $"/api/{_currentController}/Collection";

_logger.LogError($"ClientBase ImportData PostCollection param:{path},{targetJsonArrayStr}");

var resp = await _cusservice.PostCollection(path, taregetEntityListData);

var badResult = CheckResponseMessge(resp); if (badResult != null) { return badResult; }

return Ok(new { success = true, code = 200, message = "success" });

#endregion

字符串转json, […]

每月存档：2019年二月

Word通配符替换注释行

.Net Core Excel数据转Json — EPPlus

在ubuntu中搜索文件或文件夹的方法

Telerik Grid动态类型绑定实现–.Net Core