POI 导入、导出Excel

private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx"; /**
* 判断Excel的版本,获取Workbook
* @param in
* @param file
* @return
* @throws IOException
*/
public static Workbook getWorkbok(InputStream in, MultipartFile file) throws IOException {
Workbook wb = null;
if(file.getOriginalFilename().endsWith(EXCEL_XLS)){ //Excel 2003
wb = new HSSFWorkbook(in);
}else if(file.getOriginalFilename().endsWith(EXCEL_XLSX)){ // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
return wb;
} /**
* 判断文件是否是excel
* @throws Exception
*/
public static void checkExcelVaild(MultipartFile file) throws Exception{
if(ToolUtil.isEmpty(file)){
throw new Exception("文件不存在");
}
if(!((file.getOriginalFilename().endsWith(EXCEL_XLS) || file.getOriginalFilename().endsWith(EXCEL_XLSX)))){
throw new Exception("文件不是Excel");
}
} /**
* 读取Excel，兼容 Excel 2003/2007/2010
* @param excelFile
* @param sheetIndex从第几个sheet开始遍历
* @param dataRowIndex从第几行开始遍历
* @throws Exception
*/
public static List<Map<String, Object>> parseExcelObject(MultipartFile excelFile, int sheetIndex, int dataRowIndex) { List<Map<String,Object>> lists = new ArrayList<>();
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
try {
// 同时支持Excel 2003、2007
FileInputStream is = (FileInputStream) excelFile.getInputStream(); // 文件流
checkExcelVaild(excelFile);
Workbook workbook = getWorkbok(is,excelFile);
//Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel2003/2007/2010都是可以处理的 /**
* 设置当前excel中sheet的下标：sheetIndex
*/
if(sheetIndex >= 0 && sheetIndex < workbook.getNumberOfSheets())
{
for (; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++)
{ int rowSize = 0;
Sheet sheet = workbook.getSheetAt(sheetIndex);
for(int rowIndex = dataRowIndex; rowIndex <= sheet.getLastRowNum(); rowIndex++)
{
Row row = sheet.getRow(rowIndex);
if(row == null)
continue;
int tempRowSize = row.getLastCellNum();
if(tempRowSize > rowSize)
rowSize = tempRowSize; Map<String, Object> maps = new HashMap<String, Object>();
String rowValue = "";
for(short columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++)
{
Cell cell = row.getCell(columnIndex);
if(ToolUtil.isEmpty(cell))
continue;
int cellType = cell.getCellType(); String cellValue = "";
switch (cellType)
{
case Cell.CELL_TYPE_STRING: // 文本
cellValue = cell.getRichStringCellValue().getString().trim();
break;
case Cell.CELL_TYPE_NUMERIC: // 数字、日期
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.format(cell.getDateCellValue());
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = String.valueOf(cell.getRichStringCellValue().getString());
}
break;
case Cell.CELL_TYPE_BOOLEAN: // 布尔型
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK: // 空白
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_ERROR: // 错误
cellValue = "";
break;
case Cell.CELL_TYPE_FORMULA: // 公式
// 得到对应单元格的公式
//cellValue = cell.getCellFormula() + "#";
// 得到对应单元格的字符串
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = String.valueOf(cell.getRichStringCellValue().getString());
break;
default:
cellValue = "";
}
// 保存数据
if (columnIndex == 0)
{
maps.put("name", cellValue);
}
// ......其他逻辑......
}
lists.add(maps);
}
}
} is.close(); //关闭文件流 } catch (Exception e) {
e.printStackTrace();
} finally{ }
return lists;
}

/**
* 导入excel
*
* @param multipartFile
* @return
*/
@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
@ResponseBody
public void importExcel(@RequestParam("file") MultipartFile multipartFile) {
if (multipartFile == null) {
throw new Exception("excel文件请求参数错误");
}
List<Map<String, Object>> list = ExcelUtil.parseExcelObject(multipartFile, 0, 0); // 这里只解析第一个sheet，从第一行开始
for(Map<String, Object> map : list){
System.out.println(map.getString("name"));
}
}

public static XSSFWorkbook createExcelObject(List<ExcelObject> list) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
try {
// 第一步,创建一个webbook文件,对应一个excel文件
XSSFWorkbook wb = new XSSFWorkbook();
// 第二部,在excel中添加一个sheet工作簿,参数为该工作簿名字,不写为默认;
XSSFSheet sheet = wb.createSheet("sheet1");
// 第三部,做sheet中添加表头第0行,注意老版本poi对excel的行数列数有限制short
XSSFRow row = sheet.createRow((int) 0); // 第四部,设置单元格样式
XSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);//创建一个居中格式
// 生成字体
Font font = wb.createFont();
font.setFontHeightInPoints((short) 12);
font.setBold(true);
// 把字体应用到当前的样式
style.setFont(font); // 第五部,设置好表头内容
XSSFCell cell = row.createCell(0);
cell.setCelSPUue("name");
cell.setCellStyle(style) // 第六部,写入实体数据 实际应用中这些数据应该是从数据库中得到
for (int i = 0; i < list.size(); i++) {
// 每次新建一行然后在新行中插入list中的数据对象,有点繁琐,也许有更好的封装方法,留待后看
row = sheet.createRow((int) i + 1);
row.createCell((int) 0).setCellValue(list.get(i).getName());
} return wb; } catch (Exception e) {
e.printStackTrace();
} finally { }
return null;
}

/**
* 导出商品信息
*
* @param request
* @param response
* @throws Exception
*/
@RequestMapping(value = "/downExcel",method = RequestMethod.POST)
@ResponseBody
public void downExcel(HttpServletRequest request, HttpServletResponse response){ List<ExcelObject> list; // 查询出来的数据 // 正确代码顺序
//FileInputStream fs=new FileInputStream(excel);
//XSSFWorkbook workbook = new XSSFWorkbook(fs);
//FileOutputStream out=new FileOutputStream(excel);
//workbook.write(out); XSSFWorkbook wb = ExcelUtil.createExcelObject(list);
if (wb == null)
throw new Exception("excel文件解析异常"); try {
// 设置输出的格式
response.reset();// 清空输出流
response.setHeader("Content-Disposition", "attachment; filename=" + new String(("excel.xlsx").getBytes(), "iso-8859-1"));// 设定输出文件头
response.setContentType("application/x-download;charset=GBK");// 定义输出类型 //创建输出流
OutputStream outputStream = response.getOutputStream(); wb.write(outputStream);
outputStream.close();
}catch (Exception e){
e.printStackTrace();
}
}