java后台poi方式导出excel和解析excel

解析excel并返回来list

package com.hx.gpsplatform.common.utils;


import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;


import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelUtils {
private static final String XLS_TYPE = ".XLS";
private static final String XLSX_TYPE = ".XLSX";


/**
* 
* @Title: getCellStyle
* @Description: TODO（设置表头样式）
* @param wb
* @return
*/
public static CellStyle getCellStyle(Workbook wb) {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);// 设置字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
style.setFillForegroundColor(HSSFColor.LIME.index);// 设置背景色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.SOLID_FOREGROUND);// 让单元格居中
// style.setWrapText(true);//设置自动换行
style.setFont(font);
return style;
}


// 获取wokrbook
private static Workbook getWeebWork(InputStream inputtream, String name) {
Workbook workbook = null;
if (null != name) {
String fileType = name
.substring(name.lastIndexOf("."), name.length()).trim()
.toUpperCase();
if (ExcelUtils.XLS_TYPE.equals(fileType)) {
// 创建 Excel 2003 工作簿对象
try {
workbook = new HSSFWorkbook(inputtream);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} else if (ExcelUtils.XLSX_TYPE.equals(fileType.trim())) {
// 创建 Excel 2007 工作簿对象
try {
workbook = new XSSFWorkbook(inputtream);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return workbook;
}


// 获取header列
private static List<String> getHeader(Workbook workbook,String[] headerName) {
ArrayList<String> headerList = new ArrayList<String>();
// 获取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
Row header = sheet.getRow(0);
for (int j = header.getFirstCellNum(); j < header.getLastCellNum(); j++) {
headerList.add(headerName[j]);
// headerList.add(header.getCell(j).toString());
}
return headerList;
}

// 根据类型以不同的方式获取value
private static String getCellFormatValue(Cell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case Cell.CELL_TYPE_NUMERIC:
case Cell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则，取得该Cell的Date值
Date date = cell.getDateCellValue();
// 把Date转换成本地格式的字符串
cellvalue = cell.getDateCellValue().toLocaleString();
} else {
// 如果是纯数字
// 取得当前Cell的数值
DecimalFormat df = new DecimalFormat("0");
double num = new Double((double) cell.getNumericCellValue());
cellvalue = String.valueOf(df.format(num));
}
break;
}
// 如果当前Cell的Type为STRIN
case Cell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getStringCellValue().replaceAll("'", "''");
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}


// 读取excel,只适合 有规律的
public static List read(InputStream inputStream, String fileName,String[] headerName) {
LinkedList result = new LinkedList();
Workbook workbook = ExcelUtils.getWeebWork(inputStream, fileName);
// 获取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
// 获取header个数
List<String> headerList = ExcelUtils.getHeader(workbook,headerName);


for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
HashMap rowMap = new HashMap();
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
rowMap.put(headerList.get(j), getCellFormatValue(row.getCell(j)));
}
result.add(rowMap);
}
return result;
}
}

批量导入excel调用例子：

