java控制excel_java操作excel

1. 生成并下载excel文件

controller

&＃64;RequestMapping(value &＃61; "/download", method &＃61; RequestMethod.GET)

public xxx downloadFile(HttpServletResponse response) {

response.setContentType("application/octet-stream");

response.setHeader("content-type", "application/octet-stream");

try {

response.setHeader("Content-Disposition", "attachment;fileName&＃61;" &＃43; java.net.URLEncoder.encode(fileName, "UTF-8"));// 设置文件名

} catch (UnsupportedEncodingException e) {

e.printStackTrace();

}

try {

excelService.download(response.getOutputStream());

} catch (IOException e) {

e.printStackTrace();

}

return null;

}

service

public interface ExcelService {

void download(OutputStream outputStream) throws IOException;

}

impl

&＃64;Override

public void download(OutputStream outputStream) throws IOException{

//1.创建excel(工作簿)

Workbook wb &＃61; new XSSFWorkbook();

//2.创建Sheet

Sheet sheet &＃61; wb.createSheet();

//3.创建表头,参数为要创建的行的位置

Row header &＃61; sheet.createRow(0);

//4.创建单元格对象,向单元格写数据

Cell cell &＃61; null;

List headerList &＃61; Lists.newArrayList("姓名","年龄","性别","爱好","生日");

for (int i &＃61; 0; i

//行对象.createCell(单元格位置)

cell &＃61; header.createCell(i);

//赋值

cell.setCellValue(headerList.get(i));

}

//5.工作簿写进输出流

wb.write(outputStream);

}

2. 解析前端上传的excel

Controller

&＃64;RequestMapping(value &＃61; "/excel/import", method &＃61; RequestMethod.GET)

public xxx uploadExcel(&＃64;RequestParam("upload") MultipartFile file) {

excelService.uploadExcel(file);

return xxx.OK;

}

service

public interface ExcelService {

void uploadExcel(MultipartFile multipartFile);

}

impl

public void uploadExcel(MultipartFile multipartFile) {

List dataList &＃61; ExcelUtil.loadExcelData(multipartFile);

}

ExcelUtil

public static List loadExcelData(MultipartFile file) throws Exception {

Workbook wb;// 创建Excel2003文件对象

wb &＃61; WorkbookFactory.create(file.getInputStream());

//获取第一个页签对象

Sheet sheet &＃61; wb.getSheetAt(0);

//获取有内容的总行数

int rowsNumber &＃61; sheet.getLastRowNum();

if (rowsNumber >&＃61; 0) {

List excelList &＃61; new ArrayList<>(rowsNumber);

//遍历页签的每一行

Row firstRow &＃61; sheet.getRow(0);

if (rowsNumber &＃61;&＃61; 0 && firstRow &＃61;&＃61; null) {

return null;

}

//总列数

int cellsNumber &＃61; sheet.getRow(0).getLastCellNum();

for (int i &＃61; 0; i <&＃61; rowsNumber; i&＃43;&＃43;) {

Row row &＃61; sheet.getRow(i);// 获取行对象

if (row &＃61;&＃61; null) {// 如果为空&＃xff0c;不处理

continue;

}

int num &＃61; 0;//记录该行的空格总数

List newList &＃61; new ArrayList<>(cellsNumber);

for (int j &＃61; 0; j

Cell cell &＃61; row.getCell(j);// 获取单元格对象

if (cell !&＃61; null) {

String value &＃61; getValueByType(cell);

if (StringUtils.isEmpty(value)) {

num &＃43;&＃61; 1;

}

newList.add(value);

} else {

newList.add("");

num &＃43;&＃61; 1;

}

}

if (num

excelList.add(newList);

}

}

return excelList;

}

wb.close();

return null;

}

private static String getValueByType(Cell cell) {

//判断是否为null或空串

if (cell&＃61;&＃61;null || cell.toString().trim().equals("")) {

return "";

}

String cellValue &＃61; "";

int cellType&＃61;cell.getCellType();

if(cellType&＃61;&＃61;Cell.CELL_TYPE_FORMULA){ //表达式类型

cellType&＃61;evaluator.evaluate(cell).getCellType();

}

switch (cellType) {

case Cell.CELL_TYPE_STRING: //字符串类型

cellValue&＃61; cell.getStringCellValue().trim();

cellValue&＃61;StringUtils.isEmpty(cellValue) ? "" : cellValue;

break;

case Cell.CELL_TYPE_BOOLEAN: //布尔类型

cellValue &＃61; String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_NUMERIC: //数值类型

if (HSSFDateUtil.isCellDateFormatted(cell)) { //判断日期类型

cellValue &＃61; DateUtil.formatDateByFormat(cell.getDateCellValue(), "yyyy-MM-dd");

} else { //否

cellValue &＃61; new DecimalFormat("#.######").format(cell.getNumericCellValue());

}

break;

default: //其它类型&＃xff0c;取空串吧

cellValue &＃61; "";

break;

}

return cellValue;

}