Jacob 读写Excel

最新推荐文章于 2019-04-22 16:08:22 发布

原创最新推荐文章于 2019-04-22 16:08:22 发布 · 560 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#jacob #excel

Java Application 专栏收录该内容

12 篇文章

订阅专栏

本文介绍了一种利用Jacob库实现Excel文件的高效读写操作，包括数据的批量写入、单元格位置转换及值的获取和设置。重点在于简化复杂的Excel操作流程，提高开发效率。

Jacob 读写Excel

JacobExcelUtil

import java.io.File;
import java.io.FileNotFoundException;
import java.util.List;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;

/**
 * Jacob excel Util
 *
 */
public class JacobExcelUtil {

	private String file;
	private boolean readonly = false;

	private static String[] ABC = { "A", "B", "C", "D", "E", "F", "G", "H",
			"I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U",
			"V", "W", "X", "Y", "Z" };

	public JacobExcelUtil(String file) throws FileNotFoundException {
		this.file = file;
		File f = new File(file);
		if(!f.exists()) {
			throw new FileNotFoundException();
		}
	}

	/**
	 * 写入数据
	 * @param cells
	 */
	public void putData(List<ExcelCell> cells) throws Exception {
		if(cells == null || cells.size() == 0 ) {
			return;
		}
		
		ActiveXComponent excel = null;
		Dispatch workbooks = null;
		Dispatch workbook = null;
		Dispatch sheet = null;
		String filename = null;

		// 初始化
		ComThread.InitSTA();
		
		// open file
		try {
			filename = file;
			excel = new ActiveXComponent("Excel.Application");
			excel.setProperty("Visible", new Variant(false));
			workbooks = excel.getProperty("Workbooks").toDispatch();
			workbook = Dispatch.invoke(
					workbooks,
					"Open",
					Dispatch.Method,
					new Object[] { filename, new Variant(false),
							new Variant(readonly) },	// 是否以只读方式打开
					new int[1]).toDispatch();
	
			// put data
			sheet = Dispatch.get(workbook, "ActiveSheet").toDispatch();
			String position = null;
			int row = 0;
			int col = 0;
			int max = 26*26-1;
			for (ExcelCell c : cells) {
				row = c.getRow();
				col = c.getCol();
				if (row < 0 || col < 0 || col > max || c.getValue() == null
						|| c.getValue().trim().equals("")) {
					continue;
				}
				position = translateLocation(c.getRow(), c.getCol());
				setValue(sheet, position, c.getValue());
			}
			
		} finally {
			// close file
			try {
				Dispatch.call(workbook, "Save");
				Dispatch.call(workbook, "Close", new Variant(false));
			} finally {
				excel.invoke("Quit", new Variant[] {});
				ComThread.Release();
			}
		}
	}

	/**
	 * 转换单元格位置
	 * 最多支持26*26列
	 * @param i
	 * @param j
	 * @return
	 */
	public String translateLocation(int i, int j) {
		String loc = "";
		if (i <= 26) {
			loc = ABC[i-1] + (j);
		} else {
			loc = ABC[i/26-1] + ABC[i%26-1] + (j);
		}

		return loc;
	}

	/**
	 * 写入值
	 * 
	 * @param sheet
	 * @param position
	 * @param value
	 */
	protected void setValue(Dispatch sheet, String position, String value) {
		Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get,
				new Object[] { position }, new int[1]).toDispatch();
		Dispatch.put(cell, "Value", value);
	}

	/**
	 * 读取值
	 * 
	 * @param sheet
	 * @param position
	 * @return
	 */
	protected String GetValue(Dispatch sheet, String position) {
		Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get,
				new Object[] { position }, new int[1]).toDispatch();
		String value = Dispatch.get(cell, "Value").toString();
		return value;
	}
	
	public static void main(String[] args) {
		
	}

}

ExcelCell

public class ExcelCell {

	private int row;
	private int col;
	private String value;
	
	public ExcelCell() {
		
	}
	
	public ExcelCell(int row, int col, String value) {
		this.row = row;
		this.col = col;
		this.value = value;
	}

	public int getRow() {
		return row;
	}

	public void setRow(int row) {
		this.row = row;
	}

	public int getCol() {
		return col;
	}

	public void setCol(int col) {
		this.col = col;
	}

	public String getValue() {
		return value;
	}

	public void setValue(String value) {
		this.value = value;
	}

}