ExcelUtil,一个简单封装的excel解析和生成类

来源：互联网发布：xmind8 mac版序列号编辑：程序博客网时间：2024/06/07 14:50

在写代码之前将poi导入到我们的项目中
在pom.xml中添加如下依赖

        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi</artifactId>            <version>3.9</version>        </dependency>        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-examples</artifactId>            <version>3.9</version>        </dependency>    </dependencies>

开始封装我们的工具类，这里的两个参数用于区别不同版本的excel文件

public class ExcelUtil {    private ExcelUtil() {    }    private final String excel2003L = ".xls";//2003- 版本的excel    private final String excel2007U = ".xlsx";//2007+ 版本的excel    public static final ExcelUtil getInstance() {        return ExcelUtilHolder.instance;    }    private static final class ExcelUtilHolder {        private static final ExcelUtil instance = new ExcelUtil();    }}

首先获取到文件的后缀，根据不同的版本创建我们的Workbook

    /**     * 根据文件后缀，自适应上传文件的版本     */    private Workbook getWorkbook(InputStream inStr, String filename) throws Exception {        Workbook workbook;        String fileType = filename.substring(filename.lastIndexOf("."));        if (excel2003L.equals(fileType)) {            workbook = new HSSFWorkbook(inStr);//2003-        } else if (excel2007U.equals(fileType)) {            workbook = new XSSFWorkbook(inStr);//2007+        } else {            throw new Exception("解析的文件格式有误");        }        return workbook;    }

创建完Workbook之后，我们通过Workbook这个对象获取excel中的数据。
HSSFWorkbook和XSSFWorkbook 是实现了Workbook接口的Excel的文档对象。

    /**     * 获取excel中的数据     */    public List<List<Object>> readExcelData(InputStream in, String filename) throws Exception {        List<List<Object>> list;        //创建Excel工作薄        Workbook work = getWorkbook(in, filename);        if (null == work) {            throw new Exception("创建Excel工作薄为空！");        }        Sheet sheet = null;        Row row = null;        Cell cell = null;        list = new ArrayList<List<Object>>();        //遍历Excel中所有的sheet        for (int i = 0; i < work.getNumberOfSheets(); i++) {            sheet = work.getSheetAt(i);            if (sheet == null) {                continue;            }            //遍历当前sheet中的所有行            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {                row = sheet.getRow(j);                if (row == null || row.getFirstCellNum() == j) {                    continue;                }                //遍历所有的列                List<Object> li = new ArrayList<Object>();                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {                    cell = row.getCell(y);                    //通过getCellValue方法获取当前行每一列中的数据                    li.add(getCellValue(cell));                }                //将每一行的数据添加到list                list.add(li);            }        }        in.close();        return list;    }

然后获取每个单元格的方法

    /**     * 获取每个单元格的内容     */    private Object getCellValue(Cell cell) {        Object value = null;        DecimalFormat df = new DecimalFormat("0");//格式化number String字符串        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//日期格式化        switch (cell.getCellType()) {            case Cell.CELL_TYPE_STRING:                value = cell.getRichStringCellValue().getString();                break;            case Cell.CELL_TYPE_NUMERIC:                if ("General".equals(cell.getCellStyle().getDataFormatString())) {                    value = df.format(cell.getNumericCellValue());                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {                    value = sdf.format(cell.getDateCellValue());                } else {                    value = cell.getNumericCellValue();                }                break;            case Cell.CELL_TYPE_BOOLEAN:                value = cell.getBooleanCellValue();                break;            case Cell.CELL_TYPE_BLANK:                value = "";                break;            default:                break;        }        return value;    }

至此我们就已经可以获取到excel表格中的数据了

数据转成excel表格

    /**     * 数据转成excel     *     * @param dataList   需要转换的数据     * @param sheetName  生成的excel表名     * @param columnName 每一列的名字(key)     * @return     */    public Workbook dataToExcel(List<Map<String, String>> dataList, String sheetName, String[] columnName) {        int columnNum = columnName.length;        Workbook workbook = new HSSFWorkbook();        //创建第一页，并命名        Sheet sheet = workbook.createSheet(sheetName);        //创建第一行        Row row = sheet.createRow(0);        // 创建两种单元格格式        CellStyle cs = workbook.createCellStyle();        CellStyle cs2 = workbook.createCellStyle();        // 创建两种字体        Font f = workbook.createFont();        Font f2 = workbook.createFont();        // 创建第一种字体样式（用于列名）        f.setFontHeightInPoints((short) 10);        f.setColor(IndexedColors.BLACK.getIndex());        f.setBoldweight(Font.BOLDWEIGHT_BOLD);        // 创建第二种字体样式（用于值）        f2.setFontHeightInPoints((short) 10);        f2.setColor(IndexedColors.BLACK.getIndex());        //设置列名        for (int i = 0; i < columnNum; i++) {            Cell cell = row.createCell(i);            cell.setCellValue(columnName[i]);            cell.setCellStyle(cs);        }        //设置每行每列的值        int rowNum = dataList.size();        for (int j = 1; j <= rowNum; j++) {            //创建一行            Row r = sheet.createRow(j);            for (int k = 0; k < columnNum; k++) {                Cell cell = r.createCell(k);                cell.setCellValue(dataList.get(j - 1).get(columnName[k]));                cell.setCellStyle(cs2);            }        }        return workbook;    }

最后在我们的控制器中实现excel文件的下载,dataList中的数据请自行获取或添加

public void downloadExcel(List<Map<String, String>> dataList, HttpServletResponse servletResponse) throws IOException {        String[] columnName = {"name", "location", "manager", "phone", "description", "latitude", "longitude"};        Workbook workbook = ExcelUtil.getInstance().dataToExcel(dataList, "CompanyInfo", columnName);        ByteArrayOutputStream os = new ByteArrayOutputStream();        try {            workbook.write(os);        } catch (IOException e) {            e.printStackTrace();        }        byte[] content = os.toByteArray();        InputStream is = new ByteArrayInputStream(content);        // 设置response参数，可以打开下载页面        servletResponse.reset();        servletResponse.setContentType("application/vnd.ms-excel;charset=utf-8");        servletResponse.setHeader("Content-Disposition", "attachment;filename=" + new String(("CompanyInfo" + ".xls").getBytes(), "iso-8859-1"));        ServletOutputStream out = servletResponse.getOutputStream();        BufferedInputStream bis = null;        BufferedOutputStream bos = null;        try {            bis = new BufferedInputStream(is);            bos = new BufferedOutputStream(out);            byte[] buff = new byte[2048];            int bytesRead;            // Simple read/write loop.            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {                bos.write(buff, 0, bytesRead);            }        } catch (final IOException e) {            throw e;        } finally {            if (bis != null)                bis.close();            if (bos != null)                bos.close();        }    }

下图这是整个ExcelUtil的代码

package Utils;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.InputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;import java.util.Map;/** * Created by Clanner on 2017/5/10. */public class ExcelUtil {    private ExcelUtil() {    }    private final String excel2003L = ".xls";//2003- 版本的excel    private final String excel2007U = ".xlsx";//2007+ 版本的excel    /**     * 根据文件后缀，自适应上传文件的版本     */    private Workbook getWorkbook(InputStream inStr, String filename) throws Exception {        Workbook workbook;        String fileType = filename.substring(filename.lastIndexOf("."));        if (excel2003L.equals(fileType)) {            workbook = new HSSFWorkbook(inStr);//2003-        } else if (excel2007U.equals(fileType)) {            workbook = new XSSFWorkbook(inStr);//2007+        } else {            throw new Exception("解析的文件格式有误");        }        return workbook;    }    /**     * 获取excel中的数据     */    public List<List<Object>> readExcelData(InputStream in, String filename) throws Exception {        List<List<Object>> list;        //创建Excel工作薄        Workbook work = getWorkbook(in, filename);        if (null == work) {            throw new Exception("创建Excel工作薄为空！");        }        Sheet sheet = null;        Row row = null;        Cell cell = null;        list = new ArrayList<List<Object>>();        //遍历Excel中所有的sheet        for (int i = 0; i < work.getNumberOfSheets(); i++) {            sheet = work.getSheetAt(i);            if (sheet == null) {                continue;            }            //遍历当前sheet中的所有行            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {                row = sheet.getRow(j);                if (row == null || row.getFirstCellNum() == j) {                    continue;                }                //遍历所有的列                List<Object> li = new ArrayList<Object>();                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {                    cell = row.getCell(y);                    //通过getCellValue方法获取当前行每一列中的数据                    li.add(getCellValue(cell));                }                //将每一行的数据添加到list                list.add(li);            }        }        in.close();        return list;    }    /**     * 获取每个单元格的内容     */    private Object getCellValue(Cell cell) {        Object value = null;        DecimalFormat df = new DecimalFormat("0");//格式化number String字符串        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//日期格式化        switch (cell.getCellType()) {            case Cell.CELL_TYPE_STRING:                value = cell.getRichStringCellValue().getString();                break;            case Cell.CELL_TYPE_NUMERIC:                if ("General".equals(cell.getCellStyle().getDataFormatString())) {                    value = df.format(cell.getNumericCellValue());                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {                    value = sdf.format(cell.getDateCellValue());                } else {                    value = cell.getNumericCellValue();                }                break;            case Cell.CELL_TYPE_BOOLEAN:                value = cell.getBooleanCellValue();                break;            case Cell.CELL_TYPE_BLANK:                value = "";                break;            default:                break;        }        return value;    }    /**     * 数据转成excel     *     * @param dataList   需要转换的数据     * @param sheetName  生成的excel表名     * @param columnName 每一列的名字(key)     * @return     */    public Workbook dataToExcel(List<Map<String, String>> dataList, String sheetName, String[] columnName) {        int columnNum = columnName.length;        Workbook workbook = new HSSFWorkbook();        //创建第一页，并命名        Sheet sheet = workbook.createSheet(sheetName);        //创建第一行        Row row = sheet.createRow(0);        // 创建两种单元格格式        CellStyle cs = workbook.createCellStyle();        CellStyle cs2 = workbook.createCellStyle();        // 创建两种字体        Font f = workbook.createFont();        Font f2 = workbook.createFont();        // 创建第一种字体样式（用于列名）        f.setFontHeightInPoints((short) 10);        f.setColor(IndexedColors.BLACK.getIndex());        f.setBoldweight(Font.BOLDWEIGHT_BOLD);        // 创建第二种字体样式（用于值）        f2.setFontHeightInPoints((short) 10);        f2.setColor(IndexedColors.BLACK.getIndex());        //设置列名        for (int i = 0; i < columnNum; i++) {            Cell cell = row.createCell(i);            cell.setCellValue(columnName[i]);            cell.setCellStyle(cs);        }        //设置每行每列的值        int rowNum = dataList.size();        for (int j = 1; j <= rowNum; j++) {            //创建一行            Row r = sheet.createRow(j);            for (int k = 0; k < columnNum; k++) {                Cell cell = r.createCell(k);                cell.setCellValue(dataList.get(j - 1).get(columnName[k]));                cell.setCellStyle(cs2);            }        }        return workbook;    }    public static final ExcelUtil getInstance() {        return ExcelUtilHolder.instance;    }    private static final class ExcelUtilHolder {        private static final ExcelUtil instance = new ExcelUtil();    }}

1 0