1、赋值格式
2、处理日期格式
public void create_Excel() throws Exception{ Workbook workbook = new HSSFWorkbook(); // 定义一个工作博 Sheet sheet = workbook.createSheet("第一个Sheet页"); // 创建一个sheet页 Row row = sheet.createRow(0); // 创建一个行 Cell cell = row.createCell(0); // 创建一个单元格 第1列 cell.setCellValue(new Date()); // 单元格赋值 // 日期格式化 CreationHelper creationHelper = workbook.getCreationHelper(); // 创建一个格式化的类 CellStyle cellStyle = workbook.createCellStyle(); // 单元格样式类 cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss")); cell = row.createCell(1); // 第二列 cell.setCellValue(new Date()); cell.setCellStyle(cellStyle); // 设置单元格样式 cell = row.createCell(2); // 第三列 cell.setCellValue(Calendar.getInstance()); cell.setCellStyle(cellStyle); FileOutputStream fileOut = new FileOutputStream("WebRoot/document/newExcel.xls"); workbook.write(fileOut); fileOut.flush(); fileOut.close(); System.out.println("Excel创建完毕"); }
3、整页Excel数据抽取
使用ExcelExtractor抽取工具抽取,默认抽取所有内容,抽取的过程中可以指定过路条件那些不进行抽取,如
extractor.setIncludeSheetNames(false); // 过滤sheet页的名字
public void extrator_Excel() throws Exception { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("WebRoot/document/App_User.xls")); HSSFWorkbook hwbook = new HSSFWorkbook(fs); HSSFSheet hsheet = hwbook.getSheetAt(0); // 获取第一个Sheet页 ExcelExtractor extractor = new ExcelExtractor(hwbook); System.out.println(extractor.getText()); System.out.println("\n———————————————— Excel读取抽取完毕————————————————"); }
4、遍历Excel
/** * 读取xls */ public void read_Excel() throws Exception { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("WebRoot/document/App_User.xls")); HSSFWorkbook hwbook = new HSSFWorkbook(fs); HSSFSheet hsheet = hwbook.getSheetAt(0); // 获取第一个Sheet页 if (hsheet != null) { // 遍历行Row for (int i = 0; i <= hsheet.getLastRowNum(); i++) { HSSFRow hrow = hsheet.getRow(i); if (hrow != null) { // 遍历列Cell for (int j = 0; j <= hrow.getLastCellNum(); j++) { HSSFCell hcell = hrow.getCell(j); if (hcell != null) { System.out.print(getValue(hcell) + " "); } } } System.out.println(); } } System.out.println("\n———————————————— Excel读取完毕————————————————"); } /** * 根据单元格类型获取值 * * @param hcell * @return */ public String getValue(HSSFCell hcell) { if (hcell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { return String.valueOf(hcell.getBooleanCellValue()); } else if (hcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { return String.valueOf(hcell.getNumericCellValue()); } else { return String.valueOf(hcell.getStringCellValue()); } }
单元格对齐
/** * 创建Excel单元格 */ private void createCell(Workbook wb,Row row,short column,Object value,short halign,short valign){ Cell cell = row.createCell(column); // 创建单元格 if (value instanceof Integer) { cell.setCellValue((Integer)value); }else if (value instanceof Double) { cell.setCellValue((Double)value); }else if (value instanceof Boolean) { cell.setCellValue((Boolean)value); }else if (value instanceof Calendar) { cell.setCellValue((Calendar)value); }else if (value instanceof Date) { cell.setCellValue((Date)value); }else { cell.setCellValue(value.toString()); } CellStyle cellStyle = wb.createCellStyle(); // 创建单元格样式 cellStyle.setAlignment(halign); // 单元格水平方向对齐 cellStyle.setVerticalAlignment(valign); // 单元格垂直方向对齐 cell.setCellStyle(cellStyle); // 设置单元格样式 } private void create_Excel() throws Exception { Workbook workbook = new HSSFWorkbook(); // 定义一个工作博 Sheet sheet = workbook.createSheet("第一个Sheet页"); // 创建一个sheet页 Row row = sheet.createRow(3); // 创建一个行 row.setHeightInPoints(30); // 设置行的高度 createCell(workbook, row, (short)1, "X-rapido", HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_BOTTOM); createCell(workbook, row, (short)2, 100, HSSFCellStyle.ALIGN_FILL, HSSFCellStyle.VERTICAL_CENTER); createCell(workbook, row, (short)3, true, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_TOP); createCell(workbook, row, (short)4, new Date(), HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER); createCell(workbook, row, (short)5, Calendar.getInstance(), HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_BOTTOM); FileOutputStream fileOut = new FileOutputStream("WebRoot/document/newExcel.xls"); workbook.write(fileOut); fileOut.flush(); fileOut.close(); System.out.println("Excel创建完毕"); }
单元格边框处理
Cell cell = row.createCell(1); // 创建一个单元格 cell.setCellValue("X-rapido"); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); // 设置边框样式 cellStyle.setLeftBorderColor(IndexedColors.BLUE_GREY.getIndex()); // 设置边框颜色 cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setRightBorderColor(IndexedColors.GREEN.getIndex()); cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED); cellStyle.setTopBorderColor(IndexedColors.YELLOW.getIndex()); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex()); cell.setCellStyle(cellStyle);
单元格填充色和颜色处理
Cell cell = row.createCell(1); // 创建一个单元格 cell.setCellValue("X-rapido"); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); // 设置背景色 cellStyle.setFillPattern(CellStyle.BIG_SPOTS); cell.setCellStyle(cellStyle); Cell cell2 = row.createCell(2); // 创建一个单元格 cell2.setCellValue("X-rapido"); CellStyle cellStyle2 = workbook.createCellStyle(); cellStyle2.setFillForegroundColor(IndexedColors.RED.getIndex()); cellStyle2.setFillPattern(CellStyle.SOLID_FOREGROUND); cell2.setCellStyle(cellStyle2);
单元格合并
Cell cell = row.createCell(1); // 创建一个单元格 cell.setCellValue("X-rapido"); // CellRangeAddress(起始行,结束行,起始列,结束列) sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 2)); // 和并列
字体处理
// 创建一个字体处理类 Font font = workbook.createFont(); font.setFontHeightInPoints((short)30); font.setFontName("Courier New"); font.setItalic(true); // 斜体 font.setStrikeout(true); // 删除线 CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFont(font); Cell cell = row.createCell((short)1); cell.setCellValue("This is test of fonts"); cell.setCellStyle(cellStyle);
读取和重写工作薄
覆盖原有存在的文件
单元格中使用换行
Row row = sheet.createRow(1); // 创建一个行 row.setHeightInPoints(sheet.getDefaultRowHeightInPoints() * 2); Cell cell = row.createCell((short)1); cell.setCellValue("我要换行\n成功了吗?"); CellStyle cellStyle = workbook.createCellStyle(); // 设置可以换行 cellStyle.setWrapText(true); cell.setCellStyle(cellStyle);
创建用户自定义数据格式
Cell cell; short rowNum = 0; short colNum = 0; row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(111111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("0.0")); // 设置数据格式 cell.setCellStyle(style); row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(111111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("#.##0.000")); cell.setCellStyle(style);