[转]Java Jacob操作Excel

/**
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 <= count; 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<SupervisionProblem> entityList)
throws UnsupportedEncodingException { String HSSFname = "自动监控设施现场检查存在问题详表";
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(HSSFname);
sheet.getPrintSetup().setLandscape(true); // 创建标题样式
HSSFCellStyle style = wb.createCellStyle();
style.setWrapText(true); // 设置允许换行
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//  垂直   
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//  水平  
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
// 设置字体
HSSFFont font = wb.createFont();
font.setFontName("华文楷体");
font.setFontHeightInPoints((short) 16);// 设置字体大小
style.setFont(font);//  样式，居中 // 创建正文数据样式
HSSFCellStyle style2 = wb.createCellStyle();
style2.setWrapText(true); // 设置允许换行
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//  垂直   
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//  水平  
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
HSSFFont font2 = wb.createFont();
font2.setFontName("宋体");
font2.setFontHeightInPoints((short) 8);
style2.setFont(font2);// 选择需要用到的字体格式 String fileName = new String(HSSFname.getBytes(), System.getProperty("file.encoding"));
fileName = java.net.URLEncoder.encode(fileName, "UTF-8"); // 创建第一行 标题
// 设置第一行第一到第7个单元格合并
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
HSSFRow rowTitle = sheet.createRow(0);
for (int i = 0; i < 7; i++) {
HSSFCell cellone = rowTitle.createCell(i); // 第一行第一列
cellone.setCellValue(HSSFname);//  表格的第一行第一列显示的数据   
cellone.setCellStyle(style);//  样式，居中
} rowTitle.setHeight((short) 1100); // 第二行
HSSFRow row2 = sheet.createRow(1);
row2.setHeight((short) 480);
String colums[] = new String[] { "序号", "企业名称", "问题发现时间", "问题开始时间", "整改期限", "问题类别", "现场检查发现的问题" };
// 插入列名
for (int i = 0; i < colums.length; i++) {
HSSFCell row21 = row2.createCell(i);
row21.setCellValue(new HSSFRichTextString(colums[i]));//  表格的第一行第一列显示的数据
// 数据字体
row21.setCellStyle(style2);
if (i == 0) {
sheet.setColumnWidth(i, 2000);// 设置单元格宽度
} else if (i == 1) {
sheet.setColumnWidth(i, 6500);// 设置单元格宽度
} else if (i == 2) {
sheet.setColumnWidth(i, 3000);// 设置单元格宽度
} else if (i == 3) {
sheet.setColumnWidth(i, 3000);// 设置单元格宽度
} else if (i == 4) {
sheet.setColumnWidth(i, 3000);// 设置单元格宽度
} else if (i == 5) {
sheet.setColumnWidth(i, 4500);// 设置单元格宽度
} else if (i == 6) {
sheet.setColumnWidth(i, 7300);// 设置单元格宽度
}
} // 插入数据
for (int j = 0; j < entityList.size(); j++) {
SupervisionProblem sp = entityList.get(j);
HSSFRow rowData = sheet.createRow((2 + j));
rowData.setHeight((short) 480);
for (int i = 0; i < colums.length; i++) {
HSSFCell cData = rowData.createCell(i);
cData.setCellStyle(style2);
if (i == 0) {
cData.setCellValue(j + 1); // 序号
} else if (i == 1) {
cData.setCellValue(sp.getEnterpriseName());
} else if (i == 2) {
cData.setCellValue(sp.getCreateTime());
} else if (i == 3) {
cData.setCellValue(sp.getProblemStartTime());
} else if (i == 4) {
cData.setCellValue(sp.getRecEndTime());
} else if (i == 5) {
cData.setCellValue(sp.getProblemTypeName());
} else if (i == 6) {
cData.setCellValue(sp.getProblemDescription());
}
}
} try {
wb.write(os);
os.close();
os.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (os != null) {
try {
os.close();
os.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
if (wb != null) {
try {
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}---------------------
作者：追梦的王小白
来源：CSDN
原文：https://blog.csdn.net/javadakangxiaobai/article/details/83422396
版权声明：本文为博主原创文章，转载请附上博文链接！

/**
* 打印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
版权声明：本文为博主原创文章，转载请附上博文链接！