poi,与jxl一样都可以实现java操作excel文件;下载地址(http://apache.mirror.phpchina.com/jakarta/poi/release/src/ )
poi介绍:Jakarta POI - Java API To Access Microsoft Format Files
有以下几个包:
POIFS: for OLE 2 Documents ,POIFS is the oldest and most stable part of the project. It is our port of the OLE 2 Compound Document Format to pure Java.
HSSF :for Excel Documents,HSSF is our port of the Microsoft Excel 97(-2002) file format (BIFF8) to pure Java.
HWPF :for Word Documents ,HWPF is our port of the Microsoft Word 97 file format to pure Java.
HPSF :for Document Properties,HPSF is our port of the OLE 2 property set format to pure Java
下面介绍其应用(hssf):
创建工作簿(workbook):
HSSFWorkbook wb = new HSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
创建表单(sheet):
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("new sheet"); HSSFSheet sheet2 = wb.createSheet("second sheet"); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
创建单元格(cell):
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); // 创建一行并向其中添加一些cell,Rows 下标从0开始(Create a row and put some cells in it. Rows are 0 based.) HSSFRow row = sheet.createRow((short)0); // 创建一个Cell,并向其中添加值(Create a cell and put a value in it.) HSSFCell cell = row.createCell((short)0); cell.setCellValue(1); //或者直接用一行(java语句)完成 (Or do it on one line.) row.createCell((short)1).setCellValue(1.2); row.createCell((short)2).setCellValue("This is a string"); row.createCell((short)3).setCellValue(true); // 输出到文件(Write the output to a file) FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
创建Date类型单元格
。。。
HSSFCell cell = row.createCell((short)0); cell.setCellValue(new Date()); // we style the second cell as a date (and time). It is important to // create a new cell style from the workbook otherwise you can end up // modifying the built in style and effecting not only this cell but other cells. HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); cell = row.createCell((short)1); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle);
。。。
创建不同类型单元格:
HSSFRow row = sheet.createRow((short)2); row.createCell((short) 0).setCellValue(1.1); row.createCell((short) 1).setCellValue(new Date()); row.createCell((short) 2).setCellValue("a string"); row.createCell((short) 3).setCellValue(true); row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR);
指定align:
public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); HSSFRow row = sheet.createRow((short) 2); createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER); createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_CENTER_SELECTION); createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_FILL); createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_GENERAL); createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_JUSTIFY); createCell(wb, row, (short) 5, HSSFCellStyle.ALIGN_LEFT); createCell(wb, row, (short) 6, HSSFCellStyle.ALIGN_RIGHT); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); } /** * Creates a cell and aligns it a certain way. * * @param wb the workbook * @param row the row to create the cell in * @param column the column number to create the cell in * @param align the alignment for the cell. */ private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align) { HSSFCell cell = row.createCell(column); cell.setCellValue("Align It"); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(align); cell.setCellStyle(cellStyle); }
合并单元格:
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); HSSFRow row = sheet.createRow((short) 1); HSSFCell cell = row.createCell((short) 1); cell.setCellValue("This is a test of merging"); //public Region(int rowFrom, short colFrom, int rowTo, short colTo) sheet.addMergedRegion(new Region(1,(short)1,1,(short)2)); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 读取并修改文件: POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("workbook.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(2); HSSFCell cell = row.getCell((short)3); if (cell == null) cell = row.createCell((short)3); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("a test"); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); ...................................太多了,请大家到poi自带文档中看吧。 下载地址(http://apache.mirror.phpchina.com/jakarta/poi/release/src/ )