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

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

        }

    }

}

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

            }

        }

    }

}