导入导出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;    }}