Java中excel表数据的批量导入方法
发布时间：2020-05-25 10:28:59 所属栏目：Java 来源：互联网
导读：本文实例为大家分享了Java中excel表数据的批量导入，供大家参考，具体内容如下
本文实例为大家分享了Java中excel表数据的批量导入，供大家参考，具体内容如下
首先看下工具类：
import java.awt.Color;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.swing.text.AttributeSet;
import javax.swing.text.Element;
import javax.swing.text.html.CSS;
import javax.swing.text.html.HTMLDocument;
import javax.swing.text.html.HTMLEditorKit;
import cn.vrview.dev.common.exception.BusinessException;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.util.HtmlUtils;
import cn.vrview.dev.common.util.StringUtil;
public class ExcelTools {
/** log */
private static Logger log = LogManager.getLogger();
/**
* 导出excel
* <p>
* 使用方法：<br>
* <code> List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();<br>
* is = ExcelTools.exportXLS(dataList,new String[] {"createTime:日期","name:名称","sex:性别","remark:备注"});
* </code>
*
* @param collect
* 待导出的数据集合
* @param header
* 要导出的列
* @return InputStream 返回文件流
*/
public static InputStream exportXLS(Collection<Map<String,Object>> collect,String[] header) {
ByteArrayOutputStream out = new ByteArrayOutputStream();
HSSFWorkbook book = new HSSFWorkbook();
try {
// 添加一个sheet
HSSFSheet sheet = book.createSheet("Sheet1");
// 定义要导出的列名集合
Set<String> columns = new HashSet<String>();
// 设置单元格背景色
HSSFCellStyle cellStyle = book.createCellStyle();
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(new HSSFColor.YELLOW().getIndex());
// 生成表头
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellValue("序号");
// 列号从1开始
int n = 1;
// 解析头字符串
for (String str : header) {
String[] arr = str.split(":");
columns.add(n + "," + arr[0]);// 添加要导出的字段名并且与列号n绑定
cell = row.createCell(n);
cell.setCellStyle(cellStyle);
cell.setCellValue(arr[1]);
n++;
}
// 生成数据行从1开开始，0为表头
int i = 1;
// 生成数据行列
for (Map<String,Object> map : collect) {
HSSFRow dataRow = sheet.createRow(i);
// 生成序号
dataRow.createCell(0).setCellValue(i);
// 生成其他列
for (String column : columns) {
// 用逗号分割获得字段名，[0]为列号用于和表头标题对应上
String columnName = column.split(",")[1];
// 生成序号列
cell = dataRow.createCell(Integer.parseInt(column.split(",")[0]));
String value = "";
value = map.get(columnName) + "";
// 当value为null 时转换为""
if ("null".equals(value)) {
value = "";
}
RichTextString richTextString = processHtml(book,value);
cell.getCellStyle().setWrapText(false);
cell.setCellValue(richTextString);
}
i++;
}
book.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
return new ByteArrayInputStream(out.toByteArray());
}
/**
* 获得excel文件数据<br>
* 用法：<br>
* SheetInfo sheetInfo = new ExcelTools().new SheetInfo();<br>
* sheetInfo.setRowTitle(0); List<String> sheets = new ArrayList<String>();<br>
* String sheetName = "Sheet1"; sheets.add(sheetName);<br>
* sheetInfo.setSheetNames(sheets); <br>
* sheetInfo.setColumnsMapping(new String[] { "prodName:商品名称",* "prodSpec:规格","collectPrice:价格:" + {@link RegExpEnum}
* RegExpEnum.NOTEMPTY_ISNUMBER,"priceUnit:单位","collectMarket:报价市场",* "prodLevel:等级" }); <br>
* Map<String,List> data = ExcelTools.getExcel(new File(path),sheetInfo);
*
* @param
*
* @param sheetInfo
* 初始化信息
* @return Map {sheet1:List}
* @throws Exception
* Exception
*/
@SuppressWarnings("rawtypes")
public static Map getExcel(File f,SheetInfo sheetInfo,String excelType) throws Exception {
return getExcel(new FileInputStream(f),sheetInfo,excelType);
}
@SuppressWarnings({ "rawtypes","unchecked" })
public static Map getExcel(InputStream in,String excelType) throws Exception {
Map<String,String> columnsMap = new HashMap<String,String>();
// 列验证表达式map
List<String> errMsg = new ArrayList<String>();
int errNum = 0;// 错误总数
int errLimit = 10;// 限制错误提示数
/** 用于存储Excel根据指定规则读取的所有内容 */
Map excelInfo = new HashMap();
Workbook book = null;
try {
if (excelType.equals("xls")) {
book = new HSSFWorkbook(in);
//throw new BusinessException("excel版本太低，请使用2007以上版本（扩展名为：xlsx）");
} else {
book = new XSSFWorkbook(in);
}
} catch (OutOfMemoryError e) {
throw new RuntimeException("当前excel文件过大，请检查各个sheet表格中是否有无效空数据，包括带有格式和线框的行列数据，请删除这些无效数据！正常excle文件大小【1M】以内");
}
// checkTitle(book,sheetInfo);
// 获得工作表数量
int sheetNum = sheetInfo.getSheetNames().size();
// 循环所有的工作表，读取里面的数据
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
// 获得当前工作表对象
String sheetName = HtmlUtils.htmlUnescape(sheetInfo.getSheetNames().get(sheetIndex));
Map<String,String> validateMap = new HashMap<String,String>();
for (String mapstr : sheetInfo.getColumnsMapping().get(sheetName)) {
String[] arr = mapstr.split(":");
columnsMap.put(arr[1],arr[0]);
if (arr.length == 3) {// 若果验证表达式不为空，则添加到map中
validateMap.put(arr[1],arr[2]);
}
}
Sheet sheet = book.getSheet(sheetName);
if (null == sheet) {
throw new RuntimeException(String.format("获取表失败，请确认Sheet《%s》是否存在于excel中",sheetName));
}
// 用于存储所工作表中的数据内容
List sheetList = new ArrayList();
// 获取当前表格的行数
int rows = sheet.getLastRowNum();
// 获取当前表格的列数
int columns = sheet.getRow(sheetInfo.getRowTitle()).getLastCellNum();
if (rows <= sheetInfo.getRowTitle()) {// 如果当前表格没有需要的数据就继续下一次循环
continue;
}
// 获得当前工作表标题内容
List<String> titleList = new ArrayList<String>();
// 循环每一行中的每一个单元格，读取单元格内的值
Row titleRow = sheet.getRow(sheetInfo.getRowTitle());
for (int jj = 0; jj < columns; jj++) {
Cell cellTitle = titleRow.getCell(jj);
if (cellTitle != null) {
int row = cellTitle.getRowIndex();
int column = cellTitle.getColumnIndex();
if (isMergedRegion(sheet,row,column)) {
titleList.add(getMergedRegionValue(sheet,column));
} else {
titleList.add(getCellValue(cellTitle));
}
} else {
throw new RuntimeException("表头读取错误,当前设置为第" + (sheetInfo.getRowTitle() + 1) + "行<br/>表头内容为：" + titleRow + "，请检查是否正确，如有异常，可删除表头格式，重新粘贴表头！");
}
}
// System.out.println(titleList);
// 验证表头
String[] titles = sheetInfo.getColumnsMapping().get(sheetName);
for (String s : titles) {
String[] colArr = s.split(":");
// 如果Excel表格中的表头缺少该字段
boolean include = false;
for (String t : titleList) {
if (StringUtils.deleteWhitespace(t).equalsIgnoreCase(colArr[1])) {
include = true;
break;
}
}
if (!include) {
throw new RuntimeException("【" + colArr[1] + "】'列不存在！当前Excel表头：" + titleList);
}
}
// 开始循环每一行，读取每一行的值,从标题下面一行开始读取
for (int i = sheetInfo.getRowTitle() + 1; i <= rows; i++) {
Map rowMap = new HashMap();
Row dataRow = sheet.getRow(i);
if (dataRow == null) {
throw new RuntimeException(String.format("excel第[%d]行为空，请检查!",i + 1));
}
for (int j = 0; j < columns; j++) {// 循环每一行中的每一个单元格，读取单元格内的值
String columnTitle = titleList.get(j);
if ("".equals(columnTitle)) {
continue;
} else {
Cell cell = dataRow.getCell(j);
String value = "";
String columnMapping = "";
// 单元列对应的entity属性名
for (String title : columnsMap.keySet()) {
if (StringUtils.deleteWhitespace(columnTitle).equalsIgnoreCase(title)) {
columnMapping = columnsMap.get(title);
break;
}
}
if (null != cell) {
cell.setCellType(Cell.CELL_TYPE_STRING);
CellStyle cellStyle= cell.getCellStyle();
//单元格背景颜色
if (excelType.equals("xls")) {
HSSFColor color=(HSSFColor) cellStyle.getFillForegroundColorColor();
if (j==0 && color!=null) {
rowMap.put("rowColor",convertRGBToHex(color.getTriplet()));
}
} else {
XSSFColor color=(XSSFColor) cellStyle.getFillForegroundColorColor();
if (j==0 && color!=null) {
rowMap.put("rowColor",color.getARGBHex().substring(2));
}
}
value = filterStr(cell + "");
int mergRow = getMergedRegionRow(sheet,cell);
if (mergRow > 0 && !StringUtil.isEmpty(value)) {
String rowspan="";
if (rowMap.get("rowspan")!=null) {
rowspan=rowMap.get("rowspan")+",";
}
rowMap.put("rowspan",rowspan+columnMapping+"-"+value+"-"+(mergRow + 1));
}
if ( cell.getCellComment()!=null) {
//System.out.println(columnMapping+"@comment:"+cell.getCellComment().getString());
rowMap.put(columnMapping+"@comment",cell.getCellComment().getString());
}
}
// String columnMapping = columnsMap.get(columnTitle);
String validateReg = "";
String validateRegMsg = "";
if (null != validateMap.get(columnTitle)) {
// 验证正则表达式
RegExpEnum eum = RegExpEnum.valueOf(validateMap.get(columnTitle));
validateReg = eum.getValue();
validateRegMsg = eum.getText();
}
if (!StringUtil.isEmpty(validateReg)) {
if (value.matches(validateReg)) {
rowMap.put(columnMapping,value);
} else {
errNum++;
if (errNum <= errLimit) {
errMsg.add("第" + i + "行：【" + columnTitle + "】数据为:'" + value.trim() + "' 不匹配！【" + validateRegMsg + "】</br>n");
}
}
} else {
if (StringUtil.isEmpty(columnMapping)) {
continue;
} else {
//int row = cell.getRowIndex();
///int column = cell.getColumnIndex();
//if (isMergedRegion(sheet,column)) {
// rowMap.put(columnMapping,getMergedRegionValue(sheet,column));
/ |