今天需要对比2个excel表的内容找出相同;由于要学的还很多上手很慢所以在这做个分享希望对初学的有帮助;
先是pom的配置:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.11-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.11-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.11-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.11-beta2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.8.1</version>
</dependency>
</dependencies>
下边是我网上找到的工具类但是没法在简单的java项目中实现导出所以只用了导入功能
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil {
private ExcelUtil() {
}
private final String excel2003L = ".xls";//2003- 版本的excel
private final String excel2007U = ".xlsx";//2007+ 版本的excel
public static final ExcelUtil getInstance() {
return ExcelUtilHolder.instance;
}
private static final class ExcelUtilHolder {
private static final ExcelUtil instance = new ExcelUtil();
}
private Workbook getWorkbook(InputStream inStr, String filename) throws Exception {
Workbook workbook;
String fileType = filename.substring(filename.lastIndexOf("."));
if (excel2003L.equals(fileType)) {
workbook = new HSSFWorkbook(inStr);//2003-
} else if (excel2007U.equals(fileType)) {
workbook = new XSSFWorkbook(inStr);//2007+
} else {
throw new Exception("解析的文件格式有误");
}
return workbook;
}
/**
* 获取excel中的数据
*/
public List<List<Object>> readExcelData(InputStream in, String filename) throws Exception {
List<List<Object>> list;
//创建Excel工作薄
Workbook work = getWorkbook(in, filename);
if (null == work) {
throw new Exception("创建Excel工作薄为空!");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
list = new ArrayList<List<Object>>();
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
//遍历当前sheet中的所有行
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if (row == null || row.getFirstCellNum() == j) {
continue;
}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
//通过getCellValue方法获取当前行每一列中的数据
li.add(getCellValue(cell));
}
//将每一行的数据添加到list
list.add(li);
}
}
in.close();
return list;
}
/**
* 获取每个单元格的内容
*/
private Object getCellValue(Cell cell) {
Object value = null;
DecimalFormat df = new DecimalFormat("0");//格式化number String字符串
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//日期格式化
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
value = sdf.format(cell.getDateCellValue());
} else {
value = cell.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
/**
* 数据转成excel
*
* @param dataList 需要转换的数据
* @param sheetName 生成的excel表名
* @param columnName 每一列的名字(key)
* @return
*/
public Workbook dataToExcel(List<Map<String, String>> dataList, String sheetName, String[] columnName) {
int columnNum = columnName.length;
Workbook workbook = new HSSFWorkbook();
//创建第一页,并命名
Sheet sheet = workbook.createSheet(sheetName);
//创建第一行
Row row = sheet.createRow(0);
// 创建两种单元格格式
CellStyle cs = workbook.createCellStyle();
CellStyle cs2 = workbook.createCellStyle();
// 创建两种字体
Font f = workbook.createFont();
Font f2 = workbook.createFont();
// 创建第一种字体样式(用于列名)
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
// f.setBold(Font.COLOR_NORMAL);
// f.setBoldweight(Font.COLOR_RED);
// 创建第二种字体样式(用于值)
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
//设置列名
for (int i = 0; i < columnNum; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(columnName[i]);
cell.setCellStyle(cs);
}
//设置每行每列的值
int rowNum = dataList.size();
for (int j = 1; j <= rowNum; j++) {
//创建一行
Row r = sheet.createRow(j);
for (int k = 0; k < columnNum; k++) {
Cell cell = r.createCell(k);
cell.setCellValue(dataList.get(j - 1).get(columnName[k]));
cell.setCellStyle(cs2);
}
}
return workbook;
}
static HSSFWorkbook workbook;
public static void writeToExcel(String fileDir,String sheetName,List<Map> mapList) throws Exception{
//创建workbook
File file = new File(fileDir);
try {
workbook = new HSSFWorkbook(new FileInputStream(file));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
//流
FileOutputStream out = null;
HSSFSheet sheet = workbook.getSheet(sheetName);
// 获取表格的总行数
// int rowCount = sheet.getLastRowNum() + 1; // 需要加一
// 获取表头的列数
int columnCount = sheet.getRow(0).getLastCellNum()+1;
try {
// 获得表头行对象
HSSFRow titleRow = sheet.getRow(0);
if(titleRow!=null){
for(int rowId=0;rowId<mapList.size();rowId++){
Map map = mapList.get(rowId);
HSSFRow newRow=sheet.createRow(rowId+1);
for (short columnIndex = 0; columnIndex < columnCount; columnIndex++) { //遍历表头
String mapKey = titleRow.getCell(columnIndex).toString().trim().toString().trim();
HSSFCell cell = newRow.createCell(columnIndex);
cell.setCellValue(map.get(mapKey)==null ? null : map.get(mapKey).toString());
}
}
}
out = new FileOutputStream(fileDir);
workbook.write(out);
} catch (Exception e) {
throw e;
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
public static void writeExcel(List<Map> dataList, int cloumnCount,String finalXlsxPath){
OutputStream out = null;
try {
// 获取总列数
int columnNumCount = cloumnCount;
// 读取Excel文档
File finalXlsxFile = new File(finalXlsxPath);
Workbook workBook = getWorkbok(finalXlsxFile);
// sheet 对应一个工作页
Sheet sheet = workBook.getSheetAt(0);
/**
* 删除原有数据,除了属性列
*/
int rowNumber = sheet.getLastRowNum(); // 第一行从0开始算
System.out.println("原始数据总行数,除属性列:" + rowNumber);
for (int i = 1; i <= rowNumber; i++) {
Row row = sheet.getRow(i);
sheet.removeRow(row);
}
// 创建文件输出流,输出电子表格:这个必须有,否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(finalXlsxPath);
workBook.write(out);
/**
* 往Excel中写新数据
*/
for (int j = 0; j < dataList.size(); j++) {
// 创建一行:从第二行开始,跳过属性列
Row row = sheet.createRow(j + 1);
// 得到要插入的每一条记录
Map dataMap = dataList.get(j);
String name = dataMap.get("名字" + j).toString();
for (int k = 0; k <= columnNumCount; k++) {
// 在一行内循环
Cell first = row.createCell(0);
first.setCellValue(name);
}
}
// 创建文件输出流,准备输出电子表格:这个必须有,否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(finalXlsxPath);
workBook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(out != null){
out.flush();
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
System.out.println("数据导出成功");
}
/**
* 判断Excel的版本,获取Workbook
* @param in
* @param filename
* @return
* @throws IOException
*/
public static Workbook getWorkbok(File file) throws IOException{
Workbook wb = null;
FileInputStream in = new FileInputStream(file);
if(file.getName().endsWith(EXCEL_XLS)){ //Excel 2003
wb = new HSSFWorkbook(in);
}else if(file.getName().endsWith(EXCEL_XLSX)){ // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
return wb;
}
}
接下来是另一个地方找到的导出工具类
import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.Collection; import java.util.Date; import java.util.Iterator; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * 导出Excel * @author liuyazhuang * * @param <T> */ public class ExportExcelUtil<T>{ // 2007 版本以上 最大支持1048576行 public final static String EXCEl_FILE_2007 = "2007"; // 2003 版本 最大支持65536 行 public final static String EXCEL_FILE_2003 = "2003"; /** * <p> * 导出无头部标题行Excel <br> * 时间格式默认:yyyy-MM-dd hh:mm:ss <br> * </p> * * @param title 表格标题 * @param dataset 数据集合 * @param out 输出流 * @param version 2003 或者 2007,不传时默认生成2003版本 */ public void exportExcel(String title, Collection<T> dataset, OutputStream out, String version) { if(StringUtils.isEmpty(version) || EXCEL_FILE_2003.equals(version.trim())){ exportExcel2003(title, null, dataset, out, "yyyy-MM-dd HH:mm:ss"); }else{ exportExcel2007(title, null, dataset, out, "yyyy-MM-dd HH:mm:ss"); } } /** * <p> * 导出带有头部标题行的Excel <br> * 时间格式默认:yyyy-MM-dd hh:mm:ss <br> * </p> * * @param title 表格标题 * @param headers 头部标题集合 * @param dataset 数据集合 * @param out 输出流 * @param version 2003 或者 2007,不传时默认生成2003版本 */ public void exportExcel(String title,String[] headers, Collection<T> dataset, OutputStream out,String version) { if(StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())){ exportExcel2003(title, headers, dataset, out, "yyyy-MM-dd HH:mm:ss"); }else{ exportExcel2007(title, headers, dataset, out, "yyyy-MM-dd HH:mm:ss"); } } /** * <p> * 通用Excel导出方法,利用反射机制遍历对象的所有字段,将数据写入Excel文件中 <br> * 此版本生成2007以上版本的文件 (文件后缀:xlsx) * </p> * * @param title * 表格标题名 * @param headers * 表格头部标题集合 * @param dataset * 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的 * JavaBean属性的数据类型有基本数据类型及String,Date * @param out * 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中 * @param pattern * 如果有时间数据,设定输出格式。默认为"yyyy-MM-dd hh:mm:ss" */ @SuppressWarnings({ "unchecked", "rawtypes" }) public void exportExcel2007(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) { // 声明一个工作薄 XSSFWorkbook workbook = new XSSFWorkbook(); // 生成一个表格 XSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(20); // 生成一个样式 XSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(new XSSFColor(java.awt.Color.gray)); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); style.setBorderTop(XSSFCellStyle.BORDER_THIN); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 生成一个字体 XSSFFont font = workbook.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setFontName("宋体"); font.setColor(new XSSFColor(java.awt.Color.BLACK)); font.setFontHeightInPoints((short) 11); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 XSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(new XSSFColor(java.awt.Color.WHITE)); style2.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(XSSFCellStyle.BORDER_THIN); style2.setBorderLeft(XSSFCellStyle.BORDER_THIN); style2.setBorderRight(XSSFCellStyle.BORDER_THIN); style2.setBorderTop(XSSFCellStyle.BORDER_THIN); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 XSSFFont font2 = workbook.createFont(); font2.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 产生表格标题行 XSSFRow row = sheet.createRow(0); XSSFCell cellHeader; for (int i = 0; i < headers.length; i++) { cellHeader = row.createCell(i); cellHeader.setCellStyle(style); cellHeader.setCellValue(new XSSFRichTextString(headers[i])); } // 遍历集合数据,产生数据行 Iterator<T> it = dataset.iterator(); int index = 0; T t; Field[] fields; Field field; XSSFRichTextString richString; Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher; String fieldName; String getMethodName; XSSFCell cell; Class tCls; Method getMethod; Object value; String textValue; SimpleDateFormat sdf = new SimpleDateFormat(pattern); while (it.hasNext()) { index++; row = sheet.createRow(index); t = (T) it.next(); // 利用反射,根据JavaBean属性的先后顺序,动态调用getXxx()方法得到属性值 fields = t.getClass().getDeclaredFields(); for (int i = 0; i < fields.length; i++) { cell = row.createCell(i); cell.setCellStyle(style2); field = fields[i]; fieldName = field.getName(); getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { tCls = t.getClass(); getMethod = tCls.getMethod(getMethodName, new Class[] {}); value = getMethod.invoke(t, new Object[] {}); // 判断值的类型后进行强制类型转换 textValue = null; if (value instanceof Integer) { cell.setCellValue((Integer) value); } else if (value instanceof Float) { textValue = String.valueOf((Float) value); cell.setCellValue(textValue); } else if (value instanceof Double) { textValue = String.valueOf((Double) value); cell.setCellValue(textValue); } else if (value instanceof Long) { cell.setCellValue((Long) value); } if (value instanceof Boolean) { textValue = "是"; if (!(Boolean) value) { textValue = "否"; } } else if (value instanceof Date) { textValue = sdf.format((Date) value); } else { // 其它数据类型都当作字符串简单处理 if (value != null) { textValue = value.toString(); } } if (textValue != null) { matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else { richString = new XSSFRichTextString(textValue); cell.setCellValue(richString); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { // 清理资源 } } } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } } /** * <p> * 通用Excel导出方法,利用反射机制遍历对象的所有字段,将数据写入Excel文件中 <br> * 此方法生成2003版本的excel,文件名后缀:xls <br> * </p> * * @param title * 表格标题名 * @param headers * 表格头部标题集合 * @param dataset * 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的 * JavaBean属性的数据类型有基本数据类型及String,Date * @param out * 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中 * @param pattern * 如果有时间数据,设定输出格式。默认为"yyyy-MM-dd hh:mm:ss" */ @SuppressWarnings({ "unchecked", "rawtypes" }) public void exportExcel2003(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(20); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontName("宋体"); font.setColor(HSSFColor.WHITE.index); font.setFontHeightInPoints((short) 11); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.WHITE.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 产生表格标题行 HSSFRow row = sheet.createRow(0); HSSFCell cellHeader; for (int i = 0; i < headers.length; i++) { cellHeader = row.createCell(i); cellHeader.setCellStyle(style); cellHeader.setCellValue(new HSSFRichTextString(headers[i])); } // 遍历集合数据,产生数据行 Iterator<T> it = dataset.iterator(); int index = 0; T t; Field[] fields; Field field; HSSFRichTextString richString; Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher; String fieldName; String getMethodName; HSSFCell cell; Class tCls; Method getMethod; Object value; String textValue; SimpleDateFormat sdf = new SimpleDateFormat(pattern); while (it.hasNext()) { index++; row = sheet.createRow(index); t = (T) it.next(); // 利用反射,根据JavaBean属性的先后顺序,动态调用getXxx()方法得到属性值 fields = t.getClass().getDeclaredFields(); for (int i = 0; i < fields.length; i++) { cell = row.createCell(i); cell.setCellStyle(style2); field = fields[i]; fieldName = field.getName(); getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { tCls = t.getClass(); getMethod = tCls.getMethod(getMethodName, new Class[] {}); value = getMethod.invoke(t, new Object[] {}); // 判断值的类型后进行强制类型转换 textValue = null; if (value instanceof Integer) { cell.setCellValue((Integer) value); } else if (value instanceof Float) { textValue = String.valueOf((Float) value); cell.setCellValue(textValue); } else if (value instanceof Double) { textValue = String.valueOf((Double) value); cell.setCellValue(textValue); } else if (value instanceof Long) { cell.setCellValue((Long) value); } if (value instanceof Boolean) { textValue = "是"; if (!(Boolean) value) { textValue = "否"; } } else if (value instanceof Date) { textValue = sdf.format((Date) value); } else { // 其它数据类型都当作字符串简单处理 if (value != null) { textValue = value.toString(); } } if (textValue != null) { matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else { richString = new HSSFRichTextString(textValue); cell.setCellValue(richString); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { // 清理资源 } } } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } } }
导出 需要一个实体类:
public class Hu { public Hu() { super(); // TODO Auto-generated constructor stub } private String name; public void setName(String name) { this.name = name; } public Hu(String name) { super(); this.name = name; } public String getName() { return name; } }
准备工作就弄好了 接下来 实践
import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import entity.Hu; import util.ExcelUtil; import util.ExportExcelUtil; public class Poitest { public static void main(String[] args) throws Exception { InputStream in = new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\进件满.xlsx")); InputStream in1 = new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\test.xlsx")); List<List<Object>> list = ExcelUtil.getInstance().readExcelData(in, "进件满.xlsx"); List<List<Object>> list1 = ExcelUtil.getInstance().readExcelData(in1, "test.xlsx"); List<String> li1 = new ArrayList<String>(); System.out.println(list1.size()); String n = ""; for(int i = 0; i < list.size(); i++) { if(list1.contains(list.get(i))) { // list1.remove(list.get(i)); n = list.get(i).toString(); // l.add(String.valueOf(s)); n = n.replace("[", ""); n = n.replace("]", ""); li1.add(n); } } ExportExcelUtil<Hu> util = new ExportExcelUtil<Hu>(); // 准备数据 List<Hu> list5 = new ArrayList(); Hu h = new Hu(); String a = ""; for(Object s:li1) { a = String.valueOf(s); // l.add(String.valueOf(s)); a = a.replace("[", ""); a = a.replace("]", ""); list5.add(new Hu(a)); ; } String[] columnNames = {"姓名"}; util.exportExcel("用户导出", columnNames, list5, new FileOutputStream("C:\\Users\\Administrator\\Desktop\\test1.xlsx"), ExportExcelUtil.EXCEl_FILE_2007); } }
然后会在规定的excel文件里写入你比较成功的内容
这样就实现了excel的导入导出
参考博客:https://blog.csdn.net/l1028386804/article/details/79659605