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);

        }

    }

}

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);

    }

}