Java读写Excel
本文主要向你演示如何使用JavaExcel API来读写Excel文件。关于JavaExcel API,这是一个开源的lib库。其相关的feature如下:
你可以在这里下载: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(); } }
(转载本站文章请注明作者和出处 酷 壳 – CoolShell ,请勿用于任何商业用途)
《Java读写Excel》的相关评论
apache poi库操作excel更好点,支持2007的格式
@mzhou
同感,感觉poi更好些。
有没有一些操作word比较好的java类库?
转别人的贴最好说明一下出处
如果是生成比较复杂的excel文档的话,用jasperreports(http://community.jaspersoft.com/project/jasperreports-library)会更好一些吧。
用apache poi的话样式格式设置太麻烦了。