调用c#强大的ReoGrid免费表格控件,用于显示、编辑、打印、导出和导入Excel电子表格
ReoGrid : Powerful free component to show, edit, print, export and import Excel spreadsheet in .NET application
ReoGrid 是 C# 编写的。.NET 电子表格控件(类似 Excel)。支持单元格合并,边框样式,图案背景颜色,数据格式,冻结,公式,宏和脚本执行,表格事件等
开源免费.
Line Chart
import win.ui; /*DSG{{*/ var winform = win.form(text="aardio form";right=854;bottom=546) winform.add( custom={cls="custom";text="自定义控件";left=0;top=0;right=855;bottom=547;bgcolor=12639424;db=1;dl=1;dr=1;dt=1;z=1} ) /*}}*/ import dotNet; import System.Drawing; import System.Windows.Forms; var dll = dotNet.load("\unvell.ReoGrid.dll"); var ReoGrid = dll.import("unvell.ReoGrid"); var reoGridControl1 = ReoGrid.ReoGridControl(); System.Windows.Forms.CreateEmbed(reoGridControl1,winform.custom); //reoGridControl1.Load(io.fullpath("\MyTemplate.xlsx")); var Worksheet = reoGridControl1.CurrentWorksheet; Worksheet.Cells.Item["A1"].Data = "aardio World!"; Worksheet.Cells.Item["A1"].Style.TextColor = ReoGrid.Graphics.SolidColor.Red; Worksheet.Cells.Item["A1"].Style.BackColor = ReoGrid.Graphics.SolidColor.Black; Worksheet.Item["A2"] = dotNet.object({ { "", 2008, 2009, 2010, 2011, 2012 }, { "City 1", 3, 2, 4, 2, 6 }, { "City 2", 7, 5, 3, 6, 4 }, { "City 3", 13, 10, 9, 10, 9 }, { "Total", "=SUM(B3:B5)", "=SUM(C3:C5)", "=SUM(D3:D5)", "=SUM(E3:E5)", "=SUM(F3:F5)" }, }); Worksheet.AddHighlightRange("B2:F2"); Worksheet.AddHighlightRange("A3:A6"); Worksheet.AddHighlightRange("B3:F5"); var c1 = ReoGrid.Chart.LineChart(); c1.Location = ReoGrid.Graphics.Point(220, 160); c1.Size = ReoGrid.Graphics.Size(400, 260); c1.Title = "Line Chart Sample"; // Specify data source. // Data source is created from serial data and names for every serial data. c1.DataSource = ReoGrid.Chart.WorksheetChartDataSource(Worksheet, "A3:A6", "B3:F5",ReoGrid.RowOrColumn.Row); c1.DataSource.CategoryNameRange = ReoGrid.RangePosition("B2:F2"); // add line chart instance on worksheet to display it Worksheet.FloatingObjects.Add(c1); winform.show(); win.loopMessage();
Column Chart
列图表:
只需要将上面的代码, 设定为列模式,改变一句话:
var c1 = ReoGrid.Chart.ColumnChart();
Bar Chart
var c1 = ReoGrid.Chart.BarChart();
.
.
.
还有 areachart , piechart , doughnutchart 等等.
给表格增加Group和outlIine扩展线
var outline = Worksheet.AddOutline(ReoGrid.RowOrColumn.Row, 3, 5); outline.Collapse(); // collapse outline.Expand(); // expand
我这里只是演示在aardio中调用c#的图形控件, 其他关于控件怎么使用,自行参考官方文档.
var t=dll.import("unvell.ReoGrid.CellTypes"); worksheet.Cells.Item["D8"].Data=t.ButtonCell("Hello世界"); var button=t.ButtonCell("你好,世界"); worksheet.Cells.Item["D3"].Body=button worksheet.Cells.Item["D1"].Body=t.HyperlinkCell("https://www.aardio.com/", true); worksheet.Cells.Item["D2"].Body=t.ProgressCell() worksheet.Cells.Item["D2"].Data="60%" var dropdown = t.DropdownListCell( "Apple", "Orange", "Banana", "Pear", "Pumpkin", "Cherry", "Coconut" ); worksheet.Cells.Item["B2"].Data = dropdown;
分享自定义单元格类型的一些用法
回复#32 @hi_aardio :
我在VB.net下用ReoGrid测试了一下,1000万条数据(100000行x100列),保存后的文件大约21M多,完全没问题,用时(包括生成+保存)大约15秒左右。
代码:
Public Class Form1 Private Sub ReoGridControl1_DoubleClick(sender As Object, e As EventArgs) Handles ReoGridControl1.DoubleClick ReoGridControl1.CurrentWorksheet.AppendRows(100000) ReoGridControl1.CurrentWorksheet.AppendColumns(100) For row = 1 To 100000 For col = 1 To 100 ReoGridControl1.CurrentWorksheet.SetCellData(row, col, "Hello VB.net") Next Next ReoGridControl1.Save("C:\Users\Administrator\Documents\dotNetProject\VB20220326\output.xlsx") End Sub End Class
楼主:1.unvell.ReoGrid.zip 例子中,键盘输入 到达不了控件,是dotNet的问题吗?
2.现在的情况是控件显示了界面,但按键盘上下左右和回车键控件内没有反应;
3.请问怎么解决dotNet控件键盘消息输入的问题,或拦截输入给控件的键盘消息?
像System库一样 定义用户库 unvell.ReoGrid
import dotNet; dotNet.reference({ ["Antlr3.Runtime"] = $"~/lib/unvell/ReoGrid/.res/Antlr3.Runtime.dll"; ["unvell.ReoGrid"] = $"~/lib/unvell/ReoGrid/.res/unvell.ReoGrid.dll"; ["unvell.ReoScript"] = $"~/lib/unvell/ReoGrid/.res/unvell.ReoScript.dll"; }); dotNet.import("unvell.ReoGrid") // Change the context strip menu languages with unvell.ReoGrid.LanguageResource { Menu_InsertSheet = "插入"; Menu_DeleteSheet = "删除"; Menu_RenameSheet = "重命名..."; Sheet_RenameDialog_Title = "重命名工作表"; Sheet_RenameDialog_NameLabel = "名称(&N): "; Button_OK = "确定"; Button_Cancel = "取消"; } /**intellisense() unvell.ReoGrid = .Net unvell.ReoGrid 名字空间\n!dotNetNameSpace. end intellisense**/
然后导入 unevll.ReoGrid 就可以像C#一样用了
import win.ui; /*DSG{{*/ mainForm = win.form(text="ReoGrid";right=757;bottom=467) mainForm.add() /*}}*/ import console import unvell.ReoGrid import System.Windows.Forms var grid = unvell.ReoGrid.ReoGridControl() //绑定界面 System.Windows.Forms.CreateEmbed(grid, mainForm) var sheet = grid.CurrentWorksheet; sheet.Cells.Item["D7"].Data = "aardio"; var script = `workbook.currentWorksheet.getCell("B1").data = 'hello world';`; grid.RunScript(script); //grid.SetSettings(unvell.ReoGrid.WorkbookSettings.View_ShowSheetTabControl, false); grid.SheetTabWidth = 250 sheet.SelectionRange = unvell.ReoGrid.RangePosition("B3:F5"); //sheet.SelectionStyle = unvell.ReoGrid.WorksheetSelectionStyle.FocusRect; //sheet.SelectionMode = unvell.ReoGrid.WorksheetSelectionMode.Row; sheet.BeforeCellKeyDown = function(s, e){ //console.log( e.KeyCode) } var setCellFocus = function(row, col){ sheet.FocusPos = unvell.ReoGrid.CellPosition(row, col); sheet.StartEdit() } mainForm.isDialogMessage = function(hwnd,msg){ if( msg.message == 0x100/*_WM_KEYDOWN*/){ var focusPos = sheet.FocusPos; select(msg.wParam) { case 0xD/*_VK_RETURN*/{ setCellFocus(focusPos.Row + 1, focusPos.Col - 1) } case 0x9/*_VK_TAB*/{ setCellFocus( focusPos.Row, focusPos.Col + 1 ) } } } return ; } mainForm.show(); return win.loopMessage();
ReoGrid create memory workbook
import console; import unvell.ReoGrid // create memory workbook instance var grid = unvell.ReoGrid.ReoGridControl.CreateMemoryWorkbook(); // get worksheet 1 (by default, a workbook contains a default empty worksheet) var sheet = grid.Worksheets[1]; // set data into cell A1 sheet.Cells.Item["A1"].Data = "aardio"; // set data into cell A2, B2, C2, D2 sheet.Cells.Item["A2"].Data = 10 sheet.Cells.Item["B2"].Data = 20 sheet.Cells.Item["C2"].Data = "=A2+B2" sheet.Cells.Item["D2"].Data = "=SUM(B2:C2)" // set data into cell[2,0] var data = dotNet.object({ {10, 20, "=A3*B3", "=SUM(A3:C3)"}, {17, 28, "=A4*B4", "=SUM(A4:C4)"} }) sheet.SetCellData(2, 0, data) // Add customize formula functions FormulaExtension = unvell.ReoGrid.Formula.FormulaExtension /* FormulaExtension.CustomFunctions.Item["myFunc"] = function(cell, args){ if (args.Length == 0) return null; return string.upper( args[1] ) } */ // The CustomFunctions type is a dictionary, so we can call the Add() method FormulaExtension.CustomFunctions.Add("myFunc", function(cell, args){ if (args.Length == 0) return null; return string.upper( args[1] ) }) sheet.Cells.Item["A5"].Data = `=myFunc("aardio")`; // Customize name reference provider unvell.ReoGrid.Formula.FormulaExtension.NameReferenceProvider = function(cell, name){ if (name == "myName1") return 10; else if (name == "myName2") return 20; else return null; }; sheet.Cells.Item["B5"].Data = "=myName1"; sheet.Cells.Item["C5"].Data = "=myName2"; // Run Script to add functions grid.RunScript("script.myfunc = data => '[' + data + ']';"); sheet.SetCellData("D5", `=myfunc(A5)`) //To get range that contains data in a worksheet, use UsedRange peroperty. var range = sheet.UsedRange; console.log( range ) sheet.IterateCells(range, function(row, col, cell){ // c# row col start from zero console.log(string.format(" %s 行:%s 列 值:%s", row +1, col +1, cell.Data)) // return true to continue iterate, return false to abort return true; }); /* // unvell.ReoGrid.IO.FileFormat public enum FileFormat : ushort { ReoGridFormat = 1, CSV = 10, Excel2007 = 30, _Auto = 0, _Custom = 100 } */ // save sheet to file sheet.Cells.Item["A1"].Data = "aardio"; grid.Save(".\res\SampleData.xlsx", unvell.ReoGrid.IO.FileFormat.Excel2007) console.pause(true);
回复#30 @小星碰头 :
import win.ui; /*DSG{{*/ var winform = win.form(text="aardio form";right=854;bottom=546) winform.add( custom={cls="custom";text="自定义控件";left=0;top=0;right=855;bottom=547;bgcolor=12639424;db=1;dl=1;dr=1;dt=1;z=1} ) /*}}*/ import dotNet; import System.Drawing; import System.Windows.Forms; var dll = dotNet.load("\unvell.ReoGrid.dll"); var ReoGrid = dll.import("unvell.ReoGrid"); var reoGridControl1 = ReoGrid.ReoGridControl(); System.Windows.Forms.CreateEmbed(reoGridControl1,winform.custom); //reoGridControl1.Load(io.fullpath("\tt.xlsx")); var Worksheet = reoGridControl1.CurrentWorksheet; Worksheet.Cells.Item["A1"].Data = "aardio World!"; Worksheet.DeleteRows(0, 1); winform.show(); win.loopMessage();
测试没报错......
话说这个是表格控件, 只要显示界面貌似就有很多行吧? 不理解你说的"总数大于1行没问题,最后一行时报超出范围" 是啥意思? 有代码吗
登录后方可回帖
回复#4 @admin :
再更新一个MiniExcel读取Excel文件,官网地址:https://gitee.com/dotnetchina/MiniExcel#%E7%AE%80%E4%BB%8B
国人写的,开源免费,据说内存占用少,效率非常高![](/static/editor/dialogs/emotion/images/pp/i_f02.png)
读写上百万条数据没问题。官网有测试数据对比。