/**
* 批量导入入口
* 
* @throws IOException
*/
@RequestMapping("/batchInput")
public @ResponseBody String batchInput(@RequestParam("file") CommonsMultipartFile commonsMultipartFile,String sessionPart) {
String result = "";
// 判断文件是否为空
if(null == commonsMultipartFile.getOriginalFilename() && "".equals(commonsMultipartFile.getOriginalFilename())){
return "文件为空";
}else{
try {
List excelData = null;
// 把EXCEL文件转换为list
if("1".equals(sessionPart)){
if(commonsMultipartFile.getOriginalFilename().indexOf("IMEI")==-1){
return "999";
}else{
excelData = ExcelUtils.read(commonsMultipartFile.getInputStream(),commonsMultipartFile.getOriginalFilename(),ExportExcelUtils.TITLE_IMEI_TO_ENGLISH);
List<ImeiStockDTO> imeilist = HXBeanUtils.listMap2ListObj(excelData, ImeiStockDTO.class);
ImeiStockDTO imeidto = new ImeiStockDTO();
for(int i=0;i<imeilist.size();i++){
imeidto = imeilist.get(i);
if("".equals(cardStockService.saveImei(imeidto))){
result += imeidto.getImeiId() + " 、 ";
}
}
}
}else if("2".equals(sessionPart)){
if(commonsMultipartFile.getOriginalFilename().indexOf("SIM")==-1){
return "888";
}else{
excelData = ExcelUtils.read(commonsMultipartFile.getInputStream(),commonsMultipartFile.getOriginalFilename(),ExportExcelUtils.TITLE_SIM_TO_ENGLISH);
List<SimStockDTO> simlist = HXBeanUtils.listMap2ListObj(excelData, SimStockDTO.class);
SimStockDTO simdto = new SimStockDTO();
for(int i=0;i<simlist.size();i++){
simdto = simlist.get(i);
if("".equals(cardStockService.saveSIM(simdto))){
result += simdto.getSimId() + " 、 ";
}
}
}
}
} catch(Exception e){
e.printStackTrace();
}
}
return result;

后台导出excel：

package com.hx.gpsplatform.common.utils;


import java.util.HashMap;
import java.util.List;
import java.util.Map;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


public class ExportExcelUtils {
// 翻译的表头容器
private static Map<String,String[]> headMap = new HashMap<String,String[]>();
// value 名称容器
private static Map<String,String[]> valueMap = new HashMap<String,String[]>();
//
private static String[] getHeaderMap(String excelType){
if(headMap == null){
headMap = new HashMap<String,String[]>();
}
if(headMap.size() == 0){
headMap.put(ExportExcelUtils.TITLE_STOCK_IMEI, new String[]{"IMEI","设备型号","所属分组","在线状态","最后上传时间","库存状态","激活时间","在库时间","在库时长(天)"});
headMap.put(ExportExcelUtils.TITLE_STOCK_SIM, new String[]{"客户名","车牌","ICCID","SIM卡号码","所属分组","SIM卡供应商","SIM卡状态","流量","库存状态","激活时间","在库时间","在库时长(天)"});
headMap.put(ExportExcelUtils.TITLE_SIM, new String[]{ "SIM ID", "是否有效","ICCID", "用户ID(appid)","供应商ID"});
headMap.put(ExportExcelUtils.TITLE_IMEI, new String[]{ "IMEI ID", "供应商id", "类型", "型号", "描述", "是否有效", "库存状态","安装日期","服务日期"});
headMap.put(ExportExcelUtils.TITLE_RELEVANCE, new String[]{ "客户名", "车牌", "所属用户", "IMEI", "设备型号", "激活日期", "SIM卡号", "流量", "卡激活时间", "在线状态", "贷款时间", "贷款周期" });
headMap.put(ExportExcelUtils.TITLE_ONLINE, new String[]{ "车主姓名", "车牌号", "所属用户", "IMEI", "设备类型", "定位方式", "车辆状态", "速度", "定位时间", "接收状态", "在线时长", "当前位置","电量" });
headMap.put(ExportExcelUtils.TITLE_OFFLINE, new String[]{ "车主姓名", "车牌号", "所属用户", "IMEI", "设备类型", "定位方式", "车辆状态", "速度", "定位时间", "接收状态", "离线时长", "离线位置","电量" });
headMap.put(ExportExcelUtils.TITLE_TRAVEL, new String[]{ "车主姓名", "车牌号", "所属用户", "IMEI", "设备类型", "定位方式", "车辆状态", "速度", "定位时间", "接收状态", "行驶时长", "当前位置","电量" });
headMap.put(ExportExcelUtils.TITLE_FLAMOUT, new String[]{ "车主姓名", "车牌号", "所属用户", "IMEI", "设备类型","熄火时间", "熄火时长", "当前位置","在线状态" });
headMap.put(ExportExcelUtils.TITLE_POWER, new String[]{ "车主姓名", "车牌号", "所属用户", "IMEI", "设备类型","报警类型", "报警次数","当前状态","当前统计时间" });
headMap.put(ExportExcelUtils.TITLE_ALARMDETAIL, new String[]{ "车主姓名", "车牌号", "所属用户", "IMEI", "设备类型","报警类型", "报警时间","处理情况"});
headMap.put(ExportExcelUtils.TITLE_MILEAGEDAYORMON, new String[]{ "车牌", "日期","里程"});
headMap.put(ExportExcelUtils.TITLE_IMPORTAREA, new String[]{ "客户姓名", "车牌号","所属用户","IMEI","设备类型","围栏名称","出去时间","出区地点","返回时间","返回地点","累计时间"});
headMap.put(ExportExcelUtils.TITLE_TWOEXPENSE, new String[]{ "客户姓名", "车牌号","所属用户","IMEI","设备类型","二押点名称","报警类型","报警时间","处警情况","报警地点","定位方式"});
}
return headMap.get(excelType);
}
// 获取值容器
private static String[] getValueMap(String excelType){
if(valueMap == null){
valueMap = new HashMap<String,String[]>();
}
if(valueMap.size() == 0){
valueMap.put(ExportExcelUtils.TITLE_STOCK_IMEI, new String[]{"imeiId","versionType","organizeDesc","onlineStatus","uploadDate","stockStatus","activateDate","instockDate","instockTime"});
valueMap.put(ExportExcelUtils.TITLE_STOCK_SIM, new String[]{"custName","carNumber","ICCID","simId","organizeDesc","supplierName","simStatus","flow","simStockStatus","activateDate","simInstockDate","instockTime"});
valueMap.put(ExportExcelUtils.TITLE_RELEVANCE, new String[]{ "custName", "carNumber", "createdBy", "imeiId", "iType", "iActivateDate", "simId", "flow", "sActivateDate", "onlineStatus", "loanDate", "contractTerm" });
valueMap.put(ExportExcelUtils.TITLE_ONLINE, new String[]{ "custName", "carNumber","organize", "imei", "terminalType", "locationMode", "currentStatus","speed", "locationTime", "recepitTime", "totalTime","currentLocation" ,"electric"});
valueMap.put(ExportExcelUtils.TITLE_OFFLINE, new String[]{ "custName", "carNumber","organize", "imei", "terminalType", "locationMode", "currentStatus","speed", "locationTime", "recepitTime", "totalTime","currentLocation" ,"electric"});
valueMap.put(ExportExcelUtils.TITLE_TRAVEL, new String[]{ "custName", "carNumber","organize", "imei", "terminalType", "locationMode", "currentStatus","speed", "locationTime", "recepitTime", "totalTime","currentLocation" ,"electric"});
valueMap.put(ExportExcelUtils.TITLE_FLAMOUT, new String[]{ "custName", "carNumber","organize", "imei", "terminalType", "createdTime", "totalTime","currentLocation" ,"onlineState"});
valueMap.put(ExportExcelUtils.TITLE_POWER, new String[]{ "custName", "carNumber","organizeDesc", "imei", "terminalType", "alarmType","alarmNum","online" ,"alarmTime"});
valueMap.put(ExportExcelUtils.TITLE_ALARMDETAIL, new String[]{ "custName", "carNumber","organizeDesc", "imei", "terminalType", "alarmType","warningTime","alarmProcesse"});
valueMap.put(ExportExcelUtils.TITLE_MILEAGEDAYORMON, new String[]{ "licensePlate", "countDaytime","mileage"});
valueMap.put(ExportExcelUtils.TITLE_IMPORTAREA, new String[]{ "custName", "carNumber","organizeDesc","imei","terminalType","fenceName","exitTime","exitLocation","returnTime","returnLocation","cumulativeTime"});
valueMap.put(ExportExcelUtils.TITLE_TWOEXPENSE, new String[]{ "custName", "carNumber","organizeDesc","imei","terminalType","fenceName","alarmType","alarmTime","alarmProcesse","alarmLocation","locationType"});
}
return valueMap.get(excelType);
}

// IMEI的HEAD(中文转英文)
public static final String[] TITLE_IMEI_TO_ENGLISH =  new String[]{ "imeiId", "supplierId", "iType", "versionType", "description", "isEffective", "stockStatus","installDate","serviceDate"};
// SIM的HEAD(中文转英文)
public static final String[] TITLE_SIM_TO_ENGLISH = new String[]{ "simId", "simIsEffective","ICCID", "appid","simSupplierId"};

// IMEI的HEAD
public static final String TITLE_STOCK_IMEI = "TITLE_STOCK_IMEI";
// IMEI的HEAD
public static final String TITLE_STOCK_SIM = "TITLE_STOCK_SIM";
// SIM的HEAD
public static final String TITLE_SIM = "TITLE_SIM";
// IMEI的HEAD
public static final String TITLE_IMEI = "TITLE_IMEI";
// Relevance
public static final String TITLE_RELEVANCE = "TITLE_RELEVANCE";
//车辆在线报表
public static final String TITLE_ONLINE = "TITLE_ONLINE";
//车辆离线报表
    public static final String TITLE_OFFLINE = "TITLE_OFFLINE";
    //车辆行驶报表
    public static final String TITLE_TRAVEL = "TITLE_TRAVEL";
    //车辆行驶报表
    public static final String TITLE_FLAMOUT = "TITLE_FLAMOUT";
    //车辆断电报表
    public static final String TITLE_POWER = "TITLE_POWER";
    //车辆断电报表
    public static final String TITLE_ALARMDETAIL = "TITLE_ALARMDETAIL";
    //里程报表按日或按月统计
    public static final String TITLE_MILEAGEDAYORMON = "TITLE_MILEAGEDAYORMON";
    //里程报表按日或按月统计
    public static final String TITLE_IMPORTAREA = "TITLE_IMPORTAREA";
    //二押点报表统计
    public static final String TITLE_TWOEXPENSE = "TITLE_TWOEXPENSE";
@SuppressWarnings("rawtypes")
public static HSSFWorkbook getHSSFWorkbook(String excelType, List<Map> values) {
// 创建一个webbook和sheet，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("EXPORT_DATA");
// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
// 创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = null;
// 创建header
String[] headerMap = ExportExcelUtils.getHeaderMap(excelType);
for (int i = 0; i < headerMap.length; i++) {
cell = row.createCell(i);
cell.setCellValue(headerMap[i]);
cell.setCellStyle(style);
}
// 创建内容
String[] valueMap = ExportExcelUtils.getValueMap(excelType);
if(values!=null){
for (int i = 0; i < values.size(); i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < valueMap.length; j++) {
if (values.get(i).get(valueMap[j]) != null) {
row.createCell(j).setCellValue(values.get(i).get(valueMap[j]) + "");
} else {
row.createCell(j).setCellValue("");
}
}
}
}
return wb;
}


}

调用例子响应消息

@RequestMapping("/exportTwoExpenseListAll")
public String exportTwoExpenseListAll(Model model,Pageable pageable,VehicleAlarmDto vDto,HttpServletRequest request, HttpServletResponse response){
HSSFWorkbook wb = ExportExcelUtils.getHSSFWorkbook(ExportExcelUtils.TITLE_TWOEXPENSE, reportService.findTwoExpenseListAll(vDto));
// 调用公共方法直接返回文件流
GPSResponseUtils.setResponseHeader(wb, "里程统计报表_"+System.currentTimeMillis()+".xls", response);
        return "";
}

package com.hx.gpsplatform.common.utils;


import java.io.OutputStream;


import javax.servlet.http.HttpServletResponse;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;


/**
 * GPS Response工具
 * 
 * @author win7 创建时间 2017-03-29 18:11:52
 */
public class GPSResponseUtils {
/**
* 设置excel header
* 
* @param response
* @param fileName
*/
public static void setResponseHeader(HSSFWorkbook wb, String fileName,HttpServletResponse response) {
try {
fileName = new String(fileName.getBytes(), "UTF-8");
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="
+ fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}