1、导入
public STRING importExcel(@RequestParam("excelPath") MultipartFile file,@RequestParam("fileName") String fileName){
try {
if(!file.isEmpty()) {
BASE64Decoder decoder = new BASE64Decoder();
String filename = new String(decoder.decodeBuffer(fileName),"UTF-8");
int index = filename.lastIndexOf(".");
filename = filename.substring(0, index) "_" userid filename.substring(index, filename.length());
File fileDir = FileUtils.getExeclDirFile(super.uploadPath);
File newFile = new File(fileDir.getAbsolutePath() File.separator filename);
file.transferTo(newFile);
//各种验证
if (!fileName.matches("^. \\.(?i)(xls)$") && !fileName.matches("^. \\.(?i)(xlsx)$")) {
throw new Exception("上传文件格式不正确");
}
boolean isExcel2003 = true;
if (fileName.matches("^. \\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
Workbook wb = null;
inputStream is = new FileInputStream(file);
try{
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(2);//第一行
XSSFCell cell = (XSSFCell) row.getCell(1);
if(cell == null) {
return FastJsonUtils.resultSuccess(400, "失败","格式验证");
}
cell.setCellType(CellType.STRING);
String year = cell.getStringCellValue();
if(StringUtils.isEmpty(year)){
return FastJsonUtils.resultSuccess(400, "失败","格式验证");
}
//从第几列开始
if(sheet.getLastRowNum() >=4) {
for (int r = 4; r <= sheet.getLastRowNum(); r ) {
row = sheet.getRow(r);
if (row == null){
continue;
}
//获取每列的数据
XSSFCell cellDealerCode = (XSSFCell) row.getCell(0);
if(cellDealerCode == null) {
continue;
}
cellDealerCode.setCellType(CellType.STRING);
String str = cellDealerCode.getStringCellValue();
//TODO 处理业务逻辑
}
}
}finally {
if(wb != null) {wb.close(); }
if (is != null){is.close();}
}
} else {
return FastJsonUtils.resultSuccess(400, "失败","文件为空!");
}
} catch (Exception e) {
return FastJsonUtils.resultSuccess(400, "失败","上传文件失败,请重试!");
}
}
2、导出
public void downExcel(HttpServletRequest request,HttpServletResponse response) throws UnsupportedEncodingException {
String filename = "要显示的文件名.xlsx";
InputStream input = null;
OutputStream outputStream = null;
SXSSFWorkbook wb = null;
XSSFWorkbook swb = null;
try {
ClassPathResource cpr = new ClassPathResource("/template/" filename);
input = cpr.getInputStream();
swb = (XSSFWorkbook) WorkbookFactory.create(input);
wb = new SXSSFWorkbook(swb,-1);
Sheet sheet = wb.getSheetAt(0); // 工作表对象
Row nRow = null; // 行对象
Cell nCell = null; // 列对象
//设置格式样式
CellStyle style = ExportExcelUtil.setStyle(wb, null, true, true, CommonType.fontNAME, 10, false);
//TODO 根据自己的需求赋值
nCell = swb.getSheetAt(0).getRow(2).getCell(1);
nCell.setCellValue("第一个sheet页第三行的第二格赋值");
//处理各浏览器的兼容，防止乱码
String agent = request.getHeader("USER-AGENT");
if (null != agent && (-1 != agent.indexOf("MSIE") || -1 != agent.indexOf("Trident") || -1 != agent.indexOf("Edge"))) {
codedfilename = java.net.URLEncoder.encode(filename, "UTF8");
} else {
codedfilename = new String(filename.getBytes("UTF-8"), "iso-8859-1");
}
response.setContentType("application/msexcel;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=\"" codedfilename "\"");
response.flushBuffer();
outputStream = response.getOutputStream();
wb.write(response.getOutputStream());
outputStream.flush();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (swb != null) {swb.close();}
if (wb != null) {wb.close();}
if (outputStream != null) {outputStream.close();}
if (input != null) {input.close();}
} catch (Exception e) {}
}
}
3、单元格样式设置
public static CellStyle setStyle(Workbook wb,Object backgroundColor,boolean isAlignment,boolean isWrapText,String fontName,Object fontSize,boolean isBold) {
CellStyle style = wb.createCellStyle();
if(backgroundColor != null) {
style.setFillForegroundColor(Short.valueOf(backgroundColor.toString()));// 设置背景色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
if(isAlignment) {
style.setAlignment(HorizontalAlignment.CENTER); // 水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
}
if(isWrapText) {
style.setWrapText(true);//设置自动换行
}
//在样式用应用设置的字体;
Font font = wb.createFont();
if(!StringUtils.isEmpty(fontName)) {
font.setFontName(fontName);//设置字体名字
}
if(fontSize != null) {
font.setFontHeightInPoints(Short.valueOf(fontSize.toString()));//设置字体大小
}
if(isBold) {
font.setBold(true);
}
style.setBorderBottom(BorderStyle.THIN);//下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
style.setFont(font);
return style;
}
好好学习，天天搬砖，请点击关注学习更多java小知识
