package servlet;
import java.util.HashMap;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
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.ss.util.NumberToTextConverter;
public class ImportUtils {
private static Map<Integer,String> templatePositionColomn = new HashMap<Integer,String>();
private static Map<String,Integer> columnPosition = new HashMap<String,Integer>();
private static String[] templateFields = new String[]{"A","B","C"};
private static final int A = 0;
private static final int B = 1;
private static final int C = 2;
/**
* 根据预设字段所在位置,获取Excel中字段实际位置
* @param position
* @return
*/
protected static int getNewPosition(int position){
if(templatePositionColomn.size() > 0){
return position;
}
int retval = 0;
try{
if(!columnPosition.containsKey(templatePositionColomn.get(position))){
return -1;
}
retval = columnPosition.get(templatePositionColomn.get(position));
if(position != retval){
return retval;
}
}catch(Exception e){
return position;
}
return retval;
}
/**
* 读取Excel中表头内容,并将表头中的列值与所在位置做映射,并存放到Map中
* @param sheet
*/
protected void populateColumnPosition(HSSFSheet sheet){
HSSFRow hssfRow = sheet.getRow(0);
HSSFCell cell = null;
for (int i = 0; i < hssfRow.getPhysicalNumberOfCells(); i++) {
cell = hssfRow.getCell(i);
if(cell != null){
String value = cell.getStringCellValue();
if(!StringUtils.isEmpty(value)){
columnPosition.put(value.trim().toUpperCase(), i);
}
}
}
}
/**
* 读取预设的字段信息,并将其对应位置存放到Map中
* @param templateFields
*/
protected void populateTemplateColumnPosition(String[] templateFields){
for (int i = 0; i < templateFields.length; i++) {
templatePositionColomn.put(i, templateFields[i].trim().toUpperCase());
}
}
/**
* 根据预设字段所在位置
* @param hssfRow
* @param index
* @return
*/
protected String getCellValue(HSSFRow hssfRow,int index){
index = getNewPosition(index);
if(index == -1){
return "";
}
String value = "";
if(null != hssfRow && index < templateFields.length){
HSSFCell cell = hssfRow.getCell(index);
if(null != cell){
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
value = NumberToTextConverter.toText(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
default:
value="";
break;
}
}
// value = StringUtils.TrimOrNull(value);
// value = StringUtils.removeChar(value,'\n');
// value = StringUtils.removeChar(value,'\r');
return value;
}
return value;
}
}
excel导入时可以随意调整字段位置
最新推荐文章于 2022-12-16 10:24:08 发布