首页 新闻 赞助 找找看

我想用C#从Json字符串中提取数据存入Excel表格中并插入图表,但是提取到Excel单元格的数据格式不对,导致无法插入图表。请问代码哪里有错吗?

0
悬赏园豆:50 [已解决问题] 解决于 2017-05-11 16:40

这是Json,我想提取的是那两个data数据
string jsonText = @"{
color: ['#3398DB'],
tooltip : {
trigger: 'axis',
axisPointer : {
type : 'shadow'
}
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
xAxis : [
{
type : 'category',
data : ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'],
axisTick: {
alignWithLabel: true
}
}
],
yAxis : [
{
type : 'value'
}
],
series : [
{
name:'直接访问',
type:'bar',
barWidth: '60%',
data:[10, 52, 200, 334, 390, 330, 220]
}
]
}";

代码如下

JObject jo = (JObject)JsonConvert.DeserializeObject(jsonText);
string series = jo["series"].ToString();
string xAxis = jo["xAxis"].ToString();
JArray jar = JArray.Parse(jo["series"].ToString());
JArray jar2 = JArray.Parse(jo["xAxis"].ToString());
string xData = jar2[0]["data"].ToString().Remove(0, 1).Replace("]", "").Replace(""", "");
string seData = jar[0]["data"].ToString().Remove(0, 1).Replace("]", "").Replace(" ", "");

        string[] data1 = seData.Split(',');
        string[] data2 = xData.Split(',');

        //创建Excel
        Microsoft.Office.Interop.Excel._Application excel;
        Microsoft.Office.Interop.Excel.Workbook book;
        Microsoft.Office.Interop.Excel.Worksheet sheet1;
        Microsoft.Office.Interop.Excel.Worksheet sheet2;
        object misValue = System.Reflection.Missing.Value;
        excel = new Microsoft.Office.Interop.Excel.Application();
        book = excel.Workbooks.Add(misValue);
        sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets.get_Item(1);
        sheet2 = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets.get_Item(2);
        sheet1.Name = "data";
        sheet2.Name = "chart";
        //导入数据
        for (int i = 0; i < data1.Length; i++)
        {
            sheet1.Cells[i + 2, 1] = data2[i];
            Console.WriteLine(data2[i]);
            sheet1.Cells[i + 2, 2] = data1[i];
            Console.WriteLine(data1[1]);
        }

        book.SaveAs(@"C:\Users\SJ001\Desktop\sd.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
             Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        excel.Quit();

这是生成的Excel表格,单元格里面有很多空格,导致不能插入图表。想知道哪里不对。。

St4ndAlone的主页 St4ndAlone | 初学一级 | 园豆:151
提问于:2017-05-04 13:51
< >
分享
最佳答案
0

CD列是空格还是?不明白你表达的意思。代码没有什么问题。

收获园豆:50
盟怀部孩 | 小虾三级 |园豆:878 | 2017-05-11 14:02

我的意思是A B两列单元格内有空格或回车,想把它们去掉。不过我现在用另一种Json解析的方式解决了。

St4ndAlone | 园豆:151 (初学一级) | 2017-05-11 16:16

@DANtheMAN: 哦,你在处理data这一节的时候,并没有解析。而是用了字符串分割的方式

其实你可以在下面这样写就解决了。


sheet1.Cells[i + 2, 1] = data2[i].Regex.Replace( text, @"\s", "" );

Console.WriteLine(data2[i]);

sheet1.Cells[i + 2, 2] =data1[i].Regex.Replace( text, @"\s", "" );

Console.WriteLine(data1[1]);

 

 

盟怀部孩 | 园豆:878 (小虾三级) | 2017-05-11 16:23

@盟怀部孩: 嗯嗯,好的。谢谢啦!

St4ndAlone | 园豆:151 (初学一级) | 2017-05-11 16:35
其他回答(1)
0

我的解决方式,换了一种Json解析方法,
定义类
public class option
{
public title title { get; set; }
public string[] color { get; set; }
public tooltip tooltip { get; set; }
public legend legend { get; set; }
public grid grid { get; set; }
public xAxis[] xAxis { get; set; }
public yAxis[] yAxis { set; get; }
public series[] series { get; set; }
}
public class title
{
public string text { get; set; }
public string subtext { get; set; }
}
public class tooltip
{
public string trigger { get; set; }
public axisPointer axispointer { get; set; }
}
public class axisPointer
{
public string type { get; set; }
}
public class legend
{
public string[] data { get; set; }
}
public class grid
{
public string left { get; set; }
public string right { get; set; }
public string bottom { get; set; }
public Boolean containLabel { get; set; }
}
public class xAxis
{
public string type { get; set; }
public string[] data { get; set; }
public axisTick axisTick { get; set; }
public string[] boundaryGap { get; set; }
}
public class yAxis
{
public string type { get; set; }
public string[] data { get; set; }
}
public class series
{
public string name { get; set; }
public string type { get; set; }
public string barWidth { get; set; }
public string[] data { get; set; }
}
public class axisTick
{
public Boolean alignWithLabel { get; set; }
}

解析
option opt = JsonConvert.DeserializeObject<option>(json);
写入单元格
for (int i = 0; i < opt.series.Length; i++)
{
sheet1.Cells[1, i + 2] = opt.series[i].name;//写入列名
}
for (int i = 0; i < opt.xAxis[0].data.Length; i++)
{
sheet1.Cells[i + 2, 1] = opt.xAxis[0].data[i].ToString();//写入行名
for (int k = 0; k < opt.series.Length; k++)
{
sheet1.Cells[i + 2, k + 2] = opt.series[k].data[i].ToString();//从第二行第二列开始写入数据
}
}

St4ndAlone | 园豆:151 (初学一级) | 2017-05-11 16:38
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册