解析工具类：
public class ParseExcelUtil {
/**
* 解析Excel表格
* @param file 用户上传的Excel表格文件
* @return 表格内容集合
* @author xWang
* @Date 2020-05-09
*/
public static List<List<Map<Integer,Object>>> parse(MultipartFile file) throws IOException{
InputStream fileInputStream = file.getInputStream();
String fileType = ToolUtil.getFileSuffix(file.getOriginalFilename());
//Excel表格数据集合
List<List<Map<Integer,Object>>> lists = new ArrayList<List<Map<Integer,Object>>>();
Iterator<Sheet> sheets = null;
try {
//获取工作薄
Workbook wb = null;
if (fileType.equals("xls")) {
wb = new HSSFWorkbook(fileInputStream);
sheets = wb.iterator();
} else if (fileType.equals("xlsx")) {
wb = new XSSFWorkbook(fileInputStream);
sheets = wb.iterator();
} else {
return null;
}
if(sheets == null){
throw new GunsException(BizExceptionEnum.DATA_NULL);
}
// 遍历excel里每个sheet的数据。
while (sheets.hasNext()) {
Sheet sheet = sheets.next();
List<Map<Integer,Object>> list = getCellValue(sheet);
lists.add(list);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (fileInputStream != null) fileInputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return lists;
}
/**
* 获取sheet工作表中的内容
* @param sheet excel表格sheet工作页
* @return 工作表中数据内容集合
* @author xWang
* @Date 2020-05-09
*/
private static List<Map<Integer,Object>> getCellValue(Sheet sheet){
ArrayList<Map<Integer,Object>> list = new ArrayList<Map<Integer,Object>>();
Cell cell=null;
for (int rowNum = sheet.getFirstRowNum(); rowNum <sheet.getPhysicalNumberOfRows(); rowNum++) {
Row row = sheet.getRow(rowNum);
if (rowNum < 1){
continue;
}
Map<Integer,Object> map = new HashMap<>();
for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
cell = row.getCell(j);
String cellStringVal = getCellStringVal(cell);
map.put(j,cellStringVal);
}
list.add(map);
}
return list;
}
/**
* 判断Excel数据类型
* @param cell 读取de数据
* @return 对应的文本格式类型
* @author xWang
* @Date 2020-5-09
*/
private static String getCellStringVal(Cell cell) {
int cellType = cell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){
//用于转化为日期格式
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
return formater.format(d);
}
return String.valueOf((int)cell.getNumericCellValue());
case Cell.CELL_TYPE_STRING:
return cell.getRichStringCellValue().getString();
case Cell.CELL_TYPE_BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case Cell.CELL_TYPE_FORMULA:
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
try {
return formatter.format(cell.getDateCellValue());
} catch (Exception e){
throw new GunsException(BizExceptionEnum.DATA_FORMAT_ERROR);
}
case Cell.CELL_TYPE_BLANK:
return "";
case Cell.CELL_TYPE_ERROR:
return String.valueOf(cell.getErrorCellValue());
default:
return null;
}
}
}
下载工具类
public class ExcelDownLoadUtil {
static final short borderpx = 1;
/**
* 导出excel表格（标准格式）
* @param head 表头
* @param body 数据信息
* @return
* @author xWang
*/
public static HSSFWorkbook expExcel(List<String> head, List<List<String>> body) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Sheet1");
HSSFRow row = sheet.createRow(0);
HSSFCell cell= null;
HSSFCellStyle cellStyle = workbook.createCellStyle();
setBorderStyle(cellStyle, borderpx);
cellStyle.setFont(setFontStyle(workbook, "黑体", (short) 14));
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置表头颜色
// HSSFFont redFont = workbook.createFont();
// redFont.setColor(Font.COLOR_RED);
// short color = Font.COLOR_RED;
sheet.createFreezePane(0,1,0,1);
for (int i = 0; i<head.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(head.get(i));
cell.setCellStyle(cellStyle);
}
HSSFCellStyle cellStyle2 = workbook.createCellStyle();
setBorderStyle(cellStyle2, borderpx);
cellStyle2.setFont(setFontStyle(workbook, "宋体", (short) 12));
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
for (int i = 0; i < body.size(); i++) {
row = sheet.createRow(i + 1);
List<String> paramList = body.get(i);
for (int p = 0; p < paramList.size(); p++) {
cell = row.createCell(p);
cell.setCellValue(paramList.get(p));
cell.setCellStyle(cellStyle2);
}
}
for (int i = 0, isize = head.size(); i < isize; i++) {
sheet.autoSizeColumn(i);
}
return workbook;
}
/**
* 文件输出
* @param workbook 填充好的workbook
* @author xWang
*/
public static void outFile(HSSFWorkbook workbook, HttpServletResponse response, String fileName) {
OutputStream os=null;
try {
response.addHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "UTF-8"));
os= new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=utf-8");
workbook.write(os);
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
try {
os.flush();
os.close();
System.gc();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 设置字体样式
* @param workbook 工作簿
* @param name 字体类型
* @param height 字体大小
* @return HSSFFont
* @author xWang
*/
private static HSSFFont setFontStyle(HSSFWorkbook workbook, String name, short height) {
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints(height);
font.setFontName(name);
return font;
}
/**
* 设置单元格样式
* @param cellStyle 工作簿
* @param border border样式
* @author xWang
*/
private static void setBorderStyle(HSSFCellStyle cellStyle, short border) {
cellStyle.setBorderBottom(border); // 下边框
cellStyle.setBorderLeft(border);// 左边框
cellStyle.setBorderTop(border);// 上边框
cellStyle.setBorderRight(border);// 右边框
}
}