controller:
@RestController
@RequestMapping ( "/ordersetting" )
public class OrderSettingController {
@Reference
private OrderSettingService orderSettingService;
@RequestMapping ( "/upload" )
public Result upload ( @RequestParam ( "excelFile" ) MultipartFile excelFile) {
try {
List < String [ ] > list= POIUtils . readExcel ( excelFile) ;
List < OrderSetting > data = new ArrayList < > ( ) ;
for ( String [ ] strings : list) {
String orderDate = strings[ 0 ] ;
String number = strings[ 1 ] ;
OrderSetting orderSetting = new OrderSetting ( new Date ( orderDate) , Integer . parseInt ( number) ) ;
data. add ( orderSetting) ;
}
orderSettingService. add ( data) ;
return new Result ( true , MessageConstant . IMPORT_ORDERSETTING_SUCCESS) ;
} catch ( IOException e) {
e. printStackTrace ( ) ;
return new Result ( false , MessageConstant . IMPORT_ORDERSETTING_FAIL) ;
}
}
}
serviceImpl:
@Service ( interfaceClass = OrderSettingService . class )
@Transactional
public class OrderSettingServiceImpl implements OrderSettingService {
@Autowired
private OrderSettingDao orderSettingDao;
@Override
public void add ( List < OrderSetting > list) {
if ( list!= null && list. size ( ) > 0 ) {
for ( OrderSetting orderSetting : list) {
long countByOrderDate = orderSettingDao. findCountByOrderDate ( orderSetting. getOrderDate ( ) ) ;
if ( countByOrderDate> 0 ) {
orderSettingDao. editNumberByOrderDate ( orderSetting) ;
} else {
orderSettingDao. add ( orderSetting) ;
}
}
}
}
}
dao:
public interface OrderSettingDao {
public void add ( OrderSetting orderSetting) ;
public void editNumberByOrderDate ( OrderSetting orderSetting) ;
public long findCountByOrderDate ( Date orderDate) ;
}
dao.xml:
< mapper namespace= "com.itwang.dao.OrderSettingDao" >
< ! -- 新增-- >
< insert id= "add" parameterType= "com.itwang.pojo.OrderSetting" >
insert into t_ordersetting
( orderDate, number, reservations)
values
( #{ orderDate} , #{ number} , #{ reservations} )
< / insert>
< update id= "editNumberByOrderDate" parameterType= "com.itwang.pojo.OrderSetting" >
update t_ordersetting set number= #{ number}
where orderDate= #{ orderDate}
< / update>
< select id= "findCountByOrderDate" parameterType= "java.util.Date" resultType= "long" >
select count ( id) from t_ordersetting where orderDate = #{ orderDate}
< / select>
< / mapper>
poi工具类:
public class POIUtils {
private final static String xls = "xls" ;
private final static String xlsx = "xlsx" ;
private final static String DATE_FORMAT = "yyyy/MM/dd" ;
public static List < String [ ] > readExcel ( MultipartFile file) throws IOException {
checkFile ( file) ;
Workbook workbook = getWorkBook ( file) ;
List < String [ ] > list = new ArrayList < String [ ] > ( ) ;
if ( workbook != null ) {
for ( int sheetNum = 0 ; sheetNum < workbook. getNumberOfSheets ( ) ; sheetNum++ ) {
Sheet sheet = workbook. getSheetAt ( sheetNum) ;
if ( sheet == null ) {
continue ;
}
int firstRowNum = sheet. getFirstRowNum ( ) ;
int lastRowNum = sheet. getLastRowNum ( ) ;
for ( int rowNum = firstRowNum+ 1 ; rowNum <= lastRowNum; rowNum++ ) {
Row row = sheet. getRow ( rowNum) ;
if ( row == null ) {
continue ;
}
int firstCellNum = row. getFirstCellNum ( ) ;
int lastCellNum = row. getPhysicalNumberOfCells ( ) ;
String [ ] cells = new String [ row. getPhysicalNumberOfCells ( ) ] ;
for ( int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++ ) {
Cell cell = row. getCell ( cellNum) ;
cells[ cellNum] = getCellValue ( cell) ;
}
list. add ( cells) ;
}
}
workbook. close ( ) ;
}
return list;
}
public static void checkFile ( MultipartFile file) throws IOException {
if ( null == file) {
throw new FileNotFoundException ( "文件不存在!" ) ;
}
String fileName = file. getOriginalFilename ( ) ;
if ( ! fileName. endsWith ( xls) && ! fileName. endsWith ( xlsx) ) {
throw new IOException ( fileName + "不是excel文件" ) ;
}
}
public static Workbook getWorkBook ( MultipartFile file) {
String fileName = file. getOriginalFilename ( ) ;
Workbook workbook = null ;
try {
InputStream is = file. getInputStream ( ) ;
if ( fileName. endsWith ( xls) ) {
workbook = new HSSFWorkbook ( is) ;
} else if ( fileName. endsWith ( xlsx) ) {
workbook = new XSSFWorkbook ( is) ;
}
} catch ( IOException e) {
e. printStackTrace ( ) ;
}
return workbook;
}
public static String getCellValue ( Cell cell) {
String cellValue = "" ;
if ( cell == null ) {
return cellValue;
}
String dataFormatString = cell. getCellStyle ( ) . getDataFormatString ( ) ;
if ( dataFormatString. equals ( "m/d/yy" ) ) {
cellValue = new SimpleDateFormat ( DATE_FORMAT) . format ( cell. getDateCellValue ( ) ) ;
return cellValue;
}
if ( cell. getCellType ( ) == Cell . CELL_TYPE_NUMERIC) {
cell. setCellType ( Cell . CELL_TYPE_STRING) ;
}
switch ( cell. getCellType ( ) ) {
case Cell . CELL_TYPE_NUMERIC:
cellValue = String . valueOf ( cell. getNumericCellValue ( ) ) ;
break ;
case Cell . CELL_TYPE_STRING:
cellValue = String . valueOf ( cell. getStringCellValue ( ) ) ;
break ;
case Cell . CELL_TYPE_BOOLEAN:
cellValue = String . valueOf ( cell. getBooleanCellValue ( ) ) ;
break ;
case Cell . CELL_TYPE_FORMULA:
cellValue = String . valueOf ( cell. getCellFormula ( ) ) ;
break ;
case Cell . CELL_TYPE_BLANK:
cellValue = "" ;
break ;
case Cell . CELL_TYPE_ERROR:
cellValue = "非法字符" ;
break ;
default :
cellValue = "未知类型" ;
break ;
}
return cellValue;
}
}