controller
package com.land.controller.back;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Date;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import com.land.pojo.News;
import com.land.service.back.NewsServiceBack;
import com.land.utils.ExcelUtils;
import com.land.utils.JsonUtils;
@Controller
@RequestMapping(value = "/back_news", produces = "application/json;charset=UTF-8")
public class BackNewsController {
@Autowired
private NewsServiceBack newsService;
static final String rootPath="e://";
@RequestMapping(value = "/insertNews.action", produces = "application/json;charset=UTF-8")
@ResponseBody
public String insertNews(News news) {
String retvalue = "";
try {
int n = this.newsService.insertNews(news);
retvalue = JsonUtils.objectToJson(n);
} catch (Exception e) {
retvalue = JsonUtils.createJson("0", "false");
}
return retvalue;
}
@RequestMapping(value = "/deleNews.action", produces = "application/json;charset=UTF-8")
@ResponseBody
public String deleNews(long id) {
String retvalue = "";
try {
int n = this.newsService.deleNews(id);
retvalue = JsonUtils.objectToJson(n);
} catch (Exception e) {
retvalue = JsonUtils.createJson("0", "false");
}
return retvalue;
}
@RequestMapping(value = "/updateNews.action", produces = "application/json;charset=UTF-8")
@ResponseBody
public String updateNews(News news) {
String retvalue = "";
try {
int n = this.newsService.updateNews(news);
retvalue = JsonUtils.objectToJson(n);
} catch (Exception e) {
retvalue = JsonUtils.createJson("0", "false");
}
return retvalue;
}
/**
* 进入查询页面
*
* @return
*/
@RequestMapping(value = "/listUI.action")
public String listUI() {
return "/news/list";
}
/**
* 获取列表数据并显示
*
* @return
*/
@RequestMapping(value = "/getNewsList.action", produces = "application/json;charset=UTF-8")
@ResponseBody
public String getRoleList() {
List<News> list = newsService.selectNews();
return JsonUtils.objectToJson(list);
}
@RequestMapping(value ="/upload.action")
public void upload(News news,MultipartFile upfile,HttpSession session,HttpServletRequest request) throws Exception{
String fileName = upfile.getOriginalFilename();
File f = new File(rootPath, fileName);
session.setAttribute("fileName", fileName);
CommonsMultipartFile cf= (CommonsMultipartFile)upfile;
DiskFileItem fi = (DiskFileItem)cf.getFileItem();
File file= fi.getStoreLocation();
importNews(f);
/* InputStream in =null;
try {
upfile.transferTo(f);
} catch (IllegalStateException e) {
e.printStackTrace();
in.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
in.close();
}*/
}
public void importNews(File upfile) throws Exception {
String uploadFileName = upfile.getName();
int index = uploadFileName.lastIndexOf(".");
String fileEnd = null;
if(index <= -1 || index >= uploadFileName.length()){
fileEnd = "xls";
}else{
fileEnd = uploadFileName.substring(index+1).toLowerCase();
if(fileEnd == null || fileEnd.equals("") || (!fileEnd.equals("xls") && !fileEnd.equals("xlsx"))){
fileEnd = "xls";
}
}
Workbook wb = null;
if(fileEnd.equals("xlsx")){ //2007版本之后的excel
wb = new XSSFWorkbook(new FileInputStream(upfile));
}else{ //2003版本的excel
wb = new HSSFWorkbook(new FileInputStream(upfile));
}
Sheet sheet = wb.getSheetAt(0); //读取第一个文档
int rowMaxNum = sheet.getLastRowNum();
boolean error = false;
boolean repeat = false;
String errorRowNum = "";
for(int i = 2;i < rowMaxNum+1;i++){
Row row = sheet.getRow(i);
News news = new News();
try{
//下面是对空行进行判断
int columnNum=row.getPhysicalNumberOfCells();//获取总列数
int num = 0;
for(int j = 0 ; j< columnNum ;j++){
String value = ExcelUtils.getStringCellValue(row.getCell(j));
if(value == null || value.length() <= 0 ){
num++;
}
}
if(columnNum<= num){
continue;
}
//news.setId(Long.valueOf(ExcelUtils.getStringCellValue(row.getCell(0))));
news.setTitle(ExcelUtils.getStringCellValue(row.getCell(0)));
news.setContent(ExcelUtils.getStringCellValue(row.getCell(1)));
news.setCreateTime(ExcelUtils.getStringCellValue(row.getCell(2)));
news.setCreator(ExcelUtils.getStringCellValue(row.getCell(3)));
news.setIsLocked(Boolean.valueOf(ExcelUtils.getStringCellValue(row.getCell(4))));
news.setIsHot(Boolean.valueOf(ExcelUtils.getStringCellValue(row.getCell(5))));
news.setLastModifiedtime(ExcelUtils.getStringCellValue(row.getCell(6)));
System.out.println(row.getCell(7)+"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk");
news.setLastModifier(Long.valueOf(ExcelUtils.getStringCellValue(row.getCell(7))));
news.setBroweseCount(Integer.valueOf(ExcelUtils.getStringCellValue(row.getCell(8))));
news.setImage(ExcelUtils.getStringCellValue(row.getCell(9)));
System.err.println("BroweseCount: "+ExcelUtils.getStringCellValue(row.getCell(8)));
System.err.println("creator: "+ExcelUtils.getStringCellValue(row.getCell(3)));
System.err.println("Image: "+ExcelUtils.getStringCellValue(row.getCell(9)));
System.err.println("isHot: "+ExcelUtils.getStringCellValue(row.getCell(5)));
this.newsService.addNews(news);
}catch (Exception e) {
e.printStackTrace();
error = true;
errorRowNum += "第"+(i+1)+"行,";
continue;
}
}
}
}
ExcelUtils
package com.land.utils;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
public class ExcelUtils {
/**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
public static String getStringCellValue(Cell cell) {
String strCell = "";
if(null == cell){
return "";
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
strCell = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
}else {
double doubleVal = cell.getNumericCellValue();
strCell = String.valueOf(cell.getNumericCellValue());
long longVal = Math.round(doubleVal);
if(Double.parseDouble(longVal + ".0") == doubleVal)
strCell = longVal + "";
else
strCell = doubleVal + "";
}
break;
case Cell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
return strCell.trim();
}
/**
* 设置下选
* @param sheet
* @param textlist
* @param firstRow
* @param endRow
* @param firstCol
* @param endCol
* @return
*/
public static void setHSSFValidation(Sheet sheet,
String[] textlist, int firstRow, int endRow, int firstCol,
int endCol) {
// 加载下拉列表内容
DVConstraint constraint = DVConstraint
.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow,
endRow, firstCol, endCol);
// 数据有效性对象
HSSFDataValidation data_validation_list = new HSSFDataValidation(
regions, constraint);
sheet.addValidationData(data_validation_list);
}
/**
* 导入数据中的区域校验（空值校验和区域范围校验）
* @param areas
* @param area
* @param error
* @return
*/
public static boolean validateArea(List<String> areaNames,String curName,StringBuffer error){
if(curName==null||(curName=curName.trim()).equals("")){
error.append("单位不能为空");
return false;
}else{
if(areaNames.contains(curName)){
return true;
}
error.append("单位名称不合适");
return false;
}
}
public static void compareSETimes(String palnTimeS,String planTimeE,StringBuffer error,Cell cell1,Cell cell2) throws Exception{
if(palnTimeS.compareTo(planTimeE)>0){
error.append("计划开工时间早于了计划完工时间，不合适");
ExcelUtils.addCellErrorStyle(cell1);
ExcelUtils.addCellErrorStyle(cell2);
throw new Exception(error.toString());
}
}
/**
* 给错误的单元格加错误提示
* @param cell
*/
public static void addCellErrorStyle(Cell cell){
CellStyle style = cell.getRow().getSheet().getWorkbook().createCellStyle();
style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cell.setCellStyle(style);
}
/**
* 给正确的单元格加正确提示
* @param cell
*/
public static void addCellSuccessStyle(Cell cell){
CellStyle style = cell.getRow().getSheet().getWorkbook().createCellStyle();
style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cell.setCellStyle(style);
}
public static String validateMonthPlanTimeCell(Row row, int index) throws Exception {
Cell cell=row.getCell(index);
if(cell == null){
cell = row.createCell(index);
}
String planTime=ExcelUtils.getStringCellValue(cell);
planTime=planTime.replaceAll(" +","&");
StringBuffer errorMessage = new StringBuffer();
/*if(!ExcelUtils.mpTimeValidate(planTime, errorMessage)){ //格式判断
ExcelUtils.addCellErrorStyle(cell);
throw new Exception(errorMessage.toString());
} else{ */ //年月日格式校验正确
int length = planTime.length();
String year = planTime.substring(0,4);
String month = "";
String day = "";
String hour = "";
String minute = "";
String second = "";
List<String> strList=new ArrayList<String>();//存储分出来的字符串
//List<String> list = new ArrayList<String>(){{add("year"); add("str02");}};
String str="";
int num=0;
for(int i=5;i<length;i++){
char temp=planTime.charAt(i);
if(Character.isDigit(planTime.charAt(i))&&num<2){
str+=planTime.charAt(i);
num++;
}
else{
boolean isNum = str.matches("[0-9]+");
if(isNum){
strList.add(str);
}
str="";
num=0;
}
}
strList.add(str);
String date="";
String time="";
month=strList.get(0).length()==1?"0"+strList.get(0):strList.get(0);
day=strList.get(1).length()==1?"0"+strList.get(1):strList.get(1);
date=year + "-" + month + "-" + day;
//如果包含时分秒 获取时分秒的值
if(planTime.split("&").length>1){
if(strList.size()>2&&!("").equals(strList.get(2))){
hour=strList.get(2).length()==1?"0"+strList.get(2):strList.get(2);
if(strList.size()>3&&!("").equals(strList.get(3))){
minute=strList.get(3).length()==1?"0"+strList.get(3):strList.get(3);
if(strList.size()>4&&!strList.get(4).equals("")){
second=strList.get(4).length()==1?"0"+strList.get(4):strList.get(4);
}else{
second="00";
}
}else{
minute="00";
second="00";
}
}else{
hour="00";
minute="00";
second="00";
}
time=hour+":"+minute+":"+second;
}
return date+" "+time;
}
/**
*
* @param row
* @param index
* @param areaNames
* @return
* @throws Exception
*/
public static String validateAreaCell(Row row, int index, ArrayList<String> areaNames) throws Exception {
Cell cell=row.getCell(index); //传过来的index是 1 即校验第一列的area名称
if(cell == null){
cell = row.createCell(index);
}
String area=ExcelUtils.getStringCellValue(cell);
StringBuffer errorMessage = new StringBuffer();
if(!ExcelUtils.validateArea(areaNames,area,errorMessage)){
String [] strs = new String[areaNames.size()];
areaNames.toArray(strs);
int i = row.getRowNum();
Sheet sheet = row.getSheet();
ExcelUtils.setHSSFValidation(sheet, strs, i, i, index, index);
ExcelUtils.addCellErrorStyle(cell);
throw new Exception(errorMessage.toString());
}else{
ExcelUtils.addCellSuccessStyle(cell);
return area;
}
}
}
list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String requestURI = request.getRequestURI();
//获取项目名
String path = request.getContextPath();
//http://localhost:8080/项目名/
String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
+ path + "/";
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>新闻管理</title>
</head>
<body>
<table id="dg_permission" title="新闻管理" class="easyui-datagrid"
url="<%=basePath%>back_news/getNewsList.action"
alert(1);
toolbar="#toolbar_news" pagination="true"
pageList="[1,5,10]" rownumbers="true" fitColumns="true"
singleSelect="false">
<thead>
<tr>
<th field="ck" checkbox="true"></th>
<th field="id" width="50">id</th>
<th field="tilte" width="50">标题</th>
<th field="content" width="50">内容</th>
<th field="createTime" width="50">创作时间</th>
<th field="createTimeStr" width="50">url</th>
<th field="image" width="50">图片</th>
<th field="creator" width="50">作者</th>
<th field="isLocked" width="50">闪烁</th>
<th field="isHot" width="50">热度</th>
<th field="lastModifiedtime" width="50">最后修改时间</th>
<th field="lastModifier" width="50">修改人</th>
<th field="broweseCount" width="50">点击量</th>
</tr>
</thead>
</table>
<%-- <form id="upExcelForm" method="post" enctype="multipart/form-data">
选择文件： <input id="uploadExcel" name="uploadExcel" class="easyui-filebox" style="width:200px" data-options="prompt:'请选择文件...'">
<a href="#" class="easyui-linkbutton" style="width:122px" οnclick="uploadExcel()" >导入</a>
</form>
<script type="text/javascript">
function uploadExcel(){
//得到上传文件的全路径
var fileName= $('#uploadExcel').filebox('getValue');
var d1=/\.[^\.]+$/.exec(fileName);
if(d1==".xls") {
alert(1);
document.getElementById("upExcelForm").action="<%=basePath%>back_news/upexcel";
document.getElementById("upExcelForm").submit();
$.messager.alert('提示','我已经上传了','info');
}
}
</script>
</body>
</html> --%>
<script type="text/javascript">
//JS校验form表单信息
function checkData(){
var fileDir = $("#upfile").val();
var suffix = fileDir.substr(fileDir.lastIndexOf("."));
alert(suffix);
if("" == fileDir){
alert("选择需要导入的Excel文件！");
}
if(".xls" != suffix && ".xlsx" != suffix ){
alert("选择Excel格式的文件导入！");
}
$("#form1").submit();
return true;
}
</script>
</head>
<body>
<form method="POST" enctype="multipart/form-data" id="form1" action="<%=basePath%>back_news/upload.action">
<table>
<tr>
<td> <input id="upfile" type="file" name="upfile"
></td>
</tr>
<tr>
<!-- <td><input type="submit" value="提交" οnclick="return checkData()"></td> -->
<td><input type="button" value="导入" id="btn" name="btn" class="easyui-linkbutton" style="width:122px" οnclick="checkData()" ></td>
</tr>
</table>
</form>
</body>
</html>
755
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
