Java操作excel-兼容office 2007版本之后

ゝ一纸荒年。 2022-06-17 06:28 298阅读 0赞

### 前言 ###

office2007版本有一次标准升级，最为明显的就是excel文件的文件名后缀（文件扩展名）由xls变成了xlsx，这是需要注意的地方，因为按照xls格式来处理xlsx格式的文件是无法操作的，

反过来，使用操作xlsx格式的文件的方式操作文件扩展名（文件位缀）为xls是没有问题的。

因为本质上来说，office之后的版本对之前的版本是兼容的，这个在Office 开发组件poi中也得到了提现。即向下兼容，或者向后兼容的，只旧系统的东西依旧可以在新系统被正常使用。

需要注意的是，本文有使用log4j2，如果你的环境没有配置日志，可以把日志代码去除，换为System.out,println();

如果你对log4j2感兴趣，可以参考[APACHE LOG4J™ 2 学习笔记-log4j2 环境部署到各种类型输出+maven\\mysql\\滚动文件\\控制台\\异步\\过滤器][APACHE LOG4J_ 2 _-log4j2 _maven_mysql]

### maven 依赖 ###

<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>
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-ooxml-schemas</artifactId>
    			<version>3.14</version>
    		</dependency>
    		<dependency>
    			<groupId>org.apache.xmlbeans</groupId>
    			<artifactId>xmlbeans</artifactId>
    			<version>2.6.0</version>
    		</dependency>

