时间:2021-07-01 10:21:17 帮助过:25人阅读
业务场景:用户提供Excel表,在页面上点击“导入按钮”,系统读取Excel中的数据,存到对应的数据库
注:
1,目前仅提供导入简单的Excel表,没有合并单元格,只能读取单页sheet
2,方法里用到了具体业务场景的实体类,所以适用性不是很强,其他场景要用的话,还需要修改代码。但是也具有一定的参考性,所以先记录下来。我的后续思路是:在 工具类(ReadExcelUtil) 里根据导入的Excel表头确定要处理的字段名,然后拼成json格式的数据,传到Service层,再做处理。之后再做更新 //TODO
3,用的jar包:POI
正文:
html:
- <span style="color: #0000ff;"><</span><span style="color: #800000;">div</span><span style="color: #0000ff;">></span>
- <span style="color: #0000ff;"><</span><span style="color: #800000;">span</span><span style="color: #0000ff;">><</span><span style="color: #800000;">input </span><span style="color: #ff0000;">type</span><span style="color: #0000ff;">="file"</span><span style="color: #ff0000;"> id</span><span style="color: #0000ff;">="upfile"</span><span style="color: #ff0000;"> name</span><span style="color: #0000ff;">="upfile"</span><span style="color: #ff0000;"> placeholder</span><span style="color: #0000ff;">=""</span><span style="color: #0000ff;">/></</span><span style="color: #800000;">span</span><span style="color: #0000ff;">></span>
- <span style="color: #0000ff;"><</span><span style="color: #800000;">button </span><span style="color: #ff0000;">onclick</span><span style="color: #0000ff;">="importExp();"</span><span style="color: #0000ff;">></span>导入<span style="color: #0000ff;"></</span><span style="color: #800000;">button</span><span style="color: #0000ff;">></span>
- <span style="color: #0000ff;"><</span><span style="color: #800000;">span</span><span style="color: #0000ff;">></span>格式:.xls<span style="color: #0000ff;"></</span><span style="color: #800000;">span</span><span style="color: #0000ff;">></span>
- <span style="color: #0000ff;"></</span><span style="color: #800000;">div</span><span style="color: #0000ff;">></span>
JS:
- <span style="color: #0000ff;">function</span><span style="color: #000000;"> importExp() {
- </span><span style="color: #0000ff;">var</span> name = $("#upfile"<span style="color: #000000;">).val();
- </span><span style="color: #0000ff;">var</span> file = $("#upfile")[0].files[0<span style="color: #000000;">];
- </span><span style="color: #008000;">//</span><span style="color: #008000;"> ajax...</span>
- }
Java:
Controller层
- @ApiOperation(value = "导入Excel"<span style="color: #000000;">)
- @RequestMapping(value</span>="excel/import", method =<span style="color: #000000;"> RequestMethod.POST)
- </span><span style="color: #0000ff;">public</span> void<span style="color: #000000;"> importExcel(MultipartFile file) {
- </span><span style="color: #0000ff;">return</span> <span style="color: #0000ff;">this</span><span style="color: #000000;">.theService.importExcel(file);
- }</span>
Service层
- <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> importExcel(MultipartFile file) {
- </span><span style="color: #0000ff;">if</span><span style="color: #000000;">(file.isEmpty()){
- </span><span style="color: #008000;">//</span><span style="color: #008000;">请先选择Excel文件。是否把报错等信息返回到前端,视情况而定。</span>
- <span style="color: #0000ff;">return</span><span style="color: #000000;">;
- }
- Result readResult </span>= ReadExcelUtil.readExcel(file); <span style="color: #008000;">//</span><span style="color: #008000;">Result是封装了返回值的类,相当于实体类</span>
- <span style="color: #0000ff;">if</span>(readResult.getCode() != 0<span style="color: #000000;">){
- </span><span style="color: #008000;">//</span><span style="color: #008000;">错误:readResult.msg</span>
- <span style="color: #0000ff;">return</span><span style="color: #000000;">;
- }
- List</span><Award> list = (List<Award>) readResult.getData(); <span style="color: #008000;">//</span><span style="color: #008000;">Award是数据库表对应的实体类
- </span><span style="color: #008000;">//</span><span style="color: #008000;">获取了list之后,insert到数据库... </span>
- }
工具类:
ReadExcelUtil
- <span style="color: #0000ff;">package</span><span style="color: #000000;"> com.bf.base.utils;
- </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> com.bf.base.entity.DripAward;
- </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> com.bf.base.params.Result;
- </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.hssf.usermodel.HSSFWorkbook;
- </span><span style="color: #0000ff;">import</span> org.apache.poi.ss.usermodel.*<span style="color: #000000;">;
- </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.xssf.usermodel.XSSFWorkbook;
- </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.web.multipart.MultipartFile;
- </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.FileNotFoundException;
- </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.IOException;
- </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.InputStream;
- </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.text.DecimalFormat;
- </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.util.ArrayList;
- </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.util.Calendar;
- </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.util.List;
- </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> ReadExcelUtil {
- </span><span style="color: #008000;">/**</span><span style="color: #008000;">
- * 读取 单页sheet,返回一个集合
- * </span><span style="color: #808080;">@return</span>
- <span style="color: #008000;">*/</span>
- <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> Result<?><span style="color: #000000;"> readDripAwardExcel(MultipartFile file) {
- Result result </span>= <span style="color: #0000ff;">new</span> Result<><span style="color: #000000;">();
- InputStream is </span>= <span style="color: #0000ff;">null</span><span style="color: #000000;">;
- Workbook wb </span>= <span style="color: #0000ff;">null</span><span style="color: #000000;">;
- String fileName </span>=<span style="color: #000000;"> file.getOriginalFilename();
- String fileType </span>= fileName.substring(fileName.lastIndexOf(".") + 1<span style="color: #000000;">, fileName.length());
- </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
- is </span>=<span style="color: #000000;"> file.getInputStream();
- </span><span style="color: #0000ff;">if</span> (fileType.equals("xls"<span style="color: #000000;">)) {
- wb </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> HSSFWorkbook(is);
- } </span><span style="color: #0000ff;">else</span> <span style="color: #0000ff;">if</span> (fileType.equals("xlsx"<span style="color: #000000;">)) {
- wb </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> XSSFWorkbook(is);
- } </span><span style="color: #0000ff;">else</span><span style="color: #000000;"> {
- </span><span style="color: #0000ff;">return</span> <span style="color: #0000ff;">new</span> Result<>(Result.FAIL, "读取的不是excel文件", <span style="color: #0000ff;">null</span>, <span style="color: #0000ff;">null</span><span style="color: #000000;">);
- }
- </span><span style="color: #0000ff;">int</span> sheetSize = wb.getNumberOfSheets();<span style="color: #008000;">//</span><span style="color: #008000;">有多少sheet页</span>
- <span style="color: #0000ff;">if</span>(sheetSize >= 2<span style="color: #000000;">){
- </span><span style="color: #0000ff;">return</span> <span style="color: #0000ff;">new</span> Result<>(Result.FAIL, "请核对Excel的页数", <span style="color: #0000ff;">null</span>, <span style="color: #0000ff;">null</span><span style="color: #000000;">);
- }
- Sheet sheet </span>= wb.getSheetAt(0<span style="color: #000000;">);
- result </span>= sheetData2List(sheet); <span style="color: #008000;">//</span><span style="color: #008000;">关键,sheet表数据 转 集合</span>
- <span style="color: #000000;">
- } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (FileNotFoundException e) {
- e.printStackTrace();
- } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (IOException e) {
- e.printStackTrace();
- } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
- e.printStackTrace();
- }
- </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> result;
- }
- </span><span style="color: #008000;">//</span><span style="color: #008000;">获取数据</span>
- <span style="color: #0000ff;">private</span> <span style="color: #0000ff;">static</span><span style="color: #000000;"> Result sheetData2List(Sheet sheet) {
- Result result </span>= <span style="color: #0000ff;">new</span> Result<><span style="color: #000000;">();
- List</span><Award> awards = <span style="color: #0000ff;">new</span> ArrayList<><span style="color: #000000;">();
- </span><span style="color: #0000ff;">int</span> CELL_SIZE = 3; <span style="color: #008000;">//</span><span style="color: #008000;">excel固定三列(编码、金额) </span>
- <span style="color: #0000ff;">int</span> rowSize = sheet.getLastRowNum() + 1<span style="color: #000000;">;
- </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> j = 0; j < rowSize; j++) { <span style="color: #008000;">//</span><span style="color: #008000;">读取每一行</span>
- Row row =<span style="color: #000000;"> sheet.getRow(j);
- </span><span style="color: #0000ff;">if</span> (row == <span style="color: #0000ff;">null</span><span style="color: #000000;">) {
- </span><span style="color: #0000ff;">continue</span><span style="color: #000000;">;
- }
- </span><span style="color: #0000ff;">if</span>(row.getLastCellNum() !=<span style="color: #000000;"> CELL_SIZE){
- result.setCode(</span>2<span style="color: #000000;">);
- result.setMsg(</span>"第"+ j +"行数据异常,请核对后再上传!"<span style="color: #000000;">);
- </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> result;
- }
- </span><span style="color: #0000ff;">if</span> (j == 0<span style="color: #000000;">) {
- </span><span style="color: #0000ff;">continue</span><span style="color: #000000;">;
- } </span><span style="color: #0000ff;">else</span><span style="color: #000000;"> {
- DripAward rowObj </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> DripAward();
- </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> k = 0; k < CELL_SIZE; k++) { <span style="color: #008000;">//</span><span style="color: #008000;">获取列的数据</span>
- Cell cell =<span style="color: #000000;"> row.getCell(k);
- String value </span>=<span style="color: #000000;"> getCellFormatValue(cell);
- </span><span style="color: #0000ff;">switch</span><span style="color: #000000;"> (k) {
- </span><span style="color: #0000ff;">case</span> 0<span style="color: #000000;">:
- rowObj.setCode(AESUtil.encrypt(value));
- </span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
- </span><span style="color: #0000ff;">case</span> 1<span style="color: #000000;">:
- rowObj.setAmount(Double.parseDouble(value));
- </span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
- </span><span style="color: #0000ff;">case</span> 2<span style="color: #000000;">:
- rowObj.setClassify(Integer.parseInt(value));
- </span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
- </span><span style="color: #0000ff;">default</span><span style="color: #000000;">:
- </span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
- }
- }
- awards.add(rowObj);
- }
- }
- result.setCode(</span>0<span style="color: #000000;">);
- result.setData(awards);
- </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> result;
- }
- </span><span style="color: #008000;">//</span><span style="color: #008000;">获取列的数据</span>
- <span style="color: #0000ff;">private</span> <span style="color: #0000ff;">static</span><span style="color: #000000;"> String getCellFormatValue(Cell cell) {
- String cellvalue </span>= ""<span style="color: #000000;">;
- </span><span style="color: #0000ff;">if</span> (cell != <span style="color: #0000ff;">null</span><span style="color: #000000;">) {
- CellType cellType </span>=<span style="color: #000000;"> cell.getCellTypeEnum();
- </span><span style="color: #0000ff;">switch</span><span style="color: #000000;"> (cellType) {
- </span><span style="color: #0000ff;">case</span><span style="color: #000000;"> NUMERIC: {
- </span><span style="color: #0000ff;">if</span>(String.valueOf(cell.getNumericCellValue()).indexOf("E") == -1<span style="color: #000000;">){
- cellvalue </span>=<span style="color: #000000;"> String.valueOf(cell.getNumericCellValue());
- }</span><span style="color: #0000ff;">else</span><span style="color: #000000;"> {
- cellvalue </span>= <span style="color: #0000ff;">new</span> DecimalFormat("#"<span style="color: #000000;">).format(cell.getNumericCellValue());
- }
- </span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
- }
- </span><span style="color: #0000ff;">case</span><span style="color: #000000;"> STRING:
- cellvalue </span>=<span style="color: #000000;"> cell.getRichStringCellValue().getString();
- </span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
- </span><span style="color: #0000ff;">default</span><span style="color: #000000;">:
- cellvalue </span>= "-"<span style="color: #000000;">;
- }
- }
- </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> cellvalue;
- }
- }</span>
其他:
Result (封装了返回值的类,相当于实体类)
- <span style="color: #0000ff;">package</span><span style="color: #000000;"> com.bf.base.params;
- </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.commons.lang.StringUtils;
- </span><span style="color: #008000;">/**</span><span style="color: #008000;">
- * 传给前端数据的统一格式
- * code = 0时,表示调用成功
- * 其余code均表示调用接口异常,异常时,标明异常码,并给出msg和detail注释,同步文档
- </span><span style="color: #008000;">*/</span>
- <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span> Result<T><span style="color: #000000;"> {
- </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span> code;<span style="color: #008000;">//</span><span style="color: #008000;">状态返回码</span>
- <span style="color: #0000ff;">private</span> String msg;<span style="color: #008000;">//</span><span style="color: #008000;">返回码描述</span>
- <span style="color: #0000ff;">private</span> String detail;<span style="color: #008000;">//</span><span style="color: #008000;">错误详细描述或返回码对应处理方案</span>
- <span style="color: #0000ff;">private</span> T data;<span style="color: #008000;">//</span><span style="color: #008000;">返回的主体数据</span>
- <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">final</span> <span style="color: #0000ff;">int</span> FAIL = -1<span style="color: #000000;">;
- </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">final</span> <span style="color: #0000ff;">int</span> SUCCESS = 0<span style="color: #000000;">;
- @Override
- </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String toString() {
- </span><span style="color: #0000ff;">return</span> "Result [code=" + code + ", msg=" + msg + ", detail=" + detail + ", data=" + data + "]"<span style="color: #000000;">;
- }
- </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> getCode() {
- </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> code;
- }
- </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span> setCode(<span style="color: #0000ff;">int</span><span style="color: #000000;"> code) {
- </span><span style="color: #0000ff;">this</span>.code =<span style="color: #000000;"> code;
- }
- </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getMsg() {
- </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> msg;
- }
- </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setMsg(String msg) {
- </span><span style="color: #0000ff;">this</span>.msg =<span style="color: #000000;"> msg;
- }
- </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getDetail() {
- </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> detail;
- }
- </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setDetail(String detail) {
- </span><span style="color: #0000ff;">this</span>.detail =<span style="color: #000000;"> detail;
- }
- </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> T getData() {
- </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> data;
- }
- </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setData(T data) {
- </span><span style="color: #0000ff;">this</span>.data =<span style="color: #000000;"> data;
- }
- }</span>
Award(数据库表对应的实体类)
- <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> Award {
- </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String code;
- </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Double amount;
- </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> classify;
- </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getCode() {
- </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> code;
- }
- </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setCode(String code) {
- </span><span style="color: #0000ff;">this</span>.code =<span style="color: #000000;"> code;
- }
- </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Double getAmount() {
- </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> amount;
- }
- </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setAmount(Double amount) {
- </span><span style="color: #0000ff;">this</span>.amount =<span style="color: #000000;"> amount;
- }
- </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> getClassify() {
- </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> classify;
- }
- </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span> setClassify(<span style="color: #0000ff;">int</span><span style="color: #000000;"> classify) {
- </span><span style="color: #0000ff;">this</span>.classify =<span style="color: #000000;"> classify;
- }
- }</span>
【Java】【46】导入Excel到数据库
标签:描述 model continue substring 返回 book work 调用 orm