使用java的poi工具包操作excel

使用java的poi工具包操作excel

1. 在maven依赖中导入poi依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

1. 使用poi操作excel

a.创建工作簿

HSSFWorkbook wb = new HSSFWorkbook();

b.创建sheet页

HSSFSheet sheet = wb.createSheet("Sheet1");

参数指定sheet页名字

c.创建行

HSSFRow row = sheet.createRow(0);

参数指定行数，第一行为0，第二行为1

row.setHeight((short) 1000);// 设定行的高度

d.在当前行创建单元格

HSSFCell cell = row.createCell(0);

参数指定单元格所在的列数

e.给单元格填充内容

cell.setCellValue("内容");

f.设置单元格的样式

首先定义一个样式对象

HSSFCellStyle style = wb.createCellStyle();

如果需要设置字体，也可以定义一个字体对象

HSSFFont font = wb.createFont();

对字体样式进行设置

font.setFontName("宋体");
font.setFontHeight((short) 80);
font.setFontHeightInPoints((short) 24);

然后将字体样式赋值给单元格样式

style.setFont(font);

设置单元格样式

对齐方式

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平

 

// 设置边框
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

 

style.setWrapText(true);// 自动换行

 

使单元格样式在所在单元格生效

cell.setCellStyle(style);

 

g.合并单元格

sheet.addMergedRegion(new CellRangeAddress(4, 5, 12, 12));

使用addMergedRegion方法，参数为一个CellRangeAddress对象，该对象中的参数值依次代表要合并单元格的最低行号，最后一行行号，最前面一列的列号，最后一列的列号，行列号都是真实行列数-1

 

