javaPoi笔记

public class PoiTest {
    
    // 输出文件
    public void outputFile(HSSFWorkbook wb, String fileName) throws Exception{
        FileOutputStream fileOut = new FileOutputStream("d:\test\"+fileName);
        wb.write(fileOut);
        fileOut.close();
    }

    @Test
    public void test1() throws Exception{
        // 定义一个新的工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        FileOutputStream fileOut = new FileOutputStream("d:\test\test.xls");
        wb.write(fileOut);
        fileOut.close();
    }

    // create a excel with sheet
    @Test
    public void test2() throws Exception{
        // 定义一个新的工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        wb.createSheet("sheet1");
        wb.createSheet("sheet2");
        FileOutputStream fileOut = new FileOutputStream("d:\test\test2.xls");
        wb.write(fileOut);
        fileOut.close();
    }

    // create a file with row and cell
    @Test
    public void test3() throws Exception{
        // 定义一个新的工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 创建一个行
        HSSFRow row = sheet.createRow(0);
        // 创建一个单元格,第1列
        HSSFCell cell = row.createCell(0);
        // 设置单元格的值
        cell.setCellValue(1);

        outputFile(wb, "test3.xls");
    }

    @Test
    public void test4() throws Exception{
        // 定义一个新的工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 创建一个行
        HSSFRow row = sheet.createRow(0);
        // 创建一个单元格,第1列
        row.createCell(0).setCellValue(1);
        row.createCell(1).setCellValue(1.2); // 第二列
        row.createCell(2).setCellValue("字符串类型"); // 第三列
        row.createCell(3).setCellValue(false);
        row.createCell(4).setCellValue(new Date().toString());

        outputFile(wb, "test4.xls");
    }

    // 给单元格设置时间格式
    @Test
    public void test5() throws Exception{
        // 定义一个新的工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 创建一个行
        HSSFRow row = sheet.createRow(0);
        // 设置单元格样式
        HSSFCreationHelper creationHelper = wb.getCreationHelper();
        HSSFCellStyle cellStyle = wb.createCellStyle(); // 单元格样式类
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));

        row.createCell(0).setCellValue(new Date());

        HSSFCell cell = row.createCell(1);// 第二列
        cell.setCellStyle(cellStyle);
        cell.setCellValue(new Date());


