Java操作excel_导出与读取(导入)

我自己的jar包下载

一、介绍
　　常常有客户这样子要求：你要把我们的报表直接用Excel打开(电信系统、银行系统)。或者是：我们已经习惯用Excel打印。这样在我们实际的开发中，很多时候需要实现导入、导出Excel的应用。
　　目前，比较常用的实现Java导入、导出Excel的技术有:Jakarta POI、freemarker和Java Excel
　　下面我就分别讲解一下如何使用这些技术实现导入、导出Excel
二、使用Jakarta POI导出Excel
　　Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成，其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF，
　　在各种组件中目前只有用于操作Excel的HSSF相对成熟。官方主页http://poi.apache.org/index.html，API文档http://poi.apache.org/apidocs/index.html
　1.环境配置

　　下载jar
　　　　官方下载：http://poi.apache.org/download.html这里可以下载到它的最新版本和文档，目前最新版本是3.7，这里使用比较稳定的3.6版。
　　加入jar包
　　　　将根目录下的poi-3.6-20091214.jar和Lib目录下三个通用包 commons-logging-1.1.jar junit-3.8.1.jar log4j-1.2.13.jar拷贝到项目的Lib下
　2.Jakarta POI HSSF API组件

　　HSSF（用于操作Excel的组件）提供给用户使用的对象在rg.apache.poi.hssf.usermodel包中,主要部分包括Excel对象，样式和格式，还有辅助操作。有以下几种对象：
　　常用组件：
　　　　HSSFWorkbook excel的文档对象
　　　　HSSFSheet excel的表单
　　　　HSSFRow excel的行
　　　　HSSFCell excel的格子单元
　　　　HSSFFont excel字体
　　　　HSSFDataFormat 日期格式
　　　　HSSFHeader sheet头
　　　　HSSFFooter sheet尾（只有打印的时候才能看到效果）
　　样式：
　　　　HSSFCellStyle cell样式
　　辅助操作包括：
　　　　HSSFDateUtil 日期
　　　　HSSFPrintSetup 打印
　　　　HSSFErrorConstants 错误信息表
　3. 基本操作步骤

　　首先，理解一下一个Excel的文件的组织形式，一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个sheet（HSSFSheet）组成，一个sheet是由多个row（HSSFRow）组成，一个row是由多个cell（HSSFCell）组成。
　　基本操作步骤：
　　　　a、用HSSFWorkbook打开或者创建“Excel文件对象”
　　　　b、用HSSFWorkbook对象返回或者创建Sheet对象
　　　　c、用Sheet对象返回行对象，用行对象得到Cell对象
　　　　d、对Cell对象读写。

　4.我自己写的测试用例

　　

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

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

