Jacob项目:https://github.com/freemansoft/jacob-project
读写基本操作参考了:https://www.cnblogs.com/sungong1987/p/11173929.html
批量读取参考了:https://www.lazydungeon.com/blog/jacob-and-excel/
直接上代码,主要要说的问题
// 连接到当前打开Excel,python的xlwings的体验感
ActiveXComponent app = ActiveXComponent.connectToActiveInstance("Excel.Application");
// 获取当前激活的工作簿
Dispatch wb = app.getProperty("ActiveWorkbook").toDispatch();
// 获取当前激活的工作表
Dispatch sheet = Dispatch.get(wb, "ActiveSheet").toDispatch();
// 获取指定区域(range)的数据,r1c1格式比较方便拼接数据
// String position = "A1:B3";
String position = "R1C1:R3C2";
// 数据读取
// 获取区域数据
Dispatch range = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[]{
position}, new int[1]).toDispatch();
// 转换成SafeArray
SafeArray array = Dispatch.get(range, "Value").toSafeArray();
// 从SafeArray中提取数据,SafeArray不会出现下标越界问题,控制好value数组就可以了
String[][] values = new String[rows][cols];
for (int i = 1; i <= rows; i++) {
for (int j = 1; j <= cols; j++) {
values[i - 1][j - 1] = array.getString(i, j);
}
}
// 数据写入,String[][]可直接写入
// 写入指定区域(position和range区域大小要匹配)
Dispatch range = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[]{
position}, new int[1]).toDispatch();
Dispatch.put(range, "Value", values);
// 关闭屏幕更新提高写入性能,结束不要忘记打开
app.setProperty("ScreenUpdating", false); // 关闭屏幕更新
app.setProperty("ScreenUpdating", true); // 打开屏幕更新
详细具体如下:
Excel.java
import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.Dispatch;
import error.RangeIndexError;
import