本文介绍了一套用于Excel文件导出与导入的工具类，涵盖了导出配置、公式处理、时间格式读取等功能，并提供了详细的使用示例。
导出工具类
package com.shawnway.opinion.util;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.codec.binary.Base64;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* excel导出工具
* @author tlimited
* @create 2017-08-29 10:43
**/
public class PoiExcelExport {
HttpServletResponse response;
HttpServletRequest request;
// 文件名
private String fileName ;
//文件保存路径
private String fileDir;
//sheet名
private String sheetName;
//表头字体
private String titleFontType = "Arial Unicode MS";
//表头背景色
private String titleBackColor = "C1FBEE";
//表头字号
private short titleFontSize = 12;
//添加自动筛选的列 如 A:M
private String address = "";
//正文字体
private String contentFontType = "Arial Unicode MS";
//正文字号
private short contentFontSize = 12;
//Float类型数据小数位
private String floatDecimal = ".00";
//Double类型数据小数位
private String doubleDecimal = ".00";
//设置列的公式
private String colFormula[] = null;
DecimalFormat floatDecimalFormat=new DecimalFormat(floatDecimal);
DecimalFormat doubleDecimalFormat=new DecimalFormat(doubleDecimal);
private HSSFWorkbook workbook = null;
public PoiExcelExport(String fileDir,String sheetName){
this.fileDir = fileDir;
this.sheetName = sheetName;
workbook = new HSSFWorkbook();
}
public PoiExcelExport(HttpServletResponse response, String fileName, String sheetName){
this.response = response;
this.sheetName = sheetName;
this.fileName = fileName;
workbook = new HSSFWorkbook();
}
public PoiExcelExport(HttpServletRequest request, HttpServletResponse response, String fileName, String sheetName){
this.response = response;
this.request = request;
this.sheetName = sheetName;
this.fileName = fileName;
workbook = new HSSFWorkbook();
}
/**
* 设置表头字体.
* @param titleFontType
*/
public void setTitleFontType(String titleFontType) {
this.titleFontType = titleFontType;
}
/**
* 设置表头背景色.
* @param titleBackColor 十六进制
*/
public void setTitleBackColor(String titleBackColor) {
this.titleBackColor = titleBackColor;
}
/**
* 设置表头字体大小.
* @param titleFontSize
*/
public void setTitleFontSize(short titleFontSize) {
this.titleFontSize = titleFontSize;
}
/**
* 设置表头自动筛选栏位,如A:AC.
* @param address
*/
public void setAddress(String address) {
this.address = address;
}
/**
* 设置正文字体.
* @param contentFontType
*/
public void setContentFontType(String contentFontType) {
this.contentFontType = contentFontType;
}
/**
* 设置正文字号.
* @param contentFontSize
*/
public void setContentFontSize(short contentFontSize) {
this.contentFontSize = contentFontSize;
}
/**
* 设置float类型数据小数位 默认.00
* @param doubleDecimal 如 ".00"
*/
public void setDoubleDecimal(String doubleDecimal) {
this.doubleDecimal = doubleDecimal;
}
/**
* 设置doubel类型数据小数位 默认.00
* @param floatDecimalFormat 如 ".00
*/
public void setFloatDecimalFormat(DecimalFormat floatDecimalFormat) {
this.floatDecimalFormat = floatDecimalFormat;
}
/**
* 设置列的公式
* @param colFormula 存储i-1列的公式 涉及到的行号使用@替换 如A@+B@
*/
public void setColFormula(String[] colFormula) {
this.colFormula = colFormula;
}
/**
* 写excel.
* @param titleColumn 对应bean的属性名
* @param titleName excel要导出的表名
* @param titleSize 列宽
* @param dataList 数据
*/
public void wirteExcel(String titleColumn[],String titleName[],int titleSize[],List<?> dataList){
//添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
Sheet sheet = workbook.createSheet(this.sheetName);
//新建文件
OutputStream out = null;
try {
if(fileDir!=null){
//有文件路径
out = new FileOutputStream(fileDir);
}else{
//否则，直接写到输出流中
out = response.getOutputStream();//获取响应对象的输出流
fileName = fileName+".xls";
//解决火狐下载文件名乱码
String agent = request.getHeader("USER-AGENT");
if(agent != null && agent.indexOf("MSIE") == -1) {// FF
String newFileName = "=?UTF-8?B?" + (new String(Base64.encodeBase64(fileName.getBytes("UTF-8")))) + "?=";
response.setHeader("Content-Disposition", "attachment; filename=" + newFileName);
} else { // IE
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename="
+ URLEncoder.encode(fileName, "UTF-8"));
}
/* response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename="
+ URLEncoder.encode(fileName, "UTF-8"));*/
}
//写入excel的表头
Row titleNameRow = workbook.getSheet(sheetName).createRow(0);
//设置样式
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, titleFontType, (short) titleFontSize);
titleStyle = (HSSFCellStyle) setColor(titleStyle, titleBackColor, (short)10);
for(int i = 0;i < titleName.length;i++){
sheet.setColumnWidth(i, titleSize[i]*256); //设置宽度
Cell cell = titleNameRow.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellValue(titleName[i].toString());
}
//为表头添加自动筛选
if(!"".equals(address)){
CellRangeAddress c = (CellRangeAddress) CellRangeAddress.valueOf(address);
sheet.setAutoFilter(c);
}
//通过反射获取数据并写入到excel中
if(dataList!=null&&dataList.size()>0){
//设置样式
HSSFCellStyle dataStyle = workbook.createCellStyle();
titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, contentFontType, (short) contentFontSize);
if(titleColumn.length>0){
for(int rowIndex = 1;rowIndex<=dataList.size();rowIndex++){
Object obj = dataList.get(rowIndex-1); //获得该对象
Class clsss = obj.getClass(); //获得该对对象的class实例
Row dataRow = workbook.getSheet(sheetName).createRow(rowIndex);
for(int columnIndex = 0;columnIndex<titleColumn.length;columnIndex++){
String title = titleColumn[columnIndex].toString().trim();
if(!"".equals(title)){ //字段不为空
//使首字母大写
String UTitle = Character.toUpperCase(title.charAt(0))+ title.substring(1, title.length()); // 使其首字母大写;
String methodName = "get"+UTitle;
// 设置要执行的方法
Method method = clsss.getDeclaredMethod(methodName);
//获取返回类型
String returnType = method.getReturnType().getName();
String data = method.invoke(obj)==null?"":method.invoke(obj).toString();
Cell cell = dataRow.createCell(columnIndex);
if(data!=null&&!"".equals(data)){//判断数据是否为空，为空不填入
if("int".equals(returnType)){//判断数据类型
cell.setCellValue(Integer.parseInt(data));
}else if("long".equals(returnType)){
cell.setCellValue(Long.parseLong(data));
}else if("float".equals(returnType)){
cell.setCellValue(floatDecimalFormat.format(Float.parseFloat(data)));
}else if("double".equals(returnType)){
cell.setCellValue(doubleDecimalFormat.format(Double.parseDouble(data)));
}else{
cell.setCellValue(data);
}
}
}else{ //字段为空 检查该列是否是公式
if(colFormula!=null){
String sixBuf = colFormula[columnIndex].replace("@", (rowIndex+1)+"");
Cell cell = dataRow.createCell(columnIndex);
cell.setCellFormula(sixBuf.toString());
}
}
}
}
}
}
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 将16进制的颜色代码写入样式中来设置颜色
* @param style 保证style统一
* @param color 颜色：66FFDD
* @param index 索引 8-64 使用时不可重复
* @return
*/
public CellStyle setColor(CellStyle style,String color,short index){
if(color!=""&&color!=null){
//转为RGB码
int r = Integer.parseInt((color.substring(0,2)),16); //转为16进制
int g = Integer.parseInt((color.substring(2,4)),16);
int b = Integer.parseInt((color.substring(4,6)),16);
//自定义cell颜色
HSSFPalette palette = workbook.getCustomPalette();
palette.setColorAtIndex((short)index, (byte) r, (byte) g, (byte) b);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(index);
}
return style;
}
/**
* 设置字体并加外边框
* @param style 样式
* @param style 字体名
* @param style 大小
* @return
*/
public CellStyle setFontAndBorder(CellStyle style,String fontName,short size){
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints(size);
font.setFontName(fontName);
font.setBold(true);
style.setFont(font);
style.setBorderBottom(CellStyle.BORDER_THIN); //下边框
style.setBorderLeft(CellStyle.BORDER_THIN);//左边框
style.setBorderTop(CellStyle.BORDER_THIN);//上边框
style.setBorderRight(CellStyle.BORDER_THIN);//右边框
return style;
}
/**
* 删除文件
* @return
*/
public boolean deleteExcel(){
boolean flag = false;
File file = new File(this.fileDir);
// 判断目录或文件是否存在
if (!file.exists()) { // 不存在返回 false
return flag;
} else {
// 判断是否为文件
if (file.isFile()) { // 为文件时调用删除文件方法
file.delete();
flag = true;
}
}
return flag;
}
/**
* 根据传入路径删除文件
* @param path
* @return
*/
public boolean deleteExcel(String path){
boolean flag = false;
File file = new File(path);
// 判断目录或文件是否存在
if (!file.exists()) { // 不存在返回 false
return flag;
} else {
// 判断是否为文件
if (file.isFile()) { // 为文件时调用删除文件方法
file.delete();
flag = true;
}
}
return flag;
}
}
导出工具类使用示例
PoiExcelExport pee = new PoiExcelExport(request,response,fileName,"sheet1");
//对应的bean字段
String titleColumn[] = {"","financePayBank","financePayCardNumber","customerName","financeConsultingRate"};
//excel表头名
String titleName[] = {"交易流水号","收款开户行行号","收款账号","收款户名","收款金额"};
//表头宽度
int titleSize[] = {20,20,20,20,10};
//其他设置 set方法可全不调用
//String colFormula[] = new String[5];
//执行导出
pee.wirteExcel(titleColumn, titleName, titleSize, list);
导入工具类
支持部分公式，支持单元格时间格式
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* 改进，新增支持单元格公式(暂时不包含时间单元格带公式)和时间格式的读取
* 功能: [POI实现把Excel数据导入到指定的list中]
* 作者: tlimited
* 版本: 2.0
*/
public class ImportExcelUtil {
//正则表达式 用于匹配属性的第一个字母
private static final String REGEX = "[a-zA-Z]";
private static String[] titleColumn={};
/*
* 功能: Excel数据导入到bean,支持自己指定bean的字段
* 参数： in 文件的输入流
* 参数: originUrl[Excel表的所在路径]
* 参数: startRow[从第几行开始]
* 参数: endRow[到第几行结束
* (0表示所有行;
* 正数表示到第几行结束;
* 负数表示到倒数第几行结束)]
* 参数:titleColumns [指定的要匹配的bean字段]
* 参数: clazz[要返回的对象集合的类型]
*/
public static List<?> importExcel(InputStream is,String originUrl,int startRow,int endRow,String[] titleColumns,Class<?> clazz) throws IOException {
//是否打印提示信息
boolean showInfo=true;
titleColumn = titleColumns;
return doImportExcel(is,originUrl,startRow,endRow,showInfo,clazz);
}
/**
* 功能: Excel数据导入到bean,支持自己指定bean的字段
* 参数: originUrl[Excel表的所在路径]
* 参数: startRow[从第几行开始]
* 参数: endRow[到第几行结束
* (0表示所有行;
* 正数表示到第几行结束;
* 负数表示到倒数第几行结束)]
* 参数:titleColumns [指定的要匹配的bean字段]
* 参数: clazz[要返回的对象集合的类型]
*/
public static List<?> importExcel(String originUrl,int startRow,int endRow,String[] titleColumns,Class<?> clazz) throws IOException {
//是否打印提示信息
boolean showInfo=true;
titleColumn = titleColumns;
return doImportExcel(null,originUrl,startRow,endRow,showInfo,clazz);
}
/**
* 功能: Excel数据导入到指定bean
* 参数: originUrl[Excel表的所在路径]
* 参数: startRow[从第几行开始]
* 参数: endRow[到第几行结束
* (0表示所有行;
* 正数表示到第几行结束;
* 负数表示到倒数第几行结束)]
* 参数: clazz[要返回的对象集合的类型]
*/
public static List<?> importExcel(String originUrl,int startRow,int endRow,Class<?> clazz) throws IOException {
//是否打印提示信息
boolean showInfo=true;
//titleColumn = {};
return doImportExcel(null,originUrl,startRow,endRow,showInfo,clazz);
}
/**
* 功能:真正实现导入
* in和originUrl必须输入一个
*/
private static List<Object> doImportExcel(InputStream is,String originUrl,int startRow,int endRow,boolean showInfo,Class<?> clazz) throws IOException {
// 判断文件是否存在
File file = null;
// FormulaEvaluator formulaEvaluator = null;
if(originUrl != null){
file = new File(originUrl);
if (!file.exists()) {
throw new IOException("文件名为" + file.getName() + "的Excel文件不存在！");
}
}else{
}
// HSSFWorkbook wb = null;
Workbook wb = null;
InputStream fis=null;
List<Row> rowList = new ArrayList<Row>();
try {
if(is == null){
fis = new FileInputStream(file);
}else{
fis = is;
}
/* //用于处理公式的，目前已不用，已经有新的替代方案--2019.05.24
if (file.getName().endsWith("xlsx")) {
wb = new XSSFWorkbook(fis);
formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
} else {
wb = new HSSFWorkbook(fis);
formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
}*/
// 去读Excel
wb = WorkbookFactory.create(fis);
// wb = new HSSFWorkbook(fis);
Sheet sheet = wb.getSheetAt(0);
// 获取最后行号
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum > 0) { // 如果>0，表示有数据
out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：",showInfo);
}
Row row = null;
// 循环读取
for (int i = startRow; i <= lastRowNum + endRow; i++) {
row = sheet.getRow(i);
if (row != null) {
rowList.add(row);
out("第" + (i + 1) + "行：",showInfo,false);
// 获取每一单元格的值
for (int j = 0; j < row.getLastCellNum(); j++) {
String value = getCellValue(row.getCell(j));
if (!value.equals("")) {
out(value + " | ",showInfo,false);
}
}
out("",showInfo);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return returnObjectList(rowList,clazz);
}
/**
* 功能:获取单元格的值
*/
private static String getCellValue(Cell cell) {
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
Object result = "";
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING://字符串类型
result = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC: //数值类型，增强，可以处理时间格式
if(HSSFDateUtil.isCellDateFormatted(cell)){
result = sdf.format(cell.getDateCellValue());
}else{
result = cell.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_BOOLEAN://布尔类型
result = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA://新增表达式类型，（公式）--2019.05.14
FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
//String.valueOf(evaluator.evaluate(cell).getNumberValue());
result = evaluator.evaluate(cell).getNumberValue();
break;
case Cell.CELL_TYPE_ERROR://异常类型，不知道何时算异常
result = cell.getErrorCellValue();
break;
case Cell.CELL_TYPE_BLANK://空，不知道何时算空
break;
default:
break;
}
}
return result.toString();
}
/**
* 功能:返回指定的对象集合
*/
private static List<Object> returnObjectList(List<Row> rowList,Class<?> clazz) {
List<Object> objectList=null;
Object obj=null;
String attribute=null;
String value=null;
int j=0;
try {
objectList=new ArrayList<Object>();
Field[] declaredFields = clazz.getDeclaredFields();//获取所有字段,public和protected和private,但是不包括父类字段
Field[] newFields = null;
if (titleColumn.length>0){//判断时候有值
newFields = new Field[titleColumn.length];//用于存放遍历后的字段
//遍历匹配需要的字段
for (int i = 0;i<titleColumn.length;i++){
for (Field field : declaredFields) {
if (titleColumn[i]==field.getName().toString()){
newFields[i] = field;
}
}
}
}else {
newFields = declaredFields;
}
for (Row row : rowList) {
j=0;
obj = clazz.newInstance();//对象实例化
for (Field field : newFields) {
attribute=field.getName().toString();
value = getCellValue(row.getCell(j));
setAttributeValue(obj,attribute,value);
j++;
}
objectList.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
}
return objectList;
}
/**
* 功能:给指定对象的指定属性赋值
* @param obj 对象
* @param attribute 对象属性
* @param value 要赋予的值
*/
public static void setAttributeValue(Object obj,String attribute,String value) {
//得到该属性的set方法名
String method_name = convertToMethodName(attribute,obj.getClass(),true);
Method[] methods = obj.getClass().getMethods();
for (Method method : methods) {
/**
* 因为这里只是调用bean中属性的set方法，属性名称不能重复
* 所以set方法也不会重复，所以就直接用方法名称去锁定一个方法
* （注：在java中，锁定一个方法的条件是方法名及参数）
*/
if(method.getName().equals(method_name))
{
Class<?>[] parameterC = method.getParameterTypes();
try {
/**如果是(整型,浮点型,布尔型,字节型,时间类型,Long型,BigDecimal型),
* 按照各自的规则把value值转换成各自的类型
* 否则一律按类型强制转换(比如:String类型)
*/
if(parameterC[0] == int.class || parameterC[0]==java.lang.Integer.class)
{
value = value.substring(0, value.lastIndexOf("."));
method.invoke(obj,Integer.valueOf(value));
break;
}else if(parameterC[0] == float.class || parameterC[0]==java.lang.Float.class){
if (StringUtils.isNotBlank(value))
method.invoke(obj, Float.valueOf(value));
break;
}else if(parameterC[0] == BigDecimal.class || parameterC[0]==java.math.BigDecimal.class)//新增BigDecimal型
{
if (StringUtils.isNotBlank(value))
method.invoke(obj, new BigDecimal(value));
break;
} else if(parameterC[0] == double.class || parameterC[0]==java.lang.Double.class)
{
if (StringUtils.isNotBlank(value))
method.invoke(obj, Double.valueOf(value));
break;
}else if(parameterC[0] == byte.class || parameterC[0]==java.lang.Byte.class)
{
if (StringUtils.isNotBlank(value))
method.invoke(obj, Byte.valueOf(value));
break;
}else if(parameterC[0] == short.class || parameterC[0]==java.lang.Short.class)//新增short
{
if (StringUtils.isNotBlank(value))
method.invoke(obj, Short.valueOf(value));
break;
}else if(parameterC[0] == long.class|| parameterC[0]==java.lang.Long.class)//新增long
{
if (StringUtils.isNotBlank(value))
method.invoke(obj, Long.valueOf(value));
break;
}else if(parameterC[0] == boolean.class|| parameterC[0]==java.lang.Boolean.class)
{
if (StringUtils.isNotBlank(value))
method.invoke(obj, Boolean.valueOf(value));
break;
}else if(parameterC[0] == java.util.Date.class)
{
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date=null;
try {
if(value != null && !"".equals(value))
date=sdf.parse(value);
} catch (Exception e) {
e.printStackTrace();
}
method.invoke(obj,date);
break;
}else
{
method.invoke(obj,parameterC[0].cast(value));
break;
}
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
}
}
}
}
/**
* 功能:根据属性生成对应的set/get方法
*/
private static String convertToMethodName(String attribute,Class<?> objClass,boolean isSet) {
/** 通过正则表达式来匹配第一个字符 **/
Pattern p = Pattern.compile(REGEX);
Matcher m = p.matcher(attribute);
StringBuilder sb = new StringBuilder();
/** 如果是set方法名称 **/
if(isSet)
{
sb.append("set");
}else{
/** get方法名称 **/
try {
Field attributeField = objClass.getDeclaredField(attribute);
/** 如果类型为boolean **/
if(attributeField.getType() == boolean.class||attributeField.getType() == Boolean.class)
{
sb.append("is");
}else
{
sb.append("get");
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
}
/** 针对以下划线开头的属性 **/
if(attribute.charAt(0)!='_' && m.find())
{
sb.append(m.replaceFirst(m.group().toUpperCase()));
}else{
sb.append(attribute);
}
return sb.toString();
}
/**
* 功能:输出提示信息(普通信息打印)
*/
private static void out(String info, boolean showInfo) {
if (showInfo) {
System.out.print(info + (showInfo ? "\n" : ""));
}
}
/**
* 功能:输出提示信息(同一行的不同单元格信息打印)
*/
private static void out(String info, boolean showInfo, boolean nextLine) {
if (showInfo) {
if(nextLine)
{
System.out.print(info + (showInfo ? "\n" : ""));
}else
{
System.out.print( info );
}
}
}
// private POIFSFileSystem fs;
private HSSFWorkbook wb;
/**
* 读取Excel表格表头的内容
* @param filePath
* @return String 表头内容的数组
*/
public String[] readExcelTitle(String filePath) throws Exception{
try {
File file = new File(filePath);
FileInputStream fs = new FileInputStream(file);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(0);
// 标题总列数
int colNum = row.getPhysicalNumberOfCells();
System.out.println("colNum:" + colNum);
String[] title = new String[colNum];
for (int i = 0; i < colNum; i++) {
title[i] = getStringCellValue(row.getCell((short) i));
// title[i] = getCellFormatValue(row.getCell((short) i));
}
return title;
}
/**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
private String getStringCellValue(Cell cell) {
String strCell = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING://字符类型
strCell = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:// 数字类型
strCell = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN://
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:// 公式
FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
// strCell = String.valueOf(cell.getNumericCellValue());
strCell = String.valueOf(evaluator.evaluate(cell).getNumberValue());
case Cell.CELL_TYPE_BLANK:
strCell = "";
break;
case Cell.CELL_TYPE_ERROR:
strCell =String.valueOf(cell.getErrorCellValue());
break;
default:
strCell = "";
break;
}
if ("".equals(strCell) || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
}
public static void main(String[] args) throws Exception {
//bean字段
String titleColumn[] = {"financeLoanBank","financePayCardNumber","customerName","relationMan","zipCode","fixedTelephone","relationAddress",
"centificateType","customerCentificateNum","customerPhoneNumber","email","businessType"};
//导入的excel文件路径
/* */String originUrl="D:\\backup\\Downloads\\tesss.xls";
int startRow=4;//开始导入行
int endRow=0;//结束行，0默认全部导入
//导入到list中
List<WithholdAgreementSign> bookList = (List<WithholdAgreementSign>) ImportExcelUtil.importExcel(originUrl, startRow, endRow, WithholdAgreementSign.class);
System.out.println(bookList.toString());
/* ImportExcel importExcel = new ImportExcel();
String[] title = importExcel.readExcelTitle("E:\\ww\\123.xls");
for (int i=0; i<title.length;i++){
System.out.println(title[i]);
}*/
}
}
附加手写方式实现导出：
/**
* 导出文件交易汇总表 --2018.10.30
* @param request
* @param response
*/
@RequestMapping(params = "exportExcelTotal")
public void exportExcelTotal(HttpServletRequest request, HttpServletResponse response){
String agent = ResourceUtil.getSessionUser().getUserName();
String start = request.getParameter("transTime_begin");
String end = request.getParameter("transTime_end");
System.out.println(agent);
List<Map<String, Object>> total = payManagementDao.getTotal(agent, start, end);
HSSFWorkbook workbook = new HSSFWorkbook();//创建对象
Sheet sheet = workbook.createSheet("sheet1");//创建sheet
Row titleNameRow = workbook.getSheet("sheet1").createRow(0);//创建行
String titleName[] = {"公司名字","交易笔数","扣款金额"};
//表头宽度
int titleSize[] = {20,20,20};
for(int i = 0;i < titleName.length;i++){//创建表头每一列
sheet.setColumnWidth(i, titleSize[i]*256); //设置宽度
Cell cell = titleNameRow.createCell(i);
cell.setCellValue(titleName[i].toString());
}
if(total != null){
for(int i = 0;i <total.size();i++){//手动为每行填充数据
Row dataRow = workbook.getSheet("sheet1").createRow(i+1);
Cell cell = dataRow.createCell(0);
cell.setCellValue(total.get(i).get("company_name").toString());
Cell cell2 = dataRow.createCell(1);
cell2.setCellValue(total.get(i).get("counts").toString());
Cell cell3 = dataRow.createCell(2);
cell3.setCellValue(total.get(i).get("totalmoney").toString());
}
}
//下面是下载文件处理
OutputStream out =null;
try{
//否则，直接写到输出流中
out = response.getOutputStream();// 获取响应对象的输出流
String fileName = "交易汇总表.xls";
// 解决火狐下载文件名乱码
String agent1 = request.getHeader("USER-AGENT");
if (agent1 != null && agent1.indexOf("MSIE") == -1) {// FF
String newFileName = "=?UTF-8?B?" + (new String(Base64.encodeBase64(fileName.getBytes("UTF-8"))))
+ "?=";
response.setHeader("Content-Disposition", "attachment; filename=" + newFileName);
} else { // IE
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition",
"attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
}
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
上面的导入工具类的加强版–2019.05.26
增加可读取带公式的时间单元格，但是稳定性不祥，测试过一两次能行，
例子如：
package com.jeecg.postLoan.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
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.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* 改进，新增支持单元格公式(包含时间单元格带公式)和时间格式的读取
* 功能: [POI实现把Excel数据导入到指定的list中]
* 作者: tlimited
* 版本: 2.1
*/
public class ImportExcelUtil {
//正则表达式 用于匹配属性的第一个字母
private static final String REGEX = "[a-zA-Z]";
private static String[] titleColumn={};
/*
* 功能: Excel数据导入到bean,支持自己指定bean的字段
* 参数： in 文件的输入流
* 参数: originUrl[Excel表的所在路径]
* 参数: startRow[从第几行开始]
* 参数: endRow[到第几行结束
* (0表示所有行;
* 正数表示到第几行结束;
* 负数表示到倒数第几行结束)]
* 参数:titleColumns [指定的要匹配的bean字段]
* 参数: clazz[要返回的对象集合的类型]
*/
public static List<?> importExcel(InputStream is,String originUrl,int startRow,int endRow,String[] titleColumns,Class<?> clazz) throws IOException {
//是否打印提示信息
boolean showInfo=true;
titleColumn = titleColumns;
return doImportExcel(is,originUrl,startRow,endRow,showInfo,clazz);
}
/**
* 功能: Excel数据导入到bean,支持自己指定bean的字段
* 参数: originUrl[Excel表的所在路径]
* 参数: startRow[从第几行开始]
* 参数: endRow[到第几行结束
* (0表示所有行;
* 正数表示到第几行结束;
* 负数表示到倒数第几行结束)]
* 参数:titleColumns [指定的要匹配的bean字段]
* 参数: clazz[要返回的对象集合的类型]
*/
public static List<?> importExcel(String originUrl,int startRow,int endRow,String[] titleColumns,Class<?> clazz) throws IOException {
//是否打印提示信息
boolean showInfo=true;
titleColumn = titleColumns;
return doImportExcel(null,originUrl,startRow,endRow,showInfo,clazz);
}
/**
* 功能: Excel数据导入到指定bean
* 参数: originUrl[Excel表的所在路径]
* 参数: startRow[从第几行开始]
* 参数: endRow[到第几行结束
* (0表示所有行;
* 正数表示到第几行结束;
* 负数表示到倒数第几行结束)]
* 参数: clazz[要返回的对象集合的类型]
*/
public static List<?> importExcel(String originUrl,int startRow,int endRow,Class<?> clazz) throws IOException {
//是否打印提示信息
boolean showInfo=true;
//titleColumn = {};
return doImportExcel(null,originUrl,startRow,endRow,showInfo,clazz);
}
/**
* 功能:真正实现导入
* in和originUrl必须输入一个
*/
private static List<Object> doImportExcel(InputStream is,String originUrl,int startRow,int endRow,boolean showInfo,Class<?> clazz) throws IOException {
// 判断文件是否存在
File file = null;
// FormulaEvaluator formulaEvaluator = null;
if(originUrl != null){
file = new File(originUrl);
if (!file.exists()) {
throw new IOException("文件名为" + file.getName() + "的Excel文件不存在！");
}
}else{
}
// HSSFWorkbook wb = null;
Workbook wb = null;
InputStream fis=null;
List<Row> rowList = new ArrayList<Row>();
try {
if(is == null){
fis = new FileInputStream(file);
}else{
fis = is;
}
/* //用于处理公式的，目前已不用，已经有新的替代方案--2019.05.24
if (file.getName().endsWith("xlsx")) {
wb = new XSSFWorkbook(fis);
formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
} else {
wb = new HSSFWorkbook(fis);
formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
}*/
// 去读Excel
wb = WorkbookFactory.create(fis);
// wb = new HSSFWorkbook(fis);
Sheet sheet = wb.getSheetAt(0);
// 获取最后行号
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum > 0) { // 如果>0，表示有数据
out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：",showInfo);
}
Row row = null;
// 循环读取
for (int i = startRow; i <= lastRowNum + endRow; i++) {
row = sheet.getRow(i);
if (row != null) {
rowList.add(row);
out("第" + (i + 1) + "行：",showInfo,false);
// 获取每一单元格的值
for (int j = 0; j < row.getLastCellNum(); j++) {
String value = getCellValue(row.getCell(j));
if (!value.equals("")) {
out(value + " | ",showInfo,false);
}
}
out("",showInfo);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return returnObjectList(rowList,clazz);
}
/**
* 功能:获取单元格的值
*/
private static String getCellValue(Cell cell) {
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
Object result = "";
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING://字符串类型
result = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC: //数值类型，增强，可以处理时间格式
if(HSSFDateUtil.isCellDateFormatted(cell)){
result = sdf.format(cell.getDateCellValue());
}else{
result = cell.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_BOOLEAN://布尔类型
result = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA://新增表达式类型，（公式）--2019.05.14
FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
//String.valueOf(evaluator.evaluate(cell).getNumberValue());
// result = evaluator.evaluate(cell).getNumberValue();//普通数值 //有bug请用这行代码
//以下代码存在一个bug，在区分数字和时间时候可能会误认为某些数字也是时间，如果有bug，请注释掉用上面一行代码，
/*****************************************/
double val = evaluator.evaluate(cell).getNumberValue();
//判断公式执行后的结果是否为时间，进行时间转化处理
if(DateUtil.isValidExcelDate(val)){
boolean bDate = false;
CellStyle style = cell.getCellStyle();
if(style==null)
bDate=false;
int i = style.getDataFormat();
String f = style.getDataFormatString();
bDate = DateUtil.isADateFormat(i, f);
if(bDate){
result = sdf.format(HSSFDateUtil.getJavaDate(evaluator.evaluate(cell).getNumberValue()));//时间类型
}else{
result = evaluator.evaluate(cell).getNumberValue();//普通数值
}
}else{
result = evaluator.evaluate(cell).getNumberValue();//普通数值
}
/*****************************************/
break;
case Cell.CELL_TYPE_ERROR://异常类型，不知道何时算异常
result = cell.getErrorCellValue();
break;
case Cell.CELL_TYPE_BLANK://空，不知道何时算空
break;
default:
break;
}
}
return result.toString();
}
/**
* 功能:返回指定的对象集合
*/
private static List<Object> returnObjectList(List<Row> rowList,Class<?> clazz) {
List<Object> objectList=null;
Object obj=null;
String attribute=null;
String value=null;
int j=0;
try {
objectList=new ArrayList<Object>();
Field[] declaredFields = clazz.getDeclaredFields();//获取所有字段,public和protected和private,但是不包括父类字段
Field[] newFields = null;
if (titleColumn.length>0){//判断时候有值
newFields = new Field[titleColumn.length];//用于存放遍历后的字段
//遍历匹配需要的字段
for (int i = 0;i<titleColumn.length;i++){
for (Field field : declaredFields) {
if (titleColumn[i]==field.getName().toString()){
newFields[i] = field;
}
}
}
}else {
newFields = declaredFields;
}
for (Row row : rowList) {
j=0;
obj = clazz.newInstance();//对象实例化
for (Field field : newFields) {
attribute=field.getName().toString();
value = getCellValue(row.getCell(j));
setAttributeValue(obj,attribute,value);
j++;
}
objectList.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
}
return objectList;
}
/**
* 功能:给指定对象的指定属性赋值
* @param obj 对象
* @param attribute 对象属性
* @param value 要赋予的值
*/
public static void setAttributeValue(Object obj,String attribute,String value) {
//得到该属性的set方法名
String method_name = convertToMethodName(attribute,obj.getClass(),true);
Method[] methods = obj.getClass().getMethods();
for (Method method : methods) {
/**
* 因为这里只是调用bean中属性的set方法，属性名称不能重复
* 所以set方法也不会重复，所以就直接用方法名称去锁定一个方法
* （注：在java中，锁定一个方法的条件是方法名及参数）
*/
if(method.getName().equals(method_name))
{
Class<?>[] parameterC = method.getParameterTypes();
try {
/**如果是(整型,浮点型,布尔型,字节型,时间类型,Long型,BigDecimal型),
* 按照各自的规则把value值转换成各自的类型
* 否则一律按类型强制转换(比如:String类型)
*/
if(parameterC[0] == int.class || parameterC[0]==java.lang.Integer.class)
{
value = value.substring(0, value.lastIndexOf("."));
method.invoke(obj,Integer.valueOf(value));
break;
}else if(parameterC[0] == float.class || parameterC[0]==java.lang.Float.class){
if (StringUtils.isNotBlank(value))
method.invoke(obj, Float.valueOf(value));
break;
}else if(parameterC[0] == BigDecimal.class || parameterC[0]==java.math.BigDecimal.class)//新增BigDecimal型
{
if (StringUtils.isNotBlank(value))
method.invoke(obj, new BigDecimal(value));
break;
} else if(parameterC[0] == double.class || parameterC[0]==java.lang.Double.class)
{
if (StringUtils.isNotBlank(value))
method.invoke(obj, Double.valueOf(value));
break;
}else if(parameterC[0] == byte.class || parameterC[0]==java.lang.Byte.class)
{
if (StringUtils.isNotBlank(value))
method.invoke(obj, Byte.valueOf(value));
break;
}else if(parameterC[0] == short.class || parameterC[0]==java.lang.Short.class)//新增short
{
if (StringUtils.isNotBlank(value))
method.invoke(obj, Short.valueOf(value));
break;
}else if(parameterC[0] == long.class|| parameterC[0]==java.lang.Long.class)//新增long
{
if (StringUtils.isNotBlank(value))
method.invoke(obj, Long.valueOf(value));
break;
}else if(parameterC[0] == boolean.class|| parameterC[0]==java.lang.Boolean.class)
{
if (StringUtils.isNotBlank(value))
method.invoke(obj, Boolean.valueOf(value));
break;
}else if(parameterC[0] == java.util.Date.class)
{
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date=null;
try {
if(value != null && !"".equals(value))
date=sdf.parse(value);
} catch (Exception e) {
e.printStackTrace();
}
method.invoke(obj,date);
break;
}else
{
method.invoke(obj,parameterC[0].cast(value));
break;
}
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
}
}
}
}
/**
* 功能:根据属性生成对应的set/get方法
*/
private static String convertToMethodName(String attribute,Class<?> objClass,boolean isSet) {
/** 通过正则表达式来匹配第一个字符 **/
Pattern p = Pattern.compile(REGEX);
Matcher m = p.matcher(attribute);
StringBuilder sb = new StringBuilder();
/** 如果是set方法名称 **/
if(isSet)
{
sb.append("set");
}else{
/** get方法名称 **/
try {
Field attributeField = objClass.getDeclaredField(attribute);
/** 如果类型为boolean **/
if(attributeField.getType() == boolean.class||attributeField.getType() == Boolean.class)
{
sb.append("is");
}else
{
sb.append("get");
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
}
/** 针对以下划线开头的属性 **/
if(attribute.charAt(0)!='_' && m.find())
{
sb.append(m.replaceFirst(m.group().toUpperCase()));
}else{
sb.append(attribute);
}
return sb.toString();
}
/**
* 功能:输出提示信息(普通信息打印)
*/
private static void out(String info, boolean showInfo) {
if (showInfo) {
System.out.print(info + (showInfo ? "\n" : ""));
}
}
/**
* 功能:输出提示信息(同一行的不同单元格信息打印)
*/
private static void out(String info, boolean showInfo, boolean nextLine) {
if (showInfo) {
if(nextLine)
{
System.out.print(info + (showInfo ? "\n" : ""));
}else
{
System.out.print( info );
}
}
}
// private POIFSFileSystem fs;
private HSSFWorkbook wb;
/**
* 读取Excel表格表头的内容
* @param filePath
* @return String 表头内容的数组
*/
public String[] readExcelTitle(String filePath) throws Exception{
try {
File file = new File(filePath);
FileInputStream fs = new FileInputStream(file);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(0);
// 标题总列数
int colNum = row.getPhysicalNumberOfCells();
System.out.println("colNum:" + colNum);
String[] title = new String[colNum];
for (int i = 0; i < colNum; i++) {
title[i] = getStringCellValue(row.getCell((short) i));
// title[i] = getCellFormatValue(row.getCell((short) i));
}
return title;
}
/**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
private String getStringCellValue(Cell cell) {
String strCell = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING://字符类型
strCell = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:// 数字类型
strCell = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN://
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:// 公式
FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
// strCell = String.valueOf(cell.getNumericCellValue());
strCell = String.valueOf(evaluator.evaluate(cell).getNumberValue());
case Cell.CELL_TYPE_BLANK:
strCell = "";
break;
case Cell.CELL_TYPE_ERROR:
strCell =String.valueOf(cell.getErrorCellValue());
break;
default:
strCell = "";
break;
}
if ("".equals(strCell) || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
}
public static void main(String[] args) throws Exception {
//bean字段
String titleColumn[] = {"financeLoanBank","financePayCardNumber","customerName","relationMan","zipCode","fixedTelephone","relationAddress",
"centificateType","customerCentificateNum","customerPhoneNumber","email","businessType"};
//导入的excel文件路径
/* String originUrl="D:\\backup\\Downloads\\tesss.xls";*/
int startRow=4;//开始导入行
int endRow=0;//结束行，0默认全部导入
//导入到list中
// List<WithholdAgreementSign> bookList = (List<WithholdAgreementSign>) ImportExcelUtil.importExcel(originUrl, startRow, endRow, WithholdAgreementSign.class);
// System.out.println(bookList.toString());
/* ImportExcel importExcel = new ImportExcel();
String[] title = importExcel.readExcelTitle("E:\\ww\\123.xls");
for (int i=0; i<title.length;i++){
System.out.println(title[i]);
}*/
// HSSFDateUtil.getJavaDate(43748);
System.out.println( HSSFDateUtil.getJavaDate(43748));
}
}
扫一扫
1043
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
