[转]Java Jacob操作Excel
2020-12-13 06:18
标签：开发 throw 问题 rac ott book spl null param Jacob项目：https://sourceforge.net/projects/jacob-project/ 转自：https://blog.csdn.net/ZY_extreme/article/details/80019009 转自：http://www.360doc.com/content/14/0310/11/12385684_359224303.shtml 转自：https://blog.csdn.net/ZY_extreme/article/details/80007232 转自：https://www.cnblogs.com/vczh/p/5692527.html 转自：https://blog.csdn.net/javadakangxiaobai/article/details/83422396 转自：https://blog.csdn.net/a0701302/article/details/62236470 [转]Java Jacob操作Excel 标签：开发 throw 问题 rac ott book spl null param 原文地址：https://www.cnblogs.com/sungong1987/p/11173929.html
/**
2018年4月20日
**/
import com.jacob.com.*;
import com.jacob.activeX.*;
public class ReadExcel {
private static ActiveXComponent xl;
private static Dispatch workbooks = null;
private static Dispatch workbook = null;
private static Dispatch sheet = null;
private static String filename = null;
private static boolean readonly = false;
public static void main(String[] args) {
String file = "E:\\frequently\\study\\ex.xlsx";
OpenExcel(file, false);// false为不显示打开Excel
SetValue("1","A1","Value","2");
System.out.println(GetValue("基础设施情况","G10"));
CloseExcel(false);
}
// 打开Excel文档
private static void OpenExcel(String file, boolean f) {
try {
filename = file;
xl = new ActiveXComponent("Excel.Application");
xl.setProperty("Visible", new Variant(f));
workbooks = xl.getProperty("Workbooks").toDispatch();
workbook = Dispatch.invoke(workbooks, "Open", Dispatch.Method,
new Object[] { filename, new Variant(false), new Variant(readonly) }, // 是否以只读方式打开
new int[1]).toDispatch();
} catch (Exception e) {
e.printStackTrace();
}
}
// 关闭Excel文档
private static void CloseExcel(boolean f) {
try {
Dispatch.call(workbook, "Save");
Dispatch.call(workbook, "Close", new Variant(f));
} catch (Exception e) {
e.printStackTrace();
} finally {
xl.invoke("Quit", new Variant[] {});
}
}
// 写入值--以编号读写sheet
private static void SetValue(String sheetItem ,String position, String type, String value) {
// sheet = Dispatch.get(workbook,"ActiveSheet").toDispatch();
Dispatch sheets = Dispatch.get(workbook, "Sheets").toDispatch();
// 以编号读写sheet
sheet = Dispatch.invoke(sheets, "Item", Dispatch.Get, new Object[] { new String(sheetItem) }, new int[1])
.toDispatch();
Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] { position }, new int[1])
.toDispatch();
Dispatch.put(cell, type, value);
}
// 读取值--以名称读写sheet
private static String GetValue(String sheetItem,String position) {
// sheet = Dispatch.get(workbook,"ActiveSheet").toDispatch();
Dispatch sheets = Dispatch.get(workbook, "Sheets").toDispatch();
// 以名称读写sheet
sheet = Dispatch.invoke(sheets, "Item", Dispatch.Get, new Object[] { new String(sheetItem) }, new int[1])
.toDispatch();
Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] { position }, new int[1])
.toDispatch();
String value = Dispatch.get(cell, "Value").toString();
return value;
}
}
import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
public class JacobExcelTool {
private static ActiveXComponent xl = null; //Excel对象(防止打开多个)
private static Dispatch workbooks = null; //工作簿对象
private Dispatch workbook = null; //具体工作簿
private Dispatch sheets = null;// 获得sheets集合对象
private Dispatch currentSheet = null;// 当前sheet
public ActiveXComponent getXl() {
return xl;
}
public Dispatch getWorkbooks() {
return workbooks;
}
public Dispatch getWorkbook() {
return workbook;
}
/**
* 打开excel文件
* @param filepath 文件路径名称
* @param visible 是否显示打开
* @param readonly 是否只读方式打开
*/
public void OpenExcel(String filepath, boolean visible, boolean readonly) {
try {
initComponents(); //清空原始变量
ComThread.InitSTA();
if(xl==null)
xl = new ActiveXComponent("Excel.Application"); //Excel对象
xl.setProperty("Visible", new Variant(visible));//设置是否显示打开excel
if(workbooks==null)
workbooks = xl.getProperty("Workbooks").toDispatch(); //工作簿对象
workbook = Dispatch.invoke( //打开具体工作簿
workbooks,
"Open",
Dispatch.Method,
new Object[] { filepath, new Variant(false),
new Variant(readonly) },// 是否以只读方式打开
new int[1]).toDispatch();
} catch (Exception e) {
e.printStackTrace();
releaseSource();
}
}
/**
* 工作簿另存为
* @param filePath 另存为的路径
*/
public void SaveAs(String filePath){
Dispatch.invoke(workbook, "SaveAs", Dispatch.Method,
new Object[] { filePath,
new Variant(44) }, new int[1]);
}
/**
* 关闭excel文档
* @param f 含义不明 （关闭是否保存？默认false）
*/
public void CloseExcel(boolean f,boolean quitXl) {
try {
Dispatch.call(workbook, "Save");
Dispatch.call(workbook, "Close", new Variant(f));
} catch (Exception e) {
e.printStackTrace();
} finally {
if(quitXl){
releaseSource();
}
}
}
/**
* 释放资源
*/
public static void releaseSource(){
if(xl!=null){
xl.invoke("Quit", new Variant[] {});
xl = null;
}
workbooks = null;
ComThread.Release();
System.gc();
}
/**
* 添加新的工作表(sheet)，（添加后为默认为当前激活的工作表）
*/
public Dispatch addSheet() {
return Dispatch.get(Dispatch.get(workbook, "sheets").toDispatch(), "add").toDispatch();
}
/**
* 修改当前工作表的名字
* @param newName
*/
public void modifyCurrentSheetName(String newName) {
Dispatch.put(getCurrentSheet(), "name", newName);
}
/**
* 得到当前工作表的名字
* @return
*/
public String getCurrentSheetName() {
return Dispatch.get(getCurrentSheet(), "name").toString();
}
/**
* 得到工作薄的名字
* @return
*/
public String getWorkbookName() {
if(workbook==null)
return null;
return Dispatch.get(workbook, "name").toString();
}
/**
* 得到sheets的集合对象
* @return
*/
public Dispatch getSheets() {
if(sheets==null)
sheets = Dispatch.get(workbook, "sheets").toDispatch();
return sheets;
}
/**
* 得到当前sheet
* @return
*/
public Dispatch getCurrentSheet() {
currentSheet = Dispatch.get(workbook, "ActiveSheet").toDispatch();
return currentSheet;
}
/**
* 通过工作表名字得到工作表
* @param name sheetName
* @return
*/
public Dispatch getSheetByName(String name) {
return Dispatch.invoke(getSheets(), "Item", Dispatch.Get, new Object[]{name}, new int[1]).toDispatch();
}
/**
* 通过工作表索引得到工作表(第一个工作簿index为1)
* @param index
* @return sheet对象
*/
public Dispatch getSheetByIndex(Integer index) {
return Dispatch.invoke(getSheets(), "Item", Dispatch.Get, new Object[]{index}, new int[1]).toDispatch();
}
/**
* 得到sheet的总数
* @return
*/
public int getSheetCount() {
int count = Dispatch.get(getSheets(), "count").toInt();
return count;
}
/**
* 调用excel宏
* @param macroName 宏名
*/
public void callMacro(String macroName){
Dispatch.call(xl, "Run",new Variant(macroName));
}
/**
* 单元格写入值
* @param sheet 被操作的sheet
* @param position 单元格位置，如：C1
* @param type 值的属性 如：value
* @param value
*/
public void setValue(Dispatch sheet, String position, String type, Object value) {
Dispatch cell = Dispatch.invoke(sheet, "Range",
Dispatch.Get, new Object[] { position }, new int[1])
.toDispatch();
Dispatch.put(cell, type, value);
}
/**
* 单元格读取值
* @param position 单元格位置，如： C1
* @param sheet
* @return
*/
public Variant getValue(String position, Dispatch sheet) {
Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get,
new Object[] { position }, new int[1]).toDispatch();
Variant value = Dispatch.get(cell, "Value");
return value;
}
private void initComponents(){
workbook = null;
currentSheet = null;
sheets = null;
}
}
package java_word;
/**
1580536707@qq.com
2018年4月19日
**/
import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
public class DispatchTest
{
public static String fileRealPath = "C:\\Users\\Desktop\\原文档.doc";
public static String saveNewRealpath = "C:\\Users\\Desktop\\副本.doc";
public static void main(String[] args)
{
//这是copy全文方法
ComThread.InitSTA();
//被复制的文档
ActiveXComponent word2= new ActiveXComponent("Word.Application");
word2.setProperty("Visible", new Variant(false));
Dispatch documents2 = word2.getProperty("Documents").toDispatch();
//复制的文档
ActiveXComponent word= new ActiveXComponent("Word.Application");
word.setProperty("Visible", new Variant(false));
Dispatch documents = word.getProperty("Documents").toDispatch();
Dispatch doc2 = Dispatch.call(documents2, "Open",saveNewRealpath ).toDispatch();
//复制模板的内容
Dispatch doc = Dispatch.call(documents, "Open",fileRealPath).toDispatch();
Dispatch wordContent = Dispatch.get(doc, "Content").toDispatch();
/*Dispatch paragraphs = Dispatch.get(doc2, "Paragraphs").toDispatch();
Dispatch paragraph = Dispatch.call(paragraphs, "Item",new Variant(40)).toDispatch();
Dispatch range = Dispatch.get(paragraph, "Range").toDispatch();
Dispatch.call(range, "Copy");*/
Dispatch.call(wordContent, "Copy");
Dispatch selection = Dispatch.get(word2, "Selection").toDispatch();
Dispatch textRange = Dispatch.get(selection, "Range").toDispatch();
Dispatch.call(textRange, "Paste");
Dispatch.call(doc2, "Save");
Dispatch.call(doc2, "Close", new Variant(true));
Dispatch.call(word2, "Quit");
doc2 = null;
word2 = null;
Dispatch.call(doc, "Close", new Variant(true));
Dispatch.call(word , "Quit");
doc = null;
word = null;
documents2 = null;
ComThread.Release();
}
}
---------------------
作者：花好人间
来源：CSDN
原文：https://blog.csdn.net/ZY_extreme/article/details/80007232
版权声明：本文为博主原创文章，转载请附上博文链接！
package com.HeiBeiEDU.test2;
import java.io.File;
import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
public class PrintDemo {
public static boolean printOfficeFile(File f) {
if (f != null && f.exists()) {
String fileNameString = f.getName();
String postfixString = Utils.getPostfix(fileNameString);
if (postfixString.equalsIgnoreCase("xls") || postfixString.equalsIgnoreCase("xlsx")) {
/**
* 功能:实现excel打印工作
*/
ComThread.InitSTA();
ActiveXComponent xl = new ActiveXComponent("Excel.Application");
try {
// System.out.println("version=" +
// xl.getProperty("Version"));
// 不打开文档
Dispatch.put(xl, "Visible", new Variant(false));
Dispatch workbooks = xl.getProperty("Workbooks").toDispatch();
// 打开文档
Dispatch excel = Dispatch.call(workbooks, "Open", f.getAbsolutePath()).toDispatch();
// 横向打印(2013/05/24)
// Dispatch currentSheet = Dispatch.get(excel,
// "ActiveSheet")
// .toDispatch();
// Dispatch pageSetup = Dispatch
// .get(currentSheet, "PageSetup").toDispatch();
// Dispatch.put(pageSetup, "Orientation", new Variant(2));
// 每张表都横向打印2013-10-31
Dispatch sheets = Dispatch.get((Dispatch) excel, "Sheets").toDispatch();
// 获得几个sheet
int count = Dispatch.get(sheets, "Count").getInt();
// System.out.println(count);
for (int j = 1; j ) {
Dispatch sheet = Dispatch
.invoke(sheets, "Item", Dispatch.Get, new Object[] { new Integer(j) }, new int[1])
.toDispatch();
Dispatch pageSetup = Dispatch.get(sheet, "PageSetup").toDispatch();
Dispatch.put(pageSetup, "Orientation", new Variant(2));
Dispatch.call(sheet, "PrintOut");
}
// 开始打印
if (excel != null) {
// Dispatch.call(excel, "PrintOut");
// 增加以下三行代码解决文件无法删除bug
Dispatch.call(excel, "save");
Dispatch.call(excel, "Close", new Variant(true));
excel = null;
}
xl.invoke("Quit", new Variant[] {});
xl = null;
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
// 始终释放资源
ComThread.Release();
}
} else if (postfixString.equalsIgnoreCase("doc") || postfixString.equalsIgnoreCase("docx")) {
ComThread.InitSTA();
ActiveXComponent wd = new ActiveXComponent("Word.Application");
try {
// 不打开文档
Dispatch.put(wd, "Visible", new Variant(false));
Dispatch document = wd.getProperty("Documents").toDispatch();
// 打开文档
Dispatch doc = Dispatch
.invoke(document, "Open", Dispatch.Method, new Object[] { f.getAbsolutePath() }, new int[1])
.toDispatch();
// 开始打印
if (doc != null) {
Dispatch.call(doc, "PrintOut");
// 增加以下三行代码解决文件无法删除bug
Dispatch.call(doc, "save");
Dispatch.call(doc, "Close", new Variant(true));
doc = null;
}
wd.invoke("Quit", new Variant[] {});
wd = null;
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
// 始终释放资源
ComThread.Release();
}
} else {
return false;
}
} else {
return false;
}
}
public static void main(String[] args) {
PrintDemo.printOfficeFile(new File("hehe.xls"));
}
}
/**
* 生成excel
*
* @throws UnsupportedEncodingException
*/
private void moveToControlExcel(OutputStream os, List
/**
* 打印Excel文件
* @param filePath 文件路径
*/
public static boolean printFileAction(String filePath){
boolean returnFlg = false;
try {
ComThread.InitSTA();
ActiveXComponent xl = new ActiveXComponent("Excel.Application");
// 不打开文档
Dispatch.put(xl, "Visible", new Variant(true));
Dispatch workbooks = xl.getProperty("Workbooks").toDispatch();
// win下路径处理(把根目录前的斜杠删掉)
filePath = filePath.replace("/E:/","E:/");
// 判断文件是否存在
boolean fileExistFlg = fileExist(filePath);
if (fileExistFlg) {
Dispatch excel=Dispatch.call(workbooks,"Open",filePath).toDispatch();
// 开始打印
Dispatch.get(excel,"PrintOut");
returnFlg = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 始终释放资源
ComThread.Release();
}
return returnFlg;
}
---------------------
作者：star0701
来源：CSDN
原文：https://blog.csdn.net/a0701302/article/details/62236470
版权声明：本文为博主原创文章，转载请附上博文链接！
/**
* 打印Excel文件
* @param filePath 文件路径
*/
public static boolean printFileAction(String filePath,String printerName){
boolean returnFlg = false;
try {
ComThread.InitSTA();
ActiveXComponent xl = new ActiveXComponent("Excel.Application");
// 不打开文档
Dispatch.put(xl, "Visible", new Variant(true));
Dispatch workbooks = xl.getProperty("Workbooks").toDispatch();
// win下路径处理(把根目录前的斜杠删掉)
filePath = filePath.replace("/E:/","E:/");
Object[] object = new Object[8];
object[0] = Variant.VT_MISSING;
object[1] = Variant.VT_MISSING;
object[2] = Variant.VT_MISSING;
object[3] = new Boolean(false);
object[4] = printerName;
object[5] = new Boolean(false);
object[6] = Variant.VT_MISSING;
object[7] = Variant.VT_MISSING;
// 判断文件是否存在
boolean fileExistFlg = fileExist(filePath);
if (fileExistFlg) {
Dispatch excel=Dispatch.call(workbooks,"Open",filePath).toDispatch();
// 开始打印
Dispatch.callN(excel,"PrintOut",object);
returnFlg = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 始终释放资源
ComThread.Release();
}
return returnFlg;
}
---------------------
作者：star0701
来源：CSDN
原文：https://blog.csdn.net/a0701302/article/details/62236470
版权声明：本文为博主原创文章，转载请附上博文链接！
/**
* 判断文件是否存在.
* @param filePath 文件路径
* @return
*/
private static boolean fileExist(String filePath){
boolean flag = false;
try {
File file = new File(filePath);
flag = file.exists();
}catch (Exception e) {
e.printStackTrace();
}
return flag;
}
---------------------
作者：star0701
来源：CSDN
原文：https://blog.csdn.net/a0701302/article/details/62236470
版权声明：本文为博主原创文章，转载请附上博文链接！
下一篇：python基础|数据类型练习题