package com.dlj.excel;
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.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;
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.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
public class ExcelUtil {
/**
* 使用POI技术导入Excel文件
* @param filePatgh
* @return
*/
public List<Map<String, Object>> importExcele1(String filePatgh){
//创建一个集合,用于接收数据,传到方法外
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
//定义i工作簿
HSSFWorkbook workbook = null;
try {
//读取Excel表格
workbook = new HSSFWorkbook(new FileInputStream(new File(filePatgh)));
//获取第一个Sheet页面的信息
HSSFSheet sheet = workbook.getSheetAt(0);
//获取总行数
int rowNum = sheet.getLastRowNum();
//获取第一行的数据
HSSFRow row = sheet.getRow(0);
//获取总列数,用于作为Map集合的key
short lastCellNum = row.getLastCellNum();
//定义一个数组,用于保存map集合的key
String[] keys = new String[lastCellNum];
//通过循环给数组赋值
for (int i = 0; i < keys.length; i++) {
keys[i] = row.getCell(i).getStringCellValue();
}
//通过循环读取每一个单元格的信息
for (int i = 1; i < rowNum+1; i++) {
//获取每一行的数据
row = sheet.getRow(i);
//定义一个Map集合,用于保存数据
Map<String, Object> map = new HashMap<String, Object>();
//通过循环获取每一个单元格中的信息
for (int j = 0; j < row.getLastCellNum()+1; j++) {
HSSFCell cell = row.getCell(j);
if (cell != null) {
map.put(keys[j],cell.toString() );
}
}
//最终将map集合保存到list集合中
list.add(map);
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
/**
* 导出Excel表格
* @param list 需要到处的数据
* @param firstRow 标题(第一行的数据)
* @param filePath 导出的路径
* @return
*/
public String exportExcel(List<Map<String, Object>> list,String[] firstRow,String filePath){
HSSFWorkbook workbook = null;
try {
workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("sheet0");
HSSFRow row = sheet.createRow(0);
//第一行数据赋值
for (int i = 0; i < firstRow.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(firstRow[i]);
}
for (int i = 1; i < list.size()+1; i++) {
HSSFRow rows = sheet.createRow(i);
for (int j = 0; j < firstRow.length; j++) {
System.out.println(i+" "+j);
rows.createCell(j).setCellValue((String)list.get(i-1).get(firstRow[j]));
}
}
try {
workbook.write(new File(filePath));
workbook.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return filePath;
}
/**
* 根据模板导出Excel表格
* @param path 文件最终保存路径
* @param filePath 模板路径
* @param newFileName 新的文件名
* @return
*/
public String exportByMuban(String path,String filePath,String newFileName,Map<String, Object> map) {
XLSTransformer transformer = new XLSTransformer();
try {
InputStream is = new FileInputStream(path);
OutputStream oStream = new FileOutputStream(new File(path));
Workbook workbook = transformer.transformXLS(is, map);
workbook.write(oStream);
oStream.flush();
} catch (FileNotFoundException | ParsePropertyException
| InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
}
源码和jar包:https://download.csdn.net/download/qq_27243967/10592123