JAVA操作EXCEL

POI

POI 是针对微软OFFICE操作的一款JAVA API可以操作Word,Excel,PPT.
HSSF提供读写Microsoft Excel XLS格式档案的功能。
HWPF提供读写Microsoft Word DOC格式档案的功能。
HSLF提供读写Microsoft PowerPoint格式档案的功能。
POI读取Excel

//读取EXCEL文件POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));//加载EXCEL文件HSSFWorkbook wb = new HSSFWorkbook(fs);//获取指定的SheetHSSFSheet sheet = wb.getSheetAt(0);//HSSFSheet sheet = wb.getSheet("sheet1");//获取指定的行HSSFRow row = sheet.getRow(0);//获取指定的列HSSFCell cell = row.getCell(0);//获取列中的值String message = cell.getStringCellValue();

POI写入EXCEL

//创建WorkbookHSSFWorkbook wb = new HSSFWorkbook();//创建sheetHSSFSheet sheet= wb.createSheet('sheet1');/** *创建摘要*/wb.createInformationProperties();//创建文档信息DocumentSummaryInformation dsi=wb.getDocumentSummaryInformation();//摘要信息dsi.setCategory("***");dsi.setManager("***");dsi.setCompany("***");SummaryInformation si = wb.getSummaryInformation();//摘要信息si.setSubject("---");//主题si.setTitle("---");//标题si.setAuthor("---");//作者si.setComments("---");//备注/** *创建行*///创建第一行Row row= wb.createRow((short)0);//创建的一行的第一列Cell cell= row.createCell(0);//给第一行的第一列赋值cell.setCellValue("hello");/** *创建批注*/HSSFPatriarch patr = sheet.createDrawingPatriarch();HSSFClientAnchor anchor = patr.createAnchor(0, 0, 0, 0, 5, 1, 8,3);//创建批注位置HSSFComment comment = patr.createCellComment(anchor);//创建批注comment.setString(new HSSFRichTextString("-----"));//设置批注内容comment.setAuthor("---");//设置批注作者comment.setVisible(true);//设置批注默认显示cell.setCellComment(comment);//把批注赋值给单元格/** *设置页眉和页脚*/HSSFHeader header =sheet.getHeader();//得到页眉header.setLeft("---");header.setRight("---");header.setCenter("---");HSSFFooter footer =sheet.getFooter();//得到页脚footer.setLeft("---");footer.setRight("---");footer.setCenter("---");/** *设置列样式*/CellStyle style = wb.createCellStyle();//设置日期样式style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));//style.setDataFormat(wb.createDataFormat().getFormat("0.00%"));//自定义样式style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中style.setWrapText(true);//自动换行style.setIndention((short)5);//缩进style.setRotation((short)60);//文本旋转，这里的取值是从-90到90，而不是0-180度。style.setBorderTop(HSSFCellStyle.BORDER_DOTTED);//上边框style.setBorderBottom(HSSFCellStyle.BORDER_THICK);//下边框style.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);//左边框style.setBorderRight(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//右边框style.setTopBorderColor(HSSFColor.RED.index);//上边框颜色style.setBottomBorderColor(HSSFColor.BLUE.index);//下边框颜色style.setLeftBorderColor(HSSFColor.GREEN.index);//左边框颜色style.setRightBorderColor(HSSFColor.PINK.index);//右边框颜色style.setFillForegroundColor(HSSFColor.GREEN.index);//设置图案颜色style.setFillBackgroundColor(HSSFColor.RED.index);//设置图案背景色style.setFillPattern(HSSFCellStyle.SQUARES);//设置图案样式/** *设置字体 */ HSSFFont font = wb.createFont();font.setFontName("华文行楷");//设置字体名称font.setFontHeightInPoints((short)28);//设置字号font.setColor(HSSFColor.RED.index);//设置字体颜色font.setUnderline(FontFormatting.U_SINGLE);//设置下划线font.setTypeOffset(FontFormatting.SS_SUPER);//设置上标下标font.setStrikeout(true);//设置删除线style.setFont(font);/** *合并单元格 */ CellRangeAddress region=new CellRangeAddress(firstRow, lastRow, firstCol, lastCol); sheet.addMergedRegion(region);/**  设置sheet的属性*/sheet.setColumnWidth(1, 31 * 256);//设置第一列的宽度是31个字符宽度row.setHeightInPoints(50);//设置行的高度是50个点wb.setActiveSheet(0);//设置默认工作表wb.setSheetName(0, "sheet1");//重命名工作表sheet.setZoom(1,2);//50%显示比例sheet.setDisplayGridlines(false);//隐藏Excel网格线,默认值为truesheet.setGridsPrinted(true);//打印时显示网格线,默认值为false/** *生成下拉菜单 */CellRangeAddressList regions = new CellRangeAddressList(0, 65535,0, 0);//在第一列生成下拉菜单DVConstraint constraint =DVConstraint.createExplicitListConstraint(new String[] { "C++","Java", "C#" });HSSFDataValidation dataValidate = new HSSFDataValidation(regions,constraint);sheet.addValidationData(dataValidate);/** *生成超链接 */ CreationHelper createHelper = workbook.getCreationHelper();Hyperlink link =createHelper.createHyperlink(Hyperlink.LINK_URL);link.setAddress("http://poi.apache.org/");cell.setHyperlink(link);FileOutputStream fos = new FileOutputStream(filePath);wb.write(fos);fos.close();

