在Java中使用Apache POI导入导出Excel（二）

本文将继续介绍POI的使用，上接在Java中使用Apache POI导入导出Excel（一）

使用Apache POI组件操作Excel（二）

14、读取和重写工作簿

try (InputStream inp = new FileInputStream("workbook.xls")) {
//InputStream inp = new FileInputStream("workbook.xlsx");Workbook wb = WorkbookFactory.create(inp);Sheet sheet = wb.getSheetAt(0);Row row = sheet.getRow(2);Cell cell = row.getCell(3);if (cell == null)cell = row.createCell(3);cell.setCellType(CellType.STRING);cell.setCellValue("a test");// Write the output to a filetry (OutputStream fileOut = new FileOutputStream("workbook.xls")) {wb.write(fileOut);}
}

15、在单元格中使用换行符

Workbook wb = new XSSFWorkbook(); 
Sheet sheet = wb.createSheet();Row row = sheet.createRow(2);Cell cell = row.createCell(2);
cell.setCellValue("Use \n with word wrap on to create a new line");//to enable newlines you need set a cell styles with wrap=true
CellStyle cs = wb.createCellStyle();
cs.setWrapText(true);
cell.setCellStyle(cs);//increase row height to accommodate two lines of text
row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));//adjust column width to fit the content
sheet.autoSizeColumn(2);try (OutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx")) {wb.write(fileOut);
}
wb.close();

16、数据格式

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("format sheet");CellStyle style;
DataFormat format = wb.createDataFormat();Row row;
Cell cell;int rowNum = 0;
int colNum = 0;row = sheet.createRow(rowNum++);cell = row.createCell(colNum);
cell.setCellValue(11111.25);style = wb.createCellStyle();
style.setDataFormat(format.getFormat("0.0"));
cell.setCellStyle(style);row = sheet.createRow(rowNum++);cell = row.createCell(colNum);cell.setCellValue(11111.25);style = wb.createCellStyle();
style.setDataFormat(format.getFormat("#,##0.0000"));
cell.setCellStyle(style);try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {wb.write(fileOut);
}
wb.close();

17、使工作表适合一页

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("format sheet");PrintSetup ps = sheet.getPrintSetup();sheet.setAutobreaks(true);ps.setFitHeight((short)1);
ps.setFitWidth((short)1);// Create various cells and rows for spreadsheet.
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {wb.write(fileOut);
}
wb.close();

18、设置打印区域

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");//sets the print area for the first sheet
wb.setPrintArea(0, "$A$1:$C$2");//Alternatively:
wb.setPrintArea(0, //sheet index0, //start column1, //end column0, //start row0  //end row
);try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {wb.write(fileOut);
}
wb.close();

19、在页脚上设置页码

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("format sheet");Footer footer = sheet.getFooter();
footer.setRight( "Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages() );// Create various cells and rows for spreadsheet.
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {wb.write(fileOut);
}
wb.close();

20、使用便捷函数

便利函数提供 实用程序功能，例如在合并周围设置边框 区域和更改样式属性而不明确 创建新样式。

Workbook wb = new XSSFWorkbook()
Sheet sheet1 = wb.createSheet( "new sheet" );// Create a merged region
Row row = sheet1.createRow( 1 );
Row row2 = sheet1.createRow( 2 );Cell cell = row.createCell( 1 );
cell.setCellValue( "This is a test of merging" );
CellRangeAddress region = CellRangeAddress.valueOf("B2:E5");sheet1.addMergedRegion( region );// Set the border and border colors.
RegionUtil.setBorderBottom( BorderStyle.MEDIUM_DASHED, region, sheet1, wb );
RegionUtil.setBorderTop(    BorderStyle.MEDIUM_DASHED, region, sheet1, wb );
RegionUtil.setBorderLeft(   BorderStyle.MEDIUM_DASHED, region, sheet1, wb );
RegionUtil.setBorderRight(  BorderStyle.MEDIUM_DASHED, region, sheet1, wb );
RegionUtil.setBottomBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);
RegionUtil.setTopBorderColor(   IndexedColors.AQUA.getIndex(), region, sheet1, wb);
RegionUtil.setLeftBorderColor(  IndexedColors.AQUA.getIndex(), region, sheet1, wb);
RegionUtil.setRightBorderColor( IndexedColors.AQUA.getIndex(), region, sheet1, wb);// Shows some usages of HSSFCellUtil
CellStyle style = wb.createCellStyle();
style.setIndention((short)4);CellUtil.createCell(row, 8, "This is the value of the cell", style);Cell cell2 = CellUtil.createCell( row2, 8, "This is the value of the cell");CellUtil.setAlignment(cell2, HorizontalAlignment.CENTER);// Write out the workbook
try (OutputStream fileOut = new FileOutputStream( "workbook.xls" )) {wb.write( fileOut );
}
wb.close();

21、在工作表上向上或向下移动行

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("row sheet");// Create various cells and rows for spreadsheet.
// Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)sheet.shiftRows(5, 10, -5);

22、将图纸设置为已选中

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("row sheet");sheet.setSelected(true);

23、设置缩放放大倍数

Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("new sheet");sheet1.setZoom(75);   // 75 percent magnification