java 按照每周分组

本例的工作应用是导入excel  excel的列里有一个发布日期

时间格式这种  2014/5/30  年月日的
导入技术用的poi 
实现的效果需求是 按照 发布日期进行每周分组 一周的数据为星期一到星期五 如果某天没数据就留空（因为考虑到那几天可能是节假日没有出数据） 然后将分组后的数据  每组数据插入到资讯表，按照一条资讯显示一周记录的形式 在网站上呈现出来
所以下面的效果图里会显示 填充后的  数据 即将一周里没有数据的那天 用空值填充

package com.test;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.sql.Timestamp;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Calendar;import java.util.Collections;import java.util.Date;import java.util.HashMap;import java.util.Iterator;import java.util.LinkedHashMap;import java.util.LinkedList;import java.util.List;import java.util.Map;import java.util.Set;import net.sf.json.JSONObject;import ztxx.cl.entity.AreaCopperInfos;import ztxx.common.util.ExcelUtil;import ztxx.common.util.StringUtils;import ztxx.common.util.ExcelUtil.GroupBy;public class POIexceltest {/** * @param args * @throws IOException */public static void main(String[] args) throws IOException {// TODO Auto-generated method stubMap<Integer, JSONObject> content = new LinkedHashMap<Integer, JSONObject>();File file = new File("d:/test/测试.xlsx");FileInputStream fi = new FileInputStream(file);if (file.getName().toLowerCase().endsWith("xls")) {content = ExcelUtil.readExcelContent(fi, 1);} else if (file.getName().toLowerCase().endsWith("xlsx")) {content = ExcelUtil.read2007Excels(fi, 1);}List<AreaCopperInfos> listTsts = new ArrayList<AreaCopperInfos>();for (Map.Entry<Integer, JSONObject> entry : content.entrySet()) {AreaCopperInfos aci = new AreaCopperInfos();aci.setAudit(1);if (StringUtils.stringIsNull(entry.getValue().get("品名")).equals("")) {continue;}aci.setChange(Double.valueOf(StringUtils.stringIsNull(entry.getValue().get("涨跌"))));aci.setCommodity(StringUtils.stringIsNull(entry.getValue().get("品名")));aci.setFold(Double.valueOf(StringUtils.stringIsNull(entry.getValue().get("均价"))));aci.setInfTypeId(11);aci.setMaterial(StringUtils.stringIsNull(entry.getValue().get("材质")));// System.out.println("材质:"+StringUtils.stringIsNull(entry.getValue().get(// "材质")));aci.setMaximumprice(Double.valueOf(StringUtils.stringIsNull(entry.getValue().get("最高价"))));aci.setMinimumtprice(Double.valueOf(StringUtils.stringIsNull(entry.getValue().get("最低价"))));aci.setOg(StringUtils.stringIsNull(entry.getValue().get("产地/牌号")));String fbsj = StringUtils.stringIsNull(entry.getValue().get("发布日期"));// System.out.println("发布日期:"+fbsj);String dateString = fbsj;Date date = null;try {SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");date = sdf.parse(dateString);} catch (ParseException e) {System.out.println(e.getMessage());}// System.out.println("星期code:"+getWeekOfDate(date));Timestamp ts = new Timestamp(System.currentTimeMillis());String tsStr = fbsj + " 00:00:00";try {ts = Timestamp.valueOf(tsStr);} catch (Exception e) {e.printStackTrace();}aci.setReleaseDate(ts);aci.setRemarks(StringUtils.stringIsNull(entry.getValue().get("备注")));aci.setUnit(StringUtils.stringIsNull(entry.getValue().get("单位")));listTsts.add(aci);}System.out.println("导入完成:" + listTsts.size());List<AreaCopperInfos> listTstsNew = new ArrayList<AreaCopperInfos>();// 去除重复发布日期listTstsNew = removeDuplicate(listTsts);System.out.println("去除重复发布日期后的list元素数量:" + listTstsNew.size());// 按照升序排sortClass sort = new sortClass();Collections.sort(listTstsNew, sort);for (int i = 0; i < listTstsNew.size(); i++) {AreaCopperInfos temp = (AreaCopperInfos) listTstsNew.get(i);// System.out.println("品名:" + temp.getCommodity() + ",发布日期:"// + temp.getReleaseDate());}final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");Map<String, List<AreaCopperInfos>> mapMonth = new HashMap<String, List<AreaCopperInfos>>();mapMonth = ExcelUtil.group(listTstsNew, new GroupBy<String>() {@Overridepublic String groupby(Object obj) {AreaCopperInfos d = (AreaCopperInfos) obj;String fbstr = sdf.format(d.getReleaseDate());String fbstrArray[] = fbstr.split("-", -1);String yue = fbstrArray[0] + fbstrArray[1];return yue; // 分组依据为发布时间}});System.out.println("按年月分组完成" + mapMonth.size());Set<String> key = null;if (null != mapMonth) {key = mapMonth.keySet();}// Set<Long> key = map.keySet();if (null != key) {for (Iterator it = key.iterator(); it.hasNext();) { // 组循环String s = (String) it.next();List<AreaCopperInfos> laci = mapMonth.get(s); // 抽出每组里的地区铜价集合数据Map<Integer, List<AreaCopperInfos>> mapWeek = new HashMap<Integer, List<AreaCopperInfos>>(); // 按照每周分组mapWeek = ExcelUtil.group(laci, new GroupBy<Integer>() {@Overridepublic Integer groupby(Object obj) {AreaCopperInfos d = (AreaCopperInfos) obj;SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");String str = sdf.format(d.getReleaseDate());Integer weekValue = getWeek(str);return weekValue; // 分组依据为发布时间}});System.out.println("按照每周分组完成,该月一共有几周:" + mapWeek.size());Set<Integer> keyWeek = null;if (null != mapWeek) {keyWeek = mapWeek.keySet();}for (Iterator itWeek = keyWeek.iterator(); itWeek.hasNext();) { // 循环每组里的星期Integer zhou = (Integer) itWeek.next();List<AreaCopperInfos> laciWeek = mapWeek.get(zhou);Map<String, AreaCopperInfos> maplistsa = newLinkedHashMap<String, AreaCopperInfos>();LinkedList<Map<String, AreaCopperInfos>> allWeek = new LinkedList<Map<String, AreaCopperInfos>>();for (int k = 0; k < laciWeek.size(); k++) {System.out.println("周里面的铜信息:"+ laciWeek.get(k).getCommodity() + ":"+ laciWeek.get(k).getReleaseDate()+ getWeek(laciWeek.get(k).getReleaseDate()));maplistsa.put(getWeek(laciWeek.get(k).getReleaseDate()), laciWeek.get(k));}if(laciWeek.size()<5&&laciWeek.size()>=1){//做填充System.out.println("填充后:");Map<String, AreaCopperInfos> map = new LinkedHashMap<String, AreaCopperInfos>();map.put("星期一", maplistsa.get("星期一"));map.put("星期二", maplistsa.get("星期二"));map.put("星期三", maplistsa.get("星期三"));map.put("星期四", maplistsa.get("星期四"));map.put("星期五", maplistsa.get("星期五"));allWeek.add(map);for(int i=0;i<allWeek.size();i++){if(null!=allWeek.get(i).get("星期一")){if(null!=allWeek.get(i).get("星期一").getCommodity()){System.out.println("周里面的铜信息:"+ allWeek.get(i).get("星期一").getCommodity()+ ":"+ allWeek.get(i).get("星期一").getReleaseDate()+ getWeek(allWeek.get(i).get("星期一").getReleaseDate()));}}else{System.out.println("周里面的铜信息:"+"空的");}if(null!=allWeek.get(i).get("星期二")){if(null!=allWeek.get(i).get("星期二").getCommodity()){System.out.println("周里面的铜信息:"+ allWeek.get(i).get("星期二").getCommodity()+ ":"+ allWeek.get(i).get("星期二").getReleaseDate()+ getWeek(allWeek.get(i).get("星期二").getReleaseDate()));}}else{System.out.println("周里面的铜信息:"+"空的");}if(null!=allWeek.get(i).get("星期三")){if(null!=allWeek.get(i).get("星期三").getCommodity()){System.out.println("周里面的铜信息:"+ allWeek.get(i).get("星期三").getCommodity()+ ":"+ allWeek.get(i).get("星期三").getReleaseDate()+ getWeek(allWeek.get(i).get("星期三").getReleaseDate()));}}else{System.out.println("周里面的铜信息:"+"空的");}if(null!=allWeek.get(i).get("星期四")){if(null!=allWeek.get(i).get("星期四").getCommodity()){System.out.println("周里面的铜信息:"+ allWeek.get(i).get("星期四").getCommodity()+ ":"+ allWeek.get(i).get("星期四").getReleaseDate()+ getWeek(allWeek.get(i).get("星期四").getReleaseDate()));}}else{System.out.println("周里面的铜信息:"+"空的");}if(null!=allWeek.get(i).get("星期五")){if(null!=allWeek.get(i).get("星期五").getCommodity()){System.out.println("周里面的铜信息:"+ allWeek.get(i).get("星期五").getCommodity()+ ":"+ allWeek.get(i).get("星期五").getReleaseDate()+ getWeek(allWeek.get(i).get("星期五").getReleaseDate()));}}else{System.out.println("周里面的铜信息:"+"空的");}}}System.out.println("第" + zhou + "周");}}}}public static List<AreaCopperInfos> removeDuplicate(List<AreaCopperInfos> list) {for (int i = 0; i < list.size() - 1; i++) {for (int j = list.size() - 1; j > i; j--) {if (list.get(j).getReleaseDate().equals(list.get(i).getReleaseDate())) {list.remove(j);}}}return list;}public static int getWeek(String str) {SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");Date date = null;try {date = sdf.parse(str);} catch (ParseException e) {// TODO Auto-generated catch blocke.printStackTrace();}Calendar calendar = Calendar.getInstance();calendar.setTime(date);// 第几周int week = calendar.get(Calendar.WEEK_OF_MONTH);// 第几天，从周日开始int day = calendar.get(Calendar.DAY_OF_WEEK);return week;}/** * 根据日期获得星期 *  * @param date * @return */public static String getWeekOfDate(Date date) {String[] weekDaysName = { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五","星期六" };String[] weekDaysCode = { "0", "1", "2", "3", "4", "5", "6" };Calendar calendar = Calendar.getInstance();calendar.setTime(date);int intWeek = calendar.get(Calendar.DAY_OF_WEEK) - 1;return weekDaysCode[intWeek];}// 根据日期取得星期几public static String getWeek(Date date) {String[] weeks = { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六" };Calendar cal = Calendar.getInstance();cal.setTime(date);int week_index = cal.get(Calendar.DAY_OF_WEEK) - 1;if (week_index < 0) {week_index = 0;}return weeks[week_index];}/** * 判断两个日期是否为同一周 *  * @param date1 * @param date2 * @return */public static boolean isSameDate(String date1, String date2) {SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");Date d1 = null;Date d2 = null;try {d1 = format.parse(date1);d2 = format.parse(date2);} catch (Exception e) {e.printStackTrace();}Calendar cal1 = Calendar.getInstance();Calendar cal2 = Calendar.getInstance();cal1.setTime(d1);cal2.setTime(d2);int subYear = cal1.get(Calendar.YEAR) - cal2.get(Calendar.YEAR);// subYear==0,说明是同一年if (subYear == 0) {if (cal1.get(Calendar.WEEK_OF_YEAR) == cal2.get(Calendar.WEEK_OF_YEAR))return true;}// 例子:cal1是"2005-1-1"，cal2是"2004-12-25"// java对"2004-12-25"处理成第52周// "2004-12-26"它处理成了第1周，和"2005-1-1"相同了// 大家可以查一下自己的日历// 处理的比较好// 说明:java的一月用"0"标识，那么12月用"11"else if (subYear == 1 && cal2.get(Calendar.MONTH) == 11) {if (cal1.get(Calendar.WEEK_OF_YEAR) == cal2.get(Calendar.WEEK_OF_YEAR))return true;}// 例子:cal1是"2004-12-31"，cal2是"2005-1-1"else if (subYear == -1 && cal1.get(Calendar.MONTH) == 11) {if (cal1.get(Calendar.WEEK_OF_YEAR) == cal2.get(Calendar.WEEK_OF_YEAR))return true;}return false;}}

package com.test;import java.util.Comparator;import ztxx.cl.entity.AreaCopperInfos;public class sortClass implements Comparator{ public int compare(Object arg0,Object arg1){   AreaCopperInfos user0 = (AreaCopperInfos)arg0;   AreaCopperInfos user1 = (AreaCopperInfos)arg1;          int flag = user0.getReleaseDate().compareTo(user1.getReleaseDate());         //System.out.println(flag);        return flag;      }  }

excel读取工具类 相关poi工具包 网上很多这里就不分享了

package ztxx.common.util;import java.io.BufferedOutputStream;import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.sql.Timestamp;import java.text.DecimalFormat;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Calendar;import java.util.Collection;import java.util.Date;import java.util.HashMap;import java.util.Iterator;import java.util.LinkedHashMap;import java.util.LinkedList;import java.util.List;import java.util.Map;import java.util.Set;import net.sf.json.JSONObject;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDataFormat;import org.apache.poi.hssf.usermodel.HSSFDateUtil;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.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;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 ztxx.cl.entity.AreaCopperInfos;public class ExcelUtil {private static POIFSFileSystem fs;private static HSSFWorkbook wb;private static HSSFSheet sheet;private static HSSFRow row;private static FileInputStream input;private static String[] excleTitle;public static boolean isNum(String str) {return str.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$");}/** * 根据文件路径读取Excel数据内容 返回map *  * @param excelPath * @return */public static Map<Integer, JSONObject> readExcelContent(String excelPath) {Map<Integer, JSONObject> contentJson = new LinkedHashMap<Integer, JSONObject>();String excelStr = "";// excel 内容try {input = new FileInputStream(new File(excelPath));fs = new POIFSFileSystem(input);wb = new HSSFWorkbook(fs);sheet = wb.getSheetAt(0);int rowNum = sheet.getLastRowNum(); // 得到总行数row = sheet.getRow(0);// 得到标题的内容对象。int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。excleTitle = new String[colNum];for (int i = 0; i < colNum; i++) {excleTitle[i] = getStringCellValue(row.getCell((short) i));}// 正文内容应该从第二行开始,第一行为表头的标题for (int i = 1; i <= rowNum; i++) {row = sheet.getRow(i);int j = 0;while (j < colNum) {String v = "";if (j + 1 == colNum) {String vs = getStringCellValue(row.getCell((short) j)).trim();if (vs.indexOf(".") > -1) {if (isNum(vs)) { // 是否是数字if (vs.endsWith("0")) {v = vs.substring(0, vs.indexOf("."));}} else {v = vs.trim();}} else {v = vs.trim();}excelStr += v;} else {String vs = getStringCellValue(row.getCell((short) j)).trim()+ "&";if (vs.indexOf(".") > -1) {if (isNum(vs)) { // 是否是数字if (vs.endsWith("0")) { // 处理用poi读取excel整数后面加.0的格式化v = vs.substring(0, vs.indexOf("."));}} else {v = vs.trim();}} else {v = vs.trim();}excelStr += v;}j++;}String excelstrArray[] = excelStr.split("&", -1); // 每行数据Map<String, String> params = new LinkedHashMap<String, String>();for (int k = 0; k < excelstrArray.length; k++) {params.put(excleTitle[k], excelstrArray[k]);}JSONObject jsonObject = JSONObject.fromObject(params);contentJson.put(i, jsonObject);// content.put(i, excelStr);excelStr = "";}} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {if (input != null) {input.close();}} catch (IOException e) {e.printStackTrace();}}return contentJson;}/** * 根据文件流读取Excel数据内容 返回map 2003 *  * @param input * @param count从第几行开始读 * @return */public static Map<Integer, JSONObject> readExcelContent(InputStream input,int count) {// 读取Excel数据内容Map<Integer, JSONObject> contentJson = new LinkedHashMap<Integer, JSONObject>();String excelStr = "";// excel 内容try {fs = new POIFSFileSystem(input);wb = new HSSFWorkbook(fs);sheet = wb.getSheetAt(0);int rowNum = sheet.getLastRowNum(); // 得到总行数row = sheet.getRow(0);// 得到标题的内容对象。int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。excleTitle = new String[colNum];for (int i = 0; i < colNum; i++) {excleTitle[i] = getStringCellValue(row.getCell(i));}// 正文内容应该从第二行开始,第一行为表头的标题for (int i = count; i <= rowNum; i++) {row = sheet.getRow(i);int j = 0;while (j < colNum) {String v = "";if (j + 1 == colNum) {String vs = getStringCellValue(row.getCell(j)).trim();if (vs.indexOf(".") > -1) {if (isNum(vs)) { // 是否是数字if (vs.endsWith("0")) {v = vs.substring(0, vs.indexOf("."));}} else {v = vs.trim();}} else {v = vs.trim();}excelStr += v;} else {String vs = getStringCellValue(row.getCell(j)).trim()+ "&";if (vs.indexOf(".") > -1) {if (isNum(vs)) { // 是否是数字if (vs.endsWith("0")) { // 处理用poi读取excel整数后面加.0的格式化v = vs.substring(0, vs.indexOf("."));}} else {v = vs.trim();}} else {v = vs.trim();}excelStr += v;}j++;}String excelstrArray[] = excelStr.split("&", -1); // 每行数据Map<String, String> params = new LinkedHashMap<String, String>();for (int k = 0; k < excelstrArray.length; k++) {params.put(excleTitle[k], excelstrArray[k]);}JSONObject jsonObject = JSONObject.fromObject(params);contentJson.put(i, jsonObject);// content.put(i, excelStr);excelStr = "";}} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {if (input != null) {input.close();}} catch (IOException e) {e.printStackTrace();}}return contentJson;}/** * 读取Office 2007 excel *  * @param input * @param count *            从第几行开始读 * @return * @throws IOException */public static Map<Integer, JSONObject> read2007Excels(InputStream input,int count) throws IOException {Map<Integer, JSONObject> contentJson = new LinkedHashMap<Integer, JSONObject>();// 构造 XSSFWorkbook 对象，strPath 传入文件路径XSSFWorkbook xwb = new XSSFWorkbook(input);// 读取第一章表格内容XSSFSheet sheet = xwb.getSheetAt(0);XSSFRow row = null;XSSFCell cell = null;XSSFRow headerrow = sheet.getRow(0); // 表头 得到标题的内容对象int colNum = headerrow.getPhysicalNumberOfCells();// 得到每行的列数。excleTitle = new String[colNum];for (int i = 0; i < colNum; i++) {excleTitle[i] = getStringCellValue(headerrow.getCell((short) i));}// System.out.println(sheet.getPhysicalNumberOfRows());// 循环内容项 不循环标题 所以+1for (int i = sheet.getFirstRowNum() + count; i <= sheet.getPhysicalNumberOfRows(); i++) {row = sheet.getRow(i);if (row == null) {continue;}List<String> linked = new LinkedList<String>();for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {Object value = null;cell = row.getCell(j);if (null != cell) {value = getStringCellValue(cell);}linked.add(StringUtils.stringIsNull(value));}Map<String, String> params = new LinkedHashMap<String, String>();for (int j = 0; j < linked.size(); j++) {params.put(excleTitle[j], linked.get(j));}JSONObject jsonObject = JSONObject.fromObject(params);contentJson.put(i, jsonObject);}return contentJson;}/** * 根据(字节串(或叫字节数组)变成输入流的形式)读取Excel数据内容 返回map *  * @param input * @return */public static Map<Integer, JSONObject> readExcelContent(ByteArrayInputStream input) {// 读取Excel数据内容// Map<Integer, String> content = new HashMap<Integer, String>();Map<Integer, JSONObject> contentJson = new LinkedHashMap<Integer, JSONObject>();String excelStr = "";// excel 内容try {// ByteArrayInputStream is = new ByteArrayInputStream( new// byte[1000]);fs = new POIFSFileSystem(input);wb = new HSSFWorkbook(fs);sheet = wb.getSheetAt(0);int rowNum = sheet.getLastRowNum(); // 得到总行数row = sheet.getRow(0);// 得到标题的内容对象。int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。excleTitle = new String[colNum];for (int i = 0; i < colNum; i++) {excleTitle[i] = getStringCellValue(row.getCell((short) i));}// 正文内容应该从第二行开始,第一行为表头的标题for (int i = 1; i <= rowNum; i++) {row = sheet.getRow(i);int j = 0;while (j < colNum) {String v = "";if (j + 1 == colNum) {String vs = getStringCellValue(row.getCell((short) j)).trim();if (vs.indexOf(".") > -1) {if (isNum(vs)) { // 是否是数字if (vs.endsWith("0")) {v = vs.substring(0, vs.indexOf("."));}} else {v = vs.trim();}} else {v = vs.trim();}excelStr += v;} else {String vs = getStringCellValue(row.getCell((short) j)).trim()+ "&";if (vs.indexOf(".") > -1) {if (isNum(vs)) { // 是否是数字if (vs.endsWith("0")) { // 处理用poi读取excel整数后面加.0的格式化v = vs.substring(0, vs.indexOf("."));}} else {v = vs.trim();}} else {v = vs.trim();}excelStr += v;}j++;}String excelstrArray[] = excelStr.split("&", -1); // 每行数据Map<String, String> params = new LinkedHashMap<String, String>();for (int k = 0; k < excelstrArray.length; k++) {params.put(excleTitle[k], excelstrArray[k]);}JSONObject jsonObject = JSONObject.fromObject(params);contentJson.put(i, jsonObject);// content.put(i, excelStr);excelStr = "";}} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {if (input != null) {input.close();}} catch (IOException e) {e.printStackTrace();}}return contentJson;}/** * 获取单元格数据内容为字符串类型的数据97-2003 *  * @param cell * @return */private static String getStringCellValue(HSSFCell cell) {String strCell = "";if (cell != null) {switch (cell.getCellType()) {case HSSFCell.CELL_TYPE_STRING:strCell = cell.getStringCellValue();break;case HSSFCell.CELL_TYPE_NUMERIC:// strCell = String.valueOf(cell.getNumericCellValue());if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式SimpleDateFormat sdf = null;if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {sdf = new SimpleDateFormat("HH:mm");} else {// 日期sdf = new SimpleDateFormat("yyyy-MM-dd");}Date date = cell.getDateCellValue();strCell = sdf.format(date);} else if (cell.getCellStyle().getDataFormat() == 58) {// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");double value = cell.getNumericCellValue();Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);strCell = sdf.format(date);} else {double value = cell.getNumericCellValue();CellStyle style = cell.getCellStyle();DecimalFormat format = new DecimalFormat();String temp = style.getDataFormatString();// 单元格设置成常规if (temp.equals("General")) {format.applyPattern("#");}strCell = format.format(value);}break;case HSSFCell.CELL_TYPE_BOOLEAN:strCell = String.valueOf(cell.getBooleanCellValue());break;case HSSFCell.CELL_TYPE_BLANK:strCell = "";break;default:cell.setCellType(Cell.CELL_TYPE_STRING); // 如果出现意外类型就先设置为string类型否则将会报数据类型异常strCell = cell.getStringCellValue();// strCell = "";break;}}if (strCell.equals("") || strCell == null) {return "";}if (cell == null) {return "";}return strCell;}/** * 获取单元格数据内容为日期类型的数据 *  * @param cell * @return */private static String getDateCellValue(HSSFCell cell) {String result = "";try {int cellType = cell.getCellType();if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {Date date = cell.getDateCellValue();result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)+ "-" + date.getDate();} else if (cellType == HSSFCell.CELL_TYPE_STRING) {String date = getStringCellValue(cell);result = date.replaceAll("[年月]", "-").replace("日", "").trim();} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {result = "";}} catch (Exception e) {System.out.println("日期格式不正确!");e.printStackTrace();}return result;}/** * 根据byte数组，生成文件 */public static void getFile(byte[] bfile, String filePath, String fileName) {BufferedOutputStream bos = null;FileOutputStream fos = null;File file = null;try {File dir = new File(filePath);if (!dir.exists() && dir.isDirectory()) {// 判断文件目录是否存在dir.mkdirs();}file = new File(filePath + "\\" + fileName);fos = new FileOutputStream(file);bos = new BufferedOutputStream(fos);bos.write(bfile);} catch (Exception e) {e.printStackTrace();} finally {if (bos != null) {try {bos.close();} catch (IOException e1) {e1.printStackTrace();}}if (fos != null) {try {fos.close();} catch (IOException e1) {e1.printStackTrace();}}}}// 从byte[]转filepublic static File getFileFromBytes(byte[] b, String outputFile) {BufferedOutputStream stream = null;File file = null;try {file = new File(outputFile);if (!file.exists() && file.isDirectory()) {// 判断文件目录是否存在file.mkdirs(); // mkdirs() 可以在不存在的目录中创建文件夹。诸如：a\\b,既可以创建多级目录。}FileOutputStream fstream = new FileOutputStream(file);stream = new BufferedOutputStream(fstream);stream.write(b);} catch (Exception e) {e.printStackTrace();} finally {if (stream != null) {try {stream.close();} catch (IOException e1) {e1.printStackTrace();}}}return file;}/** * 读取Office 2007 excel * */private static Map<Integer, JSONObject> read2007Excels(File file)throws IOException {Map<Integer, JSONObject> contentJson = new LinkedHashMap<Integer, JSONObject>();// 构造 XSSFWorkbook 对象，strPath 传入文件路径XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));// 读取第一章表格内容XSSFSheet sheet = xwb.getSheetAt(0);Object value = null;XSSFRow row = null;XSSFCell cell = null;XSSFRow headerrow = sheet.getRow(0); // 表头 得到标题的内容对象int colNum = headerrow.getPhysicalNumberOfCells();// 得到每行的列数。excleTitle = new String[colNum];for (int i = 0; i < colNum; i++) {excleTitle[i] = getStringCellValue(headerrow.getCell((short) i));}// System.out.println(sheet.getPhysicalNumberOfRows());// 循环内容项 不循环标题 所以+1for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {row = sheet.getRow(i);if (row == null) {continue;}List<String> linked = new LinkedList<String>();for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {cell = row.getCell(j);if (null != cell) {value = getStringCellValue(cell);}linked.add(StringUtils.stringIsNull(value));}Map<String, String> params = new LinkedHashMap<String, String>();for (int j = 0; j < linked.size(); j++) {params.put(excleTitle[j], linked.get(j));}JSONObject jsonObject = JSONObject.fromObject(params);contentJson.put(i, jsonObject);}return contentJson;}/** * 获取单元格数据内容为字符串类型的数据 excel2007 *  * @param cell * @return */public static String getStringCellValue(XSSFCell cell) {String strCell = "";// if(cell.equals("发布日期")){// return String.valueOf(cell.getDateCellValue());// }switch (cell.getCellType()) {case HSSFCell.CELL_TYPE_STRING:strCell = cell.getStringCellValue();break;case HSSFCell.CELL_TYPE_NUMERIC:// strCell = String.valueOf(cell.getNumericCellValue());// break;if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式SimpleDateFormat sdf = null;if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {sdf = new SimpleDateFormat("HH:mm");} else {// 日期sdf = new SimpleDateFormat("yyyy-MM-dd");}Date date = cell.getDateCellValue();strCell = sdf.format(date);} else if (cell.getCellStyle().getDataFormat() == 58) {// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");double value = cell.getNumericCellValue();Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);strCell = sdf.format(date);} else {double value = cell.getNumericCellValue();CellStyle style = cell.getCellStyle();DecimalFormat format = new DecimalFormat();String temp = style.getDataFormatString();// 单元格设置成常规if (temp.equals("General")) {format.applyPattern("#");}strCell = format.format(value);}break;case HSSFCell.CELL_TYPE_BOOLEAN:strCell = String.valueOf(cell.getBooleanCellValue());break;case HSSFCell.CELL_TYPE_BLANK:strCell = "";break;default:if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式SimpleDateFormat sdf = null;if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {sdf = new SimpleDateFormat("HH:mm");} else {// 日期sdf = new SimpleDateFormat("yyyy-MM-dd");}Date date = cell.getDateCellValue();strCell = sdf.format(date);} else if (cell.getCellStyle().getDataFormat() == 58) {// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");double value = cell.getNumericCellValue();Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);strCell = sdf.format(date);} else {cell.setCellType(Cell.CELL_TYPE_STRING);strCell = cell.getStringCellValue();}break;}if (strCell.equals("") || strCell == null) {return "";}if (cell == null) {return "";}return strCell;}/** * 获得指定文件的byte数组 */public static byte[] getBytes(String filePath) {byte[] buffer = null;try {File file = new File(filePath);FileInputStream fis = new FileInputStream(file);ByteArrayOutputStream bos = new ByteArrayOutputStream(1000);byte[] b = new byte[1000];int n;while ((n = fis.read(b)) != -1) {bos.write(b, 0, n);}fis.close();bos.close();buffer = bos.toByteArray();} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}return buffer;}public static void main(String args[]) throws IOException {Map<Integer, JSONObject> content = new LinkedHashMap<Integer, JSONObject>();File f = new File("D://test//铜价导入模版.xlsx");FileInputStream input = null;try {input = new FileInputStream(f);} catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}String filename = f.getName();if (filename.toLowerCase().endsWith("xls")) {content = ExcelUtil.readExcelContent(input, 1);} else if (filename.toLowerCase().endsWith("xlsx")) {content = ExcelUtil.read2007Excels(input, 1);}List<AreaCopperInfos> listAci = new ArrayList<AreaCopperInfos>();for (Map.Entry<Integer, JSONObject> entry : content.entrySet()) {AreaCopperInfos aci = new AreaCopperInfos();aci.setAudit(1);aci.setChange(Double.valueOf(StringUtils.stringIsNull(entry.getValue().get("涨跌"))));aci.setCommodity(StringUtils.stringIsNull(entry.getValue().get("品名")));aci.setFold(Double.valueOf(StringUtils.stringIsNull(entry.getValue().get("均价"))));aci.setInfTypeId(129);aci.setMaterial(StringUtils.stringIsNull(entry.getValue().get("材质")));aci.setMaximumprice(Double.valueOf(StringUtils.stringIsNull(entry.getValue().get("最高价"))));aci.setMinimumtprice(Double.valueOf(StringUtils.stringIsNull(entry.getValue().get("最低价"))));aci.setOg(StringUtils.stringIsNull(entry.getValue().get("产地/牌号")));String fbsj = StringUtils.stringIsNull(entry.getValue().get("发布日期"));Timestamp ts = new Timestamp(System.currentTimeMillis());String tsStr = fbsj + " 00:00:00";try {ts = Timestamp.valueOf(tsStr);// System.out.println(ts);} catch (Exception e) {e.printStackTrace();}aci.setReleaseDate(ts);aci.setRemarks(StringUtils.stringIsNull(entry.getValue().get("备注")));aci.setUnit(StringUtils.stringIsNull(entry.getValue().get("产地/牌号")));aci.setInfosid(0);listAci.add(aci);// System.out.println(entry.getValue().get("品名"));// System.out.println(entry.getValue().get("材质"));// System.out.println(entry.getValue().get("最低价"));// System.out.println(entry.getValue().get("最高价"));// System.out.println(entry.getValue().get("均价"));// System.out.println(entry.getValue().get("发布日期"));// System.out.println("---------------------");}// 进行分组Map<Long, List<AreaCopperInfos>> map = group(listAci,new GroupBy<Long>() {@Overridepublic Long groupby(Object obj) {AreaCopperInfos d = (AreaCopperInfos) obj;return d.getReleaseDate().getTime(); // 分组依据为课程ID}});Set<Long> key = map.keySet();for (Iterator it = key.iterator(); it.hasNext();) {Long s = (Long) it.next();System.out.println(map.get(s));}// //Group g=new Group();// List<GroupContinerAreaCopperInfos>// lgac=Group.groupAreaCopperInfos(listAci);//// System.out.println("分组完成");// for(int i=0;i<lgac.size();i++){// System.out.println(lgac.get(i).getReleaseDate());// }}/** * 分組依據接口，用于集合分組時，獲取分組依據 *  * @author ZhangLiKun * @title GroupBy * @date 2013-4-23 */public interface GroupBy<T> {T groupby(Object obj);}/** *  * @param colls * @param gb * @return */public static final <T extends Comparable<T>, D> Map<T, List<D>> group(Collection<D> colls, GroupBy<T> gb) {if (colls == null || colls.isEmpty()) {System.out.println("分組集合不能為空!");return null;}if (gb == null) {System.out.println("分組依據接口不能為Null!");return null;}Iterator<D> iter = colls.iterator();Map<T, List<D>> map = new HashMap<T, List<D>>();while (iter.hasNext()) {D d = iter.next();T t = gb.groupby(d);if (map.containsKey(t)) {map.get(t).add(d);} else {List<D> list = new ArrayList<D>();list.add(d);map.put(t, list);}}return map;}public static List<AreaCopperInfos> removeDuplicate(List<AreaCopperInfos> list) {for (int i = 0; i < list.size() - 1; i++) {for (int j = list.size() - 1; j > i; j--) {if (list.get(j).getReleaseDate().equals(list.get(i).getReleaseDate())) {list.remove(j);}}}return list;}public static int getWeek(String str) {SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");Date date = null;try {date = sdf.parse(str);} catch (ParseException e) {// TODO Auto-generated catch blocke.printStackTrace();}Calendar calendar = Calendar.getInstance();calendar.setTime(date);// 第几周int week = calendar.get(Calendar.WEEK_OF_MONTH);// 第几天，从周日开始int day = calendar.get(Calendar.DAY_OF_WEEK);return week;}/** * 根据日期获得星期 *  * @param date * @return */public static String getWeekOfDate(Date date) {String[] weekDaysName = { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五","星期六" };String[] weekDaysCode = { "0", "1", "2", "3", "4", "5", "6" };Calendar calendar = Calendar.getInstance();calendar.setTime(date);int intWeek = calendar.get(Calendar.DAY_OF_WEEK) - 1;return weekDaysCode[intWeek];}// 根据日期取得星期几public static String getWeek(Date date) {String[] weeks = { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六" };Calendar cal = Calendar.getInstance();cal.setTime(date);int week_index = cal.get(Calendar.DAY_OF_WEEK) - 1;if (week_index < 0) {week_index = 0;}return weeks[week_index];}/** * 判断两个日期是否为同一周 *  * @param date1 * @param date2 * @return */public static boolean isSameDate(String date1, String date2) {SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");Date d1 = null;Date d2 = null;try {d1 = format.parse(date1);d2 = format.parse(date2);} catch (Exception e) {e.printStackTrace();}Calendar cal1 = Calendar.getInstance();Calendar cal2 = Calendar.getInstance();cal1.setTime(d1);cal2.setTime(d2);int subYear = cal1.get(Calendar.YEAR) - cal2.get(Calendar.YEAR);// subYear==0,说明是同一年if (subYear == 0) {if (cal1.get(Calendar.WEEK_OF_YEAR) == cal2.get(Calendar.WEEK_OF_YEAR))return true;}// 例子:cal1是"2005-1-1"，cal2是"2004-12-25"// java对"2004-12-25"处理成第52周// "2004-12-26"它处理成了第1周，和"2005-1-1"相同了// 大家可以查一下自己的日历// 处理的比较好// 说明:java的一月用"0"标识，那么12月用"11"else if (subYear == 1 && cal2.get(Calendar.MONTH) == 11) {if (cal1.get(Calendar.WEEK_OF_YEAR) == cal2.get(Calendar.WEEK_OF_YEAR))return true;}// 例子:cal1是"2004-12-31"，cal2是"2005-1-1"else if (subYear == -1 && cal1.get(Calendar.MONTH) == 11) {if (cal1.get(Calendar.WEEK_OF_YEAR) == cal2.get(Calendar.WEEK_OF_YEAR))return true;}return false;}@SuppressWarnings("static-access")public static String getValue(XSSFCell xssfCell) {if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {return String.valueOf(xssfCell.getBooleanCellValue());} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {//DecimalFormat df = new DecimalFormat("#.######");//String strCell = df.format(xssfCell.getNumericCellValue());// DecimalFormat df = new DecimalFormat("0");  //     String strCell = df.format(xssfCell.getNumericCellValue()); return String.valueOf(xssfCell.getNumericCellValue());} else {return String.valueOf(xssfCell.getStringCellValue());}}@SuppressWarnings("static-access")public static String getValue(HSSFCell hssfCell) {if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {return String.valueOf(hssfCell.getBooleanCellValue());} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(hssfCell)) {// sb.append(SEPARATOR + cell.getDateCellValue());return String.valueOf(hssfCell.getDateCellValue());} else {// sb.append(SEPARATOR + cellValue.getNumberValue());return String.valueOf(hssfCell.getNumericCellValue());}// return String.valueOf(hssfCell.getNumericCellValue());// return String.valueOf(hssfCell.getNumericCellValue());// } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_STRING) {// return String.valueOf(hssfCell.getStringCellValue());} else {return String.valueOf(hssfCell.getStringCellValue());}}}

业务实体bean

package ztxx.cl.entity;import java.sql.Timestamp;/** * AreaCopperInfos entity. @author MyEclipse Persistence Tools */public class AreaCopperInfos implements java.io.Serializable {// Fieldsprivate Integer id;private String commodity;private String material;private Double minimumtprice;private Double maximumprice;private Double fold;private String unit;private Double change;private String og;private Timestamp releaseDate;private String remarks;private Timestamp createdate;private Integer infTypeId;private Integer audit;private Timestamp auditDate;private Timestamp updatetime;private Integer infosid;private String itname;// Constructors/** default constructor */public AreaCopperInfos() {}/** full constructor */public AreaCopperInfos(String commodity, String material,Double minimumtprice, Double maximumprice, Double fold,String unit, Double change, String og, Timestamp releaseDate,String remarks, Timestamp createdate, Integer infTypeId,Integer audit, Timestamp auditDate, Timestamp updatetime,Integer infosid) {this.commodity = commodity;this.material = material;this.minimumtprice = minimumtprice;this.maximumprice = maximumprice;this.fold = fold;this.unit = unit;this.change = change;this.og = og;this.releaseDate = releaseDate;this.remarks = remarks;this.createdate = createdate;this.infTypeId = infTypeId;this.audit = audit;this.auditDate = auditDate;this.updatetime = updatetime;this.infosid = infosid;}// Property accessorspublic Integer getId() {return this.id;}public void setId(Integer id) {this.id = id;}public String getCommodity() {return this.commodity;}public void setCommodity(String commodity) {this.commodity = commodity;}public String getMaterial() {return this.material;}public void setMaterial(String material) {this.material = material;}public Double getMinimumtprice() {return this.minimumtprice;}public void setMinimumtprice(Double minimumtprice) {this.minimumtprice = minimumtprice;}public Double getMaximumprice() {return this.maximumprice;}public void setMaximumprice(Double maximumprice) {this.maximumprice = maximumprice;}public Double getFold() {return this.fold;}public void setFold(Double fold) {this.fold = fold;}public String getUnit() {return this.unit;}public void setUnit(String unit) {this.unit = unit;}public Double getChange() {return this.change;}public void setChange(Double change) {this.change = change;}public String getOg() {return this.og;}public void setOg(String og) {this.og = og;}public Timestamp getReleaseDate() {return this.releaseDate;}public void setReleaseDate(Timestamp releaseDate) {this.releaseDate = releaseDate;}public String getRemarks() {return this.remarks;}public void setRemarks(String remarks) {this.remarks = remarks;}public Timestamp getCreatedate() {return this.createdate;}public void setCreatedate(Timestamp createdate) {this.createdate = createdate;}public Integer getInfTypeId() {return this.infTypeId;}public void setInfTypeId(Integer infTypeId) {this.infTypeId = infTypeId;}public Integer getAudit() {return this.audit;}public void setAudit(Integer audit) {this.audit = audit;}public Timestamp getAuditDate() {return this.auditDate;}public void setAuditDate(Timestamp auditDate) {this.auditDate = auditDate;}public Timestamp getUpdatetime() {return this.updatetime;}public void setUpdatetime(Timestamp updatetime) {this.updatetime = updatetime;}public Integer getInfosid() {return this.infosid;}public void setInfosid(Integer infosid) {this.infosid = infosid;}public String getItname() {return itname;}public void setItname(String itname) {this.itname = itname;}}

效果图：





我上传了测试用的excel 文件 大家可以看看效果

有什么问题大家加我qq  讨论 6637152
apche  shiro权限框架  技术讨论群  欢迎您的加入 208316279