直接读取excle的数据去add
不用上传到服务器，再去解析数据
很简单很直接很粗暴的excle导入新增
直接上代码--excle有一处很烦的，就是版本问题，我是用的xls版本的，直接让用户下载xls的模版，然后用我指定的模版去新增数据，这样我直接读取xls版本的数据
======================================
/**
* excle上传文件
*/
@RequestMapping(value="/upload")
public CIPResponseMsg upload(HttpServletRequest request, HttpServletResponse response) throws Exception {
CIPResponseMsg msg = new CIPResponseMsg();
try {
CIPRuntimeOperator operator = LoginOperator.getOperator(request, response);
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile("file");
if(file.isEmpty()){
throw new Exception("文件不存在！");
}
InputStream in = file.getInputStream();
ExcelUtils parser = new ExcelUtils();
parser.initWorkBook(in);
List<String> titleList = parser.getTitleFromSheet(0, 0);
String[] reference = { "项目信息", "项目类型", "项目负责人","所属部门"};
boolean isValid = this.validateFileContentFormat(reference,titleList);
//用于校验excel的表头是否正确
if (!isValid) {
msg.errorCode = 100003;
msg.msg = "文件内容格式不正确，请重新下载模板！";
responseMsg(response, msg);
return msg;
}
List<Map<String, String>> valueList = parser.getDataInSheet(0, 1);
int size = valueList.size();
int successCount = 0;
int errorCount = 0;
int car_exist = 0;
StringBuffer erroName=new StringBuffer("");
for (int j = 0; j < size; j++) {
HCM_project_informVO vo = new HCM_project_informVO();
Map<String, String> valueMap = valueList.get(j);
String project_inform = valueMap.get("项目信息").trim();
String project_type = valueMap.get("项目类型").trim();
String fuze_id_name = valueMap.get("项目负责人").trim();
String three_id_name = valueMap.get("所属部门").trim();
//项目信息
if(StringUtils.isBlank(project_inform)){
errorCount++;
erroName.append("第'"+(j+1)+"'条据中,项目信息不能为空！");
continue;
}
//项目类型
if(StringUtils.isBlank(project_type)){
errorCount++;
erroName.append("第'"+(j+1)+"'条据中,项目类型不能为空！");
continue;
}
//对项目负责人的验证
if (StringUtils.isBlank(fuze_id_name)) {
errorCount++;
erroName.append("第'"+(j+1)+"'条据中,项目负责人不能为空！");
continue;
}
//所属部门
if(StringUtils.isBlank(three_id_name)){
errorCount++;
erroName.append("第'"+(j+1)+"'条据中,所属部门不能为空！");
continue;
}
List<HCM_project_informData> bmdt = dataDao.getOrg(three_id_name);
if(bmdt.size()<=0){
errorCount++;
erroName.append("第'"+(j+1)+"'条据中，未找到对应的部门机构！");
continue;
}
List<HCM_project_informData> namedt2 = dataDao.getName(fuze_id_name);
if(namedt2.size()<=0){
errorCount++;
erroName.append("第'"+(j+1)+"'条据中，项目负责人未在数据库中找到！");
continue;
}
if(project_type=="重点岗位"||project_type.equals("重点岗位")||project_type=="开放招聘"||project_type.equals("开放招聘")){
int a=0;
}else{
errorCount++;
erroName.append("第'"+(j+1)+"'条据中,项目类型输入不正确！");
continue;
}
vo.setProject_inform(project_inform);
vo.setProject_type(project_type);
//项目名称去重
List<HCM_project_informData> dt2 = dataDao.findPro(project_inform);
if(dt2.size()>0){
msg.errorCode = 10003;
msg.msg = "导入失败,错误数据第"+(j+1)+"条，"+"错误信息："+"项目名称重复!";
responseMsg(response, msg);
return msg;
}else{
//根据负责人name和部门name找出负责人id
List<HCM_project_informData> dt = dataDao.findByFzr(three_id_name);
vo.setFuze_id(namedt2.get(0).getUser_id()); //根据项目负责人名找到id
vo.setThree_id(bmdt.get(0).getOrgId()); //根据部门名称查询部门id
dataService.addData(vo, operator);
msg.data = true;
msg.msg="上传成功";
return msg;
}
}
long endTime = System.currentTimeMillis();
if(0==errorCount){
msg.errorCode = 0;
msg.msg = CIPErrorCode.CALL_SUCCESS.name;
responseMsg(response, msg);
return msg;
}else {
msg.errorCode = 10003;
msg.msg = "导入失败,错误数据"+errorCount+"条，"+"错误信息："+erroName.toString();
responseMsg(response, msg);
return msg;
}
} catch (CIPServiceException e) {
CIPErrorCode error = e.getErrorCode();
msg.errorCode = error.code;
msg.msg = error.name;
} catch (CIPDaoException e) {
CIPErrorCode error = e.getErrorCode();
msg.errorCode = error.code;
msg.msg = error.name;
} catch (CIPRuntimeException e) {
CIPErrorCode error = e.getErrorCode();
msg.errorCode = error.code;
msg.msg = error.name;
}catch(Exception e){
e.printStackTrace();
}
return msg;
}
===========================
用到的工具类ExcelUtils
package com.yd.hcm.utils;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
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.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
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.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import com.yd.common.utils.DateUtils;
import com.yd.hcm.rec.data.HCM_project_informData;
public class ExcelUtils{
private static final Log log = LogFactory.getLog(ExcelUtils.class);
//默认列宽
private static int columnWidth = 21;
private Logger logger = Logger.getLogger(ExcelUtils.class);
private HSSFWorkbook workbook;// 工作簿
public ExcelUtils() {
}
/**
* 导入时通过文件初始化
* @param file
*/
public ExcelUtils(File file) {
initWorkBook(file);
}
/**
* 获取sheet
* @param sheetNumber
* @return
*/
public HSSFSheet getSheet(int sheetNumber){
return workbook.getSheetAt(sheetNumber);
}
public HSSFWorkbook getHSSFWorkbook(){
return workbook;
}
/**
* 判断workbook 是否为空
* @return
*/
public boolean isNullWorkBook(){
return workbook==null?true:false;
}
/**
* 初始化workbook方法
* @param file
*/
public void initWorkBook(File file){
try {
// 获取工作薄workbook
workbook = new HSSFWorkbook(new FileInputStream(file));
} catch (FileNotFoundException e) {
log.debug(e);
} catch (IOException e) {
log.debug(e);
}
}
public void initWorkBook(InputStream input){
try {
workbook = new HSSFWorkbook(input);
} catch (IOException e) {
log.debug(e);
}
}
/**
* 获取sheet 标题部分
* @param sheetNumber
* @param titleRow
* @return
*/
public List<String> getTitleFromSheet(int sheetNumber, int titleRow){
// 获得指定的sheet
HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
return getTitleFromSheet(sheet,titleRow);
}
/**
* 根据sheet获取sheet的标题部分
* @param sheet
* @param titleRow
* @return
*/
public List<String> getTitleFromSheet(HSSFSheet sheet, int titleRow){
List<String> titles = new ArrayList<String>();
// 获得指定的sheet
int rowCount = sheet.getLastRowNum();
if (rowCount < titleRow ) {
return null;
}
//获取一行
HSSFRow row = sheet.getRow(titleRow);
if (null != row) {
int cellCount = row.getLastCellNum();
// 遍历列cell
for (int cellIndex = 0; cellIndex < cellCount; cellIndex++) {
HSSFCell cell = row.getCell(cellIndex);
// 获得指定单元格中的数据
Object cellStr = this.getCellString(cell);
if(cellStr!= null){
titles.add(cellStr.toString());
}
}
}
return titles;
}
/**
* 获取第sheetNumber个sheet的数据，从rowIndex 开始
* @param sheetNumber
* @param rowStart
* @return
*/
public List<List<Object>> getDatasInSheet(int sheetNumber, int rowStart) {
List<List<Object>> result = new ArrayList<List<Object>>();
// 获得指定的sheet
HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
// 获得sheet总行数
int rowCount = sheet.getLastRowNum();
logger.info("found excel rows count:" + rowCount);
if (rowCount < 1) {
return result;
}
if(rowStart<0){
rowStart =0;
}
// 遍历行row
for ( ; rowStart <= rowCount; rowStart++) {
// 获得行对象
HSSFRow row = sheet.getRow(rowStart);
if (null != row) {
List<Object> rowData = new ArrayList<Object>();
// 获得本行中单元格的个数
int cellCount = row.getLastCellNum();
int flag = 0; //记录为空的数量
// 遍历列cell
for (int cellIndex = 0; cellIndex < cellCount; cellIndex++) {
HSSFCell cell = row.getCell(cellIndex);
// 获得指定单元格中的数据
Object cellStr = this.getCellString(cell);
if(cellStr==null){
flag++ ;
}
rowData.add(cellStr);
}
if(flag != cellCount){ //如果数据不全部为空则添加到列表中
result.add(rowData);
}
}
}
return result;
}
/**
* 获取第sheetNumber个sheet的数据，从rowIndex 开始
* @param sheetNumber
* @param rowStart
* @return
*/
public List<Map<String, String>> getDataInSheet(int sheetNumber, int rowStart){
List<Map<String, String>> result = new ArrayList<Map<String, String>>();
// 获得指定的sheet
HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
// 获得sheet总行数
int rowCount = sheet.getLastRowNum();
logger.info("found excel rows count:" + rowCount);
if (rowCount < 1) {
return result;
}
if(rowStart<0){
rowStart =0;
}
//获取列头
List<String> titleList = this.getTitleFromSheet(0, 0);
// 遍历行row
for ( ; rowStart <= rowCount; rowStart++) {
// 获得行对象
HSSFRow row = sheet.getRow(rowStart);
if (null != row) {
Map<String, String> rowData = new HashMap<String, String>();
// 获得本行中单元格的个数
int cellCount = titleList.size();
int flag = 0; //记录为空的数量
// 遍历列cell
for (int cellIndex = 0; cellIndex < cellCount; cellIndex++) {
HSSFCell cell = row.getCell(cellIndex);
// 获得指定单元格中的数据
Object cellStr = this.getCellString(cell);
if(cellStr==null){
flag++ ;
}
rowData.put(titleList.get(cellIndex),cellStr!=null?cellStr.toString().trim():"");
}
if(flag != cellCount){ //如果数据不全部为空则添加到列表中
result.add(rowData);
}
}
}
return result;
}
/**
* 获取单元格的数据
* @param cell
* @return
*/
private Object getCellString(HSSFCell cell) {
Object result = null;
if (cell != null) {
// 单元格类型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
int cellType = cell.getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
result = cell.getRichStringCellValue().getString();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellDateFormatted(cell)){ //判断是否为日期格式
result = DateUtils.getDate(cell.getDateCellValue());
}else{
DecimalFormat df = new DecimalFormat("0");
result = df.format(cell.getNumericCellValue());
}
// result = cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA:
result = cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = null;
break;
case HSSFCell.CELL_TYPE_ERROR:
result = null;
break;
default:
System.out.println("枚举了所有类型");
break;
}
}
return result;
}
/**
* 根据value 生成workBook
* @param sheetName
* @param value
* @return
* @throws IOException
*/
public static SXSSFWorkbook createWorkBook(String sheetName, Map<String, String> excelTitle, List<Map<String,Object>> value) throws IOException {
SXSSFWorkbook wb = new SXSSFWorkbook();
// 创建一个SHEET
Sheet sheet1 = wb.createSheet(sheetName);
if(value== null || value.isEmpty()){
return wb;
}
Object[] title = excelTitle.keySet().toArray();
// String[] title = { "编号", "产品名称", "产品价格", "产品数量", "生产日期", "产地", "是否出口" };
int i = 0;
// 创建一行
Row row = sheet1.createRow(0);
/* // 生成一个样式
CellStyle style = wb.createCellStyle();
// 设置这些样式
style.setAlignment(SXSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
Font font = wb.createFont();
font.setBoldweight(SXSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font); */
// 填充标题
for (Object s : title) {
Cell cell = row.createCell(i);
cell.setCellType(SXSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(getTitleStyle(wb));
cell.setCellValue(excelTitle.get(s));
sheet1.setColumnWidth(i, 20*256);
i++;
}
int indexRow = 1; //行号
int indexCell = 0; //列
for(Map<String,Object> map: value){
Row row1 = sheet1.createRow(indexRow);
indexRow ++;
indexCell = 0;
for(Object cellTitle : title){
Cell cell = row1.createCell(indexCell);
Object obj = map.get(cellTitle.toString());
String cellValue = "";
if(obj!=null){
cellValue = obj.toString();
}
cell.setCellValue(cellValue);
indexCell++;
}
if(indexRow % 1000 == 0) {
((SXSSFSheet)sheet1).flushRows(1000); // retain 100 last rows and flush all others
// ((SXSSFSheet)sh).flushRows() is a shortcut for ((SXSSFSheet)sh).flushRows(0),
// this method flushes all rows
}
}
return wb;
}
public static SXSSFWorkbook appendWorkBook(SXSSFWorkbook wb, String sheetName, Map<String,String> excelTitle, List<Map<String,Object>> value) throws IOException {
Sheet sheet = wb.getSheet(sheetName);
if(value== null || value.isEmpty()){
return wb;
}
Object[] title = excelTitle.keySet().toArray();
int indexRow = sheet.getLastRowNum()+1; //行号
int indexCell = 0; //列
for(Map<String,Object> map: value){
Row row1 = sheet.createRow(indexRow);
indexRow ++;
indexCell = 0;
for(Object cellTitle : title){
Cell cell = row1.createCell(indexCell);
Object obj = map.get(cellTitle.toString());
String cellValue = "";
if(obj!=null){
cellValue = obj.toString();
}
cell.setCellValue(cellValue);
indexCell++;
}
if(indexRow % 1000 == 0) {
((SXSSFSheet)sheet).flushRows(1000); // retain 100 last rows and flush all others
// ((SXSSFSheet)sh).flushRows() is a shortcut for ((SXSSFSheet)sh).flushRows(0),
// this method flushes all rows
}
}
return wb;
}
/**
* exce表头单元格样式处理
* @param workbook
* @return
*/
public static CellStyle getTitleStyle(Workbook workbook) {
// 产生Excel表头
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置边框样式
titleStyle.setBorderLeft((short) 1); // 左边框
titleStyle.setBorderRight((short) 1); // 右边框
titleStyle.setBorderTop((short) 1); // 左边框
titleStyle.setBorderBottom((short) 1); // 右边框
titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 顶边框
titleStyle.setFillForegroundColor(HSSFColor.YELLOW.index); // 填充的背景颜色
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案
return titleStyle;
}
/**
* 根据workbook生成excel 文件
* @param wb
* @param path ["d:\test.xls"]
*/
public void createExcel(HSSFWorkbook wb,String path){
FileOutputStream fileOut = null;
try {
fileOut = new FileOutputStream(path);
wb.write(fileOut);
} catch (FileNotFoundException e) {
log.debug(e);
} catch (IOException e) {
log.debug(e);
}
finally{
try {
fileOut.close();
} catch (IOException e) {
log.debug(e.getMessage());
}
}
}
/**
* 为单元格创建一个样式
* @param wb
* @param style ("#.##")
*/
public CellStyle setCellStyle(Workbook wb,String style){
if(wb == null){
return null;
}
CellStyle cellStyle = wb.createCellStyle();
DataFormat format = wb.createDataFormat();
cellStyle.setDataFormat(format.getFormat(style));
// cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
// 设定样式
// cell.setCellStyle(cellStyle);
// HSSFCellStyle cellStyle = wb.createCellStyle();
// cellStyle.setFont(font);
// cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平布局：居中
// cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
//// cellStyle.setFillForegroundColor(CellStyle..LIGHT_YELLOW.index);
// cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
// cellStyle.setBorderTop(CellStyle.BORDER_THIN);
// cellStyle.setBorderRight(CellStyle.BORDER_THIN);
// cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
// cellStyle.setBottomBorderColor(CellStyle.DARK_RED.index);
return cellStyle;
}
// test
public static void main(String[] args) {
File file = new File("d:\abc.xls");
ExcelUtils parser = new ExcelUtils(file);
List<List<Object>> datas = parser.getDatasInSheet(0,0);
for (int i = 0; i < datas.size(); i++) {
List<Object> row = datas.get(i);
for (short j = 0; j < row.size(); j++) {
Object value = row.get(j);
String data = String.valueOf(value);
System.out.println(data + " ");
}
System.out.println();
}
}
/**
* 描述：根据文件路径获取项目中的文件
* @param fileDir 文件路径
* @return
* @throws Exception
*/
public File getExcelDemoFile(String fileDir) throws Exception{
String classDir = null;
String fileBaseDir = null;
File file = null;
classDir = Thread.currentThread().getContextClassLoader().getResource("/").getPath();
fileBaseDir = classDir.substring(0, classDir.lastIndexOf("classes"));
//file = new File(fileBaseDir+fileDir);
file = new File("D:/hcm/hcm/src/main/webapp/WEB-INF/excel/项目信息表.xlsx");
if(!file.exists()){
throw new Exception("模板文件不存在！");
}
return file;
}
public Workbook writeNewExcel(File file,String sheetName,List<HCM_project_informData> lis) throws Exception{
Workbook wbdd = null;
//HSSFWorkbook wb = null;
Sheet sheet;
int lastRow;
Row row = null;
Cell cell = null;
CellStyle cs;
int a ;
FileInputStream fis = new FileInputStream(file);
//判断文件后缀名是xls，还是xlsx
String path = file.getName();
String type = path.substring(path.lastIndexOf(".")+1);
//如果是xls，使用HSSFWorkbook；如果是xlsx，使用XSSFWorkbook
if(type.equals("xls")||type=="xls"){
HSSFWorkbook wb = new HSSFWorkbook(fis);
sheet = wb.getSheet(sheetName);
lastRow = sheet.getLastRowNum()+1; //插入数据的数据ROW
cs = setSimpleCellStyle(wb); //Excel单元格样式
a = 0;
wbdd = wb;
}else{
XSSFWorkbook wb = new XSSFWorkbook(fis);
sheet = wb.getSheet(sheetName);
lastRow = sheet.getLastRowNum()+1; //插入数据的数据ROW
cs = setSimpleCellStyle(wb); //Excel单元格样式
a = 1;
wbdd = wb;
}
//wb = new ImportExcelUtil().getWorkbook(fis, file.getName()); //获取工作薄
//循环插入数据
//int lastRow = sheet.getLastRowNum()+1; //插入数据的数据ROW
//CellStyle cs = setSimpleCellStyle(wb); //Excel单元格样式
for (int i = 0; i < lis.size(); i++) {
row = sheet.createRow(lastRow+i); //创建新的ROW，用于数据插入
//按项目实际需求，在该处将对象数据插入到Excel中
HCM_project_informData vo = lis.get(i);
if(null==vo){
break;
}
//Cell赋值开始
cell = row.createCell(0);
cell.setCellValue(vo.getProject_inform());
cell.setCellStyle(cs);
cell = row.createCell(1);
cell.setCellValue(vo.getProject_type());
cell.setCellStyle(cs);
cell = row.createCell(2);
cell.setCellValue(vo.getFuze_id());
cell.setCellStyle(cs);
cell = row.createCell(3);
cell.setCellValue(vo.getUpdate_man());
cell.setCellStyle(cs);
cell = row.createCell(4);
cell.setCellValue(vo.getFramework());
cell.setCellStyle(cs);
cell = row.createCell(5);
cell.setCellValue(vo.getTwo_id());
cell.setCellStyle(cs);
cell = row.createCell(6);
cell.setCellValue(vo.getThree_id());
cell.setCellStyle(cs);
}
return wbdd;
}
/**
* 描述：设置简单的Cell样式
* @return
*/
public CellStyle setSimpleCellStyle(Workbook wb){
CellStyle cs = wb.createCellStyle();
cs.setBorderBottom(CellStyle.BORDER_THIN); //下边框
cs.setBorderLeft(CellStyle.BORDER_THIN);//左边框
cs.setBorderTop(CellStyle.BORDER_THIN);//上边框
cs.setBorderRight(CellStyle.BORDER_THIN);//右边框
cs.setAlignment(CellStyle.ALIGN_CENTER); // 居中
return cs;
}
}
=========================
改改逻辑就可以直接用，用不动的直接问我，wx：18621617758 通过备注：博客园
祝你顺利