当前位置:Gxlcms > 数据库问题 > 【Java】【46】导入Excel到数据库

【Java】【46】导入Excel到数据库

时间:2021-07-01 10:21:17 帮助过:25人阅读

业务场景:用户提供Excel表,在页面上点击“导入按钮”,系统读取Excel中的数据,存到对应的数据库

注:

1,目前仅提供导入简单的Excel表,没有合并单元格,只能读取单页sheet

2,方法里用到了具体业务场景的实体类,所以适用性不是很强,其他场景要用的话,还需要修改代码。但是也具有一定的参考性,所以先记录下来。我的后续思路是:在 工具类(ReadExcelUtil) 里根据导入的Excel表头确定要处理的字段名,然后拼成json格式的数据,传到Service层,再做处理。之后再做更新 //TODO

3,用的jar包:POI

正文:

html:

  1. <span style="color: #0000ff;"><</span><span style="color: #800000;">div</span><span style="color: #0000ff;">></span>
  2. <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>
  3. <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>
  4. <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>
  5. <span style="color: #0000ff;"></</span><span style="color: #800000;">div</span><span style="color: #0000ff;">></span>

JS:

  1. <span style="color: #0000ff;">function</span><span style="color: #000000;"> importExp() {
  2. </span><span style="color: #0000ff;">var</span> name = $("#upfile"<span style="color: #000000;">).val();
  3. </span><span style="color: #0000ff;">var</span> file = $("#upfile")[0].files[0<span style="color: #000000;">];
  4. </span><span style="color: #008000;">//</span><span style="color: #008000;"> ajax...</span>
  5. }

Java:

Controller层

  1. @ApiOperation(value = "导入Excel"<span style="color: #000000;">)
  2. @RequestMapping(value</span>="excel/import", method =<span style="color: #000000;"> RequestMethod.POST)
  3. </span><span style="color: #0000ff;">public</span> void<span style="color: #000000;"> importExcel(MultipartFile file) {
  4. </span><span style="color: #0000ff;">return</span> <span style="color: #0000ff;">this</span><span style="color: #000000;">.theService.importExcel(file);
  5. }</span>

Service层

  1. <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> importExcel(MultipartFile file) {
  2. </span><span style="color: #0000ff;">if</span><span style="color: #000000;">(file.isEmpty()){
  3. </span><span style="color: #008000;">//</span><span style="color: #008000;">请先选择Excel文件。是否把报错等信息返回到前端,视情况而定。</span>
  4. <span style="color: #0000ff;">return</span><span style="color: #000000;">;
  5. }
  6. Result readResult </span>= ReadExcelUtil.readExcel(file); <span style="color: #008000;">//</span><span style="color: #008000;">Result是封装了返回值的类,相当于实体类</span>
  7. <span style="color: #0000ff;">if</span>(readResult.getCode() != 0<span style="color: #000000;">){
  8. </span><span style="color: #008000;">//</span><span style="color: #008000;">错误:readResult.msg</span>
  9. <span style="color: #0000ff;">return</span><span style="color: #000000;">;
  10. }
  11. List</span><Award> list = (List<Award>) readResult.getData(); <span style="color: #008000;">//</span><span style="color: #008000;">Award是数据库表对应的实体类
  12. </span><span style="color: #008000;">//</span><span style="color: #008000;">获取了list之后,insert到数据库... </span>
  13. }

工具类:

ReadExcelUtil

  1. <span style="color: #0000ff;">package</span><span style="color: #000000;"> com.bf.base.utils;
  2. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> com.bf.base.entity.DripAward;
  3. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> com.bf.base.params.Result;
  4. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.hssf.usermodel.HSSFWorkbook;
  5. </span><span style="color: #0000ff;">import</span> org.apache.poi.ss.usermodel.*<span style="color: #000000;">;
  6. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.xssf.usermodel.XSSFWorkbook;
  7. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.web.multipart.MultipartFile;
  8. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.FileNotFoundException;
  9. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.IOException;
  10. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.InputStream;
  11. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.text.DecimalFormat;
  12. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.util.ArrayList;
  13. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.util.Calendar;
  14. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.util.List;
  15. </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> ReadExcelUtil {
  16. </span><span style="color: #008000;">/**</span><span style="color: #008000;">
  17. * 读取 单页sheet,返回一个集合
  18. * </span><span style="color: #808080;">@return</span>
  19. <span style="color: #008000;">*/</span>
  20. <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> Result<?><span style="color: #000000;"> readDripAwardExcel(MultipartFile file) {
  21. Result result </span>= <span style="color: #0000ff;">new</span> Result<><span style="color: #000000;">();
  22. InputStream is </span>= <span style="color: #0000ff;">null</span><span style="color: #000000;">;
  23. Workbook wb </span>= <span style="color: #0000ff;">null</span><span style="color: #000000;">;
  24. String fileName </span>=<span style="color: #000000;"> file.getOriginalFilename();
  25. String fileType </span>= fileName.substring(fileName.lastIndexOf(".") + 1<span style="color: #000000;">, fileName.length());
  26. </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
  27. is </span>=<span style="color: #000000;"> file.getInputStream();
  28. </span><span style="color: #0000ff;">if</span> (fileType.equals("xls"<span style="color: #000000;">)) {
  29. wb </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> HSSFWorkbook(is);
  30. } </span><span style="color: #0000ff;">else</span> <span style="color: #0000ff;">if</span> (fileType.equals("xlsx"<span style="color: #000000;">)) {
  31. wb </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> XSSFWorkbook(is);
  32. } </span><span style="color: #0000ff;">else</span><span style="color: #000000;"> {
  33. </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;">);
  34. }
  35. </span><span style="color: #0000ff;">int</span> sheetSize = wb.getNumberOfSheets();<span style="color: #008000;">//</span><span style="color: #008000;">有多少sheet页</span>
  36. <span style="color: #0000ff;">if</span>(sheetSize >= 2<span style="color: #000000;">){
  37. </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;">);
  38. }
  39. Sheet sheet </span>= wb.getSheetAt(0<span style="color: #000000;">);
  40. result </span>= sheetData2List(sheet); <span style="color: #008000;">//</span><span style="color: #008000;">关键,sheet表数据 转 集合</span>
  41. <span style="color: #000000;">
  42. } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (FileNotFoundException e) {
  43. e.printStackTrace();
  44. } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (IOException e) {
  45. e.printStackTrace();
  46. } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
  47. e.printStackTrace();
  48. }
  49. </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> result;
  50. }
  51. </span><span style="color: #008000;">//</span><span style="color: #008000;">获取数据</span>
  52. <span style="color: #0000ff;">private</span> <span style="color: #0000ff;">static</span><span style="color: #000000;"> Result sheetData2List(Sheet sheet) {
  53. Result result </span>= <span style="color: #0000ff;">new</span> Result<><span style="color: #000000;">();
  54. List</span><Award> awards = <span style="color: #0000ff;">new</span> ArrayList<><span style="color: #000000;">();
  55. </span><span style="color: #0000ff;">int</span> CELL_SIZE = 3; <span style="color: #008000;">//</span><span style="color: #008000;">excel固定三列(编码、金额) </span>
  56. <span style="color: #0000ff;">int</span> rowSize = sheet.getLastRowNum() + 1<span style="color: #000000;">;
  57. </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>
  58. Row row =<span style="color: #000000;"> sheet.getRow(j);
  59. </span><span style="color: #0000ff;">if</span> (row == <span style="color: #0000ff;">null</span><span style="color: #000000;">) {
  60. </span><span style="color: #0000ff;">continue</span><span style="color: #000000;">;
  61. }
  62. </span><span style="color: #0000ff;">if</span>(row.getLastCellNum() !=<span style="color: #000000;"> CELL_SIZE){
  63. result.setCode(</span>2<span style="color: #000000;">);
  64. result.setMsg(</span>"第"+ j +"行数据异常,请核对后再上传!"<span style="color: #000000;">);
  65. </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> result;
  66. }
  67. </span><span style="color: #0000ff;">if</span> (j == 0<span style="color: #000000;">) {
  68. </span><span style="color: #0000ff;">continue</span><span style="color: #000000;">;
  69. } </span><span style="color: #0000ff;">else</span><span style="color: #000000;"> {
  70. DripAward rowObj </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> DripAward();
  71. </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>
  72. Cell cell =<span style="color: #000000;"> row.getCell(k);
  73. String value </span>=<span style="color: #000000;"> getCellFormatValue(cell);
  74. </span><span style="color: #0000ff;">switch</span><span style="color: #000000;"> (k) {
  75. </span><span style="color: #0000ff;">case</span> 0<span style="color: #000000;">:
  76. rowObj.setCode(AESUtil.encrypt(value));
  77. </span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
  78. </span><span style="color: #0000ff;">case</span> 1<span style="color: #000000;">:
  79. rowObj.setAmount(Double.parseDouble(value));
  80. </span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
  81. </span><span style="color: #0000ff;">case</span> 2<span style="color: #000000;">:
  82. rowObj.setClassify(Integer.parseInt(value));
  83. </span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
  84. </span><span style="color: #0000ff;">default</span><span style="color: #000000;">:
  85. </span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
  86. }
  87. }
  88. awards.add(rowObj);
  89. }
  90. }
  91. result.setCode(</span>0<span style="color: #000000;">);
  92. result.setData(awards);
  93. </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> result;
  94. }
  95. </span><span style="color: #008000;">//</span><span style="color: #008000;">获取列的数据</span>
  96. <span style="color: #0000ff;">private</span> <span style="color: #0000ff;">static</span><span style="color: #000000;"> String getCellFormatValue(Cell cell) {
  97. String cellvalue </span>= ""<span style="color: #000000;">;
  98. </span><span style="color: #0000ff;">if</span> (cell != <span style="color: #0000ff;">null</span><span style="color: #000000;">) {
  99. CellType cellType </span>=<span style="color: #000000;"> cell.getCellTypeEnum();
  100. </span><span style="color: #0000ff;">switch</span><span style="color: #000000;"> (cellType) {
  101. </span><span style="color: #0000ff;">case</span><span style="color: #000000;"> NUMERIC: {
  102. </span><span style="color: #0000ff;">if</span>(String.valueOf(cell.getNumericCellValue()).indexOf("E") == -1<span style="color: #000000;">){
  103. cellvalue </span>=<span style="color: #000000;"> String.valueOf(cell.getNumericCellValue());
  104. }</span><span style="color: #0000ff;">else</span><span style="color: #000000;"> {
  105. cellvalue </span>= <span style="color: #0000ff;">new</span> DecimalFormat("#"<span style="color: #000000;">).format(cell.getNumericCellValue());
  106. }
  107. </span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
  108. }
  109. </span><span style="color: #0000ff;">case</span><span style="color: #000000;"> STRING:
  110. cellvalue </span>=<span style="color: #000000;"> cell.getRichStringCellValue().getString();
  111. </span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
  112. </span><span style="color: #0000ff;">default</span><span style="color: #000000;">:
  113. cellvalue </span>= "-"<span style="color: #000000;">;
  114. }
  115. }
  116. </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> cellvalue;
  117. }
  118. }</span>

其他:

Result (封装了返回值的类,相当于实体类)

  1. <span style="color: #0000ff;">package</span><span style="color: #000000;"> com.bf.base.params;
  2. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.commons.lang.StringUtils;
  3. </span><span style="color: #008000;">/**</span><span style="color: #008000;">
  4. * 传给前端数据的统一格式
  5. * code = 0时,表示调用成功
  6. * 其余code均表示调用接口异常,异常时,标明异常码,并给出msg和detail注释,同步文档
  7. </span><span style="color: #008000;">*/</span>
  8. <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span> Result<T><span style="color: #000000;"> {
  9. </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span> code;<span style="color: #008000;">//</span><span style="color: #008000;">状态返回码</span>
  10. <span style="color: #0000ff;">private</span> String msg;<span style="color: #008000;">//</span><span style="color: #008000;">返回码描述</span>
  11. <span style="color: #0000ff;">private</span> String detail;<span style="color: #008000;">//</span><span style="color: #008000;">错误详细描述或返回码对应处理方案</span>
  12. <span style="color: #0000ff;">private</span> T data;<span style="color: #008000;">//</span><span style="color: #008000;">返回的主体数据</span>
  13. <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;">;
  14. </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;">;
  15. @Override
  16. </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String toString() {
  17. </span><span style="color: #0000ff;">return</span> "Result [code=" + code + ", msg=" + msg + ", detail=" + detail + ", data=" + data + "]"<span style="color: #000000;">;
  18. }
  19. </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> getCode() {
  20. </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> code;
  21. }
  22. </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) {
  23. </span><span style="color: #0000ff;">this</span>.code =<span style="color: #000000;"> code;
  24. }
  25. </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getMsg() {
  26. </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> msg;
  27. }
  28. </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setMsg(String msg) {
  29. </span><span style="color: #0000ff;">this</span>.msg =<span style="color: #000000;"> msg;
  30. }
  31. </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getDetail() {
  32. </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> detail;
  33. }
  34. </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setDetail(String detail) {
  35. </span><span style="color: #0000ff;">this</span>.detail =<span style="color: #000000;"> detail;
  36. }
  37. </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> T getData() {
  38. </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> data;
  39. }
  40. </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setData(T data) {
  41. </span><span style="color: #0000ff;">this</span>.data =<span style="color: #000000;"> data;
  42. }
  43. }</span>

Award(数据库表对应的实体类)

  1. <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> Award {
  2. </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String code;
  3. </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Double amount;
  4. </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> classify;
  5. </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getCode() {
  6. </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> code;
  7. }
  8. </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setCode(String code) {
  9. </span><span style="color: #0000ff;">this</span>.code =<span style="color: #000000;"> code;
  10. }
  11. </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Double getAmount() {
  12. </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> amount;
  13. }
  14. </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setAmount(Double amount) {
  15. </span><span style="color: #0000ff;">this</span>.amount =<span style="color: #000000;"> amount;
  16. }
  17. </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> getClassify() {
  18. </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> classify;
  19. }
  20. </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) {
  21. </span><span style="color: #0000ff;">this</span>.classify =<span style="color: #000000;"> classify;
  22. }
  23. }</span>

 

【Java】【46】导入Excel到数据库

标签:描述   model   continue   substring   返回   book   work   调用   orm   

人气教程排行