背景:某同学找到我,问我能不能帮忙写个代码,批量设置excel的超链接(excel有文件名)。然后立马想到poi,这不是很简单吗。于是写了个小demo,最后也被同学称赞牛逼(我就深藏功与名的微笑.jpg)
PS:建议将excel文件改为xls文件
以下是代码:
前端js上传代码太简单了,一个ajax上传即可,只展示controller代码。
@Controller
@RequestMapping(value = "/test/excel")
public class TestExcelController {
@RequestMapping(value = "/uploadFile.do", method = RequestMethod.POST)
@ResponseBody
public Map<String, Object> uploadReportBiFile(HttpServletRequest request,HttpServletResponse response, MultipartFile file) {
Map<String, Object> resultMap = new HashMap<String, Object>();
resultMap.put("result", "success");
try {
InputStream inputStream = null;
Workbook workbook = null;
try {
inputStream = file.getInputStream();
String fileAllName = file.getOriginalFilename();
String fileName=fileAllName.substring(0,fileAllName.lastIndexOf("."));
int type=0;
String targetFilePath="./pdf/";//excel文件目录下的pdf文件夹
//建议用xls文件
if (fileAllName.endsWith(".xls")) {
workbook = new HSSFWorkbook(inputStream);
type=2;
} else if (fileAllName.endsWith(".xlsx")) {//不建议使用
workbook = new XSSFWorkbook(inputStream);
type=1;
} else {
resultMap.put("result", "error");
resultMap.put("message", "文件必须为xls或者xlsx");
return resultMap;
}
//假设数据在第1个sheet,并且是从第一列的第二行开始,
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
Integer rowIndex = row.getRowNum(); // 行索引
if (rowIndex == 0 || rowIndex>1000) {//第1行是标题跳过,假设最多1000个数据
continue; // 这里的if没太多作用,只是为了不让循环太多次,节约时间
}
try {
Cell cell = row.getCell(0);//第1列
if (cell!=null) {
String name = getCellValue(cell);
if (name!=null) {
String namef=name+".pdf";
if (type==1) {//xlsx
//这种方式不建议使用,因为框里会显示函数
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("HYPERLINK(\"" + targetFilePath+namef+ "\",\"" + name+"\")");
cell.setCellValue(name);
CellStyle linkStyle = workbook.createCellStyle();
Font cellFont= workbook.createFont();
cellFont.setUnderline(HSSFFont.U_SINGLE);
cellFont.setColor(HSSFColor.BLUE.index);
cellFont.setFontHeightInPoints((short)10);//font size
linkStyle.setFont(cellFont);
cell.setCellStyle(linkStyle);
}
if (type==2) {//xls
//建议使用这种
CellStyle linkStyle = workbook.createCellStyle();
Font cellFont = workbook.createFont();
cellFont.setUnderline(HSSFFont.U_SINGLE);
cellFont.setColor(HSSFColor.BLUE.index);
cellFont.setFontHeightInPoints((short)10);//font size
linkStyle.setFont(cellFont);
cell.setCellValue(name);
Hyperlink link = cell.getHyperlink();
link = new HSSFHyperlink(HSSFHyperlink.LINK_FILE);
link.setAddress(targetFilePath+namef);
cell.setHyperlink(link);
cell.setCellStyle(linkStyle);
}
}
}
} catch (Exception e) {
// 解析失败
e.printStackTrace();
}
}
//写到本地
File newFile=new File("D:/test");
OutputStream stream=null;
try {
stream = new FileOutputStream(new File(newFile, new Date().getTime()+"-"+fileAllName));
workbook.write(stream);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(stream != null);
stream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
System.out.println("--------------------success---------------------");
}catch(Exception e){
e.printStackTrace();
}
} catch (Exception e) {
resultMap.put("result", "error");
resultMap.put("message", e.getMessage() + ",发生错误");
}
return resultMap;
}
/**
*
* <p>Title: getCellValue</p>
*
* <p>Description: 获取excel的单元的值</p>
*
* @param cell
* @return
*
*/
public static String getCellValue(Cell cell) {
if(null == cell){
return "";
}
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
// 返回布尔类型的值
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
// 返回数值类型的值
return String.valueOf((int)cell.getNumericCellValue());
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
String cellValue = cell.getStringCellValue();
if(null != cellValue){
cellValue = cellValue.trim();
}
// 返回字符串类型的值
return String.valueOf(cellValue);
}
}
}