java控制excel_java操作excel

1. 生成并下载excel文件

controller

@RequestMapping(value = "/download", method = 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=" + 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

@Override

public void download(OutputStream outputStream) throws IOException{

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

Workbook wb = new XSSFWorkbook();

//2.创建Sheet

Sheet sheet = wb.createSheet();

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

Row header = sheet.createRow(0);

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

Cell cell = null;

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

for (int i = 0; i < headerList.size(); i++) {

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

cell = header.createCell(i);

//赋值

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

}

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

wb.write(outputStream);

}

2. 解析前端上传的excel

Controller

@RequestMapping(value = "/excel/import", method = RequestMethod.GET)

public xxx uploadExcel(@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 = ExcelUtil.loadExcelData(multipartFile);

}

ExcelUtil

public static List loadExcelData(MultipartFile file) throws Exception {

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

wb = WorkbookFactory.create(file.getInputStream());

//获取第一个页签对象

Sheet sheet = wb.getSheetAt(0);

//获取有内容的总行数

int rowsNumber = sheet.getLastRowNum();

if (rowsNumber >= 0) {

List excelList = new ArrayList<>(rowsNumber);

//遍历页签的每一行

Row firstRow = sheet.getRow(0);

if (rowsNumber == 0 && firstRow == null) {

return null;

}

//总列数

int cellsNumber = sheet.getRow(0).getLastCellNum();

for (int i = 0; i <= rowsNumber; i++) {

Row row = sheet.getRow(i);// 获取行对象

if (row == null) {// 如果为空，不处理

continue;

}

int num = 0;//记录该行的空格总数

List newList = new ArrayList<>(cellsNumber);

for (int j = 0; j < cellsNumber; j++) {

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

if (cell != null) {

String value = getValueByType(cell);

if (StringUtils.isEmpty(value)) {

num += 1;

}

newList.add(value);

} else {

newList.add("");

num += 1;

}

}

if (num < cellsNumber) {//不是全部空格，把这行加到返回数据中

excelList.add(newList);

}

}

return excelList;

}

wb.close();

return null;

}

private static String getValueByType(Cell cell) {

//判断是否为null或空串

if (cell==null || cell.toString().trim().equals("")) {

return "";

}

String cellValue = "";

int cellType=cell.getCellType();

if(cellType==Cell.CELL_TYPE_FORMULA){ //表达式类型

cellType=evaluator.evaluate(cell).getCellType();

}

switch (cellType) {

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

cellValue= cell.getStringCellValue().trim();

cellValue=StringUtils.isEmpty(cellValue) ? "" : cellValue;

break;

case Cell.CELL_TYPE_BOOLEAN: //布尔类型

cellValue = String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_NUMERIC: //数值类型

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

cellValue = DateUtil.formatDateByFormat(cell.getDateCellValue(), "yyyy-MM-dd");

} else { //否

cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());

}

break;

default: //其它类型，取空串吧

cellValue = "";

break;

}

return cellValue;

}