        outputFile(wb, "test5.xls");
    }

    private String getValue(HSSFCell cell) {
        switch (cell.getCellType()) {
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            default:
                return String.valueOf(cell.getStringCellValue());
        }
    }

    // 遍历工作簿
    @Test
    public void test6() throws Exception{
        FileInputStream is = new FileInputStream("d:\test\二货名单.xls");
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        // get first sheet
        HSSFSheet hssfSheet = wb.getSheetAt(0);

        // 遍历row
        for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            HSSFRow hssfRow = hssfSheet.getRow(rowNum);
            if (hssfRow == null) {
                continue;
            }
            // 遍历列cell
            for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
                HSSFCell hssfCell = hssfRow.getCell(cellNum);
                if (hssfCell == null) {
                    continue;
                }
                System.out.print(" "+ getValue(hssfCell));
            }
            System.out.println();
        }
    }

    // 直接提取工作簿的文本
    @Test
    public void test7() throws Exception{
        FileInputStream is = new FileInputStream("d:\test\二货名单.xls");
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);

        ExcelExtractor excelExtractor = new ExcelExtractor(wb);
        excelExtractor.setIncludeSheetNames(false); // 不抽取sheet页的名字
        System.out.println(excelExtractor.getText());
    }

    // 设置对齐方式
    @Test
    public void test8() throws Exception{
        // 定义一个新的工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 创建一个行
        HSSFRow row = sheet.createRow(2);
        // 设置行高
        row.setHeightInPoints(30);

        createCell(wb, row, (short) 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
        createCell(wb, row, (short) 1, HorizontalAlignment.FILL, VerticalAlignment.CENTER);
        createCell(wb, row, (short) 2, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
        createCell(wb, row, (short) 3, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);


        outputFile(wb, "test8.xls");
    }

    /**
     * 创建一个单元格并为其设置指定对齐方式
     * @param wb 工作簿
     * @param row 行
     * @param column 列
     * @param halign 水平对齐方式
     * @param valign 垂直对齐方式
     */
    private void createCell(Workbook wb, Row row, short column, HorizontalAlignment halign, VerticalAlignment valign) {
        Cell cell = row.createCell(column);
        cell.setCellValue(new HSSFRichTextString("Align It"));
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cell.setCellStyle(cellStyle);
    }


    // 设置边框和颜色
    @Test
    public void test9() throws Exception{
        // 定义一个新的工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 创建一个行
        HSSFRow row = sheet.createRow(1);

        HSSFCell cell = row.createCell(1);
        cell.setCellValue(4);

        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置边框的颜色和样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());

        cellStyle.setBorderTop(BorderStyle.DOTTED);
        cellStyle.setTopBorderColor(IndexedColors.RED.getIndex());

        cell.setCellStyle(cellStyle);

        outputFile(wb, "test9.xls");
    }

    // 设置背景颜色
    @Test
    public void test10() throws Exception{
        // 定义一个新的工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 创建一个行
        HSSFRow row = sheet.createRow(1);

        HSSFCell cell = row.createCell(1);
        cell.setCellValue("XX");

        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex()); // 背景色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 填充样式

        cell.setCellStyle(cellStyle);

        outputFile(wb, "test10.xls");
    }

    // 合并单元格
    @Test
    public void test11() throws Exception{
        // 定义一个新的工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 创建一个行
        HSSFRow row = sheet.createRow(1);

        HSSFCell cell = row.createCell(1);
        cell.setCellValue("单元格合并测试");

        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 2));

        outputFile(wb, "test11.xls");
    }

    // 设置字体
    @Test
    public void test12() throws Exception{
        // 定义一个新的工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 创建一个行
        HSSFRow row = sheet.createRow(1);

        // 创建字体处理类
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 24);
        font.setFontName("Courier New");
        font.setItalic(true);
        font.setStrikeout(true);

        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFont(font);

        HSSFCell cell = row.createCell((short) 1);
        cell.setCellValue("This is test of fonts");
        cell.setCellStyle(cellStyle);

        outputFile(wb, "test12.xls");
    }

    // 读取和重写工作簿
    @Test
    public void test13() throws Exception{
        FileInputStream inp = new FileInputStream("d:\test\test13.xls");
        POIFSFileSystem fs = new POIFSFileSystem(inp);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = sheet.getRow(0);
        if (null == row) {
            row = sheet.createRow(0);
        }
        HSSFCell cell = row.getCell(0);
        if (null == cell) {
            cell = row.createCell(3);
        }
        cell.setCellType(CellType.STRING);
        cell.setCellValue("测试单元格");

        outputFile(wb, "test13.xls");
    }

    // 单元格内换行
    @Test
    public void test14() throws Exception{
        // 定义一个新的工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 创建一个行
        HSSFRow row = sheet.createRow(2);
        HSSFCell cell = row.createCell(2);
        cell.setCellValue("我要换行 
 成功了吗?");

        HSSFCellStyle cs = wb.createCellStyle();
        // 设置可以换行
        cs.setWrapText(true);
        cell.setCellStyle(cs);

        // 调整下行的高度
        row.setHeightInPoints(2 * sheet.getDefaultRowHeightInPoints());
        // 调整单元格宽度
        sheet.autoSizeColumn(2);

        outputFile(wb, "test14.xls");
    }

    // 设置数据格式
    @Test
    public void test15() throws Exception{
        // 定义一个新的工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");
        CellStyle style;
        DataFormat dataFormat = wb.createDataFormat();
        Row row;
        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(dataFormat.getFormat("0.0")); // 设置数据格式
        cell.setCellStyle(style);

        row = sheet.createRow(rowNum++);
        cell = row.createCell(colNum);
        cell.setCellValue(111111.25);
        style = wb.createCellStyle();
        style.setDataFormat(dataFormat.getFormat("#,##0.000")); // 设置数据格式
        cell.setCellStyle(style);

        outputFile(wb, "test15.xls");
    }

    // 循环填数据
    private void fillExcelData(ResultSet rs, Workbook wb, String[] headers) throws Exception {
        int rowIndex = 0;
        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow(rowIndex++);
        for (int i = 0; i < headers.length; i++) {
            row.createCell(i).setCellValue(headers[i]);
        }
        while (rs.next()) {
            row = sheet.createRow(rowIndex++);
            for (int i = 0; i < headers.length; i++) {
                row.createCell(i).setCellValue(rs.getObject(i+1).toString());
            }
        }
    }

    // 导出excel
    private void export(HttpServletResponse response, Workbook wb, String fileName) throws Exception {
        response.setHeader("Content-Disposition", "attachment;filename="+ new String(fileName.getBytes("utf-8"), "iso8859-1"));
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream outputStream = response.getOutputStream();
        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    // 用模板填数据
    private Workbook fillExcelDataWithTemplate(ResultSet rs, String templateFileName) throws Exception {
        FileInputStream inp = new FileInputStream("d:\test\模板.xls");
        POIFSFileSystem fs = new POIFSFileSystem(inp);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        // 获取列数
        int cellNum = sheet.getRow(0).getLastCellNum();
        int rowIndex = 1;
        while (rs.next()) {
            Row row = sheet.createRow(rowIndex++);
            for (int i = 0; i < cellNum; i++) {
                row.createCell(i).setCellValue(rs.getObject(i+1).toString());
            }
        }
        return wb;
    }

}