JAVA入门13 操作excel表格 ExcelUtil

导包

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>

例子

demo1

package com.zz.excel;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
 * @Description: java类作用描述
 * @Author: Bsea
 * @CreateDate: 2019/8/26$ 20:55$
 */
public class Demo1 {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        //创建一个excel文件
        HSSFWorkbook wb= new HSSFWorkbook();
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream("c:\\tmp1\\workbook2019.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch ( IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        // FileOutputStream fileOut= new FileOutputStream("c:/workbook.xls");


    }
}

demo2

package com.zz.excel;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
 * @Description: java类作用描述
 * @Author: Bsea
 * @CreateDate: 2019/8/26$ 20:58$
 */
public class Demo2 {
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        HSSFWorkbook wb = new HSSFWorkbook();    //建立新HSSFWorkbook对象
        HSSFSheet sheet = wb.createSheet("java10");  //建立新的sheet对象
        HSSFRow row = sheet.createRow((short)0);
        //在sheet里创建一行，参数为行号（第一行，此处可想象成数组）
        HSSFCell cell = row.createCell((short)0);
        //在row里建立新cell（单元格），参数为列号（第一列）
        cell.setCellValue("2019");
        //cell.set
        //设置cell的整数类型的值
        row.createCell((short)1).setCellValue(1.2);     //设置cell浮点类型的值
        row.createCell((short)2).setCellValue("test");   //设置cell字符类型的值
        row.createCell((short)3).setCellValue(true);    //设置cell布尔类型的值
        HSSFCellStyle cellStyle = wb.createCellStyle(); //建立新的cell样式
        cellStyle.setDataFormat(HSSFDataFormat. getBuiltinFormat("m/d/yy h:mm"));
        //设置cell样式为定制的日期格式
        HSSFCell dCell =row.createCell((short)4);
        dCell.setCellValue(new Date());            //设置cell为日期类型的值
        dCell.setCellStyle(cellStyle);              //设置该cell日期的显示格式
        HSSFCell csCell =row.createCell((short)5);
        //csCell.setEncoding(HSSFCell.ENCODING_UTF_16);
        //设置cell编码解决中文高位字节截断
        csCell.setCellValue("中文测试_Chinese Words Test");  //设置中西文结合字符串
        row.createCell((short)6).setCellType(HSSFCell.CELL_TYPE_ERROR);
        //建立错误cell

