如何从数据库添加数据并将其设置为高图C#中的数据系列(how to add data from database and set it to data serie in highchart C#)
我是c#的新手
我想从数据库设置数据,但我有一些问题。 在高图中,Serie.Data是对象数组。现在要从数据库中获取数据并将其保存到列表中,然后我尝试将列表转换为object [],但它仍然是错误,无法将列表转换为对象[]。 你能说出从数据库设置数据的最佳方法是什么,并将其设置为高图。
这是我的代码
namespace Please { public partial class plotLayout : System.Web.UI.Page { int x=0; protected void Page_Load(object sender, EventArgs e){ //variable SqlCommand cmd; SqlDataReader data_read; //connect sql server SqlConnection sqlCon = new SqlConnection(@"Data Source=SUPERTONG-PC\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True"); //create array list to keep data from data_read List<Double> costs = new List<Double>(); List<Double> revenues = new List<Double>(); //open sql try { sqlCon.Open(); Response.Write("connection is successfull"); //get value in Table emploSalary //crate adapter to sent your string direction sql with db database server //SqlDataAdapter AdapSql = new SqlDataAdapter("select salary from emploSalary", sqlCon); String getSalary = "select salary from emploSalary"; cmd = new SqlCommand(getSalary, sqlCon); data_read = cmd.ExecuteReader(); //function sqldata.read จะทำการ อ่าน ข้อมูลให้ที่เดียว while(data_read.Read()) { //Response.Write(data_read["fname"]); //add salary to list costs.Add(Convert.ToDouble(data_read["salary"])); } data_read.Close(); Response.Write("num list ::" + costs.Count()); // get data in table revenue String getRevenue = "select revenue from comRevennue"; cmd = new SqlCommand(getRevenue, sqlCon); data_read = cmd.ExecuteReader(); while (data_read.Read()) { revenues.Add(Convert.ToDouble(data_read["revenue"])); } Response.Write("cost ::" + costs.Count()); Response.Write("revenue ::" + revenues.Count()); data_read.Close(); } catch(Exception ex) { Response.Write("Error exception ::" + ex.Message.ToString()); } //create title on Y && under X hcLine.Title = new Title("Main Title"); hcLine.SubTitle = new SubTitle("Sub Title"); hcLine.Theme = "gray"; hcLine.Legend = new Legend{align = Align.center }; //set main YAxis to left hand side. hcLine.YAxis.Add(new YAxisItem { title = new Title("SuperSup Title")}); hcLine.YAxis.Add(new YAxisItem { title = new Title("SuperSub Title"), opposite = true }); //hcLine.YAxis.Add(new YAxisItem { title = new Title("Faturamento") }); hcLine.XAxis.Add(new XAxisItem {categories = new []{"Jan", "Feb", "Mar", "Apr", "May"}}); var test_series = new List<Serie>(); // can not cast list to object[] test_series.Add(new Serie { data = revenues.Cast<object[]>().ToArray(), name = "year1991", yAxis = 0 }); test_series.Add(new Serie { data = new object[] { 60.6, 49.7}, name = "year1992", yAxis = 1 }); //data to plot //var serise = new Collection<Serie>(); //serise.Add(new Serie {data = new object[] {58.3, 76.2, 24.8, 88.2, 150.3}}); //plot options hcLine.PlotOptions = new PlotOptionsColumn { dataLabels = new DataLabels { enabled = true }}; //set data to grap line hcLine.DataSource = test_series; hcLine.DataBind(); } } }
谢谢
i'm new in c#
i want to set data from database but i have some problem . In the highchart ,Serie.Data is object array.And now to get data from data base and keep it to list then i try to cast list to object[] but it's still error can not cast list to object[]. Can you tell what is the best way to set data from database and set it in highchart.
this is my code
namespace Please { public partial class plotLayout : System.Web.UI.Page { int x=0; protected void Page_Load(object sender, EventArgs e){ //variable SqlCommand cmd; SqlDataReader data_read; //connect sql server SqlConnection sqlCon = new SqlConnection(@"Data Source=SUPERTONG-PC\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True"); //create array list to keep data from data_read List<Double> costs = new List<Double>(); List<Double> revenues = new List<Double>(); //open sql try { sqlCon.Open(); Response.Write("connection is successfull"); //get value in Table emploSalary //crate adapter to sent your string direction sql with db database server //SqlDataAdapter AdapSql = new SqlDataAdapter("select salary from emploSalary", sqlCon); String getSalary = "select salary from emploSalary"; cmd = new SqlCommand(getSalary, sqlCon); data_read = cmd.ExecuteReader(); //function sqldata.read จะทำการ อ่าน ข้อมูลให้ที่เดียว while(data_read.Read()) { //Response.Write(data_read["fname"]); //add salary to list costs.Add(Convert.ToDouble(data_read["salary"])); } data_read.Close(); Response.Write("num list ::" + costs.Count()); // get data in table revenue String getRevenue = "select revenue from comRevennue"; cmd = new SqlCommand(getRevenue, sqlCon); data_read = cmd.ExecuteReader(); while (data_read.Read()) { revenues.Add(Convert.ToDouble(data_read["revenue"])); } Response.Write("cost ::" + costs.Count()); Response.Write("revenue ::" + revenues.Count()); data_read.Close(); } catch(Exception ex) { Response.Write("Error exception ::" + ex.Message.ToString()); } //create title on Y && under X hcLine.Title = new Title("Main Title"); hcLine.SubTitle = new SubTitle("Sub Title"); hcLine.Theme = "gray"; hcLine.Legend = new Legend{align = Align.center }; //set main YAxis to left hand side. hcLine.YAxis.Add(new YAxisItem { title = new Title("SuperSup Title")}); hcLine.YAxis.Add(new YAxisItem { title = new Title("SuperSub Title"), opposite = true }); //hcLine.YAxis.Add(new YAxisItem { title = new Title("Faturamento") }); hcLine.XAxis.Add(new XAxisItem {categories = new []{"Jan", "Feb", "Mar", "Apr", "May"}}); var test_series = new List<Serie>(); // can not cast list to object[] test_series.Add(new Serie { data = revenues.Cast<object[]>().ToArray(), name = "year1991", yAxis = 0 }); test_series.Add(new Serie { data = new object[] { 60.6, 49.7}, name = "year1992", yAxis = 1 }); //data to plot //var serise = new Collection<Serie>(); //serise.Add(new Serie {data = new object[] {58.3, 76.2, 24.8, 88.2, 150.3}}); //plot options hcLine.PlotOptions = new PlotOptionsColumn { dataLabels = new DataLabels { enabled = true }}; //set data to grap line hcLine.DataSource = test_series; hcLine.DataBind(); } } }
thank you
原文:https://stackoverflow.com/questions/23251396
最满意答案
如果您只想复制文件,那么只需使用
shutil
。 还有一些openpyxl不支持的东西,比如丢失的图像和图表。 而且,正如你所看到的,记忆是一个问题。 每个单元使用大约45 kB的内存。openpyxl文档非常清楚打开工作簿时使用的不同选项:data_only只读取任何公式的结果并忽略公式。
如果要复制工作表,请参阅https://bitbucket.org/openpyxl/openpyxl/issue/171/copy-worksheet-function 。
否则,您可以使用两个工作簿,一个处于只读模式,另一个处于只写模式。 但是如果要复制,最好在文件系统中完成。
如果您只想将值从一个工作簿复制到另一个工作簿,则可以组合只读和只写模式以减少内存占用。 以下伪代码应该为您提供依据。
wb1 = load_workbook("file.xlsx", read_only=True, data_only=True) wb2 = Workbook(write_only=True) for ws1 in wb1: ws2 = wb2.create_sheet(ws1.title) for r1 in ws1: ws2.append(r1) # this might need unpacking to use values wb2.save("copy.xlsx")
If you just want to copy the file then just do that using
shutil
. There are still several things that openpyxl doesn't support such as images and charts that will be lost. And, as you're seeing, memory is an issue. Each cell uses about 45 kB of memory.The openpyxl documentation is pretty clear about the different options used when opening workbooks: data_only only read the results of any formulae and ignore the formulae.
See https://bitbucket.org/openpyxl/openpyxl/issue/171/copy-worksheet-function if you want to copy worksheets.
Otherwise you can use two workbooks, one in read-only mode and the other in write-only mode. But if you want to copy, this is best done in the file system.
If you only want to copy the values from one workbook to another then you can combine read-only and write-only modes to reduce the memory footprint. The following pseudo-code should give you a basis.
wb1 = load_workbook("file.xlsx", read_only=True, data_only=True) wb2 = Workbook(write_only=True) for ws1 in wb1: ws2 = wb2.create_sheet(ws1.title) for r1 in ws1: ws2.append(r1) # this might need unpacking to use values wb2.save("copy.xlsx")
相关问答
更多-
无论您实际使用何种语言,公式都以美国英语存储在Excel中。 美国英语分隔符是逗号( , ),而不是分号( ; )。 请参阅官方文档中“使用公式”一章中标题为“非US Excel函数和语法”的部分。 Formulas are stored in Excel in U.S. English, no matter what language you are actually using. The U.S. English separator is the comma (,), not semicolon (;) ...
-
我经常通过python和xlrd,python和Excel COM对象访问excel文件。 对于这项工作,xlrd将不起作用,因为它不支持xlsx格式。 但无论如何,这两种方法对你所寻找的东西都是矫枉过正的。 简单的Excel公式将提供您想要的,特别是VLOOKUP。 VLOOKUP“在表的最左列中查找值,然后从指定的列中返回同一行中的值。 关于VLOOKUP的一些建议,首先,如果你想匹配多个单元格,创建一个连接你感兴趣的单元格(在两个工作簿中)的“关键”单元格。 其次,确保将VLOOKUP的最后一个参数设 ...
-
在这个主题上有一个Apache POI FAQ条目 。 基本上,你的类路径中混合了新旧POI jar,这让人感到困惑, 显然不是支持的选项 您需要浏览类路径,并删除任何旧的或不匹配的POI jar,只留下最新Apache POI版本的jar。 如果你无法弄清楚你正在使用什么罐子,你可以利用FAQ条目中的代码来帮助你找到答案 随着所有旧罐子的消失,添加最新的( 目前为3.12 ),包括他们的依赖关系,如果不使用Maven ,你的代码将是好的! There's an Apache POI FAQ entry o ...
-
xlutils.copy是一个模块。 尝试这个: from xlutils.copy import copy 然后 workbookCopy = copy(workbook) 更多信息: http : //xlutils.readthedocs.io/en/latest/copy.html xlutils.copy is a module. Try this: from xlutils.copy import copy then workbookCopy = copy(workbook) More ...
-
让我们一个一个去。 我重新创建了如图所示的.xlsx文件,并添加了一个对如何将文件读入熊猫数据框的更改。 1.您可以按以下方式使用applymap : import pandas as pd pd.set_option('expand_frame_repr', False) dataframe = pd.read_excel('file.xlsx', sheetname = name, header = 0, index_col = 0) dataframe.applymap(lambda x: x.st ...
-
如果您只想复制文件,那么只需使用shutil 。 还有一些openpyxl不支持的东西,比如丢失的图像和图表。 而且,正如你所看到的,记忆是一个问题。 每个单元使用大约45 kB的内存。 openpyxl文档非常清楚打开工作簿时使用的不同选项:data_only只读取任何公式的结果并忽略公式。 如果要复制工作表,请参阅https://bitbucket.org/openpyxl/openpyxl/issue/171/copy-worksheet-function 。 否则,您可以使用两个工作簿,一个处于只读 ...
-
你所看到的是预期的行为。 当单元格具有公式时,Excel会将公式的结果保存为缓存值。 openpyxl从不评估公式,因此永远不会维护缓存并使任何现有缓存无效。 相反,如果您需要公式的结果,则可以使用data_only=True参数加载文件。 虽然这将取代公式与价值。 这包括在文档中: http : //openpyxl.readthedocs.org/en/stable/usage.html#read-an-existing-workbook What you are seeing is expected ...
-
我过去曾经使用过xlsx软件包,尽管有很大的希望,却发现它缺乏功能和易用性。 当我在一分钟前搜索包装手册时,似乎不可能做你想做的事情。 好消息是有一个 XLConnect包形式的替代品 。 XLConnect使用与xlsx相同的Java代码(来自Apache POI项目),因此您在操作系统之间仍具有相同的高级别互操作性。 XLConnect有一个功能XLConnect您的需求: setCellFormula() 。 参考文献: 包装手册, 网址为http://cran.r-project.org/web/p ...
-
XLSX获得最后一栏?(XLSX get last column?)[2021-11-21]
您必须检查每一Row并调用Row.getLastCellNum() 。 最大单元格数是最后一列num。 You have to check each Row and call Row.getLastCellNum(). The max cell num is the last column num. -
将xlsx文件保存在rails中(Saving xlsx file in rails)[2022-12-06]
to_xlsx应该返回Axlsx包。 该包将自己保存到文件: relation.to_xlsx.serialize("report.xlsx") 但是,如果您通过电子邮件发送,则只需将其作为附件即可。 您无需将其另存为文件: class UserMailer < ActionMailer::Base def export(users) content = User.to_xlsx(data: users).to_stream.string attachments["Users.xlsx ...