Excel父子数据导入
public class PoiUtil
{
/**
* 读取excel数据
* @param path
*/
public static ArrayList<Map<String,String>> readExcelToObj(File file) {
Workbook wb = null;
ArrayList<Map<String,String>> result = null;
try {
wb = WorkbookFactory.create(file);
result = readExcel(wb, 0, 3, 0);
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return result;
}
/**
* 读取excel文件
* @param wb
* @param sheetIndex sheet页下标：从0开始
* @param startReadLine 开始读取的行:从0开始
* @param tailLine 去除最后读取的行
*/
private static ArrayList<Map<String,String>> readExcel(Workbook wb,int sheetIndex, int startReadLine, int tailLine) {
Sheet sheet = wb.getSheetAt(sheetIndex);
Row row = null;
ArrayList<Map<String,String>> result = new ArrayList<Map<String,String>>();
for(int i=startReadLine; i<sheet.getLastRowNum()-tailLine+1; i++) {
row = sheet.getRow(i);
Map<String,String> map = new HashMap<String,String>();
for(Cell c : row) {
String returnStr = "";
boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
//判断是否具有合并单元格
if(isMerge) {
String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
returnStr = rs;
}else {
returnStr = getCellValue(c);
}
if(c.getColumnIndex()==0){
map.put("lv1",returnStr);//考核模板名称
}else if(c.getColumnIndex()==1){
map.put("lv2",returnStr); //考核模板名称下的考核指标名称
}else if(c.getColumnIndex()==2){
map.put("lv3",returnStr); //指标分数
}else if(c.getColumnIndex()==3){
map.put("lv4",returnStr); //详细指标
}else if(c.getColumnIndex()==4){
map.put("lv5",returnStr); //自评
}else if(c.getColumnIndex()==5){
map.put("lv6",returnStr); //部门经理评分（50%）
}else if(c.getColumnIndex()==6){
map.put("lv7",returnStr); //考核小组评分（30%）
}
}
result.add(map);
// System.out.println();
}
return result;
}
public static String getMergedRegionValue(Sheet sheet, int row, int column)
{
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; ++i) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if ((row >= firstRow) && (row <= lastRow) &&
(column == firstColumn)) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell);
}
}
return null;
}
private static boolean isMergedRow(Sheet sheet, int row, int column)
{
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; ++i) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if ((row == firstRow) && (row == lastRow) &&
(column >= firstColumn) && (column <= lastColumn))
return true;
}
return false;
}
private static boolean isMergedRegion(Sheet sheet, int row, int column)
{
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; ++i) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if ((row >= firstRow) && (row <= lastRow) &&
(column >= firstColumn) && (column <= lastColumn))
return true;
}
return false;
}
private static boolean hasMerged(Sheet sheet)
{
return (sheet.getNumMergedRegions() > 0);
}
private static void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol)
{
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
public static String getCellValue(Cell cell)
{
if (cell == null) return "";
if (cell.getCellType() == 1)
{
return cell.getStringCellValue();
}
if (cell.getCellType() == 4)
{
return String.valueOf(cell.getBooleanCellValue());
}
if (cell.getCellType() == 2)
{
return cell.getCellFormula();
}
if (cell.getCellType() == 0)
{
String value = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
DecimalFormat df = new DecimalFormat("0");
value = df.format(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue().trim();
break;
default:
value = "";
break;
}
return value.trim();
}
return "";
}
/**
* 从excel读取模板名称
*/
public static String atemplateName(String fileName) {
String atemplateName="";
boolean isE2007 = false; //判断是否是excel2007格式
if(fileName.endsWith("xlsx"))
isE2007 = true;
try {
InputStream input = new FileInputStream(fileName); //建立输入流
Workbook wb = null;
//根据文件格式(2003或者2007)来初始化
if(isE2007)
wb = new XSSFWorkbook(input);
else
wb = new HSSFWorkbook(input);
Sheet sheet = wb.getSheetAt(0); //获得第一个表单
Row row = sheet.getRow(0);
Cell cells = row.getCell(0);
atemplateName = cells.getStringCellValue();
} catch (IOException ex) {
ex.printStackTrace();
}
return atemplateName;
}
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:mybatis-config.xml",
"classpath:applicationContext.xml", "classpath:springmvc-config.xml" })
public class VB {
@Resource
private Assess_indexDAO assessindexDAO;
@Resource
private Assess_processDAO assessprocessDao;
@Resource
private Assess_recordDAO assessrecorDao;
@Resource
private Assess_templateDAO assesstemplateDAO;
@Test
public void show() {
// 导入之前根据该用户的角色找到改模板删除模板表和考核指标表数据
Integer assess_indexFbh = null;
File file = new File(
"C:\\Users\\Administrator\\Desktop\\OA半天更新下代码\\2018年考核指标--项目经理.xlsx");
ArrayList<Map<String, String>> readExcelToObj = PoiUtil
.readExcelToObj(file);
Assess_template template = new Assess_template();
template.setTemplate_name(PoiUtil
.atemplateName("C:\\Users\\Administrator\\Desktop\\OA半天更新下代码\\2018年考核指标--项目经理.xlsx"));
// 重新导入的话先全部删除
Assess_template atemplate = new Assess_template();
atemplate.setCreate_time(new Date());
atemplate.setTemplate_status(new BigDecimal(0));
atemplate.setTemplate_code("001");
atemplate
.setTemplate_name(PoiUtil
.atemplateName("C:\\Users\\Administrator\\Desktop\\OA半天更新下代码\\2018年考核指标--项目经理.xlsx"));// 考核指标名称生成考核模板数据
// 考核指标 考核指标 分数 详细指标 自评（20%） 部门经理评分（50%） 考核小组评分（30%）
assesstemplateDAO.saveAssess_template(atemplate);
// 导入的时候
for (Map<String, String> item : readExcelToObj) {
String lv1 = item.get("lv1");
String lv2 = item.get("lv2");
String lv3 = item.get("lv3");
String lv4 = item.get("lv4");
String lv5 = item.get("lv5");
String lv6 = item.get("lv6");
String lv7 = item.get("lv7");
if (lv1 != null && !"".equals(lv1)) {
Assess_index assess_index = new Assess_index();
assess_index.setIndex_name(lv1);
if (this.assessindexDAO.getAssess_index(assess_index) == null) {
Assess_index assessindex = new Assess_index();
assessindex.setCreate_time(new Date());
assessindex.setIndex_status(new BigDecimal(0));
assessindex.setTemplate_code(atemplate.getBh());
assessindex.setIndex_name(lv1);
this.assessindexDAO.saveAssess_index(assessindex);
assess_indexFbh = assessindex.getBh();
}
}
Assess_index assessindex = new Assess_index();
assessindex.setCreate_time(new Date());
assessindex.setIndex_status(new BigDecimal(0));
assessindex.setTemplate_code(atemplate.getBh());
assessindex.setParent_index(assess_indexFbh.toString());
if (lv2 != null && !"".equals(lv2)) {
assessindex.setIndex_name(lv2);
}
if (lv3 != null && !"".equals(lv3)) {
assessindex.setIndex_score(Integer.parseInt(lv3));
}
if (lv4 != null && !"".equals(lv4)) {
assessindex.setIndex_desc(lv4);
}
// 考核指标表生成
this.assessindexDAO.saveAssess_index(assessindex);
}
}
}
模板在文件中下载
jar包
docx4j-3.3.0.jar
dom4j-1.6.1.jar
poi-3.11-20141221.jar
poi-ooxml-3.8-20120326.jar
poi-ooxml-schemas-3.7-20101029.jar
poi-scratchpad-3.11-20141221.jar
