JAVA入门13 操作excel表格 ExcelUtil

悠悠 2021-07-26 17:20 413阅读 0赞

# 导包 #

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.14</version>
            </dependency>
            
            <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();
        }
        }