表格拆分成多个表格"/>
【无标题】用记事本编写的java代码,实现EXCEL中按固定列将一个表格拆分成多个表格
用记事本编写的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生成Workbooktry{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开
更多推荐
【无标题】用记事本编写的java代码,实现EXCEL中按固定列将一个表格拆分成多个表格
发布评论