public class POIExportExcel {
    public HSSFWorkbook generateExcel1(List list, HSSFWorkbook wb, String title) {
        //行的数量
        int rows = 0;
        HSSFSheet sheet = wb.createSheet("Sheet1");
        // 数据格样式
        HSSFCellStyle dataCellStyle = createDataCellStyle(wb);
        // 小标题样式
        HSSFCellStyle cellStyle = createCellStyle(wb);
        sheet.setDefaultRowHeight((short) 400);//设置所有行高为400
        //设置列宽
        sheet.setColumnWidth((short) 1, (short) 2800);
        sheet.setColumnWidth((short) 2, (short) 2800);
        sheet.setColumnWidth((short) 3, (short) 2800);
        sheet.setColumnWidth((short) 4, (short) 2800);
        sheet.setColumnWidth((short) 5, (short) 2800);
        sheet.setColumnWidth((short) 6, (short) 2800);
        sheet.setColumnWidth((short) 7, (short) 2800);
        sheet.setColumnWidth((short) 8, (short) 2800);
        sheet.setColumnWidth((short) 9, (short) 2800);
        sheet.setColumnWidth((short) 10, (short) 3200);
        sheet.setColumnWidth((short) 11, (short) 3200);
        sheet.setColumnWidth((short) 12, (short) 3200);
        sheet.setColumnWidth((short) 13, (short) 3200);
        //标题行
        HSSFRow row1 = sheet.createRow((short) (rows++));
        row1.setHeight((short) 500);
        sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 13));//横跨1-14列
        createCell(row1, (short) 0, cellStyle, title);
        //表头行(1,2列合并；3,4,5,6,7列合并；8,9列合并)
        HSSFRow row2 = sheet.createRow((short) (rows++));
        sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) 1));
        sheet.addMergedRegion(new Region(1, (short) 2, 1, (short) 6));
        sheet.addMergedRegion(new Region(1, (short) 7, 1, (short) 8));
        sheet.addMergedRegion(new Region(1, (short) 9, 1, (short) 9));
        sheet.addMergedRegion(new Region(1, (short) 10, 1, (short) 10));
        sheet.addMergedRegion(new Region(1, (short) 11, 1, (short) 11));
        sheet.addMergedRegion(new Region(1, (short) 12, 1, (short) 12));
        sheet.addMergedRegion(new Region(1, (short) 13, 1, (short) 13));
        createCell(row2, (short) 0, dataCellStyle, "name1");
        createNullCell(row2, (short) 1, dataCellStyle);
        createCell(row2, (short) 2, dataCellStyle, "name2");
        createNullCell(row2, (short) 3, dataCellStyle);
        createNullCell(row2, (short) 4, dataCellStyle);
        createNullCell(row2, (short) 5, dataCellStyle);
        createNullCell(row2, (short) 6, dataCellStyle);
        createCell(row2, (short) 7, dataCellStyle, "name3");
        createNullCell(row2, (short) 8, dataCellStyle);
        createCell(row2, (short) 9, dataCellStyle, "name4");
        createCell(row2, (short) 10, dataCellStyle, "name5");
        createCell(row2, (short) 11, dataCellStyle, "name6");
        createCell(row2, (short) 12, dataCellStyle, "name7");
        createCell(row2, (short) 13, dataCellStyle, "name8");
        //遍历集合,保存数据到单元格
        for(int i=0;i<list.size();i++){
            Object javaBean = list.get(i);
            //具体的数据值通过对象去获取，这里简单给值
            String variable1 = "value1"+"_"+i;
            String variable2 = "value2"+"_"+i;
            String variable3 = "value3"+"_"+i;
            String variable4 = "value4"+"_"+i;
            String variable5 = "value5"+"_"+i;
            String variable6 = "value6"+"_"+i;
            String variable7 = "value7"+"_"+i;
            String variable8 = "value8"+"_"+i;
            
            HSSFRow rowi = sheet.createRow((short) (rows++));
            sheet.addMergedRegion(new Region(i+2, (short) 0, i+2, (short) 1));
            sheet.addMergedRegion(new Region(i+2, (short) 2, i+2, (short) 6));
            sheet.addMergedRegion(new Region(i+2, (short) 7, i+2, (short) 8));
            sheet.addMergedRegion(new Region(i+2, (short) 9, i+2, (short) 9));
            sheet.addMergedRegion(new Region(i+2, (short) 10, i+2, (short) 10));
            sheet.addMergedRegion(new Region(i+2, (short) 11, i+2, (short) 11));
            sheet.addMergedRegion(new Region(i+2, (short) 12, i+2, (short) 12));
            sheet.addMergedRegion(new Region(i+2, (short) 13, i+2, (short) 13));
            createCell(rowi, (short) 0, dataCellStyle, variable1);
            createNullCell(rowi, (short) 1, dataCellStyle);
            createCell(rowi, (short) 2, dataCellStyle, variable2);
            createNullCell(rowi, (short) 3, dataCellStyle);
            createNullCell(rowi, (short) 4, dataCellStyle);
            createNullCell(rowi, (short) 5, dataCellStyle);
            createNullCell(rowi, (short) 6, dataCellStyle);
            createCell(rowi, (short) 7, dataCellStyle, variable3);
            createNullCell(rowi, (short) 8, dataCellStyle);
            createCell(rowi, (short) 9, dataCellStyle, variable4);
            createCell(rowi, (short) 10, dataCellStyle, variable5);
            createCell(rowi, (short) 11, dataCellStyle, variable6);
            createCell(rowi, (short) 12, dataCellStyle, variable7);
            createCell(rowi, (short) 13, dataCellStyle, variable8);
        }

        return wb;
    }
    /**
     * 创建单元格
     * @param row
     * @param id
     * @param style
     * @param value
     */
    private void createCell(HSSFRow row, short id, HSSFCellStyle style,
            String value) {
        HSSFCell cell = row.createCell((short) id);
        // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(value);
        cell.setCellStyle(style);
    }
    /**
     * 创建空的单元格
     */
    private HSSFCell createNullCell(HSSFRow row, short id, HSSFCellStyle style) {
        HSSFCell null1Cell = row.createCell(id);
        null1Cell.setCellStyle(style);
        return null1Cell;
    }
    /**
     * 设置数据单元格样式
     * @param wb
     * @return
     */
    private HSSFCellStyle createDataCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle dataCellStyle = wb.createCellStyle();
        // 水平居中
        dataCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        dataCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        dataCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        dataCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        dataCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        return dataCellStyle;
    }
    /**
     * 设置单元格样式
     * @param wb
     * @return
     */
    private HSSFCellStyle createCellStyle(HSSFWorkbook wb) {
        // 小标题样式
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 水平居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 字体
        HSSFFont littleFont = wb.createFont();
        // 设置字体为粗体
        littleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 然后将字体关联到样式
        cellStyle.setFont(littleFont);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return cellStyle;
    }
    
    public static void main(String[] args){
        /*String serverPath = request.getSession().getServletContext().getRealPath("/")+"exportExcel";   
        //在服务器端创建文件夹   
        File file = new File(serverPath+path);   
        if(!file.exists()){   
            file.mkdir();   
        } 
        SimpleDateFormat sfm = new SimpleDateFormat("yyyy-MM-dd");   
        String filename = "excel" + "_" + sfm.format(new java.util.Date());   
        String encodedfileName = new String(filename.getBytes(), "GBK");   
        FileOutputStream out = new FileOutputStream(serverPath+path+"/"+encodedfileName+".xls");  */ 
        new POIExportExcel();
        String title = "POI导出excel";
        List list = new ArrayList<String>();
        //加入两条做个测试
        list.add(new String());
        list.add(new String());
        try {
            FileOutputStream out = new FileOutputStream("D:/"+title+".xls");
            HSSFWorkbook wb =new HSSFWorkbook();
            wb = new POIExportExcel().generateExcel1(list,wb,title);
            wb.write(out);
            /*List srcfile=new ArrayList(); 
            srcfile.add(new File(serverPath+path+"/"+encodedfileName+".xls"));   
            //将服务器上存放Excel的文件夹打成zip包   
            File zipfile = new File(serverPath+path+".zip");   
            ZipUtil.zipFiles(srcfile, zipfile);   
            //弹出下载框供用户下载   
            this.downFile(response,serverPath, path+".zip");*/
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

　　结果如下图：

　　

三、使用Jakarta POI读取(导入)Excel

　　依赖dom4j.jar和xmlbeans.jar

　　　　没有的话会抛出：Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject

　　

import java.io.File;
import java.io.FileInputStream;
import java.sql.Date;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.RichTextString;
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.xssf.usermodel.XSSFWorkbook;

public class MyExcelReader {
    /**
     * 分析获取上传的excel文件中的数据信息。 仅对Excel文件的第一张表进行分析，分析过程中如果遇到空行就结束。
     * @param file
     *            excel
     * @return excel中对应的数据以二维数组形式返回
     * @throws Exception
     */
    private String[][] readExcel(File file) {
        String[][] data = null;
        Workbook wb = null;
        try {
            // 解析excel2003
            if (file.getName().endsWith(".xls")) {
                POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
                        file.getPath()));
                // excel数据对象
                wb = new HSSFWorkbook(fs);
            }
            // 解析excel2007及以上
            if (file.getName().endsWith(".xlsx")) {
                wb = new XSSFWorkbook(new FileInputStream(file.getPath()));
            }
            // excel中对应的表单
            Sheet sheet = wb.getSheetAt(0);
            // excel中的有效列数
            int colNum = getValidColNum(sheet);
            // 只有excel的有效列数大于零才对数据进行分析
            if (colNum > 0) {
                // 从Excel中获取到的有效数据
                data = getExcelData(sheet, colNum);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return data;
    }

    /**
     * 得到表单的有效列数. 获取表单第一行开始位置连续非空单元格的个数
     * 
     * @param sheet
     *            Excel的表单
     * @return 列数
     * @throws Exception
     */
    private int getValidColNum(Sheet sheet) throws Exception {
        // 判断文件是否为空文件
        if (sheet.getRow(0) == null) {
            System.out.println("文件为空");
            throw new Exception("error.importExcel.blankExcelFile");
        }
        // 表单总列数
        int colNum = sheet.getRow(0).getPhysicalNumberOfCells();
        Row row = sheet.getRow(0);
        // 如果表单第一行即表头位置出现空单元格，该单元格后面的信息不在导入
        for (int i = 0; i < colNum; i++) {
            if (row.getCell((short) i) != null) {
                Cell cell = row.getCell((short) i);
                String value = this.getCellValue(cell);
                if (StringUtils.isEmpty(value)) {
                    colNum = i;
                    break;
                }
            }
            // 如果列表头信息为空，不读取后面的数据
            else {
                colNum = i;
            }
        }
        return colNum;
    }

    /**
     * 读取Excel数据. 读取过程中，遇到空行自动停止，舍弃空行后面的数据。 可根据数组信息获取Excel的有效行数
     * 
     * @param sheet
     *            表单对象
     * @param colNum
     *            有效列数
     * @return 数据二维数组
     */
    private String[][] getExcelData(Sheet sheet, int colNum) {
        int tempRowNum = sheet.getPhysicalNumberOfRows();
        // 存储从Excel表中读取的数据信息
        List<List<String>> dataList = new ArrayList<List<String>>();
        // 读取Excel数据，将其存储在可变长的List容器中，同时得到Excel文件的有效数据行数
        int rowNum = 0;
        for (; rowNum < tempRowNum && sheet.getRow(rowNum) != null; rowNum++) {
            List<String> rowData = new ArrayList<String>();
            // 对行信息进行累加，判断该行是否为空行
            String rowValue = "";
            for (int j = 0; j < colNum; j++) {
                if (sheet.getRow(rowNum).getCell((short) j) != null) {
                    Cell cell = sheet.getRow(rowNum).getCell((short) j);
                    String value = getCellValue(cell);
                    rowData.add(value);
                    rowValue += value;
                } else {
                    rowData.add(null);
                }
            }
            // 读取信息时如遇到空行，结束读入
            if (rowValue.length() == 0) {
                break;
            } else {
                dataList.add(rowData);
            }
        }
        // 将Excel数据转存到数组对象中
        String[][] data = new String[rowNum][colNum];
        for (int i = 0; i < dataList.size(); i++) {
            for (int j = 0; j < dataList.get(i).size(); j++) {
                data[i][j] = dataList.get(i).get(j);
            }
        }
        return data;
    }

    /**
     * 得到Excel单元格的数据内容
     * 
     * @param cell
     *            Excel单元格对象
     * @return 单元格的内容
     */
    public String getCellValue(Cell cell) {
        // excel的日期格式和java的有很大区别
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
                && DateUtil.isCellDateFormatted(cell)) {
            return new Date(cell.getDateCellValue().getTime()).toString();
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            double value = cell.getNumericCellValue();
            return new DecimalFormat("###.####").format(value);
        } else {
            String result = "";
            try {
                RichTextString textString = cell.getRichStringCellValue();
                if (textString != null) {
                    result = textString.getString().trim();
                    result = this.subStr(result);
                    if (result.length() > 0) {
                        char firstChar = result.charAt(0);
                        // 如果信息不是以英文字符或数字开始，替换掉信息中的所有中英文空格，以英文或数字开始的信息不处理
                        if (!(firstChar >= 'a' && firstChar <= 'z'
                                || firstChar >= 'A' && firstChar <= 'Z' || firstChar >= '0'
                                && firstChar <= '9')) {
                            result = result.replaceAll(" ", "").replaceAll("　",
                                    "");
                        }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return result;
        }
    }

    /**
     * 截取字符串中的内码
     * 
     * @param str
     * @return
     */
    public String subStr(String str) {
        for (int i = 0; i < str.length(); i++) {
            // 韩文等乱码,均以&#开头以；结束的字符
            if (str.indexOf("&#") >= 0
                    && str.indexOf(";", str.indexOf("&#")) > 0) {
                String s2 = str.substring(str.indexOf("&#"), str.indexOf(";",
                        str.indexOf("&#")) + 1);
                s2 = this.cov(s2);
                str = str.substring(0, str.indexOf("&#"))
                        + s2
                        + str.substring(
                                str.indexOf(";", str.indexOf("&#")) + 1, str
                                        .length());
            }
        }
        return str;
    }

    /**
     * 转化编码，韩文等乱码
     * 
     * @param string
     * @return
     */
    public String cov(String string) {
        String str = string.replaceAll("&#", ",").replaceAll(";", "");
        String[] s2 = str.split(",");
        String s1 = "";
        for (int i = 1; i < s2.length; i++) {
            int a = Integer.parseInt(s2[i], 10);
            s1 = s1 + (char) a;
        }
        return s1;
    }
    /**
     * 测试
     * @param args
     */
    public static void main(String[] args) {
        File file = new File("D:/test.xlsx");
        // 保存Excel文件到服务器
        String[][] dataArray = new MyExcelReader().readExcel(file);
        // System.out.println("标题："+dataArray[0].toString());
        for (int i = 0; i < dataArray.length; i++) {
            String[] rowData = dataArray[i];
            for (int j = 0; j < rowData.length; j++) {
                System.out.println("第" + (i + 1) + "行第" + (j + 1) + "列："
                        + dataArray[i][j]);
            }
        }
    }
}

 

三、使用freemarker导出excel

貌似是只支持office2007之后的版本，wps也会出问题。

先放两个链接供参考：

　　http://blog.csdn.net/zgf19930504/article/details/50804122

　　https://my.oschina.net/u/1765449/blog/272334

 

四、第三方插件Java Excel(jxl)操作excel

http://blog.csdn.net/jerehedu/article/details/45195359 

 

 

 

 

 

 

 

参考文章:

　　http://blog.csdn.net/jerehedu/article/details/45195359