工具类
package com.inxedu.os.common.util;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
public class ExcelTool {
public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
public static final String EMPTY = "";
public static final String POINT = ".";
public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
/**
* 获得path的后缀名
* @param path
* @return
*/
public static String getPostfix(String path){
if(path==null || EMPTY.equals(path.trim())){
return EMPTY;
}
if(path.contains(POINT)){
return path.substring(path.lastIndexOf(POINT)+1,path.length());
}
return EMPTY;
}
/**
* 单元格格式
* @param hssfCell
* @return
*/
@SuppressWarnings({ "static-access", "deprecation" })
public static String getHValue(HSSFCell hssfCell){
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
String cellValue = "";
if(HSSFDateUtil.isCellDateFormatted(hssfCell)){
Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());
cellValue = sdf.format(date);
}else{
DecimalFormat df = new DecimalFormat("#.##");
cellValue = df.format(hssfCell.getNumericCellValue());
String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());
if(strArr.equals("00")){
cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
}
}
return cellValue;
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
/**
* 单元格格式
* @param xssfCell
* @return
*/
public static String getXValue(XSSFCell xssfCell){
if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfCell.getBooleanCellValue());
} else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
String cellValue = "";
if(XSSFDateUtil.isCellDateFormatted(xssfCell)){
Date date = XSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue());
cellValue = sdf.format(date);
}else{
DecimalFormat df = new DecimalFormat("#.##");
cellValue = df.format(xssfCell.getNumericCellValue());
String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());
if(strArr.equals("00")){
cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
}
}
return cellValue;
} else {
return String.valueOf(xssfCell.getStringCellValue());
}
}
/**
* 自定义xssf日期工具类
* @author lp
*
*/
static class XSSFDateUtil extends DateUtil {
protected static int absoluteDay(Calendar cal, boolean use1904windowing) {
return DateUtil.absoluteDay(cal, use1904windowing);
}
}
}
工具类
package com.inxedu.os.common.util;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtils {
public static int totalRows; //sheet中总行数
public static int totalCells; //每一行总单元格数
/**
* read the Excel .xlsx,.xls
* @param file jsp中的上传文件
* @return
* @throws IOException
*/
public static List<ArrayList<String>> readExcel(File file) throws IOException {
if(file==null){
return null;
}else{
String postfix = ExcelTool.getPostfix(file.getName());
if(!ExcelTool.EMPTY.equals(postfix)){
if(ExcelTool.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
return readXls(file);
}else if(ExcelTool.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){
return readXlsx(file);
}else{
return null;
}
}
}
return null;
}
/**
* read the Excel 2010 .xlsx
* @param file
* @return
* @throws IOException
*/
@SuppressWarnings("deprecation")
public static List<ArrayList<String>> readXlsx(File file){
List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
// IO流读取文件
InputStream input = null;
XSSFWorkbook wb = null;
ArrayList<String> rowList = null;
try {
input = new FileInputStream(file);
// 创建文档
wb = new XSSFWorkbook(input);
//读取sheet(页)
for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
if(xssfSheet == null){
continue;
}
totalRows = xssfSheet.getLastRowNum();
//读取Row,从第二行开始
for(int rowNum = 0;rowNum <= totalRows;rowNum++){
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if(xssfRow!=null){
rowList = new ArrayList<String>();
totalCells = xssfRow.getLastCellNum();
//读取列,从第一列开始
for(int c=0;c<=totalCells+1;c++){
XSSFCell cell = xssfRow.getCell(c);
if(cell==null){
rowList.add(ExcelTool.EMPTY);
continue;
}
rowList.add(ExcelTool.getXValue(cell).trim());
}
list.add(rowList);
}
}
}
return list;
} catch (IOException e) {
e.printStackTrace();
} finally{
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/**
* read the Excel 2003-2007 .xls
* @param file
* @return
* @throws IOException
*/
public static List<ArrayList<String>> readXls(File file){
List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
// IO流读取文件
InputStream input = null;
HSSFWorkbook wb = null;
ArrayList<String> rowList = null;
try {
input = new FileInputStream(file);
// 创建文档
wb = new HSSFWorkbook(input);
//读取sheet(页)
for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
if(hssfSheet == null){
continue;
}
totalRows = hssfSheet.getLastRowNum();
//读取Row,从第二行开始
for(int rowNum = 0;rowNum <= totalRows;rowNum++){
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if(hssfRow!=null){
rowList = new ArrayList<String>();
totalCells = hssfRow.getLastCellNum();
//读取列,从第一列开始
for(short c=0;c<=totalCells+1;c++){
HSSFCell cell = hssfRow.getCell(c);
if(cell==null){
rowList.add(ExcelTool.EMPTY);
continue;
}
rowList.add(ExcelTool.getHValue(cell).trim());
}
list.add(rowList);
}
}
}
return list;
} catch (IOException e) {
e.printStackTrace();
} finally{
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
}
控制层
@RequestMapping(value = "o_import.do",method = RequestMethod.POST)
public String importXls(HttpServletRequest request, HttpServletResponse response,
@RequestParam(value = "file", required = false) MultipartFile file){
try {
String originalFilename = file.getOriginalFilename();
String ext = FileUploadUtils.getSuffix(originalFilename);
String filePath = getPath(request,ext);
File files = new File(CommonConstants.projectRootDir+filePath);
if(!files.getParentFile().exists()){
files.getParentFile().mkdirs();
}
//保存文件
file.transferTo(files);
//list为excel数据集合
List<ArrayList<String>> list = ExcelUtils.readExcel(files);
Article article=new Article();
ArticleContent articleContent=new ArticleContent();
for(int i=1;i<list.size();i++){
article.setTitle(list.get(i).get(0));
article.setSummary(list.get(i).get(1));
article.setSource(list.get(i).get(2));
article.setAuthor(list.get(i).get(3));
article.setType(2);
article.setClickNum(0);
article.setPraiseCount(0);
article.setPublishTime(new Date());
article.setCreateTime(new Date());
article.setSort(0);
int articleId = articleService.createArticle(article);
// 添加文章内容
articleContent.setArticleId(articleId);
articleContent.setContent(list.get(i).get(4));
articleService.addArticleContent(articleContent);
}
//删除文件
File deleteFile = new File(CommonConstants.projectRootDir+filePath);
if (deleteFile.exists() && deleteFile.isFile()
&& deleteFile.delete() == true) {
}
} catch (Exception e) {
e.printStackTrace();
return "导入失败";
}
return "导入成功";
}
private String getPath(HttpServletRequest request,String ext){
String filePath = "/images/upload/";
filePath+= CommonConstants.projectName;
filePath+="/"+ DateUtils.toString(new Date(), "yyyyMMdd")+"/"+System.currentTimeMillis()+"."+ext;
return filePath;
}
应该是这几个依赖但是下面删了也不行了,就留着了
到这用postman测试是可以添加数据到数据库了