javaweb-Excel导入导出后台代码

前言：

导入导出后台java代码写好很久了，但是。。。但是。。。前台不会写啊。

先把后台代码帖上吧

1、excelToDb

package util;
/**
 * 代码解释：此方法将传入一个URL，即为当前用户所上传的Excel的目标路径，然后返回一个泛型为StudentInfoEntity的ArrayList
 */

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import dao.DistrictDao;
import dao.impl.DistrictDaoImp;

import entity.District;


public class ExcelToList {
    public ArrayList<District> excelTo(String URL) throws IOException{
        //创建list集合存放对象
        ArrayList<District> list = new ArrayList<District>();
    
        File file = new File(URL);

        Workbook workbook=null;
        try {
            workbook = WorkbookFactory.create(file);
        } catch (InvalidFormatException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //代码解释：读取默认第一个工作表sheet
        Sheet sheet =  workbook.getSheetAt(0);
        //代码解释：获取sheet中最后一行行号
        int lastRowNum = sheet.getLastRowNum();
       // System.out.println("lastRowNum========="+lastRowNum);
        //代码解释：循环所有行
        ArrayList<String> list2 = new ArrayList<String>();
        for (int i = 1; i <= lastRowNum; i++) {
            //代码解释：获取当前行中的内容
            Row row = sheet.getRow(i);
            short cell = row.getLastCellNum();
          //  System.out.println("cellnumber======="+cell);
            if(row !=null && cell!=0){
                for(int j=0;j<cell;j++){
                    District district=new District();
                    Cell name=row.getCell(j);
                    district.setName(getValue(name));
                    list.add(district);
                }
            }
        }
        return list;
    }
    //取单元格中的值
    public String getValue(Cell cell){
        String result="";
        if(cell.getCellType() == cell.CELL_TYPE_BOOLEAN){
            //返回布尔类型的值
            result = cell.getBooleanCellValue() +"";
        }else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC){
            //返回数值类型的值
            if(HSSFDateUtil.isCellDateFormatted(cell)){
                result = DateUtil.getJavaDate(cell.getNumericCellValue()).toString();
            }else{
                result = cell.getNumericCellValue()+"";
            }
            return String.valueOf(cell.getNumericCellValue());
        }else if(cell.getCellType() == cell.CELL_TYPE_FORMULA){
            result = cell.getCellFormula();
        }else if(cell.getCellType() == cell.CELL_TYPE_STRING){
            result = cell.getStringCellValue();
        }else{
            //返回字符口串类型的值
            result = cell.getStringCellValue();
        }
        return result;
    }
    
    public static void main(String[] args) throws IOException {
        ExcelToList excelToList=new ExcelToList();
        ArrayList<District> list =excelToList.excelTo("D:\work\File\district.xls");
        DistrictDao dao=new DistrictDaoImp();
        for (District district : list) {
            dao.addDistrict(district);
        }
    }
    
    
}

这里我设想的很美，JSP页面点击导入，选择文件，给我个xls的路径，我就可以返回给你个结果list集供你页面显示。但是在点击打开选择文件窗口选择文件确认后传路径给后台的JS代码我不会写。。。。。

2、listToExcel:导出

package util;

import java.io.File;
import java.io.FileOutputStream;
import java.util.List;

import org.apache.commons.io.FileUtils;
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 dao.DistrictDao;
import dao.impl.DistrictDaoImp;

import entity.District;

/**
 * 导出，从数据库中导出数据，并生成excel文件
 * @author 0
 *
 */
public class OutExcel {
    //动态从JSp页面获取标题
    public String downloadGrade(String contextPath,String filename){
        DistrictDao  districtDao=new DistrictDaoImp();
        List<District> listd = districtDao.getDistricts();
        String[] title={"ID","disname"};
        //创建excel工作薄
        XSSFWorkbook workbook=new XSSFWorkbook();
        //创建一个工作表sheet
        XSSFSheet sheet=workbook.createSheet();
        //创建第一行
        XSSFRow row=sheet.createRow(0);
        XSSFCell cell=null;
        //插入第一行数据 id 地区名称
        for(int i=0;i<title.length;i++){
            //创建一行的一格
            cell = row.createCell(i);
            //赋值
            cell.setCellValue(title[i]);
        }
        //追加数据行数
        int j=1;
        for(int i=0;i<listd.size();i++){
            //从集合中得到一个对象
            District district2=listd.get(i);
            //创建第2行
            XSSFRow nextrow=sheet.createRow(j);
            //创建第1列并赋值
            XSSFCell cess2 = nextrow.createCell(0);
            cess2.setCellValue(district2.getId());
            //创建第2列并赋值
            XSSFCell cess3 = nextrow.createCell(1);
            cess3.setCellValue(district2.getName());
            j++;
        }
        
        //创建一个文件
        String path = contextPath +"/"+filename+".xlsx";
        File file =new File(path);
        try {
            file.createNewFile();
            //将excel内容存盘
            FileOutputStream fos =FileUtils.openOutputStream(file);
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return path;
    }
    
    public static void main(String[] args) {
        OutExcel oe=new OutExcel();
        
        String path = oe.downloadGrade("D:", "district");
        System.out.println(path);
    }
}

这个导出，我是想JSP给我传勾选的每一行的信息给后端处理，然后按需导出。但页面JS那块不会处理