导入导出Excel-JAVA

/**
 * 封装对excel的操作，包括本地读写excel和流中输出excel 有参构造函数参数为excel的全路径
 * 
 */
public class ExcelUtils {

    // excel文件路径
    private String path = "";

    /**
     * 无参构造函数 默认
     */
    public ExcelUtils() {

    }

    /**
     * 有参构造函数
     * 
     * @param path
     *            excel路径
     */
    public ExcelUtils(String path) {
        this.path = path;
    }

    /**
     * 在磁盘生成一个含有内容的excel,路径为path属性
     * 
     * @param sheetName
     *            导出的sheet名称
     * @param fieldName
     *            列名数组
     * @param data
     *            数据组
     * @throws IOException
     */
    public void makeExcel(String sheetName, String[] fieldName, List data, List counts) throws IOException {
        // 在内存中生成工作薄
        HSSFWorkbook workbook = makeWorkBook(sheetName, fieldName, data, counts);
        // 截取文件夹路径
        String filePath = path.substring(0, path.lastIndexOf("\\"));
        // 如果路径不存在，创建路径
        File file = new File(filePath);
        // System.out.println(path+"-----------"+file.exists());
        if (!file.exists())
            file.mkdirs();
        FileOutputStream fileOut = new FileOutputStream(path);
        workbook.write(fileOut);
        fileOut.close();
    }

    /**
     * 在输出流中导出excel
     * 
     * @param excelName
     *            导出的excel名称 包括扩展名
     * @param sheetName
     *            导出的sheet名称
     * @param fieldName
     *            列名数组
     * @param data
     *            数据组
     * @param counts 
     * @param response
     *            response
     */
    public static void makeStreamExcel(String excelName, String sheetName, String[] fieldName, List data, List counts, HttpServletResponse response) {
        OutputStream os = null;
        try {
            response.reset(); // 清空输出流
            os = response.getOutputStream(); // 取得输出流
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(excelName, "UTF-8") + ".xls"); // 设定输出文件头
            response.setContentType("application/msexcel"); // 定义输出类型
        } catch (IOException ex) {// 捕捉异常
            System.out.println("流操作错误:" + ex.getMessage());
        }
        // 在内存中生成工作薄
        HSSFWorkbook workbook = makeWorkBook(sheetName, fieldName, data, counts);
        try {
            os.flush();
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("Output is closed");
        }
        finally {
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 根据条件，生成工作薄对象到内存
     * 
     * @param sheetName
     *            工作表对象名称
     * @param fieldName
     *            首列列名称
     * @param data
     *            数据
     * @param counts 
     * @return HSSFWorkbook
     */
    public static HSSFWorkbook makeWorkBook(String sheetName, String[] fieldName, List<Object[]> data, List<Object[]> counts) {
        // 产生工作薄对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 产生工作表对象
        HSSFSheet sheet = workbook.createSheet();
        sheet.setDefaultRowHeightInPoints(25);
        sheet.setDefaultColumnWidth(16);
        // 为了工作表能支持中文,设置字符集为UTF_16
        workbook.setSheetName(0, sheetName);
        // 产生一行
        HSSFRow row = sheet.createRow(0);
        // 产生单元格
        HSSFCell cell;
        //设置样式 
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        HSSFDataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));

