/**
* 导入excel文件
* 2014-7-23
* @return
*/
@RequiresPermissions("plug:product:caiwu:upload")
@RequestMapping("upload.do")
public String upload(
@RequestParam(value = "filePath", required = false) MultipartFile file,
HttpServletRequest request, HttpServletResponse response,
RedirectAttributes ra)throws IllegalStateException, IOException {
Map<String, String> errorMap = new HashMap<String, String>();
Integer siteId = Context.getCurrentSiteId(request);
String parentId = Servlets.getParameter(request, "parentId");
Site site = Context.getCurrentSite(request);
String base = site.getFilesBasePath("");
if (StringUtils.isBlank(parentId)) {
parentId = base;
}
ServletContext sc = request.getServletContext();
String savePath = Constants.SAVE_PATH;
// 上传文件的保存路径
ra.addFlashAttribute(MESSAGE, Constants.UPLOAD_SUCCESS);
String excelPath = sc.getRealPath("/") + savePath + "\\";// 上传成功读取文件
File dest = new File(excelPath, file.getOriginalFilename());
try {
file.transferTo(dest);
// 导入表格数据
String[][] execlResult = ExcelOperate.getData(dest, 1);// 1表示忽略的行数
errorMap = service.batchInsertExcelData(execlResult, siteId);// 批量更新excel表格数据到数据库中
request.getSession().setAttribute("errorMap", errorMap);
return "plug/product/product_upload_excel";
} catch (Exception e) {
errorMap.put("error", "文件不存在,或者上傳格式不正確!");
request.getSession().setAttribute("errorMap", errorMap);
return "plug/product/product_upload_excel";
}
}
/****
* 导出成Excel表格数
* @param request
* @return
* @throws UnsupportedEncodingException
*/
@RequiresPermissions("plug:product:caiwu:downloadexcel")
@RequestMapping("downloadexcel.do")
public String downLoadExcel(HttpServletRequest request,HttpServletResponse response) throws Exception{
response.setContentType("text/html;charset=utf-8");
request.setCharacterEncoding("UTF-8");
java.io.BufferedInputStream bis = null;
java.io.BufferedOutputStream bos = null;
List<ProductEntity> productList = new ArrayList<ProductEntity>();
ProductEntity product = new ProductEntity();
Site site = Context.getCurrentSite(request);
product.setSite(site);
productList = service.getAllProductList(product);
ExcelOperate operate = new ExcelOperate();
ServletContext sc = request.getServletContext();
String downloadPath = Constants.DOWNLOAD_PATH;
// 上传文件的保存路径
String excelPath = sc.getRealPath("/") + downloadPath + "\\";// 上传成功读取文件
operate.downLoadExcelData(productList,excelPath);//生成excel
String downLoadPath = excelPath + Constants.DOWNLOAD_EXCEL_NAME;
// System.out.println(downLoadPath);
try {
long fileLength = new File(downLoadPath).length();
response.setContentType("application/x-msdownload;");
response.setHeader("Content-disposition", "attachment; filename="
+ new String(Constants.DOWNLOAD_EXCEL_NAME.getBytes("utf-8"), "ISO8859-1"));
response.setHeader("Content-Length", String.valueOf(fileLength));
bis = new BufferedInputStream(new FileInputStream(downLoadPath));
bos = new BufferedOutputStream(response.getOutputStream());
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
//return "redirect:list.do";
return null;
}
EXCEL操作类。ExcelOperate.java
package com.paiergao.common;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
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.hssf.util.CellRangeAddressList;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.stereotype.Controller;
import com.jspxcms.core.domain.Site;
import com.jspxcms.core.support.Constants;
import com.jspxcms.plug.domain.ProductEntity;
import com.jspxcms.plug.repository.ProductDao;
/****
* 读取excel操作类
* 导入excel类
* @author Administrator
*
*/
@Controller
public class ExcelOperate {
private SimpleDateFormat sf = new SimpleDateFormat("yyyy/MM/dd");
private SimpleDateFormat sf2 = new SimpleDateFormat("yyyy-MM-dd");
private SimpleDateFormat sf3 = new SimpleDateFormat("dd/MM/yyyy");
//当前状态
private String [] currentStatuses={"已申号","新收到未修","待件","烧机","已修好待发","客户拒修待发","等待报价","已报价待确认","同意报价要求先修","先修未付费","作废","已发"};
//维修状态
private String [] warrantyStatuses={"保内维修","保内更换", "保内无故障","保外维修","保外拒修","保外无故障"};
//批量插入数据到qudao_excel表中2014-6-6
public Map<String,String> insertDataExcel(String[][] result,ProductDao dao,Integer siteId){
Map<String,String> errorMap = new HashMap<String, String>();
Site site = new Site();
site.setId(siteId);//站点id
int rowLength = result.length;//行数
// List<QuDaoEntity> dataList = new ArrayList<QuDaoEntity>();
long time1 = System.currentTimeMillis();
for(int i=0;i<rowLength;i++) {
ProductEntity product = new ProductEntity();
product.setSite(site);//设置站点
boolean bool = true;
for(int j=0;j<result[i].length;j++) {
System.out.print(result[i][j]+"\t\t");
if(result[i][0]!=null){
product.setRmaNumber(result[i][0]);
ProductEntity bean = dao.findExist(product);//判断rmaNumber不重复
if(bean!=null){
//有重复数据返回
errorMap.put(product.getRmaNumber(), "RMANumber已存在!");
bool= false;
}else{
bool = true;
}
}
if((result[i][1])!=null){
product.setPartner(result[i][1]);
}
if((result[i][2])!=null){
product.setModel(result[i][2]);
}
if((result[i][3])!=null){
product.setSerialNumber(result[i][3]);
}
if(null!=(result[i][4])){
product.setErrorInfo(result[i][4]);
}
if(null!=(result[i][5])&&""!=result[i][5]){
try {
product.setWarrantyDate(sf3.parse(result[i][5]));
} catch (ParseException e) {
e.printStackTrace();
}
}
if(null!=(result[i][6])){
product.setClaim(result[i][6].substring(0, result[i][6].length()-3));//去除后面的.00小数
}
if(null!=(result[i][7])&&""!=result[i][7]){
try {
product.setRecvDate(sf2.parse(result[i][7]));
} catch (ParseException e) {
e.printStackTrace();
}
}
if(null!=(result[i][8])&&""!=result[i][8]){
try {
product.setDeliverDate(sf2.parse(result[i][8]));
} catch (ParseException e) {
e.printStackTrace();
}
}
if(result[i][9]!=null&&""!=result[i][9]){
try {
product.setPartnerConfirmDate(sf2.parse(result[i][9]));
} catch (ParseException e) {
e.printStackTrace();
}
}
if(result[i][10]!=null&&""!=result[i][10]){
try {
product.setBackDate(sf2.parse(result[i][10]));
} catch (ParseException e) {
e.printStackTrace();
}
}
if(result[i][11]!=null){
product.setTrackNumber(result[i][11]);
}
if(result[i][12]!=null){
product.setYunFee(Double.parseDouble(result[i][12]));
}
if(result[i][13]!=null){
product.setCR(result[i][13]);
}
if(result[i][14]!=null){
product.setFindErrorInfo(result[i][14]);
}
if(result[i][15]!=null){
product.setHowFix(result[i][15]);
}
if(result[i][16]!=null){
product.setRepalceParts(result[i][16]);
}
if(result[i][17]!=null){
product.setEngineer(result[i][17]);
}
if(result[i][18]!=null){
/**取出当前状态,判断,存储**/
if(result[i][18].equals(Constants.C1)){
product.setCurrentStatus(1);
}
if(result[i][18].equals(Constants.C2)){
product.setCurrentStatus(2);
}
if(result[i][18].equals(Constants.C3)){
product.setCurrentStatus(3);
}
if(result[i][18].equals(Constants.C4)){
product.setCurrentStatus(4);
}
if(result[i][18].equals(Constants.C5)){
product.setCurrentStatus(5);
}
if(result[i][18].equals(Constants.C6)){
product.setCurrentStatus(6);
}
if(result[i][18].equals(Constants.C7)){
product.setCurrentStatus(7);
}
if(result[i][18].equals(Constants.C8)){
product.setCurrentStatus(8);
}
if(result[i][18].equals(Constants.C9)){
product.setCurrentStatus(9);
}
if(result[i][18].equals(Constants.C10)){
product.setCurrentStatus(10);
}
if(result[i][18].equals(Constants.C11)){
product.setCurrentStatus(11);
}
if(result[i][18].equals(Constants.C12)){
product.setCurrentStatus(12);
}
}
if(result[i][19]!=null){
if(result[i][19].equals(Constants.W1)){
product.setWarrantyStatus(1);
}
if(result[i][19].equals(Constants.W2)){
product.setWarrantyStatus(2);
}
if(result[i][19].equals(Constants.W3)){
product.setWarrantyStatus(3);
}
if(result[i][19].equals(Constants.W4)){
product.setWarrantyStatus(4);
}
if(result[i][19].equals(Constants.W5)){
product.setWarrantyStatus(5);
}
if(result[i][19].equals(Constants.W6)){
product.setWarrantyStatus(6);
}
}
if(result[i][20]!=null&&""!=result[i][20]){
product.setFixFee(Double.parseDouble(result[i][20]));
}
if(result[i][21]!=null){
product.setNoteInfo(result[i][21]);
}
}
//dataList.add(q);
System.out.println();
//ball.setId(i+1);
if(bool){
errorMap.put(product.getRmaNumber(),"导入成功!");
dao.save(product);
}else{
continue;
}
}
// dao.batchInsertData(dataList);//批量插入数据2014-6-6
return errorMap;
}
/**判断是否是数字**/
public static boolean isNumeric(String str){
final String number = "0123456789.";
for(int i = 0;i<str.length();i++){
if(number.indexOf(str.charAt(i)) == -1){
return false;
}
}
return true;
}
/**
* 读取Excel的内容,第一维数组存储的是一行中格列的值,二维数组存储的是多少个行
* @param file 读取数据的源Excel
* @param ignoreRows 读取数据忽略的行数,比喻行头不需要读入 忽略的行数为1
* @return 读出的Excel中数据的内容
* @throws FileNotFoundException
* @throws IOException
*/
public static String[][] getData(File file, int ignoreRows)
throws FileNotFoundException, IOException {
List<String[]> ballResult = new ArrayList<String[]>();//双色球数据
int rowSize = 0;
BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));
// 打开HSSFWorkbook
POIFSFileSystem fs = new POIFSFileSystem(in);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFCell cell = null;
for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
HSSFSheet st = wb.getSheetAt(sheetIndex);
// 第一行为标题,不取
for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
HSSFRow row = st.getRow(rowIndex);
if (row == null) {
continue;
}
int tempRowSize = row.getLastCellNum() + 1;
if (tempRowSize > rowSize) {
rowSize = tempRowSize;
}
String[] values = new String[rowSize];
Arrays.fill(values, "");
boolean hasValue = false;
for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
String value = "";
cell = row.getCell(columnIndex);
if (cell != null) {
// 注意:一定要设成这个,否则可能会出现乱码
//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd")
.format(date);
} else {
value = "";
}
}
else {
DecimalFormat df = new DecimalFormat("#.00"); //保留两位小数
String whatYourWant = df.format(cell.getNumericCellValue());
//System.out.println(whatYourWant);
value = whatYourWant;
/* value = new DecimalFormat("0").format(cell
.getNumericCellValue());*/
// value = String.valueOf(cell.getNumericCellValue());
// System.out.println("value:"+cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_FORMULA:
// 导入时如果为公式生成的数据则无值
if (!cell.getStringCellValue().equals("")) {
value = cell.getStringCellValue();
} else {
value = cell.getNumericCellValue() + "";
}
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y"
: "N");
break;
default:
value = "";
}
}
if (columnIndex == 0 && value.trim().equals("")) {
break;
}
values[columnIndex] = rightTrim(value);
hasValue = true;
}
if (hasValue) {
if(sheetIndex==0){
//百度杀毒sheet表数据
ballResult.add(values);
}
}
}
}
in.close();
String[][] ballReturnArray = new String[ballResult.size()][rowSize];
for (int i = 0; i < ballReturnArray.length; i++) {
ballReturnArray[i] = (String[]) ballResult.get(i);
}
//返回百度杀毒数据
return ballReturnArray;
}
/**
* 去掉字符串右边的空格
* @param str 要处理的字符串
* @return 处理后的字符串
*/
public static String rightTrim(String str) {
if (str == null) {
return "";
}
int length = str.length();
for (int i = length - 1; i >= 0; i--) {
if (str.charAt(i) != 0x20) {
break;
}
length--;
}
return str.substring(0, length);
}
public static void main(String[] args) {
ExcelOperate op = new ExcelOperate();
File file = new File("d:\\test.xls");
try {
String a[][] = op.getData(file, 1);
for (String[] strings : a) {
for (String string : strings) {
System.out.print(string);
}
System.out.println();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**导出excel**/
public void downLoadExcelData(List<ProductEntity> productList,String downloadPath) {
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("派尔高维修中心维修设备表");
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("维修编号");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("客户编号");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("设备型号");
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("系列号");
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue("客户反馈故障");
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue("保修期至");
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue("Claim ");
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue("收到日期");
cell.setCellStyle(style);
cell = row.createCell(8);
cell.setCellValue("报价日期");
cell.setCellStyle(style);
cell = row.createCell(9);
cell.setCellValue("客户确认日期");
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellValue("发回日期");
cell.setCellStyle(style);
cell = row.createCell(11);
cell.setCellValue("运单号跟踪");
cell.setCellStyle(style);
cell = row.createCell(12);
cell.setCellValue("运费");
cell.setCellStyle(style);
cell = row.createCell(13);
cell.setCellValue("C/R");
cell.setCellStyle(style);
cell = row.createCell(14);
cell.setCellValue("工程师发现故障");
cell.setCellStyle(style);
cell = row.createCell(15);
cell.setCellValue("如何维修");
cell.setCellStyle(style);
cell = row.createCell(16);
cell.setCellValue("更换配件");
cell.setCellStyle(style);
cell = row.createCell(17);
cell.setCellValue("工程师");
cell.setCellStyle(style);
cell = row.createCell(18);
cell.setCellValue("当前状态");
cell.setCellStyle(style);
cell = row.createCell(19);
cell.setCellValue("保修状态");
cell.setCellStyle(style);
cell = row.createCell(20);
cell.setCellValue("维修费用");
cell.setCellStyle(style);
cell = row.createCell(21);
cell.setCellValue("客户联系信息");
cell.setCellStyle(style);
// 第五步,写入实体数据 实际应用中这些数据从数据库得到,
for (int i = 0; i < productList.size(); i++)
{
row = sheet.createRow((int) i + 1);
ProductEntity product = (ProductEntity) productList.get(i);
// 第四步,创建单元格,并设置值
if(product.getRmaNumber()!=null){
row.createCell(0).setCellValue(product.getRmaNumber());
}
if(product.getPartner()!=null){
row.createCell(1).setCellValue(product.getPartner());
}
if(product.getModel()!=null){
row.createCell(2).setCellValue(product.getModel());
}
if(product.getSerialNumber()!=null){
row.createCell(3).setCellValue(product.getSerialNumber());
}
if(product.getErrorInfo()!=null){
row.createCell(4).setCellValue(product.getErrorInfo());
}
if(product.getWarrantyDate()!=null){
row.createCell(5).setCellValue(sf3.format(product.getWarrantyDate()));
}
if(product.getClaim()!=null){
row.createCell(6).setCellValue(product.getClaim());
}
if(product.getRecvDate()!=null){
row.createCell(7).setCellValue(sf2.format(product.getRecvDate()));
}
if(product.getDeliverDate()!=null){
row.createCell(8).setCellValue(sf2.format(product.getDeliverDate()));
}
if(product.getPartnerConfirmDate()!=null){
row.createCell(9).setCellValue(sf2.format(product.getPartnerConfirmDate()));
}
if(product.getBackDate()!=null){
row.createCell(10).setCellValue(sf2.format(product.getBackDate()));
}
if(product.getTrackNumber()!=null){
row.createCell(11).setCellValue(product.getTrackNumber());
}
if(product.getYunFee()!=null){
row.createCell(12).setCellValue(product.getYunFee());
}
if(product.getCR()!=null){
row.createCell(13).setCellValue(product.getCR());
}
if(product.getFindErrorInfo()!=null){
row.createCell(14).setCellValue(product.getFindErrorInfo());
}
if(product.getHowFix()!=null){
row.createCell(15).setCellValue(product.getHowFix());
}
if(product.getRepalceParts()!=null){
row.createCell(16).setCellValue(product.getRepalceParts());
}
if(product.getEngineer()!=null){
row.createCell(17).setCellValue(product.getEngineer());
}
if(product.getCurrentStatus()!=null){
CellRangeAddressList regions = new CellRangeAddressList(i+1,i+1,18,18);
//生成下拉框内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(currentStatuses);
//绑定下拉框和作用区域
HSSFDataValidation data_validation = new HSSFDataValidation(regions,constraint);
//对sheet页生效
sheet.addValidationData(data_validation);
switch (product.getCurrentStatus()) {
case 0:
row.createCell(18).setCellValue(Constants.C0);
break;
case 1:
row.createCell(18).setCellValue(Constants.C1);
break;
case 2:
row.createCell(18).setCellValue(Constants.C2);
break;
case 3:
row.createCell(18).setCellValue(Constants.C3);
break;
case 4:
row.createCell(18).setCellValue(Constants.C4);
break;
case 5:
row.createCell(18).setCellValue(Constants.C5);
break;
case 6:
row.createCell(18).setCellValue(Constants.C6);
break;
case 7:
row.createCell(18).setCellValue(Constants.C7);
break;
case 8:
row.createCell(18).setCellValue(Constants.C8);
break;
case 9:
row.createCell(18).setCellValue(Constants.C9);
break;
case 10:
row.createCell(18).setCellValue(Constants.C10);
break;
case 11:
row.createCell(18).setCellValue(Constants.C11);
break;
case 12:
row.createCell(18).setCellValue(Constants.C12);
break;
default:
break;
}
}
if(product.getWarrantyStatus()!=null){
CellRangeAddressList regions = new CellRangeAddressList(i+1,i+1,19,19);
//生成下拉框内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(warrantyStatuses);
//绑定下拉框和作用区域
HSSFDataValidation data_validation = new HSSFDataValidation(regions,constraint);
//对sheet页生效
sheet.addValidationData(data_validation);
switch (product.getWarrantyStatus()) {
case 0:
row.createCell(19).setCellValue(Constants.W0);
break;
case 1:
row.createCell(19).setCellValue(Constants.W1);
break;
case 2:
row.createCell(19).setCellValue(Constants.W2);
break;
case 3:
row.createCell(19).setCellValue(Constants.W3);
break;
case 4:
row.createCell(19).setCellValue(Constants.W4);
break;
case 5:
row.createCell(19).setCellValue(Constants.W5);
break;
case 6:
row.createCell(19).setCellValue(Constants.W6);
break;
default:
break;
}
}
if(product.getFixFee()!=null){
row.createCell(20).setCellValue(product.getFixFee());
}
if(product.getNoteInfo()!=null){
row.createCell(21).setCellValue(product.getNoteInfo());
}
}
// 第六步,将文件存到指定位置
try
{
FileOutputStream fout = new FileOutputStream(downloadPath+Constants.DOWNLOAD_EXCEL_NAME); //导出成excel的文件名称和路径
wb.write(fout);
fout.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}