用记事本编写的java代码,实现EXCEL中按固定列将一个表格拆分成多个表格,部分内容是从网络中搜索获得、仅供学些交流。这个是拆分的主要实现模块,还有几个辅助模块,包括界面设计,EXCEL表导入、过滤等功能,有需要的朋友我会继续更新。
//导入apache.poi的相关类
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.ArrayList;
import java.util.List;
import java.math.BigDecimal;
import java.util.Vector;
import java.util.Iterator;
/**
* 提供常用excel操作<br>
* 大部分代码从网上搜索,后续根据需求修改
*
*/
public class ExcelOperationUtil{
//设置三个常量分别标记EXCEL的03版、07版和打开错误
public static final int EXCEL_VERSION_XLS = 0;//03版
public static final int EXCEL_VERSION_XLSX = 1;//07版
public static final int EXCEL_VERSION_ERROR = 2;//打开文件错误
public static int getExcelVersion(String fileName){
if(fileName.endsWith(".xls")){
return EXCEL_VERSION_XLS;
}else if(fileName.endsWith(".xlsx")){
return EXCEL_VERSION_XLSX;
}else{
return EXCEL_VERSION_ERROR;
}
}
public static Workbook getWorkbook(File file,int excelVersion){
Workbook workBook = null;
InputStream inputStream = null;
try{
if(file!=null){
inputStream = new FileInputStream(file);
}else{
return null;
}
/*
*以下两种方式都可以生成Workbook,方式1兼容性可能更强
*以下两种方式生成Workbool均不能导入带批注的EXCEL文件,需要修改代码,暂时未找到解决办法
*/
//方式1生成Workbook
try{
workBook = WorkbookFactory.create(inputStream);
}catch(java.lang.Exception e){
e.printStackTrace();
return null;
}
//方式2生成Workbook
/*if(excelVersion == EXCEL_VERSION_XLS){
try{
workBook = new HSSFWorkbook(inputStream);
}catch(IOException e){
return null;
}
}else{
try{
workBook = new XSSFWorkbook(inputStream);
}catch(java.lang.Exception e){
e.printStackTrace();
return null;
}
}
*/
inputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
return null;
} catch (IOException e) {
e.printStackTrace();
return null;
}finally {
if (null != inputStream){
try{
inputStream.close();
}catch (IOException e){
e.printStackTrace();
return null;
}
}
}
return workBook;
}
//此静态函数返回WorkBook的所有Sheet页的名称,尽量用Vector集合存放Sheet页的名称,如果用数组返回null可能有问题
public static Vector<String> getSheetNames(Workbook workBook){
int sheetNumber = 0;
if(workBook!=null){
sheetNumber = workBook.getNumberOfSheets();
}else{
return null;
}
Vector<String> sheetNames = new Vector<String>();
for(int i = 0;i< sheetNumber;i++){
sheetNames.add(workBook.getSheetName(i));
}
return sheetNames;
}
public static int getRowCount(Sheet sheet){
if(null != sheet){
return sheet.getLastRowNum()+1;//getLastRowNum最后一行的下表索引(从0开