JXL

JXL是一个专门针对Excel的API，并不支持对Word和PPT的操作。它是在POI基础上进一不包装，从性能上来说JXL更好。
JXL读取EXCEL

//选取Excel文件得到工作薄WorkbookWorkbook workbook = Workbook.getWorkbook(new File(filePath));//读取第一个sheetSheet sheet = workbook.getSheet(0);//获取指定的单元格 单获取的列不存在时会抛出空指针异常Cell cell = sheet.getCell(0,0);//把单元格中的信息以字符的形式读取出来String str = cell.getContents();//字符单元格if (cell.getType() == CellType. LABEL) {    LabelCell lc = (LabelCell) cell;    String str = lc. getString();}if (cell.getType() == CellType. DATE) {    DateCell dc = (DateCell) cell;    Date date= dc. getDate();}if (cell.getType() == CellType.NUMBER) {    NumberCell nc = (NumberCell) cell;    double num= nc.getValue();}workbook.close();

JXL写EXCEL

//方法一//创建可写入的Excel工作薄WritableWorkbook wwb = Workbook.createWorkbook(new File(filePath));//方法二//将WritableWorkbook直接写入到输出流OutputStream os = new FileOutputStream(filePath);WritableWorkbook wwb = Workbook.createWorkbook(os);WritableSheet ws = wwb.createSheet("sheet1", 0);//创建sheet//添加文本类单元格Label label = new Label(0, 0, "---");ws.addCell(label);//添加设置样式了的文本WritableFont wf = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, true);//设置文本的颜色//WritableFont wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD, //false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);WritableCellFormat wcf = new WritableCellFormat(wf);label1 = new Label(0, 1, "---", wcf);ws.addCell(label1);//添加数字NumberFormat nf = new NumberFormat("#.##");WritableCellFormat wcfn = new WritableCellFormat(nf);Number numC = new Number(0, 2, 0.0002, wcfn);ws.addCell(numC);/添加日期DateFormat df = new DateFormat("dd MM yyyy hh:mm:ss");WritableCellFormat wcfd = new WritableCellFormat(df);DateTime labelD = new DateTime(0, 3, new Date(), wcfd);ws.addCell(labelD);//表示将从第x+1列，y+1行到m+1列，n+1行合并ws.mergeCells(int x,int y,int m,int n);//添加单元格样式WritableFont wfont = new WritableFont(WritableFont.createFont("楷书"), 20);WritableCellFormat wc = new WritableCellFormat(wfont);wc.setAlignment(Alignment.CENTRE); // 设置居中wc.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线wc.setBackground(jxl.format.Colour.RED); // 设置单元格的背景颜色labelS = new Label(0, 4, "---", wc);ws.addCell(labelS);wwb.write();wwb.close();