poi是专门操作office文件的免费的API：[http://poi.apache.org/][http_poi.apache.org]

废话不多说了，直接贴代码：

·读取excel文件   readOfficeExcelFile

·在原先的excel文件续写-没有加末尾判断，可能会造成数据被覆盖  writeOfficeExcelFile

·程序新建一个excle文件，并且填充一些数据  createOfficeExcelFile

枚举类提供常量-本例中仅部分有用到

### 工具类 ###

package com.bestcxx.mavenstu.mavenssh.file;
    
    import java.io.*;
    import java.text.DecimalFormat;
    import java.util.List;
    
    import com.google.common.collect.Lists;
    import org.apache.logging.log4j.LogManager;
    import org.apache.logging.log4j.Logger;
    import org.apache.poi.EncryptedDocumentException;
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    /**
     *
     * @theme 操作 excel 文件的工具类
     * HSSF － 提供读写Microsoft Excel格式档案的功能。
     * XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。
     * HWPF － 提供读写Microsoft Word格式档案的功能。
     * HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
     * HDGF － 提供读写Microsoft Visio格式档案的功能。
     */
    public class FileOfficeExcel {
        private static Logger logger=LogManager.getLogger(FileOfficeExcel.class);
    
        /**
         * 读取 txt 文件内容
         * @param readMeTxt
         * @throws IOException
         */
        public void readFromTxt(String readMeTxt) throws IOException {
    
                //读取全部文件内容
                File file = new File(readMeTxt);
                StringBuilder sb = new StringBuilder();
                String s ="";
                BufferedReader br = new BufferedReader(new FileReader(file));
    
                while( (s = br.readLine()) != null) {
                    sb.append(s + "\n");
                }
    
                br.close();
                String str = sb.toString();
                System.out.println(str);
        }
    
        /**
         * 将离线 excel 文件按照 行数拆分为子文件
         * 只支持第一个sheet
         * @param excelPath          离线文件路径
         * @param excelName          离线文件名称 abc.xlsx
         * @param thresholdNumber     文件分割行数阈值
         * @param subexcelPath        拆分子文件保存路径
         * @param subExcelNamePerefix  拆分子文件前缀
         */
        public void devideOfflineExcelFile(String excelPath, String excelName,
                                           int thresholdNumber, String subexcelPath,
                                           String subExcelNamePerefix) {
    
            try {
    
                File file = new File(excelPath, excelName);
    
                // 文件转化为输入流
                FileInputStream fi;
    
                fi = new FileInputStream(file);
    
                // 操作excel文件需要创建Workbook 对象
                Workbook wb;
                wb = WorkbookFactory.create(fi);
    
                // 获得具体的一个sheet页-这里是第一个
                Sheet sheet = wb.getSheetAt(0);
    
                // 获取最后一行的行号
                int rowNum = sheet.getLastRowNum() + 1;//这里加1，是因为获取的是序数值，而函数是从0开始计数的
                //logger.info("\n获取第一个 sheet 包含行数为:"+rowNum);
    
                //获取标题行
                Row rowTitle = sheet.getRow(0);
    
    
                // 获取某一行的数据-这里是第i行
                List rowList = Lists.newArrayList();
                rowList.add(rowTitle);
                for(int i=1;i<rowNum;i++){
                    Row row = sheet.getRow(i);
                    // 获取某一行的列数
                    //int cellNum = row.getRowNum();
                    //logger.info("\n 第"+i+"行 包含列数为:"+cellNum);
                    //输出这一句就可以看到，A1,B1,   A2,B2,C2,D2,E2
                    //logger.info("\n行信息："+row.toString());
                    rowList.add(row);
                    if((i%thresholdNumber==0)){
                        createOfficeExcelFileForSub(subexcelPath,subExcelNamePerefix+"-"+(i/thresholdNumber)+".xlsx",rowList);
                        rowList=Lists.newArrayList();
                        rowList.add(rowTitle);
                    }else if(i==(rowNum-1)){
                        createOfficeExcelFileForSub(subexcelPath,subExcelNamePerefix+"-"+(i/thresholdNumber+1)+".xlsx",rowList);
                        rowList=Lists.newArrayList();
                        rowList.add(rowTitle);
                    }
    
                }
    
    
            } catch (FileNotFoundException e1) {
                logger.info(e1.toString(),e1);
            } catch (EncryptedDocumentException e) {
                logger.info(e.toString(),e);
            } catch (InvalidFormatException e) {
                logger.info(e.toString(),e);
            } catch (IOException e){
                logger.info(e.toString(),e);
            }
    
        }
    
        /**
         *
         * @instruction 程序新建一个excle文件，并且填充一些数据
         * @Datetime 2017年3月27日 下午6:00:08
         * @param excelPath
         * @param excelName
         */
        public void createOfficeExcelFileForSub(String excelPath, String excelName,List<Row> copyRowList) {
            //如果文件存在，就先删除
            String fileName=excelPath+"/"+excelName;
            File file = new File(excelPath,excelName);
            if(file.exists()){    //文件存在
                logger.info("文件已经存在，删除 "+fileName);
                file.delete();
            }
            // 操作excel文件需要实例化 Workbook，借助poi，（office 2007 及之后版本XSSF，向后（下）兼容，xlsx 结尾）
            //HSSF － 提供读写Microsoft Excel格式档案的功能，xls。
            //XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。
            //Workbook wb = new XSSFWorkbook();
            Workbook wb = new XSSFWorkbook();
    
            // 设置字体格式-略 import org.apache.poi.ss.usermodel.Font;
            // Font font = wb.createFont();
    
            // 设置单元格样式-略 import org.apache.poi.ss.usermodel.CellStyle;
            // CellStyle style = wb.createCellStyle();
    
            // 新建工作表
            Sheet sheet = wb.createSheet("sheet1");
            DecimalFormat df = new DecimalFormat("0");
    
            // 给第0行添加数据
            for(int j=0;j<copyRowList.size();j++){
                // 新建行-第0行，从0开始计数
                Row row = sheet.createRow(j);
                for (int i = 0; i < copyRowList.get(j).getPhysicalNumberOfCells(); i++) {
                    Cell cell = row.createCell(i);
                    //对于标题行特殊处理，非标题行第一列为数字解决数字展示不直观的问题(处理后展示出普通数字，而不是科学计数法)
                    if(i==0&&j!=0){
                        try{
                            //第一行可能只是看起来是数字，try catch 兜底
                            cell.setCellValue(df.format(copyRowList.get(j).getCell(i).getNumericCellValue()));
                        }catch (IllegalStateException e){
                            cell.setCellValue(copyRowList.get(j).getCell(i) + "");
                        }
    
                    }else{
                        cell.setCellValue(copyRowList.get(j).getCell(i) + "");
                    }
    
                    // 结合样式
                    // cell.setCellStyle(style);
                }
            }
    
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            OutputStream out = null;
            try {
                wb.write(os);
                byte[] xls = os.toByteArray();
                out = new FileOutputStream(file);
                out.write(xls);
            } catch (IOException e) {
                logger.info(e.toString());
            } finally {
                try {
                    if(wb!=null){
                        wb.close();
                    }
                    if(out!=null){
                        out.close();
                    }
                    if(os!=null){
                        os.close();
                    }
                    logger.info("新建文件 "+fileName);
                } catch (IOException e) {
                    logger.info(e.toString());
                }
    
            }
    
        }
    
    
        /**
         *
         * @instruction 读取excel文件
         * @Datetime 2017年3月27日 下午3:59:15
         * @param excelPath 文件夹路径
         * @param excelName 文件名字  officeExcelDemo.xlsx
         */
        public void readOfficeExcelFile(String excelPath,String excelName) {
            try {
    
                File file = new File(excelPath, excelName);
    
                // 文件转化为输入流
                FileInputStream fi;
    
                fi = new FileInputStream(file);
    
                // 操作excel文件需要创建Workbook 对象
                Workbook wb;
                wb = WorkbookFactory.create(fi);
    
                // 获得具体的一个sheet页-这里是第一个
                Sheet sheet = wb.getSheetAt(0);
    
                // 获取最后一行的行号
                int rowNum = sheet.getLastRowNum() + 1;//这里加1，是因为获取的是序数值，而函数是从0开始计数的
                //logger.info("\n获取第一个 sheet 包含行数为:"+rowNum);
    
                // 获取某一行的数据-这里是第i行
                for(int i=0;i<rowNum;i++){
                    Row row = sheet.getRow(i);
                    // 获取某一行的列数
                    int cellNum = row.getLastCellNum();
                    logger.info("\n 第"+i+"行 包含列数为:"+cellNum);
                    //输出这一句就可以看到，A1,B1,   A2,B2,C2,D2,E2
                    //logger.info("\n行信息："+row.toString());
    
                    //遍历行
                    for(int j=0;j<cellNum;j++){
                        Cell cell=row.getCell(j);
                        cell.setCellType(Cell.CELL_TYPE_STRING);//将表格数据格式转化为字符类型
                        logger.info(cell.getStringCellValue()+" ");
                    }
    
                }
    
    
            } catch (FileNotFoundException e1) {
                logger.info(e1.toString());
            } catch (EncryptedDocumentException e) {
                logger.info(e.toString());
            } catch (InvalidFormatException e) {
                logger.info(e.toString());
            } catch (IOException e) {
                logger.info(e.toString());
            }
    
        }
    
    
        /**
         *
         * @instruction 在原先的excel文件续写-没有加末尾判断，可能会造成数据被覆盖
         * @Datetime 2017年3月27日 下午6:25:26
         * @param excelPath
         * @param excelName
         */
        public void writeOfficeExcelFile(String excelPath, String excelName) throws IOException {
            Workbook wb;
            File file=new File(excelPath+excelName);
            file.createNewFile();
            //File file=new File(excelPath,excelName);
            String fileName=excelPath+excelName;
            try {
                //wb = WorkbookFactory.create(file);
                wb = new XSSFWorkbook();
                Sheet sheet = wb.createSheet("sheet1");
                Row row=sheet.createRow(1);
    
                for (int i = 0; i < 10; i++) {
                    Cell cell = row.createCell(i);
                    cell.setCellValue(i + "");
                }
    
                ByteArrayOutputStream os = new ByteArrayOutputStream();
                OutputStream out = null;
                try {
                    wb.write(os);
                    byte[] xls = os.toByteArray();
                    out = new FileOutputStream(file);
                    out.write(xls);
                } catch (IOException e) {
                    logger.info(e.toString());
                } finally {
                    try {
                        if(wb!=null){
                            wb.close();
                        }
                        if(out!=null){
                            out.close();
                        }
                        if(os!=null){
                            os.close();
                        }
                        logger.info("覆盖重写文件 "+fileName);
                    } catch (IOException e) {
                        logger.info(e.toString());
                    }
    
                }
    
            } catch (EncryptedDocumentException e) {
                logger.info(e.toString(),e);
            }
    
    
    
        }
    
        /**
         *
         * @instruction 程序新建一个excle文件，并且填充一些数据
         * @Datetime 2017年3月27日 下午6:00:08
         * @param excelPath
         * @param excelName
         */
        public void createOfficeExcelFile(String excelPath, String excelName) {
            //如果文件存在，就先删除
            String fileName=excelPath+"/"+excelName;
            File file = new File(excelPath,excelName);
            if(file.exists()){    //文件存在
                logger.info("文件已经存在，删除 "+fileName);
                file.delete();
            }
            // 操作excel文件需要实例化 Workbook，借助poi，（office 2007 及之后版本XSSF，向后（下）兼容，xlsx 结尾）
            //HSSF － 提供读写Microsoft Excel格式档案的功能，xls。
            //XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。
            //Workbook wb = new XSSFWorkbook();
            Workbook wb = new XSSFWorkbook();
    
            // 设置字体格式-略 import org.apache.poi.ss.usermodel.Font;
            // Font font = wb.createFont();
    
            // 设置单元格样式-略 import org.apache.poi.ss.usermodel.CellStyle;
            // CellStyle style = wb.createCellStyle();
    
            // 新建工作表
            Sheet sheet = wb.createSheet("测试sheet1");// 创建工作表，名称为test
    
            // 新建行-第0行，从0开始计数
            Row row = sheet.createRow(0);
    
            // 给第0行添加数据
            for (int i = 0; i < 10; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(i + "");
                // 结合样式
                // cell.setCellStyle(style);
            }
    
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            OutputStream out = null;
            try {
                wb.write(os);
                byte[] xls = os.toByteArray();
                out = new FileOutputStream(file);
                out.write(xls);
            } catch (IOException e) {
                logger.info(e.toString());
            } finally {
                try {
                    if(wb!=null){
                        wb.close();
                    }
                    if(out!=null){
                        out.close();
                    }
                    if(os!=null){
                        os.close();
                    }
                    logger.info("新建文件 "+fileName);
                } catch (IOException e) {
                    logger.info(e.toString());
                }
    
            }
    
        }
    
    }

### 测试类 ###

package com.bestcxx.mavenstu.mavenssh.file;
    
    public class FileOfficeExcelTest {
        private static Logger logger=LogManager.getLogger(FileOfficeExcelTest.class);
    
        @Test
        public void testDevideOfflineExcelFile(){
            FileOfficeExcel f=new FileOfficeExcel();
            //excel 所在文件夹
            String excelPath = EnumUtil.FILE_OFFICE_EXCEL_PATH.toString();
    
            //excel 名称
            String excelName = EnumUtil.FILE_OFFICE_EXCEL_NAME.toString();
    
            //子文件 excel 名称前缀
            String subExcelName = EnumUtil.FILE_OFFICE_SUB_EXCEL_NAME_PREFIX.toString();
    
            f.devideOfflineExcelFile(excelPath,excelName,20000,excelPath,subExcelName);
        }
    
        @Test
        //@Ignore
        public void testReadOfficeExcelFile(){
            FileOfficeExcel f=new FileOfficeExcel();
            //excel 所在文件夹
            String excelPath = EnumUtil.FILE_OFFICE_EXCEL_PATH.toString();
    
            //excel 名称
            String excelName = EnumUtil.FILE_OFFICE_EXCEL_NAME.toString();
    
            f.readOfficeExcelFile(excelPath,excelName);
        }
    
        @Test
        public void testWriteOfficeExcelFile() throws IOException {
            FileOfficeExcel f=new FileOfficeExcel();
    
            //excel 所在文件夹
            String excelPath = EnumUtil.FILE_OFFICE_EXCEL_PATH.toString();
            //excel 名称
            String excelName = "test.xlsx";
    
            f.writeOfficeExcelFile(excelPath,excelName);
        }
    
        @Test
        public void testCreateOfficeExcelFile(){
            FileOfficeExcel f=new FileOfficeExcel();
    
            //excel 所在文件夹
            String excelPath = EnumUtil.FILE_OFFICE_EXCEL_PATH.toString();
            //excel 名称
            String excelName = "test.xlsx";
    
            f.createOfficeExcelFile(excelPath,excelName);
        }
    
    }

本文属于作者原创，转载请声明：[http://blog.csdn.net/bestcxx][http_blog.csdn.net_bestcxx]

### \+20170811 补充+如果生成的文件供下载，fileName为 name.xls或者 name.xlsx格式 ###

OutputStream out = null;
    		try {
    			response.setContentType("application/vnd.ms-excel");   
    			response.setHeader("Content-disposition", "attachment;filename=" + fileName);  
    			out = response.getOutputStream();
    			wb.write(out);//Workbook wb = new HSSFWorkbook();
    			out.flush();
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} finally {
    			try {
    				if (wb != null) {wb.close();}
    				if (out != null) {out.close();}
    			} catch (IOException e) {
    				e.printStackTrace();
    			}
    		}

### \+20180710 补充+文件字段分为 文本，数字，日期三种类型，而且有可能为空 ###

if(null!=row.getCell(1)){//普通文本
               row.getCell(1).getStringCellValue();
            }
    
            if(null!=row.getCell(2)){//整形数字
                //这里涉及到一个类型转化，Excel 数字，但是只能先以 dubbo 类型来处理，转化为整形
                (int)row.getCell(2).getNumericCellValue();
            }
    
            if(null!=row.getCell(7)){//小数类型
                e.setBusinessInsuranceAmount(new BigDecimal(row.getCell(7).getNumericCellValue()));
            }
    
    if(null!=row.getCell(3)){//日期类型
                //该类型为日期类型
                row.getCell(3).getDateCellValue();
            }

### \+20210618 补充+拆分文档时数字类型的处理——保证子文档也是数字 ###

Workbook wb = new XSSFWorkbook();

Sheet sheet = wb.createSheet("sheet1");

DecimalFormat df = new DecimalFormat("0");

Row row = sheet.createRow(j);

Cell cell = row.createCell(i);
    //对于标题行特殊处理，非标题行第一列为数字解决数字展示不直观的问题(处理后展示出普通数字，而不是科学计数法)
    cell.setCellValue(df.format(copyRowList.get(j).getCell(i).getNumericCellValue()));

### \+20210620 补充 mac 环境Excel、txt 文件拆分工具包+ ###

[https://download.csdn.net/download/bestcxx/19721754][https_download.csdn.net_download_bestcxx_19721754]

[APACHE LOG4J_ 2 _-log4j2 _maven_mysql]: http://blog.csdn.net/bestcxx/article/details/54564687
[http_poi.apache.org]: http://poi.apache.org/
[http_blog.csdn.net_bestcxx]: http://blog.csdn.net/bestcxx
[https_download.csdn.net_download_bestcxx_19721754]: https://download.csdn.net/download/bestcxx/19721754