Java操作excel-兼容office 2007版本之后
东郭骁
前言
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
类似资料：