xssf 导入mysql_Excel导入MySql数据库

1、JSP页面

请选择excel文件:

导入进度

X

2、JS

function save(){

if(!$("#dataForm").form('validate')){

return ;

}

var form = $("#dataForm");

var options = {

url:'/mallGiftAction/save.shtml',

type:'post',

beforeSend: ajaxLoading,

success:function(data)

{

ajaxLoadEnd();

if(data.success){

$('#tb_mallGift').datagrid('reload');

$('#tb_mallGift').datagrid('clearSelections');

}else{

$.messager.info(data.msg);

$("#progress").css("display", "none");

}

}

};

form.ajaxSubmit(options);

}

function ajaxLoading(){

$('#hideDiag').dialog('close');

$("#progress").css("display", "block");

var myDate = new Date();

startTime = myDate.getTime();

$(this).attr("disabled", true);

window.setTimeout("getProgressBar()", 1000);

$("

}

function ajaxLoadEnd(){

$(".datagrid-mask").remove();

}

3、Action

@RequestMapping(value = "/save")

@ResponseBody

public ResultVO save(String excelFilePath, MultipartFile excelFile, HttpServletRequest request, HttpServletResponse response) {

try {

mallGiftService.doStockin(excelFile,request);

return success("导入成功");

} catch (Exception e) {

return error(e.getMessage());

}

}

4、Service

public void doStockin(MultipartFile excelFile, HttpServletRequest request) throws Exception {

// excel读取的字段

Map beanNameMap = new HashMap();

beanNameMap.put("卡号", "cardNo");

beanNameMap.put("密码", "verifyCode");

// 读取excel数据

List> list = ExcelUtil.parseExcel(excelFile, beanNameMap);

List mallGiftList = ExcelUtil.toObjectList(list, MallGift.class);

int totalCount = mallGiftList.size();

int count = 1;

// excel数据入库

for (MallGift mallGift : mallGiftList) {

mallGift.setStatus(MallGift.STATUS_NEW);

// 加载监听器

ProgressModel status = new ProgressModel();

status.setTotalCount(totalCount);

status.setOverCount(count++);

request.getSession().setAttribute("status", status);

mallGiftDao.buffSave(mallGift);

}

}

5、ExcelUtil

public static List> parseExcel(MultipartFile excelFile, Map beanNameMap) throws IOException {

List> result = new LinkedList>();

InputStream is = excelFile.getInputStream();

XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);

XSSFRow beanNameRow = xssfWorkbook.getSheetAt(0).getRow(0);

int excleRowLength = xssfWorkbook.getSheetAt(0).getRow(0).getPhysicalNumberOfCells();

String[] columnName = new String[excleRowLength];

for (int i = 0; i < columnName.length; i++) {

XSSFCell name = beanNameRow.getCell(i);

Iterator it = beanNameMap.keySet().iterator();

while (it.hasNext()) {

String key = (String) it.next();

String value = beanNameMap.get(key);

if (key.trim().equals(name.getStringCellValue())) {

columnName[i] = value;

}

}

}

for (int sheetIndex = 0; sheetIndex < xssfWorkbook.getNumberOfSheets(); sheetIndex++) {

XSSFSheet sheet = (XSSFSheet) xssfWorkbook.getSheetAt(sheetIndex);

for (int rowIndex = 1; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {

XSSFRow row = sheet.getRow(rowIndex);

Map map = new HashMap();

for (int cellIndex = 0; cellIndex < xssfWorkbook.getSheetAt(0).getRow(0).getPhysicalNumberOfCells(); cellIndex++) {

XSSFCell cell = row.getCell(cellIndex);

String value = getCellValue(cell);

if (StringUtils.isEmpty(value)) {

throw new BaseException("Excel表格中的第:" + (rowIndex + 1) + "行第" + (cellIndex + 1) + "列为空.");

}

if (columnName[cellIndex] != null && columnName[cellIndex].trim().length() > 0) {

map.put(columnName[cellIndex].trim(), getCellValue(cell));

}

}

result.add(map);

}

}

return result;

}

/**

* 利用反射将 List>数据 生成相应的List数据

*

*/

public static List toObjectList(List> list, Class clazz) throws Exception {

List returnList = new LinkedList();

for (int i = 0; i < list.size(); i++) {

Set> set = list.get(i).entrySet();

Iterator> it = set.iterator();

T obj = clazz.newInstance();

Method[] methods = clazz.getDeclaredMethods();

while (it.hasNext()) {

Map.Entry entry = (Map.Entry) it.next();

for (Method m : methods) {

if (m.getName().startsWith("set")) {

String methodName = entry.getKey().toString();

StringBuffer sb = new StringBuffer(methodName);

sb.replace(0, 1, (methodName.charAt(0) + "").toUpperCase());

methodName = "set" + sb.toString();

if (methodName.equals(m.getName())) {

m.invoke(obj, entry.getValue());

break;

}

}

}

}

returnList.add(obj);

}

return returnList;

}

/**

* 获取当前单元格内容

*/

private static String getCellValue(Cell cell) {

String value = "";

if (cell != null) {

switch (cell.getCellType()) {

case Cell.CELL_TYPE_NUMERIC:

if (HSSFDateUtil.isCellDateFormatted(cell)) {

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());

value = sdf.format(date);

} else {

DecimalFormat format = new DecimalFormat("###################.###########");

value = format.format(cell.getNumericCellValue());

}

break;

case Cell.CELL_TYPE_STRING:

value = cell.getStringCellValue();

break;

case Cell.CELL_TYPE_BOOLEAN:

Boolean data = cell.getBooleanCellValue();

value = data.toString();

break;

case Cell.CELL_TYPE_ERROR:

System.out.println("单元格内容出现错误");

break;

case Cell.CELL_TYPE_FORMULA:

DecimalFormat format = new DecimalFormat("###################.###########");

value = format.format(cell.getNumericCellValue());

if (value.equals("NaN")) {

value = cell.getStringCellValue().toString();

}

break;

case Cell.CELL_TYPE_BLANK:

System.out.println("单元格内容 为空值 ");

break;

default:

value = cell.getStringCellValue().toString();

break;

}

}

return value;

}