        try {
            FileOutputStream fileOut = new FileOutputStream("c:\\tmp1\\workbook1.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
}

demo3

package com.zz.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @Description: java类作用描述
 * @Author: Bsea
 * @CreateDate: 2019/8/26$ 21:29$
 */
public class Demoxlsx {

    public static void main(String[] args) throws IOException, InvalidFormatException {
        String xlsPath = "C:/tmp1/测试.xlsx";

        // excel文档对象
        XSSFWorkbook wk = new XSSFWorkbook();
        // sheet对象
        XSSFSheet sheet = wk.createSheet("测试");

        // 字体样式
        XSSFFont xssfFont = wk.createFont();
        // 加粗
        xssfFont.setBold(true);
        // 字体名称
        xssfFont.setFontName("楷体");
        // 字体大小
        xssfFont.setFontHeight(12);

        // 表头样式
        XSSFCellStyle headStyle = wk.createCellStyle();
        // 设置字体css
        headStyle.setFont(xssfFont);
        // 竖向居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 横向居中
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        // 边框
//        headStyle.setBorderBottom(Borde*rStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderTop(BorderStyle.THIN);

        // 内容字体样式
        XSSFFont contFont = wk.createFont();
        // 加粗
        contFont.setBold(false);
        // 字体名称
        contFont.setFontName("楷体");
        // 字体大小
        contFont.setFontHeight(11);
        // 内容样式
        XSSFCellStyle contentStyle = wk.createCellStyle();
        // 设置字体css
        contentStyle.setFont(contFont);
        // 竖向居中
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 横向居中
        //contentStyle.setAlignment(HorizontalAlignment.CENTER);
        // 边框
        contentStyle.setBorderBottom(BorderStyle.THIN);
        contentStyle.setBorderLeft(BorderStyle.THIN);
        contentStyle.setBorderRight(BorderStyle.THIN);
        contentStyle.setBorderTop(BorderStyle.THIN);

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

        // 数字样式
        XSSFCellStyle numStyle = wk.createCellStyle();
        // 设置字体css
        numStyle.setFont(contFont);
        // 竖向居中
        numStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 横向居中
        numStyle.setAlignment(HorizontalAlignment.CENTER);
        // 边框
        numStyle.setBorderBottom(BorderStyle.THIN);
        numStyle.setBorderLeft(BorderStyle.THIN);
        numStyle.setBorderRight(BorderStyle.THIN);
        numStyle.setBorderTop(BorderStyle.THIN);

        // 标题字体样式
        XSSFFont titleFont = wk.createFont();
        // 加粗
        titleFont.setBold(false);
        // 字体名称
        titleFont.setFontName("宋体");
        // 字体大小
        titleFont.setFontHeight(16);

        // 标题样式
        XSSFCellStyle titleStyle = wk.createCellStyle();
        titleStyle.setFont(titleFont);
        // 竖向居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 横向居中
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        // 边框
        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);

        // 合并单元格（第一行、标题）
        CellRangeAddress cAddress = new CellRangeAddress(0, 0, 0, 3);
        sheet.addMergedRegion(cAddress);

        // 合并单元格（第一个分类）
        CellRangeAddress cAddress2 = new CellRangeAddress(2, 3, 0, 0);
        sheet.addMergedRegion(cAddress2);

        // 创建第一行
        XSSFRow row1 = sheet.createRow(0);
        // 创建第一行第一列
        XSSFCell row1Cell1 = row1.createCell(0);
        row1Cell1.setCellValue("title");
        row1Cell1.setCellStyle(titleStyle);
        XSSFCell row1Cell2 = row1.createCell(1);
        // 为了保证合并的单元格能有效追加外框、被合并的单元格、内容要设置为空
        row1Cell2.setCellValue("");
        row1Cell2.setCellStyle(titleStyle);
        XSSFCell row1Cell3 = row1.createCell(2);
        row1Cell3.setCellValue("");
        row1Cell3.setCellStyle(titleStyle);
        XSSFCell row1Cell4 = row1.createCell(3);
        row1Cell4.setCellValue("");
        row1Cell4.setCellStyle(titleStyle);

        // 创建第二行
        XSSFRow row2 = sheet.createRow(1);
        // 创建第二行第一列
        XSSFCell row2Cell1 = row2.createCell(0);
        row2Cell1.setCellValue("分类");
        row2Cell1.setCellStyle(headStyle);
        // 列宽
        sheet.setColumnWidth(row2Cell1.getColumnIndex(), 60 * 50);
        // 创建第二行第二列
        XSSFCell row2Cell2 = row2.createCell(1);
        row2Cell2.setCellValue("内容");
        row2Cell2.setCellStyle(headStyle);
        // 列宽
        sheet.setColumnWidth(row2Cell2.getColumnIndex(), 356 * 50);
        // 创建第二行第三列
        XSSFCell row2Cell3 = row2.createCell(2);
        row2Cell3.setCellValue("标准");
        row2Cell3.setCellStyle(headStyle);
        // 列宽
        sheet.setColumnWidth(row2Cell3.getColumnIndex(), 70 * 50);
        // 创建第二行第四列
        XSSFCell row2Cell4 = row2.createCell(3);
        row2Cell4.setCellValue("备注");
        row2Cell4.setCellStyle(headStyle);
        // 列宽
        sheet.setColumnWidth(row2Cell4.getColumnIndex(), 70 * 50);

        // 创建第三行
        XSSFRow row3 = sheet.createRow(2);
        // 创建第三行第一列
        XSSFCell row3Cell1 = row3.createCell(0);
        row3Cell1.setCellValue("分类1");
        row3Cell1.setCellStyle(contentStyle);
        // 创建第三行第二列
        XSSFCell row3Cell2 = row3.createCell(1);
        row3Cell2.setCellValue("AAAAAAAAAAAAAAAAAAAAAA");
        row3Cell2.setCellStyle(contentStyle);
        // 创建第三行第三列
        XSSFCell row3Cell3 = row3.createCell(2);
        row3Cell3.setCellValue(10);
        row3Cell3.setCellStyle(numStyle);
        // 创建第三行第四列
        XSSFCell row3Cell4 = row3.createCell(3);
        row3Cell4.setCellValue(6);
        row3Cell4.setCellStyle(numStyle);

        // 创建第四行
        XSSFRow row4 = sheet.createRow(3);
        // 创建第四行第一列
        XSSFCell row4Cell1 = row4.createCell(0);
        row4Cell1.setCellValue("");
        row4Cell1.setCellStyle(contentStyle);
        // 创建第四行第二列
        XSSFCell row4Cell2 = row4.createCell(1);
        row4Cell2.setCellValue("BBBBBBBBBBBBBBBBBBBBBBBBBBBB");
        row4Cell2.setCellStyle(contentStyle);

        // 创建第四行第三列
        XSSFCell row4Cell3 = row4.createCell(2);
        row4Cell3.setCellValue(10);
        row4Cell3.setCellStyle(numStyle);
        // 创建第四行第四列
        XSSFCell row4Cell4 = row4.createCell(3);
        row4Cell4.setCellValue(6);
        row4Cell4.setCellStyle(numStyle);

        // 创建第五行
        XSSFRow row5 = sheet.createRow(4);
        // 创建第五行第一列
        XSSFCell row5Cell1 = row5.createCell(0);
        row5Cell1.setCellValue("分类2");
        row5Cell1.setCellStyle(contentStyle);
        // 创建第五行第二列
        XSSFCell row5Cell2 = row5.createCell(1);
        row5Cell2.setCellValue("CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC");
        row5Cell2.setCellStyle(contentStyle);
        // 创建第五行第三列
        XSSFCell row5Cell3 = row5.createCell(2);
        row5Cell3.setCellValue(10);
        row5Cell3.setCellStyle(numStyle);
        // 创建第五行第四列
        XSSFCell row5Cell4 = row5.createCell(3);
        row5Cell4.setCellValue(6);
        row5Cell4.setCellStyle(numStyle);

        FileOutputStream outputStream = new FileOutputStream(xlsPath);
        wk.write(outputStream);
        outputStream.flush();
    }
}

工具类 ExcelUtil

package com.zz.util;
/**
 * Created by Bsea
 * 2017-06-11 19:12
 */
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil {


        public static void readXls() throws IOException {
//            InputStream is = new FileInputStream("D:\\excel\\xls_test2.xls");
            InputStream is = new FileInputStream("C:\\tmp\\党员资料.xls");
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }

                // 循环行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();
                }
            }
        }

        public static String getValue(HSSFCell hssfCell) {
            if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
                return String.valueOf(hssfCell.getBooleanCellValue());
            } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
                    System.out.print("日期处理" );
                    Date date = hssfCell.getDateCellValue();
                    SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd");
                    return  sdf.format(date);
                }
                return String.valueOf(hssfCell.getNumericCellValue());
            } else {
                return String.valueOf(hssfCell.getStringCellValue());
            }
        }

        //xlsx
        public static void readXlsx() throws IOException{
            String fileName = "D:\\excel\\xlsx_test.xlsx";
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook( fileName);

            // 循环工作表Sheet
            for(int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++){
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt( numSheet);
                if(xssfSheet == null){
                    continue;
                }

                // 循环行Row
                for(int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++ ){
                    XSSFRow xssfRow = xssfSheet.getRow( rowNum);
                    if(xssfRow == null){
                        continue;
                    }

                    // 循环列Cell
                    for(int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++){
                        XSSFCell xssfCell = xssfRow.getCell( cellNum);
                        if(xssfCell == null){
                            continue;
                        }
                        System.out.print("   "+getsValue(xssfCell));
                    }
                    System.out.println();
                }
            }
        }
        public static String getsValue(XSSFCell xssfCell){
            if(xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN){
                return String.valueOf( xssfCell.getBooleanCellValue());
            }else if(xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC){
                return String.valueOf( xssfCell.getNumericCellValue());
            }else{
                return String.valueOf( xssfCell.getStringCellValue());
            }
        }

    public static void main(String[] args) throws IOException {
        readXls();
    }
    }