在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 file
    try (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 index
        0, //start column
        1, //end column
        0, //start row
        0  //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