1. 以下是用poi创建一个excel表表头的小demo，仅供参考：

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Sheet1");
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeight((short) 80);
        font.setFontHeightInPoints((short) 24);
        // 设置标题样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 1000);// 设定行的高度
        HSSFCell cell = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12));
        cell.setCellValue("车辆动用申请汇总审批表");
        cell.setCellStyle(style);

        //第二行
        row = sheet.createRow(1);
        row.setHeight((short) 550);
        for (int i = 0; i < 13; i++) {
            cell = row.createCell(i);
            switch (i) {
                case 0:
                    cell.setCellValue("制表：" + summaryExcel.getTabulationUnit());
                    style = wb.createCellStyle();

                    // 设置位置
                    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
                    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);//水平
                    style.setWrapText(true);// 自动换行
                    // 设置字体
                    font = wb.createFont();
                    font.setFontName("黑体");
                    font.setFontHeight((short) 30);
                    font.setFontHeightInPoints((short) 12);
                    style.setLocked(true);
                    style.setFont(font);
                    cell.setCellStyle(style);
                    break;
//
                case 12:
                    cell.setCellValue(summaryExcel.getExportDate());
                    style = wb.createCellStyle();

                    // 设置位置
                    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
                    style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//水平
                    style.setWrapText(true);// 自动换行
                    // 设置字体
                    font = wb.createFont();
                    font.setFontName("黑体");
                    font.setFontHeight((short) 30);
                    font.setFontHeightInPoints((short) 12);
                    style.setLocked(true);
                    style.setFont(font);
                    cell.setCellStyle(style);
                    break;
                default:
                    break;
            }
        }


        //第三行
        row = sheet.createRow(2);
        row.setHeight((short) 400);
        for (int i = 0; i < 13; i++) {
            cell = row.createCell(i);
            switch (i) {
                case 0:
                    cell.setCellValue("序号");
                    cell.setCellStyle(getStyleBorderTop(wb));
                    break;
                case 1:
                    cell.setCellValue("用车单位");
                    cell.setCellStyle(getStyleBorderTop(wb));
                    break;
                case 2:
                    cell.setCellValue("带车干部");
                    cell.setCellStyle(getStyleBorderTop(wb));
                    break;
                case 3:
                    cell.setCellValue("联系电话");
                    cell.setCellStyle(getStyleBorderTop(wb));
                    break;
                case 4:
                    cell.setCellValue("车牌号");
                    cell.setCellStyle(getStyleBorderTop(wb));
                    break;
                case 5:
                    cell.setCellValue("车型");
                    cell.setCellStyle(getStyleBorderTop(wb));
                    break;
                case 6:
                    cell.setCellValue("驾驶员");
                    cell.setCellStyle(getStyleBorderTop(wb));
                    break;
                case 7:
                    cell.setCellValue("车数");
                    cell.setCellStyle(getStyleBorderTop(wb));
                    break;
                case 8:
                    cell.setCellValue("动用时间");
                    cell.setCellStyle(getStyleBorderTop(wb));
                    break;
                case 9:
                    cell.setCellStyle(getStyleBorderTop(wb));
                    break;
                case 10:
                    cell.setCellValue("乘坐人员");
                    cell.setCellStyle(getStyleBorderTop(wb));
                    break;
                case 11:
                    cell.setCellValue("任务");
                    cell.setCellStyle(getStyleBorderTop(wb));
                    break;
                case 12:
                    cell.setCellValue("路线");
                    style = wb.createCellStyle();
                    style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
                    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                    style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);//上边框
                    style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);//右边框
                    // 设置位置
                    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
                    style.setWrapText(true);// 自动换行
                    // 设置字体
                    font = wb.createFont();
                    font.setFontName("黑体");
                    font.setFontHeight((short) 30);
                    font.setFontHeightInPoints((short) 12);
                    style.setLocked(true);
                    style.setFont(font);
                    cell.setCellStyle(style);
                    break;
                default:
                    break;
            }
        }

        //第四行

        row = sheet.createRow(3);
        row.setHeight((short) 400);
        for (int i = 0; i < 13; i++) {
            cell = row.createCell(i);
            switch (i) {
                case 0:
                    sheet.setColumnWidth(i, 1300);
                    cell.setCellStyle(getStyleText(wb));
                    break;
                case 1:
                    sheet.setColumnWidth(i, 2500);
                    cell.setCellStyle(getStyleText(wb));
                    break;
                case 2:
                    sheet.setColumnWidth(i, 2500);
                    cell.setCellStyle(getStyleText(wb));
                    break;
                case 3:
                    sheet.setColumnWidth(i, 3500);
                    cell.setCellStyle(getStyleText(wb));
                    break;
                case 4:
                    sheet.setColumnWidth(i, 3000);
                    cell.setCellStyle(getStyleText(wb));
                    break;
                case 5:
                    sheet.setColumnWidth(i, 2000);
                    cell.setCellStyle(getStyleText(wb));
                    break;
                case 6:
                    sheet.setColumnWidth(i, 2200);
                    cell.setCellStyle(getStyleText(wb));
                    break;
                case 7:
                    sheet.setColumnWidth(i, 1500);
                    cell.setCellStyle(getStyleText(wb));
                    break;
                case 8:
                    sheet.setColumnWidth(i, 3500);
                    cell.setCellValue("日期");
                    cell.setCellStyle(getStyleText(wb));
                    break;
                case 9:
                    sheet.setColumnWidth(i, 1800);
                    cell.setCellValue("时间");
                    cell.setCellStyle(getStyleText(wb));
                    break;
                case 10:
                    sheet.setColumnWidth(i, 2500);
                    cell.setCellStyle(getStyleText(wb));
                    break;
                case 11:
                    sheet.setColumnWidth(i, 2000);
                    cell.setCellStyle(getStyleText(wb));
                    break;
                case 12:
                    sheet.setColumnWidth(i, 3000);
                    cell.setCellStyle(getStyleBorderRight(wb));
                default:
                    break;
            }
        }

sheet.addMergedRegion(new CellRangeAddress(4, carExportParamsList.size() + 3, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(4, carExportParamsList.size() + 3, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(4, carExportParamsList.size() + 3, 2, 2));
sheet.addMergedRegion(new CellRangeAddress(4, carExportParamsList.size() + 3, 3, 3));
sheet.addMergedRegion(new CellRangeAddress(4, carExportParamsList.size() + 3, 7, 7));
sheet.addMergedRegion(new CellRangeAddress(4, carExportParamsList.size() + 3, 8, 8));
sheet.addMergedRegion(new CellRangeAddress(4, carExportParamsList.size() + 3, 9, 9));
sheet.addMergedRegion(new CellRangeAddress(4, carExportParamsList.size() + 3, 11, 11));
sheet.addMergedRegion(new CellRangeAddress(4, carExportParamsList.size() + 3, 12, 12));