一、Excel导入数据库实例
注意：xlsx格式需导入xmlbeans包，否则会报错，相关jar下载地址：
1.后台controller实例代码：
/**
* 从EXCEL导入到数据库
*/
@RequestMapping(value="/readCardExcel")
public ModelAndView readCourseExcel(
@RequestParam(value="excel",required=false) MultipartFile file) throws Exception{
ModelAndView mv = this.getModelAndView();
PageData pd = new PageData();
int saveSum=0,updateSum=0,errorSum=0; //保存成功导入的数
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if (null != file && !file.isEmpty()) {
String filePath = PathUtil.getClasspath() + Const.FILEPATHFILE; //文件上传路径
String fileName = FileUpload.fileUp(file, filePath, "cardexcel"); //执行上传
List<PageData> listPd = (List)ObjectExcelRead.readCardExcel(filePath, fileName, 1, 0, 0); //执行读EXCEL操作,读出的数据导入List 1:从第2行开始；0:从第A列开始；0:第0个sheet
/**
* var0 :名称
* var1 :账款
* var2 :款率
* var3 :备注
*/
for(int i=0;i<listPd.size();i++){
String var0=listPd.get(i).getString("var0");
String var1=listPd.get(i).getString("var1");
String reg = "^[0-9]+(.[0-9]+)?$";
//账款不是数字类型，则跳过
if(!var1.matches(reg) && !(var1.contains("E") || var1.contains("e"))){
continue;
}
String var2=listPd.get(i).getString("var2");
String var3=listPd.get(i).getString("var3");
pd.put("name", var0);
pd.put("balance",var1);
pd.put("ratio",var2);
pd.put("updateTime",sdf.format(new Date()));
//查询名称是否已存在账款，存在则更新
PageData pdd=receivablesService.selectReceivables(pd);
if(pdd != null){
int updateResult=receivablesService.updateReceivables(pd);
updateSum=updateResult==1?updateSum+updateResult:updateResult+0;
errorSum=updateResult==1?errorSum+0:errorSum+1;
}else{
int saveResult=receivablesService.saveReceivables(pd);
saveSum=saveResult==1?saveSum+saveResult:saveSum+0;
errorSum=saveResult==1?errorSum+0:errorSum+1;
}
}
/*存入数据库操作======================================*/
mv.addObject("msg","保存成功 " +saveSum+ " 条,更新"+updateSum+"条,失败："+errorSum+"条");
}
mv.setViewName("company/saveReceivables_result");
return mv;
}
2.类FileUpload：
package com.credit.util;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import org.apache.commons.io.FileUtils;
import org.springframework.web.multipart.MultipartFile;
/**
* 上传文件13 * @version
*/
public class FileUpload {
/**
* @param file //文件对象
* @param filePath //上传路径
* @param fileName //文件名
* @return 文件名
*/
public static String fileUp(MultipartFile file, String filePath, String fileName){
String extName = ""; // 扩展名格式：
try {
if (file.getOriginalFilename().lastIndexOf(".") >= 0){
extName = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
}
copyFile(file.getInputStream(), filePath, fileName+extName).replaceAll("-", "");
} catch (IOException e) {
System.out.println(e);
}
return fileName+extName;
}
/**
* 写文件到当前目录的upload目录中
*
* @param in
* @param fileName
* @throws IOException
*/
private static String copyFile(InputStream in, String dir, String realName)
throws IOException {
File file = new File(dir, realName);
if (!file.exists()) {
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
file.createNewFile();
}
FileUtils.copyInputStreamToFile(in, file);
return realName;
}
}
3.读取Excel数据关键代码：readCardExcel
/**
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;
* 读取Excel表格内容----支持xlsx格式和xls格式
* @param filepath //文件路径
* @param filename //文件名
* @param startrow //开始行号
* @param startcol //开始列号
* @param sheetnum //sheet
* @return list
*/
public static List<Object> readCardExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
List<Object> varList = new ArrayList<Object>();
try {
File target = new File(filepath, filename);
FileInputStream fi = new FileInputStream(target);
String suffix=filename.substring(filename.lastIndexOf(".")+1); //获取文件后缀名
if("xlsx".equals(suffix)){ //.xlsx格式读取
XSSFWorkbook xwb = new XSSFWorkbook(fi); //利用poi读取excel文件流
XSSFSheet sheet = xwb.getSheetAt(sheetnum); //读取sheet的第一个工作表
//HSSFWorkbook wb = new HSSFWorkbook(fi);
//HSSFSheet sheet = wb.getSheetAt(sheetnum); //sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号
for (int i = startrow; i < rowNum; i++) { //行循环开始
PageData varpd = new PageData();
XSSFRow row = sheet.getRow(i); //行
int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置
for (int j = startcol; j < cellNum; j++) { //列循环开始
XSSFCell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
case 0:
DecimalFormat df = new DecimalFormat("0.0000");
cellValue = df.format(cell.getNumericCellValue());
// cellValue = String.valueOf(cell.getNumericCellValue());
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
varpd.put("var"+j, cellValue);
}
varList.add(varpd);
}
}else if("xls".equals(suffix)){ //.xls格式读取
// XSSFWorkbook xwb = new XSSFWorkbook(fi);
// XSSFSheet sheet = xwb.getSheetAt(sheetnum); //读取sheet的第一个工作表
HSSFWorkbook wb = new HSSFWorkbook(fi); //利用poi读取excel文件流
HSSFSheet sheet = wb.getSheetAt(sheetnum); //sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号
for (int i = startrow; i < rowNum; i++) { //行循环开始
PageData varpd = new PageData();
//XSSFRow row = sheet.getRow(i); //行
HSSFRow row = sheet.getRow(i); //行
int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置
for (int j = startcol; j < cellNum; j++) { //列循环开始
//XSSFCell cell = row.getCell(Short.parseShort(j + ""));
HSSFCell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
case 0:
DecimalFormat df = new DecimalFormat("0");
cellValue = df.format(cell.getNumericCellValue());
// cellValue = String.valueOf((int) cell.getNumericCellValue());
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
varpd.put("var"+j, cellValue);
}
varList.add(varpd);
}
}
} catch (Exception e) {
System.out.println(e);
}
return varList;
}
3.导出Excel实例
1.controller代码实例
/*
* 导出到excel
* @return
*/
@RequestMapping(value="/excel")
public ModelAndView exportExcel(){
logBefore(logger, "导出到excel");
ModelAndView mv = new ModelAndView();
PageData pd = new PageData();
pd = this.getPageData();
try{
Map<String,Object> dataMap = new HashMap<String,Object>();
List<String> titles = new ArrayList<String>();
titles.add("课程名称"); //1
titles.add("课程分类"); //2
titles.add("作用"); //3
titles.add("目标"); //4
titles.add("课程开始时间"); //5
titles.add("课程结束时间"); //6
titles.add("课程价格"); //7
titles.add("V卡价格"); //8
titles.add("是否结束"); //9
titles.add("课程满足人数"); //10
titles.add("课程已预约人数"); //11
titles.add("课程地点"); //12
titles.add("课程视频地址"); //13
titles.add("课程介绍"); //14
titles.add("课程图像"); //15
titles.add("课程注意事项"); //16
titles.add("课程群二维码地址"); //17
titles.add("备注"); //18
dataMap.put("titles", titles);
List<PageData> varOList = courseService.listAll(pd);
List<PageData> varList = new ArrayList<PageData>();
for(int i=0;i<varOList.size();i++){
PageData vpd = new PageData();
vpd.put("var1", varOList.get(i).getString("COURSENAME")); //1
vpd.put("var2", varOList.get(i).getString("CLASSIFY")); //2
vpd.put("var3", varOList.get(i).getString("EFFECT")); //3
vpd.put("var4", varOList.get(i).getString("TARGET")); //4
vpd.put("var5", varOList.get(i).getString("COURSETIMEB")); //5
vpd.put("var6", varOList.get(i).getString("COURSETIMEE")); //6
vpd.put("var7", varOList.get(i).getString("COURSEPRICE")); //7
vpd.put("var8", varOList.get(i).getString("VPRICE")); //8
vpd.put("var9", varOList.get(i).get("HASOVER").toString()); //9
vpd.put("var10", varOList.get(i).get("COURSEFULLNUMBER").toString()); //10
vpd.put("var11", varOList.get(i).get("COURSEORDERNUMBER").toString()); //11
vpd.put("var12", varOList.get(i).getString("COURSEADD")); //12
vpd.put("var13", varOList.get(i).getString("COURSEVIDEO")); //13
vpd.put("var14", varOList.get(i).getString("COURSEINTRO")); //14
vpd.put("var15", varOList.get(i).getString("COUSERPIC")); //15
vpd.put("var16", varOList.get(i).getString("COURSEATTENTION")); //16
vpd.put("var17", varOList.get(i).getString("COURSEQRCODE")); //17
vpd.put("var18", varOList.get(i).getString("REMARK")); //18
varList.add(vpd);
}
dataMap.put("varList", varList);
ObjectExcelView erv = new ObjectExcelView();
mv = new ModelAndView(erv,dataMap);
} catch(Exception e){
logger.error(e.toString(), e);
}
return mv;
}
2.类ObjectExcelView
package com.util;
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;
/**
* 导入到EXCEL
*/
public class ObjectExcelView extends AbstractExcelView{
@Override
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
// TODO Auto-generated method stub
Date date = new Date();
String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
HSSFSheet sheet;
HSSFCell cell;
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
sheet = workbook.createSheet("sheet1");
List<String> titles = (List<String>) model.get("titles");
int len = titles.size();
HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont headerFont = workbook.createFont(); //标题字体
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short)11);
headerStyle.setFont(headerFont);
short width = 20,height=25*20;
sheet.setDefaultColumnWidth(width);
for(int i=0; i<len; i++){ //设置标题
String title = titles.get(i);
cell = getCell(sheet, 0, i);
cell.setCellStyle(headerStyle);
setText(cell,title);
}
sheet.getRow(0).setHeight(height);
HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
List<PageData> varList = (List<PageData>) model.get("varList");
int varCount = varList.size();
for(int i=0; i<varCount; i++){
PageData vpd = varList.get(i);
for(int j=0;j<len;j++){
String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : "";
cell = getCell(sheet, i+1, j);
cell.setCellStyle(contentStyle);
setText(cell,varstr);
}
}
}
}
