我想用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表格，单元格里面有很多空格，导致不能插入图表。想知道哪里不对。。
最佳答案
0
CD列是空格还是？不明白你表达的意思。代码没有什么问题。
收获园豆：50
我的意思是A B两列单元格内有空格或回车，想把它们去掉。不过我现在用另一种Json解析的方式解决了。
@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]);
@盟怀部孩: 嗯嗯，好的。谢谢啦！
其他回答(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();//从第二行第二列开始写入数据
}
}
