程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> JAVA編程 >> 關於JAVA >> Java讀寫Excel實現教程及代碼

Java讀寫Excel實現教程及代碼

編輯:關於JAVA
 

本文主要向你演示如何使用JavaExcel API來讀寫Excel文件。關於JavaExcel API,這是一個開源的lib庫。其相關的feature如下:

  • 支持Excel 95, 97, 2000, XP, 2003 的制表頁。
  • 可以讀寫相關的Excel公式 (僅支持Excel 97 及以後版本)
  • 可以生成 Excel 2000 格式的xls文件。
  • 支持字體,數字和日期格式。
  • 支持單元格的陰影,邊框和顏色。
  • 可以修改已存在的制表頁。
  • 國際化多語言集。(公式目前支持,英文,法文,西班牙文和德文)
  • 支持圖表拷貝。
  • 支持圖片的插入和復制。
  • 日志生成可以使用Jakarta Commons Logging, log4j, JDK 1.4 Logger, 等。
  • 更多……

    你可以在這裡下載:http://jexcelapi.sourceforge.net/,然後,把jxl.jar加到你的Java的classpath中。

    下面是兩段例程,一段是如何創建Excel,一段是如何讀取Excel。

     

    創建Excel

    package writer;
    
    import java.io.File;
    import java.io.IOException;
    import java.util.Locale;
    
    import jxl.CellView;
    import jxl.Workbook;
    import jxl.WorkbookSettings;
    import jxl.format.UnderlineStyle;
    import jxl.write.Formula;
    import jxl.write.Label;
    import jxl.write.Number;
    import jxl.write.WritableCellFormat;
    import jxl.write.WritableFont;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    import jxl.write.WriteException;
    import jxl.write.biff.RowsExceededException;
    
    
    public class WriteExcel {
    
    	private WritableCellFormat timesBoldUnderline;
    	private WritableCellFormat times;
    	private String inputFile;
    	
    public void setOutputFile(String inputFile) {
    	this.inputFile = inputFile;
    	}
    
    	public void write() throws IOException, WriteException {
    		File file = new File(inputFile);
    		WorkbookSettings wbSettings = new WorkbookSettings();
    
    		wbSettings.setLocale(new Locale("en", "EN"));
    
    		WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
    		workbook.createSheet("Report", 0);
    		WritableSheet excelSheet = workbook.getSheet(0);
    		createLabel(excelSheet);
    		createContent(excelSheet);
    
    		workbook.write();
    		workbook.close();
    	}
    
    	private void createLabel(WritableSheet sheet)
    			throws WriteException {
    		// Lets create a times font
    		WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
    		// Define the cell format
    		times = new WritableCellFormat(times10pt);
    		// Lets automatically wrap the cells
    		times.setWrap(true);
    
    		// Create create a bold font with unterlines
    		WritableFont times10ptBoldUnderline = new WritableFont(
    				WritableFont.TIMES, 10, WritableFont.BOLD, false,
    				UnderlineStyle.SINGLE);
    		timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
    		// Lets automatically wrap the cells
    		timesBoldUnderline.setWrap(true);
    
    		CellView cv = new CellView();
    		cv.setFormat(times);
    		cv.setFormat(timesBoldUnderline);
    		cv.setAutosize(true);
    
    		// Write a few headers
    		addCaption(sheet, 0, 0, "Header 1");
    		addCaption(sheet, 1, 0, "This is another header");
    		
    
    	}
    
    	private void createContent(WritableSheet sheet) throws WriteException,
    			RowsExceededException {
    		// Write a few number
    		for (int i = 1; i < 10; i++) {
    			// First column
    			addNumber(sheet, 0, i, i + 10);
    			// Second column
    			addNumber(sheet, 1, i, i * i);
    		}
    		// Lets calculate the sum of it
    		StringBuffer buf = new StringBuffer();
    		buf.append("SUM(A2:A10)");
    		Formula f = new Formula(0, 10, buf.toString());
    		sheet.addCell(f);
    		buf = new StringBuffer();
    		buf.append("SUM(B2:B10)");
    		f = new Formula(1, 10, buf.toString());
    		sheet.addCell(f);
    
    		// Now a bit of text
    		for (int i = 12; i < 20; i++) {
    			// First column
    			addLabel(sheet, 0, i, "Boring text " + i);
    			// Second column
    			addLabel(sheet, 1, i, "Another text");
    		}
    	}
    
    	private void addCaption(WritableSheet sheet, int column, int row, String s)
    			throws RowsExceededException, WriteException {
    		Label label;
    		label = new Label(column, row, s, timesBoldUnderline);
    		sheet.addCell(label);
    	}
    
    	private void addNumber(WritableSheet sheet, int column, int row,
    			Integer integer) throws WriteException, RowsExceededException {
    		Number number;
    		number = new Number(column, row, integer, times);
    		sheet.addCell(number);
    	}
    
    	private void addLabel(WritableSheet sheet, int column, int row, String s)
    			throws WriteException, RowsExceededException {
    		Label label;
    		label = new Label(column, row, s, times);
    		sheet.addCell(label);
    	}
    
    	public static void main(String[] args) throws WriteException, IOException {
    		WriteExcel test = new WriteExcel();
    		test.setOutputFile("c:/temp/lars.xls");
    		test.write();
    		System.out
    				.println("Please check the result file under c:/temp/lars.xls ");
    	}
    }
    

     

    讀取Excel
     

    package reader;
    
    import java.io.File;
    import java.io.IOException;
    
    import jxl.Cell;
    import jxl.CellType;
    import jxl.Sheet;
    import jxl.Workbook;
    import jxl.read.biff.BiffException;
    
    public class ReadExcel {
    
    	private String inputFile;
    
    	public void setInputFile(String inputFile) {
    		this.inputFile = inputFile;
    	}
    
    	public void read() throws IOException  {
    		File inputWorkbook = new File(inputFile);
    		Workbook w;
    		try {
    			w = Workbook.getWorkbook(inputWorkbook);
    			// Get the first sheet
    			Sheet sheet = w.getSheet(0);
    			// Loop over first 10 column and lines
    
    			for (int j = 0; j < sheet.getColumns(); j++) {
    				for (int i = 0; i < sheet.getRows(); i++) {
    					Cell cell = sheet.getCell(j, i);
    					CellType type = cell.getType();
    					if (cell.getType() == CellType.LABEL) {
    						System.out.println("I got a label "
    								+ cell.getContents());
    					}
    
    					if (cell.getType() == CellType.NUMBER) {
    						System.out.println("I got a number "
    								+ cell.getContents());
    					}
    
    				}
    			}
    		} catch (BiffException e) {
    			e.printStackTrace();
    		}
    	}
    
    	public static void main(String[] args) throws IOException {
    		ReadExcel test = new ReadExcel();
    		test.setInputFile("c:/temp/lars.xls");
    		test.read();
    	}
    
    }
    
 
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved