用POI实现 报表读取
来源:互联网 发布:java list转json字符串 编辑:程序博客网 时间:2024/05/20 14:17
package com.bjnlmf.nerc.operation.web.controller.picture;import com.bjnlmf.nerc.common.api.enumeration.ImageType;import com.bjnlmf.nerc.common.api.util.ResponseJson;import com.bjnlmf.nerc.configration.api.enumeration.ServiceType;import com.bjnlmf.nerc.configration.api.pojo.Area;import com.bjnlmf.nerc.configration.api.pojo.AreaQuery;import com.bjnlmf.nerc.configration.api.pojo.News;import com.bjnlmf.nerc.configration.api.service.AreaService;import com.bjnlmf.nerc.configration.api.service.NewsService;import com.bjnlmf.nerc.configration.api.service.UploadService;import com.bjnlmf.nerc.operation.web.shiro.ShiroUtils;import org.apache.commons.jexl2.UnifiedJEXL;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.ResponseBody;import org.springframework.web.multipart.MultipartFile;import java.io.*;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import java.util.Map;@Controller@RequestMapping("/upload")public class UploadController { @Autowired UploadService uploadService; @Autowired NewsService newsService; @Autowired AreaService areaService; private Logger logger = LoggerFactory.getLogger(UploadController.class); /** * 上传图片和缩略图 */ @RequestMapping(value = "image", method = RequestMethod.POST) @ResponseBody public ResponseJson<Map<String,String>> imageUpload(ImageType imageType, MultipartFile image) throws IOException{ String prefix=getPrefix(image);//获得文件后缀 return ResponseJson.ok(uploadService.upload(image.getBytes(),prefix,imageType)); } /** * 上传文件 */ @RequestMapping(value = "file", method = RequestMethod.POST) @ResponseBody public ResponseJson<String> fileUpload(MultipartFile file) throws IOException { String prefix=getPrefix(file);//获得文件后缀 return ResponseJson.ok(uploadService.upload(file.getBytes(),prefix)); } //获得文件后缀 private String getPrefix(MultipartFile file){ String fileName = file.getOriginalFilename(); return fileName.substring(fileName.lastIndexOf(".")+1); } /** * 读取excel文件 */ @RequestMapping(value = "excel", method = RequestMethod.POST) @ResponseBody public ResponseJson<String> excelUpload(MultipartFile file) throws IOException { // 检查 // this.preReadCheck(filePath); // 获取workbook对象 Workbook workbook = null; try { ByteArrayInputStream is = new ByteArrayInputStream(file.getBytes()); String prefix=getPrefix(file); if (prefix.equals("xls")) { workbook = new HSSFWorkbook(is); } else if (prefix.equals("xlsx")) { workbook = new XSSFWorkbook(is); } // 读文件 一个sheet一个sheet地读取 for (int numSheet = 0; numSheet < 1; numSheet++) { Sheet sheet = workbook.getSheetAt(numSheet); if (sheet == null) { continue; } int firstRowIndex = sheet.getFirstRowNum(); int lastRowIndex = sheet.getLastRowNum(); // 读取首行 即,表头 Row firstRow = sheet.getRow(firstRowIndex); if(!this.getCellValue(firstRow.getCell(0), true).equals("标题")){ return ResponseJson.ok("excel文件字段不对应"); } if(!this.getCellValue(firstRow.getCell(1), true).equals("来源")){ return ResponseJson.ok("excel文件字段不对应"); } if(!this.getCellValue(firstRow.getCell(2), true).equals("政策发布时间")){ return ResponseJson.ok("excel文件字段不对应"); } if(!this.getCellValue(firstRow.getCell(3), true).equals("内容")){ return ResponseJson.ok("excel文件字段不对应"); } if(!this.getCellValue(firstRow.getCell(4), true).equals("所属省份")){ return ResponseJson.ok("excel文件字段不对应"); } if(!this.getCellValue(firstRow.getCell(5), true).equals("市名")){ return ResponseJson.ok("excel文件字段不对应"); } // 读取数据行 for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) { Row currentRow = sheet.getRow(rowIndex);// 当前行 News news = new News(); String title = this.getCellValue(currentRow.getCell(0), true); if (title.equals("") || title == null) { break; } news.setTitle(title); String resource = this.getCellValue(currentRow.getCell(1), true); if(resource!="" && resource !=null){ news.setSource(resource); } String time = this.getCellValue(currentRow.getCell(2), true); if(time!=""&&time!=null){ SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd"); Date date = sf.parse(time); news.setReleaseTime(date); }else{ news.setReleaseTime(new Date()); } news.setContent(this.getCellValue(currentRow.getCell(3), true)); //news.setServiceType(this.getCellValue(currentRow.getCell(4), true)); String province = this.getCellValue(currentRow.getCell(4), true); String city = this.getCellValue(currentRow.getCell(5), true); List<Area> province1s= areaService.queryListProvince(); for (Area province1:province1s) { if(province1.getAreaName().equals(province)){ if(city == null || city == ""){ news.setTerritory(province1.getAreaCode()); break; }else{ AreaQuery query = new AreaQuery(); query.setParentId(province1.getId()); List<Area> citys = areaService.queryList(query); for (Area ct:citys) { if(ct.getAreaName().equals((city))){ news.setTerritory(ct.getAreaCode()); break; } } break; } } } String userToken = ShiroUtils.getCurrentUserToken(); news.setUserToken(userToken); news.setServiceState("1"); newsService.save(news); } } } catch (Exception e) { e.printStackTrace(); } finally { if (workbook != null) { try { workbook.cloneSheet(1); } catch (UnifiedJEXL.Exception e) { e.printStackTrace(); } } } return ResponseJson.ok("导入成功"); } /** * 取单元格的值 * @param cell 单元格对象 * @param treatAsStr 为true时,当做文本来取值 (取到的是文本,不会把“1”取成“1.0”) * @return */ private String getCellValue(Cell cell ,boolean treatAsStr) { if (cell == null) { return ""; } if (treatAsStr) { // 虽然excel中设置的都是文本,但是数字文本还被读错,如“1”取成“1.0” // 加上下面这句,临时把它当做文本来读取 cell.setCellType(Cell.CELL_TYPE_STRING); } if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return String.valueOf(cell.getNumericCellValue()); } else { return String.valueOf(cell.getStringCellValue()); } }}
0 0
- 用POI实现 报表读取
- 【POI】POI+JFREECHART实现图表报表
- springmvc poi实现报表导出
- 使用poi灵活生成和读取报表
- Apache POI实现excel读取
- java读取excel实现 poi
- poi 读取 excel ->简单实现
- POI实现导出Excel报表小例
- poi报表实现,导出到excel文件
- Java实现POI导出Excel报表功能
- 使用POI实现报表打印功能
- poi实现Excel模板的报表导出
- java用poi实现对word读取和修改操作
- java用poi实现对word读取和修改操作
- java用poi实现对word读取和修改操作
- POI报表
- POI报表
- Java用poi读取excel
- 多继承
- 建行E商贸通支付开发系列之四(测试环境搭建)
- hdu4565 So Easy! 广义斐波那契+矩阵快速幂+共轭构造
- ps去色的10种方法
- mac下终端命令行下添加mysql命令
- 用POI实现 报表读取
- 深入理解Java的接口和抽象类
- 一些面试题
- 虚拟机(win7-32bit)与主机(ubuntu14.04-64bit)实现同一个网络
- 建行E商贸通支付开发系列之五(接口功能的开发)
- 文档 ID 1324574.1 11gR2 RAC 服务在实例关闭时不会故障转移到其他节点
- 第三方框架汇总
- Eclipse的使用Tip
- 契约——XSD