public static void createExcel(List> list, LinkedHashMap headMap, String fileName) {

HSSFWorkbook workbook = new HSSFWorkbook();

HSSFSheet sheet = workbook.createSheet();

sheet.setDefaultRowHeightInPoints(10);

sheet.setDefaultColumnWidth(10);

int rowNum = 0;

// 设置标题行

HSSFCellStyle titleStyle = workbook.createCellStyle();

titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直

titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平

HSSFFont font = workbook.createFont();

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗

titleStyle.setFont(font);

HSSFRow titleRow = sheet.createRow((short) 0);

int m = 0;

for (String title : headMap.values()) {

HSSFCell cell = titleRow.createCell(m);

cell.setCellValue(title);

cell.setCellStyle(titleStyle);

m++;

}

rowNum++;

HSSFCellStyle style = workbook.createCellStyle();

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平

// 设置内容行

for (Map map : list) {

HSSFRow row = sheet.createRow((short) rowNum);

int i = 0;

for (String value : headMap.keySet()) {

HSSFCell cell = row.createCell(i);

cell.setCellValue(String.valueOf(map.get(value)));

cell.setCellStyle(style);

i++;

}

rowNum++;

}

OutputStream out = null;

try {

out = new FileOutputStream(fileName);

workbook.write(out);

} catch (IOException e) {

e.printStackTrace();

} finally {

if (out != null) {

try {

out.close();

} catch (IOException e) {

e.printStackTrace();

}

}

}

}

6、ProgressModel

private long pBytesRead = 0L;

private long pContentLength = 0L;

private int totalCount;

private int overCount;

public ProgressModel() {

pBytesRead = 0L;

pContentLength = 0L;

}

public long getPBytesRead() {

return pBytesRead;

}

public void setPBytesRead(long bytesRead) {

pBytesRead = bytesRead;

}

public long getPContentLength() {

return pContentLength;

}

public void setPContentLength(long contentLength) {

pContentLength = contentLength;

}

public int getTotalCount() {

return totalCount;

}

public void setTotalCount(int totalCount) {

this.totalCount = totalCount;

}

public int getOverCount() {

return overCount;

}

public void setOverCount(int overCount) {

this.overCount = overCount;

}

7、MallGiftDao

public void buffSave(MallGift mallGift) {

try {

super.buffSave(mallGift);//存入数据库方法

} catch (RuntimeException e) {

Throwable cause = e.getCause();

if (cause instanceof MySQLIntegrityConstraintViolationException) {

throw new RuntimeException("卡号:" + mallGift.getCardNo() + "在数据库已经存在,不能重复导入");

}

}

//myCache.set(mallGift);

//if (StringUtils.isNotEmpty(mallGift.getCardNo()))

//myCache.hSet(mallGift, "cardNo", mallGift.getCardNo());

}