        // 写入各个字段的名称
        for (int i = 0; i < fieldName.length; i++) {
            // 创建第一行各个字段名称的单元格
            cell = row.createCell(i);
            // 为了能在单元格中输入中文,设置字符集为UTF_16
            // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
            // 给单元格内容赋值
            cell.setCellStyle(style);
            cell.setCellValue(new HSSFRichTextString(fieldName[i]));
        }
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        HSSFFont font2 = workbook.createFont();
        font2.setFontHeightInPoints((short) 11);
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        style2.setFont(font2);
        style2.setDataFormat(format.getFormat("@"));
        // 写入各条记录,每条记录对应excel表中的一行
        for (int i = 0; i < data.size(); i++) {
            Object[] tmp = data.get(i);
            // 生成一行
            row = sheet.createRow(i + 1);
            for (int j = 0; j < tmp.length; j++) {
                cell = row.createCell(j);

                if(tmp[j] == null){
                    cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                } else if(tmp[j] instanceof Boolean){
                    cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
                    cell.setCellValue((Boolean)tmp[j]);
                }else if(tmp[j] instanceof Number){
                    Number value = (Number)tmp[j];
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(value.doubleValue());
                }else{
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cell.setCellValue(String.valueOf(tmp[j]));
                }
                cell.setCellStyle(style2);
            }
        }
        //写入统计数据
        if (counts != null) {
            int rowNum = sheet.getLastRowNum();
            for (int i = 0; i < counts.size(); i++) {
                Object[] temp = counts.get(i);
                row = sheet.createRow(rowNum + i + 2);
                cell = row.createCell(0);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(temp[0] == null ? "" : String.valueOf(temp[0]));
                cell.setCellStyle(style);

                cell = row.createCell(1);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(temp[1] == null ? "" : String.valueOf(temp[1]));
                cell.setCellStyle(style2);
            }
        }
        return workbook;

    }

    public void write(int sheetOrder, int colum, int row, String content) throws Exception {
        Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(path)));
        Sheet sheet = workbook.getSheetAt(sheetOrder);
        Row rows = sheet.createRow(row);
        Cell cell = rows.createCell(colum);
        cell.setCellValue(content);
        FileOutputStream fileOut = new FileOutputStream(path);
        workbook.write(fileOut);
        fileOut.close();

    }

    /**
     * 得到一个工作区最后一条记录的序号
     * 
     * @param sheetOrder
     *            工作区序号
     * @return int
     * @throws IOException
     */
    public int getSheetLastRowNum(int sheetOrder) throws IOException {
        Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(path)));
        Sheet sheet = workbook.getSheetAt(sheetOrder);
        return sheet.getLastRowNum();
    }

    public String read(int sheetOrder, int colum, int row) throws Exception {
        Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(path)));
        Sheet sheet = workbook.getSheetAt(sheetOrder);
        Row rows = sheet.getRow(row);
        Cell cell = rows.getCell(colum);
        String content = cell.getStringCellValue();
        return content;
    }

    /**
     * 根据path属性，在磁盘生成一个新的excel
     * 
     * @throws IOException
     */
    public void makeEmptyExcel() throws IOException {
        Workbook wb = new HSSFWorkbook();
        // 截取文件夹路径
        String filePath = path.substring(0, path.lastIndexOf("\\"));
        // 如果路径不存在，创建路径
        File file = new File(filePath);
        if (!file.exists())
            file.mkdirs();
        FileOutputStream fileOut = new FileOutputStream(filePath + "\\" + path.substring(path.lastIndexOf("\\") + 1));
        wb.write(fileOut);
        fileOut.close();
    }

    /**
     * 根据工作区序号，读取该工作去下的所有记录，每一条记录是一个String[]<br/>
     * 注意如果单元格中的数据为数字将会被自动转换为字符串<br/>
     * 如果单元格中存在除数字，字符串以外的其他类型数据，将会产生错误
     * 
     * @param sheetOrder
     *            工作区序号
     * @return
     * @throws IOException
     * @throws
     */
    public List<String[]> getDataFromSheet(int sheetOrder) throws IOException {
        Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(path)));
        Sheet sheet = workbook.getSheetAt(sheetOrder);
        List<String[]> strs = new ArrayList<String[]>();
        // 注意得到的行数是基于0的索引 遍历所有的行
        // System.out.println(sheet.getLastRowNum());
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row rows = sheet.getRow(i);
            String[] str = new String[rows.getLastCellNum()];
            // 遍历每一列
            for (int k = 0; k < rows.getLastCellNum(); k++) {
                Cell cell = rows.getCell(k);
                // 数字类型时
                if (0 == cell.getCellType()) {
                    // 用于格式化数字，只保留数字的整数部分
                    DecimalFormat df = new DecimalFormat("########");
                    str[k] = df.format(cell.getNumericCellValue());
                } else
                    str[k] = cell.getStringCellValue();
                // System.out.println(cell.getCellType()+"-------------"+str[k]);
            }
            strs.add(str);
        }
        return strs;
    }

    /**
     * 获得单元格内容
     * 
     * @param cell
     * @return
     */
    public static String getCellData(Cell cell) {
        String value = "";
        if (cell != null) {
            // 数字类型时
            if (0 == cell.getCellType()) {
                // 用于格式化数字，只保留数字的整数部分
                DecimalFormat df = new DecimalFormat("########");
                value = df.format(cell.getNumericCellValue());
            } else if (2 == cell.getCellType())
                return "";
            else
                value = cell.getStringCellValue();
            value = value.trim();
            if ("/".equals(value))
                value = "";
        }
        return value;
    }

    // 获取邮编
    public static String getCellYB(Cell cell) {
        String value = "";
        if (cell != null) {
            // 数字类型时
            if (0 == cell.getCellType()) {
                // 用于格式化数字，只保留数字的整数部分
                DecimalFormat df = new DecimalFormat("000000");
                value = df.format(cell.getNumericCellValue());
            } else if (2 == cell.getCellType())
                return "";
            else
                value = cell.getStringCellValue();
            value = value.trim();
            if ("/".equals(value))
                value = "";
        }
        return value;
    }

    public static List<Object[]> stringList2Object(List<String[]> data){
        List<Object[]> objList = new ArrayList<Object[]>();
        if(data != null && data.size() > 0){
            for(String[] row : data){
                objList.add(row);
            }
        }
        return objList;
    }
}