/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package net.netjava.service;
import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import javax.servlet.http.HttpServletRequest;
import javax.swing.JFileChooser;
import javax.swing.UIManager;
import javax.swing.filechooser.FileSystemView;
import net.netjava.entity.Cucustomer;
import net.netjava.entity.Suer;
import net.netjava.manager.MCucustomerDAO;
import net.netjava.page.AccessController;
import net.netjava.page.BaseAjaxAction;
import net.netjava.util.ExcelFilter;
import net.netjava.util.ExcelUtil;
import net.netjava.util.Netjava;
import net.netjava.util.ParamUtil;
import net.netjava.util.SystemHelper;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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;
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package net.netjava.service;
import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import javax.servlet.http.HttpServletRequest;
import javax.swing.JFileChooser;
import javax.swing.UIManager;
import javax.swing.filechooser.FileSystemView;
import net.netjava.entity.Cucustomer;
import net.netjava.entity.Suer;
import net.netjava.manager.MCucustomerDAO;
import net.netjava.page.AccessController;
import net.netjava.page.BaseAjaxAction;
import net.netjava.util.ExcelFilter;
import net.netjava.util.ExcelUtil;
import net.netjava.util.Netjava;
import net.netjava.util.ParamUtil;
import net.netjava.util.SystemHelper;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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;
/** * * @author Nfang */ public class ExcelService extends BaseAjaxAction{ @Override public String onAction(HttpServletRequest request) { int myaction = ParamUtil.getInt(request, "myaction", -1); String msg = null; switch (myaction) { case Netjava.ACTION_DAOCHU: msg = this.Import(request); break; } return msg; } private String Import(HttpServletRequest request) { String msg = "导入成功!"; MCucustomerDAO cuMrg = new MCucustomerDAO(); String filePath = null; String name; boolean isrollback = false; Suer user = AccessController.isValidUser(request.getSession()); try{ // 设置对话框的风格 try { UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName()); } catch (Exception e1) { // TODO Auto-generated catch block e1.printStackTrace(); } FileSystemView fsv = FileSystemView.getFileSystemView(); File line = fsv.getHomeDirectory();//获取桌面路径 JFileChooser jFileChooser = new JFileChooser(line); ExcelFilter excelFilter = new ExcelFilter();//excel过滤,只显示文件夹和excel格式文件 jFileChooser.addChoosableFileFilter(excelFilter); jFileChooser.setFileFilter(excelFilter); jFileChooser.setDialogTitle("请选择Excel表格");//修改弹出框标题 int i = jFileChooser.showOpenDialog(null); if(i== jFileChooser.APPROVE_OPTION){//打开文件 filePath = jFileChooser.getSelectedFile().getAbsolutePath(); name = jFileChooser.getSelectedFile().getName(); // System.out.println("当前文件路径:"+path+"\n当前文件名:"+name); } //判断excel是什么版本的 boolean isExcel2003 = true; if (ExcelUtil.isExcel2007(filePath)){ isExcel2003 = false; } Workbook wookbook = null; // 创建对Excel工作簿文件的引用 if (isExcel2003){ wookbook = new HSSFWorkbook(new FileInputStream(filePath)); }else{ wookbook = new XSSFWorkbook(new FileInputStream(filePath)); } // 在Excel文档中,第一张工作表的缺省索引是0 Sheet sheet = wookbook.getSheetAt(0); //获取到Excel文件中的所有行数 int rows = sheet.getPhysicalNumberOfRows(); //遍历行 for (int j = 1; j < rows; j++) { // 读取左上端单元格 Row row = sheet.getRow(j); // 行不为空 String value = ""; if (row != null) { //获取到Excel文件中的所有的列 int cells = row.getPhysicalNumberOfCells(); //遍历列 for (int t = 0; t < cells; t++) { //获取到列的值 Cell cell = row.getCell(t); if (cell != null) { // 以下是判断数据的类型 switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // 数字 DecimalFormat df = new DecimalFormat("0"); value += df.format(cell.getNumericCellValue()) + ","; break; case HSSFCell.CELL_TYPE_STRING: // 字符串 value += cell.getStringCellValue() + ","; break; case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean value += cell.getBooleanCellValue() + ","; break; case HSSFCell.CELL_TYPE_FORMULA: // 公式 value += cell.getCellFormula() + ","; break; case HSSFCell.CELL_TYPE_BLANK: // 空值 value += "" + ","; break; case HSSFCell.CELL_TYPE_ERROR: // 故障 value += "非法字符" + ","; break; default: value += "未知类型" + ","; break; } } } } //将数据插入到mysql数据库中 String[] val = value.split(","); Cucustomer cu = new Cucustomer(); cu.setCucustomerclass(0); cu.setCucustomersource(0);//来自网页 cu.setCucustomertype(0); cu.setCucrafttype(0); cu.setCucreateid(user.getUserid()); cu.setCucreatetime(SystemHelper.createTime()); cu.setIsblack(0); cu.setCuisvip(0); if("周边游".indexOf(val[4])!=-1){ cu.setCutype(1); }else if("国内游".indexOf(val[4])!=-1){ cu.setCutype(2); }else{ cu.setCutype(3); } cu.setCucustomername(val[1]); if("男".equals(val[2])){ cu.setSex(0); }else{ cu.setSex(1); } cu.setCuid(Long.valueOf(val[0])); cu.setCuphone(val[0]); cu.setCuidcard(val[3]); cuMrg.insert(cu); } }catch (Exception e) { msg = "导入数据失败," + e.getLocalizedMessage() + "!"; e.printStackTrace(); isrollback = true; } return msg; } }
备注:
导入的jar有:poi-3.8-20120326,poi-ooxml-3.9,poi-ooxml-schemas-3.9,xbean-2.3.0,xmlbeans-2.3.0