当前位置: 首页 > 工具软件 > EXCEL-UTIL4J > 使用案例 >

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

东郭骁
2023-12-01

 

前言

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

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

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

 

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

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

 

maven 依赖

 

<!-- 操作office办公文件 -->
		<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/

废话不多说了,直接贴代码:

·读取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

+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

 

 

 

 

 

 

 

 

 

 

 类似资料: