POI_Excel表格数据导入导出实例--支持xls/xlsx格式
一、Excel导入数据库实例
注意：xlsx格式需导入xmlbeans包，否则会报错，相关jar下载地址：
1.后台controller实例代码：
1 /** 2 * 从EXCEL导入到数据库 3 */ 4 @RequestMapping(value="/readCardExcel") 5 public ModelAndView readCourseExcel( 6 @RequestParam(value="excel",required=false) MultipartFile file 7 ) throws Exception{ 8 ModelAndView mv = this.getModelAndView(); 9 PageData pd = new PageData(); 10 int saveSum=0,updateSum=0,errorSum=0; //保存成功导入的数 11 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 12 if (null != file && !file.isEmpty()) { 13 String filePath = PathUtil.getClasspath() + Const.FILEPATHFILE; //文件上传路径 14 String fileName = FileUpload.fileUp(file, filePath, "cardexcel"); //执行上传 15 16 List<PageData> listPd = (List)ObjectExcelRead.readCardExcel(filePath, fileName, 1, 0, 0); //执行读EXCEL操作,读出的数据导入List 1:从第2行开始；0:从第A列开始；0:第0个sheet 17 18 /** 19 * var0 :名称 20 * var1 :账款 21 * var2 :款率 22 * var3 :备注 23 */ 24 for(int i=0;i<listPd.size();i++){ 25 String var0=listPd.get(i).getString("var0"); 26 String var1=listPd.get(i).getString("var1"); 27 String reg = "^[0-9]+(.[0-9]+)?$"; 28 //账款不是数字类型，则跳过 29 if(!var1.matches(reg) && !(var1.contains("E") || var1.contains("e"))){ 30 continue; 31 } 32 String var2=listPd.get(i).getString("var2"); 33 String var3=listPd.get(i).getString("var3"); 34 pd.put("name", var0); 35 pd.put("balance",var1); 36 pd.put("ratio",var2); 37 pd.put("updateTime",sdf.format(new Date())); 38 //查询名称是否已存在账款，存在则更新 39 PageData pdd=receivablesService.selectReceivables(pd); 40 if(pdd != null){ 41 int updateResult=receivablesService.updateReceivables(pd); 42 updateSum=updateResult==1?updateSum+updateResult:updateResult+0; 43 errorSum=updateResult==1?errorSum+0:errorSum+1; 44 }else{ 45 int saveResult=receivablesService.saveReceivables(pd); 46 saveSum=saveResult==1?saveSum+saveResult:saveSum+0; 47 errorSum=saveResult==1?errorSum+0:errorSum+1; 48 } 49 50 } 51 /*存入数据库操作======================================*/ 52 53 mv.addObject("msg","保存成功 " +saveSum+ " 条,更新"+updateSum+"条,失败："+errorSum+"条"); 54 } 55 56 mv.setViewName("company/saveReceivables_result"); 57 return mv; 58 }
2.类FileUpload：
1 package com.credit.util; 2 3 import java.io.File; 4 import java.io.IOException; 5 import java.io.InputStream; 6 7 import org.apache.commons.io.FileUtils; 8 import org.springframework.web.multipart.MultipartFile; 9 10 /** 11 * 上传文件13 * @version 14 */ 15 public class FileUpload { 16 17 /** 18 * @param file //文件对象 19 * @param filePath //上传路径 20 * @param fileName //文件名 21 * @return 文件名 22 */ 23 public static String fileUp(MultipartFile file, String filePath, String fileName){ 24 String extName = ""; // 扩展名格式： 25 try { 26 if (file.getOriginalFilename().lastIndexOf(".") >= 0){ 27 extName = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".")); 28 } 29 copyFile(file.getInputStream(), filePath, fileName+extName).replaceAll("-", ""); 30 } catch (IOException e) { 31 System.out.println(e); 32 } 33 return fileName+extName; 34 } 35 36 /** 37 * 写文件到当前目录的upload目录中 38 * 39 * @param in 40 * @param fileName 41 * @throws IOException 42 */ 43 private static String copyFile(InputStream in, String dir, String realName) 44 throws IOException { 45 File file = new File(dir, realName); 46 if (!file.exists()) { 47 if (!file.getParentFile().exists()) { 48 file.getParentFile().mkdirs(); 49 } 50 file.createNewFile(); 51 } 52 FileUtils.copyInputStreamToFile(in, file); 53 return realName; 54 } 55 }
3.读取Excel数据关键代码：readCardExcel
1 /**
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;
import com.util.PageData;
import com.util.Tools;
2 * 读取Excel表格内容----支持xlsx格式和xls格式 3 * @param filepath //文件路径 4 * @param filename //文件名 5 * @param startrow //开始行号 6 * @param startcol //开始列号 7 * @param sheetnum //sheet 8 * @return list 9 */ 10 public static List<Object> readCardExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) { 11 List<Object> varList = new ArrayList<Object>(); 12 13 try { 14 File target = new File(filepath, filename); 15 FileInputStream fi = new FileInputStream(target); 16 String suffix=filename.substring(filename.lastIndexOf(".")+1); //获取文件后缀名 17 if("xlsx".equals(suffix)){ //.xlsx格式读取 18 XSSFWorkbook xwb = new XSSFWorkbook(fi); //利用poi读取excel文件流 19 XSSFSheet sheet = xwb.getSheetAt(sheetnum); //读取sheet的第一个工作表 20 // HSSFWorkbook wb = new HSSFWorkbook(fi); 21 // HSSFSheet sheet = wb.getSheetAt(sheetnum); //sheet 从0开始 22 int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号 23 24 for (int i = startrow; i < rowNum; i++) { //行循环开始 25 26 PageData varpd = new PageData(); 27 XSSFRow row = sheet.getRow(i); //行 28 int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置 29 30 for (int j = startcol; j < cellNum; j++) { //列循环开始 31 32 XSSFCell cell = row.getCell(Short.parseShort(j + "")); 33 String cellValue = null; 34 if (null != cell) { 35 switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库 36 case 0: 37 DecimalFormat df = new DecimalFormat("0.0000"); 38 cellValue = df.format(cell.getNumericCellValue()); 39 // cellValue = String.valueOf(cell.getNumericCellValue()); 40 break; 41 case 1: 42 cellValue = cell.getStringCellValue(); 43 break; 44 case 2: 45 cellValue = cell.getNumericCellValue() + ""; 46 break; 47 case 3: 48 cellValue = ""; 49 break; 50 case 4: 51 cellValue = String.valueOf(cell.getBooleanCellValue()); 52 break; 53 case 5: 54 cellValue = String.valueOf(cell.getErrorCellValue()); 55 break; 56 } 57 } else { 58 cellValue = ""; 59 } 60 61 varpd.put("var"+j, cellValue); 62 63 } 64 varList.add(varpd); 65 } 66 }else if("xls".equals(suffix)){ //.xls格式读取 67 // XSSFWorkbook xwb = new XSSFWorkbook(fi); 68 // XSSFSheet sheet = xwb.getSheetAt(sheetnum); //读取sheet的第一个工作表 69 HSSFWorkbook wb = new HSSFWorkbook(fi); //利用poi读取excel文件流 70 HSSFSheet sheet = wb.getSheetAt(sheetnum); //sheet 从0开始 71 int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号 72 73 for (int i = startrow; i < rowNum; i++) { //行循环开始 74 75 PageData varpd = new PageData(); 76 //XSSFRow row = sheet.getRow(i); //行 77 HSSFRow row = sheet.getRow(i); //行 78 int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置 79 80 for (int j = startcol; j < cellNum; j++) { //列循环开始 81 82 //XSSFCell cell = row.getCell(Short.parseShort(j + "")); 83 HSSFCell cell = row.getCell(Short.parseShort(j + "")); 84 String cellValue = null; 85 if (null != cell) { 86 switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库 87 case 0: 88 DecimalFormat df = new DecimalFormat("0"); 89 cellValue = df.format(cell.getNumericCellValue()); 90 // cellValue = String.valueOf((int) cell.getNumericCellValue()); 91 break; 92 case 1: 93 cellValue = cell.getStringCellValue(); 94 break; 95 case 2: 96 cellValue = cell.getNumericCellValue() + ""; 97 break; 98 case 3: 99 cellValue = ""; 100 break; 101 case 4: 102 cellValue = String.valueOf(cell.getBooleanCellValue()); 103 break; 104 case 5: 105 cellValue = String.valueOf(cell.getErrorCellValue()); 106 break; 107 } 108 } else { 109 cellValue = ""; 110 } 111 112 varpd.put("var"+j, cellValue); 113 114 } 115 varList.add(varpd); 116 } 117 } 118 119 120 } catch (Exception e) { 121 System.out.println(e); 122 } 123 124 return varList; 125 }
3.导出Excel实例
1.controller代码实例
1 /* 2 * 导出到excel 3 * @return 4 */ 5 @RequestMapping(value="/excel") 6 public ModelAndView exportExcel(){ 7 logBefore(logger, "导出到excel"); 8 ModelAndView mv = new ModelAndView(); 9 PageData pd = new PageData(); 10 pd = this.getPageData(); 11 try{ 12 Map<String,Object> dataMap = new HashMap<String,Object>(); 13 List<String> titles = new ArrayList<String>(); 14 titles.add("课程名称"); //1 15 titles.add("课程分类"); //2 16 titles.add("作用"); //3 17 titles.add("目标"); //4 18 titles.add("课程开始时间"); //5 19 titles.add("课程结束时间"); //6 20 titles.add("课程价格"); //7 21 titles.add("V卡价格"); //8 22 titles.add("是否结束"); //9 23 titles.add("课程满足人数"); //10 24 titles.add("课程已预约人数"); //11 25 titles.add("课程地点"); //12 26 titles.add("课程视频地址"); //13 27 titles.add("课程介绍"); //14 28 titles.add("课程图像"); //15 29 titles.add("课程注意事项"); //16 30 titles.add("课程群二维码地址"); //17 31 titles.add("备注"); //18 32 dataMap.put("titles", titles); 33 List<PageData> varOList = courseService.listAll(pd); 34 List<PageData> varList = new ArrayList<PageData>(); 35 for(int i=0;i<varOList.size();i++){ 36 PageData vpd = new PageData(); 37 vpd.put("var1", varOList.get(i).getString("COURSENAME")); //1 38 vpd.put("var2", varOList.get(i).getString("CLASSIFY")); //2 39 vpd.put("var3", varOList.get(i).getString("EFFECT")); //3 40 vpd.put("var4", varOList.get(i).getString("TARGET")); //4 41 vpd.put("var5", varOList.get(i).getString("COURSETIMEB")); //5 42 vpd.put("var6", varOList.get(i).getString("COURSETIMEE")); //6 43 vpd.put("var7", varOList.get(i).getString("COURSEPRICE")); //7 44 vpd.put("var8", varOList.get(i).getString("VPRICE")); //8 45 vpd.put("var9", varOList.get(i).get("HASOVER").toString()); //9 46 vpd.put("var10", varOList.get(i).get("COURSEFULLNUMBER").toString()); //10 47 vpd.put("var11", varOList.get(i).get("COURSEORDERNUMBER").toString()); //11 48 vpd.put("var12", varOList.get(i).getString("COURSEADD")); //12 49 vpd.put("var13", varOList.get(i).getString("COURSEVIDEO")); //13 50 vpd.put("var14", varOList.get(i).getString("COURSEINTRO")); //14 51 vpd.put("var15", varOList.get(i).getString("COUSERPIC")); //15 52 vpd.put("var16", varOList.get(i).getString("COURSEATTENTION")); //16 53 vpd.put("var17", varOList.get(i).getString("COURSEQRCODE")); //17 54 vpd.put("var18", varOList.get(i).getString("REMARK")); //18 55 varList.add(vpd); 56 } 57 dataMap.put("varList", varList); 58 ObjectExcelView erv = new ObjectExcelView(); 59 mv = new ModelAndView(erv,dataMap); 60 } catch(Exception e){ 61 logger.error(e.toString(), e); 62 } 63 return mv; 64 }
2.类ObjectExcelView
1 package com.util; 2 3 import java.util.Date; 4 import java.util.List; 5 import java.util.Map; 6 7 import javax.servlet.http.HttpServletRequest; 8 import javax.servlet.http.HttpServletResponse; 9 10 import org.apache.poi.hssf.usermodel.HSSFCell; 11 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 12 import org.apache.poi.hssf.usermodel.HSSFFont; 13 import org.apache.poi.hssf.usermodel.HSSFSheet; 14 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 15 import org.springframework.web.servlet.view.document.AbstractExcelView; 16 17 import com.util.PageData; 18 import com.util.Tools; 19 /** 20 * 导入到EXCEL 21 * 类名称：ObjectExcelView.java 22 * 类描述： 23 * @author capv 24 * 作者单位： 25 * 联系方式： 26 * @version 1.0 27 */ 28 public class ObjectExcelView extends AbstractExcelView{ 29 30 @Override 31 protected void buildExcelDocument(Map<String, Object> model, 32 HSSFWorkbook workbook, HttpServletRequest request, 33 HttpServletResponse response) throws Exception { 34 // TODO Auto-generated method stub 35 Date date = new Date(); 36 String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); 37 HSSFSheet sheet; 38 HSSFCell cell; 39 response.setContentType("application/octet-stream"); 40 response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls"); 41 sheet = workbook.createSheet("sheet1"); 42 43 List<String> titles = (List<String>) model.get("titles"); 44 int len = titles.size(); 45 HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式 46 headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 47 headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 48 HSSFFont headerFont = workbook.createFont(); //标题字体 49 headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 50 headerFont.setFontHeightInPoints((short)11); 51 headerStyle.setFont(headerFont); 52 short width = 20,height=25*20; 53 sheet.setDefaultColumnWidth(width); 54 for(int i=0; i<len; i++){ //设置标题 55 String title = titles.get(i); 56 cell = getCell(sheet, 0, i); 57 cell.setCellStyle(headerStyle); 58 setText(cell,title); 59 } 60 sheet.getRow(0).setHeight(height); 61 62 HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式 63 contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 64 List<PageData> varList = (List<PageData>) model.get("varList"); 65 int varCount = varList.size(); 66 for(int i=0; i<varCount; i++){ 67 PageData vpd = varList.get(i); 68 for(int j=0;j<len;j++){ 69 String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : ""; 70 cell = getCell(sheet, i+1, j); 71 cell.setCellStyle(contentStyle); 72 setText(cell,varstr); 73 } 74 75 } 76 77 } 78 79 }
