调用C#的SpreadsheetLight.dll程序集实现对xlsx文件的操作
By
admin
at 2021-11-05 • 1人收藏 • 2628人看过
SpreadsheetLight is an open source Open XML spreadsheet library for .NET Framework written in C#, and is released under the MIT License.
这里简单提供下使用示例
import dotNet;
var appDomain = dotNet.clr().createAppDomain();
var ssl = appDomain.loadFile("\SpreadsheetLight.dll");
var sl = ssl.new("SpreadsheetLight.SLDocument");
// set a boolean at "A1"
sl.SetCellValue("A1", true);
// set at row 2, columns 1 through 20, a value that's equal to the column index
for ( i = 1; 20; 1) sl.SetCellValue(2, i, i);
// set the value of PI
sl.SetCellValue("B3", 3.14159);
sl.SetCellValueNumeric(4, 2, "3.14159");
// normal string data
sl.SetCellValue("C6", "This is at C6!");
// typical XML-invalid characters are taken care of,
// in particular the & and < and >
sl.SetCellValue("I6", "Dinner & Dance costs < $10");
// this sets a cell formula
// Note that if you want to set a string that starts with the equal sign,
// but is not a formula, prepend a single quote.
// For example, "'==" will display 2 equal signs
sl.SetCellValue(7, 3, "=SUM(A2:T2)");
sl.SaveAs(io.fullpath("\HelloWorld.xlsx"));这个程序集也依赖其他dll, 我把示例打包了, 有这方面需求的可以下载自行研究:
开源项目地址:https://spreadsheetlight.com
17 个回复 | 最后更新于 2022-02-24
2021-11-09
#8
很好，又多了一个操作excel的途径，多谢大佬。
import console;
import dotNet
// 导入dll
var ssl = dotNet.load("/SpreadsheetLight.dll");
// 新建excel
var s1 = ssl.new("SLDocument");
// 设置单元格
s1.SetCellValue("A1", 1)
// 保存文件
s1.SaveAs(io.fullpath("/a.xlsx"))
// 打开已存在的excel
var SLDocument = ssl.import("SLDocument")
var s2 = SLDocument("a.xlsx")
//设置单元格
s2.SetCellValue("A2", "111");
// 保存文件
s2.SaveAs(io.fullpath("/a.xlsx"));
console.pause(true);
2021-11-10
#11
增加一个读取所有数据的例子：
import console;
import dotNet
// 导入dll
var ssl = dotNet.load("/SpreadsheetLight.dll");
// 打开已存在的excel
var SLDocument = ssl.import("SLDocument")
var s2 = SLDocument("a.xlsx")
// 读取所有数据
var stats = s2.GetWorksheetStatistics();
for(i=1;stats.EndRowIndex;1){
for(j=1;stats.EndColumnIndex;1){
var value = s2.GetCellValueAsString(i, j);
if(value != ""){
console.log(value)
}
}
}
2022-02-12
#13
用Python调用C#的dll，需要用到pythonnet库，改写了一下，完美运行
把Spreadsheetlight.dll放到同一文件夹。
import clr
clr.AddReference('Spreadsheetlight') # import dll, no need to add ".dll"
from SpreadsheetLight import SLDocument # import class
sl = SLDocument() # create object from class
# set a boolean at "A1"
sl.SetCellValue("A1", True)
# set at row 2, columns1 through 20, a value that's equal to the column index
for i in range(20):
sl.SetCellValue(2, i, i)
# set the value of PI
sl.SetCellValue("B3", 3.14159)
sl.SetCellValueNumeric(4, 2, "3.14159")
# normal string data
sl.SetCellValue("C6", "This is at C6!")
# typical XML - invalid characters are taken care of, in particular the & and < and >
sl.SetCellValue("I6", "Dinner & Dance costs < $10")
# this sets a cell formula
# Note that if you want to set a string that starts with the equal sign,
# but is not a formula, prepend a single quote.
# For example, "'==" will display 2 equal signs
sl.SetCellValue(7, 3, "=SUM(A2:T2)")
sl.SaveAs("HelloWorld.xlsx")
登录后方可回帖
我在NuGet找不到system.drawing.common 4.0.0.1 你这个DLL哪里下载的?